source() function for raw tables in dbt - Mini Project: Build & Apply

Using the source() Function for Raw Tables in dbt

📖 Scenario: You are working on a data project where you need to use raw data tables stored in your data warehouse. These raw tables are managed outside of dbt, but you want to reference them safely in your dbt models.Using dbt's source() function helps you refer to these raw tables clearly and track their usage.

🎯 Goal: Build a simple dbt model that uses the source() function to select data from a raw table called raw_customers in the raw schema.You will create a source configuration, then write a model that selects all columns from this source table.

📋 What You'll Learn

Create a source configuration for the raw_customers table in the raw schema

Write a dbt model SQL file that uses the source() function to select all data from raw_customers

Print the resulting SQL query that dbt will run

💡 Why This Matters

🌍 Real World

In real data projects, raw data tables are often managed outside dbt. Using the <code>source()</code> function helps you reference these tables clearly and track their usage in your dbt models.

💼 Career

Data analysts and engineers use dbt to build reliable data pipelines. Knowing how to use <code>source()</code> is essential for managing dependencies on raw data sources.

1

Create the source configuration for the raw table

Create a source configuration in your sources.yml file with the source name raw and the table name raw_customers. Set the schema to raw and the table to raw_customers exactly.

dbt

# Add the source configuration for raw_customers in raw schema
# Your code here

Hint

Use the sources key at the top level, then define name, schema, and tables with the exact names.

2

Create a config variable for the source name

In your dbt model SQL file, create a variable called source_name and set it to the string 'raw'. This will help you reference the source easily.

dbt

version: 2
sources:
  - name: raw
    schema: raw
    tables:
      - name: raw_customers

{% set source_name = 'raw' %}  # Your code here

Hint

Use Jinja syntax to set a variable: {% set source_name = 'raw' %}

3

Use the source() function to select all data

Write a SQL query in your dbt model that selects all columns from the table using source(source_name, 'raw_customers'). Use select * from and the source() function exactly as shown.

dbt

{% set source_name = 'raw' %}

select * from {{ source(source_name, 'raw_customers') }}  -- Your code here

Hint

Use double curly braces to call the source() function inside your SQL query.

4

Print the resulting SQL query

Print the full SQL query string that your dbt model will run. Use print() and include the select * from {{ source(source_name, 'raw_customers') }} line as a string.

dbt

{% set source_name = 'raw' %}

select * from {{ source(source_name, 'raw_customers') }}

# Your code here to print the SQL query string

Hint

Use print() to show the SQL query as a string exactly.

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