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.