Case Study · Data Engineering
Data Engineering: Rebuilding a Nightly Batch Estate as a Lakehouse
A group whose reports disagreed with each other and arrived too late to act on. We replaced overnight extracts and hand-maintained SQL with change-data-capture ingestion, a layered lakehouse, tested dbt models and contracts on every source, so one set of numbers lands before the business day starts.
The problem
The group ran several ERPs, a POS estate, an e-commerce platform and a warehouse system, each with its own extract job. Every night a chain of scheduled SQL scripts copied tables into a reporting database, and analysts layered views on top. Over years, definitions drifted: finance, supply chain and e-commerce each had their own version of "net revenue" and "active customer".
The symptoms were familiar. The nightly chain ran past 10am, so Monday decisions were made on Friday's data. A failure in step three left later steps loading silently incomplete data, and nobody found out until someone noticed a number looked wrong. Nobody could say where a figure came from, and the one person who understood the scripts was the bottleneck for every change.
The architecture
Ingestion
Replaced full-table extracts with log-based change data capture from the transactional databases, plus API and file connectors for SaaS sources. Loads are incremental and idempotent, so a re-run repairs rather than duplicates.
Bronze — landing
Raw records land immutably with source, ingest timestamp and batch id attached. Nothing is edited in place, so any downstream number can be replayed from source exactly as it arrived.
Silver — conformed
Deduplication, type enforcement, late-arriving and out-of-order event handling, and slowly changing dimensions for products, outlets and customers, so history is preserved instead of overwritten.
Gold — business models
Dimensional models built in dbt, where each business metric is defined once, in version control, with its owner and definition documented and exposed to BI.
Orchestration
Airflow DAGs with explicit dependencies, retries, backfill support and SLA alerts. A failed task stops its downstream tasks instead of letting them publish partial data.
Data quality as code
Contracts on every source and tests on every model: freshness, row-count deltas, uniqueness, referential integrity, accepted ranges and reconciliation back to finance totals. Failing tests block publication and page the owning team.
Observability and lineage
Column-level lineage from dashboard back to source column, with cost, runtime and freshness tracked per model so regressions are visible before users report them.
Governance
Role-based access, PII tagging with masking policies on personal columns, and retention rules applied at the table level rather than by convention.
How we sequence the migration
- Start with the reports the business actually runs on, not with the largest tables. One domain is migrated end to end and proven before the next begins.
- Run old and new in parallel and reconcile them daily. The legacy job is only switched off once the new model matches it, or once the difference is explained and agreed as a fix.
- Agree metric definitions with finance and operations in writing before modelling them, since most "data problems" at this stage are definition disagreements.
- Leave the client's team able to run it: code review, CI on every pull request, runbooks, and pairing on the last domain so the migration does not create a new single point of failure.
Why this is the work that matters
Almost every failed analytics or AI programme we are called into fails here rather than in the model. Forecasts, segmentation and agents all assume the data behind them is complete, on time, defined once and reproducible. Data engineering is what makes that assumption true, and it is the cheapest stage at which to fix it.
KPIs we design for
Technology
Have a similar challenge?
Talk to our team about what this could look like for your business.