Three Sources, Six People, No Duplicates Allowed
A platform team of six is choosing ingestion for three sources. (a) A partner drops roughly 1,200 gzipped CSV files per day into cloud storage; batches sometimes arrive hours late, a corrected copy of an already-processed file is occasionally re-uploaded, and last month two new nullable columns appeared without notice. (b) An on-premises PostgreSQL ERP must be read daily; the network team will only open a JDBC port, and the ERP table is 40 million rows and grows daily. (c) A Kafka topic carries order events with a 2-minute freshness requirement. Reruns must never duplicate rows, and there is no room to hire a data engineer, so whatever is chosen has to be operable by the existing team on-call rota.
1. Select an ingestion mechanism for each of the three sources and justify each choice against the alternatives. Name the primary failure mode of each selection.
Read the worked answer
(a) Auto Loader. At 1,200 files/day with occasional re-uploads and silent schema changes, Auto Loader is the right default: it tracks discovered files incrementally via a checkpoint rather than re-listing the whole directory, it scales from hundreds to millions of files, schema evolution can be turned on to add new nullable columns automatically, and unexpected data is captured rather than dropped. COPY INTO is a reasonable alternative at this volume and is simpler in SQL-only shops, but it re-evaluates the source directory on each run and its file-tracking is less comfortable as volume grows. Failure mode: re-uploaded corrections. A corrected file arriving at a new path is a new file, so it will be ingested again alongside the original. Auto Loader's checkpoint prevents reprocessing the same file, not the arrival of a semantically duplicate one. This must be solved in the silver layer, not by configuration.
(b) A scheduled job reading via JDBC into Delta with a merge, not Lakehouse Federation as the primary path. Federation is excellent when the need is occasional lookups and you want to avoid copying sensitive data, but 40 million rows read daily, joined against lake data, means repeated load on the ERP and latency on every query. Copy once, then query locally at full speed and without a firewall dependency on every BI refresh. Failure mode: network and credential fragility. JDBC from serverless compute generally requires private connectivity configuration; if that is unavailable, the job needs classic compute in the right network. Six people on call need a clear runbook for "the ERP connection failed at 02:00."
(c) Structured streaming from Kafka into a bronze Delta table. A 2-minute SLA rules out any batch approach. Failure mode: the two-minute window encourages skipping deduplication. Kafka delivers at-least-once on retries, so a batch restart reproduces events. Deduplicate by order id in the streaming write or in a downstream merge; do not rely on "it looked fine for a week."
The unifying decision: bronze stays append-only and replayable for all three, and all deduplication happens in silver. That way a bug in dedup logic is fixable by replaying, which a team of six will need.
2. Specify concretely how duplicates and schema drift are handled end to end, and how you would know the pipeline has silently degraded.
Read the worked answer
Duplicates. Bronze is append-only with ingestion metadata: source file path, file modification time, ingestion timestamp, and a row hash. Silver performs a MERGE on a deterministic business key, and for the partner files the key handling has to account for corrections, so define the rule explicitly: keep the latest version by file modification time per business key and mark superseded rows rather than deleting them, so finance can explain a restated figure. For Kafka, key on order id and keep the event with the highest offset. Run a duplicate-detection query as a scheduled assertion - count of business keys appearing more than once - because a merge that silently stops matching looks identical to a merge that works.
Schema drift. For the partner files: enable schema evolution to add new columns, and enable the rescued-data column so rows with unexpected types are retained with the offending payload captured rather than dropped or failing the whole batch. Alert on rescued-row volume above a small threshold; the two new nullable columns last month should have generated a notification, not a support ticket. For the ERP, drift is different - column additions are rare but type changes are common - so pin an explicit schema for the columns you depend on and let the extraction fail loudly rather than silently coercing.
Degradation signals to monitor, per source:
- Auto Loader: outstanding files and outstanding bytes in the stream metrics, plus time lag between file modification time and ingestion time - late files are the failure that everything else hides.
- ERP: row count per day versus a moving band, and job duration; a query that suddenly returns 4 million rows instead of 40 million is a filter bug, not good news.
- Kafka: consumer offset lag against the topic head.
- All: a daily freshness check that asserts the newest row in silver is within the expected window, and rescued-data row counts.
Operability, given six people: every alert must name the runbook step, not just the symptom. An alert that says "Auto Loader lag high" at 03:00 with no next action is the reason on-call teams start ignoring pages.