The night shift: nine in the morning

The watch log: what an engineer is paid for

14 min

O que você vai aprender

to close a shift by the cycle "ticket → cause → cure → the first check to catch the problem recurring"
to read the run log as a table: duration, read and write counters, state
to find a hole in a run of days with a query rather than by eye
to understand how the rest of the course is structured, and what in it runs for real and what is emulated

18:40. The shift is handed over

The lights on the intake level of Vault-9 switch to standby mode.

You fixed a lot during the shift: you found the cause of the failure and reproduced it in code, the loader no longer falls over on a row with no status, the port fetches the window whole, and re-running one date does not double the warehouse.

But the data itself has not been repaired yet. The mart is still empty for 14 March and still doubled for the 13th. The belt will only be able to reload the affected days once it learns to move data between layers — that is chapter de2.

What is built into it now, though, is the main condition of a safe reload: a repeated run no longer spoils the result.

One step of the shift is left — the log entry. That is what separates a watch from heroics.

What matters in the log is not listing "what I did" but recording four points from which the next engineer — or you yourself in a month — can reconstruct the failure without a word of spoken explanation:

the ticket — what the Academy saw, and in which numbers;
the cause — what happened in the belt, with a reference to rows of the run log;
the cure — what exactly has been changed in the code;
the check — what will now fail first if this happens again.

The first three points describe what has already happened. The fourth will help catch a recurrence — which is why it matters more than the rest.

Start with the cause. On a shift you do not retell it from memory: you show it with a query against etl_runs.

The watch log on the desk: on the left one line struck out in someone else's hand, on the right four even entries with their marks, and between the pages a calendar strip with one empty cell. — You are paid not for the re-run but for the entry: ticket, cause, cure, check — otherwise the night comes round again.

In front of you is a week of the build_dm mart pipeline at work. Each row combines the attempts of all its tasks for one day. Five ordinary nights set the background against which the failure stands out. Compare 13 and 14 March with the other days: the attempt count, not_succeeded, and minutes. The rows_written column sums counters; for an unsuccessful attempt, it only shows that the attempt reached the writing stage.

The Switchman's rule

Five nights running the picture is the same: eleven minutes, two attempts, none unsuccessful. That is the normal background of the pipeline.

On the 13th everything changes: four attempts across two tasks, two unsuccessful, fifty-seven minutes. On the 14th a different anomaly appears — one of the two attempts never finished. finished_at is empty, which is why the "four minutes" in the last row are counted over one completed step alone rather than over the night as a whole.

None of these facts had to be found out from people. The run log is an ordinary TABLE, not a file of logs. It holds one row per attempt of a task for a logical date: the state, read and write counters, when it started and when it finished.

That is exactly why you do not have to scroll through the log by eye. You can put a question to a table — "show me the nights that do not look ordinary" — and get the answer in a second.

Now back to your predecessor. They called him the Switchman: for three years he ran the belt and switched it over by hand whenever something went wrong.

Over that very mart loader, in his code, hung a comment:

# IMPORTANT: do not run twice for the same date — it will double the day!

The comment was true. It was written in advance. And still, on the night of 13 March, the day was doubled: the task was re-run not by a person but by a retry. Retries do not read comments.

QUERY: I read that line every night for three years. The retry never once did.

Hence the rule the course will come back to in every chapter:

The Switchman's rule: a rule written in a comment does not exist.

Only what executes exists.

The rule "do not run this twice" has two executable forms. You have written both of them in this chapter: code in which a repeat is harmless — rewriting the partition — and a check that fails if that property disappears — two runs and a comparison of snapshots.

The fourth point of the log entry is about exactly such a check. After a failure it is not enough to know that the code was fixed. You have to leave an automatic signal that will be the first to notice the problem coming back.

A simple watch check uses a similar idea: compare the calendar of days against what actually arrived.

The week's calendar is on the left; load attempts and mart rows are on the right. A check like this finds the hole without anyone reading through the rows: the calendar exists independently of the load, so the day is always there even when it has no rows. Do not unpack the four nested counts now: they only fill the right-hand side. Focus on the independently built calendar.

The shift map: ten sections of one belt. The course is not a list of separate topics, it is the assembly of one system. Each chapter adds a working section to the belt. The names in the right-hand column are signposts for later chapters: you do not need to memorize or unpack them now.

