dbt (Data Build Tool)

dbt adalah tool yang mentransformasi data di warehouse menggunakan SQL. dbt menerapkan software engineering best practices (version control, testing, documentation) ke data transformation.

Mengapa dbt?

• SQL-first — Tidak perlu belajar bahasa baru, cukup SQL
• Version controlled — Model disimpan di Git, ada PR review
• Tested — Data quality tests built-in
• Documented — Auto-generate documentation dari model
• Dependency management — dbt tahu urutan eksekusi model

Struktur Project dbt

my_dbt_project/
├── dbt_project.yml          # Config utama
├── models/
│   ├── staging/             # Layer 1: clean raw data
│   │   ├── stg_orders.sql
│   │   ├── stg_users.sql
│   │   └── _staging.yml     # Schema & tests
│   ├── intermediate/        # Layer 2: business logic
│   │   └── int_order_enriched.sql
│   └── marts/               # Layer 3: final tables
│       ├── revenue_daily.sql
│       └── _marts.yml
├── tests/                   # Custom data tests
│   └── assert_no_negative_revenue.sql
├── macros/                  # Reusable SQL snippets
│   └── cents_to_rupiah.sql
└── seeds/                   # Static CSV data
    └── country_codes.csv

Model dbt

-- models/staging/stg_orders.sql
-- Setiap file .sql = satu model = satu tabel/view di warehouse

WITH source AS (
    SELECT * FROM {{ source('raw', 'orders') }}
),

cleaned AS (
    SELECT
        id AS order_id,
        user_id,
        CAST(amount AS DECIMAL(12,2)) AS amount,
        LOWER(status) AS status,
        created_at::TIMESTAMP AS ordered_at
    FROM source
    WHERE amount > 0
      AND status IS NOT NULL
)

SELECT * FROM cleaned

-- models/marts/revenue_daily.sql
-- ref() membuat dependency — dbt tahu urutan build
SELECT
    DATE_TRUNC('day', o.ordered_at) AS date,
    u.country,
    u.plan,
    COUNT(*) AS order_count,
    SUM(o.amount) AS total_revenue,
    AVG(o.amount) AS avg_order_value
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_users') }} u ON o.user_id = u.user_id
GROUP BY 1, 2, 3

Testing di dbt

# models/staging/_staging.yml
version: 2

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: amount
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'paid', 'shipped', 'cancelled']

# Jalankan tests
# dbt test --select stg_orders
# dbt test  (semua tests)

dbt Commands

# Build semua models
dbt run

# Build model tertentu + downstream
dbt run --select stg_orders+

# Test
dbt test

# Generate documentation
dbt docs generate
dbt docs serve  # Buka browser → dependency graph!