dbt 5 — Jinja, macros & packages

Từ SQL tĩnh đến SQL biết lập trình

Ở bài về models & materializations ta đã viết những model SQL "phẳng" — mỗi model là một câu SELECT gọn gàng. Nhưng SQL thuần có một giới hạn khó chịu: nó không có vòng lặp, không có hàm, không có biến thực sự. Muốn pivot mười phương thức thanh toán thành mười cột, bạn phải gõ tay mười khối CASE WHEN. Muốn dùng lại một công thức chuẩn hoá tiền tệ ở hai chục model, bạn phải copy-paste hai chục lần — và khi công thức đổi, bạn sửa hai chục chỗ.

dbt giải bài toán này bằng Jinja — một ngôn ngữ template (vay mượn từ hệ sinh thái Python) được nhúng ngay trong file SQL. Với Jinja, câu SQL của bạn trở thành một template động: bạn viết logic sinh SQL, dbt chạy logic đó rồi compile ra SQL thuần để gửi xuống warehouse. Điều quan trọng cần khắc cốt: warehouse không bao giờ nhìn thấy Jinja — nó chỉ nhận SQL đã biên dịch xong.

Quy ước: sandbox của chúng ta là PostgreSQL và dbt không thực sự chạy ở đây, nên toàn bộ đoạn mã dưới đây là minh hoạ cú pháp dbt 1.x.

Jinja trong dbt: hai cặp dấu ngoặc

Jinja trong dbt xoay quanh ba cặp dấu, nhưng hai cặp đầu là quan trọng nhất:

