Data lineage tracking in Snowflake - Mini Project: Build & Apply

Data Lineage Tracking in Snowflake

📖 Scenario: You work as a data engineer in a company that uses Snowflake for data warehousing. Your team wants to track the flow of data between tables to understand where data comes from and where it goes. This helps in debugging and auditing data pipelines.

🎯 Goal: Build a simple data lineage tracking setup in Snowflake by creating tables, adding a configuration variable, writing a query to capture lineage, and finally creating a view to show the lineage.

📋 What You'll Learn

Create a source table with sample data

Create a target table to receive data

Add a configuration variable to hold the current timestamp

Write a query to insert data from source to target while capturing lineage

Create a view that shows the lineage information

💡 Why This Matters

🌍 Real World

Tracking data lineage helps companies understand data flow, troubleshoot issues, and comply with regulations.

💼 Career

Data engineers and analysts use lineage tracking to maintain data quality and transparency in data pipelines.

1

Create source and target tables

Create a table called source_data with columns id INT and value STRING. Insert three rows with ids 1, 2, 3 and values 'A', 'B', 'C'. Then create an empty table called target_data with the same columns.

Snowflake

-- Create source_data table and insert rows
-- Create target_data table

Need a hint?

Use CREATE OR REPLACE TABLE to create tables and INSERT INTO to add rows.

2

Add a configuration variable for timestamp

Create a variable called current_time and set it to the current timestamp using CURRENT_TIMESTAMP().

Snowflake

CREATE OR REPLACE TABLE source_data (id INT, value STRING);
INSERT INTO source_data (id, value) VALUES (1, 'A'), (2, 'B'), (3, 'C');
CREATE OR REPLACE TABLE target_data (id INT, value STRING);
-- Create variable current_time with CURRENT_TIMESTAMP()

Need a hint?

Use SET variable_name = value; to create a variable in Snowflake.

3

Insert data with lineage tracking

Insert data from source_data into target_data. Add a new column load_time to target_data to store the value of current_time for lineage tracking. Alter target_data to add load_time TIMESTAMP before inserting.

Snowflake

CREATE OR REPLACE TABLE source_data (id INT, value STRING);
INSERT INTO source_data (id, value) VALUES (1, 'A'), (2, 'B'), (3, 'C');
CREATE OR REPLACE TABLE target_data (id INT, value STRING);
SET current_time = CURRENT_TIMESTAMP();
-- Alter target_data to add load_time column
-- Insert data from source_data into target_data with load_time

Need a hint?

Use ALTER TABLE to add a column. Use INSERT INTO ... SELECT ... to copy data with the timestamp.

4

Create a view to show data lineage

Create a view called data_lineage_view that selects id, value, and load_time from target_data to show the lineage information.

Snowflake

CREATE OR REPLACE TABLE source_data (id INT, value STRING);
INSERT INTO source_data (id, value) VALUES (1, 'A'), (2, 'B'), (3, 'C');
CREATE OR REPLACE TABLE target_data (id INT, value STRING);
SET current_time = CURRENT_TIMESTAMP();
ALTER TABLE target_data ADD COLUMN load_time TIMESTAMP;
INSERT INTO target_data (id, value, load_time) SELECT id, value, $current_time FROM source_data;
-- Create view data_lineage_view selecting id, value, load_time from target_data

Need a hint?

Use CREATE OR REPLACE VIEW view_name AS SELECT ... to create the view.