Your first model and `dbt run`

Description

A dbt model is, in its purest form, a .sql file inside models/ that contains a single SELECT — no CREATE TABLE, no CREATE VIEW, just the query. dbt takes care of wrapping that SELECT in the correct DDL statement (CREATE VIEW <name> AS ... or CREATE TABLE <name> AS ..., depending on how you configure it) and running it against the warehouse — exactly the mechanism you already saw, done by hand with plain DuckDB, in lesson 3.

This lesson writes that first file, deliberately trivial — it doesn't touch any real Kiosko data yet, that starts in module 2 — and runs dbt run, the command that turns .sql files into real objects inside kiosko.duckdb. By the end of this lesson you're going to have, for the first time, a queryable table built by dbt.

Connection to the module. Lessons 5 and 6 built the scaffolding — dbt_project.yml, profiles.yml — without a single model existing yet. This lesson is where that scaffolding finally gets used for something: dbt run has nothing to do if models/ is empty, and up to now it has been.

An analogy: the mold and the casting machine

Think about how a mass-produced metal part gets made. The designer creates a mold: a hollow shape, inert on its own, that doesn't manufacture anything by itself. A casting machine takes that mold, pours molten metal into it, and produces the final part, solid, ready to use — and it can repeat the process as many times as needed, always with the same result, as long as the mold doesn't change.

Your .sql file is the mold: it defines the shape of the result (what columns, what rows, what logic), but on its own, saved to disk, it produces nothing — it's just text. dbt run is the casting machine: it reads the mold, fills it with the warehouse's real data at that moment, and produces the final object — a real, queryable table or view — inside kiosko.duckdb. If you run dbt run twice without changing the mold, you get the same result both times — the same guarantee you'd expect from any well-built mass-production machine, and the same idempotency principle you already saw in data-engineering-foundations-guide, now applied to a complete dbt model.

Worked example: my_first_dbt_model.sql

Inside models/, create an example/ subfolder — a clearly temporary place, which you're going to replace in module 2 with Kiosko's real staging models:

mkdir -p models/example

And inside that folder, the very first .sql file of the whole project:

-- models/example/my_first_dbt_model.sql
select
    1 as id,
    'kiosko_analytics project is alive' as status
union all
select
    2 as id,
    'connected to kiosko.duckdb' as status

Notice what this file does not have: no CREATE, no table name written, no reference to kiosko.duckdb. It's, literally, just the SELECT — two fixed rows, not depending on any external data yet, so this lesson's only purpose is to confirm the entire mechanism works end to end. The file's name, my_first_dbt_model, is exactly what the resulting object is going to be called inside kiosko.duckdb — dbt never asks you to declare that name separately.

Now, the command that turns that file into a real object:

dbt run

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Unable to do partial parsing because saved manifest not found. Starting full parse.
Found 1 model, 500 macros

Concurrency: 4 threads (target='dev')

1 of 1 START sql view model main.my_first_dbt_model ............................ [RUN]
1 of 1 OK created sql view model main.my_first_dbt_model ....................... [OK in 0.04s]

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

Completed successfully

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

Read this output from top to bottom, because every line reports something specific. Found 1 model, 500 macros — dbt found your one .sql file in models/ (the "500 macros" are internal functions dbt-core ships with out of the box; you didn't write them, and you're going to write your first one in module 7). 1 of 1 START sql view model main.my_first_dbt_model — dbt identified your file as a view-type model (the default you configured in dbt_project.yml, lesson 5) in the main schema (DuckDB's default schema). OK created sql view model confirms the CREATE VIEW ran with no errors. And the final line, Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1, is the summary you're going to learn to read from memory for the rest of this guide — one model, zero errors, zero warnings.

Verify the result with the dbt command built exactly for this — previewing a model's content without leaving dbt:

dbt show --select my_first_dbt_model --output json

What to expect.

{
  "node": "my_first_dbt_model",
  "show": [
    {
      "id": 1,
      "status": "kiosko_analytics project is alive"
    },
    {
      "id": 2,
      "status": "connected to kiosko.duckdb"
    }
  ]
}

The two rows you wrote in the SELECT are there, exactly as written — but notice you never "inserted" them anywhere: they're the direct result of running your query against the warehouse, at the moment you ran dbt run. And since the kiosko.duckdb file is a normal file on your system, you can also confirm it outside dbt, with DuckDB's own client:

import duckdb
con = duckdb.connect("kiosko.duckdb")
print(con.sql("SELECT * FROM my_first_dbt_model ORDER BY id"))

What to expect.

┌───────┬───────────────────────────────────┐
│  id   │              status               │
│ int32 │              varchar              │
├───────┼───────────────────────────────────┤
│     1 │ kiosko_analytics project is alive │
│     2 │ connected to kiosko.duckdb        │
└───────┴───────────────────────────────────┘

This last step confirms something important: my_first_dbt_model isn't a dbt-exclusive concept — it's a real, persistent view, indistinguishable from any CREATE VIEW you would have written by hand in lesson 3. dbt didn't invent its own storage format; it built, with your SELECT, exactly the same kind of object you already knew how to create without it.

Idempotency: run dbt run a second time

Without changing a single line of the .sql file, run the same command again:

dbt run

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 1 model, 500 macros

Concurrency: 4 threads (target='dev')

1 of 1 START sql view model main.my_first_dbt_model ............................ [RUN]
1 of 1 OK created sql view model main.my_first_dbt_model ....................... [OK in 0.07s]

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

Completed successfully

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

PASS=1 again, identical to the first run (the time in milliseconds varies, as is normal — no software run takes exactly the same time twice). dbt didn't "accumulate" a second copy of the two rows, and it didn't fail with "already exists" — a view model gets completely recreated every time you run dbt run, with CREATE OR REPLACE VIEW underneath. Running dbt run over and over, on the same SELECT, always produces the same result — the same idempotency property you demanded in the foundations pipeline, now guaranteed out of the box by the way dbt materializes a view model.

Common mistakes

Writing CREATE VIEW or CREATE TABLE inside the model file. What happens: someone, coming from years of writing loose SQL, writes CREATE VIEW my_first_dbt_model AS SELECT ... inside the .sql file itself, instead of just the SELECT. Why it happens: it's the natural reflex for anyone used to SQL without dbt — it's exactly what you wrote by hand in lesson 3. How to spot it: dbt run fails with a SQL syntax error, because dbt is already wrapping your file in its own CREATE VIEW my_first_dbt_model AS (...), and you end up with one CREATE VIEW nested inside another, something no SQL engine accepts. How to fix it: a dbt model file contains only the SELECT — never the CREATE, never the table name. dbt builds that wrapper automatically from the file's name and the materialization config.

Confusing the error Catalog Error: Table with name ... does not exist! with a dbt problem. What happens: someone writes a model that references a table that doesn't exist yet — for example, writing a table name directly in the FROM, instead of using source() or ref() (which you're going to get to know in modules 2 and 3) — and dbt run fails. For example:

