How analytics data flows

This is the map of how reporting data gets from a live application to a chart, and the vocabulary the rest of the Analytics docs use. It is for anyone who reads or reasons about a dashboard, not only engineers. If you are deciding where a dashboard’s data should come from, read Data marts done right next; this page is the concepts underneath that decision.

OLTP and OLAP are different jobs#

A production database is built to serve one record fast: fetch this order, update this account, insert this ticket. That is OLTP, online transaction processing, and an application database like MongoDB or PostgreSQL is tuned for it.

Reporting is the opposite shape. It scans many rows, joins across tables, and aggregates a long stretch of history into one number. That is OLAP, online analytical processing. The two workloads want different storage and different indexes, and heavy reporting run on the live application database competes with the transactions the app is trying to serve. Point reporting straight at it and every report is also pinned to the application’s exact table shape.

The mistake is treating one database as good at both. There is a step before reaching for a second one: a slow report is often a missing index or a nightly summary table in the same database, not a reason to build a warehouse. When even that isn’t enough, or the source can’t precompute in place, the work moves off the application database. That decision is Data marts done right.

Two paths from production to a chart#

Everything starts from a single read-only copy of production, never production itself, and then splits into two paths.

 Production database  (OLTP: MongoDB / PostgreSQL)
        │  continuous sync, read-only
        ▼
 Read-only replica ───────────────────────►  BI (Metabase)     PATH A: light, recent questions
        │
        │  nightly ELT: land raw, then join + flatten in SQL
        ▼
 OLAP mart  (ClickHouse: staging → facts) ──►  BI (Metabase)    PATH B: joins across tables, full history

Path A, the replica. Most charts query a read-only replica of production directly. The replica is kept continuously in sync, seconds behind production, and the BI connection to it is read-only. Production is never queried by the BI tool. This path is enough for light, recent, single-table questions.

Path B, the mart. When a question joins across tables or scans a lot of history, doing it per query is too slow, and past the BI tool’s timeout it fails outright. So the join and the scan are done once, ahead of time, into an OLAP mart, and the BI tool reads the finished result. When to take this path is Data marts done right.

A data mart is flat facts, joined ahead of time#

A data mart is an analytical database that is narrow in scope, one subject area’s questions rather than the whole business, whose tables are shaped for the questions rather than for the application. Instead of the app’s normalized tables that you join at read time, a mart holds fact tables: wide, flat tables where the joins are already done and one row is one real-world thing, a completed order, a payment, a support ticket.

The textbook shape is a star schema: a central fact table of measures, surrounded by separate dimension tables you join to at query time. We deliberately go one step further and fully denormalize, folding the dimensions into the fact so a query has nothing left to join. This is sometimes called one big table. It trades storage and some duplication for the one thing the read-side rule demands: zero joins at read time, so a year-long scan is a fast read of one flat table.

Staging is raw, facts are query-ready#

A mart has two layers, and keeping them separate is what makes it cheap to change.

• Staging (stg_*) holds the raw source records, landed as-is with almost no transformation. It is a faithful copy of what came out of the source database.
• Facts (fact_*) are built from staging by SQL that does the join and the flatten. They are typed, flat, and each has one documented grain: a single, stated answer to “what is one row of this table?”

Two things follow from keeping staging raw. Adding a field a report needs is a change to the SQL that builds the fact, not a re-extraction from the source. And the pipeline is ELT, not ETL: you extract, load the raw data first, then transform it inside the warehouse, rather than transforming on the way in.

One tool per job#

We use one tool per layer, each doing the job it is built for.

ClickHouse is columnar: it stores each column separately, so an aggregation that reads one or two columns across millions of rows touches only those columns. A row-oriented application database reads every page holding those rows unless a covering index exists, and building one covering index per report is the cost the mart removes. Metabase sits on top and treats both the replica and the mart as ordinary databases to query.

The numbers are a snapshot, not live#

Both paths are snapshots, not a live feed. The replica is seconds behind production; the mart is rebuilt once a night, so it holds through the end of yesterday, not today. That is by design: analytics answers “what happened,” and a stable daily number is more useful for a report than one that shifts while you read it.

For “what is happening right now,” neither path is the answer. Use your live operational and observability tooling for current state, and treat the analytics stack as the record of what already happened.