A complete data pipeline implementation for the Chinook database with dbt transformations and Dagster orchestration.

chinook-pipeline/
├── dbt/                        # dbt project
│   ├── chinook_analytics/      # Main project
│   │   ├── models/             # Data models
│   │   │   ├── staging/       # Raw source models
│   │   │   └── core/          # Business transformations
│   │   ├── seeds/             # Reference data
│   │   ├── tests/             # Data tests
│   │   └── dbt_project.yml    # Project config
├── dagster/                   # Orchestration
│   ├── repository.py          # Pipeline definitions
│   ├── schedules.py           # Automation config
│   └── workspace.yaml         # Dev environment
├── docker-compose.yml         # Container setup
├── Dockerfile_dbt             # dbt environment
└── scripts/                   # Utility scripts

• Docker
• Docker Compose

git clone https://github.com/fegvilela/chinook-pipeline.git
cd chinook-pipeline

# Start all services
docker-compose up -d --build

# Run all models
docker-compose exec dbt_chinook dbt run

# Run specific model
docker-compose exec dbt_chinook dbt run --models <MODEL_NAME>

# Generate docs
docker-compose exec dbt_chinook dbt docs generate

The Dagster pipeline is configured to:

1. Load new data at 02:00 UTC daily
2. Run dbt transformations
3. Handle incremental updates

View scheduled runs in the Dagster UI.

# Run dbt tests
docker-compose exec dbt_chinook dbt test

# Run unit tests
docker-compose exec dagster pytest

• Incremental models for fact tables
• Dagster assets for data lineage
• Containerized environment
• Daily automation with error handling

Star schema for chinook (simplified diagram)

1. PK for line-item granularity:

   • invoice_line_key as surrogate primary key
   • Enables analysis of individual sale items

2. Dual-key for traceability:

   • Maintains invoice_line_id (original ID) as business key
   • Adds invoice_id for full document reference

3. Optional self-relationship:

   • invoice_key can create an invoice header dimension if needed

1. Fact table has invoice line granularity (not invoice-level)
2. All metrics (quantity, unit_price) are naturally line-items
3. Aggregate queries should use invoice_id for document-level analysis

-- Line-item analysis
SELECT
    f.invoice_line_id,
    t.track_name,
    f.quantity,
    f.total_amount
FROM fact_invoice f
JOIN dim_track t ON f.track_key = t.track_key

-- Complete invoice analysis
SELECT
    f.invoice_id,
    SUM(f.total_amount) as invoice_total
FROM fact_invoice f
GROUP BY f.invoice_id

1. fact_invoice.customer_key → dim_customer.customer_key
2. fact_invoice.employee_key → dim_employee.employee_key
3. fact_invoice.date_key → dim_date.date_key
4. fact_invoice.track_key → dim_track.track_key

• Each row in fact_invoice represents a sale line item
• Calculated metrics: total_amount = quantity × unit_price
• Denormalized dimensions for query optimization

Note: All primary keys (PK) are dbt-generated surrogate keys.

The invoces fact is incremental, based on the invoice_info_key as unique key, that's a surrogate key composed by invoice_id and invoice_line_id. The incremental strategy chosen was the delete+insert, as merge option is not available for the yugabyte database (link), that would be the best one for this case, as we wanted to insert new rows and add the updates, but delete+insert ensures updated records are fully replaced.

invoice_info_key was chosen instead of invoice_id because it enables 1 - n relationships with dimension tables, such as dim_tracks

When implementing a streaming solution that consumes data from a REST API, alternatives like Apache Kafka, Apache Flink, or AWS Kinesis are often more suitable than Dagster or dbt. These tools are designed for real-time data processing, offering low-latency ingestion, event-driven processing, and scalability. For instance, Kafka can act as a message broker to buffer API data, while Flink or Spark Streaming can process it in real time. Lightweight frameworks like FastAPI with WebSockets or serverless solutions (AWS Lambda, Google Cloud Functions) can also be used for near-real-time polling of APIs, depending on the use case.

Dagster and dbt, however, are not ideal for streaming because they are primarily batch-oriented frameworks. Dagster focuses on orchestrating scheduled data pipelines, while dbt specializes in transforming batch-loaded data in warehouses. Neither supports true event-driven processing or low-latency streaming natively. While Dagster can trigger pipelines frequently (e.g., every minute), it still introduces delays and overhead compared to dedicated streaming tools. For real-time use cases, leveraging purpose-built streaming technologies ensures better performance and scalability.

Streaming architecture (simplified diagram)