The `source()` function and why it matters

Description

source() is a Jinja function — the templating engine dbt uses inside .sql files — that takes exactly two text arguments: the source group's name, and the table's name inside that group. Inside any model, it looks like this:

select * from {{ source('kiosko_raw', 'orders') }}

That's it. There's no third way to write it, no optional arguments for this lesson. But behind those two arguments there's a guarantee that isn't obvious until you see it fail: dbt validates that kiosko_raw.orders exists, declared in some sources.yml, before running a single line of SQL against the warehouse — not when you run the model, but at the moment dbt compiles your entire project, a step that happens before any SELECT touches the database.

This lesson demonstrates that guarantee with three real models, deliberately run to fail in three different ways — and compares that against the obvious alternative: writing the file's path directly in the FROM, without going through source() at all.

Connection to the module. Lesson 2 declared the four sources — the receiving register is written — but you never used the source() function inside any .sql file. This lesson closes that gap: it explains exactly what the function does, and why it's worth using even in a project as small as Kiosko's, with only four source tables.

An analogy: the registered supplier number, versus memorizing the truck's address

Go back to lesson 2's warehouse. When someone in the warehouse needs more raw material from a known supplier, they don't get in a car to go look for "the address where I remember the supplier is" — they request the material by its registered supplier number in the warehouse's system. If they type a number that doesn't exist in the register, the system tells them immediately, right there, before anyone even starts an engine: "there's no supplier with that number." The error gets caught at the desk, in seconds, not on the highway, hours later.

Now imagine that same worker instead deciding to memorize the supplier's exact address and write it from memory on every new order, without using the registered number. If a digit in the address is wrong, nobody knows until the truck arrives somewhere that doesn't exist — and by then the whole trip is already wasted. source() is the registered supplier number: a reference validated against a central register, that fails fast and in the right place. Writing a file's path directly in a FROM, without source(), is memorizing the address — it works as long as you don't make a mistake, and when you do, you find out late.

Worked example: three models, three ways to fail

You're going to write three deliberately broken versions of the same trivial model, to see with your own eyes — not from memory — what source() catches and at exactly what moment. None of these three files are part of the final project; delete them as soon as you finish the exercise.

Model 1 — path written by hand, no source(), with a typo

-- models/staging/kiosko/stg_orders_hardcoded.sql (temporary, for this exercise)
select *
from 'raw_data/kiosko/order_*.csv'

Notice the deliberate mistake: order_*.csv instead of orders_*.csv — missing the "s". Run:

dbt run --select stg_orders_hardcoded

What to expect.

1 of 1 START sql view model main.stg_orders_hardcoded .......................... [RUN]
1 of 1 ERROR creating sql view model main.stg_orders_hardcoded ................. [ERROR in 0.03s]

Finished running 1 view model in 0 hours 0 minutes and 0.11 seconds (0.11s).

Completed with 1 error, 0 partial successes, and 0 warnings:

[ERROR]: in model stg_orders_hardcoded (models/staging/kiosko/stg_orders_hardcoded.sql)
  Runtime Error in model stg_orders_hardcoded (models/staging/kiosko/stg_orders_hardcoded.sql)
  IO Error: No files found that match the pattern "raw_data/kiosko/order_*.csv"

  compiled code at target/compiled/kiosko_analytics/models/staging/kiosko/stg_orders_hardcoded.sql

Done. PASS=0 WARN=0 ERROR=1 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

Notice 1 of 1 START ... [RUN]: dbt already started running this model against the warehouse before discovering the problem. In a project with a single model, the difference is cosmetic. In a project with fifty models, this error only shows up when it's this specific model's turn in the run — which could be the first one, or model number forty-seven, depending on the order dbt decides to run things in.

Model 2 — source() pointing at a name that doesn't exist in sources.yml

-- models/staging/kiosko/stg_orders_typo.sql (temporary, for this exercise)
select *
from {{ source('kiosko_raw', 'ordrs') }}

Another typo, this time in the table name inside source(): ordrs instead of orders. Run the same command:

dbt run --select stg_orders_typo

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
[ERROR]: Encountered an error:
Compilation Error
  Model 'model.kiosko_analytics.stg_orders_typo' (models/staging/kiosko/stg_orders_typo.sql) depends on a source named 'kiosko_raw.ordrs' which was not found

