Module 1: Your First Dbt Project

dbt and the T in ELT

Next →

Description

data-engineering-foundations-guide (module 3) already taught you the difference between ETL and ELT: in ETL you transform the data before loading it into the warehouse, usually in an external process; in ELT you load the raw data first and transform it inside the warehouse itself, taking advantage of its columnar engine. This lesson doesn't repeat that distinction — it takes it as given and asks a more specific question: where, exactly, does dbt live within those three letters?

The answer, in a sentence you're going to repeat for the rest of this guide: dbt is only the T. Never the E, never the L. dbt has no command that connects to an external API to extract data, and no command that loads a .csv file into a table from scratch. The only thing dbt knows how to do is take data that's already in the warehouse and transform it with SQL — nothing more, nothing less. That restriction, far from being a limitation, is what makes dbt good at its job: it doesn't compete with your extraction pipeline, it plugs in at the end of it.

Connection to the module. Lesson 2 gave you the role — analytics engineer. This lesson gives that role its exact territory within the data flow: everything you do with dbt, from module 2 onward, is going to assume Kiosko's raw files (orders, events, stores, products) are already accessible to the warehouse — dbt is never going to go fetch them for you. That boundary is, precisely, the same one lesson 2 drew when it said the analytics engineer "doesn't build the library building."

An analogy: the interpreter who never leaves the conference room

Think of a professional interpreter at an international conference. They don't travel to fetch speakers from their home countries — the event organization does that — and they don't decide who gets handed the final printed transcript — the logistics team does that. Their job starts exactly when the speaker is already at the podium, talking, and ends exactly when they finish translating that sentence into the audience's language. They don't extract the speaker from anywhere, don't transport them, don't load them onto a stage — they only transform what's already there, in real time, with a clear rule for input and output.

dbt is that interpreter. It's not going to go fetch orders.csv from any external system (that's extraction — the work of a pipeline like the one you built in foundations). It doesn't decide where the final file physically lives or who has access to it (that's loading and infrastructure). The only thing it does, and does with discipline, is take what has already arrived at the warehouse and turn it, with SQL, into something with a different shape — just like the interpreter doesn't change the content of the speech, only its shape, so the right audience can use it.

   E (Extract)          L (Load)            T (Transform)
   ────────────         ──────────          ──────────────────
   orders.csv       -->  DuckDB/warehouse -->  dbt: SELECT ... FROM
   events.jsonl          (raw table,            {{ source(...) }}
   (foundations,          "as it arrives")      (this guide, module
    module 2)                                    2 onward)

   dbt NEVER touches   dbt NEVER decides      dbt ONLY lives here
   this part             where it's stored

Worked example: a SELECT becomes a real warehouse object

Module 4 of data-engineering-foundations-guide transformed data by moving it into Python objects in memory — a list of dataclass Order — computing revenue there, and then writing the result back to a file. That pattern is valid, but it has a cost: the data leaves the warehouse, travels to a Python process, and comes back in. dbt does the transformation a different way: it runs entirely inside the SQL engine, with data never passing through an intermediate Python object. You're going to see that difference concretely, without dbt yet — you install it in lesson 4 — using plain DuckDB to demonstrate the exact mechanism dbt automates.

-- create the base table, as you already did in lesson 1
CREATE TABLE stores_table AS
SELECT * FROM (VALUES
    ('S01', 'Kiosko Centro', 'Bogota'),
    ('S02', 'Kiosko Norte', 'Lima'),
    ('S03', 'Kiosko Sur', 'Santiago')
) AS t(store_id, store_name, city);
-- the SELECT that "transforms" -- this is, literally, what's going to live
-- inside a dbt .sql file starting in lesson 7
CREATE VIEW orders_by_store_view AS
SELECT
    s.store_id,
    s.store_name,
    COUNT(*) AS order_count
FROM stores_table AS s
JOIN (VALUES ('S01'), ('S01'), ('S02'), ('S03'), ('S03'), ('S03')) AS o(store_id)
    ON o.store_id = s.store_id
GROUP BY s.store_id, s.store_name
ORDER BY s.store_id;
import duckdb
con = duckdb.connect("t_in_elt.duckdb")
con.execute(open("step1_create_stores.sql").read())
con.execute(open("view_demo.sql").read())

print("=== Querying the view, like any table ===")
print(con.sql("SELECT * FROM orders_by_store_view ORDER BY store_id"))

What to expect. Running this script, the output is exactly this:

