Data Pipeline Theoretical

The reasoning

A pipeline exists because the place data is made, an app database, a payment service, a sensor, is the wrong place to analyse it. The pipeline copies the data out, cleans it, reshapes it, and lands it where analysts, dashboards and models can read it, on a schedule or continuously.

The stages are the same in every design. Ingestion captures data from sources, by pulling files or query results, or by receiving events. Storage holds it, usually in object storage as files. Transformation cleans, joins and aggregates it. Serving exposes the result through a warehouse, a table format or an API.

Two things sit around the stages. Orchestration decides what runs when, and in what order, with retries. Observability watches each stage for failures, lateness and wrong numbers, and tells someone.

A good interview answer names the stages, then says what each one must guarantee. Ingestion must not lose events. Storage must be replayable. Transformation must be idempotent. Serving must be fresh enough for its readers.

The reasoning

ETL is extract, transform, load. A job reads the source, cleans and joins the rows in its own compute, and writes only the finished result. Informatica, SSIS and many Spark jobs work this way. The warehouse never sees the raw data.

ELT is extract, load, transform. The raw data lands first, as it arrived, in cheap storage or a raw schema. Then SQL inside the warehouse, often managed by dbt, builds the clean tables. Fivetran into Snowflake with dbt on top is the common example.

Three things moved with the letter. The compute bill moved into the warehouse. The logic moved into SQL that analysts can read. And the raw copy now exists, so a bug is fixed by re-running the transform, not by re-extracting.

ETL is still right when the raw form must not land at all, such as regulated personal data, or when the source is too large to copy whole. Most new platforms default to ELT and transform before loading only where a rule forces it.

The reasoning

Without CDC you copy the whole table every night. With CDC you receive each change as an event: operation type, the row before and after, and the commit position. The warehouse applies those changes and stays close to the source.

Log-based CDC uses a tool such as Debezium to read the transaction log. It sees every change, including deletes and intermediate states, in commit order, with little load on the source. It needs a privileged connection, log access and a connector to operate.

Query-based CDC runs a query on a schedule against a modified-time column. It is simple and needs no special access. It misses deletes unless the source soft-deletes, misses rows that changed twice between runs, and depends on the column being set on every change.

Either way, the consumer must apply changes in order per key and be idempotent, because the capture path can replay. The commit position, such as the Postgres LSN, is the ordering key.

The reasoning

A full load is the simplest correct design. Read everything, write everything, done. It handles deletes for free because the target is replaced, and a rerun is harmless. Its cost grows with the table, so it stops being practical at some size or some freshness requirement.

An incremental load reads only the rows beyond a stored watermark, or the changes a CDC feed delivered. It needs three things: a trustworthy signal of what changed, a key to merge on, and a stored position that is advanced only after a successful write.

Incremental loads fail quietly. A row updated without its modified-time column changing is never picked up. A clock skew between source and loader skips rows. A delete that leaves no trace is never applied. A full load has none of these problems.

The usual practice is incremental for the daily cost and a periodic full rebuild, or a reconciliation count, as the correctness check.

The reasoning

Events arrive out of order. A window for 10:00 to 10:05 cannot close at 10:05 by the wall clock, because an event stamped 10:03 may still be in flight. The watermark is the engine's rule for when to stop waiting.

In Spark Structured Streaming, `withWatermark('event_time', '10 minutes')` sets the watermark to the maximum event time the engine has seen minus 10 minutes. When the watermark passes a window's end, the window is finalised and its state can be dropped.

Events older than the watermark are late. The Spark guide says data less than the delay late is never dropped, and data later than that is not guaranteed to be kept. Flink's watermarks work the same way, with allowed lateness and side outputs for what arrives after.

The delay is a trade. A long delay waits for stragglers and holds more state. A short delay answers sooner and drops more. You choose it from the measured arrival-lag of your source, not from a guess.

The reasoning

A phone records a play at 09:58, loses signal, and uploads it at 10:40. The event time is 09:58. The processing time is 10:40. Ingestion time, a third stamp, is when the event entered the stream.

Group by processing time and the play lands in the 10:40 window. The 09:55 to 10:00 window is wrong forever, and a replay of the same data gives a different answer. Group by event time and the play lands where it belongs, as long as the window was still open.

Event time needs watermarks, because the engine must decide how long to wait for stragglers. Processing time needs nothing, which is why it is tempting, and why it is only acceptable for metrics about the pipeline itself.

Keep both stamps on every row. Event time for the business answer. Processing or ingest time to measure lag and to debug.

The reasoning

