Databricks Unity Catalog: Secure Publication of Governed Data Products with Lineage Tracking

This tutorial demonstrates the secure publication of governed data products with lineage tracking using Databricks Unity Catalog and Databricks SQL. We’ll focus solely on the publication and lineage aspects, utilizing a simplified scenario to illustrate the process.

Script 1: Initial Data Creation and Catalog Setup

First, we create a small dataset within a Unity Catalog schema and table. This is the foundation for our data product.

CREATE SCHEMA IF NOT EXISTS analytics.sales_data;
CREATE TABLE IF NOT EXISTS analytics.sales_data.orders (
    order_id STRING,
    customer_id STRING,
    product_id STRING,
    quantity INT,
    sale_date DATE
);
INSERT INTO analytics.sales_data.orders (order_id, customer_id, product_id, quantity, sale_date)
VALUES
('ORDER1001', 'CUST001', 'PROD001', 2, '2024-01-15'),
('ORDER1002', 'CUST002', 'PROD002', 1, '2024-01-20'),
('ORDER1003', 'CUST001', 'PROD003', 3, '2024-01-25');

-- Verify the data
SELECT  FROM analytics.sales_data.orders;

This script creates the schema `analytics.sales_data` and the `orders` table within it. We then insert three sample orders, and verify their existence. The fundamental step for governance is now complete—a table exists in a controlled environment.

Note that Unity Catalog manages access control at the database, schema, and table level. We will use this control throughout this tutorial.

The goal is to publish this `orders` table as a data product, ensuring lineage tracking, and setting appropriate security policies.

End Result:

Output:
-- Assuming the insert statement executed successfully
SELECT  FROM analytics.sales_data.orders;

Script 2: Secure Publication with Lineage Tracking

Now, we publish the `orders` table as a data product, leveraging Unity Catalog’s capabilities for secure publication and lineage tracking. We’ll explicitly define the metadata.

CREATE PUBLICATION IF NOT EXISTS analytics.sales_data_publication;
CREATE TABLE IF NOT EXISTS analytics.sales_data_publication.orders_product (
    order_id STRING,
    customer_id STRING,
    product_id STRING,
    quantity INT,
    sale_date DATE
)
AS SELECT  FROM analytics.sales_data.orders;

-- Verify the publication
SELECT  FROM analytics.sales_data_publication.orders_product;

-- Verify lineage (this will show the source table's lineage)
SHOW LINEAGE FOR TABLE analytics.sales_data_publication.orders_product;

This script creates a publication named `analytics.sales_data_publication`. The `CREATE TABLE … AS SELECT` statement replicates the data from the source table to a new table within the publication. Crucially, this process automatically captures lineage information, showing the dependency between the source and published tables. The `SHOW LINEAGE` statement provides a direct view of the lineage, allowing us to track the data’s origin. The publication acts as a layer of abstraction and governance control around the data product.

Unity Catalog ensures that only authorized users and services can access the published data product through the publication. Data access control is defined at the publication level, allowing for granular permissions management.

End Result:

Output:
-- Assuming the statements executed successfully
SELECT  FROM analytics.sales_data_publication.orders_product;
SHOW LINEAGE FOR TABLE analytics.sales_data_publication.orders_product;

Script 3: Data Product Update and Verification of Lineage

Let’s simulate an update to the source data and verify that the lineage is correctly reflected in the published data product.

-- Simulate an update to the source data
UPDATE analytics.sales_data.orders
SET quantity = 4
WHERE order_id = 'ORDER1002';

-- Verify the updated data
SELECT  FROM analytics.sales_data.orders;

-- Verify the lineage again - it should reflect the updated source.
SHOW LINEAGE FOR TABLE analytics.sales_data_publication.orders_product;

This script updates a single row in the source table. Because the published `orders_product` table is derived from the source table, the update propagates to the published data product. The `SHOW LINEAGE` statement confirms that the lineage reflects the update. Unity Catalog’s audit trail and lineage tracking are effectively maintained, providing a complete history of data changes.

This demonstrates that data products in Unity Catalog are immutable (at the published level), and changes are reflected through updates to the source table and propagation through the lineage.

End Result:

Output:
-- Assuming the statements executed successfully
SELECT  FROM analytics.sales_data.orders;
SHOW LINEAGE FOR TABLE analytics.sales_data_publication.orders_product;