Data lineage tracking in Snowflake - Commands & Configuration

Introduction

Data lineage tracking helps you see where your data comes from and how it changes. It solves the problem of understanding data flow and impact in your Snowflake environment.

When you want to find the source of a data error in your reports

When you need to understand how data moves between tables and views

When you want to check which processes update a specific dataset

When you need to comply with data governance rules by showing data origins

When you want to optimize your data pipelines by knowing dependencies

Commands

This command sets the current database context to MY_DATABASE so that subsequent commands run in this database.

Terminal

USE DATABASE MY_DATABASE;

Expected Output

Database changed.

This command queries the data lineage for the table MY_SCHEMA.MY_TABLE, showing where its data comes from and what depends on it.

Terminal

SELECT * FROM TABLE(INFORMATION_SCHEMA.LINEAGE_BY_OBJECT('MY_SCHEMA.MY_TABLE'));

Expected Output

OBJECT_NAME          | OBJECT_TYPE | DEPENDENCY_TYPE | DEPENDENT_OBJECT

MY_SCHEMA.MY_TABLE   | TABLE       | BASE            | 

MY_SCHEMA.SOURCE_VIEW| VIEW        | DEPENDENT       | MY_SCHEMA.MY_TABLE

This command shows the lineage of data involved in the given query, helping you understand all data sources and transformations used.

Terminal

SELECT * FROM TABLE(INFORMATION_SCHEMA.LINEAGE_BY_QUERY('SELECT * FROM MY_SCHEMA.MY_TABLE'));

Expected Output

QUERY_ID  | OBJECT_NAME          | OBJECT_TYPE | DEPENDENCY_TYPE

123456789 | MY_SCHEMA.MY_TABLE   | TABLE       | BASE

123456789 | MY_SCHEMA.SOURCE_VIEW| VIEW        | DEPENDENT

Key Concept

If you remember nothing else from this pattern, remember: Snowflake's INFORMATION_SCHEMA functions let you trace data origins and dependencies easily.

Common Mistakes

Running lineage queries without setting the correct database context.

The lineage functions will not find the objects if the database is not set, causing errors or empty results.

Always run USE DATABASE command before lineage queries to set the right context.

Using incorrect object names or schema names in lineage functions.

Misspelled or wrong names cause no results or errors because Snowflake cannot find the objects.

Double-check object and schema names before running lineage queries.

Summary

Set the database context with USE DATABASE before running lineage queries.

Use INFORMATION_SCHEMA.LINEAGE_BY_OBJECT to see data sources and dependencies for a table.

Use INFORMATION_SCHEMA.LINEAGE_BY_QUERY to understand data flow for a specific query.