Practice: ELT transformations with dbt
Generated by Claude through GigAI 0.1.11.10 on 2026-10-11.
Main tool: dbt · dbt is the tool the lesson is about: it turns plain select statements into an ordered, tested, documented transformation graph and runs entirely on a laptop against a local warehouse file. · Alternatives: SQLMesh (plans and virtual environments with built-in column-level lineage, smaller ecosystem), Dataform (SQL transformation tied to BigQuery only), Apache Spark or Databricks Lakeflow pipelines (code-first transformation outside or beside the warehouse, heavier to run), and hand-written SQL scheduled by Airflow (full control but no built-in dependency graph, tests or docs).
Every step links into the tool's own documentation; links were checked on 2026-10-11 · version 1
Setup (one time)
1. Read the self-hosted installations section of the quickstarts and pick the local path that needs no cloud account, using DuckDB as the warehouse.
The quickstarts split into hosted platform and self-hosted routes, and only the self-hosted route runs free on a laptop. Choosing it first keeps every later step local.
Explained in: dbt Quickstarts | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: You can name the self-hosted quickstart you will follow and the local warehouse it uses.
2. Create a Python virtual environment and install dbt together with the adapter for your local warehouse, DuckDB being the simplest choice.
dbt talks to each warehouse through an adapter, so the adapter decides which SQL dialect and connection settings you use. A virtual environment keeps the install isolated and repeatable.
Explained in: Supported data platforms | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: The dbt version command prints the installed dbt version and lists your adapter as a plugin.
3. Check the requirements list against your laptop and adapter before going further.
Not every adapter and feature is supported on every engine version, and finding a gap now is cheaper than finding it mid-project.
Explained in: Supported features | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: You have confirmed your operating system and adapter appear as supported, or have noted which features to avoid.
4. Look up the current versions and write the exact dbt and adapter versions you installed into a requirements file in your project folder.
A pinned version is what lets a teammate or a CI job rebuild the same environment later. It also keeps you off any known-bad release.
Explained in: dbt-releases | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: A requirements file in the project folder lists exact versions that match the dbt version output.
Steps
1. Run the starter project end to end: initialise a project, point its profile at a local DuckDB file, and build the example models.
A first successful run proves the install, the connection profile and the adapter all work together. It also shows you the log output you will read every day.
Explained in: SQL models | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: The run finishes with every example model reported as successful and the tables are visible in the local database file.
2. Load two or three raw CSV files into the local warehouse untransformed, then write one model that is only a select statement over them.
This is ELT in miniature: the data lands raw first and the transformation happens inside the warehouse in SQL. You see that dbt owns only the transform step and wraps your select in the create statement for you.
Explained in: SQL models | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: The raw tables exist unchanged in the warehouse and a new relation built from your select statement sits beside them.
3. Write one staging model per raw table, declare the raw tables as sources, and connect a downstream model to the staging models with the ref function instead of hard-coded table names.
Sources and ref are how dbt learns the dependency graph, which is what lets it build models in the right order and across environments. Hard-coded names break that graph silently.
Explained in: SQL models | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: Running the downstream model with its parents selected builds the staging models first, and the compiled SQL shows ref replaced by real relation names.
4. Configure materializations: set staging models to views in the project file, override one mart model to a table inside the model file, and rebuild.
Materialization decides whether a model is a view, a table or something incremental, which drives cost and freshness. Knowing the two places a config can live, and which one wins, avoids confusing overrides.
Explained in: SQL models | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: The warehouse shows staging relations as views and the mart as a table, matching what the run log reports.
5. Create staging, intermediate and marts folders and move your models into them following the structure guide, adding one intermediate model that joins two staging models.
The three layers give every model an obvious home: staging cleans one source each, intermediate combines, marts serve consumers. A shared structure is what lets a new engineer navigate a client project on day one.
Explained in: How we structure our dbt projects | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: The project builds cleanly from the new folders and each model name and location follows the guide's layer conventions.
6. Write generic data tests in a properties file: unique and not null on each primary key, accepted values on a status column, and a relationships test between a fact and a dimension.
Generic tests are reusable assertions declared in configuration, and these four catch most real data breakage. They are the cheapest quality gate you can add.
Explained in: Add data tests to your DAG | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: The test command lists each declared test with a pass result.
7. Write one singular data test as a SQL file that returns the rows violating a business rule, such as an order with a negative total.
Some rules do not fit a generic test, and a singular test is just a query that should return zero rows. Writing one makes the pass and fail mechanics concrete.
Explained in: Add data tests to your DAG | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: The singular test passes on clean data and fails after you insert a deliberately bad row into the raw data.
8. Run only the data tests for one folder using selection, without rebuilding any models.
On a real project you rarely run everything, so node selection is how you keep feedback fast. Separating test runs from build runs also clarifies what each command does.
Explained in: Add data tests to your DAG | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: The log shows only tests executing, limited to the folder you selected, with no models rebuilt.
9. Enable stored test failures, rerun the failing test from the bad row, then query the failures table and compare it before and after you fix the data.
Persisting the failing rows turns a red test into something you can inspect and hand to a data owner. Comparing runs shows whether a fix actually removed the bad records.
Explained in: Add data tests to your DAG | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: A failures table in the warehouse contains the bad row after the first run and is empty after the fix and rerun.
10. Package a dependency into the project: add the dbt utils package, install it, check its compatibility message, and use its unique combination of columns test on a model with a composite key.
Packages are how dbt code is shared and versioned across projects, and compatibility differs between engine versions. Using one packaged test shows the whole install and reference cycle.
Explained in: Supported features | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: The package installs without a compatibility error and the packaged test appears and passes in the test output.
11. Reproduce the whole project from nothing: delete the local database file and the target folder, recreate the virtual environment from the pinned requirements file, and run a single build that runs models and tests together.
A clean rebuild from pinned versions is exactly what a CI job does on every pull request. If it works from an empty folder on your laptop, it will work in automation.
Explained in: SQL models | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: One build command on a fresh environment and empty warehouse ends with all models and tests passing and no manual steps in between.
12. Configure a production-like layout modelled on the common architectures: the hosted dbt platform is a paid cloud service, so as the closest local substitute define separate dev and prod targets in your profile writing to different schemas, and build prod only from the pinned version.
Real deployments separate development from production and control which dbt version each environment runs. Mirroring that split locally teaches the promotion habit without any cloud cost.
Explained in: Release tracks in dbt platform | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: Building with the dev target and then the prod target produces the same models in two separate schemas, and prod is built only from the pinned environment.
13. Start the self-hosted dbt MCP server against the finished project, ask an AI client to list your models through it, and read the data retention section to learn what the server keeps.
A forward deployed engineer is often asked to expose a client's dbt project to AI agents, and must be able to state what data leaves the machine and how long it is kept. The self-hosted server lets you practise that on a laptop.
Explained in: dbt Model Context Protocol server | dbt Developer Hub (docs.getdbt.com), checked 2026-10-11
Done when: The client returns the model list from your local project and you can state in one sentence what the retention section says is stored.