• {{ ... }} — biểu thức (expression): chèn giá trị vào SQL. Bất cứ thứ gì nằm trong đây được tính ra một chuỗi rồi dán vào vị trí đó. Ví dụ {{ ref('dim_khach_hang') }} được tính thành tên bảng đầy đủ như analytics.dim_khach_hang.
• {% ... %} — câu lệnh (statement): điều khiển luồng — if/else, for, set, gọi macro. Bản thân câu lệnh không sinh ra chuỗi; nó quyết định phần nào của template được render.
• {# ... #} — chú thích: không được render, không xuống warehouse.

Điều làm nhiều người "vỡ oà" khi mới học dbt: ref(), source(), config() mà bạn đã dùng ở các bài trước chính là hàm Jinja. Chúng không phải cú pháp SQL đặc biệt — chúng là các hàm Python được dbt phơi ra trong ngữ cảnh Jinja. Đó là lý do bạn viết chúng trong {{ ... }}.

Sơ đồ trên tóm gọn triết lý: bạn viết Jinja, dbt compile ra SQL thuần (lưu ở target/compiled/), rồi mới run lên warehouse. Macros và packages là kho logic được nhiều model cùng dùng.

Context & biến — làm SQL thích nghi với môi trường

Jinja mạnh vì dbt bơm vào nó một context đầy đủ thông tin về dự án và môi trường đang chạy. Vài mảnh quan trọng nhất:

• {{ var('ten_bien') }} — biến do dự án khai báo trong dbt_project.yml hoặc truyền qua CLI (dbt run --vars '{ngay_bat_dau: "2024-01-01"}'). Dùng cho tham số hoá: ngày mốc, ngưỡng, cờ bật/tắt.
• {{ env_var('DBT_SCHEMA') }} — đọc biến môi trường của hệ điều hành. Rất hợp để không hard-code secret hay cấu hình theo môi trường vào code.
• {{ target }} — thông tin về "đích" đang chạy: target.name (dev hay prod), target.schema, target.type. Đây là chìa khoá để một model cư xử khác nhau giữa dev và prod.
• {{ this }} — tham chiếu tới chính model hiện tại (tên relation của nó). Cực kỳ hữu ích trong incremental (xem bài Incremental & performance) và trong hooks.
• {{ run_query(...) }} — chạy một truy vấn ngay tại lúc compile và trả về kết quả để Jinja lặp qua.

Ví dụ đơn giản hoá dữ liệu ở dev để build nhanh:

-- models/staging/stg_su_kien.sql (minh hoạ)
select *
from {{ source('app', 'su_kien') }}
{% if target.name == 'dev' %}
    -- Ở dev chỉ lấy 7 ngày gần nhất cho nhẹ
    where ngay_su_kien >= current_date - interval '7 days'
{% endif %}

Khi chạy dbt run --target dev, phần where xuất hiện; khi --target prod, nó biến mất. Cùng một file, hai hành vi — đó là "SQL biết lập trình".

Cần phân biệt rõ hai loại "biến" hay bị lẫn: var() đọc từ cấu hình dự án hoặc cờ CLI (do bạn — người vận hành dbt — kiểm soát), còn env_var() đọc từ biến môi trường của tiến trình (do hệ thống/CI kiểm soát). Nguyên tắc thực dụng: những gì thuộc logic nghiệp vụ (ngày mốc, ngưỡng) nên là var(); những gì thuộc hạ tầng và bí mật (thông tin kết nối, tên schema theo môi trường) nên là env_var() để không lọt vào Git.

Macros — hàm cho SQL

Macro là đơn vị tái dùng của dbt, tương đương "hàm" trong lập trình. Bạn định nghĩa macro trong thư mục macros/:

-- macros/xu_ly_tien.sql (minh hoạ)
{% macro xu_dong_ve_dong(cot, so_le=2) %}
    ({{ cot }} / 100)::numeric(16, {{ so_le }})
{% endmacro %}

Rồi gọi từ bất kỳ model nào:

-- models/marts/fct_thanh_toan.sql (minh hoạ)
select
    thanh_toan_id,
    {{ xu_dong_ve_dong('so_tien_cents') }} as so_tien_dong,
    {{ xu_dong_ve_dong('phi_cents', so_le=0) }} as phi_dong
from {{ ref('stg_thanh_toan') }}

Sau khi compile, cả hai lời gọi được thay bằng biểu thức SQL đầy đủ. Nếu ngày mai công thức đổi (ví dụ đổi kiểu ép về numeric(18, ...)), bạn chỉ sửa một chỗ trong macro — mọi model dùng nó tự cập nhật. Đây là tinh thần DRY (Don't Repeat Yourself) áp dụng vào SQL.

Lưu ý cú pháp: tên cột được truyền vào dưới dạng chuỗi có nháy ('so_tien_cents'), không phải định danh trần. Bên trong macro, {{ cot }} render ra chính chuỗi đó.

Hai công cụ đi kèm macro:

• {% set x = ... %} — đặt biến Jinja cục bộ, thường đặt ở đầu model cho dễ đọc: {% set phuong_thuc = ['card', 'cash', 'transfer'] %}.
• {% do ... %} — chạy một biểu thức Jinja mà không in kết quả ra SQL (ví dụ {% do log("Đang build...", info=true) %} hoặc gọi một hàm có tác dụng phụ). Khác với {{ ... }} vốn luôn cố render kết quả thành chuỗi.

Control flow ứng dụng thực tế: pivot bằng for loop

Sức mạnh thực sự lộ ra khi kết hợp for với run_query. Giả sử ta muốn pivot mỗi phương thức thanh toán thành một cột tổng tiền — nhưng không muốn hard-code danh sách phương thức (nó có thể thay đổi).

-- models/marts/thanh_toan_pivot.sql (minh hoạ)
{% if execute %}
  {% set ds = run_query(
       "select distinct phuong_thuc from " ~ ref('stg_thanh_toan') ~ " order by 1"
  ) %}
  {% set cac_phuong_thuc = ds.columns[0].values() %}
{% else %}
  {% set cac_phuong_thuc = [] %}
{% endif %}

select
    don_hang_id,
    {% for pt in cac_phuong_thuc %}
    sum(case when phuong_thuc = '{{ pt }}' then so_tien else 0 end) as tong_{{ pt }}
    {%- if not loop.last %},{% endif %}
    {% endfor %}
from {{ ref('stg_thanh_toan') }}
group by 1

Vài điểm đáng nói:

• {% if execute %} bọc quanh run_query là bắt buộc trên thực tế. dbt có hai pha: parse (không có kết nối warehouse) và execute. Trong pha parse, execute là false, nên ta phải tránh chạy query lúc đó, nếu không sẽ lỗi.
• loop.last là biến Jinja có sẵn trong vòng for, giúp đặt dấu phẩy đúng chỗ (không thừa dấu phẩy cuối).
• {%- ... %} với dấu trừ là whitespace control — cắt bớt khoảng trắng/xuống dòng để SQL biên dịch ra gọn hơn. Dùng vừa phải; lạm dụng dễ rơi vào "hố thỏ" chỉnh khoảng trắng vô tận.

Với 3 phương thức, dbt sinh ra 3 dòng sum(case when ...). Thêm phương thức mới ở dữ liệu nguồn? Model tự sinh cột mới ở lần build sau — không đụng code.

Nếu bạn thấy mẫu run_query + for này lặp lại, hãy biết rằng dbt_utils đã đóng gói sẵn nó: macro dbt_utils.get_column_values(...) lấy danh sách giá trị phân biệt, còn dbt_utils.pivot(...) sinh luôn các cột — thường gọn hơn tự viết. Đây là minh hoạ cụ thể cho nguyên tắc "tận dụng macro có sẵn trước khi tự viết".

Packages — đừng phát minh lại bánh xe

Bạn không phải tự viết mọi macro. dbt có hệ sinh thái package giống như thư viện phần mềm. Khai báo trong packages.yml, chạy dbt deps để tải về dbt_packages/.

# packages.yml (minh hoạ)
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]
  - package: calogica/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]

