The Data Pipeline That Broke the Month-End
A finance team automated its month-end close using an ETL pipeline that pulls from the billing system, maps accounts via a rules table, and loads a consolidation model. Last month the close took 3 days instead of the usual 1. The root cause: a vendor changed a billing-system field name, the ETL silently mapped unmapped accounts to a default 'suspense' account, and the consolidation ran without error. Suspense ended up holding $4.3M across 1,200 transactions. Nobody noticed until the controller reviewed the trial balance on day 2. The team's response is to add a manual review of the suspense account - but the same pipeline feeds regulatory reporting and management dashboards.
1. Why did the control environment fail to catch this, and what is wrong with the proposed fix?
Read the worked answer
The failure is a silent-failure design flaw: the pipeline had no validation that every source account mapped to a valid target. A default-to-suspense rule converts a data error into a plausible-looking number, so the process 'succeeds' while being wrong. There was no reconciliation control between source transaction count/amount and loaded count/amount, and no exception threshold that would halt the load.
The proposed fix - manual review of suspense - is a detective control that depends on a human looking at the right place, and it only covers the consolidation feed. It does nothing for the regulatory and dashboard feeds, which share the same broken pipeline and likely carry the same $4.3M misclassification. It also doesn't prevent recurrence: the next field change will fail the same way.
A manual review is a patch, not a control. It also adds a recurring manual step that will erode as the team gets busy.
2. Design the preventive and detective controls you would put in place, including how to treat the downstream regulatory and dashboard feeds.
Read the worked answer
Preventive (pipeline):
- Schema validation: fail the load (do not default) if an expected field is missing or renamed. No silent fallback to suspense.
- Referential integrity check: every source account must map to a valid target; unmapped = hard stop with an exception report.
- Row-count and amount reconciliation: source vs loaded counts and totals must match within a defined tolerance before the job can proceed.
- Change management: vendor field changes must be tested in a staging environment before production.
Detective:
- Automated suspense-balance alert with a zero-tolerance threshold for month-end.
- Daily reconciliation of the three downstream consumers to the source.
Downstream treatment: Treat the regulatory and dashboard feeds as in scope of the same controls - quarantine and restate any period affected by the $4.3M misclassification, and notify the regulatory reporting owner. A single pipeline feeding multiple consumers means a single control failure contaminates all of them; the fix must be at the pipeline level, not per-report.