Retail Analytics Platform — dbt + BigQuery

Analytics Engineering case study simulating a production data pipeline for a mid-market retail company. Demonstrates advanced dbt patterns applied to a realistic business scenario.

Architecture

Raw Data (BigQuery) → Staging → Marts
                                ├── dim_customers (SCD Type 2 ready)
                                ├── dim_products
                                ├── dim_products_scd (SCD Type 2)
                                ├── dim_warehouses
                                ├── fact_orders (Incremental)
                                └── fact_order_items

Stack

• Transformation: dbt Core 1.11 + dbt-bigquery
• Warehouse: Google BigQuery (EU region)
• Data Generation: Python (Faker) — 5k customers, 200 products, 15k orders
• Testing: dbt_utils, dbt_expectations, custom singular tests
• Orchestration: Apache Airflow (DAG in /orchestration)
• CI/CD: GitHub Actions (.github/workflows/)
• Quality: SQLFluff (pre-commit hooks)

Advanced dbt Patterns

SCD Type 2 — dim_products_scd

Tracks historical changes in product price and supplier. Each change creates a new row with validity dates (valid_from, valid_to, is_current). Uses dbt_utils.generate_surrogate_key for version-aware surrogate keys.

Incremental Models — fact_orders

Processes only new/updated orders on each run using updated_at watermark. Reduces processing from full table scan to daily delta. Uses unique_key for merge strategy.

Custom Macros

Macro	Purpose
calculate_revenue	Centralized revenue calculation with discount support
customer_segmentation	Reusable segmentation logic (never_ordered → loyal)
cents_to_dollars	Monetary unit conversion

Data Quality — 47 Tests

• Generic: unique, not_null, relationships (referential integrity)
• dbt_expectations: value ranges, data types, accepted values
• Singular tests: business rule validation across tables

Test	Business Rule
assert_order_total_matches_items	Revenue totals must match line items
assert_no_negative_inventory	Inventory cannot be negative post-cleaning
assert_active_products_have_price	Active products must have valid price

Data Model

Staging Layer (8 models)

Cleans and standardizes raw source data:

• stg_customers — deduplication by email, row numbering
• stg_products — active/discontinued logic
• stg_orders — status normalization, orphan filtering
• stg_order_items — line total calculation
• stg_inventory — negative quantity handling
• stg_warehouses, stg_suppliers, stg_shipments

Marts Layer (6 models)

Business-ready dimensional model:

• dim_customers — lifetime metrics, segmentation macro
• dim_products — supplier enrichment
• dim_products_scd — full change history (SCD Type 2)
• dim_warehouses — warehouse master data
• fact_orders — order-level metrics (incremental)
• fact_order_items — most granular fact table

Business Logic Decisions

Decision	Rationale
confirmed grouped with pending	Not yet shipped, same operational treatment
Cancelled orders excluded from LTV	Revenue recognition: only completed orders
Negative inventory → 0	Data cleaning: upstream pipeline issue, not real stock
Future discontinued_at → still active	Product not yet discontinued
Orphan orders filtered	1,202 orders with non-existent customer_id excluded

CI/CD Pipeline

GitHub Actions workflow runs on every PR:

- dbt run --select state:modified+
- dbt test --select state:modified+

Pre-commit hooks enforce SQL style with SQLFluff.

Orchestration

Airflow DAG (orchestration/dbt_retail_dag.py):

dbt_run_staging >> dbt_run_marts >> dbt_test

Project Structure

dbt_retail/
├── models/
│   ├── staging/          # 8 staging models + sources.yml
│   └── marts/core/       # 6 mart models + schema.yml
├── macros/               # 3 custom macros
├── tests/                # 3 singular tests
├── .github/workflows/    # CI/CD pipeline
└── orchestration/        # Airflow DAG

Running the Project

# Install dependencies
pip install dbt-bigquery
dbt deps

# Run all models
dbt run

# Run tests
dbt test

# Generate documentation
dbt docs generate
dbt docs serve

Key Results

• 47 data tests — generic, expectation-based, and business rule validation
• SCD Type 2 — full product change history with surrogate keys
• Incremental processing — optimized for production-scale data volumes
• 3 custom macros — reusable business logic across models
• Documented lineage — full dbt docs with column-level descriptions