When is a window finished?

Each dot is a click. Across is event time, when it happened on the phone. Down is processing time, when it reached the pipeline. Most arrive within seconds; phones that lost signal report much later.

Group by processing time and the counts are simply wrong: a late click lands in whatever window is open when it shows up.

Group by event time and a new question appears: how long do you wait before emitting a window? The watermark (orange) is the job's guess at "everything before this time has arrived", usually the newest event seen minus a fixed delay. A window is emitted once the watermark passes its end.

Events behind the watermark are late. With allowed lateness the job re-emits an updated count; past it, the event is dropped. A longer delay is more correct and less fresh. There is no free setting.

Two writes that aren't one

For every message the consumer does two things: write the row, and commit the offset so a restart knows where to resume. A crash between them leaves one done and the other not.

• At most once: commit first. A crash skips the message. Nothing is ever duplicated, and payments are lost.
• At least once: write first. A crash replays the message. Nothing is lost, and payments are doubled.
• Idempotent sink: still at least once, but the write is an upsert on the payment id, so a replay changes nothing. This is how most "exactly-once" pipelines really work.
• Transactional: the row and the offset are committed in one transaction, so they can't disagree. Kafka transactions and Flink's two-phase commit sinks do this across systems.

Exactly-once is about the effect, not delivery: messages may still be read twice, but the result is as if each counted once.

Snapshots vs a history

Polling runs a query every few seconds for rows with a newer updated_at. It gets each row's state at poll time, so:

• two updates between polls look like one; the middle state is lost,
• a delete leaves nothing behind to find,
• a row inserted and deleted between polls never existed as far as you know,
• and every poll is a query against the production database.

Log-based CDC (Debezium, AWS DMS, Fivetran, Postgres logical replication) reads the write-ahead log in order. Every insert, update and delete arrives, with before and after values and a log sequence number (LSN) to resume from after a restart. The cost: access to replication slots, log retention to manage, and schema changes to follow.

A table is a list of files

Data files are immutable. A table is a snapshot: metadata listing which files make up the table right now. A write adds new files, then atomically swaps the pointer to a new snapshot. Readers see the old snapshot or the new one, never half of a write.

Deleting a row means either:

• Copy-on-write: rewrite every file that holds an affected row. Slow writes, fast reads.
• Merge-on-read: write a small delete file, and let readers filter those rows out. Fast writes, slower reads, until compaction folds them in.

Old snapshots still point at old files, which gives you time travel for free until you expire them. Two writers at once use optimistic concurrency: the loser's commit fails, it checks for conflicts, and retries.

One hot key

To GROUP BY customer_id, rows are shuffled: sent over the network to the worker picked by hash(customer_id), so all rows for one customer end up together.

That's fair when keys are similar in size. Real data is skewed: one customer, one country or one NULL can own a big share of rows. Its worker becomes a straggler, and the stage waits for it while the others sit idle.

Salting adds a random suffix to the key, spreads the hot key over all workers, then combines the partial results in a second, much smaller step. Modern engines (Spark's adaptive query execution) can split skewed partitions automatically.

Broadcast or shuffle?

Joining a 500 GB fact table to a dimension, the engine has two options:

• Shuffle join: repartition both tables by the join key so matching rows meet. Works for any size, but moves everything over the network.
• Broadcast join: copy the small table whole to every node. The big table doesn't move at all. Only works if the small table fits in each node's memory.

Optimizers pick automatically from table statistics, which is why stale statistics can turn a two-minute query into an out-of-memory error.

Follow the column

Column-level lineage records which source columns each column is computed from. Tools like dbt, OpenLineage and data catalogs build it by parsing the SQL of every model.

Downstream is impact analysis: before renaming crm.customers.email, see every table, feature and dashboard that depends on it. Upstream is root cause: when a number looks wrong, which sources feed it?

Governance rides on the same graph. Tag PII at the source and let the tag propagate down the lineage, and masking policies apply to every copy automatically, including derived columns nobody remembered were personal.

What is "revenue"?

Finance subtracts tax and refunds. Sales counts what was booked. A notebook forgot to filter test orders. All three are "revenue", and none of them agree.

A semantic layer (dbt's MetricFlow, Cube, LookML) defines each metric once, in code, next to the models. Every dashboard, notebook and spreadsheet asks it for revenue by name and gets the same SQL underneath.

Precompute, or scan every time?

On-demand warehouses bill by bytes scanned. A dashboard that hits a big table 200 times a day pays for the full scan 200 times.

A materialized view or pre-aggregated table pays once to compute a small result, then every query reads that. The refresh costs something too: a full rebuild rescans the whole table each time, an incremental refresh processes only new data.

Precomputing wins for popular, repetitive queries on big tables. For a rarely opened report, just scan.