535 messy Excel sheets → one governed lakehouse
Years of monthly Excel exports from 8 core banking systems — inconsistent names, drifting schemas, buried headers, corrupt files — turned into a governed Delta lakehouse with an automated pipeline. This page walks through the delivery the way it really ran: not just the code, but the proposals, the stakeholder loops, and the guides that made the data trustworthy.
The problem: nobody could use the history
Eight core banking systems had been exporting monthly position files for years, under changing conventions. The history existed, but it was unusable: filenames encoded dates seven different ways, column names changed between months, headers hid under title rows, and a few files wouldn't even open. The sharpest constraint was simpler still: the business could only ever look at one month at a time — each file a frozen snapshot, any question across time unanswerable. Risk and reporting work needed that history in the lakehouse, conformed and trustworthy.
A glimpse of the raw landscape (from the sample universe):
Estimated vs. real
The initial plan assumed ~30 GB and a few dozen tables. Discovery found 41+ GB across 535 sheets — the first lesson: measure before you promise.
Schema drift
The same field appears as A/C No, ACCOUNT_NO, and Account Number depending on the year — and some fields exist only in some eras.
Hidden structure
Headers buried under title rows, decoy summary sheets, duplicate rows, numbers stored as text, and files with corrupt internals.
Discovery & analysis: measure the mess first
Before proposing anything, the landscape was inventoried system by system: every file walked, every sheet listed, every filename parsed for its reporting period. The output — a bronze file-metadata table — became the backbone of everything that followed: sheet selection, header detection, and the column-mapping exercise all hang off this inventory.
Scan the landscape
Live demo · synthetic dataThe discovery write-up also fixed the ten processing concerns any solution had to answer — from folder-level reading order to audit trails — each captured as an issue → requirement pair with the business:
1–4 · Read & record
System-level folder reading · yearly/monthly file processing · sheet selection within files · metadata collection.
5–7 · Standardise & check
Column-name standardisation (mapping layer) · column data-quality checks · completeness and consistency validation.
8–10 · Trust & evolve
Error handling and tracking · an audit trail for every step · modularity so new systems and formats can join later.
The proposal: a framework, not a script
The findings went back to stakeholders as a formal research proposal: treat this as a schema-drift problem and build a reusable ingestion framework on the medallion architecture — not a one-off load. The proposal set the rules the business signed off on before heavy engineering started.
🥉 Bronze — preserve
Every sheet lands as-is, with full lineage metadata (system, file, sheet, period). Nothing is dropped — even duplicates are preserved here, so no data is ever lost.
🥈 Silver — conform
Mappings apply the conformed dictionary: canonical column names, typed values, quality rules logged, duplicates resolved by deterministic rules.
🥇 Gold — serve
Governed, query-ready Delta tables per system domain — the layer risk and reporting actually consume.
That's how the proposal framed it. As built, the pipeline was leaner: incremental batches land directly in the gold catalog — as-is landing tables and conformed tables side by side, keeping the preserve/conform/serve roles without separate physical layers. The as-built architecture is in chapter 5.
Deterministic duplicate handling — agreed up front so no engineer ever makes a silent judgement call:
Drop
Exact duplicate columns with identical data — keep one, log the drop.
Merge
Same field split across variants — coalesce into the canonical column.
Prefix
Same name, genuinely different data — keep both with deterministic prefixes, flagged for a business decision.
Quarantine
High-risk conflicts on critical fields hold the whole file/sheet for review, logged — a value is never silently chosen.
The plan was phased with dated milestones and explicit deliverables — and tracked against them publicly with the stakeholders:
Setup & discovery
Workspace, repo, ADLS mount; file inventory; first header-detection report.
Mapping & canonicalisation
Draft conformed schema; business SME sessions to confirm mappings system by system.
Bronze & Silver
Ingestion jobs with lineage metadata; mappings + typing + data-quality logs.
Drift engine, backfill & QA
Automated drift logging and alerts; full historic backfill; reconciliation of row counts.
Documentation & handover
Runbook, mapping change playbook, and training — the framework outlives the project.
Stakeholder loops: where the data got its meaning
Two decisions could not be automated: which sheet in each workbook is the data, and what each column actually means. Both belonged to the business. The engineering answer was to make their input cheap: automated detection did 95% of the work, then purpose-built guides walked stakeholders through confirming the rest — with review sessions to close each loop.
Find the header
Live demo · synthetic dataReal sheets rarely start with the header. A scoring pass rates every candidate row — null ratio, text ratio, keyword hits, uniqueness, whether the next row looks numeric — and skips traps like title rows and numeric-heavy data rows. Corrupt files first go through a recovery cascade.
The column matrix stakeholders filled
Live demo · synthetic dataOne report per system (Cards produces two — CB and IB). Every column ever seen is partially standardised, plotted 1/blank against every month, counted, and sorted by coverage — with a TOTAL_COUNT summary row at the bottom. The last three columns ship empty on purpose: Required, Final Field Name, and Data Type are what the business fills in — that returned matrix is the conformed dictionary's source of truth.
Unify & ingest: incremental batches, directly into gold
The as-built architecture is deliberately lean: incremental batches land directly in the gold catalog — no separate bronze or silver stores. An orchestration pipeline copies each system's files year by year into the lake; a control table carries one row per file/sheet (which sheet, the zero-based header row, the target table, the source system, an active flag); and loader jobs process every active row, landing each sheet as-is into its own gold table. Re-runs are safe: a load overwrites exactly its own table, so any batch can be replayed from its control rows.
It took two solutions to get there. The first solution worked — its runbook and solution document are real deliverables of this project — but it treated every file as a small manual project:
First solution — the manual way, per file
- 1 · Prepare by hand — re-export as CSV UTF-8 or clean the workbook: delete unrelated sheets, remove every row above the header, fix duplicate column names, strip forced-text apostrophes
- 2 · Stage — place the file in the agreed volumes path, note the exact full path
- 3 · Register — hand-write one SQL INSERT into the control table: path, sheet name, header row, target table, source system…
- 4 · Trigger — run the loader job (or wait for the schedule)
- 5 · Verify — compare source row count vs table row count, spot-check values
- The file had to fit the loader: header in the first row, nothing above it, unique clean names. Fine for a handful of loads — × 535 sheets it doesn't scale
- What the business got: queryable tables at last — but one per file, the history still fragmented month by month
Final solution — the loader fits the files
- Inventory notebook walks every folder: period from the filename, sheets per workbook, corrupt-file recovery — zero per-file effort
- Header detection scores candidate rows — nobody deletes title rows by hand; the detected row becomes control metadata
- Control rows generated automatically from that metadata — no hand-written INSERTs
- Column matrix exposes drift and collects the business mapping (chapter 4)
- Files load as they are — batches roll system by system, year by year
- What the business got: one conformed table per system — the entire multi-year history, open to any question
Inside the loader, every sheet gets the same treatment: Excel XML escapes decoded, forced-text apostrophes stripped, cells read as strings so leading zeros survive, column names normalised (lowercase, underscores, digit-leading names prefixed, duplicates suffixed _1, _2), a load timestamp added — and the result lands with every column as string, so nothing is ever silently coerced at landing. Typing happens later, in the conformed layer, where the dictionary says what each field really is.
The control table, recreated with synthetic rows — this is where chapter 4's outputs (selected sheets, detected header rows) become machine instructions:
Run the pipeline
Live demo · synthetic dataStandardisation currently runs as a dedicated notebook: it applies the dictionary to the as-is tables and builds one conformed table per system, stamped with lineage metadata — source system, year, month, source file, ingestion time and more. Newly arriving files are picked up incrementally and appended, so the history keeps growing without reloading — and the roadmap folds the whole flow into a single unified, cleansed pipeline.
The conformed data dictionary — every canonical field with the raw variants it absorbs (excerpt):
Outcome & handover
A framework, delivered
Generic ingestion + standardisation pipeline in Databricks (PySpark) — new systems join by adding mappings, not code.
A dictionary with owners
Conformed data dictionary and column-mapping framework, built with and signed off by the business.
Enforced standards
SQL validation rules and automated checks guard the layers; anomalies are logged, never silently fixed.
A process that transfers
Runbook, guides, and review cadence handed over — the stakeholder loop keeps working without its author.
Put simply: the business went from opening one month's file at a time, to per-file tables, to querying the entire multi-year history in one conformed table per system — trends, comparisons, and analyses that were previously impossible are now a SQL query. And the lasting result isn't just the loaded history — it's that the next 535 sheets won't be a project. The framework, the dictionary, and the stakeholder process turned a one-off rescue into a repeatable capability.