Data engineering interviews: SQL, event pipelines and trustworthy metrics

Scope and assumptions: The producer guarantees stable (tenant_id, event_id) identities; duplicate deliveries have identical business fields. A reused ID with changed content is quarantined instead of silently chosen. Times are normalized UTC strings in this tiny SQL fixture; production should use appropriate typed timestamps and an explicit reporting timezone.

1. Step 1

   Keep raw events

   Persist identity, event time and arrival provenance.

   →
2. Step 2

   Deduplicate

   One business event per tenant + event ID.

   →
3. Step 3

   Group by event day

   Attribute late payments to the day they occurred.

   →
4. Step 4

   Reconcile + publish

   Compare totals, then replace the affected partition.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY tenant_id, event_id
    ORDER BY ingested_at, source_offset
  ) AS delivery_number
  FROM raw_events
)
SELECT tenant_id,
       substr(event_time, 1, 10) AS event_day,
       SUM(amount_cents) AS net_cents
FROM ranked
WHERE delivery_number = 1
GROUP BY tenant_id, substr(event_time, 1, 10)
ORDER BY tenant_id, event_day;

The unique ingestion offset breaks ties between identical deliveries. After the producer-content check, retain one delivery per event identity, then aggregate by event day. Expected net_cents: October 10 = 6700; October 11 = 1100. The query recomputes from retained raw input; it does not implement a streaming watermark or write the destination table.

A correct total has a definition, a grain, stable identities and a correction policy. A watermark estimates arrival progress; it cannot prove that every business event has arrived. Retained raw data and reconciliation make the calculation explainable.