Compare this against Model 1, line by line. There's no 1 of 1 START, no Concurrency: 4 threads, absolutely no attempt to touch the warehouse. The error literally says Compilation Error — dbt stopped during the compilation step, before running anything, and tells you exactly which model and which source are involved. This is source()'s central guarantee: dbt knows, ahead of time, the complete graph of which model depends on which source, and validates that entire graph before running a single query.

Model 3 — source() written correctly, but with no external_location yet

-- models/staging/kiosko/stg_orders_no_location.sql (temporary, for this exercise)
select *
from {{ source('kiosko_raw', 'orders') }}

This time there's no typo — kiosko_raw and orders are exactly the names you declared in lesson 2. Run:

dbt run --select stg_orders_no_location

What to expect.

1 of 1 ERROR creating sql view model main.stg_orders_no_location ............... [ERROR in 0.03s]

Finished running 1 view model in 0 hours 0 minutes and 0.08 seconds (0.08s).

Completed with 1 error, 0 partial successes, and 0 warnings:

[ERROR]: in model stg_orders_no_location (models/staging/kiosko/stg_orders_no_location.sql)
  Runtime Error in model stg_orders_no_location (models/staging/kiosko/stg_orders_no_location.sql)
  Catalog Error: Table with name "kiosko_raw.orders" does not exist because schema "kiosko_raw" does not exist.

  LINE 6: from "kiosko"."kiosko_raw"."orders"
               ^

  compiled code at target/compiled/kiosko_analytics/models/staging/kiosko/stg_orders_no_location.sql

Done. PASS=0 WARN=0 ERROR=1 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

This third error is different from the other two, and it's worth looking at carefully. Inspect the compiled SQL:

cat target/compiled/kiosko_analytics/models/staging/kiosko/stg_orders_no_location.sql

What to expect.

select *
from "kiosko"."kiosko_raw"."orders"

Without meta.external_location declared, dbt-duckdb does the only reasonable thing it can do with a source(): treat it as a reference to a real table inside DuckDB's catalog, in a schema named the same as the source group (kiosko_raw). Since you never created any table or schema with that name — the raw files are loose files in raw_data/, not warehouse tables — DuckDB responds with the literal truth: that schema doesn't exist. source() passed its own validation — the name is declared correctly, Model 2's Compilation Error doesn't show up — but it's still missing the piece that connects that name with the real physical file. That piece is, exactly, lesson 4's topic.

Delete the three temporary files before continuing:

rm models/staging/kiosko/stg_orders_hardcoded.sql
rm models/staging/kiosko/stg_orders_typo.sql
rm models/staging/kiosko/stg_orders_no_location.sql

Going deeper: when each error gets caught, and why it matters

The row that matters most is Model 2's, because it isn't exclusive to this model: dbt compile (or any dbt command, since all of them compile first) checks the entire project's dependency graph before running anything. If you had fifty models and one of them had a misspelled source(), that error would show up immediately, no matter how many other models are fine — you're never going to discover a broken source() "halfway through a two-hour-long run," because dbt doesn't even start that run if the graph has a name that doesn't resolve.

There's a second advantage to source(), less visible in a four-table project but real in any larger one: a single place to update the physical location. If the team generating Kiosko's files decided tomorrow to move orders_*.csv to a different folder, with the path written by hand you'd have to find and fix that path in every model that used it. With source(), you fix meta.external_location once, in _sources.yml — every model using source('kiosko_raw', 'orders') inherits the change automatically on the next run, without touching a single line of its own .sql file.

A third advantage, outside this guide's scope but worth naming: since source() gives dbt explicit metadata about your raw data, dbt can also check how fresh a source is with the dbt source freshness command — comparing the file's date against a threshold you define — something completely impossible to automate over a path written by hand in a FROM. This guide doesn't implement it, but it's the reason real data teams treat declaring sources as non-negotiable, even for a single file.

Common mistakes

