DataOps Peach

Data Foundations · Medallion: Bronze và Silver · Bài 3/4

Chuẩn hoá kiểu và giá trị

Ép kiểu bằng TRY_CAST và đếm số NULL phát sinh, gộp nhiều định dạng ngày bằng COALESCE, rồi đưa giá trị phân loại về một cách viết.

Học tương tác →

TRY_CAST thay cho CAST

CAST('N/A' AS DECIMAL(18,2)) báo lỗi và dừng cả câu lệnh: một giá trị bẩn chặn 5020 dòng. TRY_CAST trả NULL cho giá trị không ép được và chạy tiếp.

Đổi lại, TRY_CAST không báo gì khi thất bại. Vì vậy sau mỗi lần ép kiểu phải đếm số NULL phát sinh. Con số đó là tín hiệu, không phải phiền toái:

Ví dụ: quên bỏ , và VND rồi TRY_CAST(unit_price AS DECIMAL(18,2)) thẳng thì ra 4540 NULL thay vì 305. Giá "2,310,000 VND" hợp lệ nhưng không phải số cho tới khi được làm sạch chuỗi:

TRY_CAST(replace(replace(unit_price, ',', ''), ' VND', '') AS DECIMAL(18,2))

Tiền dùng DECIMAL, không dùng DOUBLE: số thực dấu phẩy động có sai số làm tròn khi cộng dồn.

Nhiều định dạng ngày

order_ts có ba định dạng. TRY_STRPTIME(chuỗi, định_dạng) trả NULL khi chuỗi không khớp định dạng; COALESCE lấy kết quả khác NULL đầu tiên:

COALESCE(
  TRY_STRPTIME(order_ts, '%Y-%m-%d %H:%M:%S'),
  TRY_STRPTIME(order_ts, '%d/%m/%Y %H:%M'),
  TRY_STRPTIME(order_ts, '%Y-%m-%dT%H:%M:%S')
)

Sau đó đếm số dòng còn NULL. Còn NULL là còn định dạng chưa biết: bỏ định dạng thứ ba khỏi danh sách trên thì 1633 dòng ra NULL. Khi nguồn thêm định dạng thứ tư, phép đếm này là thứ báo cho bạn biết.

Chuẩn hoá giá trị phân loại

region có 9 cách viết cho 3 vùng: khác hoa thường, thừa khoảng trắng đầu cuối và giữa các từ. Ba hàm xử lý ba vấn đề:

Hàm Xử lý
trim(region) khoảng trắng đầu và cuối
regexp_replace(..., '\s+', ' ', 'g') nhiều khoảng trắng liền nhau thành một ('g': thay mọi chỗ khớp)
lower(...) hoa thường

Spark SQL và PostgreSQL có INITCAP để viết hoa chữ đầu mỗi từ; DuckDB không có hàm này, nên lab đưa về chữ thường. Viết kiểu nào không quan trọng bằng việc chỉ còn một kiểu.

Cách này không sửa được lỗi mất dấu: Hà Nội và Ha Noi vẫn là hai giá trị, cần một bảng ánh xạ. Hậu quả nếu bỏ qua: báo cáo theo vùng tách một vùng thành nhiều dòng, mỗi dòng doanh thu thấp, và giám đốc vùng có thể cắt ngân sách sai cả một quý.

Tương tự: status về lower(trim(status)) (còn 3 giá trị), channel rỗng về NULL bằng NULLIF(trim(channel), '') để chuỗi rỗng và NULL không thành hai nhóm.

flowchart LR
  D[_dedup_orders: chuỗi] -->|TRY_CAST, TRY_STRPTIME| T[_typed_orders: đúng kiểu]
  T --> N[Đếm NULL: 305 giá, 0 ngày]
  C[silver_customers] -->|trim, regexp_replace, lower| R[region: 3 giá trị]

Lệnh hay dùng

Lệnh Để làm gì
SELECT count(*) FROM t WHERE TRY_CAST(... AS DECIMAL(18,2)) IS NULL đếm giá trị không ép được trước khi ghi bảng
COALESCE(TRY_STRPTIME(c, fmt1), TRY_STRPTIME(c, fmt2)) đọc cột ngày có nhiều định dạng
count(*) FILTER (WHERE cột IS NULL) đếm NULL từng cột sau khi ép kiểu
DESCRIBE _typed_orders xem kiểu từng cột

Lỗi hay gặp

Học tương tác →