Overview

Moving from ad-hoc analytics to a structured, tested, and documented data transformation pipeline. This project demonstrates how dbt brings software engineering practices — version control, testing, documentation, CI/CD — to data transformation.

Data Model

Staging Layer

Raw source tables are cleaned and standardized in the staging layer. Column names are normalized, data types are cast explicitly, and basic filtering (removing test/deleted records) is applied.

-- stg_suppliers.sql
select
    supplier_id,
    trim(supplier_name) as supplier_name,
    upper(region) as region,
    cast(created_at as timestamp) as created_at
from {{ source('raw', 'suppliers') }}
where is_deleted = false

Intermediate Layer

Business logic transformations that combine multiple staging models. This is where joins, aggregations, and business rule calculations live.

Mart Layer

Final star schema tables optimized for analytics consumption:

  • dim_suppliers — Supplier attributes with SCD Type 2 history
  • dim_products — Product catalog with category hierarchy
  • dim_dates — Date dimension with fiscal calendar support
  • fct_orders — Order facts with pre-calculated metrics
  • fct_shipments — Shipment tracking with delivery performance metrics

Testing Strategy

Every model has at minimum:

  • unique and not_null tests on primary keys
  • accepted_values tests on categorical columns
  • relationships tests on foreign keys
  • Custom data tests for business rule validation
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: supplier_id
        tests:
          - relationships:
              to: ref('dim_suppliers')
              field: supplier_id

Results

  • 45 dbt models across 3 layers
  • 120+ data tests with 100% pass rate in production
  • Query performance: 80% reduction in average dashboard load time
  • Full dbt docs site generated automatically for team self-service