Data Lineage and Master Data: Tracing a Number Back

Data lineage is the record of where a number came from, hop by hop, back to the point a person or a system first produced it. Master data is the separate agreement about which record is the customer when several exist. Lineage answers where did this come from, master data answers who is this, and a firm that has solved one has not touched the other.

There is one question that decides whether either of these is worth the trouble, and it is not a question anybody asks on a calm day. The question is asked on the day something has already gone wrong: what else consumes this field? Somebody has found that a value arriving from an upstream system is not the shape it used to be, and the only thing that matters in the next hour is the list of everything downstream that was quietly reading it. A firm with a complete record answers that from the record. A firm without one sends somebody to find out, and finding out takes days. The distance between those two answers is what lineage is for.

What does it mean to trace a number, and how far back does tracing have to go?

Take a household budget on a phone. The total at the bottom says Rs 45,000/- of monthly outgoings. Somebody asks why it went up. The answer points at the electricity line. The household asks why the electricity line went up, and the answer points at the meter reading. The next question is where the meter reading came from, and the answer is that a person walked up to the meter and wrote a number on a slip. The slip is the only answer that ends the conversation. At the slip the number stopped being a copy of something else and started being a thing somebody produced.

Data lineageThe record of where a number came from, hop by hop, back to where it was first produced. is that walk written down in advance, for a number that matters, so nobody has to make the walk under pressure. Each step of the walk is a hopOne step in that record, from one place a number sat to the next place it sat.: one move from a place the number sat to the next place it sat. A trace that stops at the previous system has recorded a handover rather than an origin, and nobody ever asks about a handover. Tracing has to run all the way back to first production.

Stopping early is the commonest way tracing gets declared finished, and it is worth being blunt about. If the answer to where did this come from is the reporting system, all that has been learned is that the number was copied. If the answer is a field on an application form that somebody typed on a handset in the middle of a Tuesday, what has been learned can be acted on: the form itself can be examined, along with what the label said, what values it accepted, and what happened on the day the label changed.

What are the seven hops from a committee pack back to a typed field?

At Sumeru Bank Limited, an invented lender, the intake chain takes retail personal loan applications from a handset through to a decision. In one steady month it started 10,000 applications and carried 8,600 of them to a decision. One number inside that chain is worth following end to end: the declared monthly income figure. The declared figure appears on a monthly pack that goes to the credit committee, and it began life as a figure an applicant typed into a form.

Between those two points sit seven hops, and the bank numbered them so that every later note could cite a hop by its number rather than by describing it. Hop 1 is the figure on the credit committee pack. Hop 2 is the monthly aggregation that produced that figure. Hop 3 is the decision record for one file. Hop 4 is the value the income corroboration rule compared against. Hop 5 is the corroborated figure computed from three months of statement. Hop 6 is the statement feed arriving from the upstream system. Hop 7 is the field as the applicant typed it. Seven hops is not a standard and nobody should expect their own number to be seven; it is simply how many times this particular figure changed hands at this particular bank.

What is the difference between naming a system and naming a field?

Here is where most lineage work quietly stops being useful. There are two ways to fill in a hop, and on a diagram they look identical. One names the system the number sat in; the other names the exact field inside that system. Both produce a labelled box with an arrow coming out of it. Only one of them is an answer.

Naming the system identifies which team to ask. The team can always be rung, so on a calm day naming the system feels like enough. Field levelNaming the exact field at a hop rather than only the system it lived in. naming identifies what changed and what it fed. Whenever anybody actually opens a lineage record, the question is what else consumes this field, and only a field level record answers it without a person going away and working it out. Ringing the team produces a good, willing colleague who says they will check. The record was supposed to save that conversation.

What could this bank actually name at each hop?

At month 6 Sumeru Bank Limited could name the system at all seven hops. The diagram existed, it was complete, and nothing on it was wrong. The bank could name the field at only four of the seven, or 57.1 per cent. Hops 2, 3 and 6 carried a system and no field: the monthly aggregation, the decision record and the statement feed.

The pattern repeats almost everywhere, so it is worth asking why those three and not the other four. The hops that get recorded at field level are the ones somebody already had an independent reason to write down, and the hops that stay at system level are the machine to machine handovers nobody was ever asked to describe. Hop 1 had a definition on the pack. Hop 7 had a label on the form. Somebody wrote the 34 lines of the income corroboration rule by hand and can read them, and hops 4 and 5 are both named inside it. Hops 2, 3 and 6 are a query somebody built, a write into a record and a feed arriving from elsewhere. Nobody was ever refused anything for not describing them.

