A production data pipeline for tracking car prices

The dependency nobody named

The pipeline had a hidden coupling. To know what to fetch next, it queried dbt — intermediate tables that computed staleness scores, mapped listing IDs to VINs, and produced the scrape queue. That meant after every batch of fetches, the pipeline needed a dbt rebuild before it could schedule the next one. A full build was taking 45 seconds. It could not run concurrently — a build lock had to be added after parallel runs started stepping on each other.

The carousel hints problem made it worse. Fetching vehicle A would sometimes surface price updates for B, C, and D in the same response. Those vehicles did not need their own fetch — but the pipeline could not know that without running dbt first. Operational logic and analytical logic were tangled in the same models, running on the same schedule, blocking each other.

The question that reframed it

The fix came from asking a different question: what should dbt actually own?

dbt is good at one thing — transforming a stable, append-only record into analytical assets. Deal scores, price history, national benchmarks. That work runs hourly and can fail and retry without consequence. The scrape queue is different. It needs to reflect the state of the world right now: which listings are stale, which VINs are cooling down, which artifacts have not been processed. That is operational state. Rebuilding an analytical model to answer "what do I do next?" is the wrong tool for the job.

The three-schema solution

Postgres gets an ops schema: HOT tables, one row per entity, always current. The scrape queue becomes a plain Postgres view reading from those tables — no dbt, no rebuild cycle, no lock. Operational workers own these directly.

A staging schema holds event tables: one row per event, append-only. Processing writes the current row and its event in the same transaction. On a schedule the archiver exports those events to hive-partitioned Parquet and deletes what it exported. Postgres never becomes the analytics store — it hands the record off and stays lean.

dbt then runs against the Parquet through DuckDB's httpfs extension. The lake is the warehouse. Deal scores and price history are built from the permanent record rather than from operational tables that are still being written to.

Where the 403 backoff ended up, and why it matters

The same question has a second, sharper answer. When a listing gets refused, an exponential cooldown decides when it may be tried again. That looks analytical — it is computed from a history of block events — and it originally lived in a dbt model.

It does not belong there, for the reason above: it is a decision the pipeline executes, not an interpretation of what happened. So the executable backoff now lives in the Postgres ops.ops_detail_scrape_queue view, where the scrape queue reads it transactionally in the same query that claims work. dbt still consumes the same event stream for cohort, funnel, and block-rate analysis.

Two consumers, one event stream, and the split is between deciding and understanding rather than between two copies of the same logic.

What changed

The dbt build that used to block the pipeline now runs on its own schedule without holding anything up. Operational queries that took seconds take milliseconds. The staging tables give the analytical layer a clean event stream, and when the archiver drains them, Postgres shrinks back down rather than growing.

Object storage started as a storage fix. It ended as the thing that made the separation possible — a boundary between what the pipeline needs right now and what the analytics layer needs eventually. Once the architecture reflected that, both got faster.

The problem with logic you cannot test

The issue was that n8n was not just an orchestrator — it was holding business logic. SQL queries lived in Postgres nodes inside workflows. Conditional branching, error handling, retry decisions, all of it in a visual UI exported as JSON. None of it could be unit tested. None of it showed up in a diff in any meaningful way. When a workflow had a bug, diagnosing it meant clicking through an interface and guessing which node was wrong.

The worst part: a broken workflow looked the same as a passing one. n8n would report success on a run that had silently dropped records or skipped a step it should not have. The only way to catch it was to notice that data had stopped arriving, which could be hours later. That is the same failure mode the observability section below was eventually redesigned around, arriving from a different direction.

I was still patching n8n workflows on April 13th. Two days later, I started building Airflow.

The insight that drove the switch

The decision was not really about Airflow's feature set. It was about a principle the n8n experience had made concrete: orchestration logic should be subject to the same standards as everything else.

If it cannot be unit tested, it is not code — it is configuration. And configuration that holds business logic is the worst kind, because it looks trustworthy sitting in the repository while not being testable the way code is.

This became the fat services, thin DAGs pattern. A DAG contains two things: scheduling and an HTTP call. No business logic, no SQL, no conditional branching. All of that lives in Python services where it can be tested, diffed, and reviewed like any other function.

The migration

Airflow ran in parallel with n8n for four days while the DAGs were built and validated. On April 20th the n8n workflows were deactivated. Nine days later the container was removed from Compose entirely.

The cutover was clean because the services had not changed — only what was calling them. The scraper, processor, and archiver already exposed HTTP endpoints. n8n had been calling those endpoints; Airflow now calls those endpoints. Swapping the orchestrator did not require touching a service.

What changed

A broken DAG now fails in CI before it ships. Every piece of business logic that used to live in a workflow node lives in a Python function with a test. The DAGs that run the pipeline today are each readable in under a minute, because they are scheduling and nothing else.

The pipeline did not get more capable when I switched to Airflow. It got more trustworthy. That turned out to matter more.

First: compression, with the dictionary problem solved properly