Package quan trọng nhất là dbt_utils của dbt Labs — bộ macro "dao đa năng" mà gần như dự án nào cũng cài. Vài macro tiêu biểu:

• star — sinh danh sách tất cả cột của một relation (có thể except vài cột). Thay cho việc gõ tay hàng chục tên cột.
• pivot / unpivot — pivot giá trị hàng thành cột và ngược lại.
• generate_surrogate_key — sinh khoá thay thế bằng hàm băm, đa nền tảng warehouse. Rất hợp cho dim/fact.
• date_spine — sinh chuỗi ngày liên tục giữa hai mốc, để dựng bảng lịch (calendar/date dimension).
• get_column_values — lấy các giá trị phân biệt của một cột thành mảng (giống ví dụ pivot ở trên nhưng gọn hơn).
• union_relations — UNION ALL nhiều relation kể cả khi cột lệch nhau.
• safe_add, group_by — cộng an toàn cột nullable, sinh mệnh đề GROUP BY 1..N.

Gọi macro của package phải kèm tiền tố package:

-- models/marts/dim_ngay.sql (minh hoạ)
{{ dbt_utils.date_spine(
    datepart="day",
    start_date="cast('2020-01-01' as date)",
    end_date="cast('2026-01-01' as date)"
) }}

dbt_utils còn cung cấp thêm generic tests (như accepted_range, unique_combination_of_columns, expression_is_true, recency...) — bổ sung cho bốn test cốt lõi đã học ở bài Tests & docs. Các package đáng chú ý khác: dbt_expectations (test kiểu Great Expectations), codegen (tự sinh boilerplate YAML/SQL). Tất cả liệt kê trên dbt Hub (hub.getdbt.com).

Hooks & operations — chạy SQL ngoài model

Đôi khi bạn cần chạy SQL không phải để dựng model mà để quản trị: cấp quyền, tạo index, ghi log audit. Đó là việc của hooks.

• on-run-start / on-run-end — khai báo trong dbt_project.yml, chạy một lần trước/sau toàn bộ lệnh dbt run. Ví dụ ghi một dòng vào bảng audit khi bắt đầu, hoặc cấp quyền select cho role BI trên toàn schema khi kết thúc.
• pre-hook / post-hook — gắn vào từng model (qua config() hoặc YAML), chạy ngay trước/sau khi model đó được build. Ví dụ tạo index sau khi bảng vừa dựng xong.

-- models/marts/fct_don_hang.sql (minh hoạ)
{{ config(
    materialized='table',
    post_hook="create index if not exists idx_kh on {{ this }} (khach_hang_id)"
) }}

select ...

