Series dbt · Bài 5/10. Doanh thu theo ngày mới trả lời được một góc nhìn. Người dùng còn muốn xem theo khách hàng, thành phố, sản phẩm và nhóm sản phẩm. Nếu mỗi báo cáo tự join từ raw, cùng một lỗi có thể bị lặp ở nhiều nơi. Bài này xây mô hình sao với ba dimension và một fact ở grain dòng hàng, sau đó truy vấn nhiều góc nhìn trên cùng nền dữ liệu.

Tiếp tục project dbt-retail sau bài 4. Stack đang chạy, môi trường Python và DBT_PASSWORD đã sẵn sàng. Dữ liệu gốc vẫn có 4 khách, 3 sản phẩm, 5 đơn và 7 dòng hàng. Ta dùng target dev; các bảng mới nằm trong dev_marts theo cấu hình có từ bài 2. Không cần package dbt bên ngoài để chạy lab.

1. Chọn grain trước khi chọn cột

Fact ghi nhận phép đo tại một mức chi tiết cụ thể. Dimension cung cấp ngữ cảnh để lọc và nhóm phép đo. Với cửa hàng trong lab, chọn grain: một dòng fact cho một order_item_id của nguồn shop. quantity và line_amount_vnd thuộc grain đó; customer, product và order_date giúp phân tích. order_id được giữ để truy vết và đếm số đơn, dù không phải khóa duy nhất của fact.

Không trộn một dòng đơn hàng và một dòng tổng ngày trong cùng fact rồi mong SUM tự hiểu. Cũng không lặp order_amount_vnd trên từng item để người dùng vô tình cộng lại. Fact này giữ tiền từng dòng; tổng tiền đơn có thể tính bằng GROUP BY order_id hoặc dùng model ở grain đơn của bài 3.

Ta giữ mọi trạng thái đơn trong fct_sales để phân tích pending/cancelled khi cần. Chỉ tiêu doanh thu paid phải lọc order_status=paid. Tên fct_sales không có nghĩa mỗi dòng chắc chắn là doanh thu đã ghi nhận; mô tả và trường trạng thái phải làm rõ điều đó. Chưa mô hình hóa hoàn tiền, thuế, phí hoặc sự kiện thanh toán nhiều lần.

2. Khóa nghiệp vụ khác khóa kỹ thuật

customer_id là khóa từ hệ thống nguồn. Khi gộp nhiều nguồn, cùng ID 1 có thể là hai người khác nhau. Lab tạo technical key xác định bằng md5 trên chuỗi có tiền tố nguồn và loại thực thể, giữ cả ID gốc để đối chiếu. Đây là cách minh họa không cần bảng cấp số, không phải khẳng định hash không bao giờ va chạm hoặc tốt hơn mọi loại khóa.

select md5('shop.customer|' || 1::text) as customer_key;

Với một khóa integer không null và tiền tố cố định, cách ghép chuỗi ở đây không mơ hồ. Nếu khóa gồm nhiều cột hoặc có null, cần quy tắc phân tách/biểu diễn null chặt chẽ. Không dùng row_number() theo thứ tự truy vấn để làm khóa ổn định: thêm dữ liệu có thể làm các số thay đổi. Một technical key ổn định theo ID cũng chưa đủ biểu diễn nhiều phiên bản lịch sử SCD Type 2.

3. Xây ba dimension

models/marts/dim_customers.sql

select
    md5('shop.customer|' || customer_id::text) as customer_key,
    customer_id,
    customer_name,
    city
from {{ ref('stg_customers') }}

models/marts/dim_products.sql

select
    md5('shop.product|' || product_id::text) as product_key,
    product_id,
    product_name,
    category
from {{ ref('stg_products') }}

Hai dimension có một dòng cho mỗi ID nhờ grain nguồn và kiểm tra unique ở phần sau. Không thêm DISTINCT để làm mất bằng chứng khi nguồn trùng. Nếu hai bản ghi cùng customer_id nhưng khác city, phải chọn quy tắc bản hiện tại hoặc lưu lịch sử; DISTINCT toàn dòng vẫn có thể giữ cả hai và khiến join nhân fact.

models/marts/dim_date.sql

with calendar as (
    select day_value::date as full_date
    from generate_series(
        timestamp '2026-01-01',
        timestamp '2026-01-31',
        interval '1 day'
    ) as days(day_value)
)
select
    to_char(full_date, 'YYYYMMDD')::integer as date_key,
    full_date,
    extract(year from full_date)::integer as calendar_year,
    extract(month from full_date)::integer as calendar_month,
    extract(quarter from full_date)::integer as calendar_quarter,
    extract(isodow from full_date)::integer as iso_weekday
