Fresh, tested, trusted
A dashboard is only useful if people believe it on Monday morning. This lesson shows you how to run the chain behind the weekly trading dashboard in the right order, promise and show how fresh the data is, catch missing and odd data with volume checks, send alerts to someone who acts, and handle the morning when a number is wrong.
By the end you can
- Order a pipeline by dependencies (load, transform, test, refresh) instead of by clock times
- Write a freshness SLA from the meeting it serves, and show "data as of" on the dashboard
- Write SQL that checks each store's freshness and row count against the same weekday of past weeks
- Route alerts to an owner and run the incident routine: banner, tell users, fix, backfill, write-up
- Agree a data contract with the team that sends you the data
The everyday picture
Think of a morning newspaper. The presses start at 03:00, but only after the last pages are in: printing a paper with a blank sports page is worse than printing ten minutes late. The date is on the front page, so nobody reads yesterday's paper by mistake. Someone at the printer checks that today's paper has the usual number of pages; a Sunday edition with 12 pages instead of 48 means something went wrong.
When a story is wrong, the paper prints a correction that says what was wrong and what is right, and the editors find out how it slipped through. And the news agencies that send stories have agreed with the paper on when they deliver and in what form. Your dashboard needs the same five habits: wait for the inputs, print the date, count the pages, correct in public, and agree terms with whoever sends you the data.
The chain behind one number: schedules and dependencies
Every number on Mangrove's weekly trading dashboard is the end of a chain. For store sales it goes like this. After closing, each store's POS uploads a file with the day's sales. A load job copies the files into raw tables in the warehouse. Transformations build the staging models and the sales mart you built in Transforming data in the warehouse. Tests check the result. Then the BI tool refreshes the dashboard's dataset, ready for the 08:30 Monday trading meeting. Each step needs the one before it to be finished and correct.
There are two ways to start those steps.
- By the clock. Load at 02:00, transform at 03:00, refresh at 06:00. It is easy to set up, and it works until a step runs long. On the night the load takes until 03:40, the 03:00 transform builds the mart from half the files, the tests pass on the rows that are there, and the 06:00 refresh shows the result with no warning.
- By dependency. Only the first step has a start time or a trigger. Every other step starts when the steps it needs have succeeded. A late load makes everything after it late, never wrong.
The tool that runs steps by dependency is an orchestrator. Apache Airflow, Dagster and Prefect are common ones, and dbt Cloud can run dbt jobs on a schedule. They describe the chain as a DAG and give you retries with a wait between them, timeouts, a log of every run and one screen that shows what failed. Inside the transform step, dbt build already works this way: it runs models and their tests in dependency order and skips the models downstream of a failed test.
Three design choices matter more than the tool:
- Wait for the inputs. The first step checks that the inputs have arrived. "Start at 02:00. If fewer than 320 store files are in, check again every 15 minutes until 05:30. Then run with what is there and list the missing stores."
- Make the refresh the last step of the chain. The pipeline tells the BI tool to refresh when the tests pass. Most BI tools can refresh on their own timetable, and that timetable knows nothing about whether the data is ready. Most can also be triggered from outside, through an API or an orchestrator's connector; use that.
- Decide what failure looks like. When a step fails, stop the chain, keep the last good data on the dashboard and show a message. Yesterday's correct numbers, clearly dated, are better than today's wrong ones.
Quick check
The load is scheduled at 02:00, the transform at 03:00 and the dashboard refresh at 06:00, each on its own clock. One night the load runs until 03:40. What does the Monday meeting see?
Clock schedules don't know about each other. The transform ran on time over whatever had arrived, the tests passed on those rows, and the refresh published the result. Waiting for the load only happens when the steps are chained by dependency.