Series dbt · Bài 9/10. Khi cùng công thức tiền xuất hiện trong nhiều model, một lần sửa có thể bị quên ở một file. Jinja và macro giúp tái sử dụng logic sinh SQL, nhưng cũng có thể làm SQL khó đọc hơn nếu lạm dụng. Bài này viết macro nhỏ có giới hạn rõ, xem SQL đã compile và kiểm tra kết quả trên dữ liệu bán hàng xuyên suốt series.

Tiếp tục dbt-retail sau bài 8 với Core 1.9.8 và PostgreSQL. Đảm bảo nguồn đã hoàn nguyên: doanh thu paid 400.000 VND, pending 50.000 VND và cancelled 200.000 VND. Giữ các model/tests trước đó; bài chỉ refactor công thức dòng hàng và thêm một mart tổng hợp trạng thái. Không cần cài package ngoài.

1. Jinja không phải một vòng lặp chạy cho mỗi dòng dữ liệu

Jinja được dbt dùng để render văn bản SQL trước khi gửi tới database. Một vòng for có thể tạo ba biểu thức SUM, nhưng ba phép SUM vẫn do PostgreSQL tính trên dữ liệu. Điều này khác một for Python đọc từng dòng từ database rồi tính trên máy host. Hiểu đúng ranh giới giúp bạn phân biệt lỗi template với lỗi SQL hoặc dữ liệu.

Một macro giống hàm sinh đoạn SQL. Nó nhận tham số, tạo biểu thức hoặc truy vấn và có thể được gọi từ model/test. Macro không tự làm phép tính tiền nhanh hơn: nếu SQL sau render giống nhau, lợi ích chính là nhất quán, khả năng sửa và review.

2. Macro tính tiền với phạm vi cố ý nhỏ

Tạo macros/line_amount.sql. Trong lab, quantity và unit_price_vnd là các biểu thức SQL do người phát triển chỉ định; scale chỉ được 0 hoặc 2. Chúng ta giữ cast PostgreSQL để đúng môi trường đang học, chưa tuyên bố macro chạy nguyên xi trên mọi warehouse.

{% macro line_amount(quantity_expr, price_expr, scale=0) %}
    {% if scale not in [0, 2] %}
        {{ exceptions.raise_compiler_error('line_amount scale must be 0 or 2') }}
    {% endif %}
    (({{ quantity_expr }}) * ({{ price_expr }}))::numeric(18, {{ scale }})
{% endmacro %}

Các chuỗi quantity_expr và price_expr được chèn như SQL expression, không phải chuỗi dữ liệu có dấu nháy. Ví dụ truyền quantity, unit_price_vnd để sinh quantity nhân unit_price_vnd. Không đưa văn bản tùy ý từ người dùng hoặc request web vào tham số này; macro không phải lớp xử lý SQL injection.

scale=0 giữ VND nguyên như các bài trước. scale=2 có thể dùng khi bạn chủ ý cần hai chữ số thập phân trong một bối cảnh khác. Cast có thể làm tròn giá trị và numeric(18, scale) có giới hạn độ lớn; phải chọn kiểu từ yêu cầu dữ liệu, không chỉ vì ví dụ chạy được.

3. Thay công thức lặp nhưng giữ nguyên schema đầu ra

Thay models/staging/stg_order_items.sql bằng nội dung đầy đủ sau. Các cột và tên model giữ nguyên để downstream không phải sửa. Ta chỉ chuyển biểu thức line_amount_vnd thành một lời gọi macro.

select
    order_item_id::integer as order_item_id,
    order_id::integer as order_id,
    product_id::integer as product_id,
    quantity::integer as quantity,
    unit_price_vnd::numeric(14,0) as unit_price_vnd,
    {{ line_amount('quantity', 'unit_price_vnd', 0) }} as line_amount_vnd
from {{ source('shop', 'order_items') }}

dbt compile --profiles-dir profiles --select stg_order_items
cat target/compiled/retail_analytics/models/staging/stg_order_items.sql
dbt run --profiles-dir profiles --select stg_order_items+
dbt test --profiles-dir profiles

File compiled phải có biểu thức nhân/cast PostgreSQL, không còn lời gọi line_amount hoặc Jinja. Tên source cũng được resolve. Đọc phần SQL này trước khi điều tra database nếu lỗi bắt đầu ngay sau khi sửa macro. Thay macro có thể tác động nhiều nơi, nên không chỉ test một model đại diện rồi cho rằng mọi caller đều đúng.

Selector stg_order_items+ chạy nhánh downstream của items. Các relation upstream khác như dimensions và snapshot cần đã tồn tại từ bài trước; nếu dựng project mới, phải theo quy trình bootstrap đầy đủ, không coi selector này là cách dựng mọi đầu vào từ trống.

