Reading files directly with `external_location`

Description

meta.external_location is the configuration key — specific to dbt-duckdb, not part of dbt's general standard — that finally connects a source() to a real file on disk. It's declared inside the meta: section of each table, in _sources.yml, and accepts two different forms of value:

• A path in quotes, like "raw_data/kiosko/stores.csv" — DuckDB automatically detects how to read it based on its extension (.csv, .json, .jsonl, .parquet), a mechanism DuckDB calls a replacement scan.
• An explicit call to a DuckDB function, like "read_csv('file.csv', types={'field': 'DATE'})" — needed when you want to control something the automatic detection doesn't guess well on its own: explicit column types, column names different from the file's, compression, or a JSON's exact format.

This lesson adds that key to Kiosko's four sources — finally closing the gap lesson 3 left open — and uses dbt compile --inline and dbt show --inline to demonstrate, with real SQL and real rows, exactly what dbt-duckdb builds from each of the two forms.

Connection to the module. Lesson 3 ended with Model 3 failing with Catalog Error: ... schema "kiosko_raw" does not exist — a perfect source(), with no physical place to point to. This lesson fixes exactly that: by the end, Kiosko's four sources are going to return real rows, read straight from raw_data/kiosko/, with no staging .sql file existing yet.

An analogy: the dock's exact address, not just the supplier's name

Lesson 3 compared source() to requesting material by the registered supplier number, instead of memorizing an address. But a registered supplier number, on its own, doesn't move any box either — the warehouse's system also needs that supplier's exact address: the street, the number, the specific dock where the truck has to deliver. Without that address, the supplier number is a valid name with no place to send the order to — which is, precisely, the state source('kiosko_raw', 'orders') was left in at the end of lesson 3.

