Databricks Unity Catalog Lineage Analysis Tutorial

This tutorial focuses on using Databricks Unity Catalog to analyze lineage and determine the downstream impact of a schema change. We’ll explore how to leverage Unity Catalog’s features for robust data governance and impact analysis within Databricks SQL.

Script 1: Setting Up the Environment and Initial Data

This script establishes a simple data environment within Unity Catalog and populates it with sample data. This forms the foundation for our lineage analysis.

-- Create a database within Unity Catalog
CREATE DATABASE IF NOT EXISTS test_db;

-- Create a schema within the database
CREATE SCHEMA IF NOT EXISTS test_schema;

-- Create a table within the schema
CREATE TABLE IF NOT EXISTS test_table (
    id INT,
    name STRING,
    value DOUBLE
);

-- Insert some sample data
INSERT INTO test_table (id, name, value) VALUES
(1, 'Alice', 10.5),
(2, 'Bob', 20.0),
(3, 'Charlie', 15.7);

-- Verify the data
SELECT  FROM test_table;

Explanation:

• We first create a database and schema within Unity Catalog. The `IF NOT EXISTS` clause prevents errors if the objects already exist.
• Next, we define a table named `test_table` with three columns: `id`, `name`, and `value`.
• Finally, we insert three rows of sample data into the table.

The final SELECT statement confirms that the data has been loaded correctly.

SELECT  FROM test_table;

Output:

<The result of the query 'SELECT  FROM test_table;' will be displayed here.  This will be three rows of data with id, name, and value columns. It's important to note that the exact result may vary slightly depending on the Databricks version, but it should contain the inserted data.>

Script 2: Introducing the Schema Change and Analyzing Lineage

This script simulates a schema change – renaming a column – and then uses Unity Catalog’s lineage feature to understand the impact on downstream queries.

-- Alter the table to rename the 'value' column to 'numeric_value'
ALTER TABLE test_db.test_schema.test_table RENAME COLUMN value TO numeric_value;

-- Analyze the lineage of the 'test_table'
SHOW LINEAGE FOR QUERY 'SELECT  FROM test_db.test_schema.test_table'
;

-- Verify that the lineage reflects the schema change
SELECT lineage FROM INFORMATION_SCHEMA.QUERY_LINEAGE
WHERE query_text = 'SELECT  FROM test_db.test_schema.test_table';

Explanation:

• We use the `ALTER TABLE` statement to rename the `value` column to `numeric_value` within the `test_table`. This is a controlled change that will directly affect downstream queries.
• The `SHOW LINEAGE FOR QUERY` command generates a report showing the dependencies and impact of the schema change.
• The `INFORMATION_SCHEMA.QUERY_LINEAGE` query provides a more detailed view of the lineage, confirming the change was tracked.

SHOW LINEAGE FOR QUERY 'SELECT  FROM test_db.test_schema.test_table'
;

Output:

<The output of 'SHOW LINEAGE FOR QUERY' will present a tree-like structure detailing how the 'SELECT  FROM test_db.test_schema.test_table' query depends on the 'test_table' table. The lineage should now include the 'numeric_value' column as a dependency.  The exact format may vary, but the key is that it shows the effect of the ALTER TABLE statement. >

SELECT lineage FROM INFORMATION_SCHEMA.QUERY_LINEAGE
WHERE query_text = 'SELECT  FROM test_db.test_schema.test_table';

Output:

<The output of the INFORMATION_SCHEMA.QUERY_LINEAGE query will visually represent the lineage, highlighting the 'numeric_value' column as the result of the ALTER TABLE operation. The specific formatting may vary. >

Script 3: Confirming the Impact with a Validation Query

This script demonstrates how to use the analyzed lineage to validate that downstream queries are behaving as expected after the schema change.

-- Validate the query after the schema change
SELECT id, name, numeric_value
FROM test_db.test_schema.test_table;

Explanation:

• This query simply retrieves all data from the table, now with the new column name (`numeric_value`).
• By running this query, you can verify that the data is still accessible and that the query is functioning correctly despite the schema change.

SELECT id, name, numeric_value
FROM test_db.test_schema.test_table;

Output:

<The result of the query 'SELECT id, name, numeric_value FROM test_db.test_schema.test_table;' will be three rows of data, with the 'value' column replaced by the 'numeric_value' column.  The output will show the original data with the new column name. >