Data lineage tracking in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to enable data lineage tracking for a Snowflake table.

Snowflake

ALTER TABLE sales_data SET [1] = TRUE;

Drag options to blanks, or click blank then click option'

ALINEAGE_ENABLED

BDATA_RETENTION_TIME_IN_DAYS

CCHANGE_TRACKING

DTIME_TRAVEL

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing lineage tracking with time travel or change tracking features.

Setting CHANGE_TRACKING to TRUE activates data lineage tracking on the table.

2fill in blank

medium

Complete the code to query the lineage information for a specific table in Snowflake.

Snowflake

SELECT * FROM [1] WHERE DEPENDED_ON_OBJECT_NAME = 'sales_data';

Drag options to blanks, or click blank then click option'

AACCOUNT_USAGE.OBJECT_DEPENDENCIES

BACCOUNT_USAGE.QUERY_HISTORY

CINFORMATION_SCHEMA.COLUMNS

DINFORMATION_SCHEMA.TABLES

Attempts:

3 left

💡 Hint

Common Mistakes

Using TABLES or COLUMNS views which do not show lineage.

Using QUERY_HISTORY which shows query logs, not lineage.

The ACCOUNT_USAGE.OBJECT_DEPENDENCIES view shows lineage and dependencies between objects.

3fill in blank

hard

Fix the error in the code to enable lineage tracking on a table named 'orders'.

Snowflake

ALTER TABLE orders SET [1] = TRUE;

Drag options to blanks, or click blank then click option'

ACHANGE_TRACKING

BDATA_RETENTION_TIME_IN_DAYS

CLINEAGE_ENABLED

DTIME_TRAVEL

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around TRUE which causes syntax errors.

Using wrong property names.

The property CHANGE_TRACKING must be set to the boolean value TRUE without quotes.

4fill in blank

hard

Fill both blanks to create a query that shows the source and dependent objects for 'customer_data'.

Snowflake

SELECT [1], [2] FROM ACCOUNT_USAGE.OBJECT_DEPENDENCIES WHERE DEPENDED_ON_OBJECT_NAME = 'customer_data';

Drag options to blanks, or click blank then click option'

ADEPENDED_ON_OBJECT_NAME

BOBJECT_NAME

CDEPENDING_OBJECT_NAME

DOBJECT_SCHEMA

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up DEPENDED_ON_OBJECT_NAME and DEPENDING_OBJECT_NAME.

Selecting schema names instead of object names.

The DEPENDED_ON_OBJECT_NAME is the source object, and DEPENDING_OBJECT_NAME is the dependent object.

5fill in blank

hard

Fill all three blanks to create a query that lists all tables with lineage tracking enabled in a specific schema.

Snowflake

SELECT TABLE_NAME AS [1], TABLE_SCHEMA AS [2] FROM INFORMATION_SCHEMA.TABLES WHERE [3] = 'YES' AND TABLE_SCHEMA = 'SALES';

Drag options to blanks, or click blank then click option'

Alineage_status

Btable_name

Ctable_schema

DCHANGE_TRACKING

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect alias names.

Using a non-existent column for filtering lineage.

Use table_name, table_schema as aliases, and filter by CHANGE_TRACKING = 'YES'.

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