Data Engineering cho Phân tích Rủi ro 🏠
🏠 Trang chủ / Data Engineering cho Phân tích Rủi ro / Phần 1 · Thu thập & lưu trữ dữ liệu rủi ro / Module 4
📥 Module 4 / 18 · 1–2 tuần Cơ bản

Nguồn dữ liệu & thu nạp: batch, gia tăng, CDC và API

Lập bản đồ hệ thống nguồn của An Phát, chọn kiểu thu nạp phù hợp (toàn bộ, gia tăng, CDC, API, file), viết bước nạp gia tăng có watermark và giao dịch, upsert danh mục, gọi API tỷ giá có retry và lưu dữ liệu thô kèm metadata.

IngestionWatermarkIdempotencyCDCAPI
📌 Vì sao module này quan trọng

Mọi phân tích rủi ro chỉ tốt bằng dữ liệu đầu vào. Lỗi thu nạp thường im lặng: thiếu một ngày, nạp trùng một lô, bỏ sót bản ghi được cập nhật. Không ai thấy cho đến khi báo cáo công nợ lệch sổ cái hàng tỷ đồng. Một bước thu nạp đúng chuẩn phải luỹ đẳng, truy vết được và an toàn khi chạy lại.

🎯 Mục tiêu học tập

  • Lập bản đồ nguồn dữ liệu với chủ sở hữu, tần suất và cách truy cập
  • Chọn giữa nạp toàn bộ, gia tăng, CDC, API, file
  • Viết bước nạp gia tăng luỹ đẳng bằng watermark + giao dịch
  • Upsert danh mục bằng INSERT ... ON CONFLICT
  • Gọi API có retry, lưu dữ liệu thô kèm metadata thu nạp

🟢 Bản đồ nguồn dữ liệu của An Phát

NguồnHệ thốngDữ liệuCách lấyTần suấtChủ sở hữu
Bán hàngERP (PostgreSQL)Đơn hàng, hoá đơn, đại lýCDC hoặc truy vấn gia tăngLiên tục / mỗi giờGĐ Kinh doanh
Kế toánPhần mềm kế toán (SQL Server)Thu tiền, sổ cái, phải trảTruy vấn gia tăng hằng đêmHằng ngàyKế toán trưởng
Ngân hàngCổng ngân hàng doanh nghiệpSao kê, lệnh chiFile qua SFTP hoặc APIHằng ngàyThủ quỹ
Tỷ giáAPI ngân hàng/ nhà cung cấp dữ liệuTỷ giá USD/VNDAPI RESTHằng ngày 16:00Phòng Tài chính
Hạn mứcExcel của phòng kinh doanh 😬Hạn mức, người duyệtChuyển sang form/ bảng có kiểm soátKhi thay đổiGĐ Kinh doanh
Nhân sựHRMNhân viên, tài khoản ngân hàngXuất có kiểm soát, che dữ liệu cá nhânHằng tuầnTrưởng phòng Nhân sự
Nguồn nguy hiểm nhất là Excel

File Excel hạn mức không có lịch sử, ai cũng sửa được, không ai biết ai sửa lúc nào. Trước khi xây pipeline, hãy đề xuất chuyển nó thành bảng có nhật ký thay đổi (module 5, SCD2). Đây là thay đổi quy trình, không chỉ là kỹ thuật.

🟢 Chọn kiểu thu nạp