Tumbling windows cut time into equal, non-overlapping blocks: 10:00 to 10:05, 10:05 to 10:10. Each event belongs to exactly one window. Use them for counts per interval, such as orders per minute.

Sliding windows have a size and a step. A 5-minute window every minute produces windows 10:00 to 10:05, 10:01 to 10:06, and so on. Each event belongs to several windows. Use them for moving averages and rolling alerts. They cost more state because each event is counted in size-over-step windows.

Session windows are defined by a gap. For one user, events closer together than 30 minutes join one session; a gap of 30 minutes ends it. Their length depends on the data, and the state is held per key until the gap passes. Use them for user sessions and device activity.

In every case the window groups the data and the watermark decides when the group is complete. Without a watermark, a window on an unbounded stream never closes.

The reasoning

The three names describe what happens around a failure. If the consumer commits its position before processing, a crash loses the batch in hand: at-most-once. If it commits after, a crash replays the batch: at-least-once. There is no ordering of commit and write that gives neither.

Exactly-once inside one system is real. Kafka transactions make a read-process-write between topics atomic. Flink and Spark checkpoint source offsets with operator state so a restart neither skips nor double-counts within the job.

Across a boundary the system does not control, a database, an API, an object store, the guarantee becomes effectively-once: at-least-once delivery plus a write that is safe to repeat. A `MERGE` on a key, a partition overwrite, or an insert that checks an event id.

So the honest answer names the layer. Delivery is at-least-once. The sink is idempotent. The pair gives the business the result of exactly-once without the word.

The reasoning

The word comes from mathematics: applying the operation twice is the same as applying it once. In a pipeline, that means running yesterday's job again today, or retrying a failed task, leaves the target exactly as a single clean run would.

Append is not idempotent: a rerun adds the rows again. The idempotent patterns replace rather than add. `MERGE` on a business key updates existing rows and inserts missing ones. `INSERT OVERWRITE` of a partition replaces that slice completely. `INSERT ... WHERE NOT EXISTS` skips keys already present. A full refresh replaces everything.

Each pattern assumes something about the batch. A merge needs a reliable key. A partition overwrite needs the batch to be the complete slice. An insert-if-absent needs a stable event id. A full refresh needs the source to be readable whole.

Idempotency also applies upstream. An extraction advances its watermark only after the write commits. A producer gives every event a unique id, so the consumer can drop repeats.

The reasoning

When one record fails schema validation or a business rule, you have two choices. Fail the whole job, which stops good data too, or set the record aside and continue. The dead-letter queue is the place it is set aside.

A useful DLQ record has the raw payload exactly as received, so it can be replayed after a fix. It has the error message and the validation rule that failed. It has the source topic, partition and offset or the file name and line, and the time it was seen. Without that metadata, the record is a mystery.

The queue must be watched. A DLQ nobody reads is a silent data loss with extra steps. Alert on its growth, and build the replay path: fix the schema or the rule, then re-process from the DLQ with the same idempotent write as production.

Kafka Connect has a built-in dead-letter topic for sink connectors, with error context in headers. Spark and Flink jobs implement it as a side output written to its own table or prefix.

The reasoning

A job that runs for hours will sometimes die. Without a checkpoint it starts over, and if it had written partial output it also has to undo that. With a checkpoint it reads the last committed position and resumes.

In Spark Structured Streaming and Flink, the checkpoint holds three things. The source offsets planned for each batch. A commit log of finished batches. And the state of running aggregations, joins and deduplication. Flink takes consistent snapshots of all operators at once; Spark writes them per micro-batch.

In batch, the checkpoint is simpler: a watermark table holding the last processed timestamp, or a manifest of partitions already written. The rule is the same: advance it only after the output is durably committed, never before.

A checkpoint ties the job to its logic. Changing an aggregation or the state schema usually needs a fresh checkpoint, and deleting the checkpoint means reprocessing from the configured start, which the sink must survive.

The reasoning

Bronze keeps the source's truth. Files land as received, with ingestion metadata added: file name, load time, batch id. Duplicates from overlapping extracts are allowed, because bronze is a log, not a table. The one rule is that nothing in bronze is ever modified or deleted except by retention.

Silver is where trust is created. Types are cast, nulls handled, duplicates removed by key, formats standardised, and bad rows quarantined. Tables are still one per source entity, so silver is easy to reason about and easy to rebuild from bronze.

Gold is for readers. Dimensional models, wide tables for a dashboard, aggregates at the grain a team needs. Gold tables may be rebuilt entirely from silver, and several gold tables may serve the same silver table to different teams.

