Data Pipeline Trade-offs & Tool Choice

The reasoning

The extract's advantages are almost all operational. It is a job that runs, succeeds or fails, and can be rerun; there is no cluster, no retention policy, no consumer lag, and nobody carries a pager for it. It is also trivially reprocessable — run it again for a different date. Against that, it puts periodic load on the source, cannot see intermediate states between runs, and has a floor on freshness set by the schedule.

Kafka's real advantage is usually misidentified as latency. The more durable one is *fan-out*: publish once and let five consumers read independently, with a replayable log in between that decouples producer from consumer. If three teams each want order events, the extract answer means three extracts hitting the same database. That is the argument that survives scrutiny; 'we want it faster' often does not.

So the answer depends on two questions, and asking them is most of the signal. How many consumers want these events, and what changes if they arrive at 09:00 instead of overnight? One consumer and a morning deadline is an extract, and building Kafka for it means running a distributed log to serve one nightly reader. Several consumers, or a genuine minutes-matter requirement, and the platform pays for itself. **What would change my answer**: the moment a second and third consumer appear for the same data, or the source can no longer absorb repeated full extracts.

The reasoning

On correctness CDC wins clearly. Reading the write-ahead log means you see every change including hard deletes, you get a monotonic ordering column for free, and nothing depends on application code maintaining a timestamp. A query-based extract infers change from a column, which means it inherits every gap in how that column is maintained and cannot see a delete at all.

On cost the picture reverses. CDC needs replication-level access to a production database, a connector running continuously, monitoring for replication lag, and attention when the slot backs up and threatens the source's disk. That last one is why DBAs push back: a stalled consumer can take down the operational database, which makes your analytics pipeline an availability risk to someone else's system. A query-based extract has none of that blast radius.

In practice the deciding factors are whether deletes matter and whether you can get the access. If the table is append-only — events, transactions, log lines — a query-based extract on an insert timestamp is completely adequate and far simpler. If it is a mutable entity table where rows get deleted or corrected, CDC is worth the argument. **What would change my answer**: discovering that the modified column is not maintained on every write path, which turns the simple option into a silent data-loss option.

The reasoning

The week to write it is not the cost. The cost is three years of the source's API changing, pagination edge cases, rate limits that appear under load, auth token rotation, and the schema drifting underneath you — all of which lands on whoever is on call. For a commodity SaaS source, a vendor amortises that across thousands of customers and it is genuinely cheaper than your team doing it once.

Where buying stops making sense is volume and strangeness. Per-row pricing that is comfortable at ten million rows becomes indefensible at ten billion, and the crossover arrives faster than people plan for. And a connector for an internal system, an unusual protocol or a source with real business logic in the extraction does not exist to buy, so the comparison never starts.

The trap worth naming is lock-in through incidental coupling: the vendor's landing schema becomes the shape everything downstream models against, and switching later means rewriting every model rather than just the connector. Landing into your own raw layer with a thin normalising step keeps the option open cheaply. **What would change my answer**: volume crossing the point where the invoice exceeds an engineer, or the vendor dropping support for a source you depend on.

The reasoning

Forty hand-written pipelines means forty places a bug lives, forty different retry conventions, and a fix that has to be applied thirty-nine more times. That is a real and compounding cost, and it is why the framework instinct is right more often than not.

The framework's failure mode is specific and worth naming, because interviewers have lived it: configuration grows until it is a programming language nobody designed. It starts with source, table, schedule. Then one source needs a custom predicate, so there is a filter option. Then one needs two passes. Three years later the YAML has conditionals, and it is harder to read than the forty scripts would have been — with none of the tooling, tests or debugger a real language gives you.

The line that holds is to templatise the *shape* and keep the exceptions as code. A framework should cover extract, land, merge, and the standard checks; a source that needs genuinely different logic gets its own module rather than a new flag. **What would change my answer**: the count. At five sources the framework is premature abstraction; at forty, bespoke is unmaintainable — and the crossover is usually around the point where a second person has to make a change.

