Series dbt · Bài 6/10. Một job chạy xanh chỉ chứng minh các câu lệnh đã thực thi theo điều kiện của chúng. Nó chưa chứng minh bảng báo cáo đủ đơn, doanh thu không bị cộng hai lần hoặc khách hàng được gán đúng thành phố. Bài này bổ sung một hệ kiểm tra nhiều tầng cho project bán hàng, đồng thời thực hành tạo lỗi, quan sát test phản ứng và phục hồi dữ liệu.

Dùng tiếp dbt-retail của bài 5, Python 3.11, dbt Core 1.9.8 và dbt-postgres 1.9.1. PostgreSQL đang chạy, venv đã activate và DBT_PASSWORD đã export. Mốc gốc: 7 dòng fact mọi trạng thái; trong đó 5 dòng paid thuộc 3 đơn, tổng quantity=9 và doanh thu=400.000 VND. Những con số này dùng đối chiếu lab, không phải quy tắc cố định cho dữ liệu production.

1. Kiểm tra theo rủi ro, không theo số lượng test

Trước khi thêm test, viết điều có thể sai và hậu quả của nó. Một item không có product làm báo cáo theo sản phẩm mất ngữ cảnh. Một dimension trùng khóa có thể nhân fact khi join. Một đơn paid không có item khiến doanh thu bị thiếu. Mỗi lỗi cần một quy tắc phát hiện gần nơi nó xuất hiện, thay vì chỉ đặt một test tổng tiền ở cuối pipeline.

Không phải cứ nhiều test là tốt. Test cùng một quy tắc ở nhiều nơi có thể tăng chi phí mà không thêm khả năng phát hiện. Ngược lại, chỉ kiểm tổng doanh thu là quá yếu: hai lỗi cộng thừa và lọc thiếu có thể bù nhau. Một bộ kiểm tra hữu ích phải quan sát cả khóa, quan hệ, số dòng và chỉ tiêu tại grain phù hợp.

2. Đọc đúng generic test có sẵn

Bài 5 đã tạo _star_schema.yml và _relationships.yml. Giữ các file đó, không khai báo lại cùng model trong file YAML mới. Trong Core 1.9, cú pháp tests cùng các tham số trực tiếp như examples trước vẫn dùng được. Nếu xem tài liệu phiên bản mới có arguments lồng thêm, đừng trộn hai cách tùy tiện vào project đã pin.

Ví dụ, product_key vừa relationships vừa not_null là cần thiết khi fact không cho thiếu dimension. Một khóa null không nên được hiểu là đã qua kiểm tra tham chiếu nên chắc chắn đúng. Các test dbt là truy vấn kiểm tra dữ liệu; chúng không tự tạo primary key hoặc foreign key vật lý ở PostgreSQL.

dbt test --profiles-dir profiles --select fct_sales dim_products
dbt build --profiles-dir profiles

test kiểm tra những relation hiện có. build có thể xây rồi kiểm tra các tài nguyên được chọn theo graph. Nếu source đổi nhưng fact table chưa được xây lại, chạy test chỉ trên fact có thể đang kiểm phiên bản cũ. Luôn biết test đang đọc trạng thái nào trước khi kết luận dữ liệu nguồn hiện tại khỏe.

3. Viết singular test cho quy tắc nghiệp vụ

File test SQL phải trả các dòng vi phạm. Khi không có dòng, test pass. Không viết SELECT count(*) rồi coi giá trị 0 là pass: câu SELECT đó vẫn trả một dòng và có thể khiến test đếm thành một vi phạm. Không thêm dấu chấm phẩy cuối file singular test vì dbt có thể bọc câu SQL vào truy vấn khác.

tests/assert_paid_orders_have_items.sql

select
    order_id,
    order_status,
    line_count,
    order_amount_vnd
from {{ ref('int_orders_enriched') }}
where order_status = 'paid'
  and (line_count is null or line_count <= 0 or order_amount_vnd is null)

Một đơn paid có tổng tiền bằng 0 không mặc định sai: cửa hàng có thể có hàng tặng hoặc giảm giá toàn bộ. Lab chỉ yêu cầu có item và tính được tiền, không áp quy tắc “mọi đơn paid phải lớn hơn 0” nếu chưa thống nhất nghiệp vụ. Những quy tắc tưởng hiển nhiên thường là nơi test làm sai dữ liệu hợp lệ.

tests/assert_fact_preserves_item_ids.sql

with missing_items as (
    select order_item_id from {{ ref('stg_order_items') }}
    except
    select order_item_id from {{ ref('fct_sales') }}
),
extra_items as (
    select order_item_id from {{ ref('fct_sales') }}
    except
    select order_item_id from {{ ref('stg_order_items') }}
)
select 'missing_in_fact' as issue, order_item_id from missing_items
union all
select 'extra_in_fact' as issue, order_item_id from extra_items

