Series dbt · Bài 3/10. Khi một câu SQL dài làm mọi việc, lỗi có thể nằm ở bất kỳ đoạn nào: đổi kiểu dữ liệu, lọc trạng thái, join hay tính doanh thu. Bài này chia project bán hàng thành staging, intermediate và marts, để mỗi phần có mục đích và mức chi tiết rõ ràng. Sau cùng, bạn sẽ xây bảng doanh thu ngày có thể đối chiếu bằng tay.

Tiếp tục project dbt-retail của bài 2: PostgreSQL đang chạy, venv được activate, DBT_PASSWORD đã export và bốn bảng raw có dữ liệu gốc. Các đường dẫn tính từ thư mục project. Giữ nguyên dbt_project.yml và profiles/profiles.yml đã tạo; không chạy lại bài tập đổi trạng thái nếu chưa hoàn nguyên.

1. Tách lớp theo trách nhiệm, không theo hình thức

Staging chuẩn hóa biểu diễn của từng nguồn: tên cột rõ nghĩa, kiểu dữ liệu và mã trạng thái nhất quán. Intermediate chuẩn bị một phép biến đổi có thể dùng lại, chẳng hạn cộng dòng hàng về một đơn. Mart phục vụ câu hỏi nghiệp vụ, chẳng hạn doanh thu paid theo ngày. Thư mục là quy ước tổ chức code; schema là nơi database giữ relation, hai thứ liên hệ qua cấu hình chứ không tự đồng nhất.

Ở project nhỏ, không nhất thiết phải có đủ mọi lớp cho mọi model. Ta dùng ba lớp ở đây vì có một bước thay grain đáng học: từ dòng hàng về đơn hàng. Nếu chỉ đổi tên một cột rồi tạo thêm một model intermediate không được dùng lại, cấu trúc có thể làm người đọc khó theo dõi hơn. Tách khi có trách nhiệm rõ ràng và có điểm kiểm tra hữu ích.

2. Grain: xác định đơn vị của một dòng

Grain là câu mô tả một dòng đại diện cho điều gì. raw.orders có một dòng mỗi order_id. raw.order_items có một dòng mỗi order_item_id, nên cùng order_id xuất hiện nhiều lần. Hai bảng đều đúng dù số dòng khác nhau. Khi join, bạn phải biết grain đầu vào và grain đầu ra trước khi cộng tiền hoặc đếm đơn.

Nếu join orders với items rồi count(*), đơn 1001 có hai dòng nên bị đếm thành hai. count(distinct order_id) có thể sửa một chỉ tiêu đếm, nhưng không tự sửa mọi phép sum. Cách rõ ràng hơn cho báo cáo theo đơn là tổng hợp items trước về order_id, rồi mới join với orders.

3. Tạo bốn staging model, giữ nguồn dễ truy vết

File _sources.yml của bài 2 đã khai báo đủ bốn bảng. Thay nội dung stg_orders.sql bằng bản dưới nếu cần; ba model còn lại là file mới. Không thêm SELECT DISTINCT để giấu dòng trùng, không lọc đơn hủy ở staging và không tổng hợp sớm làm mất chi tiết. Nguồn bẩn cần được nhận diện, không âm thầm biến mất.

models/staging/stg_customers.sql

select
    customer_id::integer as customer_id,
    trim(customer_name) as customer_name,
    trim(city) as city
from {{ source('shop', 'customers') }}

models/staging/stg_products.sql

select
    product_id::integer as product_id,
    trim(product_name) as product_name,
    trim(category) as category
from {{ source('shop', 'products') }}

models/staging/stg_orders.sql

select
    order_id::integer as order_id,
    customer_id::integer as customer_id,
    ordered_at::date as order_date,
    lower(trim(status)) as order_status
from {{ source('shop', 'orders') }}

models/staging/stg_order_items.sql

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,
    (quantity * unit_price_vnd)::numeric(18,0) as line_amount_vnd
from {{ source('shop', 'order_items') }}

line_amount_vnd là phép tính cố định tại grain dòng hàng. Có thể đặt ở staging vì mọi nơi dùng dòng này đều cần cùng định nghĩa, trong phạm vi lab chưa có giảm giá hoặc thuế. Nếu định nghĩa phụ thuộc chính sách phòng ban, nên đặt ở lớp nghiệp vụ phù hợp. Tên cột có hậu tố _vnd để người dùng không trộn tiền đồng với ngoại tệ.

