Build an E-commerce Analytics Platform with dbt

Your SQL queries work. But your CEO's dashboard is wrong — and you can't explain why. Build the production analytics platform that prevents that forever.

~8–10 hrsIntermediate4 Parts

dbt CoreSQLJinjaPostgreSQLdbt CloudGitHub ActionsSQLFluffGit

  ┌─────────────┐
  │  raw_*      │  Sources (5 tables)
  │  customers  │  orders, items,
  │  products   │  web_events
  └──────┬──────┘
         │
         v
  ┌─────────────┐
  │  stg_*      │  Staging (5 models)
  │  Clean &    │  Type casting,
  │  Rename     │  deduplication
  └──────┬──────┘
         │
    ┌────┴────┐
    v         v
┌────────┐ ┌────────┐
│ int_*  │ │ int_*  │  Intermediate
│ orders │ │ metrics│  (4 models)
└───┬────┘ └───┬────┘
    │          │
    └────┬─────┘
         v
  ┌─────────────┐
  │  fact_*     │  Marts (5 models)
  │  dim_*      │  Star schema
  │  fct_*      │  BI-ready
  └──────┬──────┘
         │
         v
  ┌─────────────┐
  │  snapshots  │  SCD Type 2
  │  incremental│  8x faster
  └─────────────┘

Fig 1.1: dbt model lineage — 17 models across 5 layers

What You'll Build

A real analytics platform — not a tutorial project. Something you'd be proud to show in an interview.

5-Layer dbt Architecture

Sources → Staging → Intermediate → Marts → Snapshots. 17 production models following industry best practices.

E-commerce Star Schema

Fact tables and dimensions that answer real business questions: revenue by segment, cohort retention, product performance.

Incremental + Historical Tracking

Models that refresh in 30 seconds (not 4 minutes) and SCD Type 2 snapshots that capture customer history over time.

Production CI/CD Pipeline

GitHub Actions with Slim CI, automated testing, monitoring alerts — the same setup used at companies like Airbnb and Stripe.

Progressive Build Path

Each part builds on the previous. Watch your analytics platform grow from 5 to 17 models.

Foundation — Clean the Broken Data

2 hrs

Your raw data is messy. Set up the dbt project and build 5 staging models that clean duplicates, fix types, and make the data trustworthy.

Clean staging layer ready

5/17 models

dbt initSourcesstg_customersstg_ordersstg_products

Star Schema — Answer Questions No One Could Before

2.5 hrs

Design the grain, build fact_orders and 4 dimensions. The CEO's dashboard finally answers: "What is our revenue by customer segment this quarter?"

Star schema + analytics marts built

14/17 models

Grain Designfact_ordersdim_customersfct_cohortsfct_performance

Scale & History — Don't Lose Yesterday's Data

2.5 hrs

Add incremental models (8x faster refreshes) and SCD Type 2 snapshots to track how customers changed over time. Never lose historical data again.

Incremental + customer history captured

17/17 models

IncrementalSCD Type 2SnapshotsPartitioning

Production — Ship It and Own It

2.5 hrs

CI/CD pipeline, automated testing, monitoring, and documentation. You own a production-grade analytics platform that your team will trust.

Production analytics platform deployed

LIVE system

dbt CloudGitHub ActionsSlim CIAlertsdbt Docs

Total: ~8–10 hours across 4 parts

Seed Your Database

ShopCo's raw e-commerce data — 3M+ records across 5 tables

terminal

# Clone the project & seed ShopCo data
$ git clone https://github.com/aide/shopco-analytics.git
$ cd shopco-analytics

# Install dbt & seed raw data into your warehouse
$ pip install dbt-postgres
$ dbt seed --select shopco_raw

# Verify: 5 tables, 3M+ records loaded
$ dbt source freshness

customers

100K

orders

500K

order_items

products

10K

web_events

Production Standards

CI/CD Automation

GitHub Actions pipeline with Slim CI for pull requests, automated testing, and production deployment workflows.

Data Governance

Source freshness checks, schema tests, unique/not-null constraints, and documented column descriptions across all models.

Modular Architecture

5-layer design (Sources → Staging → Intermediate → Marts → Snapshots) following dbt best practices and naming conventions.

Prerequisites

SQL fundamentals (SELECT, JOIN, GROUP BY, window functions)
Command line basics (running commands, navigating dirs)
Git basics (commit, push, pull requests)
E-commerce metrics understanding (helpful, not required)

Related Learning Path

This project pairs perfectly with the dbt Fundamentals skill toolkit. Complete the modules first for maximum understanding, or dive straight in if you have prior dbt experience.

View dbt Skill Toolkit

Ready to build?