Case: An Analytics Dashboard
Metric, Source, Aggregation, Freshness, Visualization. "We need a dashboard" is a request for a chart; the requirement is a metric someone will act on, with a definition, a source and a freshness that decide whether the first version is a query against production, a nightly summary table, or a pipeline into a warehouse.
The situation, the reflex, and why it stalls
Every lesson starts where being stuck starts: someone has a problem, and the first move that comes to mind feels like progress.
The founder wants "a dashboard for the store". How do you find the metric behind the chart, and which of its properties — definition, source, aggregation, freshness — decides how much data engineering the first version needs?
The request arrived as a screenshot of someone else's dashboard with "like this" written on it. You could build the charts. What you cannot say is which numbers the founder will look at, what they will do when a number moves, or whether "orders today" should mean created, paid or fulfilled — and every one of those changes what you build.
Pick the charting tool and connect it to the production database. Every dashboard tool can do that in an afternoon, the charts look like the screenshot, and the numbers are real because they come straight from the tables that hold them.
The chart shows "orders" and two people read two different numbers from it: one counts orders created, one counts orders paid. The dashboard has no definition, so it cannot be wrong, so it cannot be trusted.
- The chart shows "orders" and two people read two different numbers from it: one counts orders created, one counts orders paid. The dashboard has no definition, so it cannot be wrong, so it cannot be trusted.
- The queries run against production, and the first heavy aggregation on a busy afternoon slows checkout. The dashboard has become a load on the system it measures.
- Nobody said how fresh the numbers must be, so the tool refreshes every minute because it can, and the founder plans a week from a number that only needed to be right by the morning.
- The charts are built from the screenshot, not from a decision. When the founder asks "so what should I do about this?", the dashboard has no answer, because no chart was built to inform one.
The move
Precisely enough to apply it to a problem you have never seen — not a slogan.
- Find the metric behind the chart by asking what decision it informs. "Orders today" is a chart; "are we selling enough to keep the ad spend on?" is a decision, and it needs a precise metric — paid orders, by day, attributed to the campaign — with a definition someone will sign (What Am I Actually Trying to Achieve?).
- Decompose every metric into five properties: Metric (the definition, including which order states count), Source (the tables or events it comes from), Aggregation (the grain — per day, per campaign — and the function), Freshness (how stale it may be before the decision is wrong), Visualization (last, and smallest). The first four decide the architecture; the fifth is the screenshot.
- Let freshness and load decide the first version. A metric that may be a day stale, over a table the production database can aggregate without noticing, is a nightly query into a summary table. A metric that must be minutes fresh, or that scans data production cannot spare, needs a pipeline — and that is where Data Engineering begins (OLTP vs OLAP in Data is the boundary).
- Write the definition down where the chart is, and test it: a metric whose value you cannot reproduce by hand from the source is a metric nobody can trust (Two Dashboards, Two Numbers in Data is the failure it prevents).
The dashboard, by property
The decomposition is per metric, not per chart. The screenshot had six charts; the tree below has the one metric the case worked through, decomposed into the five properties, with the observation that would show each is right. The Visualization leaf is deliberately last and smallest.
- ├Metric— the definition the founder signs
- └Definition in one sentencetestable Two people given the definition and the orders list compute the same number for the same day.
- ├Source— where the definition's nouns actually live
- └Order + Payment + campaigntestable Every column the definition needs exists; the campaign column was missing and became a store requirement.
- ├Aggregation— the grain and the function
- └Count and sum by confirmation day and campaigntestable A day with a known set of paid orders produces the expected count and total in the summary row.
- ├Freshness— a property of the decision, not the tool
- └Yesterday complete by morningtestable The summary row for yesterday exists before the founder's first look, and a late-confirmed payment is included by the next run.
- ├Visualization— last and smallest
- └Table with sparklinetestable The founder reads the number for a campaign and a day without asking what it means.
The stacked area chart from the screenshot is absent. It was a visualization of a metric nobody had defined.
Summary table, or pipeline
The decision that freshness and load make. The first version is the first row; the second row is where Data Engineering begins and the links point. The case used the first for the founder's metrics and a separate small live query for the operational one, which is the third row.
Given the metric's freshness and the source's load, what is the smallest architecture that meets it?
when A day stale is acceptable, the aggregation runs on production at a quiet hour without hurting it, and the sources are the application's own tables.
cost A job, a summary schema, and a hand-verification of the first days; the first live-number request cannot be met by it.
when Freshness of minutes, or aggregations production cannot spare, or sources beyond the application's tables — ad platforms, support tools.
cost A pipeline with its own failures, freshness monitoring, and a second copy of the data whose definitions must match the first (The OLTP to OLAP Journey, Stale Dashboards).
when An operational question about current state — stuck orders — with freshness of minutes over a small, indexed slice of production.
cost A query on the production path that must stay cheap; not a dashboard metric, and not aggregated over history.
Where the number goes wrong
Each row is a way the dashboard is wrong while looking right — the invisibility this case is about. The response column is the property to go back to; most of them are Metric, because a definition nobody signed is the most common cause of a chart nobody can trust.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| Two readers, two numbers | "Orders" means created to one person and paid to another; the meeting argues about the chart. | No definition names the order state. | Write and sign the Metric sentence; show it beside the chart. |
| Checkout slows on a busy afternoon | Latency spikes while the dashboard refreshes. | Aggregations run on production on demand. | Summary table at a quiet hour; or a pipeline if freshness forbids it. |
| The founder plans from a stale number | Yesterday's late payments are missing from yesterday's row. | Freshness was never stated, so the job's timing was arbitrary. | State freshness as a decision property; include late events on the next run (Freshness Checks). |
| The number is right and the source is wrong | Campaign revenue is attributed to "unknown" for most orders. | The campaign column was added after the definition assumed it. | Treat the gap as a store requirement; backfill what can be backfilled; label what cannot. |
| Every chart is fresh, nothing is decided | Numbers move by the minute; nobody acts. | Charts were built from a screenshot, not from decisions. | Ask what each chart informs; drop the rest to an exploration page. |
How to do it
Most important first.
- For each requested chart, ask: what decision does this inform, and what would you do differently if the number were half or double? A chart with no answer goes on the "not V1" list.
- Write each metric's definition in one sentence that names the entity, the state, the grain and the time window. "Paid orders per day, by the day the payment was confirmed, attributed to the campaign on the order."
- Name the source and check it has what the definition needs. If "attributed to the campaign" needs a column the Order does not have, that is a requirement for the store, discovered by the dashboard (Missing Requirements).
- Ask for freshness as a decision property: "if this number were a day old, would the decision be wrong?" Most business metrics are fine by the morning; a few are not, and those are the ones that justify a pipeline (The Freshness SLO in Data).
- Build V1 as a summary table filled by a scheduled query, and a small page that reads it. Verify one day's numbers by hand against the source before anyone acts on them (The Pipeline Succeeded. The Data Is Wrong.).
Worked on a concrete problem
The move has to produce something. This is what it produced.
- The screenshot, translated. Six charts became three decisions: keep or cut a campaign (paid orders and revenue per campaign per day); reorder stock (units sold per product per week against stock on hand); and whether checkout is losing people (carts created versus orders paid per day). Three charts were dropped because nobody could say what they would do differently if the number moved.
- One metric, decomposed. Metric: paid orders per day per campaign, counting orders whose payment was confirmed that day. Source: Order joined to Payment, and a campaign column on Order that does not exist yet — a requirement handed back to the store. Aggregation: count and sum of total, by confirmation day and campaign. Freshness: the founder looks each morning; yesterday complete by then is enough. Visualization: a table with a sparkline, not the screenshot's stacked area.
- The first version. A nightly job runs the three aggregation queries against the production database at its quietest hour and writes one row per day per grain into summary tables; the dashboard reads the summaries and nothing else. Load on production: three queries a night. Freshness: yesterday, complete. One day's numbers were verified by hand against the orders list before the founder saw them (Full Refresh vs Incremental in Data covers the job's shape).
- The requirement that would change the architecture, when it appeared. Support wanted "orders stuck in PENDING for more than a few minutes, right now", which is a freshness of minutes over a state the summary table does not hold. That is not a dashboard metric; it is an operational alert, and it was built as a query against production with an index on state and time — small, fresh and separate. The nightly pipeline stayed nightly. Had the founder wanted campaign revenue fresh to the minute, the answer would have been a change stream into a warehouse, and the ledger says so (Cost vs Freshness in Data is the trade).
How you know it worked
What now exists that did not before, and what question you can now ask.
- Every chart on the dashboard names a decision, and the founder can say what they would do if the number moved.
- Every metric has a one-sentence definition next to it, and one day's value has been reproduced by hand from the source.
- The dashboard's load on production is a known number of queries at a known hour, and checkout does not notice it.
- Freshness was asked for as a decision property, and the architecture matches the answer rather than the tool's refresh button.
The questions you can now ask
The field this whole domain exists for. After this lesson, these are the questions to put to an unfamiliar problem.
- ?What decision does this chart inform, and what would change if the number were half or double?
- ?What is the metric's definition — entity, state, grain, window — in one sentence someone will sign?
- ?How stale may this number be before the decision is wrong, and does anything need to be fresher than the morning?
- ?Does the source actually hold what the definition needs, or has the dashboard just discovered a requirement for the store?
What can go wrong
- The metric definitions are written and the charts are built against production anyway, because the summary table "can come later". The load problem arrives on the first busy day.
- Freshness is set to the maximum the tool supports, and every metric is treated as if it needed minutes. The pipeline that follows is built for a requirement one metric had and the rest did not.
- The definition is signed and the source cannot supply it — the campaign column does not exist — and the dashboard shows a number computed from a proxy without saying so.
- The decision question is asked so rigorously that exploratory charts are refused. Some dashboards exist to find the question, and those are allowed to be vague as long as nobody acts on them yet.
- A nightly summary table is cheap, safe and a day stale; the first request for a live number cannot be met by it, and the answer is either a separate small query or a pipeline.
- Writing definitions someone signs turns "the dashboard is wrong" into a conversation about the definition — slower than fixing a query, and the only way the number can be trusted.
- Dropping charts that inform no decision loses the exploratory charts that would have found the next decision. The case kept a separate, unsigned "exploration" page for those.
- "Dashboards should not query production." Small ones can, at quiet hours, into summary tables — that is the case's first version. The rule is about load and freshness, and it flips into a pipeline when either requirement demands it.
- "Fresher is better." Fresher is more expensive and, for a decision made each morning, no better. Freshness is a property of the decision, not a virtue of the dashboard.
- "The charting tool is the dashboard." The tool is the fifth capability. Metric, source, aggregation and freshness are the dashboard; a wrong definition renders beautifully.
Where this applies
Problem-solving advice is stated as universal far more often than it is. These labels say what each method is specific to — and where CONTESTED appears, the note gives the strongest form of the opposing view.
- GENERALMetric, Source, Aggregation, Freshness, Visualization — in that order — apply to any reporting or analytics request; what varies is which property forces the architecture, and for most business dashboards it is freshness and load.
- DOMAIN-SPECIFICFor an operational dashboard — stuck orders, error rates, queue depth — freshness is minutes and the source is often the system's own telemetry, so the first version is an alerting query, not a summary table; the case treats the stuck-orders request that way. For a finance report, freshness is monthly and correctness is everything, and the definition is signed by someone outside engineering.
- ILLUSTRATIVEThe screenshot, the six charts, the three decisions, the nightly hour and the stuck-orders request are invented for the shape of the argument; no volumes or timings are being reported.
Where the depth lives
This domain asks the question and hands the answer off by name.
- — The Data Engineering domain owns everything past the summary table: pipelines, warehouses, freshness monitoring. This case ends where a requirement first needs one.