meta.external_location is that exact address. And, like any real address, it can be written two ways: the simple address ("raw_data/kiosko/stores.csv", so clear the delivery driver understands it with no extra instructions) and the address with special instructions ("read_csv('file.csv', types={...})", for when the driver needs to know something the address alone doesn't tell them — an access code, a specific time window, an exact floor inside a big building).

Worked example: completing _sources.yml

Update models/staging/kiosko/_sources.yml, adding meta.external_location to each of the four tables:

# models/staging/kiosko/_sources.yml
version: 2

sources:
  - name: kiosko_raw
    description: "Kiosko's raw files, read directly from disk with dbt-duckdb."
    tables:
      - name: orders
        description: "One row per point-of-sale sale, one CSV file per day of the week from 2026-08-03 to 2026-08-09."
        meta:
          external_location: "raw_data/kiosko/orders_*.csv"
      - name: events
        description: "The delivery app's clickstream (page_view, add_to_cart, purchase), one JSON Lines file per day of the same week."
        meta:
          external_location: "read_ndjson_auto('raw_data/kiosko/events_*.jsonl')"
      - name: stores
        description: "Catalog of Kiosko's three stores."
        meta:
          external_location: "raw_data/kiosko/stores.csv"
      - name: products
        description: "Product catalog, version 1 (before the price and category change module 5 introduces)."
        meta:
          external_location: "raw_data/kiosko/products_v1.csv"

Three of the four paths are simple: orders, stores, and products use a path in quotes, no function. events uses the explicit-function form, read_ndjson_auto(...) — you're going to confirm in a moment that, in this particular case, it wasn't strictly required (a simple path also works with .jsonl), but it's worth learning the explicit syntax now, because it's what you're going to need the day a real file requires options the automatic detection can't guess.

Also notice the orders_*.csv and events_*.jsonl pattern: the asterisk is a glob, a wildcard DuckDB expands to read the seven daily files — orders_2026-08-03.csv through orders_2026-08-09.csv — as if they were a single table, in one pass, without you having to name every file or write seven UNION ALLs. stores.csv and products_v1.csv, on the other hand, point at a single file each — there's no pattern to expand, because there's no other file to combine it with.

Verification: compiled SQL, with no .sql model yet

Before writing the first staging model — that starts in lesson 7 — you can verify external_location works with dbt compile --inline, which compiles a loose query with no need to save it as a file:

dbt compile --inline "select * from {{ source('kiosko_raw', 'orders') }}"

What to expect.

Compiled inline node is:
select * from 'raw_data/kiosko/orders_*.csv'

Compare this against the compiled SQL you saw in lesson 3 (from "kiosko"."kiosko_raw"."orders", the catalog reference that failed). Now source('kiosko_raw', 'orders') expands straight to the physical path, in single quotes — exactly the same mechanism you already used, with no dbt, in the previous module when you wrote SELECT * FROM 'file.csv' by hand with plain DuckDB. Repeat the check with the other three sources:

dbt compile --inline "select * from {{ source('kiosko_raw', 'events') }}"
dbt compile --inline "select * from {{ source('kiosko_raw', 'stores') }}"
dbt compile --inline "select * from {{ source('kiosko_raw', 'products') }}"

What to expect.

Compiled inline node is:
select * from read_ndjson_auto('raw_data/kiosko/events_*.jsonl')

Compiled inline node is:
select * from 'raw_data/kiosko/stores.csv'

Compiled inline node is:
select * from 'raw_data/kiosko/products_v1.csv'

events keeps the read_ndjson_auto(...) call exactly as you wrote it — dbt-duckdb doesn't reformat function calls, it passes them through literally — while stores and products expand as simple paths in quotes, just like orders.

Now, the real test: request actual rows, with dbt show --inline (the same mechanism, but running the query against the warehouse instead of just compiling it):

dbt show --inline "select * from {{ source('kiosko_raw', 'orders') }} order by order_id" --limit 3

What to expect.

Previewing inline node:
| order_id | store_id | product_id | quantity | unit_price |            order_ts |
| -------- | -------- | ---------- | -------- | ---------- | ------------------- |
| ORD-1001 | S01      | P001       |        3 |       0.55 | 2026-08-03 08:14:00 |
| ORD-1002 | S01      | P002       |        1 |       1.20 | 2026-08-03 08:20:00 |
| ORD-1003 | S02      | P003       |        2 |       0.75 | 2026-08-03 08:31:00 |

Real rows, from the first file of the week, read straight from raw_data/kiosko/orders_2026-08-03.csv — with no prior loading step, no intermediate table. Repeat with the other three sources:

dbt show --inline "select * from {{ source('kiosko_raw', 'events') }} order by event_id" --limit 3
dbt show --inline "select * from {{ source('kiosko_raw', 'stores') }} order by store_id" --limit 5
dbt show --inline "select * from {{ source('kiosko_raw', 'products') }} order by product_id" --limit 5

What to expect.

Previewing inline node:
| event_id | event_type  | session_id |            event_ts |
| -------- | ----------- | ---------- | ------------------- |
| E5001    | page_view   | SESS-01    | 2026-08-03 08:00:12 |
| E5002    | add_to_cart | SESS-01    | 2026-08-03 08:02:45 |
| E5003    | purchase    | SESS-01    | 2026-08-03 08:03:10 |

Previewing inline node:
| store_id | store_name    | city     |
| -------- | ------------- | -------- |
| S01      | Kiosko Centro | Bogota   |
| S02      | Kiosko Norte  | Lima     |
| S03      | Kiosko Sur    | Santiago |

Previewing inline node:
| product_id | product_name         | category    | unit_cost | product_updated_at |
| ---------- | -------------------- | ----------- | --------- | ------------------ |
| P001       | Bottled Water 600ml  | beverages   |      0.40 |         2026-08-01 |
| P002       | Energy Bar           | snacks      |      0.60 |         2026-08-01 |
| P003       | Instant Coffee Sa... | beverages   |      0.35 |         2026-08-01 |
| P004       | Phone Charger Cable  | electronics |      2.10 |         2026-08-01 |

(P003's product_name cell shows up truncated with ... — that's just a column-width limit in how dbt show draws the table in the terminal, not a data problem: the real value is still Instant Coffee Sachet, in full.) Notice something else, besides the data: DuckDB already inferred reasonable types without you asking — quantity as an integer, unit_price with two decimals, order_ts as a real timestamp, product_updated_at as a date — even though these four sources are, technically, just plain text in a CSV. That automatic inference is convenient for exploring data quickly, but it's no reliable type guarantee for a real project: lesson 7 is going to cast every column explicitly inside the staging models, precisely to avoid depending on DuckDB guessing right every time.

Going deeper: did you really need read_ndjson_auto() for events?

Try it yourself. Temporarily change events's external_location to a simple path, with no function:

external_location: "raw_data/kiosko/events_*.jsonl"

Run dbt show --inline "select * from {{ source('kiosko_raw', 'events') }} order by event_id" --limit 3 again — you're going to see exactly the same result as before. DuckDB recognizes the .jsonl extension just like it recognizes .csv, and applies its own auto-detection mechanism with no need to ask for it explicitly. Set read_ndjson_auto('raw_data/kiosko/events_*.jsonl') back before continuing — this guide prefers the explicit form for events, not because it's required in this particular case, but because it's the syntax you really are going to need the day a real JSON file doesn't let itself be cleanly auto-detected: a nested JSON with an irregular structure, columns you want named differently than they come in the file, or a format that isn't JSON Lines but a single large JSON array (format = 'array', an option only the function form lets you pass). Seeing the explicit form work here, on a simple case, prepares you to recognize it when it really is essential.

Common mistakes

A typo in the glob pattern, with external_location already declared. What happens: someone writes "raw_data/kiosko/order_*.csv" (missing the "s") in meta.external_location, instead of orders_*.csv. Why it happens: it's the same typo you already saw in lesson 3, but this time it happens inside the configuration YAML instead of in a hand-written FROM. How to spot it: running any dbt show --inline or dbt compile that uses that source, the error is:

Runtime Error
  IO Error: No files found that match the pattern "raw_data/kiosko/order_*.csv"

How to fix it: check external_location's exact value against the real file names in raw_data/kiosko/ — a quick ls raw_data/kiosko/ confirms what really exists; unlike the Compilation Error of a misspelled source() (lesson 3), this error really does require dbt to try touching the warehouse before it shows up, because the source's name is valid — the problem is one level deeper, in the physical path that name resolves to.

Forgetting single quotes inside a function call. What happens: someone writes external_location: read_ndjson_auto(raw_data/kiosko/events_*.jsonl), with no quotes around the path inside the function. Why it happens: you already wrote simple paths inside the YAML's double quotes ("raw_data/...") several times in this lesson, and it's easy to forget that, inside a function call, the path also needs its own single quotes, because that's where DuckDB — not dbt-duckdb — interprets it as a SQL text string. How to spot it: the error shows up as a SQL syntax problem, typically complaining about an unrecognized identifier with the folder or file's name. How to fix it: any path living inside function parentheses needs its own single quotes — read_ndjson_auto('raw_data/kiosko/events_*.jsonl'), exactly as it appears in this lesson's worked example.

Thinking dbt show --inline is the module's final step. What happens: someone, after seeing real rows with dbt show --inline, assumes the staging layer is already done, without having written any permanent .sql file. Why it happens: dbt show --inline really does demonstrate the mechanism works end to end, and it feels like "that's it" — but it's a quick verification tool, not a persistent project object. How to spot it: if you run dbt ls and see no new model under models/staging/kiosko/, you haven't built anything permanent yet — you've only confirmed the sources pipeline works. How to fix it: lessons 5, 6, and 7 are the ones that turn this verification into the four real .sql files (stg_orders, stg_events, stg_stores, stg_products) that are going to persist as part of the project.

Exercises

Exercise 1 — Break stores's glob and read the error. Temporarily change stores's external_location to "raw_data/kiosko/store.csv" (missing the final "s") and run dbt show --inline "select * from {{ source('kiosko_raw', 'stores') }}". Write down the exact message, and fix it before continuing.

Runtime Error
  IO Error: No files found that match the pattern "raw_data/kiosko/store.csv"

Exercise 2 — Count each source's rows with plain SQL. Using dbt show --inline, write the four queries needed to confirm the total row count for each of the four sources (orders, events, stores, products), without looking at lesson 1's or 2's table.

dbt show --inline "select count(*) as n from {{ source('kiosko_raw', 'orders') }}"
dbt show --inline "select count(*) as n from {{ source('kiosko_raw', 'events') }}"
dbt show --inline "select count(*) as n from {{ source('kiosko_raw', 'stores') }}"
dbt show --inline "select count(*) as n from {{ source('kiosko_raw', 'products') }}"

Exercise 3 — Explain, in your own words, the difference between the two external_location forms. In 2-3 sentences, using the dock-address analogy, explain when it makes sense to use a simple path in quotes and when it makes sense to use an explicit function call.

Summary and next step

In this lesson you completed the last piece source() was missing: meta.external_location, in its two forms — simple path and explicit function call — finally connecting Kiosko's four sources to their real files in raw_data/kiosko/. You confirmed, with dbt compile --inline and dbt show --inline, that every source now compiles to a real file read and returns real rows — with no permanent .sql file existing yet in the project — and saw that DuckDB detects .jsonl automatically just as much as .csv, though this guide prefers the explicit form for events for the flexibility it offers down the road.

Before moving on you should be able to: write from memory the two valid forms of external_location; and explain what command you'd use to preview a source's rows without writing any new .sql file.

With the four sources fully working, lesson 5 takes the next conceptual step: what, exactly, a staging model is, and why the rule "one view per source table, no joins or aggregations" isn't this guide's whim but a documented convention from the dbt Labs team itself.

Resources

• dbt-duckdb — official GitHub repository, the sources configuration section with meta.external_location, including examples of both forms (simple path and explicit function). github.com/duckdb/dbt-duckdb. In English.
• DuckDB — "Multiple Files," official documentation for glob patterns (*) to read several files as a single table, the basis for this lesson's orders_*.csv pattern. duckdb.org/docs/stable/data/multiple_files/overview. In English.
• DuckDB — "JSON Loading," official documentation for read_json, read_json_auto, and read_ndjson_auto, including the format = 'newline_delimited' option for JSON Lines. duckdb.org/docs/stable/data/json/loading_json. In English.
• dbt Developer Hub — "dbt show," the reference for the command used in this lesson with the --inline flag to preview loose queries. docs.getdbt.com/reference/commands/show. In English.