Using source() in some models and hand-written paths in others, "in a hurry." What happens: someone, mid-crunch against the clock, writes a new model with the file's path straight in the FROM, promising themselves "I'll switch it to source() later." Why it happens: in the moment, both forms produce the same result if there's no typo — the difference only shows up when something breaks. How to spot it: check any .sql file inside models/staging/ looking for single quotes followed by a path ('raw_data/...') instead of the source( function; any match is a sign of this inconsistency. How to fix it: this guide's rule, with no exceptions, is that no staging model references a raw file directly — it always goes through source(), even if that means declaring a new entry in _sources.yml before you can write the model.

Confusing a Compilation Error with an error in your SQL logic. What happens: someone sees Compilation Error in the terminal and starts checking their SELECT for a SQL syntax problem, when the real problem is in the name passed to source(). Why it happens: the word "Compilation" sounds generic, and it isn't always clear at first glance that dbt compiles Jinja, not just SQL — source('kiosko_raw', 'ordrs') is a Jinja function call dbt tries to resolve before generating the final SQL. How to spot it: read the full message — depends on a source named 'kiosko_raw.ordrs' which was not found tells you, unambiguously, that the problem is the source's name, not any SQL clause. How to fix it: when you see Compilation Error with the phrase "depends on a source," go straight to _sources.yml and compare the exact name (case, underscores) against what you wrote inside source(...) in the model.

Thinking fixing Model 3's source() already solves the error. What happens: someone fixes the spelling of source('kiosko_raw', 'orders') (it was already spelled correctly) expecting the Catalog Error to disappear. Why it happens: it's easy to assume every source()-related error gets fixed inside the function itself. How to spot it: if the group and table names in your source() exactly match _sources.yml, and the error is still Catalog Error: ... schema ... does not exist, the problem isn't in the model — it's that _sources.yml still has no meta.external_location. How to fix it: lesson 4 adds exactly that piece; there's nothing else to adjust in the model's .sql file.

Exercises

Exercise 1 — Predict the error type before running it. Without running anything yet, for each of these three models, predict whether the error (if any) would be a Compilation Error (caught before touching the warehouse) or a runtime error (caught while running):

-- (a)
select * from {{ source('kiosko_raw', 'stores') }}

-- (b)
select * from {{ source('kiosko_stock', 'stores') }}

-- (c)
select * from 'raw_data/kiosko/stores.csv'

Exercise 2 — Trigger the compilation error yourself, with a different variation. Instead of a misspelled table name, write a temporary model using a misspelled source group name (kiosko_rawx instead of kiosko_raw, with orders correct). Run dbt run on that model and compare the exact message against Model 2's from this lesson.

select * from {{ source('kiosko_rawx', 'orders') }}

Exercise 3 — Argue the scaling advantage in your own words. Imagine a real dbt project with 80 models, where 12 of them use source('sales_system', 'transactions'). In 2-3 sentences, explain what distinguishes the experience of fixing a location change for that raw file if all 12 models use source(), versus if all 12 models have the path written by hand.

Summary and next step

In this lesson you saw, run three times, exactly what source() guarantees and what it doesn't: it validates — at compile time, before touching the warehouse — that the name you pass it exists, declared in sources.yml, and tells you so with a message pointing straight at the model and source involved. What it does not do, yet, is tell dbt-duckdb where the real physical file is — a perfectly well-written source(), with no external_location, still fails, though with a different error (Catalog Error, at runtime) than a misspelled name (Compilation Error, at compile time).

Before moving on you should be able to: explain the difference between a Compilation Error and a runtime error, in your own words; and predict, for any source() you see, whether a typo in it would be caught before or after dbt starts running models.

Lesson 4 closes the last piece: meta.external_location, the key that finally connects source('kiosko_raw', 'orders') with the real file in raw_data/kiosko/.

Resources

• dbt Developer Hub — "The source() function," the official reference for this function's syntax and behavior. docs.getdbt.com/reference/dbt-jinja-functions/source. In English.
• dbt Developer Hub — "About sources," which also documents dbt source freshness, the feature mentioned in this lesson's "going deeper" section. docs.getdbt.com/docs/build/sources. In English.
• dbt-duckdb — official GitHub repository, where create_from_source in the adapter's code implements exactly the behavior you saw in Model 3 (a source() with no external_location resolves as a regular catalog table). github.com/duckdb/dbt-duckdb. In English.