The Data Engineer: Making It Arrive Every Night

On Monday morning Aisha has Ben’s work open on one screen and an empty scheduling tool on the other. Ben’s work is genuinely good. It took him three weeks, forty or so hand-run queries, and a long afternoon merging duplicate customers by hand — Juniper Health, which a bad import had entered twice, and Corvex Manufacturing, which had sat under two separate IDs since its rebrand. Out of that came the finding the project now rests on: leavers’ logins start sliding about ten weeks before they cancel.

Aisha’s job is to make that happen again tonight. And tomorrow night. And on the Saturday of the August bank holiday when nobody is looking, and on the morning after the CRM vendor renames a column without telling anyone. The distance between it worked once on my laptop and it works every night unattended looks small written down. It is the entire profession.

What lands on Aisha’s desk

Three things arrive together. From Ben comes the analysis and, more usefully, the metric dictionary — the agreed definitions of active user, login trend, seat use — plus the deduplication rules he worked out by hand. From Sofia’s product requirements document comes a constraint written weeks ago and not negotiable now: risk scores must be refreshed within 24 hours of new usage data. And from Daniel’s budget split comes the money: $175k for data infrastructure out of the $600k programme, sitting inside the $250k envelope Omar approved for the build.

There is a fourth item nobody hands over formally: the five source systems that have to be wired in — the CRM, the product’s own event stream, billing, the support desk and the survey tool. Five systems, five owners, five release schedules that were never designed to line up.

What a data engineer actually does

An analyst answers a question. An engineer makes the answer keep arriving. Aisha’s work is to move data from the systems where it is created to the places where it is used, on a schedule, with checks that catch problems before users do — written as versioned code rather than a sequence of clicks, so that a year from now somebody can read exactly what happened to a number and change it safely.

The unglamorous heart of it is failure handling. Sources go down. Files arrive late, twice, or truncated. A column called the same thing for six years is suddenly called something else. A good pipeline is not one that works on a good night; it is one that behaves sensibly on a bad one.

The software on Aisha’s desk

Azure Data Factory (or Airflow, or a similar orchestrator) is the tool that turns Ben’s forty hand-run queries into something that happens at 2am without anybody present. It knows what runs, in what order, what to do when a step fails, and who to tell. That last part is more of the job than newcomers expect: an unattended system is only trustworthy if it is loud when it breaks.

Databricks does the heavy lifting where the data is too large for a single machine — turning millions of raw usage events into one tidy row per customer. dbt is where Ben’s hand-applied cleaning rules become permanent, tested code, so that the Corvex rebrand is collapsed into one company every night rather than once, by a person who happened to notice. And Grafana watches the whole arrangement: data freshness, row counts, failed runs. Together these four turn “it worked on my laptop” into “it works every night”, which is the entire distance this desk exists to cover.

The four decisions

Deciding the layers: never destroy what you cannot recreate

The first decision is the order of the stages. Data leaves five source systems, and before it reaches the model, the customer success platform and Ben’s dashboards, it has to pass through something. Aisha’s order is fixed and, to her, non-negotiable. First a raw landing zone — an exact, timestamped copy of every file and extract as it arrived, untouched, kept whether or not anyone ever reads it. Then a cleaned layer, built from that copy, where Ben’s deduplication rules run automatically: Juniper’s double entry collapses to one company, Corvex’s two IDs become one, dates get standardised, obvious rubbish gets quarantined rather than deleted. Then the feature table — one row per customer, every signal in its final agreed form — which is the only thing anything downstream is permitted to read.

The reason for that order is not tidiness. It is that cleaning code has bugs — every cleaning rule anyone has ever written has been wrong at least once, usually in a way that looks fine. If the cleaning runs before anything has been archived, the bug does not throw an error. It silently destroys information, and the only evidence that the information existed was the thing you just overwrote. Six weeks later somebody asks why survey scores stop in April, and the honest answer is that nobody can find out, because April is gone.

Keep the raw copy and every mistake becomes replayable instead of fatal. Fix the rule, run it again over the archive, and the damage undoes itself.

Deciding the cadence: how fast does the decision move?

The second decision sets most of the system’s cost. How often does it run — continuously, nightly, or weekly?

The seductive answer is real-time streaming: scores updating within minutes of a customer’s last login, which demos beautifully and which somebody on the leadership team has almost certainly seen a competitor show off. It is also roughly three times the cost and complexity — around $60k against $20k, plus two extra weeks — and every dollar comes out of the same envelope Priya, Elena and Kofi are living inside.

Aisha chooses nightly: one run at 2am, finished long before anyone opens a laptop. The reasoning takes a minute and is worth copying. Churn does not happen in minutes, it happens over weeks — that is precisely what Ben’s ten-week warning window says. Sofia promised 24 hours, and a 2am run honours that with hours to spare. And nobody at Northwind can act faster than daily anyway: Jade’s team makes calls in office hours, from a list they read with their coffee. Real-time would buy freshness the problem cannot consume and the organisation cannot answer.

