Use dbt models, tests, and macros to compose reusable, version‑controlled SQL blocks that can be executed incrementally and validated automatically.
Step‑by‑step
1. Initialize a dbt project
```bash
dbt init my_project --adapter postgres
```
2. Define sources in models/src.yml to lock downstream dependencies:
```yaml
version: 2
sources:
- name: raw
tables:
- name: events
```
3. Create a staged model that normalizes raw events:
```sql
-- models/stg_events.sql
{{ config(materialized='view') }}
SELECT
event_id,
user_id,
TIMESTAMP_TRUNC(event_ts, HOUR) AS event_hour,
event_type
FROM {{ source('raw', 'events') }}
```
4. Build an aggregation model with incremental materialization and a custom macro for dynamic grain:
```sql
-- macros/agg_grain.sql
{% macro agg_grain(columns) %}
{{ columns | join(', ') }}
{% endmacro %}
```
```sql
-- models/fct_event_counts.sql
{{ config(materialized='incremental', unique_key='event_hour') }}
SELECT
{{ agg_grain(['event_hour', 'event_type']) }},
COUNT() AS cnt
FROM {{ ref('stg_events') }}
GROUP BY {{ agg_grain(['event_hour', 'event_type']) }}
{% if is_incremental() %}
HAVING event_hour > (SELECT MAX(event_hour) FROM {{ this }})
{% endif %}
```
5. Add schema tests to enforce non‑null keys and row‑count thresholds:
```yaml
version: 2
models:
- name: fct_event_counts
tests:
- not_null:
column_name: event_hour
- accepted_range:
column_name: cnt
min: 0
```
6. Run and document
```bash
dbt run --models fct_event_counts
dbt docs generate && dbt docs serve
```
Quick comparison
| Feature | dbt core | dbt Cloud |
|--------|----------|-----------|
| Scheduler | external (Airflow, cron) | built‑in |
| IDE | VS Code, dbt‑ls | web UI |
| CI/CD | GitHub Actions | integrated runs |
Gotcha: When a source table adds a new column, incremental models that use SELECT will silently ignore it; update the model’s column list or use {{ config(full_refresh=True) }} for the next run to avoid schema drift.