The reasoning

Pulling means you decide when, how often and how to recover. If it fails, your monitoring sees it and your team fixes it, and a rerun is entirely within your control. The costs are latency — you only see what has happened by the time you ask — and load on the source, which you are imposing on someone else's system on your schedule.

Pushing inverts all of it. The producer sends as things happen, so latency is low and the source is not being scanned. But the delivery guarantee is now theirs: if their emitter breaks, silently stops, or is switched off during their incident, your data stops and you may not find out until someone notices a stale dashboard. Detecting *absence* is much harder than detecting a failed job, and this is the asymmetry that decides most cases.

The practical answer for push is to make absence loud: a heartbeat or expected-volume check that fires when nothing arrives, and an agreed contract about what is sent and how often. Push suits event-shaped data from teams who want to own the emission; pull suits state-shaped data from systems that have no interest in you. **What would change my answer**: whether the producing team is willing to be on call for delivery — if they are not, pull, whatever the latency argument says.

The reasoning

Start by conceding what no longer differs. Both give ACID transactions, time travel, schema evolution and good SQL. Both handle semi-structured data. Both scale further than most organisations need. Arguments built on those distinctions are out of date and interviewers notice.

What genuinely differs is who can read the bytes. Iceberg or Delta on object storage means Spark, Trino, DuckDB, Flink and a warehouse can all read the same files, which matters when workloads are not all SQL — ML feature pipelines and large-scale transformation are the usual reasons. A proprietary warehouse format means everything goes through that engine and its pricing, and migration later is a data movement project rather than a config change.

The other real difference is operational burden. A lakehouse hands you compaction, file sizing, manifest maintenance and metadata catalogue decisions — genuine work that needs someone who understands it. A warehouse does that invisibly, which is worth a great deal to a small team. **What would change my answer**: team size and workload mix. A five-person team doing SQL analytics should take the warehouse and not think about it again; a platform serving Spark, ML and SQL over the same petabytes should take open table formats and accept the maintenance.

The reasoning

dbt is not really an engine; it is dependency management, testing and documentation around SQL that the warehouse executes. That is why it wins so often: the work is SQL, the warehouse is already paid for, and the surrounding machinery is what teams actually lack. Lineage and tests come from the same definitions, and an analytics engineer can own the whole thing.

Spark is a general distributed compute engine, and it earns its place where SQL stops. Parsing unstructured or deeply nested input, feature engineering that is genuinely procedural, custom algorithms, or transformation of data that has not landed in the warehouse — those are Spark's, and forcing them into SQL produces something nobody can maintain.

The healthy arrangement in most platforms is both with a clear seam: Spark handles ingestion and anything procedural, landing well-shaped tables; dbt models everything from there. The pathologies are at both extremes — a Spark job doing what a fifteen-line dbt model would do, and a five-hundred-line dbt model with nested CTEs reimplementing a loop. **What would change my answer**: the shape of the input. Structured and already landed is dbt's; anything else is worth reconsidering.

The reasoning

SQL's advantages compound. It is declarative, so the engine chooses the execution and adapts as data grows. It runs where the data already is, avoiding movement. And the readership is much wider — analysts, analytics engineers and data engineers all read SQL, while Python narrows it to a subset of them. For set-oriented work, which is most of what a pipeline does, it is simply the better tool.

Python earns the switch when the logic is procedural in a way SQL only simulates: iterative algorithms, calling an external service per row, parsing irregular text, anything needing real control flow or libraries. A recursive CTE that reimplements a loop, or a window-function stack nobody can follow, is SQL being pushed past where it helps.

There is also a threshold that is about people rather than capability. When a model reaches several hundred lines of nested CTEs, the fact that it is expressible in SQL stops being the point — nobody can safely change it. The honest response is usually to decompose it into intermediate models rather than to rewrite it in Python, because the problem was structure, not language. **What would change my answer**: needing a library, an external call, or genuine iteration — anything else and SQL is still the better bet.

The reasoning

