Data marts done right

For anyone building reporting, or deciding whether a dashboard needs a mart at all. This page paints what “done right” looks like beside what “done wrong” looks like, then how to build one. For what a mart, a fact and a replica actually are, read How analytics data flows first.

A mart is done right when the next question is a small, boring change and every number on it is one you would defend in a review. A query that used to time out returns instantly, adding a report is one registry entry and one SQL file, and nobody has to explain why the dashboard disagrees with itself. Done wrong, it becomes a second source of truth that drifts from production, times out in new ways, and grows a bespoke pipeline per question until no one dares touch it.

The examples below are invented; the failures they show are ones most teams ship at least once.

Dos and don’ts#

• Do try an index or a materialized view in the source database first. Don’t stand up a warehouse for a report a single index would have served.
• Do give every fact one grain you can say in a sentence. Don’t mix grains in one table, that is how numbers get double-counted.
• Do bake joins into the fact overnight. Don’t join across tables live in BI.
• Do label the number as of end of yesterday. Don’t let a day-old dashboard pass for live.
• Do rebuild whole and swap in atomically. Don’t truncate-and-reload a table people are reading.
• Do decide what exists in a registry. Don’t grow a pipeline per report.
• Do keep every connection read-only or narrowly scoped. Don’t point the loader at production with an admin user.

Build one only when a live query has hit the wall#

Reach for a mart when a live query has actually blown the BI timeout and the source can’t precompute the answer in place. Not before.

Bad: “We’ll need analytics at scale eventually, so let’s stand up a warehouse now.” An instance, a nightly job, and a day-old number nobody asked for, all maintained for a dashboard a single index would have served.

Good: “The support dashboard’s cards join 525,000 rows and hit the 60-second timeout over a year window, and the source can’t hold a precomputed view, so an index won’t rescue it. That goes in a mart.”

A mart is not free: it is an instance to run, a nightly job someone has to notice failing, and a snapshot people will mistake for live. Earn it. Try an index or a nightly materialized view in the source first, and reach for a mart only when the source can’t precompute in place, when the precompute is too heavy to run beside production writes, or when a fact must join across more than one source database.

One row, one meaning#

Every fact has one grain you can state in a sentence.

Bad: one row per completed order plus an extra row per refund event, so every refunded order is counted twice in revenue.

Good: one row per completed order, with the refund as a column on that row.

Where a report needs a denominator (a cancellation rate needs all orders, not just the cancelled ones), keep every row and let the report filter.

Test: can you say what one row of this fact is in a sentence? If not, the grain isn’t decided yet.

The joins are already done#

BI reads flat, pre-joined facts and never joins across tables at query time.

Bad: a chart that joins three tables live “to stay flexible.” It re-does the join on every load and dies over a year of history.

Good: the join is baked into the fact overnight; the chart reads one flat table and returns instantly.

If a card needs a join, the join belongs in the mart, not the query. This is why a mart is fully denormalized rather than a star schema: storage is cheap, a query-time join at scale is not.

It’s a snapshot, and it says so#

Freshness is stated, not assumed: a mart is D+1, holding through the end of yesterday.

Bad: a dashboard everyone treats as live that is actually a day behind, so ops makes a real-time call on yesterday’s number.

Good: the dashboard is labelled “as of end of yesterday,” and anyone who needs “right now” uses the live ops tooling instead.

A mart answers “what happened,” not “what’s happening.” That is a feature, not a limitation: a number that holds still is what a report needs.

How to build one#

Build it in layers, and keep each layer dumb: the database stores, the SQL transforms, config decides what exists. Each step below has a pattern to follow and an anti-pattern that looks fine until it doesn’t.

1. Stage the source raw. Land each source record into a stg_* table as-is, one per source table or collection, with almost no transformation. Staging is a faithful copy, so any fact can be rebuilt from it without touching the source again. See staging and facts.

Anti-pattern: building a fact straight from the source with no staging, so the transform logic hides in the extractor and every change means a re-extract.

2. Build each fact with SQL, typed, one grain. A fact is a SELECT over staging that does the join and the flatten into a flat, explicitly typed table with a single grain. Widen a column’s type when the source can hand you junk, an impossible date overflows a narrow date type.

Anti-pattern: one big table for the whole business, a fresh fact per chart, or every column left as a string.

3. Decide what exists in a registry, not in code. Keep the list of sources and facts in one config, with each fact’s DDL and transform SQL beside it. Adding a question is a new entry, so the next report costs a config change and a SQL file, nothing else.

Anti-pattern: a bespoke extract script and cron per report. Nine pipelines in six months and nobody knows which feeds what.

4. Rebuild whole, publish atomically. Rebuild each fact into a shadow table and swap it in with an atomic exchange (ClickHouse EXCHANGE TABLES), so a reader never sees a half-built table and a failed run leaves last night’s tables intact. Full nightly reload while it fits the window; move a single fact to an incremental load only when its rebuild stops finishing before morning. One caveat: a reload reflects the source as of last night, so a value that changed in place is gone unless the fact snapshots it.

Anti-pattern: truncate-then-reload a table people are querying, so every night has a window of partial or empty data.

5. Log every run. Append a row per run with per-table counts, status and any error, so “did last night work?” is one query.

Anti-pattern: a silent job you learn has failed from a blank dashboard at 9am.

6. Scope every credential. The loader reads a read-only replica and writes only to the mart’s own database; BI reads the mart read-only.

Anti-pattern: pointing the loader at production with an admin user “to keep it simple.”

When this does not apply#

The test for all of these is one question: does the query return within the BI timeout against the replica, at the window the report actually needs? If yes, it stays on the replica. If no, and the source can’t precompute the answer in place, it goes to the mart. Measure it, don’t guess.

• “What is happening right now” is not a mart question. A mart is a day behind by design. Use live operational and observability tooling for current state.

• Light, recent, single-table questions don’t need a fact. Read the replica directly.

• A cheaper fix often wins. An index or a nightly materialized view in the source database answers many slow reports without a mart at all.

• Sub-day freshness is a per-dataset change (an incremental or streaming load for that one fact), not a bigger nightly job for everything.

  Test: name the decision someone makes tomorrow morning from this number. If there isn’t one, D+1 is enough.

We have applied this pattern on client engagements where reporting outgrew the live database. It is the standard when analytics stops fitting in a query, not a reason to stand up a warehouse before the first report has hit that wall.