Ask three platform dashboards what your ROAS was last month and you'll get three answers Meta, Google, and Amazon each attribute conversions their own way, on their own windows, and each one grades its own homework. A marketing data warehouse exists to end that argument: raw data from every platform lands in one place, one set of metric definitions is applied, and every dashboard reads from the same tables. The stack that has become the default for this is dbt for transformation and Snowflake (or BigQuery) for storage and compute.
The architecture, end to end
- Ingestion scheduled pipelines pull raw data from ad platforms (Amazon Ads, Meta, Google, TikTok), web analytics, CRM, and commerce systems into raw schemas, unmodified
- Staging models dbt models that rename, retype, and lightly clean each source, one staging model per source table
- Intermediate models where the real work happens: currency normalization, attribution-window alignment, deduplication across platforms, joining spend to revenue
- Reporting marts final tables shaped for consumption: campaign performance, client rollups, executive KPIs the only layer dashboards are allowed to read
Why dbt is the piece that matters most
Most marketing data quality problems live in the transformation layer the logic that turns raw API exports into 'spend' and 'ROAS.' Before dbt, that logic lived in dashboard formulas and analysts' heads, so it silently diverged between reports. dbt makes transformations version-controlled SQL with tests and documentation: when ROAS is defined once in a tested model, every downstream dashboard inherits the same number. The test suite is the difference between a warehouse and a data swamp every metric that appears in an executive or client-facing report should have not-null, uniqueness, and reconciliation tests behind it.
Marketing-specific traps generalist builds miss
- Attribution restatement ad platforms restate the last 1–28 days of conversion data as late attributions land; pipelines must re-pull rolling windows, not just append yesterday
- Metric non-additivity you cannot sum platform-attributed conversions across platforms without double counting; the warehouse should keep platform-attributed and blended views separate and labeled
- Currency and timezone drift a global account structure mixes currencies and report timezones; normalize once, in staging, never in the dashboard
- Schema churn platforms rename fields and deprecate reports on their own schedule; contract tests on raw schemas catch the break before it corrupts the marts
Snowflake vs BigQuery and does it matter?
Less than the modeling discipline does. Snowflake's separation of storage and compute makes multi-tenant agency workloads easy to isolate and bill; BigQuery is the natural pick when a team already lives on Google Cloud. Both run dbt identically. What actually determines cost is incremental modeling rebuilding only new data instead of full-refreshing every table and that is a dbt pattern, not a vendor feature.
“The warehouse pattern is always the same: ingest raw, model in dbt, test every client-facing metric. The build that skips the tests is the one that ends up back in spreadsheets within a year.”Techesthete, on marketing warehouse builds
A realistic build sequence
Start from the weekly questions the business actually asks and model backwards: pick the two highest-spend platforms, ingest them raw, model spend and revenue into one tested mart, and wire a single dashboard to it. That first slice ships in weeks and earns the trust that funds the rest. Then add sources one at a time each new platform reuses the same staging-intermediate-mart pattern at a falling marginal cost. This is the approach behind the centralized reporting we built for BTR Media, where unifying retail media data into one warehouse drove a 20% ROAS improvement for their clients.