from calendar

Khoảng lịch cố định tháng 1/2026 để mọi người có cùng 31 dòng và dễ đối chiếu. iso_weekday theo ISO: thứ hai là 1 và chủ nhật là 7. Đây chưa phải lịch tài chính hoặc lịch ngày nghỉ Việt Nam. Production phải tạo lịch đủ rộng cho dữ liệu, thống nhất timezone khi chuyển timestamp về ngày và duy trì các thuộc tính lịch nghiệp vụ.

date_key dạng YYYYMMDD là một khóa có ý nghĩa dễ đọc được dùng trong lab. Không suy ra phải nhét thông tin nghiệp vụ vào mọi surrogate key. Khóa khách và sản phẩm được thiết kế khác để tách ID nguồn khỏi khóa dùng trong fact.

4. Xây fct_sales ở grain dòng hàng

models/marts/fct_sales.sql

select
    md5('shop.order_item|' || i.order_item_id::text) as sales_line_key,
    i.order_item_id,
    i.order_id,
    o.customer_id,
    i.product_id,
    c.customer_key,
    p.product_key,
    d.date_key,
    o.order_date,
    o.order_status,
    i.quantity,
    i.unit_price_vnd,
    i.line_amount_vnd
from {{ ref('stg_order_items') }} as i
left join {{ ref('stg_orders') }} as o
    on i.order_id = o.order_id
left join {{ ref('dim_customers') }} as c
    on o.customer_id = c.customer_id
left join {{ ref('dim_products') }} as p
    on i.product_id = p.product_id
left join {{ ref('dim_date') }} as d
    on o.order_date = d.full_date

Bắt đầu từ items để giữ đúng grain. Mỗi dimension phải có khóa join duy nhất để một dòng item chỉ ghép tối đa một dòng ngữ cảnh. LEFT JOIN giữ item thiếu ngữ cảnh để test phát hiện thay vì làm nó biến mất. Những khóa dimension null không được coi là “tạm ổn”; phần kiểm tra bên dưới sẽ báo lỗi.

Trong hệ thống production, có thể xây một dimension member Unknown với khóa riêng cho dữ liệu đến muộn, kèm cảnh báo và cơ chế cập nhật lại. Lab chọn cách để null rồi fail test cho dễ học, chưa triển khai quy trình Unknown. Hai chiến lược đều cần minh bạch; âm thầm loại item bằng INNER JOIN có thể làm doanh thu thấp mà không có tín hiệu lỗi.

dbt run --profiles-dir profiles --select +fct_sales

docker compose exec -T postgres psql -U dbt_lab -d retail \
  -c "select count(*) as fact_rows from dev_marts.fct_sales;"

Kết quả phải có 7 dòng fact, không phải 3. Ba là số đơn paid; năm là số dòng hàng thuộc đơn paid; bảy là toàn bộ dòng hàng mọi trạng thái. Nhầm ba số này là dấu hiệu chưa nắm grain. Các phép join không được làm fact tăng hoặc giảm số dòng so với bảy item ban đầu.

5. Kiểm tra khóa, quan hệ và số tiền

Tạo models/marts/_star_schema.yml. Cú pháp tests bên dưới dành cho bộ Core 1.9 của lab; khi đọc tài liệu phiên bản mới có thể thấy data_tests và cách đặt arguments khác. Không sửa cú pháp chỉ theo một ví dụ khác phiên bản khi project hiện tại vẫn dùng phiên bản đã pin.

version: 2
models:
  - name: dim_customers
    columns:
      - name: customer_key
        tests: [unique, not_null]
      - name: customer_id
        tests: [unique, not_null]
  - name: dim_products
    columns:
      - name: product_key
        tests: [unique, not_null]
      - name: product_id
        tests: [unique, not_null]
  - name: dim_date
    columns:
      - name: date_key
        tests: [unique, not_null]
      - name: full_date
        tests: [unique, not_null]
  - name: fct_sales
    description: 'One row per shop order_item_id, all order statuses'
    columns:
      - name: sales_line_key
        tests: [unique, not_null]
      - name: order_item_id
        tests: [unique, not_null]
      - name: order_id
        tests: [not_null]
      - name: order_date
        tests: [not_null]
      - name: order_status
        tests:
          - not_null
          - accepted_values:
              values: ['paid', 'pending', 'cancelled']
      - name: customer_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_key
      - name: product_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_products')
              field: product_key
      - name: date_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_date')
              field: date_key