The layers give you replay. A bug in silver logic is fixed by re-running from bronze. A bug in gold is fixed from silver. Nothing is ever re-extracted from the source to fix a transform.

The reasoning

A normal run processes the newest slice. A backfill processes old slices again: the 6 days a job was broken, the 2 years a new metric needs, the month a transform was wrong. The logic is the same; the inputs and the volume are not.

Scope it. Name the partitions or the date range, and nothing else, so the backfill cannot touch today's data by accident. Isolate it: its own pool, cluster or queue, so it does not starve the live run that readers depend on.

Write it the way production writes. Partition overwrite or keyed merge, never append, so a backfill run twice is harmless. Then validate: row counts and totals against the source or the previous version, before anyone reads the result.

Watch the downstream. A backfilled silver table does not fix the gold aggregates built from it. The backfill has to continue down the lineage, or the two layers disagree.

The reasoning

Schemas change because products change. A new field appears, a field is renamed, a type widens from int to long. Evolution is the discipline of making those changes without a 2 a.m. page.

Backward compatibility protects history. A consumer on the new schema can still parse everything written under the old one. That is what you need to replay bronze with today's code. Adding a field with a default keeps it, because old data simply lacks the field and the default fills in.

Forward compatibility protects slow consumers. A reader on the old schema can still parse data written under the new one, ignoring what it does not know. Adding an optional field keeps it too. Removing a field the old reader required breaks it.

Full compatibility is both at once, and it is what a schema registry enforces by default in many setups. The safe changes under all three are: add optional fields, widen types where the format allows, and never rename or remove a required field without a versioned migration.

The reasoning

Without a registry, every producer embeds the full schema or none, and a breaking change is discovered by the consumer that crashes. With a registry, the producer's serializer registers the schema before the first write, and the registry checks it against the previous versions.

The compatibility rule is set per subject. Confluent's registry offers BACKWARD, FORWARD, FULL and their transitive forms, with BACKWARD as the default. A change that fails the rule is rejected at the producer, before any message exists.

On the wire, a Confluent-serialized message starts with a magic byte and a 4-byte schema id, followed by the Avro, Protobuf or JSON Schema payload. Consumers cache schemas by id, so the registry is read rarely and never on the hot path.

The registry does not fix consumers that pin an old schema and need a renamed field. It stops the change at the door; the rollout order for a rename is still a human process.

The reasoning

The problem a contract solves is the 2 a.m. rename. A source team changes its own table, because it is their table, and 12 downstream models fail. Nobody was wrong, because nothing said the column was depended on. The contract makes the dependency explicit.

It contains the schema, with types and nullability. It states the meaning of each field, including units and time zones, and the grain of the table. It states the freshness and completeness the producer commits to, and the quality rules that hold. And it sets a change process: a deprecation notice, versioning, a date.

Ownership sits with the producer, enforced at their boundary: a schema registry rule, a test in their CI, a versioned API. Consumers' tests on their own side are a backstop, not a contract; they only find the breach after it happened.

Tools vary: a registry for streams, dbt model contracts for warehouse tables, an OpenAPI spec for an API. The idea is the same in each: the shape is declared, checked by a machine, and changed on purpose.

The reasoning

Checks at landing protect everything downstream. Does the file have the expected columns and types? Are required fields present? Is the row count in the normal range for this source? These catch a broken extract before it becomes a broken table.

Checks after the transform protect the model. Is the key unique at the declared grain? Do foreign keys resolve to a dimension row? Are amounts positive, dates in range, codes in the allowed set? These catch join fan-out and bad logic.

Checks before serving protect the reader. Is the table fresh enough for its SLA? Does today's total reconcile with the source? Is the volume within a few standard deviations of normal? These catch the silent failures where everything ran and the number is wrong.

Each check needs a verdict: block the publish, or warn and continue. Block when a wrong number is worse than a missing one. Warn when stale-but-correct is acceptable. Decide per check, not globally.

The reasoning

Pick a slice both sides can see, such as yesterday by event date. Count rows in the source and in the target. Sum the amount column on both sides. Count distinct order ids. If the three numbers match, the data moved whole.

A mismatch tells you where to look. Target count low: rows dropped, a filter too strict, a failed partition. Target count high: duplicates, join fan-out, a rerun that appended. Sum differs with equal counts: a type cast, a currency, a null treated as zero.

Reconciliation runs late, after the load, so it is a detector, not a gate. It should block the publish of the slice it checked, and it should alert with the two numbers, not just a failure.