=== Querying the view, like any table ===
┌──────────┬───────────────┬─────────────┐
│ store_id │  store_name   │ order_count │
│ varchar  │    varchar    │    int64    │
├──────────┼───────────────┼─────────────┤
│ S01      │ Kiosko Centro │           2 │
│ S02      │ Kiosko Norte  │           1 │
│ S03      │ Kiosko Sur    │           3 │
└──────────┴───────────────┴─────────────┘

Same result you already saw in lesson 1 — but pay attention to the mechanical difference, not the number. CREATE VIEW didn't compute anything in Python: it asked the DuckDB engine to store the SELECT's definition as a database object, and that object gets recalculated every time someone queries it, inside the engine itself. No dict, no dataclass, no intermediate Python object was involved — the join, the COUNT(*), the GROUP BY, all of it ran in SQL, inside the warehouse. This is, exactly, what a dbt model does: every .sql file you're going to write starting in lesson 7 is a SELECT, and dbt automatically wraps it in the CREATE VIEW file_name AS (or CREATE TABLE, depending on how you configure it) that you just wrote by hand here. dbt doesn't replace your SQL — it generates the DDL wrapper around it and decides when to execute it.

Diagram: where dbt lives, precisely

flowchart LR
    subgraph outside["Outside dbt (outside this guide)"]
        A["orders.csv, events.jsonl\n(Kiosko's raw files)"]
    end
    subgraph inside["Inside the warehouse (DuckDB)"]
        B["Data already accessible\nto dbt-duckdb"]
        C["source('kiosko_raw', 'orders')\n(module 2)"]
        D["stg_orders, fact_orders, ...\n(SELECTs compiled by dbt)"]
    end
    A -.->|"E + L: foundations pipeline\ndbt NEVER does this part"| B
    B --> C
    C -->|"dbt ONLY lives here: the T"| D

Going deeper: why "doesn't extract or load" is a design decision, not a limitation

