Marketing data warehouse: the smallest one that actually works
"We should build a marketing data warehouse" is usually the right instinct followed by the wrong project. The instinct is correct: your numbers live in six systems that disagree, and someone re-derives them by hand every Monday. The project that follows — a cloud warehouse, an ELT vendor, a transformation repo, a BI tool on top — solves a scale problem most marketing teams do not have, and adds a maintenance surface they definitely cannot staff. This is a piece about which parts are load-bearing and which are cargo cult.
What it actually is
A marketing data warehouse is one store holding data from every channel — ad platforms, web analytics, search, sometimes CRM and email — with consistent definitions across sources, refreshed automatically. That's the whole idea. It exists so "what did we spend, and what did we get" is answered once, from storage, rather than re-derived per platform in a spreadsheet by whoever is free.
Everything else people bolt on is implementation, and implementation is negotiable.
The five jobs it has to do
Strip the vendor logos away and any working setup does exactly these five things. If a proposed stack skips one, it will fail in a predictable way.
1. Ingest, incrementally and unattended
Every source needs authenticated, scheduled, incremental pulls — plus the ability to backfill history when you first connect. This is boring and it is where most DIY attempts die: OAuth refresh, pagination, rate limits, and the fact that ad platforms silently revise yesterday's numbers. If a human has to click Export for any source, you do not have a warehouse. You have a habit.
2. Normalization into shared definitions
The load-bearing job. Meta's purchase value, Google Ads' conversion value, and GA4's revenue are three different measurements of three different populations. If you land them raw and hope the chart reconciles them, the chart will produce a confident, wrong number. Normalization has to happen on ingest: one definition of cost, clicks, conversions and conversion value, applied as data arrives — so that later, no matter who asks or how, the answer matches.
3. Lossless raw underneath
Normalization throws away nuance, and sometimes you need the nuance. TikTok video views and profile visits have no cross-channel equivalent; Search Console reports query-level rows nothing else does. A warehouse that only keeps the unified layer forces you back to the platform UI the first time someone asks a forensic question. Keep both altitudes over the same data.
4. Durable, queryable storage
The part people over-engineer. Be realistic about size: daily aggregates per campaign, ad group and ad, for a handful of accounts, across three years, is millions of rows. Query-level Search Console data is the biggest table you will have and it is still comfortably in single-node territory. Columnar files plus an embedded analytical engine handle this without a cluster.
5. Honest freshness
The one every architecture diagram forgets. Sources lag differently and lie differently. Search Console finalizes roughly two to three days late — this is normal, not an outage. Meta re-attributes conversions over a trailing window, so yesterday's ROAS changes next week. A warehouse that reports one "last updated" timestamp for the whole thing is hiding the number that matters, and reports built on it arrive on time and wrong.
Test for a proposal: ask which of the five it covers and which it leaves to you. "Warehouse plus BI tool" covers 4 and half of 3 — you still own 1, 2 and 5. That's where the headcount goes.
Why the classic stack is oversized here
The BigQuery + Fivetran + dbt + Looker pattern is genuinely excellent, and it was designed for a company with billions of event rows, dozens of analysts, and a data team to run it. Applied to a marketing team of three it inverts: warehouse and ELT bills that scale with connectors and rows, a transformation repo that needs someone fluent enough to maintain models, and a BI layer billing per human. You have bought five solved problems and one new full-time job.
There's a second, subtler mismatch. That stack assumes the consumer of the data is a person opening a BI tool. Increasingly it isn't — it's an agent, at a terminal, that would rather have SQL and a schema than a rendered chart.
What the small version looks like
Keep all five guarantees, delete the parts that exist for scale you don't have. That's the shape of TableBI: connect each source once via OAuth, and it syncs on a schedule into two altitudes — unified cross-channel definitions, and lossless per-platform raw tables underneath.
npm i -g @tablebi/cli tablebi login tablebi install # job 1: ingest. one OAuth round-trip each, then unattended tablebi connect gsc --site sc-domain:example.com tablebi connect ga4 tablebi connect google_ads tablebi connect meta_ads # anything without a connector lands as CSV, normalized the same way tablebi connect csv --file tiktok-export.csv --platform tiktok_ads
Under the hood, facts live as per-tenant Parquet on object storage with a DuckDB engine over the full history; Postgres holds metadata, encrypted credentials and a hot recent window so common reads stay fast. That's job 4, without a cluster to operate.
Jobs 2 and 3 show up when you query. Cross-channel questions hit the unified layer, where definition macros mean the arithmetic is derived once rather than re-invented per question:
# unified altitude — one definition of spend and return tablebi ask "SELECT platform, SUM(cost) AS spend, roas(SUM(conversion_value), SUM(cost)) AS roas FROM facts WHERE date >= (SELECT MAX(date) FROM facts) - 28 GROUP BY platform ORDER BY spend DESC"
Forensic questions drop to raw, where each platform's own fields survive intact:
# raw altitude — query-level rows only Search Console has tablebi ask "SELECT query, SUM(impressions) AS imp, AVG(position) AS pos FROM search_console_raw WHERE date >= (SELECT MAX(date) FROM search_console_raw) - 28 GROUP BY query ORDER BY imp DESC LIMIT 20"
Job 5 is not a separate tool — it rides along. Every answer carries a trust block with per-source freshness and the caveats that keep an analysis honest, and you can check the state of the whole warehouse in one call:
tablebi sources # connected sources + how stale each one is tablebi pending # what's stale or not connected yet tablebi context --json # definitions + sources + freshness, for the agent
The consumer is an agent, not a dashboard
This is the part that makes the small version viable rather than merely cheaper. A warehouse normally needs a BI layer because humans cannot read Parquet. But if the thing asking questions is Claude Code, the interface it wants is a schema and read-only SQL — which the warehouse already has. tablebi install plants a skill so the agent knows the commands exist; tablebi schema, values and sample let it interrogate the data before querying it, with bounded output that's safe to read into context.
And when an analysis is worth keeping, it becomes a live URL rather than a screenshot:
tablebi pin --title "Cross-channel overview" \ --widget "Daily clicks (28d)::line=SELECT date, SUM(clicks) AS clicks FROM facts …" \ --widget "Spend by platform=SELECT platform, SUM(cost) AS spend FROM facts …" ✓ published → https://dk.tablebi.com/d/dsh_… (public, read-only, self-refreshing)
Here's one running on live Search Console data across a portfolio of sites — pinned exactly that way, re-running as the sources sync: