Why source() function for raw tables in dbt? - Purpose & Use Cases

The Big Idea

What if you could fix broken data queries by changing just one place instead of many?

The Scenario

Imagine you have many raw data tables coming from different systems. You want to use them in your data models, but you have to write full table names everywhere, and keep track of where each table lives.

The Problem

Manually typing full table names is slow and easy to mess up. If a table moves or changes, you must update every place you used it. This causes errors and wastes time.

The Solution

The source() function lets you define raw tables once and then refer to them simply. It keeps your code clean and safe from changes in table locations.

Before vs After

✗ Before

select * from raw_database.raw_schema.customers

✓ After

select * from {{ source('raw_database', 'customers') }}

What It Enables

You can build reliable data models that easily adapt when raw tables move or change.

Real Life Example

A data analyst uses source() to refer to sales data tables. When the data warehouse reorganizes, only the source definition changes, not all the queries.

Key Takeaways

Manually writing full raw table names is error-prone and hard to maintain.

source() centralizes raw table references for cleaner code.

This makes your data models more reliable and easier to update.

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