Snowflake 4 — SQL & dữ liệu bán cấu trúc (VARIANT)

Snowflake SQL — ANSI cộng phần mở rộng

Snowflake nói SQL chuẩn ANSI: SELECT, JOIN, CTE (WITH), window function, GROUP BY, subquery... đều hoạt động y như bạn quen ở PostgreSQL hay các warehouse khác. Ai chuyển từ Postgres sang thường viết được ngay 90% truy vấn mà không sửa gì. Nhưng phần còn lại — 10% khác biệt — mới là nơi Snowflake tạo ra giá trị, đặc biệt khi làm việc với dữ liệu bán cấu trúc (semi-structured: JSON, Avro, Parquet, ORC, XML).

Có vài điểm phương ngữ cần nhớ ngay, vì chúng khác Postgres:

• Không phân biệt hoa/thường mặc định: SELECT name và SELECT NAME là một; tên object viết không nháy sẽ bị upper-case khi lưu vào metadata. Muốn giữ nguyên hoa/thường phải nháy kép "MyColumn" — và khi đó phải luôn nháy khi tham chiếu.
• ILIKE cho so khớp không phân biệt hoa/thường, RLIKE/REGEXP cho biểu thức chính quy.
• QUALIFY — lọc theo kết quả window function ngay trong cùng câu, không cần bọc subquery (chi tiết bên dưới).
• :: để ép kiểu (giống Postgres), nhưng đây là công cụ chủ lực khi rút giá trị ra khỏi VARIANT.
• Cú pháp truy cập JSON bằng colon : và dot . — đây là điểm mạnh nhất và cũng lạ nhất so với SQL truyền thống.

Toàn bộ SQL trong bài là phương ngữ Snowflake, không chạy được trên sandbox PostgreSQL của Knowledge Base. Đọc để hiểu cú pháp, đừng dán thẳng vào ô "Chạy được".

Bộ ba kiểu bán cấu trúc: VARIANT, OBJECT, ARRAY

Snowflake xử lý dữ liệu bán cấu trúc bằng ba kiểu dữ liệu gốc (native), không cần định nghĩa schema trước:

Điểm cốt lõi: VARIANT lưu dữ liệu ở dạng cột nén nội bộ, không phải chuỗi text. Khi bạn nạp một document JSON vào cột VARIANT, Snowflake tự phân tích cấu trúc, tách các "path" con thành các cột phụ được nén và đánh chỉ mục thống kê riêng. Nhờ vậy truy vấn payload:customer.id chỉ đọc đúng phần dữ liệu cần, không quét toàn bộ blob — hiệu năng gần bằng cột quan hệ thường, khác hẳn việc lưu JSON dưới dạng TEXT rồi parse mỗi lần đọc.

Giới hạn thực tế cần nhớ: một giá trị VARIANT tối đa 16 MB sau khi nén. Log hoặc payload quá lớn phải tách nhỏ trước khi nạp.

Nạp JSON/Parquet vào VARIANT

Cách phổ biến nhất là định nghĩa một bảng có đúng một cột VARIANT, rồi COPY INTO từ file trên stage. Với JSON:

