Module 4: Load to the warehouse · Lesson 4.7 · about 45 min
Project 4: one pipeline, any warehouse
Ship ads-warehouse: one Dagster pipeline that loads DuckDB, Postgres or Azure SQL through one resource, with free reruns, an audit trail, security, a benchmark and honest marketing numbers.
Module 4 project · about 45 minutes to read, a day to build · read first: Lessons 4.1 to 4.6. You’ll ship one Dagster pipeline for a marketing agency that loads any of three warehouses with the same code, reruns for free, records every load, keeps each client’s data apart, and shows marketing numbers that are honest about what is still changing.
1. What you are shipping
ads-warehouse, for a fictional agency with 3 clients, 4 ad channels and 24 campaigns (synthetic data from the course generator):
ad platform API (Lesson 1.6) ──► staging (currency fixed, micros converted) ──► Warehouse resource
├─ DuckDB (local)
├─ Postgres (docker)
└─ Azure SQL (cloud, Entra sign-in)
──► mart.fct_ads_daily, mart.dim_campaign (temporal), mart.dim_date ──► dashboard
“Micros” are how some ad APIs send money: millionths of a currency unit, so 1,250,000 means 1.25.
2. Marketing numbers, correctly
- CTR (click-through rate) = clicks ÷ impressions.
- CPC (cost per click) = spend ÷ clicks.
- CPA (cost per acquisition) = spend ÷ conversions.
- ROAS (return on ad spend) = conversion value ÷ spend.
All four are ratios, so the rule from Project 3 applies: add up the tops and the bottoms at the level you report, then divide. Never average ratios.
Provisional days. Ad platforms keep adding conversions to a day for about a week (someone clicks today and buys on Friday; check each platform’s own attribution settings). So the last 7 days on the dashboard are provisional: shade them, and say so in the legend. Your 7-day look-back upsert from Lesson 4.3 is what corrects them each morning.
- ROAS
- Conversion value ÷ spend: dollars back per dollar spent on ads.
- CPA
- Spend ÷ conversions: what one sale or sign-up cost.
- Provisional
- A figure that will still change as late data arrives, shown as such on the chart.
- Swappable warehouse
- One resource interface (upsert, replace_partition, query) with a class per database, chosen by a setting.
- Physical meaning
- A client manager sees display ROAS at 0.6 for the last three days and wants to pause it. The shading reminds them these days are provisional; a week later the same days read 1.1. The dashboard just saved a working campaign.
“Zero spend means ROAS is zero.” ROAS for a channel with no spend is undefined (dividing by zero), not zero. Show “n/a”, and leave the channel out of any weighting. A zero on the chart looks like a disaster that never happened.
3. Acceptance checks
| # | Requirement | Proof in the repo |
|---|---|---|
| 1 | P2D_WAREHOUSE = duckdb, postgres or azuresql switches the target; assets unchanged |
demo and a test per target |
| 2 | 7-day look-back upsert; a rerun inserts 0 rows | an audit-table query |
| 3 | ops.load_audit with inserted, updated and deleted counts |
screenshot |
| 4 | USD spend converted with a dated exchange-rate table; the original amount kept | test |
| 5 | Columnstore on the fact (Azure SQL); primary key or unique constraints everywhere | DDL in the repo |
| 6 | Roles and row-level security by client; Entra and managed identity documented | docs/security.md |
| 7 | Benchmark table across warehouses, cold and warm medians | README |
| 8 | Dashboard: spend, ROAS and CPA by channel and client; last 7 days shaded provisional | screenshot |
| 9 | Cost note with real numbers from your Azure bill, or an estimate with its arithmetic | docs/cost.md |
Maths and principle
Principle: combining ratios across groups needs a weighted average, with each group weighted by its share of the bottom of the ratio.
Equation: for channels with spend and conversion value :
The plain mean of the ROAS values is right only if every channel spent the same.
Analogy: a centre of balance: positions weighted by mass. A heavy channel pulls the total towards its own ROAS.
Where it breaks: a channel with zero spend has no ROAS. Leave it out of the weights and show “n/a”.
Try it: search spent $8,000 at ROAS 4.0; display spent $2,000 at ROAS 1.0. Total? ((32,000 + 2,000) ÷ 10,000 = 3.4, not 2.5.)
4. Build checklist
- Finish the pipeline. Staging with currency and micros fixed, the Warehouse resource for three targets,
fct_ads_daily(MERGE on date and campaign, 7-day look-back),dim_campaignas a temporal table,dim_date. - Checks. Unique (date, campaign), spend that reconciles to the API’s own page totals within a cent, and an exchange rate for every USD day: all three blocking.
- Security. Roles, row-level security by client, managed identity or Entra sign-in in Azure;
docs/security.mdsays who can do what. - Benchmark. Your table from Lesson 4.4 in the README, with one paragraph of honest interpretation.
- Dashboard. Streamlit, or Metabase on Postgres, with provisional shading. Power BI arrives in Module 6 and reuses this mart.
- Clean up Azure. Delete what you do not need, or confirm auto-pause and the budget alert. Note it in
docs/cost.md. - README. Summary, an architecture diagram showing the swappable warehouse, “run it in 5 minutes” on DuckDB, design decisions, the benchmark, security, cost, next steps.
Your story (with your own numbers): “An agency’s ad data keeps changing for a week after each day as conversions are attributed, and one client is billed in US dollars. I built one Dagster pipeline with a warehouse resource that targets DuckDB locally and Azure SQL in the cloud with managed-identity sign-in, upserting a 7-day look-back window so reruns are free. I benchmarked Postgres, DuckDB and ClickHouse on the same queries, and wrote down why Azure SQL was still the right call for a Microsoft 365 agency of this size, and what would change that.”
What done looks like: P2D_WAREHOUSE=duckdb and P2D_WAREHOUSE=postgres give identical totals on a fresh clone; the audit table shows a rerun with 0 inserted; two client managers see only their own rows; the dashboard’s last 7 days are shaded. Commit the final state as feat: project 4 ads-warehouse. Next, Module 5 models this data into a star schema; and when the data outgrows one machine, Data at Scale takes over.
- One Warehouse resource with a class per database lets the same assets load DuckDB, Postgres or Azure SQL.
- A 7-day look-back upsert makes reruns free and lets late conversions correct recent days.
- CTR, CPC, CPA and ROAS are ratios: sum tops and bottoms first; zero spend means “n/a”, not 0.
- Shade provisional days so nobody acts on numbers that are still arriving.
- Prove it: audit rows, a benchmark table, a security note and a cost note with real numbers.
Check yourself
Sources
- Dagster documentation: Resources and partitions https://docs.dagster.io/
- Kimball, R. and Ross, M. (2013), The Data Warehouse Toolkit, 3rd edition, Wiley, Chapter 1 (non-additive facts) and Chapter 19 (ETL subsystems)
- Google Ads Help: About conversion tracking and conversion delay https://support.google.com/google-ads/
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.