Week 10: dbt Transformations

Introduction to dbt Core

dbt Setup for Azure PostgreSQL

SQL and Jinja Templating

Materializations & Layers

dbt Tests

Docs & Extras

Practice

Assignment: Borough Stats

Gotchas & Pitfalls

Slides (PDF)

Career relevance: Week 10 in the NL data job market

Glossary

History of Analytics Engineering

Going Further: Optional Deep Dives

History of Analytics Engineering

This page is optional. Nothing here is required for Week 10's learning goals or the assignment. It covers the history behind the tools and the role you practice this week. Read it in one sitting, or come back when you are curious about why the workflow you learned became the industry default.

dbt did not appear out of nowhere. It was a reaction to two decades of changes in how companies store and transform data: the rise of the cloud data warehouse, the shift from ETL to ELT, and the creation of a new job title called the analytics engineer. This chapter tells that story and explains why the workflow you learn this week became the industry default.

The ETL era and its limits

Background for Chapter 1: Introduction to dbt Core

For most of the 2000s, moving data into a warehouse followed a pattern called ETL (Extract, Transform, Load):

1. Extract raw data from source systems (databases, files, APIs).
2. Transform the data on a separate server, applying business logic, cleaning, joining, and aggregating.
3. Load the transformed data into the warehouse.

The transform step happened outside the warehouse because warehouses were expensive, slow, and had limited storage. You did not want to waste warehouse capacity on raw files or intermediate calculations. Specialized ETL tools did the heavy lifting: Informatica PowerCenter (1996), IBM DataStage (1997), and Microsoft SSIS (2005). These tools had drag-and-drop interfaces where you wired together boxes representing sources, transformations, and destinations.

The problem with ETL was that the transformations were locked inside proprietary tools. You could not put a DataStage pipeline into git. You could not write a test for an Informatica mapping. Code review meant sitting next to someone and clicking through a visual editor. When something broke at 3 AM, you opened a vendor GUI to debug it.

Meanwhile, the people writing the business logic, mostly analysts, did not want to learn Informatica. They wanted to write SQL. So the transformations that mattered most to the business lived outside the ETL tool entirely, as Excel workbooks, hand-written SQL scripts, and stored procedures that nobody tested.

The cloud data warehouse shift

Background for Chapter 2: dbt Setup for Azure PostgreSQL

Between 2011 and 2014, three products changed the economics of data warehousing:

The combined effect: storage became cheap, compute became elastic, and running a warehouse no longer required a dedicated DBA team. A small startup could load terabytes of raw data into Snowflake on a Tuesday and query it on a Wednesday. None of that was possible with on-premise Teradata or Oracle Exadata.

Once warehouses got cheap, the old "transform before you load" rule stopped making sense. If storage was nearly free, why not load everything raw and transform it inside the warehouse where the data already lived? This inverted the pipeline into ELT (Extract, Load, Transform):

ETL:  source → extract → transform (on ETL server) → load → warehouse → dashboards
ELT:  source → extract → load (raw) → warehouse → transform (in SQL) → dashboards

The transformation step moved into the warehouse itself, written in plain SQL. But there was no tool that knew how to manage a large collection of SQL transformations. That is the gap dbt filled.

The ELT gap and the birth of dbt

Background for Chapter 1: Introduction to dbt Core

In 2016, a small consulting firm in Philadelphia called Fishtown Analytics was helping clients build analytics pipelines on top of Redshift. The founders, Tristan Handy and Drew Banin, noticed the same pattern on every project: dozens of SQL files held together with shell scripts, no tests, no documentation, no dependency tracking. When a client asked "how was this number calculated?" the honest answer was usually "let me dig through the scripts and get back to you."

They wrote an internal tool to manage SQL transformations like software: version them in git, declare dependencies between them, compile templates, run them in the correct order, and test the output. They called it dbt (data build tool) and open-sourced it in 2016 under the Apache 2.0 license. The entire project was a few thousand lines of Python at first. Early adopters were other consultancies and data teams who recognized their own pain.

Key milestones:

By 2026, dbt had become the default way to build analytics pipelines at companies using Snowflake, BigQuery, Redshift, Databricks, and (as in this course) PostgreSQL. With over 1 billion cumulative downloads and a thriving community of over 100,000 members, dbt has moved from a niche consulting tool to a core pillar of the modern data stack. Most job listings for data roles at modern companies now mention dbt by name.

Compilation and the Jinja choice

Background for Chapter 3: SQL and Jinja Templating and Chapter 4: Materializations & Layers

One of dbt's early design decisions was to use Jinja, a Python templating engine originally built for web frameworks like Flask, as the templating language on top of SQL. This was not obvious at the time. Other tools tried dialects, preprocessors, or custom languages. The dbt team picked Jinja because Python developers already knew it, it was battle-tested, and it had a mature ecosystem.

The trade-off is that Jinja is not SQL-aware. It treats your SQL as a text string and substitutes variables into it. This is why dbt compiles your models in two phases: first Jinja renders the template into plain SQL, then dbt sends that SQL to the database. You can inspect the compiled output in target/compiled/ after any dbt run or dbt compile, which is one of the best debugging techniques you will use this week.

The Jinja-as-templating approach turned out to be a good bet. It let dbt stay a thin layer over SQL instead of inventing a new language, and it meant that anything you could express in Python-style control flow (loops, conditionals, macros) was available inside your models. Competing tools like SQLMesh (2022) and Dataform (acquired by Google, 2020) have since tried alternative approaches, but Jinja-on-SQL remains the dominant pattern.

Testing in analytics

Background for Chapter 5: dbt Tests

Testing analytics code was almost nonexistent before dbt. Software engineering had JUnit and pytest for decades; data teams had nothing comparable. Separate Python libraries like Great Expectations (2017) and AWS Labs' Deequ (2018) existed, but they required a separate deploy, so adoption stayed low. dbt's insight was to ship four generic tests (not_null, unique, accepted_values, relationships) declarable in YAML next to the model: that lowered the barrier enough that adding a test became the default, not the exception.

OBT and the dbt-era modeling shift

Background for Chapter 4: Materializations & Layers

The Kimball/Inmon debate that shaped warehouse modeling in the 1990s is covered in Week 9's data modeling chapter. What changed in the dbt era is the working default: star schemas assumed row-oriented storage and expensive disk, so narrow fact tables joined to dimensions at query time won. Columnar warehouses (BigQuery, Snowflake, Redshift, Databricks, DuckDB) read only the columns a query touches, so a 40-column denormalized mart costs the same as a 10-column star fact for a query using five columns. Combined with ref() making rebuilds disposable, One Big Table (OBT): a wide denormalized mart with dimension attributes pre-joined in: became the pragmatic default. Most dbt projects in the wild look like the fct_trips you built this week: zone attributes baked in, no separate dim_zones to join at read time. Pure star schemas remain the right choice when a BI tool requires them, a dimension is shared by many fact tables, or conformed dimensions cross domains at enterprise scale.

dbt Core vs dbt Cloud

Background for Chapter 1: Introduction to dbt Core and Chapter 2: dbt Setup for Azure PostgreSQL

dbt is distributed in two forms. Both run the same transformation engine, but they differ in how they are managed and paid for.

dbt Core is the open-source command-line tool. It is free forever under the Apache 2.0 license. You install it with pip, configure a profiles.yml, and run dbt run from your terminal or a CI job. Every feature this course uses (models, sources, refs, tests, docs, macros, incremental models) lives in dbt Core. Nothing about the SQL transformations themselves is locked behind a paid tier.

dbt Cloud is the commercial hosted product from dbt Labs. It launched in 2020 and bundles dbt Core with a browser-based IDE, a managed scheduler, a hosted docs site, single sign-on, audit logs, and more recently the Semantic Layer and dbt Mesh. Pricing is tiered by number of developers and features.

Both versions are maintained by dbt Labs. dbt Core is developed in the open on GitHub and accepts community contributions. dbt Cloud is the revenue model that funds the open source work.

Why this course uses dbt Core

Every example in this week uses dbt Core. The reasons are practical:

1. You learn the primitives. dbt Cloud hides details behind a UI. Running dbt debug from a terminal and editing profiles.yml by hand teaches you how the tool actually works, which transfers to any environment.
2. It fits the orchestrator. In Week 11, you will trigger dbt runs from an orchestrator (Airflow or similar). That is exactly how most production dbt Core deployments work at real companies.
3. It mirrors real engineering jobs. Many companies that could afford dbt Cloud still run dbt Core because they want tighter integration with their existing CI, secrets management, and infrastructure. Learning Core first makes Cloud trivial to pick up later. The opposite is not true.
4. No vendor lock-in. Your models, tests, and docs are plain files in a git repo. You can switch between Core and Cloud (or to any other orchestration approach) without changing a single line of SQL.

When is dbt Cloud the right call?

dbt Cloud makes sense when:

• Your team has analysts who want to write SQL in a browser IDE without installing Python.
• You do not have the DevOps capacity to run your own orchestrator.
• You need enterprise governance (SSO, audit logs, RBAC) out of the box.
• You want the Semantic Layer or dbt Mesh for cross-team collaboration.

For this course and for most entry-level analytics engineering roles you are likely to encounter, dbt Core is the more educational and more common choice.

The analytics engineer as a job title

Background for the whole week

The workflow you practice this week (version-controlled SQL, tests, documentation, modular models, CI/CD) now has a name: analytics engineering. The term was coined in a 2019 dbt Labs blog post titled What is an Analytics Engineer? and it stuck because it filled a real gap.

Before dbt, companies hired either data engineers (people who moved data with Python and Scala) or data analysts (people who wrote SQL and built dashboards). The space between these two roles (people who wrote SQL like software engineers, maintained a whole warehouse of models, and cared about tests and documentation) had no name. Candidates who actually did that work described themselves with awkward titles like "analytics developer" or "SQL engineer" or "BI developer."

Once the name existed, job listings followed. By 2022, LinkedIn showed tens of thousands of "Analytics Engineer" job postings worldwide. Salary benchmarks from Built In put the role between data analyst and data engineer, reflecting its position in the middle of the stack.

The role definition in most listings overlaps closely with what you do this week:

• Own the models/ folder of a dbt project.
• Write staging, intermediate, and mart models for specific business processes.
• Maintain tests and documentation for every model.
• Review PRs from other analytics engineers and analysts.
• Work with data engineers upstream (on ingestion) and with analysts and BI teams downstream (on dashboards).

This course is not a full analytics engineering program. But by the end of Week 10 you will have built a realistic sample of what the role looks like day to day: a dbt project on a real cloud warehouse, with tests, docs, and a mart that powers a dashboard in Week 12.

2025-2026: Fusion engine and the consolidation wave

Background for Going Further: Optional Deep Dives

Two events in 2025 reshaped what "dbt" means long-term.

The dbt Fusion engine (May 2025). A complete rewrite of the execution engine in Rust, based on technology from the SDF Labs acquisition. Fusion is dramatically faster than the Python engine and, more importantly, SQL-aware: it parses and understands the SQL itself instead of treating models as Jinja-to-text templates, unlocking column-level lineage, compile-time validation, and cross-database translation. Released under the Elastic License 2.0 rather than Apache 2.0, with some advanced features reserved for dbt Cloud paid tiers, it sparked community debate about what "open source dbt" means long-term. dbt Labs has publicly committed to keeping the classic Python engine maintained and the core primitives (models, sources, refs, tests, docs) free and open.

The Fivetran + dbt Labs + SQLMesh consolidation (late 2025-2026). In September 2025, Fivetran acquired Tobiko Data, the company behind SQLMesh. On October 13, 2025, Fivetran and dbt Labs announced an all-stock merger closing in mid-to-late 2026, creating a ~$600M ARR combined company with Fivetran CEO George Fraser at the helm and dbt Labs founder Tristan Handy as co-founder and president. In March 2026, Fivetran contributed SQLMesh to the Linux Foundation: an unusual move that signals both projects stay open and community-governed rather than becoming leverage for Fivetran's commercial tiers.