The decisive property is recovery granularity. A monolithic job that fails at step nineteen of twenty restarts at step one, redoing eighteen successful steps — which at 3am is the difference between four minutes and forty. Split into tasks, you clear the failed one and continue, and the eighteen that worked stay worked.

Small tasks bring other things with them. Failures are attributable — you can see which step broke rather than reading a stack trace from a job that does everything. Independent branches run in parallel. Resources can be sized per task rather than at the maximum any step needs. And each piece is separately testable.

The cost is real though. Every task boundary costs scheduling latency and often container startup, so a hundred tiny tasks can spend more time starting than working. State has to move between them through storage rather than memory. And the graph becomes something a person has to hold in their head at 3am, which is its own kind of complexity. The useful heuristic is that a task should be the smallest unit you would want to rerun on its own — **what would change my answer** is discovering that the steps are so tightly coupled that rerunning one alone is never meaningful.

The reasoning

The comparison people make is fee against infrastructure cost, and it is the wrong one. The real self-hosted cost is upgrades, security patching, scaling the scheduler, debugging executor problems at 2am, and the on-call rota that has to exist for a system your entire data platform depends on. For a small team that is a substantial fraction of a person, and it is spent on something that produces no data.

Self-hosting genuinely wins in three situations. You already run Kubernetes with a platform team, so the marginal cost is small. You have a regulatory or network constraint that rules out the managed option. Or you are at a scale where the fee has grown past what dedicated engineers would cost, which does happen and is a legitimate reason to move.

What deserves more weight than either is exit cost. Managed offerings differ in how much they lock you in: a hosted version of an open-source project you could run yourself is a very different bet from a proprietary service with no equivalent. Preferring the former keeps the decision reversible cheaply. **What would change my answer**: the arrival of a platform team, or the invoice crossing the cost of the engineering time it was replacing.

The reasoning

Warehouse-native scheduling is genuinely good for warehouse-shaped work. There is nothing else to run, nothing else to secure, and the tasks sit next to the data. For a platform where a managed connector lands data and dbt models it, that may be the entire pipeline, and adding Airflow to it is complexity with no return.

An external orchestrator wins when work spans systems: extract from an API, drop a file, load it, transform, then notify a downstream service or refresh a dashboard. The warehouse can only see and sequence the part that happens inside it, so a cross-system dependency has to be expressed somewhere else — usually as a sleep, which is the design people end up with by accident.

The other arguments are about capability and portability. Retries, backfills over a date range, sensors, SLA tracking and a lineage-aware view of the whole graph are things dedicated orchestrators do well and warehouse schedulers mostly do not. And a warehouse-native schedule is a warehouse-specific asset that does not migrate. **What would change my answer**: the first dependency that crosses a system boundary, or the first time someone needs a backfill over a range rather than a rerun.

The reasoning

A cron schedule encodes an assumption: that the upstream will be ready by then. When the assumption holds it is beautifully simple. When upstream runs late, the job runs anyway and processes whatever is there — which is often nothing, and a successful task that produced zero rows is far more dangerous than a failure, because nothing alerts and the downstream now has a hole.

Event-driven triggers remove the assumption. The job runs when the file lands or the upstream task completes, so it processes data that exists by definition. The costs are that timing becomes unpredictable — no fixed window to reason about — and that you now need to detect the event *not* happening, which is a different and harder alert than a failed task.

There is a third setting that decides what happens after an outage, and it is the one people get wrong: catchup. With catchup enabled, a DAG paused for three days will schedule all the missed intervals when it resumes — which is correct for an idempotent, window-parameterised job and catastrophic for one that is neither, because it runs seventy-two hourly loads at once. Disabling catchup avoids the stampede and silently skips those windows, so the data is simply missing. Neither default is safe on its own; the safe combination is catchup on plus a load that is idempotent over its window, plus a concurrency cap.

