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.
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ồn | Hệ thống | Dữ liệu | Cách lấy | Tần suất | Chủ sở hữu |
|---|---|---|---|---|---|
| Bán hàng | ERP (PostgreSQL) | Đơn hàng, hoá đơn, đại lý | CDC hoặc truy vấn gia tăng | Liên tục / mỗi giờ | GĐ Kinh doanh |
| Kế toán | Phầ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 đêm | Hằng ngày | Kế toán trưởng |
| Ngân hàng | Cổng ngân hàng doanh nghiệp | Sao kê, lệnh chi | File qua SFTP hoặc API | Hằng ngày | Thủ quỹ |
| Tỷ giá | API ngân hàng/ nhà cung cấp dữ liệu | Tỷ giá USD/VND | API REST | Hằng ngày 16:00 | Phòng Tài chính |
| Hạn mức | Excel của phòng kinh doanh 😬 | Hạn mức, người duyệt | Chuyển sang form/ bảng có kiểm soát | Khi thay đổi | GĐ Kinh doanh |
| Nhân sự | HRM | Nhân viên, tài khoản ngân hàng | Xuất có kiểm soát, che dữ liệu cá nhân | Hằng tuần | Trưởng phòng Nhân sự |
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ểu | Cách làm | Ưu điểm | Nhược điểm | Dùng khi |
|---|---|---|---|---|
| Toàn bộ (full) | Mỗi lần đọc lại hết bảng | Đơn giản, luôn đúng | Chậm, tốn tài nguyên nguồn | Bảng nhỏ: danh mục đại lý, nhà cung cấp |
| Gia tăng theo watermark | Lấy dòng có updated_at/ID > mốc lần trước | Nhanh, nhẹ | Bỏ sót xoá; cần cột updated_at tin cậy | Bả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ấp | Phức tạp, cần quyền DBA | Bảng thay đổi thường xuyên, cần gần thời gian thực |
| API | Gọi REST/ GraphQL | Chuẩn, có tài liệu | Giới hạn tốc độ, phân trang, lỗi mạng | Tỷ giá, dữ liệu bên ngoài |
| File (SFTP/ email) | Nhận file theo lịch | Phổ biến với ngân hàng | Định dạng hay đổi, file thiếu/ trùng | Sao kê ngân hàng, đối tác |
🟡 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,453Lầ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.
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 metadata | Vì sao cần |
|---|---|
| _batch_id | Truy vết dòng thuộc lô nào; xoá/ chạy lại đúng một lô |
| _source | Biết dữ liệu đến từ đâu (lineage) |
| _ingested_at | Đo độ trễ, độ tươi dữ liệu (module 8) |
| _business_date | Ngày nghiệp vụ, khác ngày nạp; dùng để phân vùng và chạy bù |
| _file_name / _row_number | Vớ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.
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
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.
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.
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á.
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.
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.
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.
- 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
2. Lỗi nào NÊN retry?
A. HTTP 401 Unauthorized
B. HTTP 503 Service Unavailable
C. HTTP 400 Bad Request
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
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
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
🏋️ Bài tập thực hành
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
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
- Debezium DocumentationCDC mã nguồn mở cho PostgreSQL, MySQL, SQL ServerTài liệu
- PostgreSQL INSERT ... ON CONFLICTCú pháp upsertTài liệu
- Designing Data-Intensive ApplicationsNhật ký, sao chép, xử lý luồng (chương 11)Sách
✅ 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