KiểuCách làmƯu điểmNhược điểmDùng khi
Toàn bộ (full)Mỗi lần đọc lại hết bảngĐơn giản, luôn đúngChậm, tốn tài nguyên nguồnBảng nhỏ: danh mục đại lý, nhà cung cấp
Gia tăng theo watermarkLấy dòng có updated_at/ID > mốc lần trướcNhanh, nhẹBỏ sót xoá; cần cột updated_at tin cậyBảng lớn chỉ thêm mới: hoá đơn, thanh toán
CDC (Change Data Capture)Đọc nhật ký giao dịch của DB (WAL/ binlog)Bắt cả update/ delete, độ trễ thấpPhức tạp, cần quyền DBABảng thay đổi thường xuyên, cần gần thời gian thực
APIGọi REST/ GraphQLChuẩn, có tài liệuGiới hạn tốc độ, phân trang, lỗi mạngTỷ giá, dữ liệu bên ngoài
File (SFTP/ email)Nhận file theo lịchPhổ biến với ngân hàngĐịnh dạng hay đổi, file thiếu/ trùngSao kê ngân hàng, đối tác
Đường đi chuẩn của một lô dữ liệu
NguồnERP, kế toán, ngân hàng
Trích xuấtfull / gia tăng / CDC / API
Landingfile thô nguyên trạng + metadata
Bronzebảng thô, chỉ thêm (append-only)
Silverchuẩn hoá, khử trùng lặp

🟡 Nạp gia tăng luỹ đẳng bằng watermark

Ý tưởng: lưu mốc lớn nhất đã nạp vào bảng etl_watermark. Mỗi lần chạy chỉ lấy phần lớn hơn mốc. Hai chi tiết quyết định chất lượng: cập nhật watermark trong cùng giao dịch với dữ liệu, và chạy lại không nạp trùng.

import duckdb

con = duckdb.connect()  # thực tế: duckdb.connect("warehouse.duckdb")
con.sql("CREATE TABLE stg_invoices AS SELECT * FROM 'invoices.csv' LIMIT 0")   # cùng cấu trúc, rỗng
con.sql("CREATE TABLE etl_watermark (table_name VARCHAR PRIMARY KEY, last_value DATE)")
con.sql("INSERT INTO etl_watermark VALUES ('invoices', DATE '1900-01-01')")


def load_increment(upto: str) -> int:
    # Nạp phần mới kể từ watermark tới `upto`. Chạy lại cùng `upto` sẽ không nạp trùng.
    (wm,) = con.execute("SELECT last_value FROM etl_watermark WHERE table_name = 'invoices'").fetchone()
    con.execute("BEGIN TRANSACTION")
    (n,) = con.execute('''
        INSERT INTO stg_invoices
        SELECT * FROM 'invoices.csv'
        WHERE invoice_date > ? AND invoice_date <= CAST(? AS DATE)
    ''', [wm, upto]).fetchone()
    con.execute("UPDATE etl_watermark SET last_value = GREATEST(last_value, CAST(? AS DATE)) "
                "WHERE table_name = 'invoices'", [upto])
    con.execute("COMMIT")      # dữ liệu và watermark cùng thành công hoặc cùng thất bại
    return n


for run, upto in enumerate(["2025-06-30", "2025-12-31", "2025-12-31"], 1):
    n = load_increment(upto)
    total = con.sql("SELECT COUNT(*) FROM stg_invoices").fetchone()[0]
    print(f"Lần chạy {run} (tới {upto}): nạp thêm {n:>6,} dòng | tổng {total:,}")
Lần chạy 1 (tới 2025-06-30): nạp thêm 18,022 dòng | tổng 18,022
Lần chạy 2 (tới 2025-12-31): nạp thêm  5,431 dòng | tổng 23,453
Lần chạy 3 (tới 2025-12-31): nạp thêm      0 dòng | tổng 23,453

Lần chạy thứ 3 (cùng mốc) nạp 0 dòng: đó là tính luỹ đẳng (idempotency). Nhờ vậy khi pipeline lỗi giữa chừng, bạn chỉ việc chạy lại mà không sợ nhân đôi doanh thu.

Cái bẫy của watermark theo ngày hoá đơn

Ví dụ trên lọc theo invoice_date cho dễ hiểu, nhưng ngày thanh toán thay đổi sau: hoá đơn tháng 11 được trả tháng 1. Nếu chỉ nạp theo invoice_date, bảng của bạn mãi mãi thấy hoá đơn đó “chưa trả”, và bảng tuổi nợ sai dần. Giải pháp: watermark theo cột updated_at do nguồn cập nhật mỗi khi dòng thay đổi, hoặc dùng CDC.