trim bỏ khoảng trắng đầu cuối; lower thống nhất trạng thái. Ta không lower tên người vì đó là dữ liệu trình bày. Chuyển text sang date có thể thất bại khi nguồn có ngày sai; lỗi đó cần được xử lý bằng quy tắc dữ liệu, không bằng cách biến mọi giá trị sai thành ngày hôm nay.

dbt run --profiles-dir profiles --select staging

docker compose exec -T postgres psql -U dbt_lab -d retail \
  -c "select * from dev_staging.stg_order_items order by order_item_id;"

Bảy dòng item cần được giữ đủ. Số tiền lần lượt là 100.000, 30.000, 200.000, 200.000, 20.000, 50.000 và 50.000 VND. Chưa lọc paid ở bước này nên tổng 650.000 VND là tổng giá trị mọi dòng hàng, không được gọi là doanh thu paid.

4. Cộng tiền dòng hàng về một dòng mỗi đơn

models/intermediate/int_order_amounts.sql

select
    order_id,
    count(*) as line_count,
    sum(quantity) as total_quantity,
    sum(line_amount_vnd) as order_amount_vnd
from {{ ref('stg_order_items') }}
group by order_id

GROUP BY order_id làm grain đổi từ dòng hàng sang đơn. line_count là số dòng hàng, total_quantity là số lượng sản phẩm; hai số không giống nhau. Đơn 1001 có hai dòng, nhưng mua tổng cộng năm sản phẩm. Phân biệt chúng trước khi đặt tên metric giúp tránh báo cáo “số sản phẩm bán ra” thực chất đang đếm dòng.

models/intermediate/int_orders_enriched.sql

select
    o.order_id,
    o.customer_id,
    o.order_date,
    o.order_status,
    a.line_count,
    a.total_quantity,
    a.order_amount_vnd
from {{ ref('stg_orders') }} as o
left join {{ ref('int_order_amounts') }} as a
    on o.order_id = a.order_id

Dùng LEFT JOIN để đơn không có item vẫn hiện ra với tổng tiền null. Nếu dùng INNER JOIN, đơn đó biến mất và báo cáo có thể thiếu mà không ai biết. Ta chưa coalesce tổng tiền thành 0, vì không biết “thiếu item” có thật sự nghĩa là đơn miễn phí hay là lỗi tải dữ liệu. Giữ null giúp đặt kiểm tra đúng ở bước sau.

dbt run --profiles-dir profiles --select +int_orders_enriched

docker compose exec -T postgres psql -U dbt_lab -d retail \
  -c "select * from dev_intermediate.int_orders_enriched order by order_id;"

Dấu + phía trước chọn cả upstream cần thiết. Khi chạy lại, view intermediate phản ánh dữ liệu mới từ staging. Đây chưa phải incremental model; dbt đang xây relation theo cấu hình view của project. Bài 4 sẽ đi sâu cách ref tạo dependency và các lựa chọn chạy một phần DAG.

5. Xây mart doanh thu ngày dùng cho báo cáo

models/marts/mart_daily_revenue.sql

select
    order_date,
    count(*) as paid_order_count,
    sum(total_quantity) as paid_item_quantity,
    sum(order_amount_vnd) as paid_revenue_vnd
from {{ ref('int_orders_enriched') }}
where order_status = 'paid'
group by order_date

Đầu vào có một dòng mỗi đơn, vì vậy count(*) lúc này đếm đơn paid đúng theo grain. Lọc trạng thái ở mart thể hiện một định nghĩa chỉ tiêu, không xóa đơn pending/cancelled khỏi mọi lớp. Một model khác vẫn có thể dùng int_orders_enriched để đo tỷ lệ hủy hoặc giá trị đơn đang chờ.

dbt run --profiles-dir profiles --select +mart_daily_revenue

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

Đây là kết quả mong đợi trên bộ dữ liệu gốc của bài 2. Ngày không có đơn paid sẽ không xuất hiện. Muốn báo cáo đủ mọi ngày với doanh thu 0, cần một date dimension/calendar rồi LEFT JOIN chỉ tiêu vào lịch; bài 5 sẽ tạo dim_date. Không tự hiểu “không có dòng” và “doanh thu bằng 0” là một trong mọi ngữ cảnh.

