A small, self-contained dbt + DuckDB project that models a synthetic retail dataset into a clean analytical layer — exactly the pattern I'd bring to a modern BI team.
dbt shows up on most modern data-team job descriptions. The goal here was a public artifact — a dbt project written, tested, and documented end-to-end. Small enough to read in twenty minutes, complete enough to demonstrate every dbt concept worth using on a job.
The broader case for dbt: the "SQL transformations as code" pattern has settled the question of how modern BI teams should organize analytical SQL. Before dbt, the dominant pattern was "analyst writes a big SQL view, pastes it into a BI tool, six months later nobody knows how revenue_clean gets computed." After dbt, every transformation is a file in git, every file has tests, every downstream model declares its dependencies via ref(), and dbt docs gives a searchable lineage graph for free. SQL plus a build system that enforces discipline.
The project also needed to be runnable on any laptop with zero cloud setup. Snowflake, BigQuery, and Databricks are the production warehouses, but a reviewer can't be handed a link that spins up cloud infrastructure. DuckDB + dbt-duckdb is the simple local stack: pip install, one file (dev.duckdb), done. It's the default dbt scratchpad for any quick macro test or materialization sanity-check.
Three seed CSVs land raw, get lightly cleaned in the staging layer (views), then get joined and aggregated into four marts (tables). Tests run against the marts. That's the whole show.
flowchart LR
subgraph Seeds
A1[customers.csv]
A2[products.csv]
A3[orders.csv]
end
subgraph Staging["Staging (views)"]
B1[stg_customers]
B2[stg_products]
B3[stg_orders]
end
subgraph Marts["Marts (tables)"]
C1[fct_orders]
C2[customer_lifetime_value]
C3[monthly_revenue]
C4[top_products]
end
A1 --> B1 --> C1
A2 --> B2 --> C1
A3 --> B3 --> C1
C1 --> C2
C1 --> C3
C1 --> C4
DuckDB as the local warehouse. DuckDB is an embedded OLAP engine — SQLite for analytics. One file, no server, columnar storage, runs this whole DAG in under a second. Free, fast, zero-setup, and clone-and-run in 30 seconds. There's a real argument that DuckDB belongs in production for many small-and-medium analytics workloads now; for this project it's just the thing that makes dbt work locally with no friction.
Seeds are 100 customers, 100 products, 150 orders, all synthetic. The size is deliberate — small enough to eyeball every transformation result, large enough for the joins and aggregations to be meaningful. This project is about craft, not scale.
The staging layer's job is small and boring: rename, cast, trim, lowercase. No joins, no business logic, no aggregations. One view per source table. If the source table changes, the fix happens here and every downstream model benefits. This is the discipline that makes dbt projects refactorable — the staging layer is the only place that knows what the source schema looks like, so a column rename touches exactly one file.
stg_customers is representative:
with src as (
select * from {{ ref('customers') }}
)
select
cast(customer_id as integer) as customer_id,
trim(first_name) as first_name,
trim(last_name) as last_name,
lower(trim(email)) as email,
upper(trim(country)) as country_code,
cast(signup_date as date) as signup_date,
lower(trim(segment)) as customer_segment
from src
Nothing clever — that's the point. Staging is materialized as a view because it's cheap to compute, and the cleaned result on every query is preferable to a cached stale one. stg_orders is a similar cast-and-clean pass. stg_products additionally computes unit_margin = unit_price - unit_cost and normalizes a messy is_active string column into a real boolean — the one place the "no logic in staging" rule bends, because unit margin is a pure function of the row. Where to draw the line between "trivial derivation" and "business logic" is a live debate in dbt practice; the rule of thumb here is keep things in staging only if they're stateless and decorrelated from any business rule a stakeholder might argue about.
Four marts, each with an explicit grain and an explicit consumer.
fct_orders — grain is one row per order line. Joins customer and product dimensions onto stg_orders, then computes gross_revenue, cost_of_goods, and gross_margin. Materialized as a table because every downstream mart hits it; pay the write cost once instead of recomputing the join on every mart build. Grain is the most important decision in fact-table design. Drift to "one row per order" loses product-level analysis; drift to "one row per shipment" misrepresents revenue timing. Pick the grain, declare it in the model description, defend it.
customer_lifetime_value — historical sum of revenue and margin per customer, plus order counts, average order value, and first/last order date. Historical sum, not predicted LTV — a Bayesian LTV model (BG/NBD + Gamma-Gamma, discrete-time hazard, or any modern survival approach) belongs in a different project. The filter where order_status = 'completed' is the load-bearing decision: pending and cancelled orders shouldn't count toward LTV. When a stakeholder asks "why doesn't this match the orders dashboard", the answer is in that WHERE clause.
monthly_revenue — date_trunc('month', order_date) grouped by channel, feeding a time-series chart. date_trunc keeps the grain at one row per (month, channel) regardless of how many orders land on a given day. The temptation is to over-engineer with a date dimension table — premature for a small mart.