Build a dbt revenue model with tests
Write a dbt model that transforms a raw orders table into a daily_revenue table with columns: date, revenue, order_count, and a 7-day rolling average of revenue. Add a schema.yml file with not_null and unique tests on the date column, and confirm the tests pass before considering the model complete.
Why this matters
dbt is the tool that turned SQL into software engineering. Version-controlled models, automated tests, and a dependency graph that documents your entire data transformation pipeline; these capabilities make data teams as rigorous as software teams. The daily_revenue model is simple enough to focus entirely on the dbt workflow rather than the SQL complexity.
Before you start
- dbt Core installed with a Postgres or DuckDB adapter (dbt-postgres or dbt-duckdb)
- A dbt project initialised (dbt init your_project)
- A raw orders table with at minimum: order_id, order_date, revenue columns
- Basic SQL including window functions (covered in the window function exercise)
Step-by-step guide
- 1
Write the staging model
Create models/staging/stg_orders.sql that selects from the raw orders table, casts columns to the correct types, and renames them to your conventions. Staging models do not aggregate; they just clean. Run dbt run --select stg_orders and verify the output table looks correct.
- 2
Write the daily_revenue model
Create models/marts/daily_revenue.sql. SELECT order_date, SUM(revenue) as revenue, COUNT(*) as order_count FROM {{ ref('stg_orders') }} GROUP BY order_date. Using ref() rather than a raw table name is how dbt builds the dependency graph; never bypass it.
- 3
Add the 7-day rolling average
Wrap the aggregation in a CTE and add: AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as revenue_7d_avg. This window function computes the trailing 7-day average. Verify the first 6 rows have averages equal to the revenue column divided by the number of available days, not full 7-day windows.
- 4
Write schema.yml tests
Create models/marts/schema.yml. Add not_null and unique tests on the date column. Add an accepted_values test if your data has a status column. Run dbt test --select daily_revenue. A failed test is a schema contract violation; treat it as a build failure.
- 5
Add a data freshness source
Define the raw orders table as a dbt source in models/staging/sources.yml with loaded_at_field and freshness thresholds. Run dbt source freshness. If the source has not been updated recently, this warns before you run stale models. Data freshness awareness is a production-grade habit worth building from the start.