The arrangement most mature platforms land on is both triggers: a data-availability condition plus a deadline. Run when the upstream is ready, and if it is not ready by an agreed time, that is an SLA breach someone hears about. Airflow datasets and sensors with timeouts, and Dagster's asset-based scheduling, are both this idea. **What would change my answer**: an upstream whose completion you cannot observe at all — then cron plus a freshness assertion on the input is the honest fallback.

The reasoning

When transformation logic sits inside orchestrator tasks, testing it requires the orchestrator's runtime — its context objects, its connections, its execution model. That makes local development awkward and unit testing close to impossible, which in practice means the logic is not tested at all. It also means an orchestrator migration is a rewrite of your business logic rather than a rewrite of your scheduling.

The alternative is thin tasks that invoke something self-contained: run this dbt selection, run this container, call this function in a library you can import. The logic is then testable in isolation, runnable on a laptop, reviewable on its own, and portable. The orchestrator keeps what it is genuinely good at — dependencies, retries, scheduling, backfills, visibility.

The pragmatic exception is glue: a few lines to build a path, pick a date partition or format a notification are fine in a task and would be silly to extract. The line is whether a data consumer would recognise it as a business rule. **What would change my answer**: essentially nothing about scale — this is a discipline that gets *more* valuable as the platform grows, which is why it is worth holding early.

The reasoning

The genuine problem with three schedulers is not duplication of infrastructure, it is that a dependency between teams cannot be expressed. Team B's job needs Team A's table, and with separate graphs the only available mechanism is a time offset and hope. When A runs long, B processes stale or missing data and nothing notices — which is the same silent failure as bare cron, at organisational scale.

The cost of consolidating is real and usually understated. One platform means one upgrade cycle that must suit everyone, one place a bad job degrades everybody, one team owning it, and often a queue for changes. Teams that were autonomous become dependent on a central function, and that friction is what makes consolidation efforts stall.

The resolution is to separate the two things being conflated. What must be shared is the *dependency and lineage layer* — a way for B to depend on A's output and to find out when it is late. That can be a shared asset catalogue, a data-availability event, or a contract with a freshness SLA, none of which require one deployment. **What would change my answer**: if the teams have no cross-dependencies at all, leave them alone; the consolidation would be tidiness with no benefit.

The reasoning

The overhead is per task and it is not small: scheduler latency, worker assignment, container or JVM startup, connection setup, and often reading state from storage that the previous step just wrote. When a task's real work is two seconds and its overhead is twenty, splitting has made the pipeline slower and the bill larger for no operational gain.

The gain a boundary is supposed to buy is independent rerunnability. If steps four through nine always fail together and are never rerun individually — because rerunning one alone produces nothing meaningful — then those five boundaries cost overhead and buy nothing. That is the test to apply: would anyone ever rerun this step by itself?

The fix is to group by shared failure and rerun semantics rather than by logical step. Keep boundaries where the work is genuinely long, where an external system is involved, or where the downstream half is expensive enough that you would not want to redo it. **What would change my answer**: task duration. When each step takes twenty minutes, the overhead is noise and fine-grained boundaries are free; when each takes two seconds, they are the dominant cost.

The reasoning

At ingestion you are checking someone else's promise: schema, types, nullability, referential sanity, plausible volume. Failing here is cheapest because nothing downstream has run, and it puts the problem where it belongs — with the producer, with evidence attached.

After transformation you are checking yourself: grain uniqueness, no unexpected fan-out from a join, totals reconciling against the source, business invariants like non-negative revenue. Ingestion checks cannot catch these because the data arrived fine and your code broke it, and they are the class of bug that survives longest without a check.

Before serving you are checking the consumer's expectations: freshness against the SLA, row counts within a reasonable band, the specific columns a dashboard depends on. This is the last chance to stop a wrong number reaching a person. The general principle is that a check catches only what it is positioned to see, and the cost of catching late is everything computed in between. **What would change my answer**: nothing about where — but on a tight budget, ingestion checks first, because they are cheapest to run and cheapest to act on.

The reasoning