What does an unrecorded hop cost when somebody finally asks?

In month 8 an upstream income field changed format on one channel, and six weeks passed before the monitoring flagged it. How that change was eventually detected belongs to model monitoring rather than to lineage. The moment it was found one question got asked, and nobody cared about any other: which reported figures has this touched?

Answering it took 9 working days. The change was not hard to understand and nobody was slow. Somebody had to sit down and re-deriveWorking out by hand what a step must have done, because nobody recorded it. the monthly aggregation by hand: open the query, read what it actually did, work out which figures it fed, and check. Three hops were unrecorded at field level, and the bank's own reckoning is about 3 working days of re-derivation per unrecorded hop. Three times three is nine. The cost of a missing hop is not paid when the hop goes missing; it is paid, all at once and with somebody waiting, on the day a question arrives that the record was supposed to answer.

Put that 9 working days beside what a complete record would have given. With all seven hops recorded at field level, the same question is answered from the record in under an hour. The bank counts that as about 0.1 of a working day. At this bank a working day is 7 hours, so 9 working days is 63 hours of somebody's attention against roughly 42 minutes. Nine working days is also 9 of the 20 in a month, so the answer to a question asked on the first of the month arrives when the month is nearly half gone.

What does the record for one hop have to hold?

A hop record is short, and its shortness is the point. Sumeru Bank Limited settled on five numbered items for each hop. Item 1 is the field as it arrived, by its exact name. Item 2 is the field as it left, by its exact name. The two are often not the same, and that difference is where most surprises live. Item 3 is what was done to it in between, in one readable line. Item 4 is which system held it while that happened. Item 5 is who is accountable for that system, by name and role.

Four of the five items are cheap to write and the fifth, what was actually done to the number in between, is the one that costs an afternoon and saves the nine days. Anybody can name a system. Working out and writing down that the monthly aggregation takes the median of the corroborated figures across all decided files in the month, and drops files decided outside it, takes somebody an hour of reading a query they did not write. The hour is the whole trade.

A second list connects to the hop record, and the connection is not decorative. For every field the chain consumes, the bank keeps eight numbered data requirement items: the field, where it comes from, who is accountable for it at its source, its format and permitted values, how often it changes, what happens when it is absent, what may be retained and for how long, and the consent basis where one applies. Item 2 of that list, where it comes from, is a single hop of lineage. Lineage is that item followed all the way down.

And item 6 is where the same failure shows up again. Of the 14 fields the reading step extracts, 3 carry all eight items, or 21.4 per cent. A further 5 fields, 35.7 per cent of them, carry every item other than item 6, and the 3 sit inside those 5. Item 6, what happens when the field is absent, was missing for 11 of the 14 fields, being 78.6 per cent. A missing item 6 is the same class of failure as an unrecorded hop: nobody had written down how the chain behaves when the expected thing does not arrive. A missing hop record and a missing item 6 are both an unasked question, and both get answered under pressure by somebody guessing well.

How to Create Data Lineage for an Automated Financial Report that is already running?

Most lineage work starts too late to be voluntary. A report is already produced every month by a chain of steps nobody assembled in one sitting, and somebody asks a question about one figure on it. An automated reportA figure produced by a chain of steps rather than assembled by a person. makes this harder than a hand-built one in exactly one way: with a hand-built report there is a person who remembers, and with an automated one there is not.

Sumeru Bank Limited did it in six numbered steps, and the order is the part worth stealing. Step 1, fix the figure: name the exact figure on the exact report, with the date it was produced, so there is no drift about which number is being traced. Step 2, work backwards and never forwards. Step 3, record each hop at field level, five items each. Step 4, stop only at first production, and count what could not be recorded. Step 5, build the reverse index: for every field on the path, list what else consumes it. Step 6, put a name and a date on the record and state what makes it get re-done.

Step 2 is the one people get wrong. Tracing forwards from a source means following every branch it feeds, and the branches multiply; tracing backwards from a figure follows one path, and one path is what the question was about. Starting at the source produces a large, correct, unfinishable map. Starting at the figure finishes the work this week.

Step 5 is the step that gets skipped, and step 5 is the step the month 8 question needed. The trace being built runs backwards along one path. A question about a changed field runs forwards from that field along every path. Both directions read the same record, and only one of the two gets built by accident.

What is Master Data, and why is tracing not enough on its own?

Tracing so far has assumed something that is not usually true: that everybody agrees which customer the number belongs to. Drop that assumption and the whole apparatus above stays intact and stops being useful.

