Work project · RAKBANK — System Analyst, Data Platforms · 2025–2026
Years of monthly Excel exports from 8 core banking systems — 535 sheets, 41+ GB, no consistent structure — needed to become a governed, queryable data lakehouse. This repository is an interactive case study of how that delivery actually ran, end to end: discovering the mess, proposing a framework, looping with stakeholders to give the data meaning, and landing everything in one automated ~2-hour pipeline. The live page tells the story chapter by chapter, with runnable demos at each step.
Technically, the delivery was a schema-drift-aware ingestion framework on Databricks (PySpark) over Azure Data Lake — run as incremental batches landing directly in the gold catalog on Delta Lake. Automated metadata extraction inventoried every file and sheet; a scoring heuristic located header rows buried under title rows (with a recovery cascade for corrupt workbooks); a column-presence matrix exposed schema drift across months and became the stakeholder interface for building a conformed data dictionary. The load itself is metadata-driven: a control table holds one row per file/sheet (sheet name, zero-based header row, target table, source system, active flag); loader jobs — parameterised by system, filetype, and batch filter — clean each sheet (escape decoding, forced-text apostrophes, leading zeros preserved), normalise column names, and land it as-is into its own gold table, all columns as strings plus a load timestamp, with overwrite-mode idempotent re-runs and source-vs-target row-count verification. Standardisation — including deterministic duplicate rules (drop / merge / prefix / quarantine) — runs as a dedicated notebook that applies the dictionary on top of the untouched as-is tables, building one conformed table per system stamped with lineage metadata (system, year, month, ingestion time); newly arriving files are picked up incrementally and appended. The demos on the live page re-implement this behaviour in the browser against a fully synthetic sample universe that mirrors the real problem's shape.
- Historic data onboarding — bring years of manually-exported spreadsheet history into a lakehouse with lineage, validation, and a repeatable pipeline instead of one-off scripts
- Schema-drift management — detect, log, and reconcile column changes across time without losing or silently altering data
- Stakeholder-driven data dictionaries — turn business review sessions into a structured mapping exercise (required flags, final field names, data types) that directly drives the ETL
- Messy-Excel ingestion at scale — automated header detection, corrupt-file recovery, and filename-based period extraction across hundreds of inconsistent workbooks
- The data couldn't describe itself — headers sat under title rows, sheet names lied, and filenames encoded dates seven different ways; every piece of structure had to be detected, scored, or confirmed by a human
- Schema drift across years — the same field appeared under different names era to era, fields appeared and vanished, and one system split into conventional/Islamic sheets with different columns
- Corrupt workbooks — some files failed every normal reader and needed a low-level recovery cascade (stripped styles, raw XML parsing, cleaned sheet names)
- Meaning is a business decision — no algorithm can decide which columns matter or what they should be called; the delivery depended on making stakeholder input cheap, structured, and auditable
- Confidentiality by construction — everything here runs on synthetic data generated by
tools/generate_sample_data.py; the page mirrors the real pipeline's behaviour
index.html ← the interactive case study (GitHub Pages)
sample-data/universe.js ← synthetic sample universe (8 systems, 535 data sheets)
sample-data/ABOUT.md ← what the synthetic universe contains and how it's shaped
tools/generate_sample_data.py ← deterministic generator — proof the data is fake
Note on authenticity: the process, architecture, and pipeline behaviour shown are real; every account, amount, filename, and entity on the page is fictional. The real project's headline numbers (8 systems, 535 sheets, 41+ GB, ~2-hour automated run) are facts of the delivery — the demos reproduce them at browser scale.