AgileData #22 - Data Lineage, mapping your way to magic

Welcome to the Agile Data Podcast, where Shane and Nigel discuss the techniques they use to bring an agile way of working to the data world in a simply magical way.

Welcome to the Agile Data Podcast.

I'm Shane Gibson.

And I'm Nigel Vining.

And today we thought we'd have a little chat about a thing called Lineage.

So Lineage has been around for a long, long time in the world of data. I remember some of the early ETL tools that we played with or used in anger 20 odd years ago. It must have been one of the things they were always pimping out was their ability to do lineage. So it's been around for a while, it kind of goes out of favor, it comes back in, new tools come out, they don't have it, they then start building it. And if we talk about what Lineage is.

The way I explain it is I use a visualization or a visual thing in my head, which is London Underground, the tube map of the London Underground. And it gives you the ability to see that if you start at one station, there's a journey you can take through a number of other stations to get to your destination. And Lineage for Data for me is the same thing. It says that I have some data in a source system in a data factory, and that data passes through all these bits of code to end up in my report. I want to be able to understand each of those steps in the journey the data takes. For some specific reason, there's some action I want to take by understanding how that data moved. So for me, that's what Lineage is all about.

Yeah, I guess my mind's similar and it's funny that he's mentioned tools in 20 years, but I always, I guess I go to something like the Oracle suite at the time or Oracle Data Integrator, all those tools and Lineage was effectively your left to right diagrams how you built up ETL and data pipelines. You'd start from the left using a source object, you'd grab a target for the object on the right and you'd chuck some widgets between them and it was effectively your journey. The data integrator would say, take the data from the left, go through the widgets in the middle and spit out something on the right. And that was your lineage. It was what we'd call left to right. And it's funny because that's still actually what we call it today. It's left to right, how we get there. And then you can add the complexity because then your right becomes another left and it keeps going and going. And going like YouTube mat analogy.

Yeah, I think Oracle Data integrator. That's only 10 years ago. You've been around long enough that it was Oracle Warehouse Builder or actually it was hand coded pl, SQL in your day, wasn't it?

You're right, it was Warehouse Builder.

One of the challenges with all those tools in the old days was black box code. Even when we use those tools, we still had nodes sitting in the tools tool that had a bunch of code where the developer, or what we call the data engineer now, couldn't use the pointy clicky features of the tool for some reason and so ended up writing a blob of code to do something that was relatively complex. And typically as soon as that happened, it broke the lineage. It was as if when you got to the station, between the station and the next station there was like a tunnel and when you got to it, you could never tell which way the train went because the tools didn't really handle that. And I'm not sure with the latest versions of the Lineage tools, I've seen that they solve that problem. Because with those tools you're still writing a bunch of black box code. For us. I think one of the things that makes it slightly different within agiledata IO when we talk about lineages, you can't actually write black box code. You have to create a rule using a rule type. That's a known piece of config. We understand the technique or the pattern around that and therefore we can always map. Lineage means that there's some things that you can't do right now because they're too complex for something that we've designed a technique for. But that's the trade off, right, Is providing that simplicity. You lose some of the complexity until we figure out how to do it.

Yeah, yeah, the black box. Yeah, you're right about the black box. That's always the sticking point. I know just recently I was helping a customer and they were moving between products, I guess you'd call them ETL products. And one of the must have features for their new platform product was Lineage. And the first thing, I think pretty early on they realized that all of their legacy code, at least good, 95% of it, was embedded in black boxes. So there is no lineage when you have a black box because it's not a left to a right through a transformation, it's a left to a right. But the code in the middle could actually be bringing in five more tables, producing some temp steps, a whole lot of transformations. So the relationship between Left and right.

You know, there's nothing like it would look like on a lineage graph. You've lost all of the complexity, all the transformations and actually what's happening in that black box. And Shane, you're right. Effectively the only.

I guess the organic way to do lineage is you need to keep your building blocks small and transparent. Everything is a left and a right with the transformation.

Because that's the only way you can really get a very strong lineage map out the other side of it, because all of the known touch points are kept very visible.

Yeah, I suppose if we look at that black box code again, it depends on the technologies that people are using. If the data pipelines or the transformations they're writing are purely SQL based in theory. Right. There's a structure there, there's, you know, some data going in, some joiny stuff. You might be creating some temp tables, but they're all based on SQL. So that's a pattern that you probably can create Lineage from. But I remember when I was doing projects where there were a lot of SAS people involved, they had this great habit of using SAS macros and they were really powerful. But what it meant was, in the middle of your code, you could go call something else. I think these days it kind of sounded. It sounds like an API, really. You'd go and call this thing, it would do a whole lot of stuff and give you back the answer. The benefit of that was you could write those bits of code once and reuse them time and time again. But within the lineage, the code that you're looking at is really calling something that's outside its control, outside something that has no visibility to those techniques, again, make it even more difficult to get Lineage from those types of tools.