So sánh tập ID hai chiều phát hiện item bị mất hoặc xuất hiện ngoài nguồn. EXCEPT xử lý theo tập nên không thay test unique: nếu fact nhân cùng một ID hai lần, tập ID vẫn giống nhau. Kết hợp kiểm tra tập với unique và not_null mới phù hợp grain một dòng mỗi item.

4. Đối soát doanh thu theo ngày từ nguồn tới mart

Ta xây expected trực tiếp từ raw, chuẩn hóa trạng thái và cộng quantity nhân giá tại dòng hàng. Actual lấy từ mart_daily_revenue. Hai đường tính độc lập hơn việc so hai bảng cùng dùng lại một model sai, nhưng vẫn chưa độc lập hoàn toàn về quy tắc nghiệp vụ: cả hai cùng định nghĩa paid và cùng nguồn đầu vào.

tests/assert_daily_revenue_reconciles.sql

with expected as (
    select
        o.ordered_at::date as order_date,
        count(distinct o.order_id) as paid_orders,
        sum(i.quantity * i.unit_price_vnd) as revenue_vnd
    from {{ source('shop', 'orders') }} as o
    join {{ source('shop', 'order_items') }} as i
        on o.order_id = i.order_id
    where lower(trim(o.status)) = 'paid'
    group by o.ordered_at::date
),
actual as (
    select
        order_date,
        paid_order_count as paid_orders,
        paid_revenue_vnd as revenue_vnd
    from {{ ref('mart_daily_revenue') }}
)
select
    coalesce(e.order_date, a.order_date) as order_date,
    e.paid_orders as expected_orders,
    a.paid_orders as actual_orders,
    e.revenue_vnd as expected_revenue_vnd,
    a.revenue_vnd as actual_revenue_vnd
from expected as e
full outer join actual as a on e.order_date = a.order_date
where coalesce(e.paid_orders, -1) <> coalesce(a.paid_orders, -1)
   or coalesce(e.revenue_vnd, -1) <> coalesce(a.revenue_vnd, -1)

FULL OUTER JOIN giữ ngày chỉ có ở một bên. Giá trị -1 làm sentinel vì chỉ tiêu trong lab không âm và mỗi ngày xuất hiện có ít nhất một đơn paid. Nếu hệ thống có doanh thu âm hợp lệ, phải thay bằng cách so sánh null-aware phù hợp, chẳng hạn IS DISTINCT FROM trên PostgreSQL, thay vì dùng sentinel có thể trùng dữ liệu thật.

Expected dùng INNER JOIN items nên tự nó không phát hiện đơn paid hoàn toàn thiếu item. Đó là lý do test assert_paid_orders_have_items vẫn cần tồn tại. Một bài đối soát tốt phải ghi rõ phần mù của nó; đừng coi một câu SQL cuối cùng là bằng chứng cho toàn bộ pipeline.

dbt build --profiles-dir profiles
dbt test --profiles-dir profiles --select assert_daily_revenue_reconciles

Trên dữ liệu gốc, test không trả dòng vi phạm. Nếu chỉ sửa raw rồi test mà chưa chạy lại mart table, test đối soát có thể fail đúng vì actual đang cũ. Nó không tự phân biệt nguyên nhân do lịch cập nhật hay logic sai; người vận hành cần xem thời điểm ingestion và build.

5. Warning, error và nơi lưu dòng lỗi

Không phải mọi bất thường cần chặn cùng mức. Khóa fact trùng thường cần error vì có thể nhân báo cáo. Một đơn lớn hơn mức thường thấy có thể cần người kiểm tra, nhưng chưa chắc sai. Ta dùng test cảnh báo có ngưỡng điều chỉnh để thực hành phân biệt hai tình huống.

tests/assert_large_paid_order.sql

{{ config(severity='warn', warn_if='>0', store_failures=true) }}

select order_id, order_amount_vnd
from {{ ref('int_orders_enriched') }}
where order_status = 'paid'
  and order_amount_vnd > {{ var('large_order_threshold_vnd', 500000) }}

Ngưỡng mặc định 500.000 VND nên dữ liệu gốc không cảnh báo. Khi thử ngưỡng 200.000 VND, đơn 1003 trị giá 220.000 phải tạo một warning. Giá trị var ở đây là cấu hình do người phát triển kiểm soát, không lấy trực tiếp từ một ô nhập tùy ý của người dùng.

dbt test --profiles-dir profiles --select assert_large_paid_order \
  --vars '{"large_order_threshold_vnd": 200000}'

dbt --warn-error test --profiles-dir profiles --select assert_large_paid_order \
  --vars '{"large_order_threshold_vnd": 200000}'