Chú ý {{ this }} trong post-hook trỏ đúng tới bảng vừa tạo. Còn dbt run-operation ten_macro --args '{...}' cho phép gọi thẳng một macro từ CLI như một tác vụ độc lập — hữu ích cho các thao tác một lần (backfill, cấp quyền thủ công) hay tích hợp vào pipeline CI/CD.

Use case thực tế

1. Macro chuẩn hoá tiền tệ dùng lại nhiều model. Công ty có 20+ model marts, mọi cột tiền lưu bằng đơn vị "cents" (số nguyên). Yêu cầu nghiệp vụ: xuất ra dạng "đồng" với đúng 2 chữ số thập phân và ép kiểu numeric thống nhất. Thay vì rải công thức (cot/100)::numeric(16,2) khắp nơi, ta viết một macro xu_dong_ve_dong (như trên) và gọi ở mọi model. Sáu tháng sau, kế toán yêu cầu đổi sang numeric(18,4) — ta sửa một dòng trong macro, chạy dbt run, toàn bộ marts đồng loạt cập nhật. Không macro, đây là một buổi chiều tìm-thay đầy rủi ro sót chỗ.

2. Giới hạn dữ liệu ở dev. Bảng sự kiện có hàng tỷ dòng. Nếu mỗi lần lập trình viên chạy dbt run ở dev đều quét toàn bộ, chi phí và thời gian chờ khủng khiếp. Dùng {% if target.name == 'dev' %} để chỉ lấy vài ngày gần nhất ở dev, còn prod quét đủ. Lập trình viên build model trong vài giây, còn dữ liệu prod vẫn đầy đủ. Đây là mẫu phổ biến nhất của việc dùng target để phân biệt môi trường.

Cảnh báo: đừng lạm dụng

Jinja quyến rũ đến mức dễ nghiện. Nhưng hãy nhớ: mục tiêu cuối cùng là SQL đọc được, debug được. Một model nhồi năm tầng for lồng nhau, ba lần run_query, và whitespace control chi chít sẽ khiến đồng nghiệp (và chính bạn sau ba tháng) không hiểu nó sinh ra gì. Vài nguyên tắc "dbtonic":

• Ưu tiên rõ ràng hơn DRY tuyệt đối. Lặp lại vài dòng SQL đôi khi tốt hơn một macro trừu tượng khó hiểu. Chỉ trừu tượng hoá khi logic thực sự lặp và ổn định.
• Đặt set ở đầu model cho các danh sách/hằng số, thay vì hard-code rải rác giữa câu SQL.
• Tận dụng macro có sẵn của dbt_utils trước khi tự viết.
• Luôn kiểm tra compiled SQL. Khi một model "chạy lạ", đừng đoán — mở target/compiled/<project>/... xem SQL thật mà dbt gửi xuống warehouse. Đây là công cụ debug số một cho mọi vấn đề liên quan Jinja. Có thể ép biên dịch bằng dbt compile.

Ghi nhớ

• Jinja biến SQL tĩnh thành template động: {{ }} chèn giá trị, {% %} điều khiển luồng, {# #} chú thích. dbt compile Jinja ra SQL thuần rồi mới run — warehouse không thấy Jinja.
• ref(), source(), config() là hàm Jinja, không phải cú pháp SQL đặc biệt.
• Context: var() (biến project/CLI), env_var() (biến môi trường), target (phân biệt dev/prod), this (model hiện tại), run_query() (chạy query lúc compile — luôn bọc {% if execute %}).
• Macros trong macros/ là hàm tái dùng cho SQL (DRY); set đặt biến, do chạy không in ra.
• Packages qua packages.yml + dbt deps; dbt_utils cho star/pivot/generate_surrogate_key/date_spine..., cùng dbt_expectations và codegen trên dbt Hub.
• Hooks (on-run-start/end, pre/post-hook) và run-operation để chạy SQL quản trị ngoài model.
• Đừng lạm dụng: giữ SQL rõ ràng, debug bằng target/compiled.

Bài tiếp theo: Incremental & performance sẽ dùng nhiều Jinja và {{ this }} để xây model cập nhật gia tăng hiệu quả.