-- Phiên bản đúng với bảng có thể bị cập nhật: dựa vào updated_at và trừ lùi một khoảng an toàn
SELECT * FROM erp.invoices
WHERE updated_at >  :last_watermark - INTERVAL 10 MINUTE   -- bù cho giao dịch commit muộn
  AND updated_at <= :run_started_at
-- Sau đó MERGE/upsert theo invoice_id vào Bronze/Silver (khử trùng lặp do khoảng trừ lùi)

🟡 Upsert danh mục: chèn hoặc cập nhật

-- Upsert danh mục đại lý: dòng mới thì chèn, dòng đã có thì cập nhật
CREATE TABLE dim_dealer (
  dealer_id          VARCHAR PRIMARY KEY,
  segment            VARCHAR,
  credit_limit_mvnd  INTEGER
);
INSERT INTO dim_dealer SELECT dealer_id, segment, credit_limit_mvnd FROM 'dealers.csv';

-- Lô thay đổi từ hệ thống bán hàng: DL0001 được nâng hạn mức, DL9999 là đại lý mới
INSERT INTO dim_dealer VALUES ('DL0001', 'Cấp 2', 900), ('DL9999', 'Siêu thị mini', 300)
ON CONFLICT (dealer_id) DO UPDATE SET
  segment           = excluded.segment,
  credit_limit_mvnd = excluded.credit_limit_mvnd;

SELECT (SELECT COUNT(*) FROM dim_dealer) AS tong_dai_ly, dealer_id, segment, credit_limit_mvnd
FROM dim_dealer WHERE dealer_id IN ('DL0001', 'DL9999') ORDER BY dealer_id;
+-------------+-----------+---------------+-------------------+
| tong_dai_ly | dealer_id | segment       | credit_limit_mvnd |
+-------------+-----------+---------------+-------------------+
|         601 | DL0001    | Cấp 2         |               900 |
|         601 | DL9999    | Siêu thị mini |               300 |
+-------------+-----------+---------------+-------------------+
(2 dòng)

INSERT ... ON CONFLICT DO UPDATE chạy được trên PostgreSQL, DuckDB, SQLite. Trên SQL Server, Snowflake, Databricks dùng MERGE INTO. Lưu ý: upsert ghi đè giá trị cũ. Với hạn mức tín dụng, bạn cần giữ lịch sử, nên module 5 sẽ dùng SCD loại 2 thay vì upsert.

🟡 Gọi API tỷ giá có retry và lưu dữ liệu thô

import json, time, uuid, datetime as dt
import requests

API = "https://api.example-bank.vn/v1/fx-rates"     # minh hoạ: thay bằng nhà cung cấp của bạn


def fetch_rates(day: str, max_retries: int = 4) -> dict:
    for attempt in range(1, max_retries + 1):
        try:
            r = requests.get(API, params={"date": day, "pair": "USDVND"}, timeout=10)
            if r.status_code == 429 or r.status_code >= 500:      # quá tải/ lỗi máy chủ: thử lại
                raise requests.HTTPError(f"HTTP {r.status_code}")
            r.raise_for_status()                                  # lỗi 4xx khác: không thử lại
            return r.json()
        except (requests.ConnectionError, requests.Timeout, requests.HTTPError) as e:
            if attempt == max_retries:
                raise
            wait = 2 ** attempt                                   # backoff luỹ thừa: 2, 4, 8 giây
            print(f"Lần {attempt} lỗi: {e}. Thử lại sau {wait}s")
            time.sleep(wait)


def land_raw(payload: dict, day: str) -> str:
    # Lưu NGUYÊN TRẠNG kèm metadata: có thể xử lý lại bất cứ lúc nào
    record = {
        "_batch_id": str(uuid.uuid4()),
        "_source": "fx_api",
        "_ingested_at": dt.datetime.now(dt.timezone.utc).isoformat(),
        "_business_date": day,
        "payload": payload,
    }
    path = f"lake/landing/fx/business_date={day}/{record['_batch_id']}.json"
    # ... ghi file (local, S3, MinIO) ...
    return path