Lần đầu warning thông thường không làm job kết thúc như test error; lần thứ hai --warn-error nâng warning theo hành vi flag thành lỗi. Flag có thể áp cho các loại warning khác, nên không bật rộng chỉ để “cho nghiêm” khi chưa kiểm tra ảnh hưởng. Với severity=warn, error_if không được dùng như mức chặn thứ hai theo cách nhiều người tưởng; muốn có hai ngưỡng cần cấu hình severity=error cùng error_if/warn_if phù hợp.

{{ config(severity='error', error_if='>10', warn_if='>0') }}

select *
from {{ ref('fct_sales') }}
where product_key is null

Đoạn trên chỉ minh họa cách đặt ngưỡng, không tạo thêm file test cho lab: từ 1 đến 10 dòng vi phạm sẽ warning, trên 10 sẽ error theo điều kiện này. Với product_key bắt buộc, chấp nhận 10 null thường không phù hợp; giữ not_null error của bài 5. Ngưỡng phải xuất phát từ hậu quả nghiệp vụ, không từ sự tiện lợi khi muốn job xanh.

store_failures lưu các dòng test trả về vào audit relation theo cấu hình và schema naming của project. Xem tên relation chính xác trong log, đừng đoán rồi truy vấn một bảng khác. Bảng lỗi có thể chứa thông tin khách hàng, nên cần quyền truy cập, retention và cách dọn phù hợp. Đây là dữ liệu phục vụ điều tra, không nên public cùng tài liệu.

6. Tạo lỗi thật, chứng minh test phản ứng

Chỉ chạy các thay đổi sau trên database lab. Đầu tiên tạo trường hợp khách hàng thiếu bằng cách đặt customer_id của đơn 1001 thành null. raw.orders không có ràng buộc NOT NULL cho cột này nên phép sửa thành công, cho ta quan sát test ở tầng phân tích.

docker compose exec -T postgres psql -U dbt_lab -d retail \
  -c "update raw.orders set customer_id = null where order_id = 1001;"

dbt build --profiles-dir profiles

Kỳ vọng test not_null/quan hệ đã khai báo phản ứng, hoặc downstream bị skip khi upstream test fail. Một số bảng có thể đã được xây trước khi lỗi được phát hiện; dbt build không phải transaction toàn project. Không coi bảng fact cũ còn tồn tại là bằng chứng job mới đã hoàn thành.

docker compose exec -T postgres psql -U dbt_lab -d retail \
  -c "update raw.orders set customer_id = 1 where order_id = 1001;"

dbt build --profiles-dir profiles

Sau phục hồi, kiểm tra lại tổng paid 400.000 VND. Tiếp theo, thử sửa raw.orders.status của 1004 từ pending sang paid, chỉ chạy test đối soát mà không rebuild mart: phải thấy chênh lệch. Sau đó build lại, kiểm tổng 450.000 VND, rồi hoàn nguyên pending và build lần nữa để giữ mốc cho bài 7.

docker compose exec -T postgres psql -U dbt_lab -d retail \
  -c "update raw.orders set status = 'paid' where order_id = 1004;"
dbt test --profiles-dir profiles --select assert_daily_revenue_reconciles
dbt build --profiles-dir profiles

docker compose exec -T postgres psql -U dbt_lab -d retail \
  -c "update raw.orders set status = 'pending' where order_id = 1004;"
dbt build --profiles-dir profiles

7. Đặt test vào quy trình vận hành

Ghi rõ người nhận và hành động cho từng cảnh báo: lỗi grain thì dừng cung cấp bảng mới; bất thường đơn lớn thì kiểm chứng với nghiệp vụ; source trễ thì xác minh ingestion. Retry không sửa một khách hàng không có trong danh mục. Chỉ tự retry lỗi tạm thời đã được phân loại, tránh vòng lặp chạy lại trên cùng dữ liệu sai.

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

Bạn cần chứng minh bốn điều: dữ liệu gốc pass; ngưỡng 200.000 tạo đúng warning cho đơn 1003; một lỗi customer_id null được phát hiện; mart cũ khác nguồn mới bị test đối soát tìm ra. Sau cùng hoàn nguyên nguồn, chạy build và đối chiếu lại 3 đơn paid, 9 sản phẩm, 400.000 VND.

Tự trả lời: vì sao unique chưa đủ bảo vệ một foreign key? Vì nó không kiểm tra relation đích và không thay not_null. Vì sao so hai mart có thể cùng pass dù cùng sai? Vì cả hai có thể dùng chung upstream lỗi. Vì sao cần giữ exit code khi lập lịch? Vì scheduler chỉ biết thất bại nếu lệnh hoặc script truyền trạng thái đó ra ngoài. Bài 10 sẽ đóng gói các kiểm tra thành job có artifacts và nhật ký.

Tài liệu tham khảo

• dbt: data tests
• Severity, error_if và warn_if
• Lưu các dòng vi phạm
• Lệnh dbt build
• Warning và error flags