Motivation
The US Revenue Quality team maintained a surcharge dashboard powered by an aging SAS workflow. Every time rates changed, an analyst manually reran a chain of scripts, hand-checked outputs and reconciled totals — a process that took roughly 10 days and was error-prone at the scale of tens of millions of rows.
The goal: migrate to Ab Initio, make it config-driven so rate changes become a data edit rather than a code change, and add validation so accuracy is provable instead of assumed.
Architecture
The pipeline is parameterized end-to-end with Ab Initio PDL (Parameter Definition Language). Rate tables and run dates live in config; the same graphs run for any month without modification.
🗄️Source ExtractsTeradata / files
→
⚙️PDL Configrates · dates · keys
→
🔗Ab Initio Graphstransform · join
→
✅Validationrow · null · dup
→
📊Dashboard + Emailautomated
Config-driven flow — a rate change is a parameter edit, not a code change.
What I built
- Config-driven graphs in PDL — rate tables, effective dates and join keys are externalized, so a new rate cycle is a parameter change.
- Full SAS → Ab Initio migration of the surcharge logic across 25 months of historical data (113M+ rows).
- A validation layer — automated row-count reconciliation, null checks, duplicate-key detection and period-over-period rollups that block bad runs.
- An automated reporting workflow that extracts backend data and distributes the refreshed dashboard via email with zero manual steps.
Results
10d→4hRate-update turnaround
113M+Rows in production
25Months reconciled
0Manual reruns after launch
The headline is the turnaround drop — 10 days to ~4 hours — but the bigger win is that correctness became provable through automated validation rather than manual spot-checks.
Challenges
- Parity with legacy SAS: the old logic had implicit edge-case handling. I reverse-engineered it and validated the new pipeline against historical outputs row-for-row before cutover.
- Scale: joins across 113M+ rows needed careful partitioning and key design in Ab Initio to stay performant.
- Trust: stakeholders relied on the dashboard for revenue decisions, so validation fails loudly rather than silently producing wrong numbers.
Learnings
- Config over code is the highest-leverage decision in ETL — it moves change-management from engineering to operations safely.
- Migration is mostly about provable equivalence, not new features. Validation is the product.
- Performance at scale comes from data layout (partitioning, keys) far more than micro-optimizations.