Overview

Not the result, but the shape of the input and when the result becomes available: batch processes a bounded dataset in one pass and produces a result once that pass finishes; streaming processes an unbounded, ongoing feed record by record, updating the aggregate continuously as each new record arrives.

The difference is purely when transformation happens: ETL transforms the data before loading it into the warehouse; ELT loads the raw data first and transforms it afterward, in-warehouse, using SQL. ELT became practical once cloud warehouses made elastic in-warehouse compute cheap enough that paying for transformation inside the warehouse stopped being a bottleneck.

Source → ingest (EL, extract and load) → transform (T, in-warehouse) → serve (BI/ML). Ingest and transform are split into two distinct stages because ELT loads raw data first and only transforms it afterward.

Bronze keeps source data exactly as it arrived, plus load metadata (e.g. a load_ts column); silver cleans, types, dedupes, and conforms it; gold serves consumption-ready, often aggregated data. Layering matters because it isolates raw data from what gets served -- a mistake made while cleaning silver never has to be reconciled against a raw bronze row that has already been overwritten, since bronze is never mutated.

A transform is idempotent when re-running it against the same input produces the same result as running it once -- no duplicated rows, no double-counted totals. It is usually achieved by merging or upserting on a natural key (an anti-join before insert, or a MERGE ... WHEN MATCHED / WHEN NOT MATCHED statement) rather than blindly appending every row on every run.

Incremental processing handles only the rows newer than a watermark, for efficiency on the common case. Backfill is the deliberate exception: reprocessing an explicit past date range, often the whole range again. Backfill is only safe because the transform is idempotent -- reprocessing rows that were already processed once must not duplicate or corrupt what is already there.

It encodes a column's value directly in the storage directory path, as key=value/ segments (e.g. region=east/). This lets a query engine prune -- skip reading -- every partition directory whose encoded value cannot match the query's filter, without opening or scanning those files at all.

A fact table holds numeric measurements (quantities, amounts) plus foreign keys pointing to the dimensions that give those numbers context. A dimension table holds the descriptive, mostly non-numeric attributes -- names, categories, dates -- that a fact's foreign keys reference.

The grain is the business definition of exactly what one fact row represents -- e.g. "one row per order line," not "one row per order." It must be declared first because every later modeling decision (which columns are facts vs. dimensions, whether a measure is additive) depends on knowing precisely what one row means; mixing rows at two different grains in the same fact table silently corrupts any aggregate computed over it.

Additive: revenue -- sums correctly across every dimension. Semi-additive: an account balance -- sums correctly across every dimension except time (summing balances across days produces nonsense). Non-additive: a ratio, like a conversion rate -- never sum a ratio directly; store its numerator and denominator as separate additive facts and divide them at query time, after aggregating each component.

Type 1: overwrite the value in place, no history retained. Type 2: insert a new row with an effective-date range and a current-flag, keeping every prior version. Type 3: add a dedicated column holding the one immediately-prior value alongside the current one. Type 6: a hybrid -- Type-2 historical rows, plus one column on each row that is kept overwritten Type-1-style to always reflect the current value.

A topic is modeled as one or more partitions, each an append-only log; every record appended to a partition gets a monotonically increasing offset unique within that partition. A consumer group assigns each partition to exactly one member of the group at a time, so within a group no two consumers ever process the same partition concurrently.

Ordering is guaranteed only within a single partition -- records appended to the same partition are read back in the same order. There is no ordering guarantee across different partitions of the same topic. The default delivery semantic is at-least-once: a retry after a failure can redeliver a message, producing a duplicate; exactly-once requires an idempotent, transactional producer on top of that default.

Tumbling: fixed-size, non-overlapping -- every event falls in exactly one window. Hopping: fixed-size but overlapping, because the window advances by less than its own size -- one event can land in more than one window. Session: variable-size, defined by an inactivity gap rather than a fixed duration -- a new session starts only after the gap has elapsed since the last event.

Event-time windowing buckets a record by when the event actually happened (a timestamp carried in the record itself); processing-time windowing buckets it by when the system happened to receive it, which can differ from event-time due to network delay or replay. A watermark tracks how far event-time processing has progressed and controls how long a window stays open waiting for late-arriving events before it is considered complete and emits its result.

Completeness, validity, uniqueness, timeliness, consistency, and accuracy. Example assertions: a completeness check that a required column is never null; a uniqueness check that a declared key never repeats within a batch. Each dimension names a specific, checkable class of assertion that can fail a batch before it reaches downstream consumers.

