Home / DPA Insights / Data Management
What Green Doesn’t Tell You
A Three-Part Series on Building Trustworthy Financial Data
A green job status only means the machine ran. Job status and row counts don’t know what the data means. On a security master build, four checks replaced them: is each security unique for its date, does every expected period exist, do the values behind valuation and risk hold up, and can each curated figure trace back to its source. Validation runs in each stage of the pipeline, not as the last step before sign-off. Historical completeness is a modelling problem, not a loading problem. Write down what should exist before you call a load complete. A reliable pipeline gives the same correct answer every time you repeat it, and does no damage when it overlaps with another job.
The Job Ran Green. The Data Was Wrong.
What Green Doesn’t Tell You · A Three-Part Series on Building Trustworthy Financial Data
Every job finished. No errors, no alerts, row counts close to the day before. By any dashboard’s measure, the load worked.
Then a validation check found one security holding two records for the same business date. Both looked valid. Only one could be true. The client’s risk team was pricing from whichever one the query returned first.
Green only means the machine ran
We were building a security master platform for an investment firm, and we started the way most teams do. Land the source files, clean them in layers, publish tables for valuation, risk and reporting. We trusted job status and row counts. If both looked healthy, we moved on.
Neither signal knows what the data means. A duplicate delivery adds rows, so the count looks fine. A date in the wrong format still loads. A column that changes type between stages still writes. Every check passes, and the business picture is still wrong.
So we changed the question
We stopped asking whether the job ran. We started asking what the data should look like once it had. That gave us four checks.
- Is each security unique for its date?
- Does every expected period exist?
- Do the values behind valuation and risk hold up?
- Can each curated figure trace back to its source?
The first time we ran those checks, they flagged more than a few stray issues that job status had waved straight through. That mattered. The curated layer feeds valuation, risk and the reports our client’s teams rely on every day. One wrong record there doesn’t stay in one place.
So validation stopped being the last step before sign-off. We built it into each stage of the pipeline, so problems got caught where they started, not three systems later.
A database protects the write. It can’t tell you whether the write is right. That judgement has to be designed in, and it starts with a plain answer to one question: what does a single record stand for?
The Month-End That Wasn’t There
What Green Doesn’t Tell You · A Three-Part Series on Building Trustworthy Financial Data
On the same security master build, the historical rebuild looked clean. Every file had arrived. Every file had loaded. Then someone pulled a month-end view for an older period, and a group of securities simply wasn’t there.
Nothing had failed. The source had never sent those records.
A file only tells you what arrived
We had quietly assumed that complete meant loading everything we received. It doesn’t. A daily file shows what the source sent today. It says nothing about what the platform should hold for a month two years back.
For this platform, the right set of records depended on business dates. When did the security start? Where was the cutoff? Which month-ends fell in between? None of that sits inside a delivery file.
Write down what should exist first
So we flipped the order. Before calling a load complete, we built the full list of security and period combinations that ought to exist. Then we compared it with what the platform actually held.
Missing periods stopped being a surprise someone found in a report. They became something we could see and track. The first comparison turned up gaps in periods we had assumed were long settled.
Historical completeness is a modelling problem, not a loading problem. A trustworthy platform can answer two questions. What do we have? And what should we have? Most can only answer the first.
Run It Twice
What Green Doesn’t Tell You · A Three-Part Series on Building Trustworthy Financial Data
Late in the same build, a historical refresh was halfway through when the daily load kicked off on schedule. Both jobs reached for the same table. The logic in each was correct. The run still failed.
Correct code, unsafe run
Our first instinct was to fix the write that broke. The real cause sat further out, across scheduling, storage and timing. Two valid jobs touching one table at once made the whole execution unsafe.
That raised a question we had never answered clearly. When you rerun a load, what should actually happen?
There are three right answers
It depends on the record. If the data changed, update it. If it’s genuinely new, add it. If nothing changed, leave the table exactly as it was. A rerun that quietly creates a second copy fails that test, even when it reports success.
So we split routine daily loads from historical refreshes and stopped them colliding. Then we checked the table before and after repeated runs, including records we had already processed.
A reliable pipeline isn’t one that succeeds once. It gives the same correct answer every time you repeat it, and does no damage when it overlaps with another job. If you can’t safely press run twice, you’re not there yet.
The series at a glance
| Part | The signal we trusted | What we checked instead | What stayed with us |
|---|---|---|---|
| Part 1The Job Ran Green. The Data Was Wrong. | Job status and row counts | What the data should look like: four checks, built into each stage of the pipeline | A database protects the write. It can’t tell you whether the write is right. |
| Part 2The Month-End That Wasn’t There | Loading everything we received | The full list of security and period combinations that ought to exist | Historical completeness is a modelling problem, not a loading problem. |
| Part 3Run It Twice | A rerun that reports success | The table before and after repeated runs, including records already processed | If you can’t safely press run twice, you’re not there yet. |
Can your pipeline safely run twice?
If your team runs a security master or another financial data platform, we will walk through the checks behind each stage of your pipeline: uniqueness, expected periods, the values behind valuation and risk, traceability to source, and what a rerun should do.
Book a working sessionFrequently asked questions
Why doesn’t a green job status mean the data is right?
Green only means the machine ran. Job status and row counts don’t know what the data means. A duplicate delivery adds rows, so the count looks fine. A date in the wrong format still loads. A column that changes type between stages still writes. Every check passes, and the business picture is still wrong.
What data quality checks does a security master platform need?
On this security master build, four checks replaced job status and row counts. Is each security unique for its date? Does every expected period exist? Do the values behind valuation and risk hold up? Can each curated figure trace back to its source? The checks run in each stage of the pipeline, so problems get caught where they start, not three systems later.
How do you find missing historical records in a financial data platform?
A file only tells you what arrived. It says nothing about what the platform should hold for a month two years back. Before calling a load complete, build the full list of security and period combinations that ought to exist, based on business dates: when the security started, where the cutoff was, and which month-ends fell in between. Then compare it with what the platform actually holds.
What should happen when you rerun a data load?
It depends on the record. If the data changed, update it. If it’s genuinely new, add it. If nothing changed, leave the table exactly as it was. A rerun that quietly creates a second copy fails that test, even when it reports success.
Why can two correct jobs still cause a failed run?
The cause can sit outside the code, across scheduling, storage and timing. On this build, a historical refresh and the daily load reached for the same table at once. The logic in each was correct, and the run still failed. The fix was to split routine daily loads from historical refreshes so they stopped colliding, then check the table before and after repeated runs.
