Guide introduction: from loose SQL to a software project

Why this guide exists

data-modeling-for-analytics-guide left Kiosko with a real dimensional warehouse: a complete star schema, a historized product dimension with hand-written SCD type 2 in MERGE INTO, an accumulating snapshot, a wide table for BI. The model is well designed. The problem is where it lives: in a handful of loose .sql files that someone runs by hand, in an order only that person remembers. If tomorrow that person is on vacation, or switches teams, or simply forgets that dim_store.sql has to run before fact_orders.sql, the entire warehouse stops rebuilding — and no one else on the team knows why.

This guide closes that debt. It doesn't teach you a new dimensional model — you already designed that one — it teaches you analytics engineering: the discipline of treating data transformation (the "T" in ELT) as real code. Code that's versioned with git, that's tested automatically before you trust it, that generates its own documentation, and that resolves its own execution order without anyone having to memorize it. The tool you're going to use for that is called dbt (data build tool), specifically its free, local edition: dbt-core, connected to DuckDB — the same columnar engine you already used in data-modeling-for-analytics-guide, now talking to a dbt project instead of loose scripts.

Honest market note. dbt shows up as a primary requirement in analytics engineer postings in Spain and in remote US roles — in junior LATAM postings it's usually listed as "nice to have," not required. And in September 2025 Fivetran acquired SQLMesh, a competing transformation engine, completing the merger with dbt Labs on June 1, 2026 — the transformation-tools market keeps moving. That's why this guide doesn't sell you "learn dbt" as an end in itself: it teaches you the versioned transformation layer — declarative tests, macros, lineage, generated documentation — with dbt as the concrete vehicle. If the market rotates toward another tool with the same philosophy, what you learned here transfers; the concept is what matters, not the logo.

The case that stays with us: Kiosko (unchanged)

You're still working with the same Kiosko as always: the convenience-store chain with a delivery app. orders is the transactional world of the point of sale — one row per sale. events is the app's clickstream — page_view, add_to_cart, purchase. And you already have, from data-modeling-for-analytics-guide, a complete dimensional model designed over that data: a central sales fact, store and product dimensions (the latter historized), and several more advanced pieces you're going to rebuild module by module.

This guide doesn't invent new data or redesign the model. It takes exactly what already exists and rebuilds it inside a dbt project: a directory with a standard structure, a configuration file, and .sql files that dbt turns into real tables and views inside a kiosko.duckdb file — the same kind of local, free warehouse you already know, now managed by a tool that knows in what order to build each piece.

An analogy: the versioned recipe book

Think of two kitchens. In the first, the head chef has fifteen years of experience and knows, by heart, the exact order in which every dish on the menu needs to be prepared: first the base stock, then the reduction, then the plating. It works — as long as that chef is there. If they get sick, if a new assistant tries to cover their shift, or if a dish's recipe changes and nobody tells the rest of the team, the whole kitchen stalls or produces something different from what was promised. The knowledge lives in a single head, unwritten, unversioned, with no way to verify that today's dish is the same as yesterday's.

In the second kitchen, every recipe is written in a shared book: what ingredients it needs, in what order the steps happen, and a quality check at the end — does the sauce have the right consistency, did the dish come out at the temperature the menu promises — before it goes out to the table. Any new cook can open the book and follow the exact recipe, without asking anyone. If a recipe changes, it's updated in the book and the whole team sees the change. And if something goes wrong, the book tells you exactly which step to check, not the whole kitchen blindly.

data-modeling-for-analytics-guide gave you correct recipes — the dimensional model is well designed. But today they live in the head of whoever runs them by hand, in the right order, from memory. This guide teaches you to write the book: dbt is the system that turns your SQL recipes into something anyone on your team can open, follow, and trust, without you having to be there to explain it.

Worked example: the problem dbt solves, seen without dbt

Before installing anything, it's worth feeling the real problem that motivates this entire guide. You're going to rebuild, with plain DuckDB — no dbt yet — the exact situation the analogy describes: two SQL scripts that depend on each other, run by hand.

-- step1_create_stores.sql
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);

-- step2_orders_by_store.sql
CREATE TABLE orders_by_store 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;

step2 needs stores_table to already exist — that's, literally, a dependency between two files, exactly like fact_orders.sql needs dim_store to already exist in the real dimensional model. The question is: what happens if someone new on the team, not knowing that order, runs step2 first?

import duckdb
con = duckdb.connect("wrong_order.duckdb")
try:
    con.execute(open("step2_orders_by_store.sql").read())
except Exception as e:
    print(f"{type(e).__name__}: {e}")

What to expect. Running this (with step1 not yet executed), the output is exactly this:

CatalogException: Catalog Error: Table with name stores_table does not exist!
Did you mean "sqlite_master"?

LINE 6: FROM stores_table AS s
             ^

