Every data team has a check that someone runs by hand each Monday. Row counts compared against last week, a null scan on the customer table, a glance at the newest partition to see whether it landed. It feels responsible, and for a while it is. Then the person who does it goes on leave, or the table list triples, and the check quietly stops happening. The fix is not a heroic framework. It is a repeatable method for choosing what to script and in what order, and this guide lays it out as numbered steps so you can follow it with your own pipeline.<\/p>\n
The principle underneath is simple and evidence-friendly: automate only what your own records show to be identical across runs, and keep a human on whatever required interpretation.<\/p>\n
Choose a check that happens at least weekly and whose purpose you can state in one sentence, for example “confirm yesterday’s orders arrived and look plausible”. Resist picking the most impressive one. The best first candidate is dull.<\/p>\n
Run it by hand at least three times. Do it the way you normally would, with the same queries and the same dashboards. The goal of these runs is observation, not efficiency.<\/p>\n
Open a plain text file and log each step as you take it. Capture:<\/p>\n
Timing is the evidence that will later justify the work. A step that takes forty seconds is not worth much effort. One that takes twenty minutes and is repeated daily is.<\/p>\n
Lay the three logs next to each other. Mark anything identical in all of them: same query shape, same thresholds, same output layout. Mark separately anything where your conclusion depended on context, for instance “volume is low, but it is a holiday”.<\/p>\n
You now have two columns. The first is mechanical. The second is interpretive. Only the first column is eligible for automation.<\/p>\n
Take the identical steps and put them into a script or a scheduled query. Keep it modest. A good early version does three things: runs the query, compares the result to an expected range, and writes a one-line outcome with a timestamp. It does not attempt to explain the result.<\/p>\n
For teams that want a formal framework, the ideas in a hands-on introduction to automated validation in DataOps<\/a> map neatly onto this step, and so does the broader overview of testing automation for data pipelines<\/a> published here earlier. Use either as a menu, not a mandate.<\/p>\n This is where many first automations go wrong. A failing check can notify someone, block a downstream job, or simply log the failure. For a first version, notify and log. Blocking downstream jobs should come only after the check has run quietly for a few weeks and you have measured how often it raises false alarms.<\/p>\n Write the failure message for the reader who will see it cold. It should name the table, the expected range, the observed value and the link to the query. If the message cannot be acted on without opening three other tabs, rewrite it.<\/p>\n For two or three cycles, let the script run while a person repeats the manual check. Compare outcomes. If they agree, good. If they disagree, you have found either a bug or an interpretive step you had mislabelled as mechanical. Both are valuable discoveries, and both are far cheaper to find now than in a production incident.<\/p>\n Automation tells you that something moved. It rarely tells you why. Decide who reads the outcome, within what time, and what they are permitted to do about it: investigate, pause a job, escalate. The answer should be written next to the script, not remembered.<\/p>\n After a month, count three things: how many times the script ran, how many alerts it raised, and how many of those alerts needed action. A check with many alerts and no actions is noise, so loosen the threshold. A check that never alerts may be fine, or may be testing the wrong thing, so deliberately break a copy of the data once to confirm it would notice.<\/p>\n Picture a team that compares daily row counts for a handful of source tables. By hand, each comparison takes a few minutes and involves the same query with a different table name. After three manual rounds, the log shows the query never changes and the threshold has always been “within a band of the same weekday last week”. The interpretation, such as “that source had a planned outage”, changes every time.<\/p>\n So they script the query and the band comparison across all tables, send a short summary each morning, and keep a field where the analyst types a reason for any miss. The mechanical work falls away. The analyst’s notes, kept in one place, start to show which sources cause trouble most often, which is a finding nobody had time to see before.<\/p>\nStep 5: decide what the script does when it fails<\/h2>\n
Step 6: run both versions in parallel<\/h2>\n
Step 7: assign the interpretation to a named person<\/h2>\n
Step 8: measure and prune<\/h2>\n
A short worked pattern<\/h2>\n
The same logic outside data engineering<\/h2>\n