A data contract is a producer-side, enforceable guarantee about a dataset's shape -- its columns, their types, and constraints like not-null -- that lives close to the model or system producing the data. Rather than merely flagging a violation downstream after the fact (as a data-quality check does), a contract violation fails the build at the point of production, stopping schema drift from ever reaching a consumer in the first place.

Tasks (run by operators), their dependencies on one another, a schedule, and a retry policy. A given task runs once the scheduler determines every one of its declared dependencies has already completed successfully -- dependency-met, not simply "next in a list," is what triggers execution.

Table-level lineage records that dataset X feeds into dataset Y -- it answers "does this table feed that one," a coarse dependency question. Column-level lineage records, more precisely, which specific input column produced which specific output column via which transform -- it answers the finer "if I change this one column, exactly what downstream columns does that affect," which is what real impact analysis needs.

Query-based CDC polls a source table for rows that look newer than a cursor value it tracked last time. Log-based CDC instead reads the database's own transaction log directly. Query-based polling systematically misses deletes (a deleted row simply vanishes from the poll, leaving no trace of the delete event) and any change that happened and was then overwritten again between two poll intervals; log-based CDC captures inserts, updates, and deletes because every one of them is a distinct entry in the log it reads.

Exactly-once processing guarantees are scoped to what happens inside the engine -- how it tracks offsets and state during computation. The moment the engine writes to something external (a database, a file, another system), that external system has no idea what "exactly-once inside the engine" even means; a retried write after a crash can still land twice at the sink unless the sink write is itself made idempotent, typically via a merge-on-a-dedup-key operation rather than a plain append or insert.

Not necessarily. Batch and streaming can compute the identical aggregate (co-01); the only real difference is latency and input shape, not correctness or capability. An hourly batch job already meets "yesterday's total," and the actual complaint ("a whole hour stale sometimes") is a scheduling frequency problem -- running the batch every 15 minutes instead of hourly may fully resolve the complaint at a fraction of the cost and risk of a full streaming rewrite. This maps directly to this topic's Tensions & trade-offs -- when NOT to reach for this section: reach for streaming machinery once genuinely continuous, sub-minute freshness is a real requirement, not by default whenever "more real-time" sounds appealing.

The reviewer wants ETL's ordering (co-02) for this specific sensitive-field case: transform (mask) BEFORE loading, rather than ELT's load-then-transform default. This is not a medallion problem (co-04) -- bronze can still exist as the landing layer, but the masking transform needs to run before that landing step for genuinely sensitive fields, rather than after it as ELT's usual in-warehouse-transform pattern would have it. The two ideas are independent: ETL vs. ELT is about WHEN transformation happens relative to loading; medallion layering is about HOW the warehouse organizes what it already holds.

The transform is not idempotent (co-05) -- a plain INSERT has no mechanism to recognize "this row already landed" and blindly appends every matching bronze row again on a rerun. Backfill safety depends entirely on idempotency (co-06): reprocessing an already-processed range is supposed to be safe precisely because the transform is idempotent, and here it wasn't. The fix is to replace the plain INSERT with a MERGE/upsert keyed on the order's natural key (or a DELETE for the target date range immediately before the INSERT, if a full overwrite is acceptable), so a rerun of the identical range produces the identical row count either way.

The fact table's declared grain (co-09) and its actual, physically-loaded grain disagree -- the loading query silently produced a finer grain (one row per line) than what was declared (one row per order), and every downstream sum inherits that mismatch as double-counting wherever an order has multiple lines. The fix is not a query patch; it's a modeling decision: either declare the grain as "one row per order line" (matching what the load actually produces, an equally valid choice) and have every consuming query aggregate up to order level explicitly, or fix the load to genuinely aggregate down to one row per order before it lands. The grain must be declared correctly and then matched by the actual load, in either direction, not left to silently drift.