(Without the try/except, DuckDB raises the same uncaught exception, and you'd see a full Python traceback instead of this clean, single-piece message — the content of the error is identical, only how much noise you decide to show around it changes.)

DuckDB has no way of knowing that step2 depends on step1 — to the engine, they're two unrelated SQL commands with no declared relationship. Now run the same two scripts, this time in the right order:

con = duckdb.connect("right_order.duckdb")
con.execute(open("step1_create_stores.sql").read())
con.execute(open("step2_orders_by_store.sql").read())
print(con.sql("SELECT * FROM orders_by_store ORDER BY store_id"))

What to expect.

┌──────────┬───────────────┬─────────────┐
│ store_id │  store_name   │ order_count │
│ varchar  │    varchar    │    int64    │
├──────────┼───────────────┼─────────────┤
│ S01      │ Kiosko Centro │           2 │
│ S02      │ Kiosko Norte  │           1 │
│ S03      │ Kiosko Sur    │           3 │
└──────────┴───────────────┴─────────────┘

Same two files, same content — the only difference is the order a human decided to run them in. Nothing in the system enforced that order or verified it; it worked because, this time, someone remembered. Multiply this problem by the eight or ten real .sql files in Kiosko's warehouse, and by a team of five people instead of one, and the risk stops being hypothetical. That is, precisely, the problem dbt solves starting in module 3 of this guide: instead of you memorizing the order, you declare which model depends on which, with a function you'll get to know soon (ref()), and dbt figures out the correct order for you, every time, with no exceptions and no human memory involved.

The map of the ecosystem's 8 guides, so far

dbt-analytics-engineering-guide is the fourth of data-engineering-ecosystem's seventeen guides:

The guides that follow — airflow-and-declarative-orchestration-guide, spark-and-distributed-processing-guide, lakehouse-and-iceberg-guide, streaming-with-kafka-and-flink-guide, data-reliability-and-governance-guide — pick up specific pieces of what you build here. Lesson 7 of module 8 of this guide names, one by one, which guide solves what.

The map of this guide's 8 modules

Module   What it covers
──────   ─────────────────────────────────────────────────────────────
  1      (you are here) Installing dbt, understanding its anatomy, your first model
  2      Declaring Kiosko's raw files as source(), staging models
  3      ref(), the dependency DAG, rebuilding the star schema
  4      The four out-of-the-box tests, a custom test, business tests
  5      Snapshots: the SCD type 2 you did by hand, now automatic
  6      Incremental models: why rebuilding everything every time doesn't scale
  7      Reusable macros, self-generated documentation and lineage
  8      Project: Kiosko's complete dbt warehouse, end to end

Notice the progression: modules 1-3 build the skeleton — how a dbt project is put together and how dependencies between models are declared. Modules 4-6 make it reliable — tests, automatic historization, models that don't explode on re-run. Module 7 makes it legible to others — documentation and lineage that generate themselves, without anyone writing them by hand. And module 8 pulls it all together into Kiosko's complete dbt project.

The map of this module

Lesson    Question it answers
────────  ──────────────────────────────────────────────────────────
L1        (this one) What this guide is about, and what problem dbt solves
L2        What, exactly, an analytics engineer is
L3        Where dbt lives inside ELT -- only the T, never the E or the L
L4        How I install dbt-core and the DuckDB adapter
L5        What the minimal anatomy of a dbt project is
L6        How I connect that project to Kiosko's warehouse
L7        How I write and run my first real dbt model
L8        Project: Kiosko's first dbt project, from scratch

Lesson 2 answers who does this work — the analytics engineer role, and how it differs from a data engineer and a data analyst. Lesson 3 places dbt precisely inside ELT: it doesn't extract data, it doesn't load it, it only transforms it, with SQL that runs inside the warehouse itself. Lessons 4 through 7 are where the module turns hands-on: you install dbt for real, you understand every piece of a dbt project, you connect that project to Kiosko's warehouse, and you run your first real model. And lesson 8 closes with a mini-project that assembles the whole flow from an empty folder, including the first step of version control over the project.

The boundary: what does NOT belong in this guide (or this module)

This guide has specific sibling guides for what's deliberately left out here:

• Conceptual dimensional modeling (the Kimball process, declaring the grain, star vs. snowflake vs. OBT, SCD theory) → already taught by data-modeling-for-analytics-guide. Here that model gets implemented in dbt .sql files; it doesn't get re-justified as to why the grain is what it is.
• Orchestrating dbt in production (dbt Cloud, Airflow running dbt build as a DAG task, dev/prod environments, CI/CD) → airflow-and-declarative-orchestration-guide. Here dbt run executes by hand, from the terminal, start to finish.
• Production Python (packaging, pytest, structured logging) → python-for-data-engineering-guide. There's almost no Python here: dbt is declarative SQL, YAML, and Jinja.
• Distributed computing (Spark) and lakehouse table formats (Iceberg, Delta) → spark-and-distributed-processing-guide and lakehouse-and-iceberg-guide. Kiosko's warehouse still fits in DuckDB, in-process, on purpose.
• Governance and reliability at scale (published data contracts, observability, masking) → data-reliability-and-governance-guide. Here dbt docs generate produces the lineage of a single project, not a governance platform.

Within this specific module: you're going to install dbt and understand its anatomy, you're not yet going to declare Kiosko's raw data as source() (that's module 2) nor rebuild the full star schema (that starts in module 3). The first model you're going to run here is deliberately trivial — proof that the scaffolding works, not the real warehouse yet.