Frame it as a comparison between two harms. Blocking means the consumer has yesterday's data or none; publishing means they have data that might be wrong. For a finance table feeding a regulatory filing, wrong is far worse than late, so it blocks. For an internal dashboard where a slightly odd row count is tolerable, absent is worse than imperfect, so it warns.

That framing gives you a per-check rule instead of a global policy, which is what makes gates survive. Uniqueness violated on a primary key blocks, because every downstream join is now wrong. Row count 5% below the seven-day average warns, because it is a signal and not a proof. Referential integrity broken on a critical dimension blocks; a new unexpected category value warns.

Two operational points matter as much as the rule. A blocking check must be trustworthy — a gate that fires falsely twice gets disabled by someone under time pressure, and then it protects nothing. And warnings need a route to a human who acts, or they accumulate into a channel nobody reads, which is functionally the same as having no check. **What would change my answer**: a check that has been flapping should be downgraded to a warning until it is trustworthy again, whatever its severity says.

The reasoning

A registry moves enforcement to the producer's deployment: they publish an Avro schema, CI checks the new version against a compatibility rule, and a breaking change fails their build before a single bad message is emitted. The rule is the part worth knowing by name. BACKWARD compatibility means a new schema can read data written with the old one, which is what protects *you* as the consumer and is the usual default. FORWARD means the old schema can read new data, which protects producers who deploy ahead of consumers. FULL is both. Adding an optional field with a default is backward compatible; removing a required field or renaming one is not, which is exactly why a rename has to be an add-then-drop across two releases.

It requires the producer to adopt it, and that is an organisational commitment. They need to publish schemas, run compatibility checks in CI, and accept that your consumption constrains their release. Teams who do not see themselves as data producers often decline, and there may be no lever to change that.

Assertions on landing need no agreement at all. You declare the shape you expect, check it before anything downstream runs, and fail loudly with a message naming the producer and the field. It is detection rather than prevention, so the bad change already shipped — but it converts a silent corruption into a specific, attributable failure, which is most of the value and is available immediately. **What would change my answer**: nothing about the ordering. Build assertions first regardless; they are also the evidence that makes the registry conversation persuasive.

The reasoning

The instinct to blame the producer is understandable and usually wrong. They own an operational system, they changed a column in it, and nothing told them anyone was depending on that column. Expecting them to guess is not a workable model, and framing it as their failure is how a data team acquires a reputation that makes every future conversation harder.

What was missing is an explicit interface. A data contract makes a specific subset of the schema a published, versioned thing the producer has agreed to support — distinct from their internal tables, which they may change freely. Once that exists the answer is unambiguous: break the contract and it is on the producer, depend on something outside it and it is on you.

Making that real needs three things beyond the document. The producer needs to know what depends on them, which is what lineage is actually for. There has to be a change process — announce, deprecate, then remove — rather than a same-day rename. And it needs an owner on each side, because a contract nobody is accountable for is a wiki page. **What would change my answer**: if a contract existed and they broke it, then it is squarely theirs, and that clarity is the entire point of having one.

The reasoning

Prioritise by what fails quietly. A column that goes null makes charts look odd and someone reports it; a duplicated grain silently doubles revenue and nobody does. So the highest-value tests are grain uniqueness, referential integrity to dimensions people join to, and business invariants such as revenue never being negative and a count never exceeding its parent. These catch the errors that otherwise reach a decision.

Then weight by consequence. A model feeding the board pack or a regulatory return deserves more than one feeding an exploratory dashboard. Tagging models by criticality and testing accordingly is a more defensible policy than testing everything equally, and it is a conversation the business can participate in.

Cost is a real constraint at scale — a full test suite that scans every table nightly can become a visible line on the warehouse bill. Practical responses: test recent partitions rather than full history, sample the large tables, and run the expensive suite on a schedule rather than on every model. And the most reliable source of new tests is incidents: every one should end with a test that would have caught it. **What would change my answer**: the criticality tier — for a regulatory table the cost argument stops applying and you test it fully.

The reasoning

