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

Need a 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

Need a 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

Need a 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

Need a hint?

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