-- models/example/broken_model.sql
select
    1 as id,
    'broken' as status
from nonexistent_table

Running dbt run --select broken_model, the output includes exactly this:

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

[ERROR]: in model broken_model (models/example/broken_model.sql)
  Runtime Error in model broken_model (models/example/broken_model.sql)
  Catalog Error: Table with name nonexistent_table does not exist!
  Did you mean "sqlite_temp_master"?

  LINE 8: from nonexistent_table
               ^

  compiled code at target/compiled/kiosko_analytics/models/example/broken_model.sql

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

Why it happens: it's the same error you already saw in lesson 1, without dbt — DuckDB can't find a table the SELECT needs. How to spot it: the error explicitly says Catalog Error, not a dbt error — dbt compiled your SQL with no problem and handed it to DuckDB, which is what rejected the query. How to fix it: check that every table name in your FROM really exists in the warehouse — starting in module 2, you're going to stop writing loose table names and start using source()/ref(), which make this specific error impossible (dbt would fail earlier, at compile time, if the reference doesn't exist in the project).

Forgetting dbt run and staring at a .sql file that "does nothing." What happens: someone writes a perfectly valid model, saves it, and expects it to automatically show up in kiosko.duckdb — as if saving the file were already enough. Why it happens: in other contexts (an app with hot reload, a notebook that runs each cell as you save it) saving and executing are linked; in dbt, never. How to spot it: if you query kiosko.duckdb and the model you just wrote is nowhere to be found, confirm you ran dbt run after saving the file. How to fix it: remember the mold analogy — a saved .sql file is only the mold; nothing gets produced until you run the casting machine, dbt run.

Exercises

Exercise 1 — Add a third row. Modify models/example/my_first_dbt_model.sql to have a third row, with id = 3 and whatever status you want. Run dbt run again and confirm, with dbt show, that there are now three rows.

select
    1 as id,
    'kiosko_analytics project is alive' as status
union all
select
    2 as id,
    'connected to kiosko.duckdb' as status
union all
select
    3 as id,
    'ready for module 2' as status

Exercise 2 — Break the model on purpose and read the error. Temporarily change a column's name in the SELECT so the two sides of the union all have columns with different names (for example, status in the first half and estado in the second). Run dbt run and observe the exact error.

select
    1 as id,
    'kiosko_analytics project is alive' as status
union all
select
    2 as id,
    'connected to kiosko.duckdb' as estado

Exercise 3 — Argue this lesson's boundary. This lesson's model lives in models/example/, an explicitly temporary folder. Without writing code yet, explain in 2-3 sentences why this model is not, yet, part of Kiosko's real warehouse, and what's going to change starting in module 2.

Summary and next step

In this lesson you wrote your first real dbt model — models/example/my_first_dbt_model.sql, a file that contains only a SELECT — and ran dbt run for the first time in this entire guide: dbt turned it into a real view inside kiosko.duckdb, with PASS=1 WARN=0 ERROR=0. You confirmed the result with dbt show and with a direct DuckDB query, ran dbt run a second time to verify idempotency, and saw two real errors — a nonexistent table and a UNION ALL with incompatible columns — that aren't dbt errors, but come from the SQL engine dbt invokes on your behalf.

Before moving on you should be able to: write a trivial dbt model from memory; explain exactly what dbt run does, in terms of the CREATE VIEW/CREATE TABLE it generates; and tell a dbt compilation error apart from an underlying SQL engine error.

With all four pieces of the scaffolding complete — installation, anatomy, connection, first model — lesson 8 closes the module with a mini-project: rebuilding the complete kiosko_analytics project, from an empty folder, in one continuous flow — and taking the first step of version control over it.

Resources

• dbt Developer Hub — "About models," the official reference for what a dbt model is and how the file name relates to the resulting object. docs.getdbt.com/docs/build/models. In English.
• dbt Developer Hub — "dbt run," the complete reference for the command used in this lesson, including the --select flags you already used. docs.getdbt.com/reference/commands/run. In English.
• dbt Developer Hub — "dbt show," the reference for the preview command used in this lesson to inspect a model's result without leaving dbt. docs.getdbt.com/reference/commands/show. In English.