A grocery platform ingests catalogue and inventory feeds from hundreds of retailers, each with different formats, quality and update frequency. Should transformation happen before loading or after, and where should data quality be enforced?
Show the full answer Hide the answer
The recommendation
ELT for the bulk, with a thin validation gate before load. Land raw feeds first, transform in the warehouse — but do not load data that cannot be attributed or parsed.
Why ELT for the bulk
- Raw data is kept. When a transformation bug is found, history can be reprocessed. Under classic ETL, the untransformed input is gone and the error is permanent.
- Retailer formats change without warning. Landing raw means an unexpected field is captured rather than dropped, and the schema change is discoverable after the fact.
- Transformation logic changes far more often than ingestion. Decoupling them means a mapping fix is a warehouse deployment, not a pipeline redeploy plus a re-fetch from a partner who may not offer one.
- Warehouse compute is elastic and better suited to large transformations than a fixed ingestion tier.
Why a validation gate is still required
Pure ELT — load absolutely everything — produces a warehouse where every consumer independently discovers that one retailer sends prices in cents and another in dollars. Some checks belong before the load, specifically the ones that make data unattributable or unparseable:
- Structural validity: does it parse, are required identifiers present?
- Provenance: which retailer, which feed run, what time, which file — without this, nothing downstream can be debugged or reprocessed.
- Gross anomaly detection: a feed with 90% fewer rows than usual is almost certainly a truncated upload, and loading it silently deletes a retailer's catalogue.
That last check matters more than any other. A feed that arrives empty or partial must not be treated as an intentional update, because the downstream consequence is items vanishing from search and orders failing at fulfilment.
Where quality is enforced
In layers, each with a defined behaviour on failure:
| Layer | Checks | On failure |
|---|---|---|
| Ingestion | parses, attributed, plausible volume | reject file, alert, keep last good |
| Raw → cleaned | types, ranges, referential integrity | quarantine rows, continue |
| Cleaned → serving | business rules, freshness, completeness | block publication of that retailer |
The critical property is that failures are scoped per retailer. One partner's broken feed must not block every other partner's updates, and it must not silently publish a degraded catalogue either. It should hold that retailer at its last known-good state and raise an alert.
The freshness dimension
Inventory is not catalogue. Catalogue changes daily and tolerates hours of lag; inventory changes continuously and stale availability directly causes failed orders and substitutions. These deserve different pipelines with different SLOs, and merging them into one nightly batch — the common initial design — makes the fast-changing data as slow as the slow-changing data.
The lesson
The ETL/ELT question is usually less interesting than where the quality gate sits and what it does on failure. A pipeline with no gate publishes garbage; a pipeline that halts entirely on any bad input is hostage to the least reliable partner. Per-source isolation with last-known-good fallback is the design that survives hundreds of external feeds.