Weekly, at about $12k, is the trap in the other direction. It looks like prudent economising and it breaches the PRD outright. On a Sunday-only schedule, Bramford Logistics’ collapse would be six days old before it reached anyone’s screen — six days out of a nine-week renewal window. Sofia would bounce it back, correctly, and the rebuild would cost a week the saving never justified.

Tuesday, 6:12am: the column that changed its name

The CRM vendor’s overnight update renamed customer_id to account_ref. The 2am run failed at the very first join and stopped. It is renewal-call morning, the customer success team has no fresh list, and Aisha is reading the alert on her phone before she has finished making tea.

There are three ways out, and all three get someone working by nine. She can fail loudly and fix it properly: repair the mapping, rerun, and add an automatic check comparing each source’s shape against what the pipeline expects, so the next rename is caught by a machine at 2:05am rather than by a human at 6:12. She can patch it inline — one line, five minutes, no note, no review. Or she can quietly serve yesterday’s data, letting the platform show Monday’s world while she deals with it later.

Aisha takes the first, and it costs her an hour instead of five minutes. The inline patch is tempting precisely because it is invisible, and that is also what makes it expensive: an undocumented mapping is a fix with an expiry date, inherited by whoever is on call in eighteen months and finds a column named for something it is not, with no explanation anywhere. The third option is the genuinely dangerous one. Nobody notices stale data — that is its defining property. The team works a full morning from Monday’s picture, believing it is Tuesday’s.

That is the principle worth carrying out of this module even if you never touch a pipeline: systems that fail loudly are trusted; systems that fail quietly are believed, which is worse. A pipeline that stops and shouts is annoying and honest. A pipeline that guesses, or that serves yesterday with a straight face, teaches an entire organisation to rely on numbers that are sometimes wrong in ways nobody can detect.

Thursday: the file that arrived twice

On Thursday the payments provider times out mid-transfer and re-sends Wednesday’s file. Both copies land. Both are processed. Juniper Health now shows four failed payments instead of two, and its risk score has doubled overnight on the strength of two events that never happened.

The obvious fix is to delete the duplicate rows. It takes ten minutes and it works — and it leaves the disease completely intact, because duplicate delivery is not an accident. It is a permanent feature of systems that talk to each other over networks: retries, timeouts and re-sends will happen for as long as the pipeline exists. The next duplicate will arrive on a weekend, be processed silently, and nobody will be watching the account it distorts.

So Aisha makes ingestion idempotent, which is an intimidating word for a simple promise: safe to run twice. Each incoming file is fingerprinted by its contents, and a file whose fingerprint has been seen before is recognised and ignored. Run the same day’s load ten times and the result is identical to running it once. Then she reprocesses the affected history, so the damage is undone rather than merely stopped, and Juniper’s score drops back to what reality supports.

That last step matters, because the inflated score does not stay in the pipeline. A doubled Juniper score would sail to the top of Jade’s call list in module 11, spending one of that week’s scarce conversations on a distorted picture while Bramford — genuinely leaving, nine weeks out — sat a row lower.

Where this goes wrong

Here is the version that plays out in real companies. The analysis works, the demo lands well, everyone is delighted — and the notebook that produced it, the “temporary” one with a hard-coded file path and a manual dedup step, quietly becomes production. Two years later it is still there, run by hand, roughly weekly, by whichever person still remembers the sequence. Nobody has ever budgeted for replacing it, because from the outside it appears to be working.

Then that person goes on leave, and the numbers stop. Or worse, they do not stop: they keep arriving, slightly wrong, because a source changed shape in March and there was never a check to notice. The company discovers its churn model has been scoring customers on stale usage data for five months, and every intervention decided in those five months is now suspect.

What Aisha hands on

What leaves her desk is a production pipeline and a data-quality checklist: five sources, three layers, a 2am schedule, schema checks on every source, deduplication as tested code rather than an afternoon of somebody’s judgement, and ingestion that survives the second delivery of everything. What everyone else sees is one table — the feature table, one row per customer, refreshed nightly, with Maya’s labels joined on.

It goes to Priya, the Data Scientist, in module 8, with a sentence that is more honest than it first appears: it is as clean as the decisions we just made. Priya will train on exactly this table, landmines included — every rule Aisha got right and every one she left undone. The cadence sets the rhythm Kofi’s scoring job inherits in module 10; he will schedule around a 2am refresh because that is the world he is given. And the quality of what arrives determines whether Jade’s call list in module 11 is a ranking of real risk or a ranking of data errors.

Spot the decision

Read each situation and decide what a data engineer should do, then tap a card to flip it and check.