source() function for raw tables in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of source() function call in dbt

Given the following dbt model code snippet, what will be the output SQL generated for the source table reference?

dbt

select * from {{ source('raw_data', 'users') }}

A select * from raw_data.users

B select * from {{ raw_data.users }}

C select * from source.raw_data.users

D select * from raw_data__users

💡 Hint

The source() function returns a fully qualified table name using the source schema and table name.

The source() function in dbt returns the fully qualified table name in the format schema.table, so {{ source('raw_data', 'users') }} becomes raw_data.users in SQL.

🧠 Conceptual

intermediate

Purpose of source() function in dbt

What is the main purpose of using the source() function in a dbt model?

A To define incremental models

B To create new tables in the database

C To run raw SQL queries without transformation

D To reference raw tables defined in the source.yml file for better lineage and documentation

💡 Hint

Think about how dbt tracks raw data sources and their metadata.

The source() function is used to reference raw tables defined in source.yml files. This helps dbt track data lineage and generate documentation for raw data sources.

🔧 Debug

advanced

Identify the error in source() usage

What error will occur when running this dbt model code? select * from {{ source('raw_data') }}

dbt

select * from {{ source('raw_data') }}

A Runtime error: table raw_data does not exist

B Compilation error: source() requires two arguments: source name and table name

C No error, runs successfully

D Syntax error in SQL due to missing quotes

💡 Hint

Check the number of arguments source() expects.

The source() function requires exactly two arguments: the source name and the table name. Omitting the table name causes a compilation error.

❓ data_output

advanced

Resulting SQL from nested source() usage

What is the resulting SQL query from this dbt model code? select user_id, count(*) from {{ source('raw_data', 'events') }} group by user_id

dbt

select user_id, count(*) from {{ source('raw_data', 'events') }} group by user_id

A select user_id, count(*) from raw_data.events group by user_id

B select user_id, count(*) from source.raw_data.events group by user_id

C select user_id, count(*) from raw_data__events group by user_id

D select user_id, count(*) from {{ raw_data.events }} group by user_id

💡 Hint

Remember how source() resolves to schema.table format.

The source() function returns the fully qualified table name in the format schema.table, so the SQL becomes select user_id, count(*) from raw_data.events group by user_id.

🚀 Application

expert

Using source() with multiple environments

In a dbt project with multiple environments (dev, prod), how does the source() function help manage raw table references across environments?

A It automatically copies raw tables from prod to dev

B It requires hardcoding schema names for each environment in the model code

C It dynamically resolves the schema based on the environment, allowing the same model code to work in dev and prod

D It disables source tracking in non-prod environments

💡 Hint

Think about how dbt profiles and source configurations work.

The source() function uses the schema defined in the source configuration, which can be set differently per environment in the dbt profiles. This allows the same model code to reference the correct raw tables in dev or prod without changes.