4. Sinh báo cáo trạng thái bằng danh sách kiểm soát

Tạo models/marts/mart_status_revenue.sql. Model có một dòng cho toàn bộ dữ liệu trong lab, các cột là tổng giá trị đơn theo trạng thái. Không đặt tên tất cả các cột là “doanh thu” theo nghĩa kế toán: pending/cancelled là giá trị đơn thuộc trạng thái đó, chưa phải doanh thu paid.

{% set statuses = var('report_statuses', ['paid', 'pending', 'cancelled']) %}
{% set allowed = ['paid', 'pending', 'cancelled'] %}

{% if statuses is string or statuses is mapping or statuses is not sequence or (statuses | length) == 0 %}
    {{ exceptions.raise_compiler_error('report_statuses must be a non-empty list') }}
{% endif %}
{% if (statuses | unique | list | length) != (statuses | length) %}
    {{ exceptions.raise_compiler_error('report_statuses contains duplicate values') }}
{% endif %}
{% for status in statuses %}
    {% if status not in allowed %}
        {{ exceptions.raise_compiler_error('Unsupported report status: ' ~ status) }}
    {% endif %}
{% endfor %}

select
    {% for status in statuses %}
    coalesce(sum(
        case when order_status = '{{ status }}'
             then order_amount_vnd else 0 end
    ), 0)::numeric(18,0) as {{ status }}_value_vnd
    {% if not loop.last %},{% endif %}
    {% endfor %}
from {{ ref('int_orders_enriched') }}

Danh sách allowlist ngăn template tạo trạng thái hoặc tên cột ngoài phạm vi đã thiết kế. Kiểm tra trùng tránh tạo hai cột cùng tên. loop.last giúp đặt dấu phẩy đúng giữa các biểu thức, không để dấu phẩy thừa trước FROM. Các cột được sinh có thể thay đổi theo vars, nên cần kiểm soát biến trong môi trường chạy chính thức.

dbt compile --profiles-dir profiles --select mart_status_revenue
cat target/compiled/retail_analytics/models/marts/mart_status_revenue.sql
dbt run --profiles-dir profiles --select +mart_status_revenue

docker compose exec -T postgres psql -U dbt_lab -d retail \
  -c "select * from dev_marts.mart_status_revenue;"

Tổng ba cột là 650.000 VND, tổng giá trị mọi đơn có item trong dữ liệu gốc. Mart dùng lại tổng tiền ở grain đơn, tránh join items rồi cộng tổng đơn nhiều lần. Coalesce tổng rỗng về 0 ở đây để model vẫn có một giá trị khi không có đơn thuộc trạng thái; nó không thay test đơn paid thiếu item đã có từ bài 6.

4.1. Thử tham số không hợp lệ

dbt compile --profiles-dir profiles --select mart_status_revenue \
  --vars '{"report_statuses": ["paid", "unknown"]}'

dbt compile --profiles-dir profiles --select mart_status_revenue

Lệnh đầu phải báo compilation error theo guard, không gửi một câu SQL báo cáo với trạng thái lạ. Lệnh sau quay về danh sách mặc định vì vars của một lần chạy không tự lưu vĩnh viễn vào project. Sau compile lỗi, file compiled từ lần trước có thể vẫn còn; đọc log của invocation hiện tại, không coi một file cũ là bằng chứng compile mới thành công.

Trong production, thay tập cột theo tham số có thể làm dashboard hoặc downstream fail. Nếu report cần schema ổn định, giữ allowlist và danh sách cột cố định, chỉ thay phạm vi lọc dữ liệu. Meta-programming không nên biến contract đầu ra thành thứ thay đổi tùy ý.

5. Viết generic test có thể tái sử dụng

Tạo tests/generic/non_negative.sql. Generic test dùng block test thay vì macro thông thường để dbt nhận nó là một định nghĩa kiểm tra. model và column_name là các tham số chuẩn được dbt truyền vào khi khai báo trong YAML.

{% test non_negative(model, column_name) %}
select *
from {{ model }}
where {{ adapter.quote(column_name) }} < 0
{% endtest %}

adapter.quote xử lý tên cột trong trường hợp này như identifier. Nó khác chèn một SQL expression nhiều phần vào line_amount. Test chỉ phát hiện giá trị âm; null không thỏa điều kiện <0>

models/marts/_status_revenue.yml

version: 2
models:
  - name: mart_status_revenue
    description: 'One aggregate row with values for paid/pending/cancelled orders'
    columns:
      - name: paid_value_vnd
        tests: [not_null, non_negative]
      - name: pending_value_vnd
        tests: [not_null, non_negative]
      - name: cancelled_value_vnd
        tests: [not_null, non_negative]

mkdir -p tests/generic
dbt build --profiles-dir profiles --select +mart_status_revenue

