source() function for raw tables in dbt - Step-by-Step Execution

Concept Flow - source() function for raw tables

Define source in schema.yml

↓

Call source() in model SQL

↓

dbt compiles SQL with source reference

↓

Query raw table in warehouse

↓

Return raw table data for model processing

The source() function links your dbt model to raw tables defined in your source configuration, allowing you to query raw data directly.

Execution Sample

dbt

select * from {{ source('raw_data', 'users') }}
where signup_date > '2023-01-01'

This code selects all users from the raw_data.users table who signed up after January 1, 2023.

Execution Table

Step	Action	Input	Output	Notes
1	Read source() call	source('raw_data', 'users')	Reference to raw_data.users table	dbt identifies source config
2	Compile SQL	select * from {{ source(...) }} where signup_date > '2023-01-01'	select * from raw_data.users where signup_date > '2023-01-01'	source() replaced with raw table name
3	Run query in warehouse	select * from raw_data.users where signup_date > '2023-01-01'	Rows of users with signup_date > 2023-01-01	Raw data fetched for model
4	Return data to model	Raw rows	DataFrame or table for further processing	Model can now transform raw data
5	End	No more steps	Execution complete	Query finished successfully

💡 Query runs until all matching rows from raw_data.users are returned

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
source_ref	undefined	raw_data.users	raw_data.users	raw_data.users	raw_data.users
compiled_sql	undefined	undefined	select * from raw_data.users where signup_date > '2023-01-01'	select * from raw_data.users where signup_date > '2023-01-01'	completed
query_result	undefined	undefined	undefined	rows matching condition	rows matching condition

Key Moments - 3 Insights

Why does source() need the source name and table name?

Because source() uses these to find the exact raw table defined in your schema.yml, as shown in execution_table step 1 where source('raw_data', 'users') resolves to raw_data.users.

What happens if you forget to define the source in schema.yml?

dbt will fail to compile the SQL because source() cannot resolve the raw table name, stopping at execution_table step 2.

Does source() run the query or just reference the table?

source() only creates a reference to the raw table; the actual query runs later in the warehouse as shown in execution_table step 3.

Visual Quiz - 3 Questions

Look at the execution_table, what does source('raw_data', 'users') return at Step 1?

A A reference to the raw_data.users table

B The actual data rows from users

C An error message

D Compiled SQL query

💡 Hint

Check the Output column in execution_table Step 1

At which step does dbt replace source() with the actual table name?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the Action and Output columns in execution_table Step 2

If the source is not defined in schema.yml, what will happen during execution?

A dbt will compile SQL successfully

B The query will run but return no data

C dbt will fail to compile SQL

D The query will run on a default table

💡 Hint

Refer to key_moments about missing source definition

Concept Snapshot

source() function in dbt:
- Use source('source_name', 'table_name') to reference raw tables
- source() links to tables defined in schema.yml
- dbt replaces source() with actual table name during compilation
- Enables querying raw data directly in models
- Prevents hardcoding raw table names
- Essential for data lineage and documentation

Full Transcript

The source() function in dbt is used to reference raw tables defined in your source configuration file, usually schema.yml. When you write source('raw_data', 'users') in your model SQL, dbt looks up the raw_data source and users table name. During compilation, dbt replaces the source() call with the actual raw table name like raw_data.users. Then the compiled SQL runs in your data warehouse, fetching raw data rows. This process allows you to write models that depend on raw tables without hardcoding table names, improving maintainability and documentation. If the source is not defined, dbt will fail to compile the SQL. The execution steps show how source() is resolved, compiled, and executed step-by-step.

Practice

1. What is the main purpose of the source() function in dbt?

easy

A. To create new tables in the database

B. To run Python scripts inside dbt models

C. To delete raw tables from the database

D. To reference raw tables defined in the sources.yml file

Solution

1. Step 1: Understand the role of source()

   The source() function is used to safely reference raw tables that are defined in the sources.yml file.

2. Step 2: Differentiate from other dbt functions

   It does not create or delete tables, nor run scripts. It only connects models to existing raw data tables.

3. Final Answer:

   To reference raw tables defined in the sources.yml file -> Option D

4. Quick Check:

   source() connects to raw tables [OK]