Cột metadataVì sao cần
_batch_idTruy vết dòng thuộc lô nào; xoá/ chạy lại đúng một lô
_sourceBiết dữ liệu đến từ đâu (lineage)
_ingested_atĐo độ trễ, độ tươi dữ liệu (module 8)
_business_dateNgày nghiệp vụ, khác ngày nạp; dùng để phân vùng và chạy bù
_file_name / _row_numberVới file: tìm lại đúng dòng trong file gốc khi có tranh chấp

🔴 CDC: bắt mọi thay đổi từ nhật ký cơ sở dữ liệu

CDC đọc nhật ký ghi trước (WAL của PostgreSQL, binlog của MySQL) và phát ra sự kiện cho mỗi thay đổi. Công cụ mã nguồn mở phổ biến là Debezium, thường đẩy sự kiện vào Kafka (module 9). Một sự kiện cập nhật hoá đơn trông như sau (rút gọn):

{
  "op": "u",                                   // c = tạo, u = cập nhật, d = xoá, r = ảnh chụp ban đầu
  "ts_ms": 1767225600000,
  "source": {"db": "erp", "table": "invoices", "lsn": 23991200},
  "before": {"invoice_id": "INV021877", "paid_date": null,         "amount_vnd": 128090000},
  "after":  {"invoice_id": "INV021877", "paid_date": "2026-01-06", "amount_vnd": 128090000}
}
  • Ghi mọi sự kiện vào Bronze (append-only), kể cả xoá, để tái dựng lịch sử.
  • Silver lấy phiên bản mới nhất theo khoá (ROW_NUMBER() OVER (PARTITION BY invoice_id ORDER BY lsn DESC) = 1), bỏ dòng có op = d.
  • Theo dõi độ trễ (lag) giữa thời điểm thay đổi ở nguồn và lúc vào Bronze; đặt cảnh báo khi vượt ngưỡng.
Khi nào chưa cần CDC

Nếu báo cáo rủi ro chỉ cần số đến hết hôm qua và bảng nguồn có updated_at tin cậy, nạp gia tăng hằng đêm là đủ và rẻ hơn nhiều. Đừng dùng CDC chỉ vì nó hiện đại.

⚠️ Sai lầm thường mắc

❌ Cập nhật watermark trước khi ghi dữ liệu xong

Vì sao nguy hiểm: Lỗi giữa chừng → mất vĩnh viễn một khoảng dữ liệu.

Cách làm đúng: Ghi dữ liệu và watermark trong cùng giao dịch, hoặc cập nhật watermark sau khi ghi thành công.

❌ Watermark theo cột không đổi (ngày tạo)

Vì sao nguy hiểm: Bỏ sót cập nhật (thanh toán, điều chỉnh).

Cách làm đúng: Dùng updated_at hoặc CDC.

❌ Chạy lại nạp trùng

Vì sao nguy hiểm: Doanh thu, công nợ bị nhân đôi.

Cách làm đúng: Thiết kế luỹ đẳng: xoá-rồi-ghi theo lô/ phân vùng hoặc upsert theo khoá.

❌ Biến đổi ngay khi nạp, không giữ bản thô

Vì sao nguy hiểm: Không thể xử lý lại khi phát hiện lỗi logic.

Cách làm đúng: Luôn lưu bản thô nguyên trạng ở landing/ Bronze.

❌ Retry mọi lỗi

Vì sao nguy hiểm: Lỗi 400/401 thử lại vô ích, còn bị khoá tài khoản API.

Cách làm đúng: Chỉ retry lỗi tạm thời (timeout, 429, 5xx), có backoff.

❌ Không theo dõi số dòng mỗi lô