relationships kiểm tra giá trị tham chiếu tồn tại, thường không coi null là vi phạm theo cùng cách not_null. Vì vậy phải giữ cả hai khi không cho phép thiếu khóa. Đây là các truy vấn kiểm tra, không tự tạo foreign key constraint vật lý trong PostgreSQL chỉ nhờ khai báo YAML.

File tests/assert_sales_line_amount.sql

select *
from {{ ref('fct_sales') }}
where quantity is null
   or quantity <= 0
   or unit_price_vnd is null
   or unit_price_vnd < 0
   or line_amount_vnd is null
   or line_amount_vnd <> quantity * unit_price_vnd

Test singular trả dòng vi phạm. Không trả dòng là pass. Quy tắc quantity dương và giá không âm phù hợp dữ liệu bán hàng lab; nếu doanh nghiệp dùng dòng âm cho trả hàng, phải thiết kế fact và quy tắc riêng thay vì áp test này vào rồi xóa giao dịch hợp lệ.

File models/staging/_relationships.yml

version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
  - name: stg_order_items
    columns:
      - name: order_item_id
        tests: [unique, not_null]
      - name: order_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_orders')
              field: order_id
      - name: product_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_products')
              field: product_id

File này bổ sung kiểm tra ngay quan hệ staging, hữu ích cho cả customer mart ở bài 4. Nếu bạn đã tự khai báo các model này trong properties file khác, gộp columns/tests vào đó; dbt không cho khai báo trùng cùng model ở nhiều file. _models.yml của bài 4 chỉ có hai mart và int_orders_enriched nên không xung đột với các file mới trong bài này.

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

Chạy build toàn project ở lần đầu để các relation mà tests tham chiếu đều được xây. Một upstream test fail có thể khiến tài nguyên downstream được chọn bị skip theo graph và chế độ selection; nó không rollback toàn bộ mọi bảng của project như một transaction chung. Nếu build lỗi, đừng cho dashboard dùng kết quả mới trước khi điều tra.

6. Truy vấn cùng một fact từ nhiều góc nhìn

Các SELECT tiếp theo chạy trong psql/DBeaver kết nối database retail. Khác với file model, chúng là SQL PostgreSQL đã có tên schema vật lý nên không chứa Jinja. Đầu tiên đối chiếu toàn cảnh:

select
    count(*) as paid_line_count,
    count(distinct order_id) as paid_order_count,
    sum(quantity) as paid_quantity,
    sum(line_amount_vnd) as paid_revenue_vnd
from dev_marts.fct_sales
where order_status = 'paid';

6.1. Doanh thu theo sản phẩm

select
    p.product_name,
    p.category,
    sum(f.quantity) as quantity,
    sum(f.line_amount_vnd) as revenue_vnd
from dev_marts.fct_sales as f
join dev_marts.dim_products as p
    on f.product_key = p.product_key
where f.order_status = 'paid'
group by p.product_name, p.category
order by p.product_name;

Giá bình quân có trọng số của tất cả sản phẩm paid là tổng tiền chia tổng quantity, khoảng 44.444,44 VND/đơn vị. AVG(unit_price_vnd) tính bình quân theo dòng hàng, không trọng số số lượng. Hai chỉ tiêu khác nhau dù tên “giá trung bình” có thể khiến người đọc tưởng giống nhau.

6.2. Doanh thu theo khách và thành phố

select
    c.customer_name,
    c.city,
    count(distinct f.order_id) as paid_orders,
    sum(f.line_amount_vnd) as revenue_vnd
from dev_marts.fct_sales as f
join dev_marts.dim_customers as c
    on f.customer_key = c.customer_key
where f.order_status = 'paid'
group by c.customer_name, c.city
order by c.customer_name;

Kết quả paid có An tại Ho Chi Minh: hai đơn, 350.000 VND; Binh tại Ha Noi: một đơn, 50.000 VND. Truy vấn bắt đầu từ fact và lọc paid nên Chi/Dung không xuất hiện. Nếu yêu cầu danh sách đủ mọi khách với doanh thu 0, bắt đầu từ dimension và LEFT JOIN một tập paid đã tổng hợp, giống bài 4.

6.3. Giữ đủ ngày bằng date dimension

with daily as (
    select
        date_key,
        count(distinct order_id) as paid_orders,
        sum(line_amount_vnd) as revenue_vnd
    from dev_marts.fct_sales
    where order_status = 'paid'
    group by date_key
)
select
    d.full_date,
    coalesce(s.paid_orders, 0) as paid_orders,
    coalesce(s.revenue_vnd, 0) as revenue_vnd