Hint: Remember: source() links to raw tables only [OK]

Common Mistakes:

• Thinking source() creates tables
• Confusing source() with model creation
• Assuming source() runs scripts

2. Which of the following is the correct syntax to reference a raw table named customers in the source named raw_data using source() in a dbt model?

easy

A. select * from source.raw_data.customers

B. select * from {{ source('raw_data', 'customers') }}

C. select * from source['raw_data']['customers']

D. select * from source(raw_data, customers)

Solution

1. Step 1: Recall source() function syntax

   The correct syntax uses two string arguments: the source name and the table name, both in quotes, wrapped in {{ }}.

2. Step 2: Check each option

   The valid syntax is select * from {{ source('raw_data', 'customers') }}. Dot notation, unquoted arguments, and bracket notation are all invalid in dbt.

3. Final Answer:

   select * from {{ source('raw_data', 'customers') }} -> Option B

4. Quick Check:

   {{ source() }} with quoted arguments [OK]

Hint: Always use quotes around source and table names [OK]

Common Mistakes:

• Omitting quotes around source or table names
• Using dot or bracket notation instead of function call
• Passing variables without quotes

3. Given the following dbt model SQL code:

select id, name from {{ source('sales_db', 'customers') }} where active = true

What does this query do?

medium

A. Selects all customers from the customers table in the sales_db source where active is true

B. Creates a new table named customers in sales_db

C. Deletes inactive customers from the customers table

D. Selects all customers from a model named sales_db

Solution

1. Step 1: Understand the source() usage

   The code references the raw table customers inside the source sales_db.

2. Step 2: Analyze the SQL query

   The query selects id and name columns where active is true, filtering active customers.

3. Final Answer:

   Selects all customers from the customers table in the sales_db source where active is true -> Option A

4. Quick Check:

   source() reads raw tables, query filters active customers [OK]

Hint: Look for source() to identify raw table references [OK]

Common Mistakes:

• Thinking source() creates or deletes tables
• Confusing source with models
• Ignoring the WHERE clause filtering

4. You wrote this dbt model code:

select * from source('marketing', 'leads')

but dbt throws an error: Compilation Error: 'source' is undefined. What is the most likely cause?

medium

A. You used single quotes instead of double quotes inside source()

B. The table leads does not exist in the database

C. You forgot to wrap source() in double curly braces {{ }}

D. The marketing source is not defined in sources.yml

Solution

1. Step 1: Check dbt Jinja syntax

   dbt requires Jinja functions like source() to be inside double curly braces: {{ source(...) }}.

2. Step 2: Understand the error message

   The error says source is undefined, meaning dbt treats it as plain SQL, not a Jinja function.

3. Final Answer:

   You forgot to wrap source() in double curly braces {{ }} -> Option C

4. Quick Check:

   Use {{ source() }} to call source function [OK]

Hint: Always use {{ }} around dbt functions like source() [OK]

Common Mistakes:

• Writing source() without {{ }}
• Assuming quotes type causes error
• Ignoring missing source definition errors

5. You want to create a dbt model that selects only customers from the raw_data source's customers table who joined after 2023-01-01. Which of the following is the correct way to write this using source()?

hard

A. select * from {{ source('raw_data', 'customers') }} where join_date > '2023-01-01'

B. select * from source('raw_data', 'customers') where join_date > '2023-01-01'

C. select * from {{ source('raw_data', customers) }} where join_date > '2023-01-01'

D. select * from {{ source('raw_data', 'customers') }} where join_date > 2023-01-01

Solution

1. Step 1: Use correct source() syntax with Jinja braces

   The source() function must be inside {{ }} and both source and table names must be strings in quotes.

2. Step 2: Use correct date format in SQL condition

   The date value must be a string in quotes to compare properly in SQL.

3. Final Answer:

   select * from {{ source('raw_data', 'customers') }} where join_date > '2023-01-01' -> Option A

4. Quick Check:

   Correct syntax and date string format [OK]

Hint: Wrap source() in {{ }}, use quotes for strings and dates [OK]

Common Mistakes:

• Missing {{ }} around source()
• Not quoting table name or date string
• Using unquoted date causing SQL error