Data lineage tracking in Snowflake - Step-by-Step Execution

Process Flow - Data lineage tracking

Start: Data Source

↓

Data Ingested into Snowflake

↓

Data Processing / Transformation

↓

Lineage Metadata Captured

↓

Lineage Stored in Metadata Tables

↓

Query Lineage Information

↓

Use Lineage for Impact Analysis / Auditing

Data lineage tracking follows data from its source through processing steps, capturing metadata at each stage to enable tracing and auditing.

Execution Sample

Snowflake

CREATE TABLE raw_sales AS SELECT * FROM external_sales;
CREATE TABLE processed_sales AS SELECT id, amount*1.1 AS amount FROM raw_sales;
-- Query lineage metadata
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.LINEAGE WHERE OBJECT_NAME = 'PROCESSED_SALES';

This code ingests raw data, processes it, and queries Snowflake's lineage metadata to track data flow.

Process Table

Step	Action	Object Affected	Lineage Captured	Result
1	Create raw_sales from external_sales	raw_sales	Source: external_sales	raw_sales table created with data from external_sales
2	Create processed_sales from raw_sales	processed_sales	Source: raw_sales	processed_sales table created with transformed data
3	Query lineage metadata for processed_sales	lineage metadata	Shows processed_sales depends on raw_sales which depends on external_sales	Lineage info retrieved
4	End	-	-	Execution complete

💡 All steps executed; lineage metadata shows data flow from external_sales to processed_sales

Status Tracker

Variable	Start	After Step 1	After Step 2	Final
raw_sales	undefined	Table with external_sales data	Table unchanged	Table unchanged
processed_sales	undefined	undefined	Table with transformed data from raw_sales	Table unchanged
lineage metadata	empty	empty	populated with lineage info for processed_sales	populated

Key Moments - 2 Insights

How does Snowflake know which tables data came from?

Snowflake automatically captures lineage metadata during table creation and transformation steps, as shown in steps 1 and 2 of the execution_table.

Why query the lineage metadata after creating tables?

Querying lineage metadata (step 3) confirms the data flow and dependencies, helping understand impact and audit data origins.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the source of processed_sales?

A raw_sales

B processed_sales

C external_sales

D lineage metadata

💡 Hint

Check the 'Lineage Captured' column at step 2 in execution_table

At which step is the lineage metadata populated with information about processed_sales?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'lineage metadata' variable in variable_tracker after each step

If we skip creating raw_sales and create processed_sales directly from external_sales, how would the lineage change?

A No lineage would be captured

B processed_sales lineage would show raw_sales as source

C processed_sales lineage would show external_sales as source

D Lineage would show processed_sales as source

💡 Hint

Lineage tracks direct data sources; see how lineage changes with table dependencies in execution_table

Concept Snapshot

Data lineage tracking in Snowflake:
- Tracks data flow from source to target tables
- Captured automatically during table creation and transformations
- Stored in ACCOUNT_USAGE.LINEAGE view
- Enables impact analysis and auditing
- Query lineage metadata to understand dependencies

Full Transcript

Data lineage tracking in Snowflake means following data from where it starts to where it ends up. When you create tables or transform data, Snowflake records which tables depend on which. For example, creating a raw_sales table from external_sales captures that raw_sales comes from external_sales. Then creating processed_sales from raw_sales records that dependency. You can query the lineage metadata to see these relationships. This helps you understand where your data came from and what uses it. The execution steps show creating tables and querying lineage. Variables track table states and lineage info. Key moments clarify how lineage is captured and why querying it matters. The quiz tests understanding of lineage sources and metadata timing.

Practice

1. What is the main purpose of data lineage tracking in Snowflake?

easy

A. To increase storage capacity automatically

B. To show where data comes from and how it moves

C. To encrypt data at rest

D. To create user accounts

Solution

1. Step 1: Understand data lineage concept

   Data lineage tracks the origin and flow of data through systems.

2. Step 2: Match with Snowflake purpose

   Snowflake uses lineage to show data source and movement, not storage or encryption.

3. Final Answer:

   To show where data comes from and how it moves -> Option B

4. Quick Check:

   Data lineage = data origin and flow [OK]

Hint: Data lineage means tracking data origin and flow [OK]

Common Mistakes:

• Confusing lineage with storage management
• Thinking lineage is about encryption
• Mixing lineage with user management

2. Which Snowflake command helps you explore data lineage by showing object dependencies?

easy

A. SHOW OBJECT DEPENDENCIES

B. SHOW USERS

C. DESCRIBE TABLE

D. CREATE WAREHOUSE

Solution

1. Step 1: Identify command for lineage

   SHOW OBJECT DEPENDENCIES lists how objects depend on each other, revealing lineage.

2. Step 2: Eliminate unrelated commands

   SHOW USERS lists users, DESCRIBE TABLE shows table structure, CREATE WAREHOUSE creates compute resources.

3. Final Answer:

   SHOW OBJECT DEPENDENCIES -> Option A

4. Quick Check:

   Lineage command = SHOW OBJECT DEPENDENCIES [OK]

Hint: Use SHOW OBJECT DEPENDENCIES to see lineage [OK]

Common Mistakes:

• Using SHOW USERS for lineage
• Confusing DESCRIBE TABLE with lineage
• Thinking CREATE WAREHOUSE shows dependencies

3. Given the following Snowflake query:

SHOW OBJECT DEPENDENCIES LIKE 'SALES_DATA';

What does this query return?

medium

A. List of users who accessed SALES_DATA

B. List of warehouses running SALES_DATA queries

C. List of tables with similar names to SALES_DATA

D. List of objects that depend on or are used by SALES_DATA

Solution

1. Step 1: Understand SHOW OBJECT DEPENDENCIES usage

   This command shows dependencies related to the specified object, here SALES_DATA.

2. Step 2: Interpret LIKE filter

   LIKE 'SALES_DATA' filters to objects named SALES_DATA, showing their dependencies.

3. Final Answer:

   List of objects that depend on or are used by SALES_DATA -> Option D

4. Quick Check:

   SHOW OBJECT DEPENDENCIES LIKE = dependency list [OK]

Hint: SHOW OBJECT DEPENDENCIES lists related objects [OK]

Common Mistakes:

• Thinking it shows user access logs
• Confusing with warehouse usage
• Assuming it lists similar table names

4. You run SHOW OBJECT DEPENDENCIES LIKE 'CUSTOMER_ORDERS'; but get no results. What is a likely reason?

medium

A. The object name is misspelled or does not exist

B. You need to create a warehouse first

C. SHOW OBJECT DEPENDENCIES does not work on tables

D. Your user lacks SELECT permission on CUSTOMER_ORDERS

Solution

1. Step 1: Check object existence

   No results usually mean the object name is wrong or missing in the database.

2. Step 2: Verify command applicability and permissions

   SHOW OBJECT DEPENDENCIES works on tables; warehouse creation is unrelated; lack of SELECT permission does not hide dependencies.

3. Final Answer:

   The object name is misspelled or does not exist -> Option A

4. Quick Check:

   No results = wrong or missing object name [OK]

Hint: Check spelling and existence if no dependencies show [OK]

Common Mistakes:

• Assuming warehouse needed for lineage
• Thinking permissions block SHOW OBJECT DEPENDENCIES
• Believing tables can't have dependencies

5. You want to track data lineage for a complex pipeline involving multiple views and tables in Snowflake. Which approach best ensures accurate lineage tracking?

hard

A. Only track lineage for base tables, ignoring views

B. Manually document dependencies in a spreadsheet

C. Use SHOW OBJECT DEPENDENCIES and automate lineage extraction with Snowflake metadata queries

D. Rely on user memory to recall data flow

Solution

1. Step 1: Understand lineage complexity

   Complex pipelines need automated, accurate tracking to avoid errors and omissions.

2. Step 2: Evaluate options for accuracy and scalability

   Manual docs and memory are error-prone; ignoring views misses dependencies; using SHOW OBJECT DEPENDENCIES with automation is best practice.

3. Final Answer:

   Use SHOW OBJECT DEPENDENCIES and automate lineage extraction with Snowflake metadata queries -> Option C

4. Quick Check:

   Automate lineage with SHOW OBJECT DEPENDENCIES for accuracy [OK]

Hint: Automate lineage extraction using SHOW OBJECT DEPENDENCIES [OK]

Common Mistakes:

• Relying on manual documentation only
• Ignoring views in lineage tracking
• Trusting memory instead of tools