from dev_marts.dim_date as d
left join daily as s on d.date_key = s.date_key
order by d.full_date;

Bảng kết quả có 31 ngày. Ngày 1, 2, 3 lần lượt có 130.000, 220.000, 50.000 VND; các ngày còn lại là 0. Filter paid nằm trong CTE trước LEFT JOIN. Nếu đưa điều kiện WHERE f.order_status=paid sau khi LEFT JOIN trực tiếp fact, các dòng không có fact sẽ bị loại vì giá trị null không thỏa điều kiện.

7. Những bẫy thường gặp khi dùng mô hình sao

Trong ví dụ, category Stationery có 200.000 VND và Accessories có 200.000 VND. Tổng tiền cộng được về 400.000. Nhưng số đơn theo category không cộng đơn giản được vì đơn 1003 chứa cả Bag và Pen, thuộc hai category. Số tiền tại grain phù hợp có tính cộng được khác với chỉ tiêu distinct count.

8. Giới hạn lịch sử của dimension hiện tại

dim_customers hiện là bảng rebuild từ trạng thái hiện tại của nguồn. Nếu An chuyển từ Ho Chi Minh sang Ha Noi rồi build lại, các giao dịch cũ join theo customer_key cũng được phân loại về thành phố mới. Đó là góc nhìn hiện tại, không phải “thành phố của khách tại thời điểm bán hàng”.

Muốn giữ ngữ cảnh lịch sử cần mô hình phiên bản có hiệu lực từ/đến, khóa cho từng phiên bản và phép join theo thời gian giao dịch. Snapshot ở bài 8 sẽ hỗ trợ ghi nhận thay đổi nhưng không tự làm fact join đúng. Vẫn phải xác định khi nào dữ liệu đến muộn, bản nào áp dụng cho sự kiện và cách xử lý sửa lịch sử.

Tương tự, order_status trong fact là trạng thái tại lần build dựa trên nguồn hiện tại. Một đơn đang pending chuyển paid sẽ đổi chỉ tiêu sau lần chạy lại; bài chưa xây sổ sự kiện thanh toán bất biến. Trong production, cần chọn loại fact theo câu hỏi: giao dịch, snapshot định kỳ hay quá trình có nhiều mốc. Đừng gọi mọi bảng có số tiền là cùng một loại fact.

9. Bài tập nghiệm thu và kiểm tra lỗi cố ý

Kiểm tra số dòng: dim_customers=4, dim_products=3, dim_date=31, fct_sales=7. Kiểm tổng paid=400.000 VND và đối chiếu với mart_daily_revenue cùng mart_customer_revenue. Các kiểm tra khóa/quan hệ phải pass trên bộ dữ liệu gốc. Đây là mốc kết quả dự kiến, không phải số liệu benchmark hiệu năng.

Thử một lỗi rõ ràng trên database lab: chuyển product_id của item 7 sang mã chưa tồn tại 999. Build/test phải phát hiện quan hệ sai hoặc khóa product_key null. Sau thử nghiệm, hoàn nguyên và build lại. Không dùng INNER JOIN để làm test “pass” bằng cách xóa khỏi kết quả một dòng fact.

docker compose exec -T postgres psql -U dbt_lab -d retail \
  -c "update raw.order_items set product_id = 999 where order_item_id = 7;"

dbt build --profiles-dir profiles

docker compose exec -T postgres psql -U dbt_lab -d retail \
  -c "update raw.order_items set product_id = 10 where order_item_id = 7;"

dbt build --profiles-dir profiles

Lần build có dữ liệu lỗi có thể dừng/skip downstream do test upstream thất bại; như vậy bạn chưa chắc có một bảng fact mới chứa null để quan sát. Muốn nghiên cứu riêng hành vi join trên lab, dùng dbt run có chủ đích rồi dbt test và đọc output, sau đó hoàn nguyên. Phân biệt dữ liệu nguồn hiện tại, bảng đã xây lần trước và tài nguyên bị skip giúp tránh kết luận sai khi debug.

Sau năm bài, bạn đã có project SQL có cấu trúc, lineage, hai mart tổng hợp và một mô hình sao có kiểm tra. Bài 6 sẽ đi sâu cách thiết kế data test, phân loại lỗi và quyết định khi nào cần chặn pipeline thay vì chỉ ghi cảnh báo.

Tài liệu tham khảo

• Kimball Group: khai báo grain
• Kimball Group: fact và dimension
• dbt data tests
• dbt build
• PostgreSQL: generate_series