**Copy-on-Write** pays the whole cost at write time: locate the affected files, rewrite them, swap the metadata. Afterwards the table is plain Parquet and a reader does no reconciliation work at all. Predictable query latency, expensive updates.

**Merge-on-Read** does the opposite. An update writes a small delta or log file and records it; the base files are untouched. Writes are cheap enough to absorb a high-frequency CDC stream. The cost moves to the reader, which must merge base plus every outstanding delta, resolve by key, and apply deletes before returning a row — so query latency grows with how much has changed since the last compaction. Finding the right file to update needs a record-key index (bloom, hash or a metadata index) rather than min/max statistics alone, because the point is to avoid touching the base file.

**The layer mapping follows from the workload.** Bronze takes a stream of millions of small updates and is queried rarely, mostly for replay — Merge-on-Read, with frequent minor compaction. Silver is written in batches and read by many jobs — Merge-on-Read with scheduled major compaction, or Copy-on-Write if updates are rare enough. Gold is read constantly by dashboards and rebuilt rather than updated — Copy-on-Write, and usually no need for Merge-on-Read at all.

The honest caveat: **Merge-on-Read is a promise to compact.** Its performance profile is only acceptable if a compaction job actually runs, and a Merge-on-Read table nobody compacts degrades from "fast writes" to "unqueryable" over weeks. Choosing it is choosing to operate something — so when nobody will own that job, choose Copy-on-Write and pay at write time instead.

**What would change the layer mapping**, because it is a default rather than a rule. A bronze table that starts being queried interactively rather than only replayed flips to Copy-on-Write, even though its write pattern has not changed at all — the read cost has simply stopped being free. And a silver table that starts absorbing a high-frequency CDC stream flips the other way, because the rewrite cost per update overtakes the merge cost per read. The workload decides, and the workload moves.

The reasoning

**On-demand** scales with your traffic and charges per unit of data. You do not size, monitor or reshard anything, which is exactly what you want when you do not yet know the traffic shape, when it is bursty and unpredictable, or when the team has no capacity to operate a scaling policy. It costs more per byte.

**Provisioned** means you declare the capacity and pay for it whether you use it or not. It is cheaper at sustained volume and it gives you a predictable bill, which matters more than it sounds when someone has to forecast next quarter. In exchange you own the sizing, the monitoring for throttling, and the resharding when volume grows.

The switching signal is not a throughput number, it is **predictability**. Once you have weeks of traffic data showing a stable shape, provisioned capacity is a calculation rather than a guess. Before that, provisioning is guessing with a penalty at both ends — throttled writes if you undershoot, wasted spend if you overshoot.

Two details worth knowing. Providers **limit how often you can switch modes** — AWS caps Kinesis mode changes within a 24-hour window — so this is not a knob to flip during an incident. And on-demand is not unlimited: it scales within bounds and can still throttle if traffic jumps faster than it can respond, which is why a genuinely spiky workload still needs producer-side retry with backoff.

The reasoning

One stream is genuinely simpler when the events share a domain, an SLA, a retention requirement and a consumer set: fewer things to provision, monitor and secure, and consumers that need everything get it in one subscription.

They stop sharing quickly, and each divergence is a reason to split. **Criticality** — a payment event and a page-view event do not deserve the same recovery urgency at 3 a.m. **Traffic shape** — clickstream is one or two orders of magnitude heavier and burstier, and mixing it with transactional traffic means a marketing campaign can throttle order ingestion. **Retention** — audit records may need a year, clickstream a week, and retention is set per stream. **Security** — payment payloads and page views should not carry the same access policy, and the coarsest control you have is per-stream.

The noisy-neighbour argument is the one that decides it in practice: **failure isolation is a property of the split.** On a shared stream, one misbehaving producer degrades every consumer, and the blast radius of a bad deploy in the least important service is the most important one.

The cost of splitting, stated fairly: more infrastructure to provision and monitor, and a consumer that genuinely needs several streams now has to read several and reconcile ordering between them — which is fine, because there was never ordering *between* keys on a shared stream either.