Nightly depot files with retries, drift, and late arrivals
Northwind Freight receives 14 CSV files each night, one per regional depot, on an on-premises SFTP server. Files arrive between 22:00 and 03:00, but at least twice a month one depot uploads after 06:00. Each row contains shipment_id, depot_id, event_ts, status, and two optional fields that depots add without notice. Volume is about 2 million rows per night. A cron job on a Compute Engine VM copies everything to Cloud Storage and runs bq load --autodetect --append into a single BigQuery table. The team sees three problems: duplicate shipment events whenever a depot retries an upload because the whole file is re-sent, failed loads when a new column appears, and dashboards that show partial days because the 06:00 load is missed. They have Cloud Composer, Dataflow, Dataproc, and Datastream licensed, but only one data engineer. Leadership wants minimum custom code and predictable cost.
1. Recommend an ingestion and deduplication pattern. Justify why you would not use Dataflow here.
Read the worked answer
Use Cloud Storage as the durable landing zone and Cloud Composer to orchestrate. Each file lands under a path such as landing/date=YYYY-MM-DD/depot=NN/. A DAG loads the file into a staging table with an explicit schema, then runs a MERGE into the target table on the natural key (shipment_id, event_ts, depot_id). Partition the target by DATE(event_ts) and cluster by depot_id. Keep the raw file for replay.
Why not Dataflow: the volume is about 2 million rows per night, which is small for BigQuery batch loads. Dataflow would add a pipeline, custom parsing code, and streaming-oriented cost for a batch file problem. BigQuery load jobs are inexpensive and the MERGE makes retries idempotent. The key judgement is to separate landing, staging, and publishing so a re-sent file cannot duplicate rows. If the same shipment appears twice inside one file, deduplicate in the staging step with ROW_NUMBER() before the MERGE.
2. How should the team handle schema drift without silently losing columns or breaking the nightly load?
Read the worked answer
Treat depot columns as an additive contract. Keep a versioned schema JSON in source control and update it through a reviewed change. Load with that schema rather than --autodetect, because autodetect can infer different types across files and cause load failures. New optional columns should be added as NULLABLE; existing columns keep their types.
For unknown columns that appear before the schema is updated, land the raw CSV as a single STRING column in a quarantine table and parse known fields with SQL. That preserves the data and lets the engineer inspect the drift. Do not use --ignore_unknown_values as the permanent answer: it makes the load succeed but drops the new field, which is silent data loss. If a breaking change appears, such as a renamed event_ts, fail the file and alert rather than guessing. This balances the one-engineer constraint with the need to avoid both failed loads and invisible loss.
3. Design a late-file and backfill approach that keeps dashboards from showing partial days.
Read the worked answer
Use a completion marker per depot. After a depot finishes uploading, it writes a zero-byte _SUCCESS file. A Composer DAG waits until all 14 markers exist or until 06:30, then loads the day. If a marker is missing, the DAG should alert and not publish a partial day. Publish through a view that reads only dates marked complete in a small control table, so analysts do not see half a day.
For the late depot, trigger the same DAG for that date. Because the load uses MERGE on the natural key, re-running is safe. Keep catchup=False for the schedule but use a manual or CLI-triggered run for the specific date. Record file checksums in the control table to skip duplicate replays. This is better than a fixed 03:00 schedule because the real constraint is file completeness, not clock time.