Data lineage tracking in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Data Lineage Purpose

What is the primary purpose of data lineage tracking in a Snowflake data warehouse?

A To automatically scale compute resources based on query load.

B To encrypt data at rest and in transit within Snowflake.

C To monitor and visualize the flow of data from source to destination for auditing and troubleshooting.

D To create backups of data at regular intervals.

💡 Hint

Think about why knowing where data comes from and where it goes is important.

Data lineage helps track the origin and movement of data, which is essential for auditing, debugging, and compliance.

❓ Architecture

intermediate

Snowflake Data Lineage Components

Which Snowflake feature is essential to capture detailed data lineage information automatically?

A Snowflake Access History

B Snowflake Query History

C Snowflake Tasks

D Snowflake Streams

💡 Hint

Consider which feature records executed queries and their details.

Snowflake Query History records all queries executed, which can be analyzed to understand data flow and lineage.

❓ Configuration

advanced

Query to Extract Table Lineage

Given Snowflake's QUERY_HISTORY view, which SQL query correctly extracts the source tables used in queries for lineage tracking?

Snowflake

SELECT query_id, array_agg(DISTINCT referenced_object_name) AS source_tables FROM snowflake.account_usage.query_history WHERE start_time > CURRENT_TIMESTAMP() - INTERVAL '1 DAY' GROUP BY query_id;

A SELECT query_id, array_agg(DISTINCT referenced_object_name) AS source_tables FROM snowflake.account_usage.query_history WHERE start_time > CURRENT_TIMESTAMP() - INTERVAL '1 DAY' GROUP BY query_id;

B SELECT query_id, array_agg(DISTINCT table_name) AS source_tables FROM snowflake.account_usage.query_history WHERE start_time > CURRENT_TIMESTAMP() - INTERVAL '1 DAY' GROUP BY query_id;

C SELECT query_id, array_agg(DISTINCT referenced_table) AS source_tables FROM snowflake.account_usage.query_history WHERE start_time > CURRENT_TIMESTAMP() - INTERVAL '1 DAY' GROUP BY query_id;

D SELECT query_id, array_agg(DISTINCT source_table) AS source_tables FROM snowflake.account_usage.query_history WHERE start_time > CURRENT_TIMESTAMP() - INTERVAL '1 DAY' GROUP BY query_id;

💡 Hint

Check the exact column names in the QUERY_HISTORY view for referenced tables.

The column referenced_object_name contains the names of objects (tables) referenced by queries. Other column names do not exist in QUERY_HISTORY.

❓ security

advanced

Securing Data Lineage Metadata

Which Snowflake feature should you use to restrict access to sensitive data lineage metadata to only authorized users?

A Row Access Policies

B Network Policies

C Resource Monitors

D Virtual Warehouses

💡 Hint

Think about controlling access to specific rows or data within tables.

Row Access Policies allow fine-grained control over which rows users can see, useful for protecting sensitive lineage metadata.

❓ service_behavior

expert

Impact of Query History Retention on Lineage

Snowflake retains query history for 365 days by default. What is the impact on data lineage tracking if this retention period is reduced to 7 days?

A Lineage tracking will stop completely because query history is mandatory for lineage.

B Lineage tracking will continue unaffected because lineage data is stored separately.

C Lineage tracking will improve because less data means faster processing.

D Lineage tracking will only be possible for queries executed within the last 7 days, limiting historical analysis.

💡 Hint

Consider how query history retention affects available lineage data.

Reducing query history retention limits the time window for which lineage can be reconstructed, restricting historical lineage analysis.

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