Fifteen-Minute Shipment Status on a Nightly Batch Stack
Meridian Freight moves 40,000 shipments a day. Its analytics stack is a nightly batch ELT: eight sources - ERP, transport management, warehouse management, a Kafka event stream, and three department spreadsheets - land between 01:00 and 07:00 in a Kimball star schema in Snowflake. Operations has now signed a contract requiring shipment status to be visible to customers within 15 minutes of the scan event, with seven years of history retained for customs audit. The event stream carries twelve event types and has changed schema four times in the past year, twice without notice. The data team is four engineers, two of whom are contractors leaving in six months. Finance still reconciles from the spreadsheets. Snowflake spend is up 38% year on year and storage is the fastest-growing line item.
1. Recommend a target architecture that satisfies the 15-minute SLA and the seven-year audit retention, given a team of four. Justify the split and state what you would deliberately not do.
Read the worked answer
Split the problem into an operational path and an analytical path; do not force one model to serve both.
Operational path (15-minute SLA) A narrow current-state table keyed on shipment_id, holding the latest status, location and event time, fed from the event stream by micro-batch (1–5 minute cadence) or Snowpipe Streaming, exposed through a cached API. This table is small, frequently overwritten, and answers exactly one question. Micro-batch is chosen over a full streaming engine because the team is four people, two of whom are leaving, and the SLA is 15 minutes, not 2 seconds. A bespoke Flink cluster is operationally correct and organisationally reckless here.
Analytical path (audit and reporting) Land every event immutably in object storage, raw and untyped, partitioned by event date, with ingestion timestamp and source offset recorded. Model the star schema from that archive. Seven years of history sits in cheap storage; twelve to thirteen months stay hot for query.
Why the split matters The current-state table is derived and disposable - it can be rebuilt from the archive at any time. That property is what makes the audit defensible: the archive is the record, the star is an interpretation of it.
Deliberately not doing Not streaming into the Kimball star. Not retrofitting the nightly window. Not building custom stream infrastructure the remaining two engineers cannot support after the contractors leave.
2. The event stream has changed schema four times in a year, twice unannounced. What architectural controls would you put in place, and where in the pipeline should they sit?
Read the worked answer
Schema change is a producer problem that becomes a consumer incident. Handle it at the boundary and make the archive tolerant of it.
At ingestion
- Land raw payloads as-is and type them on read. A schema change then breaks one downstream model, not the pipeline, and nothing is lost.
- Store the schema version alongside each record so a re-model is reproducible.
- Route unparseable records to a dead-letter store with the raw payload retained, and alert on the count. Silent drops are the failure mode that produces unreconciled value later.
With producers
- Establish a schema registry with a compatibility rule - backward-compatible is the minimum acceptable for a stream with twelve event types. This is a contract negotiation, not just a tool.
- Require notice and a version bump for breaking changes. Two unannounced changes in a year means the current agreement is verbal and unenforced; get it into the integration contract.
- Add a CI check that diffs the producer schema against the registered version and blocks incompatible releases.
Monitoring Alert on: schema version change events, dead-letter volume above threshold, and per-event-type record counts departing from a rolling baseline. The last one catches an event type that stops arriving, which no schema check will find.
Judgement call Tolerant ingestion plus strict contracts is the right combination. Being tolerant only at ingestion hides the problem; being strict only at the producer blocks the business. Do both.
3. Finance still reconciles from the three spreadsheets, and storage cost is the fastest-growing line. What do you do about each, and in what order?
Read the worked answer
Treat these as separate problems with different owners; do not let the cost conversation delay the spreadsheet one.
Spreadsheets The spreadsheets are a symptom of distrust in the warehouse, not a source-system problem. Migrating them first would import unreconciled data into a governed pipeline and destroy the credibility you are trying to build.
Sequence:
- Put them behind a controlled intake - a validated file drop with a named owner, schema, and a checksum - so the data is at least captured and reproducible.
- Give Finance a reconciliation report that compares warehouse figures to their own totals, with differences itemised. Reconciliation effort drops when the explanation is published rather than performed.
- Set a decommission date per spreadsheet tied to a source-system fix, and track it. Three spreadsheets with no end date become permanent.
Storage cost The 38% increase is expected and mostly correct: you are now retaining every raw event. Do not respond by shortening retention - the seven-year customs obligation is not negotiable.
Instead:
- Set lifecycle policies so only the hot window sits in auto-resume storage.
- Audit for models that scan the raw archive on every run; those are the real cost driver, not volume.
- Review the star schema for full-refresh patterns that rebuild seven years nightly.
Order Intake control first, cost optimisation second. Intake is the correctness issue and is cheap; cost work without usage telemetry tends to cut the wrong thing and will be redone.