Design a near-real-time analytics pipeline for clickstream. What would you draw in 45 minutes?

At-least-once into Kafka with idempotent sink keys is pragmatic. Mention a dedup window or upsert keys in the serving layer and you're covered.

For anyone who wants the two-sentence version: data arrived out of order, and the code assumed it wouldn't. Fix the assumption or sort the data, pick one.

Careful with NULL in join keys, they'll drop rows in an inner join.

Start with the execution plan, numbers beat guesses.

In our case the root cause was an implicit cast preventing pushdown.

ELI5 version: it's not broken, it's just slow because it's checking way more stuff than it needs to. Narrowing what it checks is almost always the fix.

Idempotent writes with merge keys saved us during backfills.

For anyone who wants the two-sentence version: data arrived out of order, and the code assumed it wouldn't. Fix the assumption or sort the data, pick one.

Any downside to this approach with incremental models?

Interviewer pushed on exactly-once vs at-least-once. What's a sane default answer there?

Any downside to this approach with incremental models?

Agree on the staging table swap. Atomic promote prevented partial reads.