Data Marts
A narrow, purpose-built, usually pre-aggregated serving copy that trades flexibility and freshness for query cost and simplicity.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
When is it worth materialising a narrower copy of a model, and what does every consumer of that copy lose?
A specific, named group with a stable question: the finance team closing a month, an executive dashboard refreshing every few minutes for eighty viewers, an embedded customer-facing report. A mart is defined by *whose* question it answers, and a mart without a named consumer is a table nobody is responsible for (Who Actually Consumes This Data).
Deliberately coarser than the model it derives from — one country-day, one customer-month, one product-week. That coarsening is the whole point and the whole danger: the mart cannot answer anything below its grain, and joining it back to detail double counts (Grain: What Does One Row Represent?).
Let every dashboard query the wide fact table directly. It is one source of truth, one definition, nothing to keep in sync, and for a model queried a few times a day by a few analysts it is unambiguously the right answer (Fact Tables).
Eighty dashboard tiles refresh on a schedule, each scanning a year of the fact table to compute the same four aggregates. The scan cost is paid eighty times for one answer (Scan Cost).
- Eighty dashboard tiles refresh on a schedule, each scanning a year of the fact table to compute the same four aggregates. The scan cost is paid eighty times for one answer (Scan Cost).
- The BI tool re-implements the metric slightly differently in three places, so three tiles that should agree do not, and each is defensible in isolation (Two Dashboards, Two Numbers).
- A customer-facing report has a latency budget the fact table cannot meet, because answering it means scanning far more rows than the answer contains (Latency Budgets: Spending 200 Milliseconds on Purpose).
- An analyst joins the pre-aggregated revenue table back to order lines to add a product filter, re-aggregates, and every revenue figure is multiplied by the line count (Duplicate Rows).
- Someone patches the mart directly during an incident to make a number right for a board meeting, and from that day the mart and its parent model disagree with no record of why (Where Did This Number Come From?).
- The mart refreshes hourly and the model it derives from refreshes every fifteen minutes, so two dashboards show different numbers and both are correct for their own freshness (Stale Dashboards).
What is actually happening
- A mart is a serving decision, not a modelling decision. The modelling happened upstream in the fact and dimension tables; the mart selects a subset, applies a fixed set of filters, and pre-computes aggregates at a declared grain (Star Schema).
- What it buys is that the expensive work happens once per refresh rather than once per query. If a query pattern is stable and frequently repeated, materialising its result converts many large scans into one (What Actually Drives Data Platform Cost).
- What it costs is generality. Every dimension not in the mart is a question it cannot answer, and every aggregation is irreversible — you cannot recover the detail from the sum (Analytical Data Modeling).
- It also adds a hop, and a hop adds latency and a place to become stale. A mart is always at least as stale as the model it derives from, and consumers reading it rarely know how much (The Fundamental Data Journey).
- A materialised view is the same idea expressed by the database rather than by your pipeline, and the trade-offs are identical: it is a maintained copy with its own refresh semantics and its own staleness (The Data Warehouse).
- The organisational variant — a mart per department — is a governance and ownership structure as much as a technical one, and it is how "the finance number" and "the marketing number" come to differ (The Metrics Layer).
The grain gets coarser, and that is irreversible
Everything a mart gives you and everything it takes away comes from one operation: it aggregates. The rows get fewer, the queries get cheaper, the columns get simpler — and the detail is gone, not hidden. That asymmetry is why the grain of a mart is its most important documented fact.
The table below tracks one order from the fact table to a tile. Notice that two separate coarsenings happen, and that after each one a specific class of question becomes unanswerable from that table. Nothing warns a consumer about this; the mart just quietly cannot answer, or worse, answers something adjacent.
The breaksIf column is the practical part. Almost every wrong number produced by a mart traces to one of those rows, and the most common by a wide margin is joining the aggregate back to detail — because it runs, it returns rows, and every measure is silently multiplied (Duplicate Rows).
| Stage | One row is | Breaks if |
|---|---|---|
| `fct_orders` | One order, with measures and dimension keys at order grain. | It is joined to fct_order_lines without aggregating first, which multiplies every order-level measure by the number of lines (Fact Tables). |
| `fct_orders` joined to `dim_customer` | Still one order, now with customer attributes attached. | The dimension has duplicate keys — an SCD2 table read without a validity filter returns every historical version of the customer (SCD Type 2 in Practice). |
| `mart_revenue_country_day` | One country-day, with revenue and order count pre-summed. | Anyone asks for revenue by product. The mart has no product dimension and no way to acquire one without a rebuild of history (Backfills). |
| The same mart joined back to `fct_orders` | Nothing coherent — one country-day repeated once per order in it. | It is aggregated again. SUM(revenue) now counts each country-day's revenue once per order, producing a number that is large, plausible and wrong. |
| Dashboard tile | One number, with the grain and the refresh time both invisible. | The BI tool applies its own filter or join after the mart, changing the metric after every upstream check has already passed (Two Dashboards, Two Numbers). |
Two coarsenings, two irreversible losses. A mart is a projection, and no query against a projection can recover what the projection dropped.
When a mart earns its keep
The decision is narrower than it looks. A mart pays for itself when one stable, expensive query pattern is run many times between refreshes. Remove any one of stable, expensive or repeated and the arithmetic stops working, because you are now paying refresh cost for a saving you do not collect.
The options below are the real alternatives, and the middle ones matter most — the choice is rarely "mart or no mart". Caching results, clustering the parent table, or letting the engine maintain a materialised view all address the same problem with different amounts of new surface area to own.
The question to ask before any of them is whether the pattern is genuinely stable. Marts built for an exploratory question calcify it: six months later the mart exists, the question has moved on, and there is a table nobody dares delete because eight dashboards reference it (Data Platform Anti-Patterns).
What property of the workload are you actually trying to change?
when Few consumers, ad-hoc questions, dimensions still changing, or the query is run a handful of times a day.
cost None, and you keep one definition and full flexibility. Revisit when the same aggregate is computed dozens of times between any two changes to the data.
when The query scans far more than it needs because partitioning or clustering does not match the predicate.
cost A rewrite of the table and possibly a backfill. Buys a saving for every query against it, not only the one you were looking at (Clustering and Sort Order).
when Many consumers run the identical query between data changes.
cost Almost nothing, and it evaporates the moment the query text or the underlying table changes. Buys the cheapest possible win where it applies (Caching as a Contract Clause).
when The engine can refresh incrementally and route queries to it automatically.
cost Refresh compute and engine-specific limits on what can be materialised. Buys the mart trade without a second table for consumers to misuse.
when A stable pattern, many consumers, a latency budget the parent cannot meet, or a customer-facing serving path.
cost A second copy with its own refresh, staleness, reconciliation and ownership. Buys predictable serving latency and a large query-cost reduction.
when The latency budget is sub-second at high concurrency, which analytical engines are not built for (Latency Budgets: Spending 200 Milliseconds on Purpose).
cost A different system entirely, with its own sync, its own failure modes and its own on-call. Buys a serving profile a warehouse does not have (Data Engineering and Backend Engineering).
Reading a wrong mart number backwards
When a mart shows the wrong number, there are only four candidate causes and they need entirely different fixes. The mart is stale. The mart has drifted from its parent. The parent is wrong. Or the mart is being read at a grain it does not support. Establishing which one first is the whole of the debugging technique.
The chain below is the walk, upstream, with what each node can corrupt. It is short — that is the advantage of a mart with a clean derivation — and each node has one question attached: is the affected period complete and correct *here*?
The node worth dwelling on is the second. A mart that was patched by hand is the one failure with no signal at all: it reconciles against nothing, its transformation no longer reproduces it, and the only evidence is a memory. That is the argument for making rebuild-not-patch a rule rather than a preference (Idempotent Data Pipelines).
- Dashboard tile
holds One number plus filters and possibly joins defined inside the BI tool.
could corrupt A filter or join added in the BI layer that no model, test or lineage tool can see (Two Dashboards, Two Numbers).
↑ reads from - `mart_revenue_country_day`
holds One row per country-day, revenue pre-summed, refreshed on its own schedule.
could corrupt Staleness since the last refresh; drift from a direct patch; an aggregation at a grain the consumer did not expect.
↑ reads from - Mart transformation
holds The SQL that derives the mart from the model, plus its filters.
could corrupt A filter that excludes a status the model includes; a metric definition that no longer matches the metrics layer (The Metrics Layer).
↑ reads from - `fct_orders` + `dim_customer`
holds One row per order with dimension keys resolved.
could corrupt A fan-out join against a dimension with duplicate keys; a late-arriving dimension member resolving to an unknown bucket (Dimension Tables).
↑ reads from - Staging models
holds One row per order, cleaned and typed from raw.
could corrupt Deduplication choosing the wrong record as latest; a cast that nulls silently (Deduplication).
↑ reads from - Raw landing
holds Everything delivered, exactly as received.
could corrupt Missing or duplicated deliveries — an ingestion problem, not a modelling one (The Raw Landing Zone).
Four candidate causes, in order of likelihood: stale, drifted, misread grain, genuinely wrong upstream. Check freshness and reconciliation first — they are two cheap queries and they eliminate half the tree (Where Did This Number Come From?).
How to build it
Most important first.
- Build a mart only for a query pattern that is stable, repeated and expensive. Two of the three is not enough: a stable expensive pattern queried twice a week is cheaper to run twice a week (Cost vs Freshness).
- Derive it from the modelled layer, never from raw and never from another mart. Chains of derived copies multiply staleness and make the lineage unreadable (Model Layering).
- Declare the grain in the table name and in its documentation, because the grain is the mart's contract and the most common way it is misused is by someone who assumed a different one (Dataset Documentation).
- Define the metric once in a metrics layer and have the mart materialise that definition, so the mart is a performance artefact rather than a second definition (The Metrics Layer).
- Rebuild rather than patch. A mart that can only be produced by re-running its transformation is a mart that cannot silently diverge from its parent (Idempotent Data Pipelines).
- Reconcile the mart against its parent model on every refresh — the totals must match for closed periods, and the check is cheap because both sides are already aggregated — and publish the resulting freshness alongside the data, so a consumer can see how far behind the model they are reading without asking anyone (Reconciliation, Freshness Monitoring).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- The mart guarantees exactly what its transformation computed, at the moment of its last refresh, and nothing about the state of the model since (Atomic Publish).
- It guarantees consistency *within itself* if published atomically — no consumer sees a half-refreshed mart — which is a property you have to build, not one you get.
- It guarantees nothing about agreement with its parent unless a reconciliation asserts it. Two correct pipelines producing the same metric will drift the moment one changes (Data Contracts).
- It explicitly does not guarantee that its numbers can be decomposed. A sum at country-day grain cannot be broken down by product, and attempting it produces a plausible wrong answer rather than an error.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The mart-specific check is a parent reconciliation: for each closed period, the mart's summed measure equals the same aggregate computed directly from the parent model. Cheap, decisive, and it catches drift, patching and a filter that changed on one side only (Reconciliation).
- It misses grain misuse by consumers entirely. A perfectly reconciled mart that gets joined back to detail produces wrong numbers downstream and the mart is blameless.
- It also misses a definition change applied to both sides at once — if the metric changed upstream, the mart and the model agree beautifully on the new, different meaning (Semantic Changes).
- A mart is always behind its parent by at least its own refresh interval, and consumers experience the sum of both hops (The Fundamental Data Journey).
- That lag is invisible in every BI tool by default. The number renders with the same confidence whether it is four minutes or four days old, which is why freshness must be published as a column or a tile rather than assumed (The Freshness SLO).
- Making a mart fresher costs refresh compute proportional to its frequency, and past a certain point the mart costs more than the queries it was built to avoid — which is the signal to delete it (Cost vs Freshness).
- Adding a dimension to a mart is a rebuild of its history, because the aggregate was computed without it and cannot be decomposed after the fact (Backfills).
- Changing the grain is not an evolution, it is a new mart. Consumers depend on the grain in ways that no schema check can see, and silently coarsening or refining it changes every number (Breaking Schema Changes).
- A metric definition change upstream must propagate to every mart that materialised it, and the marts are exactly the places where an old definition survives longest (Impact Analysis).
- A mart should always be reproducible from its parent by re-running its transformation. If it is not — because it was patched, or because it depends on state it does not read — it is not recoverable and it is not trustworthy (Idempotent Data Pipelines).
- Rebuild into a shadow table, reconcile against the parent, then swap. A mart is the last hop before a human reads the number, so a bad rebuild reaches the business immediately (Atomic Publish).
- Backfilling a mart after fixing an upstream bug means recomputing the affected periods only, which is straightforward precisely because the mart is a pure function of the model (Planning a Backfill).
What can go wrong
- Silent divergence from the parent model after a direct patch, with no record that the patch happened.
- A consumer joining the mart back to detail and multiplying every measure (Duplicate Rows).
- A refresh that fails silently, leaving yesterday's mart in place and a dashboard that looks entirely normal (Stale Dashboards).
- Mart proliferation: a table per dashboard, each with a slightly different filter, none owned, none deletable because nobody can prove nothing reads them.
- The mitigation failing: a reconciliation that compares the mart to the parent using the mart's own logic, so a bug in the logic reconciles perfectly against itself.
- "A mart is a smaller warehouse." It is a *serving copy* of modelled data. The modelling decisions were already made upstream; a mart that does its own modelling is a second source of truth wearing a performance justification (Source of Truth).
- "Pre-aggregating is always cheaper." It is cheaper per query and costs a refresh. Below some query frequency the refresh dominates, and marts built for dashboards nobody opens are a common and invisible waste.
- "We can always drill down from the mart." You cannot. Aggregation is lossy — the detail is in the parent, and the join back to it is the classic double-counting bug (Grain: What Does One Row Represent?).
- "The mart is wrong." Usually it is stale, or it is being read at the wrong grain. Establish which of the three before changing anything, because the fixes are entirely different (Where Did This Number Come From?).
- "Every team should have its own mart." Per-team marts are how one metric becomes four. They are a legitimate ownership pattern only when they all materialise one shared definition (Data Mesh).
- A mart is often the layer where access can be widened safely, because aggregation removes the row-level detail that carried the sensitivity — but only if the grain is genuinely coarse enough that individuals cannot be reidentified (Data Minimization).
- It is also where a deletion obligation can be quietly missed: removing a person from the fact table does not change a mart that was aggregated before the deletion, and nothing will flag it (Deletion Requests).
Operating it
- Refresh success and freshness lag per mart, published where the consumer can see it rather than only in the orchestrator (Freshness Monitoring).
- Reconciliation delta against the parent model per closed period, trended. A slow drift is far more common than a sudden break and only a trend shows it.
- Query volume per mart. A mart with no queries is pure cost and is safe to delete; a mart with enormous query volume justifies more freshness (Cost Attribution).
- Bytes scanned by consumers before and after introducing the mart, which is the only evidence that it did what it was built to do (Scan Cost).
- At 10x consumer count the case for a mart strengthens sharply, because the saving multiplies by the number of readers while the refresh cost stays fixed.
- At 10x data volume the refresh itself becomes the problem, and the mart must move from full rebuild to incremental — the same migration every large table eventually makes (Full Refresh vs Incremental).
- At high dimension counts the mart's row count grows as the product of its dimensions' cardinalities, so a mart at too fine a grain across too many dimensions can be larger than the fact table it summarises (Partition Cardinality).
- A mart trades query cost for pipeline cost and storage. That is a good trade when the same aggregate is computed many times between refreshes, and a bad one when it is computed rarely (Compute Waste).
- Refresh cost scales with frequency and with how much of the mart must be recomputed. An incrementally refreshed mart costs proportional to the change; a full rebuild costs proportional to history (Incremental Processing).
- Storage for the mart is usually negligible next to the fact table, because aggregation is a large reduction — which is exactly why the query saving can be large too.
- The cost nobody counts is the maintenance of a second definition, paid in engineering time and in the meetings where two numbers are reconciled by hand (The Metrics Layer).
- You buy fast, cheap, predictable answers to one question and you give up every question below the grain. That is a good trade only when you are confident the question is stable.
- You buy simplicity for the consumer — a small table with obvious columns — and you take on a second copy that can drift, go stale, and be patched.
- You buy latency headroom for a serving path and you add a hop of staleness that no consumer can see unless you publish it.
Mart grain lab
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
- · Total revenue over time
- · Revenue by country
- · Anything by channel
- · Anything by product
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe trade — flexibility and freshness for query cost and simplicity — is a property of materialising any derived aggregate, whether it is called a mart, a materialised view, a rollup table or a BI extract.
- WAREHOUSE-SPECIFICSome engines can maintain materialised views incrementally and rewrite queries to use them automatically, which removes the consumer-facing part of the trade; others require you to build and refresh the copy yourself and to route queries to it by hand, which leaves the drift risk entirely with you.
- ORG-SPECIFICDepartment-owned marts are an ownership pattern, not a technical one: they work where every mart materialises one shared metric definition, and they produce competing numbers where each team is free to define its own.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns how a mart's refresh is deployed and rolled back, and why "patch the table by hand during the incident" is the same anti-pattern as editing production config by hand.