It cannot see logic errors that keep totals equal, such as a wrong join key that maps every order to the wrong customer. That needs sample-based checks and a human reading the output.

The reasoning

Lineage is a graph. Nodes are tables, files and models. Edges are the jobs that read one and write another. At column level, an edge says `fct_orders.net_amount` is computed from `stg_orders.amount` minus `stg_refunds.amount`.

It answers two directions of question. Downstream, impact analysis: this source column is being renamed, list every table and dashboard that depends on it. Upstream, root cause: this number is wrong, walk back to the job and the input that produced it.

Capturing it by hand fails within a month. The durable sources are automatic. dbt's manifest knows every `ref` and `source`. Spark and warehouse query logs can be parsed into column lineage. Orchestrators emit OpenLineage events, and Unity Catalog and Purview collect them.

Lineage is also the basis for governance: proving to an auditor that personal data never reached a table it should not, and finding every copy when an erasure request arrives.

The reasoning

Freshness is measured on the table: now minus the maximum event time or load time it contains. "The orders table is never more than 15 minutes stale" is a freshness SLA. It is the one a dashboard cares about.

Latency is measured per record: the time between an event happening and it being queryable. A stream with 2-second latency and a nightly batch with 8-hour latency can both be fresh at the moment you look. Latency is what a fraud system cares about.

Completeness is measured against an expectation: 94 percent of yesterday's mobile events have arrived by midnight, 99.4 percent by 02:00. It is why a report published early carries a provisional flag, and why late data is a completeness question before it is a lateness question.

An SLA names one of these, a threshold, a deadline and an owner. Monitoring then computes the measure continuously and pages when the threshold is crossed, which is a different thing from alerting that a task failed.

The reasoning

Ingestion's job is to get the data in and keep it. For files, that means copying them to a raw prefix with their name and arrival time. For databases, a full load plus CDC. For events, a producer writing to a durable stream such as Kafka or Kinesis that decouples it from consumers.

It should do light work only. Check that mandatory fields exist, that the schema version is known, and that the payload is within size limits. Attach an event id, a source and a timestamp. Records that fail go to a failure store, not into the stream.

It should not transform. A join or an enrichment at ingestion couples the door to a dimension table, slows every event, and makes the raw layer untrue to the source. Those belong in silver.

Its guarantees are durability and replay: nothing acknowledged is lost, and a consumer that fails can read the data again from retention rather than asking the producer to resend.

The reasoning

Bronze and silver are shaped by how data arrives. Serving is shaped by how it is asked for. A dashboard wants wide, pre-aggregated tables. An application wants a single row by key in milliseconds. A data scientist wants columnar files and all the history.

So serving may hold several copies. The gold tables in the warehouse for SQL and BI. A Redis or DynamoDB view for a product feature. A materialized view or an OLAP store such as ClickHouse or Pinot for sub-second dashboards.

It also enforces access: row and column permissions, masking of personal data, and the semantic layer that keeps a metric defined once.

The serving layer is where freshness is measured, because that is where it is felt. A pipeline's SLA is a statement about this layer.

The reasoning

A lake stores everything: raw JSON, logs, images, Parquet. It is cheap and open, any engine can read it, and it has no rules until you add them. Its failure mode is a swamp of files nobody can trust.

A warehouse stores structured tables with enforced schemas, ACID transactions, indexes or clustering, and a query engine built for them. It is fast and governed, and it is a closed format that only its own engine reads, priced per query or per node.

A lakehouse keeps the lake's storage and openness and adds a table format, Delta, Iceberg or Hudi, that records which files make up a table at each version. That gives transactions, time travel, schema enforcement and efficient updates on object storage.

In practice most platforms are both. A lakehouse holds raw and curated data that several engines share. A warehouse or a serverless SQL engine serves BI on top.

The reasoning

An OLTP query is "fetch order 1042 and update its status". It touches one row, needs every column of it, and must finish in milliseconds while thousands of similar queries run. A row store with a B-tree index on the key is built for exactly that.

An OLAP query is "total revenue by country for last quarter". It touches 50 million rows but reads 3 columns of 40. A columnar store reads only those 3 columns, compresses them well because values in one column are alike, and skips whole files by their min and max statistics.

Running analytics on the OLTP database hurts both: the scan competes with transactions, and the row layout reads 40 columns to use 3. That is why data is copied into an analytical system, and why the pipeline that copies it exists.

The same split explains write patterns: OLTP updates rows in place, OLAP appends and rewrites partitions, which is why updates on a lake need a table format.

The reasoning

