Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 41 ยท Projects and Practice

Project: Shop Analytics Pipeline

Design a complete dbt project for an online shop: sources, staging, intermediate and mart models, tests and docs, with every SQL layer runnable in your browser.

In this project you build the reporting layer for an online shop, the way you would in a real dbt repository. You will plan the sources, write staging, intermediate and mart models, and attach the tests that keep them honest. The real dbt engine cannot run on this site, so the project files are shown read-only and the SQL of each model runs against the sample shop database. Everything you run here is exactly what dbt would send to your warehouse after Jinja has been resolved.

The goal

The business asks for two things: a table of orders with their revenue for finance, and a customer table with lifetime value for marketing. Raw data has five tables: categories, products, customers, orders and order_items.

flowchart LR
  S1["source: orders"] --> A["stg_shop__orders"]
  S2["source: order_items"] --> B["stg_shop__order_items"]
  S3["source: customers"] --> C["stg_shop__customers"]
  S4["source: products"] --> D["stg_shop__products"]
  A --> I["int_orders_with_totals"]
  B --> I
  I --> F["fct_orders"]
  C --> G["dim_customers"]
  F --> G

The target DAG: four staging models, one intermediate, two marts.

Step 1: declare the sources

YAML
# models/staging/shop/_shop__sources.yml
version: 2

sources:
  - name: shop
    schema: raw_shop
    tables:
      - name: orders
        columns:
          - name: id
            data_tests: [unique, not_null]
      - name: order_items
      - name: customers
      - name: products

Step 2: staging models

One per source table: rename, cast, nothing more. Here are all four, run together as one query so you can see the cleaned shapes (in dbt each is its own file):

SQL Staging layer ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Each count should equal the raw table's row count. That is your first, simplest test: staging never adds or removes rows.

Step 3: an intermediate model

Finance needs each order's item total, which more than one mart will use, so it becomes int_orders_with_totals:

SQL
-- models/intermediate/int_orders_with_totals.sql
with orders as (select * from {{ ref('stg_shop__orders') }}),
items as (select * from {{ ref('stg_shop__order_items') }}),

totals as (
    select order_id, sum(quantity * unit_price) as items_amount, sum(quantity) as units
    from items
    group by order_id
)

select
    orders.*,
    coalesce(totals.items_amount, 0) as items_amount,
    coalesce(totals.units, 0) as units
from orders
left join totals using (order_id)

Run the same logic here:

SQL Intermediate model ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The left join and coalesce matter: an order with no items must stay in the model (with zero), or the row count would silently shrink.

Step 4: the marts

fct_orders adds the business definition of revenue: cancelled orders earn nothing, and shipping counts.

SQL
-- models/marts/finance/fct_orders.sql
{{ config(materialized='table') }}

select
    order_id,
    customer_id,
    ordered_at,
    order_status,
    items_amount,
    shipping_amount,
    case when order_status = 'cancelled' then 0
         else items_amount + shipping_amount end as revenue
from {{ ref('int_orders_with_totals') }}

dim_customers adds lifetime value, the order count and the first order date:

SQL Marts: revenue and customer lifetime value ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Step 5: tests and docs

Attach the tests you learned in this chapter to the marts:

YAML
# models/marts/finance/_finance__models.yml
version: 2

models:
  - name: fct_orders
    description: "One row per order. Revenue excludes cancelled orders."
    columns:
      - name: order_id
        data_tests: [unique, not_null]
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              arguments: {to: ref('dim_customers'), field: customer_id}
      - name: order_status
        data_tests:
          - accepted_values:
              arguments:
                values: ['pending', 'shipped', 'delivered', 'cancelled']
      - name: revenue
        data_tests:
          - not_negative

  - name: dim_customers
    description: "One row per customer with lifetime value."
    columns:
      - name: customer_id
        data_tests: [unique, not_null]

A singular test keeps the logic honest: the sum of revenue must reconcile with the raw tables. This is the SQL that test would hold, run on the data:

SQL A reconciliation test ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

An empty result means the two independent computations agree. Reconciliation tests like this are the best way to trust a mart.

Step 6: run it

Terminal
dbt deps
dbt build --select +fct_orders+ dim_customers
dbt docs generate

Next you harden the project with quality gates in the testing project, and model a large event table in the incremental events project.

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. Why does the intermediate model use a left join from orders to totals?

    An inner join would silently drop orders that have no items.
  2. 2. Which layer defines the business meaning of revenue?

    Staging stays a clean mirror; business rules such as excluding cancelled orders live in marts (or intermediate steps).
  3. 3. What does a reconciliation test do?

    If both computations agree, the transformation is probably correct.

Summary

  • Plan the DAG first: sources, staging, intermediate, marts.
  • Staging only renames and casts; business rules live in marts.
  • Attach unique, not_null, relationships and accepted_values tests, plus a reconciliation test.
  • Run dbt build on the selection and generate docs.

Next: Project: Incremental Events Model