A Flipkart analyst opens a folder called `sql_scripts_final_v3`. Inside are forty `.sql` files, no version history, no tests, and nobody left on the team who remembers which one actually feeds the revenue dashboard. Someone changes a column name in the source table. Three dashboards break, silently, and nobody finds out until finance asks why yesterday's GMV number is zero. This is what SQL transformation looked like before **dbt** (data build tool). Every analytics team writes SQL to turn raw tables into clean, business-ready tables. The problem was never the SQL itself - it was that nobody applied basic software engineering discipline to it. No version control, no automated tests, no documentation, no way to see what depends on what. dbt does not replace SQL. It wraps a thin layer of discipline around SQL you already know how to write, so your transformation layer behaves like software instead of a pile of scripts. > 📌 **Remember:** dbt only handles the T in ELT. Extraction and loading (Airflow, Fivetran, Debezium) happen before dbt ever runs. dbt takes data that already landed in your warehouse and transforms it into clean, tested, documented tables. ### How dbt fits into your data engineering architecture Here is where dbt sits relative to everything else you build in this roadmap - it is the layer between raw warehouse tables and everyone who actually consumes the data. API / Database / Kafka | v Ingestion (Airflow / Fivetran / Debezium) | v Raw Warehouse Tables | v dbt | +-----+-----+ | | | v v v Staging Inter- Marts mediate | v BI / Analytics / ML Features dbt never talks to your source systems directly. It only ever reads tables that ingestion has already landed in the warehouse, and only ever writes new tables and views back into that same warehouse. ### What a dbt model actually is A **dbt model** is usually a `.sql` file that defines the query for a dataset. In most cases the file ultimately produces a `SELECT` statement describing what the final data should look like - though a model can also contain Jinja logic, CTEs, and configuration blocks around that query. You generally do not write the full `CREATE TABLE` or `INSERT INTO` workflow yourself - dbt handles how the result gets materialized based on the model's configuration. ```sql -- models/marts/fct_daily_revenue.sql select order_date, restaurant_id, sum(order_amount) as daily_revenue, count(distinct order_id) as order_count from {{ ref('stg_orders') }} where order_status = 'delivered' group by 1, 2 ``` > **Note:** `{{ ref('stg_orders') }}` is Jinja templating, dbt's way of letting you reference another model by name instead of a hardcoded table path. dbt resolves this at compile time into the actual schema-qualified table name for whichever environment you are running in - dev, staging, or production. ### Why growing data teams adopt dbt Picture a large marketplace data team like the one behind a Swiggy-scale platform - dozens of analysts, hundreds of transformation models, new hires joining every quarter. At that scale, dbt turns SQL into something reviewable in a pull request, testable before it ships, and traceable when something breaks. A junior analyst can open the project, see exactly which tables feed a broken dashboard, and know precisely which SQL file to fix.
### Installing dbt and connecting to a warehouse ```bash ## Install dbt with the adapter for your warehouse - postgres shown here pip install dbt-postgres ## Confirm the install and see which adapters are available dbt --version ## Initialize a new project - creates the folder structure below dbt init swiggy_analytics ``` Expected output: ```text Running with dbt=1.8.0 Your new dbt project "swiggy_analytics" was created! ``` > 🔴 **Common Mistake:** running `dbt init` inside an existing Git repository without checking `.gitignore` first commits `profiles.yml`-style credentials if you keep them in the project folder. dbt stores connection credentials in `~/.dbt/profiles.yml`, outside the project directory, specifically to prevent this - never move that file into the repo. ### The dbt project structure ```text swiggy_analytics/ models/ staging/ intermediate/ marts/ tests/ seeds/ macros/ dbt_project.yml ``` * `models/` holds every SQL transformation, organized into staging, intermediate, and marts layers * `tests/` holds custom SQL tests that go beyond the built-in schema tests * `seeds/` holds small static CSV files (a country-code lookup, a discount-tier mapping) loaded directly into the warehouse * `macros/` holds reusable Jinja snippets, dbt's version of a shared function library * `dbt_project.yml` configures the project - materialization defaults, folder-level settings, variables * `profiles.yml` (kept outside the repo, in `~/.dbt/`) holds the actual warehouse connection and credentials > 💡 **Practice:** run `dbt init` yourself, then open `dbt_project.yml` and find the `models:` config block. Change the default materialization for the `staging` folder to `view` and the `marts` folder to `table`, then explain in one sentence why marts benefit from being tables while staging models usually do not.
A widely used and highly maintainable dbt structure separates models into staging, intermediate, and marts layers. Other conventions exist too - some teams organize by business domain instead - but this three-layer pattern is the one worth learning first, and skipping structure entirely is a common reason a dbt project turns into the same unmaintainable mess it was supposed to fix. Raw source tables | v +-------------+ one model per source table | staging | light renaming, type casting only +-------------+ | v +-------------+ joins, business logic lives here | intermediate| +-------------+ | v +-------------+ wide, business-domain tables | marts | what analysts actually query +-------------+ ### Staging models - one per source table, nothing clever ```sql -- models/staging/stg_orders.sql select order_id, customer_id, restaurant_id, cast(order_amount as numeric) as order_amount, lower(order_status) as order_status, order_created_at::timestamp as order_created_at from {{ source('swiggy_raw', 'orders') }} ``` > **Note:** `{{ source('swiggy_raw', 'orders') }}` points to a raw table declared in a `sources.yml` file, not a dbt model. Declaring sources explicitly means dbt can track freshness on the raw table and show it in the lineage graph, even though dbt never wrote that table itself. ### Source freshness and source tests - catching problems before they reach your models A dbt run can succeed perfectly while the source data itself is stale. Your models might run cleanly at 8 AM, but if the upstream ingestion pipeline failed at midnight, you are transforming yesterday's data without any error telling you so. ```yaml # models/staging/sources.yml version: 2 sources: - name: swiggy_raw tables: - name: orders loaded_at_field: _loaded_at freshness: warn_after: {count: 6, period: hour} error_after: {count: 12, period: hour} columns: - name: order_id tests: - not_null ``` ```bash ## Check whether raw source data is arriving on schedule dbt source freshness ``` Source tests and model tests answer two different questions. A **source test** asks "did bad data enter the pipeline in the first place?" A **model test** asks "did our transformation produce bad data?" Both matter - a clean transformation of already-broken source data is still broken output. > 📌 **Convention:** keep staging models as close to the source as possible - usually renaming, type casting, and basic standardization like `lower(order_status)`, rather than business-specific rules. Put joins, metric definitions, and domain logic in intermediate or mart models instead. This is a convention that keeps the layering trustworthy, not a hard rule against every kind of cleanup in staging. ### Intermediate models - where joins and business logic live ```sql -- models/intermediate/int_orders_enriched.sql select o.order_id, o.order_amount, o.order_created_at, r.restaurant_name, r.city as restaurant_city, c.customer_tier from {{ ref('stg_orders') }} o left join {{ ref('stg_restaurants') }} r on o.restaurant_id = r.restaurant_id left join {{ ref('stg_customers') }} c on o.customer_id = c.customer_id ``` ### Marts - what the business actually queries ```sql -- models/marts/fct_daily_orders.sql select date_trunc('day', order_created_at) as order_date, restaurant_city, count(*) as total_orders, sum(order_amount) as total_revenue from {{ ref('int_orders_enriched') }} group by 1, 2 ``` > 💡 **Practice:** using the three models above as a pattern, write your own `stg_`, `int_`, and mart model chain for a Zerodha-style trades dataset (`trades`, `instruments`, `customers` as raw sources). Confirm with `dbt run` that all three build without errors, in dependency order.
A **materialization** is dbt's configuration for how a model gets physically built in the warehouse. Same `SELECT` statement, four different ways to store the result. | Materialization | Rebuild Behavior | Best For | |:---|:---|:---| | `view` | Recompiled as a SQL view, no data stored | Staging models, low query frequency | | `table` | Full table rebuild every run | Marts queried often by dashboards | | `incremental` | Only new/changed rows processed | Large fact tables - events, orders, trades | | `ephemeral` | Inlined as a CTE, never queried directly | Small reusable logic, not a real table | ```sql -- Setting a materialization in the model file itself {{ config(materialized='table') }} select ... ``` > 📌 **Engineering Decision:** start with the simplest materialization that meets your needs - often `view` for lightweight transformations. Move to `table` when repeated query cost or dashboard performance becomes a real problem. Use `incremental` when rebuilding the full dataset is no longer practical. The right default can vary by warehouse - some warehouses make deeply nested views expensive, some teams use tables for isolation - so treat this as a starting heuristic, not a universal rule. Jumping straight to `incremental` on a small model adds real complexity - unique keys, lookback windows, `--full-refresh` handling - for a performance problem that does not exist yet. ### Incremental models - the pattern that saves real compute cost Rebuilding a 2-year, billion-row events table from scratch every night is slow and expensive. An **incremental model** processes only the rows that are new or changed since the last run, using a merge or insert-only strategy under the hood depending on the warehouse. ```sql -- models/marts/fct_transaction_events.sql {{ config( materialized='incremental', unique_key='transaction_id' ) }} select transaction_id, customer_id, amount, transaction_ts from {{ ref('stg_transactions') }} {% if is_incremental() %} -- reprocess a 3-day lookback window, not just rows after the max timestamp where transaction_ts >= ( select coalesce(max(transaction_ts) - interval '3 days', '1900-01-01'::timestamp) from {{ this }} ) {% endif %} ``` > **Note:** `{{ this }}` refers to the model's own already-built table in the warehouse. On the very first run, `is_incremental()` is false and the full `SELECT` runs unfiltered. On every run after that, dbt wraps your query with merge logic and the `where` clause limits which rows get reprocessed. ### Late-arriving data - the trap hidden inside a plain max(timestamp) filter An event's timestamp is not always the same moment it arrives in your warehouse. A payment recorded at 10:00 might not land in your source table until 10:30, because an upstream system was briefly unavailable. A naive filter like `where transaction_ts > max(transaction_ts)` can silently miss that record forever, since by the time it arrives, the watermark has already moved past it. > ⚠️ **Security:** `max(timestamp)` incremental filters are easy to understand but can miss late-arriving records permanently. Production pipelines commonly guard against this with a lookback window (as shown above), an ingestion timestamp instead of an event timestamp, or a proper CDC/watermark strategy - the same watermark pattern covered in the ETL Pipelines with Airflow module. > 🔴 **Common Mistake:** an incremental model without an appropriate `unique_key` may fail to merge updates into existing records, depending on the incremental strategy and warehouse - this can let duplicate or stale rows accumulate. A `unique_key` should represent the actual grain of one row in the model, and you should test that the resulting dataset really does satisfy that uniqueness assumption rather than assuming it. > 💡 **Practice:** run `dbt run --full-refresh` on your incremental model, then run `dbt run` again without the flag. Compare row counts before and after a second identical run - if the count changed on the second run, your `unique_key` or filter logic has a bug.
A model that runs without error is not the same as a model that is correct. dbt's testing layer is what turns a transformation pipeline from "it compiled" into "it is trustworthy." ### Schema tests - the four built-in checks ```yaml # models/marts/schema.yml version: 2 models: - name: fct_daily_orders columns: - name: order_date tests: - not_null - name: restaurant_city tests: - not_null - name: total_revenue tests: - not_null ``` * `not_null` - fails if any row has a null in that column * `unique` - fails if any value repeats where it should not (an order_id, a primary key) * `accepted_values` - fails if a column contains a value outside a defined list, like an `order_status` that isn't `delivered`, `cancelled`, or `pending` * `relationships` - fails if a foreign key value does not exist in the referenced table, catching broken joins before they hit a mart ```bash ## Run every test in the project dbt test ## Run tests for one model only dbt test --select fct_daily_orders ``` Expected output: ```text Completed successfully Done. PASS=12 WARN=0 ERROR=0 SKIP=0 TOTAL=12 ``` ### Custom data tests - SQL that returns rows when something is wrong ```sql -- tests/assert_positive_revenue.sql -- This test PASSES when it returns zero rows select order_date, restaurant_city, total_revenue from {{ ref('fct_daily_orders') }} where total_revenue < 0 ``` > **Note:** a custom dbt test is just a `SELECT` that should return nothing if the data is healthy. dbt runs it and fails the test if even one row comes back - the returned rows are exactly the offending records, which makes debugging fast.
A teammate reports the `fct_daily_orders` mart is showing roughly double the revenue for the past three days, but only for that window - older data looks correct. `dbt run` completes with no errors. Here is how to actually find the bug. ```sql -- Step 1 - run this directly in your warehouse to confirm the mart has duplicates select order_date, restaurant_city, count(*) as row_count from fct_daily_orders group by 1, 2 having count(*) > 1; ``` ```text 2026-08-10 | Bengaluru | 2 2026-08-11 | Bengaluru | 2 ``` ```bash ## Step 2 - check whether the intermediate model already has duplicate order_ids dbt test --select int_orders_enriched ``` ```text FAIL 14 unique_order_id_int_orders_enriched ``` The failing test in Step 2 tells you the duplication is introduced in `int_orders_enriched`, not in the final mart - almost certainly a join that is fanning out rows, most likely a `left join` to `stg_restaurants` matching more than one row per `restaurant_id`. On PostgreSQL, the fix is deduplicating the join key with a windowed subquery before joining: ```sql select * from ( select *, row_number() over ( partition by restaurant_id order by updated_at desc ) as rn from {{ ref('stg_restaurants') }} ) ranked where rn = 1 ``` > **Note:** some warehouses (Snowflake, Databricks) support a `QUALIFY` clause that does this in one line. PostgreSQL does not, so the windowed subquery above is the portable version. > 📌 **Remember:** when a mart looks wrong, always test the layer below it first. A bug almost never originates where it is finally visible - tracing it upstream through staging, intermediate, then mart is faster than staring at the mart's own SQL.
A Flipkart analyst opens a folder called sqlscriptsfinalv3. Inside are forty .sql files, no version history, no tests, a...
Installing dbt and connecting to a warehouse Expected output: > 🔴 Common Mistake: running dbt init inside an existing G...
A widely used and highly maintainable dbt structure separates models into staging, intermediate, and marts layers. Other...
A materialization is dbt's configuration for how a model gets physically built in the warehouse. Same SELECT statement, ...
A model that runs without error is not the same as a model that is correct. dbt's testing layer is what turns a transfor...
A teammate reports the fctdailyorders mart is showing roughly double the revenue for the past three days, but only for t...
Describing models and columns Expected output: > 💡 Tip: the lineage graph is the fastest way to answer "what breaks if ...
dbt supports custom macros for reusable Jinja logic, community packages like dbtutils for common patterns (surrogate key...
Everything covered so far runs locally, but the real value shows up once dbt sits inside a team's CI/CD workflow. Develo...
Initialize a dbt project connected to your PostgreSQL instance and confirm the connection. Expected output: Declare your...
Command / Concept What It Does dbt run Builds all models in dependency order dbt test Runs all schema and custom tests d...
Using table materialization for a model with millions of rows rebuilds the entire dataset on every single run, which bec...
Aligns directly with DevOps, Site Reliability (SRE), and Platform Engineering job descriptions.