Chapter	Section	What the belt gains
de1	the intake port	the row contract, , the first DAG, an idempotent run
de2	intake	the raw layer, the load watermark, late edits, , reloading, change streams
de3	the racks	what one fact row means, surrogate keys, SCD2, joining on a date, the star
de4	the belt's code	where transformation SQL lives, memory and batches, secrets, logging
de5	the beat	parse-time, `logical_date`, schedules, retries, XCom, sensors, Assets
de6	the output	MergeTree, partitions, versions instead of UPDATE, MVs, a flat mart
de7	transfer and re-run	PostgreSQL → ClickHouse, types and time zones, backfill
de8	control	checks before the mart, freshness and volume, quarantine, schema drift
de9	the stream	key and partition, commit before or after, groups and rebalancing, an idempotent consumer
de10	shift handed over	an incomplete brief, the capstone, a map of the territory, the exam

The first chapter is closed: the belt already has an intake port, a first DAG and a safe repeated run.

The nine remaining sections carry on with the same work — making the system such that every next failure takes fewer words to explain.

An honest frame for the course: what is real here.

Not everything the course teaches can run inside a browser. So let us separate three levels in advance: what runs on real engines, what is emulated, and what is not in the learning environment.

Runs for real. SQL runs in genuine PostgreSQL and ClickHouse on the same "Night Shift" . The table names match, but the engines behave differently. Python runs in a real interpreter, straight in the browser.

Emulated. Airflow, Kafka and the HTTP source: their real versions need a network, a broker and a database, none of which exist in a browser. The course replaces the TRANSPORT, not the task: the public interface, semantics and code stay the same as in . Every such lesson carries its own panel listing the limits of the emulation.

Not covered. Infrastructure: cluster deployment, access control and networks, monitoring, on-call alerting, real parallelism, and the real price of hardware.

That is not a missing part of the course. It is a boundary of the learning environment, and it is more honest to mark it plainly than to act out infrastructure where it cannot be reproduced.

Interview question

How this is asked at interview. "In the morning you see that yesterday's data is not in the mart. Describe your first fifteen minutes."

What they want here is not a set of guesses but an order of checking.

First — the run log. You need to know whether the task was launched at all, how many attempts there were, what the last one ended in, and whether any of the unsuccessful attempts reached the writing stage.

Then — find the step where the belt broke off. Failures at source intake, parsing, and publication all look the same to the user — there is no data — but they are fixed differently.

After that check the neighbouring dates. One missed day and "the pipeline has been breaking for three nights running" are different incidents and call for a different scale of reaction.

And only then does it make sense to re-run or repair anything.

Understanding the two different outcomes earns extra credit. A day can be missing — then a re-run may help. Or it can be counted twice — then the same re-run only makes things worse. You tell those cases apart by the log before launching anything again.

The final question usually goes: "and what will you do so that it does not happen again?" The answer has to lead to an executable check or a change of mechanics. "I'll write it up in the documentation" does not close the problem.

Check yourself

In the log for 13 March the build_dm pipeline has four task attempts, two of them unsuccessful, and the mart holds rows for that day from TWO different run_ids. Which entry will close the incident after the data has been repaired?

Check yourself

The question "which days in March has the mart not counted" is answered with a query. Why is it not enough to filter for March, group dm_daily_revenue by date_id, and look at the result?

Practice: solve the tasks

Solved 0 of 3 · any 2 is enough to pass

Principais pontos

Now let's put the whole shift into four lines — so that the next engineer sees not the story of the investigation but its result.

Ticket: the daily report is empty for 14 March, and for 13 March the revenue is twice what the snapshots hold.
Cause: for the 13th, the build_dm log shows four task attempts (two unsuccessful, 57 minutes instead of the usual 11), and the load wrote the day twice under two run_ids; for the 14th, it did not finish at all — finished_at is empty.
Cure: the load rewrites the partition of the logical date instead of appending; the port fetches the window whole and rejects bad records at the boundary. The belt will be able to reload the affected days in chapter de2 — what was built into it today is what makes a reload safe.
Check: two runs of the same date in a row must give the same warehouse ; the run of days is checked against a calendar, not against the mart itself.

Those four points hold the whole cycle of a watch: what broke, why, what was changed, and which check will now be the first to notice a repeat.

The Switchman's rule is the one thing carried from this chapter into all the others: a rule written in a comment does not exist. What exists is what executes.

QUERY: Shift accepted, archivist. You closed this night with the question "what happened". The next one you will close with the question "what arrived" — and that is where I shall be needed. Meanwhile I shall doze on the receiver.

The belt can already fetch data from a source, force it into a contract and survive a repeated run.

In the next chapter it gains an intake: the raw layer, the load watermark — up to which point the data has already been fetched — and late edits, when the source changes a row after it was taken.

And with them comes the first reload that can be run any number of times.