Data quality Lineage Root cause analysis

[Niyojit]

Hi everyone,

I'm working on a client use case where they want to visualize data quality together with data lineage.

My current approach is to combine:

• Unity Catalog Data Lineage for upstream/downstream dependencies
• Lakehouse Monitoring (or other data monitoring capabilities)
• DQX (Data Quality Expectations) library for table-level data quality checks

The idea is to display the lineage graph and, for each table in the lineage, show the corresponding data quality metrics/check results (e.g., passed/failed expectations, freshness, completeness, etc.) directly alongside the table node.

I'm wondering:

• Has anyone implemented something similar in Databricks?
• Is it possible to enrich the lineage graph with DQX or monitoring results?
• Are there recommended APIs, system tables, or best practices for achieving this?
• Or is there a better architecture or approach to solve this use case?

I'd appreciate any suggestions or insights from the community.

Thanks in advance!

[balajij8]

Hi Niyojit,

You can combine Unity Catalog lineage, Lakehouse Monitoring, system tables and DQX into a single unified data monitoring mart. Unity Catalog’s native lineage UI doesn't generally support custom overlay graphics directly on graph nodes. You can consolidate these tables and monitoring outputs into a dedicated metadata mart to power custom visual lineage graphs in Databricks SQL, Streamlit or AI BI.

Data Tables

You can use several key system tables and other tables to create a metadata mart

• Lineage Dependencies - Query system.access.table_lineage to map table-to-table dependencies alongside execution context (source_type such as JOB, PIPELINE, NOTEBOOK, or QUERY, entity IDs, and event timestamps), and system.access.column_lineage for column-level transformations.
• Data Quality Monitoring - Leverage system.data_quality_monitoring.table_results for automated freshness and completeness metrics, health statuses (HEALTHY, UNHEALTHY, TRAINING, ERROR), and root-cause metadata including upstream job IDs. Because this table stores time-series data, you will need to window over snapshot timestamps to isolate the current state.
• DQX Expectations - Pull the custom DQX storage destination (Delta tables or monitoring outputs) to gather expectation names, pass/fail states, actual versus expected values, and execution timestamps.
• System Tables - Use system.information_schema.tables as your base inventory (row count, size in bytes, owner, creation/modification dates), system.access.table_usage to quantify query frequency and top user patterns, and system.access.audit to track schema evolution and operational permission changes if feasible.
• Job & Pipeline Metadata - For lineage events originating from jobs or pipelines, pull execution history, run status, and failure patterns using the Databricks CLI or SDK, then materialize this metadata alongside your lineage tables.

Strategy

The metadata mart tables should represent a Current State Table Profile containing core identity attributes, aggregated lineage metrics (upstream/downstream table counts, target array lists, associated job/pipeline/notebook IDs, lineage depth), current quality health status, DQX pass rates and failed expectation lists, standard operational metadata (size, owner, 30-day query volume), and enriched metrics such as dynamic criticality scores, incident frequency and business domain tags.

You can maintain supplementary datasets as below if feasible

• Lineage Edges - Maps explicit source-to-target table pairs, relationship types, associated job or notebook IDs, and last observed timestamps to enable multi-hop path tracing.
• Quality Check History - Preserves time-series logs of DQX runs, freshness checks, and completeness outputs to report on quality trends over time.
• Incident Timeline - Tracks incident start and resolution times, root cause job IDs, and downstream impact scope to analyze chronic stability patterns.
• Job-to-Table Mapping - Maps job IDs directly to read/write table arrays, enriched with job run history, owners, and runbook links.

Maintain a Current State Table updated frequently via dynamic overwrites or merges to show immediate failures. Maintain a Historical Snapshots Table appended on a scheduled basis (daily) to track metrics drift, SLA compliance, and long-term quality trends.

[szymon_dybczak · Accepted solution]

Hi  @Niyojit ,

I think your proposed architecture is broadly the right direction, but I would separate lineage collection from lineage visualization.

Unity Catalog already exposes table-to-table relationships programmatically through system.access.table_lineage. It contains source_table_full_name and target_table_full_name, so it can effectively become the edge list for a custom lineage graph. 

For data quality, there are now several useful sources that can be joined to those lineage nodes:

• Data Quality Monitoring / anomaly detection writes table-level results to system.data_quality_monitoring.table_results.This is especially useful because it already contains a consolidated health status plus freshness and completeness results. Interestingly, it also has a downstream_impact structure with the number of downstream tables and an impact/severity level, so Databricks itself is starting to connect DQ issues with dependency information.
  
  
• Data profiling - the capability formerly called Lakehouse Monitoring - creates table_profile_metrics and  table_drift_metrics Delta tables. Those can provide things such as null counts, distributions, drift and custom metrics.
  
  
• DQX can persist centralized summary metrics to a Delta table. It captures input/error/warning/valid row counts and also exposes per-check results through check_metrics, so you can derive things such as "8/10 checks passed" or identify the specific failed checks.
  

So, in short - this is achievable. Build graph edges from system.access.table_lineage, then extract information from DQ Monitoring + profiling + DQX into a dedicated table that you can later join to graph edges.