Common mistakes

Thinking this guide teaches you how to design a data model. What happens: someone opens module 3 expecting to be told, again, why fact_orders has that grain and those columns. Why it happens: it's easy to assume "a dbt course" includes dimensional modeling, because in practice many people learn both things together. How to spot it: if you're looking for a justification of why the model has the shape it has, that question was already answered in data-modeling-for-analytics-guide — this guide takes it as given. How to fix it: every time a module rebuilds a piece of the dimensional model, it will explicitly say "this was already designed, here it's only translated into dbt" — trust that signal and don't look for the design justification here.

Installing dbt Cloud instead of dbt-core. What happens: someone searches "dbt" and finds dbt Cloud first, dbt Labs' paid SaaS product, and creates an account there instead of installing the Python package. Why it happens: dbt Cloud is what's most promoted on dbt Labs' homepage, and it's easy to confuse it with "the way to use dbt." How to spot it: if at any point it asks you to log in to a web account before writing a model, that's not what this guide uses. How to fix it: this guide installs dbt-core, the open-source CLI, with pip install dbt-core dbt-duckdb — no account, no browser, 100% local. Lesson 4 walks through it step by step.

Skipping module 1 because "installing a tool" doesn't seem to need eight lessons. What happens: someone jumps straight to module 3 (where the real dimensional model starts) without first understanding what dbt_project.yml is, what profiles.yml is, and why they exist separately. Why it happens: installing something feels like a mechanical step, not like content. How to spot it: if in module 3 you can't explain the difference between the project name, the profile name, and the folder name — three things that in this module are, on purpose, the same word — you're missing this module's anatomy. How to fix it: lesson 5 exists exactly for that — it's worth reading carefully, not just copying the files.

Exercises

Exercise 1 — Find the hidden dependency. Go back to this lesson's worked example. Without running code, answer: if you added a third script, step3_top_store.sql, that queries orders_by_store to find the store with the most orders, in what order would the three scripts have to run, and why that specific order?

Exercise 2 — Break the order on purpose, with data. Using the same pair of scripts from the worked example (step1_create_stores.sql, step2_orders_by_store.sql), run step2 first against a fresh database and note the exact error message. Then run step1 and try step2 again. Confirm it works now.

import duckdb
con = duckdb.connect("ejercicio2.duckdb")

# Step 1: step2 first (on purpose)
try:
    con.execute(open("step2_orders_by_store.sql").read())
except Exception as e:
    print(f"Expected error: {type(e).__name__}: {e}")

# Step 2: now step1, and step2 again
con.execute(open("step1_create_stores.sql").read())
con.execute(open("step2_orders_by_store.sql").read())
print("Second attempt, in the right order:")
print(con.sql("SELECT * FROM orders_by_store ORDER BY store_id"))

Exercise 3 — Argue it in your own words. Using this lesson's recipe-book analogy, explain in 2-3 sentences what it means, in concrete SQL-and-files terms, for "the knowledge to live in a single head" versus "living in a dbt project."

Summary and next step

In this lesson you installed the promise of the whole guide — from loose SQL to a software project — and felt, with a real, executed example, the concrete problem that motivates everything that follows: two SQL scripts with a real dependency between them, and nothing in the system guaranteeing the right order except the memory of whoever runs them. You saw the full map of the ecosystem's 8 guides up to this point, the map of this guide's 8 modules, and the 8 lessons of this specific module.

Before moving on you should be able to: explain, in your own words, what problem dbt solves that DuckDB alone doesn't; and roughly place which module of this guide answers which kind of question.

Lesson 2 answers who does this work on a real team: what, exactly, is an analytics engineer, and how does it differ from a data engineer and a data analyst?

Resources

• dbt Developer Hub — "Quickstart for dbt Core using DuckDB," the official guide this whole module is based on. docs.getdbt.com/guides/duckdb. In English.
• DuckDB — "The Modern Data Stack in a Box," dbt-duckdb's thesis: a complete analytical stack running locally, with no infrastructure and no cloud account. duckdb.org/2022/10/12/modern-data-stack-in-a-box.html. In English.
• Fivetran — official announcement of the merger with dbt Labs, completed on June 1, 2026, context for the named-vendor risk mentioned in this lesson. fivetran.com/press/fivetran-dbt-labs-complete-merger-to-create-the-data-infrastructure-for-trusted-ai-agents. In English.
• DuckDB — official Python client documentation, used in this lesson's worked example to run the two SQL scripts. duckdb.org/docs/api/python/overview. In English.