Vì sao nguy hiểm: Nguồn trả rỗng vì lỗi nhưng pipeline vẫn “thành công”.

Cách làm đúng: So số dòng với lịch sử; cảnh báo khi lệch bất thường.

📌 Cần nhớ
  • Lập bản đồ nguồn: hệ thống, cách lấy, tần suất, chủ sở hữu.
  • Bảng nhỏ nạp toàn bộ; bảng lớn nạp gia tăng; cần update/delete → CDC.
  • Luỹ đẳng: chạy lại không nhân đôi; watermark cập nhật cùng giao dịch.
  • Lưu dữ liệu thô kèm _batch_id, _source, _ingested_at, _business_date.
  • Retry có chọn lọc với backoff luỹ thừa.

🧩 Tự kiểm tra (bấm để xem đáp án)

1. Bảng hoá đơn có paid_date được cập nhật sau. Nạp gia tăng theo invoice_date sẽ:
A. Đúng hoàn toàn
B. Bỏ sót cập nhật thanh toán
C. Nhân đôi dữ liệu
Đáp án: B. Dòng cũ thay đổi nhưng không được nạp lại.
2. Lỗi nào NÊN retry?
A. HTTP 401 Unauthorized
B. HTTP 503 Service Unavailable
C. HTTP 400 Bad Request
Đáp án: B. 503 là lỗi tạm thời phía máy chủ.
3. Tính luỹ đẳng nghĩa là:
A. Chạy nhanh hơn
B. Chạy lại nhiều lần cho cùng kết quả
C. Chạy song song
Đáp án: B. Định nghĩa idempotency.
4. CDC phù hợp nhất khi:
A. Danh mục 200 dòng đổi mỗi quý
B. Bảng lớn có update/delete thường xuyên, cần độ trễ thấp
C. File Excel
Đáp án: B. CDC bắt mọi thay đổi với độ trễ thấp.
5. Vì sao lưu _business_date riêng với _ingested_at?
A. Cho đẹp
B. Dữ liệu ngày 31/12 có thể nạp ngày 02/01; phân vùng và chạy bù theo ngày nghiệp vụ
C. Để tiết kiệm dung lượng
Đáp án: B. Hai khái niệm thời gian khác nhau.

🏋️ Bài tập thực hành

Nạp thanh toán nhà cung cấp theo từng ngày, an toàn khi chạy lại

Viết hàm load_day(day) nạp vendor_payments.csv của đúng một ngày vào bảng bronze_payments (DuckDB), kèm cột _batch_id, _ingested_at, _business_date. Chạy cho cả tháng 3/2025, rồi chạy lại ngày 15/03 ba lần: tổng số dòng không được thay đổi.

💡 Gợi ý lời giải
Mẫu “xoá rồi ghi theo phân vùng”: trong một giao dịch, DELETE FROM bronze_payments WHERE _business_date = ? rồi INSERT ... SELECT ..., ? AS _business_date FROM 'vendor_payments.csv' WHERE payment_date = ?. Kiểm tra cuối: SELECT COUNT(*), COUNT(DISTINCT payment_id) phải bằng nhau.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Lập bản đồ nguồn cho công ty bạn
  • Chạy được ví dụ watermark
  • Hiểu vì sao watermark theo ngày tạo bỏ sót cập nhật
  • Viết upsert
  • Làm bài tập nạp theo ngày luỹ đẳng
Lưu ý: đây là tài liệu giáo dục. Công ty An Phát Distribution, đại lý, nhà cung cấp, nhân viên và mọi số liệu đều là giả định do code sinh ra, không đại diện cho doanh nghiệp thật. Phương pháp (scorecard, ECL, VaR, Benford...) được đơn giản hoá cho mục đích học; khi áp dụng thật, hãy đối chiếu chuẩn mực kế toán (VAS/ IFRS 9), quy định pháp luật hiện hành (ví dụ về bảo vệ dữ liệu cá nhân) và chính sách nội bộ của tổ chức bạn.