Fetched HTML from one site is highly repetitive across pages, which is exactly the case a trained compression dictionary is for. Bronze objects are written as independently decompressible zstd frames using a trained dictionary — and the dictionary's ID is encoded in the frame header.

That detail is the whole design. The reader selects the right dictionary from the data itself, not from a mutable "current dictionary" setting somewhere. Training a new dictionary changes future writes; it never makes an existing object unreadable. A scheme that stores the dictionary choice outside the object is a scheme where one configuration change silently orphans years of history.

Then: measuring the thing that was actually expensive

Compression bought a large reduction in bytes and did not fix the pressure on the volume. Bytes, object count, and inode consumption were being tracked as one number, and separating them showed why: each artifact is its own object, each object consumes an inode, and inodes ran out long before the disk did. The forecast alert that matters most in this stack is not "disk is filling" but "inodes are exhausted in under seven days."

Packing, and the rule that makes it safe

Packing groups a closed month of artifacts into immutable packs with a columnar sidecar index. Reading one artifact becomes a ranged GET plus a single frame decompression rather than a scan, so random access survives the consolidation.

The safety rule is the part worth copying: a source object may be deleted only after its packed replacement returns byte-identical content, verified against a hash held in the sidecar. Not "the pack was written successfully" — the packed copy is read back and compared. Deleting the only copy of history on the strength of a successful write is how archives get lost.

What changed

The first three packed months turned 2.7 million bronze objects into 222 packs and sidecars. Inodes stopped being the binding constraint on the data volume and bytes became one again, which is where a storage problem should end up — and the object count is the honest way to say it, because a whole-volume percentage would credit packing with a smaller number than it earned while the pipeline kept writing new pages throughout. The stack still runs on a single server — one Postgres, one object store, one scheduler — and that remains a deliberate choice rather than a limitation, because at this scale the bottleneck is whether the data model is right, not what the hardware can do.

A design that was built, proven, and thrown away

Section-level deduplication splits each page into named sections and stores each distinct section once. It was implemented, and it is lossless — the audit reconstructs artifacts byte-identically. On bytes alone it looked like a win. Measured against the real backend it came out 223% worse than simply compressing each page, because this single-drive object store costs roughly 8 KB per object in directory and block padding, and 60 artifacts became 556 section objects. Splitting more finely made it worse rather than better: content-defined chunking at 256 B reaches 70.5% gross deduplication and nets −622.9%. The redundancy was real and reachable, and collecting it cost far more than it saved. Showing a rejected design is the point — the measurement is what made it a decision rather than a hunch.

The bigger win that is deliberately not taken

Storing only the page regions the parser actually reads would cut stored HTML by roughly 96% stacked on the dictionary — the largest reduction measured here, and the only one that is irreversible. It is refused on what bronze is for. Bronze is what arrived; silver and mart are opinions about what it means, and an optimization that keeps only what today's parser understands quietly turns bronze into another opinion. The probe showed it concretely: the projection is parser-equivalent on 60 of 60 active listings, and it silently rewrites a blocked page and an unlisted page to active, discarding the fields that said otherwise. A delisted vehicle reappearing as available is the worst direction that error can run.

Why nothing fired

Every signal in place was a signal about a component. Is the container up? Is the process responding? Is the error rate elevated? All three answered yes, yes, no — and all three were true. The fetch path was running, responding, and returning without raising anything. It was simply returning nothing useful.

A health check that asks "are you up?" cannot distinguish a service doing its job from a service that has stopped doing its job while remaining up. That gap is not a missing alert. It is a category of alert that did not exist here.

Change one: absence has to be a value

Health became three-state, evaluated against a checked-in expected service set. This closes two holes at once.

A service that is stopped or removed does not read as unhealthy in a naive metric — it leaves the metric altogether, and a query over "all known containers" simply stops mentioning it. One absent service is silent by construction. Comparing against an expected set makes absence read as failure, which is what it is.

The second hole: "running, but with no healthcheck configured" used to be indistinguishable from healthy. It now reads as its own unattractive third value, so an unmonitored service looks like what it is rather than like a passing one.

Change two: alert on missing success

The rest of the alerting was rewritten to fire on the absence of success rather than on the presence of errors: no successful session bootstrap within a window while non-success outcomes accumulate; no successful fetch while attempts continue. Those two conditions would have caught the outage in minutes.

It is a harder question to ask, because it requires knowing what success looks like and how often to expect it. That is precisely why it is the question worth asking — an error-rate alert only needs to know what a failure looks like, which is why it is the one everybody has and the one that missed this.

What is still open

Container health, scrape volume, and log-based alerts route to Telegram, and the missing-success rules are live. Functional liveness — whether the pipeline is producing correct results at all — remains a separately measured concern, and the work to bound it is in progress rather than finished.

That sentence is deliberately not rounded up. An earlier version of this page said every failure surfaces as a notification, and the outage above is the standing proof that it did not.