It's tempting to think dbt "should" be able to connect straight to an external API and load the data for you — after all, it's a data tool. But that restriction is deliberate, and comes with a concrete advantage: by not getting involved in extraction or loading, dbt can fully specialize in doing one thing well — compiling SQL, resolving dependencies between models, running tests, generating documentation — without competing with the dozens of tools that do specialize in extraction (Fivetran, Airbyte, or the Python pipeline you already built in foundations) or with the ones that specialize in orchestrating the load (Airflow, which you're going to meet in airflow-and-declarative-orchestration-guide).

This separation of responsibilities has a technical name within the dbt ecosystem itself: dbt focuses on the T of ELT, and assumes something else — a pipeline, an ingestion tool, or, in this guide's particular case, the dbt-duckdb adapter reading files directly — has already resolved the E and the L before dbt enters the picture. You're going to see this very concretely in module 2: dbt-duckdb can read a .csv directly with meta.external_location, without a prior step "loading" it into a table — but that's still the L, executed by the adapter, not an exception to the rule. dbt still never goes to fetch the file from any external system; it simply reads what's already on disk, accessible.

Common mistakes

Expecting dbt to have a command to "pull data from an API." What happens: someone searches dbt's docs for how to connect a source like Stripe or Salesforce directly, with no intermediate step. Why it happens: dbt Labs also sells (in dbt Cloud) orchestration features and integrations, and it's easy to assume dbt-core itself includes them. How to spot it: if your search leads you to third-party tools (Fivetran, Airbyte, a custom connector) instead of a dbt command, that confirms this lesson's rule — no such command exists, and it's not a gap in the tool, it's its design. How to fix it: remember the interpreter analogy — dbt never goes out to fetch the speaker; for Kiosko, "fetching the speaker" was already solved by data-engineering-foundations-guide, and module 2 of this guide shows you how dbt-duckdb reads those already-existing files.

Thinking a dbt model "runs" in Python. What happens: someone tries to debug a slow dbt model by checking Python's memory usage, or assumes dbt brings the data into a DataFrame before transforming it. Why it happens: dbt-core is written in Python, and that fact makes it easy to confuse "the tool is in Python" with "the transformation runs in Python." How to spot it: if your dbt model handles millions of rows without your laptop's memory noticeably going up, that's the clue — the data never left the SQL engine. How to fix it: this lesson's worked example — Python only compiled and sent the SQL to DuckDB; the JOIN, the GROUP BY, and the COUNT(*) ran entirely inside the columnar engine, exactly like any dbt model does.

Confusing "view" with "something temporary that disappears." What happens: someone assumes a VIEW created with CREATE VIEW is a one-time result, like a Python script's print(), and is surprised when it still exists on later runs. Why it happens: in Python, a printed result disappears as soon as the script ends; in SQL, an object created with CREATE VIEW or CREATE TABLE persists in the warehouse's catalog until someone explicitly drops it. How to spot it: if you close your DuckDB session and reopen the same .duckdb file, the view is still there, queryable, without you ever re-running the CREATE VIEW. How to fix it: treat every dbt model for what it is — a real, persistent warehouse object, not an ephemeral output of a script. Lesson 3 of module 3 (materializations) goes deeper into this distinction between view (recalculated on every query) and table (rows physically stored).

Exercises

Exercise 1 — Classify three steps of Kiosko's pipeline. For each step, say whether it belongs to the E, the L, or the T of ELT:

  • (a) extract_orders() from foundations' module 2, which reads orders_2026-08-03.csv from the file system.
  • (b) Writing a given date's bronze partition to data/bronze/orders/dt=2026-08-03/orders.csv.
  • (c) A dbt model fact_orders.sql that does a JOIN between stg_orders and stg_stores.
See solution
  • (a) E (Extract). It brings the data from its source system (the file exported by the point of sale) to the process that's going to move it.
  • (b) L (Load). It leaves the data already accessible, in its raw form, inside the warehouse's landing area — bronze, in foundations' language.
  • (c) T (Transform). It takes data that's already in the warehouse (stg_orders, stg_stores) and combines it with SQL — exactly dbt's exclusive territory.

Exercise 2 — Find the exact boundary. dbt-duckdb can read a .csv file directly with meta.external_location, without a Python script first loading it into a table. Using this lesson's "going deeper" criteria, explain why this does not contradict the rule "dbt is only the T."

See solution

It doesn't contradict it because whoever reads the physical file and makes it available to a SQL query is the dbt-duckdb adapter — a piece of reading infrastructure, equivalent to the L — not the dbt model itself. The dbt model still does exactly the same thing it always does: a SELECT over something already queryable. The difference from a traditional pipeline is only mechanical — there's no need for a prior step that copies the file into an intermediate table — but conceptually the L is still happening, just more directly. dbt itself never decides to go fetch a file on its own; it always starts from something already declared as accessible.

Exercise 3 — Argue it in your own words. Using this lesson's interpreter analogy, explain in 2-3 sentences why separating E, L, and T into different tools or steps — instead of one single tool that does all three — is an advantage, not an unnecessary complication.

See solution

A reasonable argument: "Separating E, L, and T lets each piece specialize and be swapped out without touching the others — the same way an interpreter doesn't need to know how the speaker's trip was organized to do their job well, a dbt model doesn't need to know whether the data arrived through a homemade Python pipeline or a paid ingestion tool. If tomorrow Kiosko changes how it extracts orders.csv — from its own pipeline to a tool like Fivetran — no dbt model has to be rewritten, because the T never depended on how the E and the L were solved." The central point is that the clear boundary between the three letters is what makes it possible to replace one piece without breaking the other two.

Summary and next step

In this lesson you placed dbt precisely within ELT: dbt is only the T — it never extracts data from a source system, never decides where it's first loaded, and all its capability is concentrated in transforming, with SQL, data that's already accessible inside the warehouse. You saw, with a real, executed example, that a dbt model is nothing more than a SELECT automatically wrapped in a CREATE VIEW or CREATE TABLE that runs entirely inside the SQL engine, with no data ever passing through an intermediate Python object — the central difference from the pattern you used in data-engineering-foundations-guide, module 4.

Before moving on you should be able to: explain, in your own words, why dbt never has a command to "connect to an API"; and tell, for any step in a pipeline, whether it belongs to the E, the L, or the T.

With the module's theory closed, the next four lessons are hands-on ground: lesson 4 installs dbt-core and the DuckDB adapter for real, on your machine, with the exact commands and the most common installation pitfalls.

Resources

  • dbt Developer Hub — "What, exactly, is dbt?," the official page that defines dbt's scope within a modern data stack. docs.getdbt.com/docs/introduction. In English.
  • dbt-duckdb — official GitHub repository, with the explanation of how the adapter reads files directly via external_location, the "L" piece this lesson mentions. github.com/duckdb/dbt-duckdb. In English.
  • AWS — "The difference between OLAP and OLTP," already cited in data-engineering-foundations-guide, useful here to recall why transforming inside a columnar engine (ELT's premise) is different from transforming in an external process. aws.amazon.com/compare/the-difference-between-olap-and-oltp. In English.
← PreviousWhat is analytics engineering?Next →Installing dbt-core and dbt-duckdb