All lessons

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.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

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.

One recipe, any kitchen: the pipeline says "upsert these rows", and a swappable warehouse resource decides whether that means DuckDB on a laptop or Azure SQL in the cloud.

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.

Two channels. Search spent $8,000 with ROAS 4.0. Display spent $2,000 with ROAS 1.0.
The tempting answer. (4.0 + 1.0) ÷ 2 = 2.5. This counts display's $2,000 as much as search's $8,000.
Back to values. Search value = 8,000 × 4.0 = $32,000. Display value = 2,000 × 1.0 = $2,000.
Divide the totals. Total ROAS = 34,000 ÷ 10,000 = 3.4. Every dollar spent brought back $3.40, not $2.50.

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 c with spend Sc and conversion value Vc:

ROAStotal=∑cVc∑cSc=∑cwcROASc,wc=Sc∑kSk

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

  1. 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_campaign as a temporal table, dim_date.
  2. 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.
  3. Security. Roles, row-level security by client, managed identity or Entra sign-in in Azure; docs/security.md says who can do what.
  4. Benchmark. Your table from Lesson 4.4 in the README, with one paragraph of honest interpretation.
  5. Dashboard. Streamlit, or Metabase on Postgres, with provisional shading. Power BI arrives in Module 6 and reuses this mart.
  6. Clean up Azure. Delete what you do not need, or confirm auto-pause and the budget alert. Note it in docs/cost.md.
  7. 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

1Search: spend $6,000, value $18,000. Social: spend $4,000, value $4,000. What is the total ROAS?
2Why shade the last 7 days as provisional on the dashboard?
3Rerunning the last 7 days of the look-back load should insert how many rows?

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.