-- Bảng landing thô cho log giao dịch core banking
CREATE TABLE raw_txn_log (
    v          VARIANT,
    loaded_at  TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- File format JSON
CREATE FILE FORMAT ff_json TYPE = JSON STRIP_OUTER_ARRAY = TRUE;

-- Nạp toàn bộ file .json.gz trên stage vào cột VARIANT
COPY INTO raw_txn_log (v)
FROM @stg_txn/2026/06/30/
FILE_FORMAT = (FORMAT_NAME = ff_json)
ON_ERROR = 'CONTINUE';

STRIP_OUTER_ARRAY = TRUE bung một file JSON dạng mảng [{...},{...}] thành nhiều hàng, mỗi object một hàng — cách nạp log rất tự nhiên. Không có nó, cả file thành một hàng VARIANT duy nhất chứa cả mảng.

Với Parquet, Snowflake đọc luôn schema từ file. Bạn có thể nạp cả record vào một cột VARIANT rồi bung sau, hoặc map thẳng từng cột:

CREATE FILE FORMAT ff_parquet TYPE = PARQUET;

-- Cách A: nạp cả record thành VARIANT
COPY INTO raw_parquet (v)
FROM @stg_pq/txn/
FILE_FORMAT = (FORMAT_NAME = ff_parquet);

-- Cách B: map thẳng từng field khi COPY (ép kiểu ngay lúc nạp)
COPY INTO txn (account_no, amount, kind)
FROM (
    SELECT $1:account_no::STRING,
           $1:amount::NUMBER(18,2),
           $1:kind::STRING
    FROM @stg_pq/txn/
)
FILE_FORMAT = (FORMAT_NAME = ff_parquet);

Ký hiệu $1 là cột đầu tiên của file staged; với Parquet nó chính là một VARIANT chứa toàn bộ record, nên $1:account_no truy cập field bên trong.

Toán tử truy cập: colon, dot, ngoặc vuông

Đây là phần "khác biệt hạt nhân" của Snowflake SQL. Cho một cột VARIANT tên v chứa:

{
  "txn_id": "T-90231",
  "account": { "no": "0110123456", "currency": "VND" },
  "amount": 15000000,
  "channel": "MOBILE",
  "tags": ["salary", "recurring"],
  "legs": [
    { "type": "DEBIT",  "acc": "0110123456", "amt": 15000000 },
    { "type": "CREDIT", "acc": "0250998877", "amt": 15000000 }
  ]
}

Các cách truy cập:

• Colon : rút một key ở cấp cao nhất: v:txn_id, v:amount, v:channel.
• Dot . đi sâu vào object lồng: v:account.no, v:account.currency. (Có thể viết v:account:no cũng được — dot và colon dùng lẫn nhau khi đi sâu.)
• Ngoặc vuông [ ] lấy phần tử mảng theo chỉ số (đếm từ 0): v:tags[0], v:legs[1].type.

Điểm mấu chốt: kết quả của mọi phép truy cập trên là kiểu VARIANT, không phải kiểu vô hướng. v:amount không phải là số 15000000 mà là một VARIANT bọc số đó. Nếu in ra chuỗi, giá trị text sẽ có dấu nháy kép (ví dụ "MOBILE") và so sánh/tính toán sẽ hành xử lạ. Vì vậy hầu như luôn phải ép kiểu bằng :::

SELECT
    v:txn_id::STRING            AS txn_id,
    v:account.no::STRING        AS account_no,
    v:account.currency::STRING  AS currency,
    v:amount::NUMBER(18,2)      AS amount,
    v:channel::STRING           AS channel,
    v:tags[0]::STRING           AS first_tag
FROM raw_txn_log;

Nếu path không tồn tại, kết quả là NULL (SQL NULL) — không lỗi. Điều này khiến VARIANT rất bền với schema thay đổi: field mới xuất hiện không làm hỏng pipeline cũ, field cũ biến mất chỉ cho ra NULL.

FLATTEN và LATERAL FLATTEN — bung mảng lồng

Vấn đề lớn nhất với JSON là mảng lồng: một giao dịch có nhiều "leg" (bút toán nợ/có), một khách hàng có nhiều số điện thoại, một log có mảng sự kiện con. Để phân tích quan hệ, phải "bung" mảng thành nhiều hàng — đây là việc của FLATTEN, một table function.

FLATTEN nhận một mảng (hoặc object) và trả về một bảng, mỗi phần tử một hàng, kèm các cột metadata:

Dùng với LATERAL để nối mảng của mỗi hàng cha với chính hàng đó — mỗi hàng giao dịch nở ra thành nhiều hàng leg:

SELECT
    t.v:txn_id::STRING       AS txn_id,
    t.v:channel::STRING      AS channel,
    leg.value:type::STRING   AS leg_type,
    leg.value:acc::STRING    AS leg_account,
    leg.value:amt::NUMBER(18,2) AS leg_amount
FROM raw_txn_log AS t,
     LATERAL FLATTEN(input => t.v:legs) AS leg;

Với payload ví dụ ở trên, một hàng T-90231 cho ra hai hàng: một DEBIT và một CREDIT. leg.value là phần tử mảng (VARIANT), rồi truy cập tiếp :type, :acc, :amt và ép kiểu như thường.

FLATTEN còn nhận OUTER => TRUE (giữ hàng cha cả khi mảng rỗng/null, giống LEFT JOIN — nếu không, hàng có mảng rỗng bị mất) và RECURSIVE => TRUE (bung đệ quy toàn bộ cây lồng). Để bung nhiều tầng, xâu chuỗi nhiều LATERAL FLATTEN:

SELECT p.value:id::STRING AS payment_id,
       item.value:sku::STRING AS sku
FROM raw_batch AS b,
     LATERAL FLATTEN(input => b.v:payments) AS p,
     LATERAL FLATTEN(input => p.value:items) AS item;

Sơ đồ luồng: JSON thô → VARIANT → FLATTEN → bảng phẳng

Luồng chuẩn ELT trên Snowflake: nạp thô vào VARIANT (giữ nguyên, không mất dữ liệu), rồi biến đổi bằng SQL thành các bảng phẳng có schema rõ ràng để phân tích — thường hiện thực hoá bằng CREATE TABLE ... AS SELECT (CTAS) hoặc bằng dbt model.

PARSE_JSON và OBJECT_CONSTRUCT

Hai hàm ngược nhau, rất hay dùng:

• PARSE_JSON(text) — chuyển một chuỗi JSON thành VARIANT. Dùng khi dữ liệu JSON đang nằm ở cột STRING (ví dụ đã nạp nhầm dạng text, hoặc JSON đến từ một cột log của hệ khác):

SELECT PARSE_JSON(raw_message):event.type::STRING AS event_type
FROM audit_log_text;

Lưu ý phân biệt với TO_JSON (VARIANT → chuỗi) và TRY_PARSE_JSON (trả NULL thay vì lỗi khi chuỗi không hợp lệ — an toàn cho dữ liệu bẩn).

• OBJECT_CONSTRUCT(k1, v1, k2, v2, ...) — dựng một OBJECT/VARIANT từ các cột phẳng. Dùng khi cần gói dữ liệu quan hệ lại thành JSON để xuất ra hệ khác hoặc gọi API:

SELECT OBJECT_CONSTRUCT(
    'account_no', a.account_no,
    'currency',   a.currency,
    'balance',    a.balance
) AS account_json
FROM account a;

Cặp đôi này cho phép đi tự do giữa "thế giới cột" và "thế giới JSON" ngay trong SQL — cực tiện khi Snowflake vừa là kho phân tích vừa là nơi sinh payload cho downstream.

QUALIFY với window function

QUALIFY là mệnh đề lọc theo kết quả của window function, chạy sau SELECT/WINDOW. Không có nó, bạn phải bọc một subquery/CTE rồi mới WHERE được. Ví dụ kinh điển: lấy giao dịch mới nhất mỗi tài khoản.

SELECT account_no, amount, kind, created_at
FROM fact_transaction
QUALIFY ROW_NUMBER() OVER (
            PARTITION BY account_no
            ORDER BY created_at DESC
        ) = 1;

So với cách viết Postgres truyền thống phải dùng subquery lồng, QUALIFY gọn hơn hẳn và đọc thẳng ý định. Nó dùng được với ROW_NUMBER, RANK, SUM() OVER (...), v.v. Đây là một trong những mở rộng được ưa dùng nhất của Snowflake (cũng có ở BigQuery, xem Tổng quan BigQuery).

Các hàm hữu ích: LISTAGG, IFF, DECODE

IFF(cond, a, b) — biểu thức điều kiện ngắn gọn, tương đương CASE WHEN cond THEN a ELSE b END:

SELECT txn_id,
       IFF(amount >= 400000000, 'LARGE', 'NORMAL') AS bucket
FROM fact_transaction;

DECODE(expr, s1, r1, s2, r2, ..., default) — ánh xạ giá trị, kiểu như switch. Rất tiện để dịch mã nghiệp vụ:

SELECT DECODE(channel,
              'MOBILE',  'Mobile Banking',
              'ATM',     'ATM/CDM',
              'BRANCH',  'Quầy giao dịch',
              'Khác') AS channel_name
FROM fact_transaction;

LISTAGG(expr, delim) — gộp nhiều hàng thành một chuỗi, tương tự string_agg của Postgres. Kèm WITHIN GROUP (ORDER BY ...) để sắp thứ tự:

SELECT account_no,
       LISTAGG(kind, ', ') WITHIN GROUP (ORDER BY created_at) AS kind_sequence
FROM fact_transaction
GROUP BY account_no;

Nhóm hàm null-safe cũng đáng nhớ: NVL/COALESCE (thay NULL), NVL2, ZEROIFNULL, và EQUAL_NULL (so sánh coi NULL bằng NULL). Về mô hình hoá dữ liệu sau khi phẳng hoá, xem thêm Kimball & mô hình chiều.

Use case thực tế

Bối cảnh. Hệ core banking của NCB đẩy log giao dịch dạng JSON lên object storage, mỗi file gộp một mảng các giao dịch của một khung 5 phút; trung bình ~2,4 triệu giao dịch/ngày, mỗi giao dịch có mảng legs gồm 2–4 bút toán. Team dữ liệu cần bảng phẳng fact_transaction_leg cho báo cáo AML và đối soát cuối ngày, nhưng schema payload thỉnh thoảng thêm field mới (channel mới, cờ mới) mà không báo trước.

Cách làm — 4 bước:

1. Landing thô. Tạo bảng raw_txn_log(v VARIANT, loaded_at) và COPY INTO ... STRIP_OUTER_ARRAY = TRUE từ stage. Mỗi giao dịch thành một hàng VARIANT; field mới xuất hiện không làm hỏng bước này vì không có schema cứng.
2. Phẳng hoá header. CTAS ra fact_transaction rút v:txn_id::STRING, v:account.no::STRING, v:amount::NUMBER(18,2), v:channel::STRING, v:ts::TIMESTAMP_NTZ. Dùng TRY_TO_NUMBER/TRY_TO_TIMESTAMP cho các field dễ bẩn để hàng lỗi ra NULL thay vì fail cả batch.
3. Bung leg. LATERAL FLATTEN(input => v:legs, OUTER => TRUE) sinh fact_transaction_leg — mỗi bút toán một hàng, giữ được cả giao dịch có mảng leg rỗng nhờ OUTER => TRUE. Kết quả ~6–8 triệu hàng leg/ngày.
4. Lớp phân tích. Dùng QUALIFY ROW_NUMBER() để chống trùng (một file bị nạp lại 2 lần chỉ giữ bản mới nhất theo loaded_at), IFF(amount >= 400000000, ...) gắn cờ giao dịch giá trị lớn theo ngưỡng báo cáo, và LISTAGG gộp chuỗi kênh giao dịch trong ngày của mỗi tài khoản để phát hiện hành vi bất thường.

Kết quả. Toàn bộ biến đổi là SQL chạy trong warehouse, không cần cụm Spark riêng. Khi payload thêm field device_fingerprint, chỉ cần thêm một dòng v:device_fingerprint::STRING vào model — dữ liệu thô đã nằm sẵn trong VARIANT từ lúc nạp, không phải nạp lại. Đây chính là lợi thế "schema-on-read" của VARIANT so với việc ép schema cứng ngay ở cổng vào.

Ghi nhớ

• Snowflake SQL là ANSI + mở rộng; khác biệt cốt lõi nằm ở xử lý bán cấu trúc. Tên object không nháy bị upper-case và không phân biệt hoa/thường.
• VARIANT/OBJECT/ARRAY là ba kiểu gốc; VARIANT lưu nén theo cột (không phải text), truy vấn path hiệu quả gần bằng cột quan hệ. Giới hạn 16 MB/giá trị.
• Nạp JSON/Parquet thẳng vào một cột VARIANT bằng COPY INTO; STRIP_OUTER_ARRAY = TRUE biến mảng ngoài cùng thành nhiều hàng.
• Truy cập bằng colon : (key cấp cao), dot . (đi sâu), [ ] (chỉ số mảng); kết quả luôn là VARIANT nên gần như luôn phải ép kiểu ::. Path không có → NULL, không lỗi.
• LATERAL FLATTEN bung mảng lồng thành nhiều hàng; dùng OUTER => TRUE để giữ hàng cha khi mảng rỗng, xâu chuỗi nhiều FLATTEN cho nhiều tầng.
• PARSE_JSON/TRY_PARSE_JSON (text → VARIANT) và OBJECT_CONSTRUCT (cột → JSON) cho phép đi lại tự do giữa hai thế giới.
• QUALIFY lọc theo window function không cần subquery; IFF, DECODE, LISTAGG giúp SQL gọn và diễn đạt nghiệp vụ trực tiếp.
• VARIANT cho lợi thế schema-on-read: field mới trong payload không làm hỏng pipeline cũ, không phải nạp lại dữ liệu.