Government case study Aug 2024 – Mar 2025

Automating configuration and testing for a government-scale warehouse

A large data warehouse modernization moved from fully manual ETL configuration and testing to a metadata-driven generator, automated validation, and a custom monitoring dashboard.

Follow the workflow
  • 18
    Business processes

    Business processes covered end-to-end across the integration and information layers

  • 2
    Custom tools built

    A metadata-driven config generator and the ETL Monitoring Tools dashboard, both built in-house

  • Implementer
    Our role

    Delivered the build under an external partner's architecture and design

The shift

From hand-written configuration to generated, tested pipelines

Every ETL job, transform, and load configuration had to be hand-written from mapping specifications across a large multi-process warehouse, and full regression testing had to be repeated manually every time a fix or design change landed.

As implementer under an external partner's architecture and design, we built a metadata-driven configuration generator that reads the mapping specifications directly and produces — and version-controls — the ETL configuration, paired with an automated testing engine that runs source-versus-target validation in parallel instead of one query at a time.

Operational outcome

A purpose-built monitoring dashboard gives the team real-time visibility into job status and test results across the pipeline, and the client's own data warehouse team has continued operating and extending it using the same tooling we handed over.

Metadata-driven config generation

ETL job, transform, and load configuration is generated directly from mapping specifications instead of being hand-written per table.

Automated regression testing

Source-versus-target validation queries run in parallel and report pass or fail without a tester waiting on each one sequentially.

Built-in pipeline visibility

A custom-built monitoring dashboard surfaces job status, test results, and failure detail in real time.

Engagement status
Aug 2024 – Mar 2025
Delivery team
  • Technical Lead
  • Data Engineer
  • Quality Assurance
  • Project Manager
  • Technical Writer

Client, partner, and internal system names remain confidential.

How it works

One specification, one generated, tested pipeline

Onboarding a new business process means writing a mapping specification and running the generator — not hand-building a new pipeline from scratch.

Step 1 of 4

Map

Analysts and data engineers describe every source-to-target mapping in one structured specification per business process.

Mapping specifications were the reference. Engineers read them and built each pipeline step by hand.

Mapping specification Structured, machine-readable Reference document
  • record_id entity_key Direct
  • status_code status Lookup
  • amount_raw amount Cast
  • created_dt created_on Format

Illustrative, not client data

Step 2 of 4

Generate

The generator reads the specification and produces the job, transform, and load configuration. Every revision is tracked.

Every job, transform, and load configuration was typed by hand from the specification, table by table.

  • Mass & selective generation
  • Dry-run revision diffing
  • Automated test-query generation
Mapping specification
  • Job configuration
  • Transform configuration
  • Load configuration

Dry run: shows what changed before it is applied

An engineer typing configuration

…and again for the next table.

Illustrative, not client data

Step 3 of 4

Run

ETL jobs run across the landing, integration, and information layers, driven by the generated configuration.

The same three layers ran on configuration that engineers had written and maintained by hand.

Generated configuration Hand-written configuration
  1. Landing Zone Source data arrives
  2. Integration Zone Data is integrated
  3. Information Zone Ready for use
Apache Spark Hadoop

Illustrative, not client data

Step 4 of 4

Validate

Automated tests reconcile source and target in parallel and report pass or fail on the monitoring dashboard.

Full regression testing was repeated manually after every fix or design change, one query at a time.

  • Real-time job & test dashboards
  • Drill-down failure analysis
Source-versus-target checks Run in parallel One at a time
  • Key integrity Pass
  • Null checks Pass
  • Row-count match Fail
  • Value reconciliation Pass
  • Duplicate check Pass
  • Referential links Pass

All six checks finish together — nobody waits on a queue.

Each check waits for the one before it, and the whole set is repeated after every change.

Illustrative, not client data

Technical architecture

See how mapping specifications become generated configuration, running pipelines, and tested results.

ETL Mapping Specifications
Three-tier data architecture
Landing Zone Integration Zone Information Zone
Automation layer Custom-built

Config Generator

Python

Capabilities
  • Mass & selective generation
  • Dry-run revision diffing
  • Automated test-query generation

ETL Monitoring Tools

Custom web application

Capabilities
  • Real-time job & test dashboards
  • Custom query-driven screens
  • Drill-down failure analysis

Processing engine

Apache Spark

ETL execution across all three tiers

Storage

Hadoop

Landing, integration & information layers, ORC/Parquet

Config store

SQL Server

Job, transform & load configuration, with full revision history

A new business process is onboarded with a mapping specification and a generator run—not a hand-built pipeline.

Has your data platform outgrown manual configuration?

Tell us where manual mapping, config, or testing work is slowing your data team down. We'll help map a practical path forward.

Discuss your data platform