Right.

Yeah, yeah, I agree. And your comment around, I guess parsing SQL inside a black box or any type of code is it's.

You can do it with some success to a certain level. But given that the power of SQL and some people, you know, like to use it to its max ability, you can effectively have a SQL statement which is a whole lot of inline table table statements. There could be functions in there, but once you've nested a certain number of layers of SQL down, it becomes quite hard to tease out programmatically what the sources and targets are anymore, because you're effectively gone down quite a deep hole. A simple statement like.

Select a value from table X, join to table Y to produce output Z. Yep, you could probably infer the lineage quite cleanly, but you can pretty quickly run into complexities once you've got multiple layers of nested tables and computations down.

And what about, you know, we're seeing a lot of data engineering teams now using code, you know, Python code to do a lot of their transformations, airflow for scheduling and orchestration of it. My impression is that makes it worse, right? We, we move away from the standardization we get with SQL, the known patterns of code to, with Python you can import anything you want, you can write it in so many different ways to achieve the same task. So blobs of Python code being scheduled is probably going to make that lineage even harder, right?

Yeah, I think the whole is more and more people, I guess generate, you know, generate the code or generate their SQL on demand effectively at build time there is no lineage because it hasn't been created yet. It's often created on the fly and it can change constantly. So what you see and what actually runs can be two different things depending on how that code. So auto generated code slightly problematic again, unless it's been set up to also generate lineage on the fly as it's built. But I guess that's another complexity.

What about these cool templating engines, things like dbt, these things that are really hot at the moment, do they solve the problem? Do they give us automated lineage because they're a templating technique?

It's actually a very topical one. I'm going to say yes and no.

If your inputs into the template follow a loose left and right pattern, then you do effectively have your lineage. So if you're generating, say you're generating a SQL transformation pipeline using a template your left and right. So your inputs and outputs that you're feeding into your template unknown. So you sort of have your, I guess you have your lineage up front, then you push it through your template and you get your nicely constructed piece of SQL out the other side that you execute. So I think they help as long as you go into their design with the intention that you need to be able to track that lineage. I think so it's probably more of a mindset thing around I need lineage, I need to at a minimum know the left, the right and the transformation for every piece of templated code that I'm going to produce.

So you know, if we look at it, we would always say create a config based tool that generates your lineage for you as well as your code. But you know, there are some other techniques people can apply when they're not writing stuff as cool as we do. So one I've seen is a code parser Right. So if you're writing in a language and your divs or your data engineers have some form of standard in terms of the way they write, in theory, you can write a parser that takes each of those blobby black box bit of code and determines the lineage of what went into that code and what went out, Right?

Yeah, absolutely. Yep. Your config would, I guess, provide that story.

Yep.

It goes into your code generator. So, yes, if your devs are disciplined and they work to some sort of markdown pattern or config pattern, then yes, they're capturing their lefts and rights as they go along and then they're building their, I guess they're generating their executable code off that config.

Right. But.

I'm saying if you're not going for a config based way of working. So let's look at other ways that data engineering teams could get lineage without taking the same approach we've taken, taken. So in theory, as they write their Python code or their Scala code or their SQL code, as long as they're using a similar way of writing it, in theory you could custom write a parser that scans all that code and gives you the lineage.

Oh, sorry, Yes, I was sort of inferring that when I said they might use a markdown language, for example. So as they write their code, they're putting markdown tags in the code itself. So they're basically leaving a little trail of breadcrumbs that are wrapped in a markdown syntax. So when they run a something like maybe Sphinx or a documenter over the top, it goes, cool, lots of tags, grab, grab, grab, grab, grab. And it produces your lineage map because it's basically read through all the code, picked out all the sources and targets and transformations, these breadcrumbs, I guess, and you produce lineage. Absolutely.

No, what I meant is some magic that actually read my code with no hints and figured it out for me because I always write my code in the same way.

Yeah, I guess so, yes, if you followed a strict structure. Yep. Your parser would then, yeah, read it. Yeah, I was assuming you were going to give it hints, but you could do it straight from the structure.

That was my second technique was the idea that you could use markup to either in line with your code or at the top of the bottom of your code, provide hints to the lineage engine to say, this is what went in and this is what went out. And ideally, these are the rules we applied in a way that somebody who was looking at it could understand.

Yeah.

I personally quite like a little bit of markdown embedded in the code. Just recently I did a piece of work where all the devs added. It was literally only an extra four or five lines of markdown into their code. And we had a.