Not a bug -- exactly the documented ordering guarantee working as designed (co-12, co-13). Ordering is guaranteed only WITHIN a single partition, never across partitions of the same topic. Partitioning by a random UUID key spreads messages essentially uniformly across every partition, which is exactly the right choice for maximizing consumer parallelism, but it also means messages produced sequentially are very likely to land on different partitions and can legitimately be consumed in a different relative order. If global ordering across the whole topic is a genuine requirement, the fix is a single-partition topic (giving up parallelism) or partitioning by a key that groups must-stay-ordered messages together (e.g. an order ID, so all of one order's events share a partition and stay ordered relative to each other, even though ordering across DIFFERENT orders is still not guaranteed).

No stronger null-check would have caught this -- a null-check (co-16) can only validate a column's VALUES once that column is known to exist; it has no way to notice the column's very presence or name changed, especially not before the consuming job even runs. The actual fix is a data contract (co-17) on the producer's side: an enforceable schema guarantee (columns, types, constraints) that FAILS THE BUILD on the producing side the moment customer_id is renamed or removed, rather than letting the drift ship silently and only surfacing as a downstream crash a week later. Data quality checks and data contracts are complementary, not interchangeable: a contract prevents a producer from shipping the wrong shape at all; a data-quality check validates the values within a shape that is already known to be correct.

Log-based CDC (co-20) reading an append-only log is not itself the problem -- the log is confirmed correct. The likely cause is that the connector's restart re-read and replayed some log entries it had already applied once before the blip (a common at-least-once-delivery pattern for a restarting consumer), and the downstream WRITE to the replica table was not idempotent (co-21): a plain apply-the-update-again on an already-applied change re-applies its delta a second time rather than converging to the same end state. The general fix is making the sink write idempotent -- a merge/upsert keyed on the source row's primary key plus its change-log offset or version, so replaying the identical log entry twice converges to the identical end state rather than compounding. This is the same principle co-05's idempotent-transform mechanism uses, applied specifically to a log-replay sink.

Backfill's entire value proposition is "you can safely reprocess a past range, even one you already processed once, without worrying about the consequences." If the underlying transform is NOT idempotent, reprocessing an already-processed range would duplicate or corrupt exactly the data backfill is supposed to be able to safely touch -- making "just backfill it" a destructive instruction instead of a safe one. Idempotency is not a nice-to-have alongside backfill; it is the precondition that makes backfill a safe operation to offer at all.

Additivity is a claim about how a measure behaves when summed ACROSS ROWS at a given grain -- "revenue sums correctly across every dimension" only makes sense once you know what one row represents. A measure that is additive at "one row per order line" grain could become subtly wrong if the grain silently shifted to "one row per order" without re-deriving the measure (e.g. summing an already-summed order-level total again would double-count). Declaring the grain first is what makes any later additivity classification a meaningful, stable claim rather than one that silently breaks the moment the grain changes underneath it.

More partitions means more consumers can process a topic in parallel (co-12's consumer-group assignment spreads partitions across group members), but ordering is only ever guaranteed WITHIN a single partition (co-13) -- there is no cross-partition ordering guarantee at all. Global ordering across an entire topic can only be guaranteed by forcing every record through a single, serial partition, which caps parallelism at exactly one consumer. The two properties are structurally in tension: partitioning is what buys parallelism, and it is the SAME mechanism that gives up cross-partition ordering as its cost.

A watermark protects the correctness of a SPECIFIC WINDOW's aggregate -- it decides how long to wait for late-arriving events before emitting a window's final result, trading completeness against latency for that one window. A timeliness data-quality check instead protects trust in a WHOLE BATCH or dataset -- it asserts something like "the freshest timestamp in this batch is within N hours of now," failing the batch outright if the data is too stale to be trustworthy at all. One is a per-window wait-and-emit policy inside a streaming engine; the other is a pass/fail gate on an entire batch's overall freshness.

A contract living at the producer catches a schema-shape problem at the earliest possible moment -- before a single row of drifted data ever leaves the system that created it, by failing that system's own build. A data-quality check living downstream, at or near the consumer, catches VALUE-level problems (a null where one shouldn't be, an out-of-range number) that can only be detected by actually looking at the data as it arrives, regardless of how carefully-shaped its schema is. Placing the contract upstream prevents shape drift from ever shipping; placing quality checks downstream catches the value-level problems a correctly-shaped schema can still carry.

Log-based CDC alone guarantees the SOURCE side is captured completely and correctly -- every insert, update, and delete is a real entry in the log, nothing is missed. But CDC's own delivery to a downstream consumer is typically at-least-once (the same default co-13 names for Kafka-style streaming), meaning a connector restart or retry can replay an already-applied log entry. Without an idempotent sink write, that replay lands as a duplicate application of an already-applied change. A complete, correctly-captured log (CDC's job) plus a write that converges to the same state no matter how many times it's replayed (idempotent sink's job) are two independent halves of the same end-to-end correctness guarantee -- neither one substitutes for the other.