6. Đối soát ở từng ranh giới của mô hình

select order_id, count(*)
from dev_intermediate.int_orders_enriched
group by order_id
having count(*) > 1;

select *
from dev_intermediate.int_orders_enriched
where order_status = 'paid' and order_amount_vnd is null;

select sum(paid_order_count) as paid_orders,
       sum(paid_revenue_vnd) as paid_revenue_vnd
from dev_marts.mart_daily_revenue;

Hai truy vấn đầu cần trả 0 dòng trên bộ dữ liệu gốc. Truy vấn cuối cần trả 3 đơn và 400.000 VND. Nếu chỉ kiểm tổng doanh thu mà không kiểm grain, một lỗi nhân dòng và một lỗi lọc thiếu có thể vô tình bù nhau. Kết quả tổng đúng là điều cần, nhưng chưa đủ chứng minh mọi dòng đúng.

Để hình dung lỗi nhân tiền, giả sử bạn đã có order_amount_vnd=130.000 cho đơn 1001 rồi lại join đơn đó về hai item và sum order_amount_vnd. Tổng thành 260.000 dù không có giao dịch mới. SUM(DISTINCT order_amount_vnd) cũng không đúng: hai đơn khác nhau có thể cùng tổng tiền. Cách sửa phải dựa vào grain và quan hệ join, không dựa vào việc con số “trông có vẻ hợp lý”.

Trong project hiện tại, PostgreSQL primary key giúp khóa raw không trùng. Nguồn thực tế có thể là append log hoặc CDC gồm nhiều phiên bản của cùng khóa; lúc đó cần một model chọn trạng thái đúng theo thời gian và quy tắc rõ ràng. Không suy rộng từ bộ dữ liệu sạch của lab rằng mọi source đều có một dòng mỗi thực thể.

7. View và table: dữ liệu đổi khi nào?

staging và intermediate hiện là view; mart là table theo cấu hình bài 2. Nếu sửa raw, truy vấn view có thể thấy giá trị mới ngay, nhưng mart table vẫn giữ kết quả lần chạy trước. Vì vậy “dbt đã chạy hôm qua” không có nghĩa dashboard hôm nay đang dùng dữ liệu mới. Cần biết lịch cập nhật và thời điểm build của relation đích.

docker compose exec -T postgres psql -U dbt_lab -d retail <<'SQL'
update raw.orders set status = 'paid' where order_id = 1004;
select order_status from dev_staging.stg_orders where order_id = 1004;
select * from dev_marts.mart_daily_revenue order by order_date;
SQL

dbt run --profiles-dir profiles --select mart_daily_revenue

Sau khi chạy lại mart, ngày 2 có hai đơn, số lượng 4 và doanh thu 270.000 VND; tổng ba ngày là 450.000 VND. Chỉ cần chạy mart ở lần này vì các upstream là view đã tồn tại và tự đọc nguồn. Nếu upstream là table có kết quả cũ, chạy một mart riêng sẽ đọc bảng cũ; chọn upstream khi cần rebuild chúng.

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

Đoạn hoàn nguyên đưa dữ liệu về mốc 400.000 VND cho bài tiếp theo. Đừng bỏ qua bước này rồi báo kết quả bài 4 không khớp. Khi thử nghiệm dữ liệu, ghi rõ bạn đổi bản ghi nào và đã xây lại model nào.

8. Bài tập và tiêu chí hoàn thành

Tự viết một SELECT đếm số đơn theo order_status từ int_orders_enriched. Đáp án trên dữ liệu gốc: paid=3, cancelled=1, pending=1. Sau đó tính số lượng sản phẩm của từng đơn và giải thích vì sao line_count của 1001 là 2 nhưng total_quantity là 5.

Bài hoàn thành khi bốn staging giữ đúng số dòng nguồn; int_orders_enriched có năm dòng không trùng order_id; mart có ba ngày và tổng doanh thu 400.000 VND; bạn giải thích được vì sao mart table cần chạy lại sau khi raw đổi. Bài 4 sẽ khai thác chính luồng phụ thuộc này để kiểm tra chọn model và xem lineage.

Tài liệu tham khảo

• Staging models
• Intermediate models
• Marts
• Materializations
• Custom schemas