Document generator that published all that into Confluence for them, using that markdown to basically create a fully fleshed out version of their code, along with additional links and hints and some snippets of information around Lineage. Definitely doable.

Another technique people could use is basically writing a logging framework every time a piece of code ran. Rather than the hints being embedded in the code, that code is actually broadcasting some events to via some form of API to an event engine where you store it. So it's saying, hey, creating this table, hey, updating this table, hey, dropping this table. And those events could then be turned into Lineage. Right. Because we're starting to broadcast the things we're doing with the data, which could be put back together to tell us our Lineage story of what was moving where.

Right. Oh, I love that one. That's actually really cool. Yeah, that's a great idea. Yep, that would definitely work well. And that's an evolving Lineage story as well, because as elements of config change, those logged events effectively are picking that up. So you could see an evolving Lineage story over time. Nice.

The other thing you could do is you're using some kind of technology to create, add, update or delete that data. Right. So most of those, you know, even outside of the true database world, most of those execution or storage tools have some form of logging as well. Right. It keeps track of what was created and what was changed. So you could actually go through those logs and effectively create a story of Lineage as well, couldn't you? You could say, you know, the, the data storage platform has told me these things are being touched at this time. And therefore you could infer that, you know, there was some Lineage there. Now the problem would be, if you're running 100 of those things, it's a little bit hard to know which ones went in which order. Right. Because, yeah, they're all going to be updated at various different times.

Yeah, yeah. As soon as you said that, I suddenly thought, and then with. Exactly for the reason you said, potentially with a serverless on demand architecture, you're suddenly tracking a whole lot simultaneously and overlapped and disconnected events happening. But we do elements of that. We use a pub sub model for, I guess, our events talking to each other. And that way they're only loosely coupled because they talk to each other. By one event saying, hey, I'm done. And the next one reads that message and says, cool, I'll start. So they loosely hand off, handoff. But to look at the log, when all that's happening, it's chaos because these things are firing simultaneously all over the place. To infer a lineage would be tricky, potentially doable, but a lot of the stuff is simultaneous, so it's not a parallel flow. A file arrived, a table was loaded, the table was, you know, dumped at the end.

And the other assumption we've made with all these techniques is we control the entire end to end supply chain and programmatically we can get that information. So that's all great until we put Tableau on top of our data because then we start losing the visibility of. Right, well, that data set's been used by Tableau, but what's happening in that dashboard? What transformations are happening in there? What aggregations, what metrics have been calculated? So to get true end to end lineage, you actually need every moving part in your data infrastructure, your data architecture to be visible or talking to whatever's doing the lineage. Right?

Yeah, yep. As soon as we start across, I guess.

Multi tool, multi platform type thing, then we have, we start to run into those, I guess integration disconnects where your BI tool as users can create their own transformations and workflows, so your ETL layers being nicely tracked and then effectively once your data is exposed, you've got another whole layer of potential transformation going on. You've got to be pretty disciplined if you start to round up that metadata and pull it back to, you know, keep all your lineage metadata centralized. Not impossible, but you're starting to get into those cross platform tool integration challenges that everyone has.

And I remember back in the, I think it was probably the warehouse builder days, not the ODI days, there was, I can't remember what it was called, was it oem, Open metadata, something? And it was back in the days when Business Objects was actually a company, not part of a big German behemoth. And Cognos was around and I think it was even maybe Oracle Discoverer, it was that old. And the theory was each one of those vendors would comply with a standard for the semantic layer within their tool. And so in theory you could create Business Objects universe on top of your data and Cognos could read it right. With no need to do any extra work because they were sharing that, that model, that storage or metadata layer. And you know, what happened was none of the vendors really supported it. So, you know, you never really got cross Platform or cross tool compatibility. And, you know, probably what we're seeing again at the moment is, you know, everybody's pretending they can play together, but they don't want to play 100% because then they lose their, their slightly unique proprietary benefit. So whether we'll see that will change or not. But you know as well as the technology, we've got to come back to the core of why the hell do we want Lineage anyway?

So, you know, I think the nirvana is this idea that a data engineer is going to see that there was some data mutation in one of the data factories and they want to understand all the code that's going to be impacted when they add this new column and all the reports. They're going to go into some pretty tool and they're going to right click on it and say, show me the Lineage, show me the money. And it's going to give them an answer where they know and save them time figuring out what they need to change. But in 20 years, I've never seen that happen. I've seen Lineage being demoed a lot and one of the key criteria for buying a product, but it's really. Have I seen a data engineer or a developer actually use the bloody thing?

Yeah, yeah. It's funny you say that because just literally this week at a place I'm doing some work, there's been a lot of discussion around.

