How do you track data lineage in your engineering work?

In Europe, For GDPR compliance, having clear data lineage is very important. If a person withdraws consent of his data getting stored, it has to be deleted immediately from the system. If we do not know the orgin of data and flow, it would be difficult to comply with GDPR rules

We have 2 types of lineage, each with their own reason for usage. Row level lineage refers to the ability to track data from its source to its destination on a row-by-row basis. This means being able to identify exactly where each row of data in your destination came from, which can be crucial for data validation, debugging, and compliance purposes. Column level lineage refers to the ability to track how each column in your data has been transformed or manipulated from source to destination. This can be important for understanding how your data has been processed, ensuring data integrity, and validating data transformations.

It's very important from a compliance perspective where and how the final reports has been sourced and transformed before the output has been considered. Let me ask you like a laymen. What is the proof that your reports are speaking the truth? What are the measures and dimensions available for output validations? And how easily is the traceback process for the source of truth? Answer: Here comes data lineage especially in AI/ML solutions since lots of outputs are statically driven based on historical behavior. AI/ML driven solutions are not deterministic rules driven hence data lineage especially plays a very important role in this domain. Don't you agree?

When talking about data, debugging is not always easy. When we don’t have the expected data, we usually spend a lot of time identifying the cause of this outcome. Tracking data lineage makes this task easier and faster, giving us more time and energy to spend on delivering useful data in an effective and efficient way. Of course, all benefits are very important. But, from the perspective of a Data Engineer, debugging and performance are the most important to me.

Context is one if not the most important information about the actual data itself, and this is where the tracking part of data lineage really matters. Because it will give much deeper/wider perspective about the actual data (such as from where, who is supplying the data, etc) and how it should be processed, represented, and used.

You can track data lineage by beginning to create a comprehensive inventory of your data sources, including databases, files, APIs, and data streams. Document where data originates and where it flows. However, Manual data lineage tracking can be resource-intensive, so consider automating the process wherever possible, especially as your data ecosystem grows in complexity. Specialized data lineage tools can streamline this process and provide real-time visibility into your data flows.

Manual lineage is best avoided - it's more like lip service - looks good at first but fails in practice. Manual lineage is no different than manual documentation, and as any small dev team knows, internal docs quickly get outdated, while the effort to maintain them is about as high as maintaining the automations. It's a much better idea to automate your lineage collection from the start.

Manually tracking data lineage involves meticulous documentation of data flow from source to destination. Start with clear data models, noting sources, transformations, and destinations. Maintain detailed records, and establish robust naming conventions. Regularly update lineage documentation to reflect any changes in processes or sources. However, manual process relies on individuals to maintain records, which can be time-consuming, error-prone, and challenging to scale. It's not recommended due to its potential for inconsistencies, delays, and increased operational costs compared to automated tools or systems designed for this purpose. Automated solutions offer more accuracy, efficiency, and reliability in managing complex data flows.

Although there are several tools to store metadata on external data structures, I've had some great experience integrating metadata with the database. This is achieved when fields to store metadata on the same record within the data itself are created. Also, besides regular structures like timestamp or varchar, sometimes JSON datatypes can be valuable. Another approach to integrating metadata on the database with less storage consumption is to have another table to store the metadata and have a single field with the key to this adjacent table, again without overengineering and setting up a new tool.

To track lineage, you need to start from ingestion. Unfortunately, there are few ingestion tools in the open source with lineage support. dlt library is the first open source loading library that offers full lineage support in the form of column (via automatic schema inferrence) and row (via load id and pipeline name) level lineage. There exist also other open source solutions specific only to some technologies, such as Apache Atlas for hadoop. In the closed source, the ecosystem is more varied but also more fragmented.

Alation is also a very good tool to manage data lineage . Lineage can be tracked at Table level, column level as well as at cross platform/ tools level

While tracking data lineage automatically, keep a keen eye on cost for data and UPSERT intensive env. Example - While creating a Snowflake account SNOWFLAKE database created. It has ACCOUNT_USAGE schema, which has ACCESS_HISTORY view for Snowflake Data Lineage. It has 6 columns : 1) QUERY_ID 2) QUERY_START_TIME 3) USER_NAME 4) DIRECT_OBJECTS_ACCESSED 5) BASE_OBJECTS_ACCESSED 6) OBJECTS_MODIFIED In multi tenancy environment - In prod running millions of queries, historical lineage would be cost heavy to read history to create lineage between different tables and views relationship and updates. Try a work-around such as in case your project is using DBT for better efficiency in terms of cost of query history

Where is the collaboration in lineage? The answer is Schemas! Data contracts, or curation & documentation are collaborative efforts, so anything around defining and documenting the schemas.

By handing over lineage from ingestion to the transformation, you are able to have full lineage. So how to be effective? 1. Automate schema generation and maintenance via data contracts and schema evolution 2. Loop humans in every time new data appears, to curate it To facilitate this, you could leverage open source tooling such as dlt, which offers ingestion lineage, and SQLMesh which offers transformation lineage.

Horizontal Data Lineage traces data elements across the enterprise. In contrast, the vertical dimension of Data Lineage provides depth, connecting data to business processes, roles, rules, and definitions, offering context at every pipeline step. While horizontal lineage shows data flow, vertical lineage emphasizes why data moves, its business context, and who manages it. It operates on three levels: business (detailed processes), concept model (covering business relationships and rules), and physical data model (implementation of the concept model). Focusing on horizontal lineage misses this depth, reducing Data Lineage's full potential. The vertical dimension enriches the entire data journey's context.

Proper data lineage is not easy, however it's better to have something rough and imperfect rather than none at all. When starting, it is advisable to start with something simple which put less burden/toil, and other investment (such as massive upfront cost and large organizational workflow). Whenever possible, start with something that does automatic enablement instead of enforcing it as another manual workflow to get easier adoption. Get a first iteration done, show the impact to everyone to motivate and get some lesson learned. Next step would be to optimize or add a new thing followed by another show and tell on the new benefits. Rinse and repeat.