Medallion · SQL analytics case study
DuckDB medallion: raw → staging → marts → readouts
DuckDB medallion for the SQL analytics case study
A three-tier DuckDB medallion in which seeded raw tables and a real retail parquet land in a bronze zone, dbt staging views clean and test them into a silver zone, and dbt marts answer funnel, retention and MRR questions in the gold zone; three metric readouts then surface a 78% add-to-cart-to-checkout drop, D1-to-D30 retention falling from 19.5% to 4.2%, and a 3.5% repeat-buyer rate.
STAGE + TEST
MATERIALIZE
FCT_FUNNEL
FCT_RETENTION
SQL CASE 19
BRONZE
Raw landing
main · duckdb
Source
generate_data.py · seed 42
Tables
users · events · orders · subscriptions
Checks
byte-identical re-runs · CI green
REAL-DATA CASE 26
UCI Online Retail II parquet
1,067,371 real invoice lines
SILVER
Staging views
staging · dbt
Source
dbt view over source()
Tables
stg_users · stg_events · stg_subscriptions
Checks
17 tests inside dbt build
CLEANING LIVES HERE
typed · de-duplicated · keyed
relationships enforced
GOLD
Marts
marts · dbt
Source
dbt table materialization
Tables
fct_funnel · fct_retention · fct_mrr
Checks
golden answers · cases 01 · 02 · 14 · 19
WHAT THE MARTS ANSWER
where the funnel leaks
how cohorts decay
FUNNEL
78% drop
case 01 · add-to-cart → checkout
the single biggest leak
RETENTION
19.5% → 4.2%
case 02 · D1 → D30 · 5.0% mature
the leak is the onboarding window
REPEAT
3.5%
case 19 · 896 buyers · 31 repeat
a one-and-done purchase engine
LEGEND
BRONZE · RAW LANDING
SILVER · STAGING VIEWS
GOLD · MART (FOCAL)
METRIC READOUT
MUTED = PROMOTION · READOUT
ACCENT = INTO FOCAL