A lot of people asking, where has this source object been delivered in the target, or vice versa, where was this metric derived from? Where's it come from? And it becomes quite tricky because the metric that they're looking at, you know, it's an effect table. The fact tables come from a staging table. Staging table is built of, on top of three other staging tables under that, these multiple source tables.

Short of reading through pages of SQL code across multiple pipelines.

It actually proves quite tricky, quite hard, because there's no. You can't right click on that metric and say, you know, where did you come from? Because ideally you would do that and it would say, cool, I came from here, I was transformed from here, and I ultimately came from this column in the source system.

You know, and that's quite nice. But because the Lineage is so hard to implement that well, it just doesn't exist. People go off and they search for a column name in the source system and try and work it out themselves.

Yeah. And so I think the key there is, it's not the fact that the data moved left to right as important as what we did when we were moving it.

So It's a way of publishing the rules that we've applied via code to change the data that actually has the value. And so most of the lineage tools don't do that. They don't actually expose what we're doing to the data, they just tell us we've moved it from here to here. So it has some value because it helps us very quickly know where to look, which bits of code, out of the 1000 Python scripts that we've plumbed into airflow, which of those we should start reading. But it doesn't give us the answer yet. Ideally, we'll move to a world where when we say lineage, it will actually tell us a story, right? It'll tell us the beginning, the middle and the end. It'll tell us when the big bad wolf came and did some bad things to our data, and it tells us when the data quality woodcutter came along and made our data right for us, right? So we understand the story behind that data in a way we can understand if we're not technically literate to read Scala code. I think the other thing that was interesting when we started with this was.

The idea that we built a London Underground tube map. But very quickly we stumbled across something that's been infinitely valuable for me, and that's the ability to view the lineage of the data as it moves through the system and actually trigger that data to execute from a point going forward. What it means is, for us, I can go in and say, well, look, there's this table that's landed in history, and we've got that set as manual execution at the moment. So rather than typically, what would happen is when code's been running as production, like code for us, as soon as new data turns up, all the dependent rules execute and the data moves through that map to the end user. But as we're doing the initial development of rules, we leave that in manual so that we can create some rules and then execute them and see what happened and just iterate through that until we get the rules right to do that. What we do is we go into that lineage map and we find that history table that we know we're basing our rules on, and we say run. And then it goes through the lineage map and figures out all the dependent rules that are upstream and runs those. And I found that.

Infinitely valuable for.

Actually creating and iterating on data.

One of the things we probably want to move towards is being able to see any form of anomalies that happen with the data or those rules on that Map. So being able to see where things are starting to being detected as being a little bit funky, and using the lineage map as a way of just focusing where in that flow it is without having to find it in some other way. So, for me, it's that idea of taking something that's really complex, like the London Underground, and giving you a visual clue of where you might want to look next. There's a blockage at that station, so either go and fix it or bypass it. That's the value for me of a Lineage map in the new world.

Yeah.

I think our lineage map's quite impressive, actually. It's one of our early drafts, but it very quickly visualized.

All the patterns that automatically showed where lines were disconnected because there'd be two stations with no lines between them, or there'd be a station that, you know, had a line coming out one side but not the other. So the lineage map was quite a nice way of visualizing a whole lot of complexity down to a simple picture, which has got circles forced, which represent tables or objects, and the lines between them that show that there is a rule that moves data along that line. And yes, letting Shane right click on anything and make it run was also useful as well, because that.

Simplified a whole lot of his interactions. He didn't need to know what was happening under the covers, he just knew that he wanted this line to run, to push something down to the end of the line where he's expecting to see it turn up. So, yeah, no, that was a. That was a nice little side project, that one, which will be useful going forward.

Yeah, and it's, you know, one of the cool features that I've used and anger every day since we created it, so lots more to add to that one to make it even more magical. But right now it's a simple way for me to do something that used to take me half an hour. So massive value in that and it looks sexy too, so that's important. All right, well, I think that's pretty much us done with Lineage. I think what we're saying is it's hard. There is no magic, out of the box, lineage thing, thing at the moment, especially if you've got multiple products and multiple developers and multiple bits of data. So figure out what action you want to take off the Lineage, what you're going to do that's going to save you time or make things safer, and then focus on delivering that bit so that your team can use Lineage to make their life easier. And then probably monitor it to see if they actually use it, or you just thought it was a good idea and like 10 years ago or 20 years ago it was a great demo, but nobody ever used it.

That's true. Thanks, Shane.

And that Data Magicians was another agile data podcast from Nigel and Shane.

If you want to learn more about.

How you can apply agile ways of working to your data, head over to agiledata IO.

SA.