Tạo thư mục tests/generic trước khi lưu file test nếu nó chưa có. Không khai báo lại mart_status_revenue trong properties file khác. Nếu dùng vars để bỏ một trạng thái khỏi schema rồi vẫn giữ test cột đó, test sẽ báo lỗi cột thiếu; đó là dấu hiệu cấu hình đầu ra không còn khớp contract.

6. Kiểm chứng macro bằng các trường hợp biết trước

Data test trên bảng thật giúp tìm lỗi đầu ra, nhưng một vài trường hợp tính tay giúp kiểm tra trực tiếp công thức macro khi refactor. Tạo tests/assert_line_amount_macro.sql. Đây là singular test sinh một fixture SQL nhỏ, không đọc hay sửa bảng nguồn.

with cases as (
    select 2::integer as quantity, 50000::numeric as price, 100000::numeric as expected
    union all
    select 3, 10000::numeric, 30000::numeric
    union all
    select 0, 200000::numeric, 0::numeric
),
calculated as (
    select
        *,
        {{ line_amount('quantity', 'price', 0) }} as actual
    from cases
)
select *
from calculated
where actual is distinct from expected

dbt test --profiles-dir profiles --select assert_line_amount_macro

Ba trường hợp phải pass. quantity=0 ở fixture chỉ kiểm phép nhân và giá trị 0 của macro; nó không thay đổi quy tắc quantity dương của fact bán hàng ở bài 5. Logic phép tính và quy tắc dữ liệu là hai lớp kiểm tra khác nhau. Có thể mở rộng fixture cho giá lẻ, null hoặc tràn numeric theo policy mà bạn chọn.

Một test dùng lại chính công thức sai để tạo expected sẽ ít giá trị. Expected trong ví dụ là các hằng số tính tay, không gọi lại macro để so macro với chính nó. Khi thay đổi cách làm tròn hoặc đơn vị tiền, phải cập nhật yêu cầu nghiệp vụ trước rồi mới cập nhật expected.

7. Mô tả macro và review phạm vi ảnh hưởng

macros/_macros.yml

version: 2
macros:
  - name: line_amount
    description: 'PostgreSQL numeric multiplication; trusted SQL expressions only'
    arguments:
      - name: quantity_expr
        type: string
        description: 'SQL expression for quantity'
      - name: price_expr
        type: string
        description: 'SQL expression for unit price'
      - name: scale
        type: integer
        description: 'Only 0 or 2; default 0'

Mô tả arguments giúp người dùng macro hiểu cách gọi. Trong phiên bản của lab, đừng coi YAML type mô tả là runtime type checker toàn diện. Guard trong macro mới thể hiện giới hạn scale đã chọn. Trước merge, tìm mọi caller, compile các model liên quan và xem diff SQL cùng test kết quả.

Khi cùng project có nhiều nhóm, đặt tên macro có ngữ nghĩa và tránh che ref, source hoặc config bằng một tên riêng trùng built-in. Package bên ngoài có thể cung cấp macro tốt, nhưng cần review code, pin phiên bản và quản lý package lock; việc gọi dbt deps không tự chứng minh package an toàn hoặc tương thích với business logic của bạn.

8. Những bẫy thường gặp

Compile không cần chạy trước mọi dbt run; run cũng thực hiện các bước biên dịch cần thiết. Ở bài học này, gọi compile riêng để quan sát và debug. Với macro có run_query hoặc hooks tùy ý, phải hiểu truy vấn nào có thể chạy trong giai đoạn compile/docs, không dùng từ “an toàn tuyệt đối” chỉ vì chưa gọi run.

9. Bài tập nghiệm thu

Sau refactor, line_amount của bảy item phải giống trước; fct_sales vẫn bảy dòng; paid vẫn 400.000 VND. mart_status_revenue có đúng ba cột và một dòng với tổng 650.000 VND. Fixture macro pass, trạng thái unknown bị từ chối khi compile và lần chạy mặc định sau đó lại thành công.

Thử thêm trường hợp 2 nhân 12,5 với scale=2 và expected=25,00 trong fixture. Sau đó giải thích vì sao macro trả numeric không có nghĩa toàn pipeline đã hỗ trợ ngoại tệ, thuế hoặc tỷ giá. Một thay đổi kiểu dữ liệu phải được xem xuyên suốt source, staging, fact, mart, test và dashboard.

Bài cuối sẽ đóng gói project thành quy trình có kiểm tra source, snapshot, build/test, artifacts, lịch chạy và CI trên database tách biệt. Mục tiêu là người vận hành biết một lần chạy đã làm gì và dừng ở đâu khi có lỗi.

Tài liệu tham khảo

• Jinja và macros
• Dùng Jinja trong dbt
• Viết generic test
• Lệnh compile
• exceptions và compilation errors