Picture a wedding. The guest list is a spreadsheet somebody has been adding to for three months. Two different people added one aunt, so she is on the list twice, once under a shortened name and once under the full one. Every other column is correct. The caterer counts heads from that list, the seating plan reads from it, and the car arrangements read from it. Nobody has made a mistake anybody can point at, and the household is going to pay for one extra plate and set one empty chair.

Master dataThe agreed answer to which record is the customer when several exist. is the agreement that fixes that: which record is the person, when several records describe the same person. Master data is not a trace, and no amount of tracing produces it. Lineage answers where did this come from and master data answers who is this, and a chain can trace every hop of a figure belonging to somebody it has recorded twice, with both traces perfectly correct and about different records.

How does the same person end up existing twice?

Not through carelessness. The same applicant legitimately arrives under more than one key, and the absence of anybody's mistake is the part people find hardest to accept. At Sumeru Bank Limited an applicant can carry three: the application system's own reference, the core banking customer number where one already exists, and the identity record the identity match step checks against. Three keys make three pairs that all have to agree, and nothing in the arrangement forces them to.

The counts are worth sitting with. Of the month's 8,600 files, 1,032 matched an existing core banking customer number, or 12.0 per cent; the other 7,568, being 88.0 per cent, were people the bank had no earlier record of. Of those 1,032, 41 matched two customer numbers rather than one, or 4.0 per cent of the matches. A duplicateThe same person existing under two records that nothing links. rate of 4.0 per cent sounds like a rounding error, and 41 files a month is a person's afternoon every month. The share and the count have to be quoted together or not at all. Over a year that is 492 files, and every one of them is a decision made about somebody the bank was describing twice.

What resolves it when two records match, and why is an unwritten rule the danger?

Something has to decide. When two customer numbers both match one applicant, the chain cannot stop and it cannot pick both, so one of them wins. The statement of which one wins, and why, is the resolution ruleThe written statement of which record wins when two of them match..

Until month 11 Sumeru Bank Limited had no master record at all, and the identity match step took the more recent of the two. Taking the more recent record is a defensible choice. A recent record is more likely to carry a current address and a current employer, and if somebody had proposed it in a meeting the meeting would probably have agreed. The difficulty is not that the rule was wrong; it is that it was never a rule, only behaviour, and 41 files a month were resolved by it inside a chain whose entire justification was that its written components could be read.

Which of the two is fixed first?

Master data, and the reasoning is uncomfortable rather than complicated. Lineage built on top of an unresolved entity does not fail loudly. The answers it produces are confident, complete, well-formatted and about the wrong record. A missing answer announces itself and a wrong one gets acted on, so a confident wrong answer is harder to catch than a missing one.

Back at the wedding: if the guest list is fixed first, every count taken afterwards is right. If instead a beautiful record is built of exactly which spreadsheet cell fed which count, the result is a precise trace of a number that describes one aunt as two people. The trace is not wrong. The trace answers a question nobody asked.

A practical order sits inside that. Agreeing which record is the customer is a decision somebody has to make and write down. The decision needs no new system: at Sumeru Bank Limited it took one sentence stating which of two matching records wins and why. Building lineage is an exercise that takes weeks and touches every team the number passes through. The cheap thing goes first, and the cheap thing also happens to be the one that makes the expensive thing worth doing.

Who actually asks for this, and what do they do with the answer?

Three people ask, and each of them wants a different part of it. Knowing which one is asking determines what to build first.

An examiner or an internal reviewer asks the accountability question: show me how this reported figure was produced. The examiner is not testing whether the figure is right. A figure nobody can explain is a figure nobody can correct, so the test is whether the firm can say how the figure got there. The examiner wants hops 1 to 7 with item 3 filled in on each. Neelima Rao did the independent validation at Sumeru Bank Limited and built no part of the chain. She could read the diagram and could not read the aggregation, and the record exists to close that gap.

An operator asks the impact question, and asks it in a hurry: something changed, what does it touch. The reverse index answers that and nothing else does, and the answer is only useful inside the first day. Ismail Sheikh, who runs the exception desk, needs to know whether the files on his queue this morning were decided on the old shape of the field or the new one, and he needs to know before lunch rather than in nine working days.

A credit or finance person asks the comparison question: is this month's figure comparable with last month's. A figure computed across a book that counts 41 people twice is not comparable with anything, including itself, so the comparison question is the one master data answers rather than lineage. The examiner wants the path, the operator wants the reverse index, and the credit person wants the entity settled, and a firm that builds only the path has answered one of the three questions it will actually be asked. A household reading a bank statement wants all three too: what is this charge, what else is on this card, and is this the same shop as last month under a different name.