Five rules from an AI pipeline, applied to a data platform
Five rules I would carry into any data platform, each learned in production on my AI and Python publishing pipeline, and what each looks like in a warehouse.
Almost every failure behind these rules looked like success. An upload script refused to upload and exited 0. A reconciliation job exited 0 on every run while confirming nothing. The art generator’s dashboard showed a green “Generated 0 images” for days. A data platform fails the same way: green tasks in the scheduler, over tables that are wrong.
The pipeline is Sleep Anchor, a sleep-music catalog I run alone. AI generates each track and its artwork, and a Python pipeline checks, renders and publishes it to YouTube, podcasts and streaming. I designed it and built it with Claude Code, an AI coding assistant. The Sleep Anchor case study covers how it is built and ends with five rules I would carry into any data platform. Here is the incident behind each rule, what I changed, and how the rule applies to a warehouse and the pipelines around it.
Write state when it is created
On 26 April 2026 a 28 GB upload to YouTube was killed at 70 percent, most likely when the dashboard’s development server reloaded. Neither piece of state that mattered survived the kill intact. YouTube returns a resumable session URI when an upload starts, and the script kept it only in memory, so the retry started from byte 0. The job’s status belonged to the dashboard rather than to the job, so the dashboard went on showing it as running.
Long jobs now run detached from the dev server. The upload saves its session URI and byte count after every chunk, through a temp file and a rename, and on start it asks Google how many bytes the server already holds. The resume branches have 12 unit tests. Job state lives in a SQLite table: the row is written before the process starts, and a sweep at start-up marks jobs whose process has died.
State also has to be written whole. On 9 August a podcast render started while its loop file was still being written, quietly used the raw master instead, and exited 0. Two episodes rendered with a 7.4 dB jump at every loop point, and I caught it by ear. The render now refuses to start without a finished loop file, and the loop step writes to a .partial.wav file and renames it when done, so a half-written file never appears under the final name.
On a data platform, a backfill or incremental load should commit its high-water mark with each batch, in the same transaction as that batch’s rows, not once at the end of the run. Create the run row before the work starts, and have a start-up check mark any run whose process has gone as failed. Land files under a temporary name and rename them; on object storage, where a rename is a copy, write a success marker last. Downstream steps wait on the marker, not on a file existing.
Gate irreversible steps with checks that can fail
The join test, a two-minute clip that proves a loop repeats cleanly, once started at the top of a seven-minute loop, so it crossed no join and could not fail. It now cuts its two minutes around the join.
A check can also fire and change nothing. On 1 May a dark, low-motion track tripped the black-frame check. The upload script printed “do NOT upload” and exited 0, so the dashboard marked the step done and the calendar entry posted, with nothing uploaded. The next day the same false alarm marked three Shorts posted with no video ID. A failed check now ends both upload scripts, for videos and for Shorts, with exit code 2, and a Short with no video ID is marked failed. The false alarm got a narrow answer: the dashboard passes an explicit --skip-qa flag only for Shorts, which are graded dark for sleep, and for the black-screen version, which is black on purpose.
In a warehouse, the irreversible steps are the ones that leave the platform or overwrite history: a reverse-ETL sync into the CRM, a file sent to a partner, a full refresh that truncates a table. Gate each one on a check that runs against the exact rows about to ship. A dbt test that only sees yesterday’s partition is the clip that never crosses the join. A script that catches an exception and exits 0 shows green in Airflow, and the load downstream runs anyway. A test that raises false alarms on a legitimate case needs a where filter that excludes exactly that case, not deletion, and not a downgrade to severity: warn, which stops it gating anything.
Keep one source of truth, and reconcile it
On 20 March I loaded at least six scheduling files into one SQLite calendar, and the same day I added reconciliation against YouTube. A row marked scheduled whose video is public becomes posted. A row marked posted whose video is still private raises an alert instead of being changed.
A stored row can also be trusted too far. On 29 July, the day the two-hour format went in, the duplicate check for a two-hour upload took its search terms from a calendar row written before two-hour videos existed, and never checked the length of what YouTube returned. It found the track’s eight-hour video, and the pipeline marked as done a two-hour release that had never been published. In August a title-format change and a row holding a Short’s ID produced two more wrong matches. Each fix moved the decision onto YouTube’s own answer: first the title it returns, then the runtime it reports, so nothing under 30 minutes passes as a long video. The matcher now has 30 offline checks.
A warehouse’s invoice table is a copy of what the payment processor knows. If the billing app marks an invoice paid when it submits the charge, the table records what was intended, not what happened, and a charge that fails later stays paid. Take status from the system of record: mark a row complete only when the source confirms it, and alert rather than overwrite when the two disagree. Match on the source system’s ID, and check that the record it returns is the kind you expected: a stored ID can point at the wrong thing, as mine did when a calendar row held a Short’s ID.
Make outside calls safe to repeat
In March a 14 GB upload that looked stalled, because Python buffered its progress output, was restarted until five copies of the video were on the channel. Since April a duplicate check asks YouTube itself whether the video exists, rather than trusting a status in the local database, and the upload stops unless --force is passed.
Until July the video upload script exited 0 when it refused a duplicate, so the dashboard marked the step done with nothing uploaded. It now exits 3. The podcast host creates a new episode on every publish call, so the batch publisher re-runs with --skip-if-exists instead of --force. That flag checks the host’s own state and treats an episode already there as done.
In ELT, a load should MERGE on the source’s key, or delete and reinsert one batch’s rows in a transaction, so an orchestrator retry lands the same rows once. The key has to be unique: merging on a column that repeats, like an email address, folds two real records into one without an error. A reverse-ETL job that creates records through a vendor’s API has my podcast host’s problem: a retry makes a second record unless the call carries an idempotency key or looks the record up first. And when a match could be wrong, “already loaded, skipped” needs its own status, separate from “loaded”, so a wrong match shows up.
Monitor the monitoring
The job that reconciles the calendar with the social scheduler ran several times a day and exited 0 every time. On 27 July, looking into why Shorts the scheduler had already sent were still unconfirmed, I found it had never confirmed a post. It matched only rows marked scheduled, while the dashboard marks a row posted when it hands it over. And it fetched one page of 100 posts, oldest first, so once the account passed 100 posts its seven-day cutoff discarded every one. It now sorts newest first, matches posted rows not yet confirmed, and sets the flag only when the scheduler reports the post sent. I verified the fix live against the scheduler.
The AI side had the same failure. In August the image provider retired the endpoint the art generator called, and for days the dashboard showed a green “Generated 0 images”, because the route discarded each error and marked the job complete. Now a job that produces nothing fails, and a partial run reports its count and first error. The next day I checked every other route that marks a job complete and fixed four more that could report output they never produced.
On a data platform, treat zero rows loaded as a failure wherever zero is not plausible, and have a task that loops over sources or partitions report how many succeeded and the first error, not one green tick. Alert on the output as well as the job, with dbt source freshness or a check on max(loaded_at), because a job that never starts raises no error. And have each reconciliation job record how many rows it confirmed, with an alert when that count stays at zero.