What are the tools and techniques for data lineage analysis and visualization?

Additionally, Data Lineage can help demonstrate the journey of the data as it passes from Origination to Back office reporting. It can help track data at element level as it transforms from one application to another. This helps build trust and transparency in the data that is being reported. Data lineage can help visualize and track data movement at element level and provide organizations more control over the process as the data is transformed.

Data model and lineage can be distinguished as static vs. dynamic relation of data. Data model shows how the data is structured in a static (design time) moment, while the lineage shows the dynamic nature of the data flow. Though the lineage can be enriched with a model, the feature is not found in many commercial tools. Enterprises invest heavily on data architecture and modeling at the beginning of a data initiative, with a meaningful model, but over the time the models drift and model gets out of date as the structure and logic are changed without changing the model. This is one of the risk combining the data model with data lineage tool.

Data Lineage can be derived from following: 1. Source to target Mapping in Excel Spreadsheets or similar formats. 2. Source and target metadata and source code transformations. 3. Metadata management tools like informatica metadata manager etc. 4. Business glossaries and enterprise business catalogs There are many tools available in market that use AI to derive the metadata for a source to target mapping e.g Informatica Metadata Manage, Manta etc. Tools like Collibra store the business catalog and element level mapping as well.

To be able to use data lineage, you'll need to use proper tools for storing and using your data. If you use a database (OLTP or OLAP), you'll often have metadata that will help you get started. Also, depending on how and where you make your transformations (using SQL in the target database, using a transformation framework such as spark, or a transformation/modelling tool such as dbt), code can be analysed to provide you some lineage.

Well thought-out data modeling exercise typically includes logical, business data model represented in multiple layers. Many of the commercial data lineage products lack the ability to combine this rich business information into their lineage tool. The lineage products could also include a feedback mechanism from the humans (data stewards, analysts, or end-users) who possess knowledge of data from their years of experience or by sheer analysis they might have conducted. Such feedback mechanism could be incorporated along with scoring of feedback, or a human validation to review and certify the findings.

For SQL, there is the SQL Lineage python lib. Some tools (bigquery) offer automatic lineage as well. For spark, you can use OpenMetadata or read the logical plan to extract lineage. With dbt, they offer a simple lineage graph based on your models, and since its open source, you might be able to read the plan to generate column-level lineage (this is a open discussion in their github).

a. Combine logical data model, and business data model b. Feedback and scoring These additional features will be very valuable for an analyst, or data engineer to leverage and understand the data flow go a long way to capturing the tribal knowledge on data.

It would be good to have a data lineage tool that would cover the data flow across different tools and platforms in an organization. Most current data lineage tools lack integration with other tools and data sources and hence are not able build a complete data model.