In a row layout, order 1 is followed on disk by order 2. Reading the `amount` column means reading every byte of every order and keeping one field. Writing a new order is one append, which is why OLTP uses it.

In a columnar layout, all `amount` values sit together, then all `country` values. A query for `sum(amount)` reads one contiguous run of numbers and nothing else. Parquet does this per row group, with statistics per column chunk so a filter can skip chunks.

Compression is better because a column is homogeneous. A run of country codes dictionary-encodes to small integers; a sorted date column run-length-encodes; an increasing id delta-encodes. Row data mixes types and compresses poorly.

The cost is on writes and point reads. Assembling one whole record means reading from every column, and appending one row means touching every column chunk, which is why columnar files are written in batches.

The reasoning

CSV is the exchange format. Anyone can produce it, every tool reads it, and it has no types, no nesting and no compression of its own. A number is a string until something parses it, and a comma inside a value breaks a naive reader.

JSON carries structure: nested objects, arrays, optional fields. APIs and event producers emit it. It is still text and still row-based, so analytics over it means parsing every record and reading every byte. Newline-delimited JSON is the streaming-friendly form.

Parquet stores a schema, writes columns in row groups with min and max statistics per column, and compresses each column with an encoding that suits it. An engine reads only the columns and row groups a query needs. It is not human-readable and it is written in batches, not one record at a time.

So the layers follow the formats. Land CSV and JSON as they arrive in bronze. Convert to Parquet in silver, and query Parquet in gold. Avro sits beside them as a row-based binary format with schema evolution, used on the wire more than at rest.

The reasoning

Partition by `dt` and you get one folder per day, which a filter on `dt` can skip. Try to partition by `user_id` and you get millions of tiny folders. Bucketing solves that: hash `user_id` modulo 64 and you get 64 files, each holding a slice of users.

The join benefit comes from the hash. If `orders` and `customers` are both bucketed into 64 on `customer_id`, bucket 17 of each holds the same customers. An engine can join bucket to bucket with no shuffle, which removes the most expensive part of the join.

The conditions are strict: same column, same hash function, same bucket count, and both sides written as bucketed tables. Change the count on one side and the benefit is gone. Spark's bucketing and Hive's are not compatible with each other.

Table formats reach the same goal differently: Iceberg has a bucket partition transform, and Delta uses clustering and Z-order to keep related rows together.

The reasoning

Filtering events with `amount > 0` needs nothing but the event. Parsing JSON, renaming fields and looking up a country from a small table are the same. These scale by adding consumers and recover by restarting; there is nothing to lose.

Counting orders per minute needs the running count. Joining clicks to impressions needs the impressions held until the click arrives. Dropping duplicate event ids needs the set of ids seen. That memory is state, and it lives in the engine's state store, RocksDB in Flink and Spark.

State must survive failure, so the engine checkpoints it. It must not grow forever, so windows close and watermarks release old keys. It must be partitioned by key so each task owns its share, which is why stateful operators force a shuffle by key.

Scaling a stateful job means moving state between tasks. That is why changing the parallelism of a Flink job needs a savepoint, and why a Spark state schema change needs a new checkpoint.

The reasoning

Vertical scaling is simple and limited. A bigger instance has more cores and memory, nothing changes in the code, and there is a largest instance you can buy. It also keeps one point of failure.

Horizontal scaling is how Spark, Kafka and the warehouses work. A 1 TB table becomes 5,000 partitions on 100 machines. A topic becomes 48 partitions across 12 brokers. Adding machines adds throughput, as long as the work splits evenly.

The requirement is that the work can be split by a key. A sum per country splits by country. A global median does not split, and neither does a join whose key is 60 percent one value. Skew is the point where horizontal scaling stops helping.

Shared state is the enemy of scale-out, which is why streaming engines partition state by key and why a global counter in a stream is an anti-pattern.

The reasoning

With schema-on-write, the writer declares columns and types, and a row that does not fit is rejected at load time. Errors surface early and once. The cost is that every change to the shape is a migration, and data that does not fit yet cannot land.

With schema-on-read, anything lands. A reader later says "treat this field as a date", and the error surfaces then, in every reader, every time. It is flexible for raw and semi-structured data and dangerous for anything shared.

The medallion layers resolve the tension. Bronze is schema-on-read so no source data is refused and replay is exact. Silver imposes a schema: it is the write that validates. From silver on, every table is schema-on-write, which is what a table format enforces.

A table format's schema enforcement is schema-on-write on a lake, which is what lets a lakehouse be trusted the way a warehouse is.