Pipeline to Dashboardnihalsulaiman.com

Week 4 · Load to the warehouse › Day 28

Day 28. Proof 4: ads-warehouse, one pipeline, any warehouse

⏱ 4h · build 150m · README 60m · review 30m🏷 marketing agency (3 clients, 4 channels, 24 campaigns)Before: Days 22 to 27

By the end of today you can

  • Ship a Dagster pipeline that loads Azure SQL or Postgres through one resource
  • Show idempotent upserts, audit, security and cost evidence
  • Present a marketing performance dashboard with honest provisional labels

1. What you're shipping

ads-warehouse: API ingest (Day 6) → staging (currency, micros fixed) → warehouse load via a swappable resource → mart.fct_ads_daily, mart.dim_campaign (temporal), mart.dim_date → dashboard. Runs locally on DuckDB/Postgres and in Azure SQL with Entra auth.

Acceptance criteria

# Requirement Proof
1 P2D_WAREHOUSE = duckdb, postgres or azuresql switches target, same assets demo + test
2 7-day look-back upsert; rerun = 0 inserts audit table query
3 ops.load_audit with inserted/updated/deleted counts screenshot
4 USD spend converted with a dated FX table; original kept test
5 Columnstore on the fact (Azure SQL), PK/unique constraints DDL in repo
6 Roles + RLS by client; Entra/managed identity documented docs/security.md
7 Benchmark table across warehouses README
8 Dashboard: spend, ROAS, CPA by channel and client; last 7 days marked provisional screenshot
9 Cost note with real numbers from your Azure bill (or estimate) docs/cost.md

2. Marketing metrics, correctly

  • CTR = clicks / impressions. CPC = spend / clicks. CPA = spend / conversions. ROAS = conversion value / spend.
  • All are ratios: compute from sums at the reporting grain (Day 21's rule).
  • Show provisional shading on the last 7 days; conversions there will still rise.
Maths and principle

Principle: a weighted average is the only correct way to combine ratios across groups.

Equation: \(\text{ROAS}_{\text{total}} = \dfrac{\sum_c V_c}{\sum_c S_c} = \sum_c w_c \, \text{ROAS}_c\) with \(w_c = S_c / \sum S\). The simple mean of ROAS is wrong unless spends are equal.

Analogy: centre of mass: positions weighted by mass. Where it breaks: if a channel has zero spend, its ROAS is undefined; exclude it from weights and show "n/a", never 0.

Try it: search spent $8,000 with ROAS 4.0; display spent $2,000 with ROAS 1.0. Total ROAS? (32,000 + 2,000 = 34,000 / 10,000 = 3.4, not 2.5.)

Your story

"An agency's ad data changes for a week after each day as conversions are attributed, and one client is billed in USD. I built one Dagster pipeline with a warehouse resource that targets DuckDB locally and Azure SQL in the cloud with managed-identity auth, 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 at this size, and what would change that."

Build checklist

  1. Finish the pipeline and checks (uniqueness blocking; spend reconciles to API page totals; FX rate present for every USD day).
  2. Dashboard (Streamlit or Metabase on Postgres; Power BI comes next week and will reuse this mart).
  3. README with architecture diagram showing the swappable warehouse.
  4. Clean up Azure resources you don't need, or confirm auto-pause and budget alerts. Note it in docs/cost.md.

Check yourself

1. Search: spend $6,000, value $18,000. Social: spend $4,000, value $4,000. Total ROAS?

2. Why shade the last 7 days as provisional?

My notes