dbt Fundamentals

dbt: SQL-Based Transformations

What dbt Does

dbt transforms data in the warehouse using SQL. It brings software engineering practices: version control, testing, documentation, and modular code. Write SELECT statements; dbt handles the rest.

Project Structure

models/staging/ (clean raw data), models/intermediate/ (business logic), models/marts/ (analytics-ready tables). Each model is a .sql file with a SELECT statement.

Key Features

ref() for dependencies, source() for raw data, tests (unique, not_null, accepted_values), documentation auto-generation, lineage tracking.

Code Example

-- models/staging/stg_orders.sql
SELECT
id AS order_id,
customer_id,
created_at AS order_date,
amount / 100.0 AS amount_dollars,
UPPER(status) AS status
FROM {{ source('raw', 'orders') }}
WHERE deleted_at IS NULL

-- models/marts/fct_orders.sql
SELECT
o.order_id,
o.order_date,
c.customer_name,
o.amount_dollars
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_customers') }} c USING (customer_id)

-- schema.yml tests:
-- tests: [unique, not_null] on order_id
-- tests: [accepted_values: ['completed', 'cancelled']] on status

Best Practices

• Use staging, intermediate, marts layers
• Test all models
• Document everything
• Use ref() for dependencies

Interview Tips

• Explain dbt project structure
• How do you test models?
• Describe data modeling with dbt