Looker 5 — Derived tables & PDT

Khi một view không đến từ bảng thật

Ở Looker 3 — Explores & joins và Looker 4 — Dimensions & measures, mọi view đều ánh xạ tới một bảng có sẵn trong warehouse qua sql_table_name. Nhưng nghiệp vụ thực tế không phải lúc nào cũng gọn thế. Bạn cần một "bảng" chỉ tồn tại trong đầu bạn: doanh thu đã gộp theo ngày, một danh sách khách hàng đã khử trùng lặp, một phép tự-join phức tạp để tính first-order của mỗi khách. Warehouse không có sẵn bảng đó, và bạn cũng không muốn nhờ đội data engineer tạo một bảng vật lý cho mỗi ý tưởng phân tích.

Derived table (bảng dẫn xuất) là câu trả lời của Looker. Đó là một view mà nguồn là một truy vấn, không phải một bảng thật. Bạn định nghĩa "bảng ảo" này bằng LookML, và Looker lo phần còn lại — hoặc gói nó thành subquery mỗi lần chạy, hoặc vật chất hoá nó thành một bảng thật trong warehouse để dùng lại. Bài này đi qua toàn bộ phổ đó: từ derived table nhẹ nhàng cho tới PDT (Persistent Derived Table) và incremental PDT cho những bảng khổng lồ.

Quy ước không đổi: Looker là công cụ BI, không phải SQL sandbox. Mọi đoạn LookML dưới đây là minh hoạ khái niệm.

Hai cách định nghĩa: SQL-based và native

Một derived table có thể được mô tả theo hai kiểu, và lựa chọn giữa chúng là quyết định thiết kế đầu tiên.

SQL-based derived table — bạn viết thẳng câu SQL vào tham số sql bên trong khối derived_table. Đây là cách linh hoạt nhất: bất cứ điều gì viết được bằng SQL của warehouse thì viết được ở đây, kể cả window function, self-join, hay cú pháp đặc thù của BigQuery. Quy tắc bắt buộc: mỗi cột phải có alias sạch bằng AS, vì sau này bạn sẽ tham chiếu cột qua ${TABLE}.column_name.

# (minh hoạ) SQL-based derived table: doanh thu gộp theo ngày
view: daily_revenue {
  derived_table: {
    sql:
      SELECT
        DATE(o.created_at)          AS order_date,
        o.region                    AS region,
        COUNT(DISTINCT o.order_id)  AS order_count,
        SUM(o.amount)               AS gross_revenue
      FROM orders AS o
      WHERE o.status = 'completed'
      GROUP BY 1, 2 ;;
  }

  dimension: order_date { type: date sql: ${TABLE}.order_date ;; }
  dimension: region     { type: string sql: ${TABLE}.region ;; }
  measure: total_revenue {
    type: sum
    sql: ${TABLE}.gross_revenue ;;
  }
}

Native derived table (NDT) — thay vì viết SQL, bạn dựng bảng từ chính mô hình LookML đã có, qua tham số explore_source. Bạn chỉ định một explore làm nguồn rồi liệt kê các column là dimension/measure đã định nghĩa ở nơi khác. Looker sẽ tự sinh SQL dựa trên các join và định nghĩa field đó.

# (minh hoạ) native derived table dựng từ explore đã có
view: customer_lifetime {
  derived_table: {
    explore_source: order {
      column: customer_id { field: order.customer_id }
      column: lifetime_revenue { field: order.total_revenue }
      column: first_order_date { field: order.min_order_date }
    }
  }
  dimension: customer_id { type: number sql: ${TABLE}.customer_id ;; }
  measure: lifetime_revenue { type: sum sql: ${TABLE}.lifetime_revenue ;; }
}

Ưu điểm lớn của NDT: nó dùng lại logic đã mô hình hoá. Nếu bạn sửa định nghĩa total_revenue ở view gốc, NDT cũng cập nhật theo — không có SQL lặp lại để đồng bộ thủ công. Nhược điểm: khó biểu diễn những phép biến đổi mà LookML không diễn đạt trực tiếp được; khi ấy SQL-based vẫn là lối thoát. Kinh nghiệm chung: ưu tiên NDT khi logic đã sống trong mô hình, rơi về SQL-based khi cần cú pháp warehouse thô.

Ephemeral: subquery tạm mỗi lần chạy

Mặc định, một derived table là ephemeral (còn gọi là in-query hoặc temporary). Nghĩa là nó không được lưu ở đâu cả. Mỗi lần có người query một explore chạm tới nó, Looker gói toàn bộ định nghĩa vào một subquery hoặc CTE, ghép với các field và filter mà người dùng chọn, rồi gửi một câu SQL lớn xuống warehouse. Chạy xong là biến mất.

Điều này phù hợp cho những bảng dẫn xuất nhẹ: một phép gộp nhỏ, một danh sách lọc đơn giản, thứ mà warehouse tính lại trong tích tắc. Ưu điểm là dữ liệu luôn tươi (tính từ bảng gốc ngay lúc query) và không chiếm storage. Nhược điểm lộ ra khi truy vấn nặng: nếu derived table quét hàng trăm triệu dòng để gộp, thì mỗi lượt xem dashboard đều trả cái giá tính toán đó. Đây chính là ranh giới để nghĩ tới persistence.

PDT: vật chất hoá vào warehouse

Persistent Derived Table (PDT) biến bảng ảo thành bảng thật. Looker chạy truy vấn định nghĩa một lần, ghi kết quả vào một scratch schema (schema tạm chuyên dụng) trong warehouse, rồi mọi query sau đó đọc thẳng từ bảng đã vật chất hoá đó thay vì tính lại. Kết quả: explore nhanh hơn nhiều, và tải tính toán nặng chỉ trả một lần cho mỗi chu kỳ làm mới thay vì mỗi lượt xem.

Điều kiện tiên quyết về hạ tầng:

• Scratch schema — một schema riêng mà database user của Looker có quyền tạo và ghi bảng. Đây là nơi các PDT nằm.
• Bật PDT trên connection — thiết lập kết nối tới warehouse phải bật tính năng PDT và trỏ đúng scratch schema.
• Dialect hỗ trợ — hầu hết warehouse lớn (BigQuery, Snowflake, Redshift, PostgreSQL, Databricks...) đều hỗ trợ; chi tiết dialect nào bật được incremental thì tra bảng tương thích trong tài liệu Looker.

Biến một derived table thành PDT không cần đổi câu SQL — chỉ cần thêm một chiến lược persistence vào khối derived_table. Đó là chủ đề của mục kế tiếp.

Làm mới PDT: persist_for vs trigger

Một PDT đã vật chất hoá sẽ cũ dần so với bảng gốc. Looker cung cấp vài chiến lược để quyết định khi nào xây lại nó:

• persist_for — giữ bảng trong một khoảng, ví dụ "8 hours". Bảng chỉ được xây lại khi lần đầu bị query sau khi hết hạn. Điểm yếu: người dùng xui xẻo chạm vào ngay lúc hết hạn sẽ phải chờ bảng build xong. Đơn giản nhưng không dự đoán được độ trễ.
• datagroup_trigger — gắn PDT vào một datagroup; khi datagroup kích hoạt, Looker xây lại bảng ở nền và giữ bản cũ phục vụ cho tới khi bản mới sẵn sàng. Người dùng không phải chờ. Đây là chiến lược được khuyến nghị cho production.
• sql_trigger_value — cung cấp một câu SQL trả về đúng một dòng, một cột. Khi giá trị đó khác lần chạy trước, PDT xây lại. Ví dụ kinh điển: trả về MAX(updated_at) của bảng nguồn — có dữ liệu mới thì giá trị đổi, PDT tự làm mới.
• interval_trigger — xây lại theo chu kỳ thời gian cố định, ví dụ "24 hours".
• materialized_view: yes — uỷ thác việc duy trì cho materialized view do chính database quản lý (tuỳ dialect).

Trong đó, datagroup là cơ chế đáng đầu tư nhất vì nó thống nhất cả làm mới PDT lẫn caching. Một datagroup khai báo một điều kiện kích hoạt (sql_trigger hoặc interval_trigger) và tuỳ chọn max_cache_age:

# (minh hoạ) datagroup dùng chung cho PDT và caching
datagroup: orders_daily {
  sql_trigger: SELECT CURRENT_DATE() ;;   # đổi giá trị mỗi ngày -> kích hoạt lúc nửa đêm
  max_cache_age: "24 hours"               # lưới an toàn cho query cache
}

view: daily_revenue {
  derived_table: {
    datagroup_trigger: orders_daily        # PDT làm mới theo datagroup
    sql:
      SELECT DATE(created_at) AS order_date,
             SUM(amount)       AS gross_revenue
      FROM orders
      GROUP BY 1 ;;
  }
  # ... dimensions & measures ...
}

Cùng datagroup orders_daily đó có thể được các explore tham chiếu qua persist_with để query cache của chúng cũng hết hạn đúng lúc PDT làm mới. Nhờ vậy PDT, cache và dashboard cùng "thở" theo một nhịp: nửa đêm datagroup kích hoạt → PDT xây lại → cache cũ bị vô hiệu → sáng ra dashboard đọc dữ liệu tươi. Lưu ý một điểm tinh tế: cú pháp ${derived_table.SQL_TABLE_NAME} để nối các PDT phụ thuộc không dùng được trong sql_trigger của datagroup, nhưng dùng được trong sql_trigger_value.

Sơ đồ: ephemeral vs persistent + nhịp làm mới

Incremental PDT: chỉ xây phần mới

Với bảng nhỏ, xây lại toàn bộ PDT mỗi đêm là chấp nhận được. Với bảng hàng tỷ dòng — ví dụ fact giao dịch nhiều năm — rebuild toàn phần vừa chậm vừa đốt tiền warehouse. Incremental PDT giải quyết đúng bài này: sau lần build đầy đủ đầu tiên, mỗi lần kích hoạt Looker chỉ xây thêm phần dữ liệu mới theo một cột thời gian, rồi INSERT/MERGE vào bảng có sẵn thay vì dựng lại từ đầu.

Hai tham số cốt lõi:

• increment_key (bắt buộc) — định nghĩa đơn vị thời gian để xác định "phần mới": ví dụ theo date, week, hay month. Looker nhìn increment gần nhất đã có trong bảng và chỉ tính từ đó về sau.
• increment_offset (tuỳ chọn) — một số nguyên cho biết bao nhiêu chu kỳ trước đó cũng được xây lại cùng phần mới, để xử lý dữ liệu về muộn (late-arriving data). Ví dụ increment_key: date với increment_offset: 3 thì mỗi lần kích hoạt sẽ xây lại 3 ngày gần nhất cộng ngày mới — tổng 4 ngày — phòng khi giao dịch của mấy hôm trước mới được cập nhật.

Với SQL-based PDT, bạn còn cần một filter Liquid {% incrementcondition %} để nối increment_key với đúng cột thời gian trong câu SQL, giúp Looker biết chèn điều kiện lọc thời gian vào đâu.

# (minh hoạ) incremental PDT cho fact giao dịch lớn
view: transactions_agg {
  derived_table: {
    datagroup_trigger: orders_daily
    increment_key: "transaction_date"
    increment_offset: 3
    sql:
      SELECT
        DATE(t.txn_ts)   AS transaction_date,
        t.branch_id      AS branch_id,
        SUM(t.amount)    AS total_amount,
        COUNT(*)         AS txn_count
      FROM transactions AS t
      WHERE {% incrementcondition %} t.txn_ts {% endincrementcondition %}
      GROUP BY 1, 2 ;;
  }
  # ... dimensions & measures ...
}

Ràng buộc cần nhớ:

• Chỉ SQL-based PDT dùng tham số sql mới incremental được (không áp dụng cho sql_create hay create_process).
• Bắt buộc dùng chiến lược trigger-based (datagroup_trigger, sql_trigger_value, hoặc interval_trigger) — persist_for không tương thích với incremental.
• Dialect phải hỗ trợ (BigQuery, Snowflake, Redshift, PostgreSQL, Databricks 12.1+...).
• Nên partition/sort/index cột thời gian ở bảng nguồn để phần tính increment thật sự nhanh.

Cần phân vai rõ: datagroup quyết định khi nào làm mới, còn increment_key quyết định phạm vi nào được xây lại trong lần làm mới đó. Hai thứ hoạt động độc lập.

PDT khác gì với caching của Looker

Dễ nhầm hai khái niệm cùng nhằm mục đích "nhanh hơn" này:

• Query cache là kết quả của một câu query cụ thể (một tổ hợp field + filter), lưu trong hạ tầng của Looker, hết hạn theo max_cache_age hoặc khi datagroup kích hoạt. Nó không phải là dữ liệu trong warehouse; nó là kết quả đã tính sẵn của một truy vấn.
• PDT là một bảng vật chất hoá thật trong warehouse, có thể được nhiều query khác nhau (nhiều tổ hợp field/filter) đọc chung.

Nói ngắn: cache tăng tốc lặp lại đúng một query; PDT tăng tốc mọi query chạm vào cùng một khối tổng hợp. Trong thực tế bạn dùng cả hai, và datagroup là sợi dây buộc chúng đồng bộ.

Khi nào nên (và không nên) dùng PDT

PDT xứng đáng khi:

• Một khối tổng hợp nặng được tái dùng nhiều — nhiều explore/dashboard cùng đọc "doanh thu theo ngày theo chi nhánh".
• Truy vấn ephemeral chậm tới mức làm hỏng trải nghiệm dashboard.
• Bạn muốn cắt chi phí warehouse: trên BigQuery, gộp sẵn một lần mỗi đêm rẻ hơn nhiều so với quét lại bảng gốc mỗi lượt xem.

Cảnh báo — đừng lạm dụng PDT:

• PDT chiếm storage trong scratch schema và tốn chi phí build mỗi chu kỳ. Đẻ ra hàng chục PDT chồng chéo là một cách âm thầm đội hoá đơn warehouse.
• PDT chồng PDT (cascading) tạo chuỗi phụ thuộc dễ vỡ và khó gỡ lỗi khi một mắt xích build lỗi.
• Quan trọng nhất về mặt kiến trúc: PDT là nơi logic biến đổi bị chôn trong lớp BI, tách khỏi pipeline dữ liệu chính. Với những phép transform nặng và có tính "nguồn sự thật", cân nhắc đẩy chúng về dbt — nơi có version control, test, và lineage đầy đủ — rồi để Looker chỉ đọc bảng mà dbt đã dựng.

Nguyên tắc chốt: mô hình hoá tốt trước, dbt cho transform nặng, PDT cho tăng tốc explore. PDT là công cụ tối ưu hiệu năng, không phải nơi thay thế một data warehouse được thiết kế đàng hoàng.

Use case thực tế: doanh thu theo ngày làm mới hằng đêm

Ngân hàng cần một dashboard doanh thu giao dịch theo ngày và theo chi nhánh. Bảng transactions có vài trăm triệu dòng và tăng mỗi ngày. Nếu để ephemeral, mỗi lần lãnh đạo mở dashboard buổi sáng, warehouse quét lại toàn bộ bảng để gộp — chậm và tốn.

Giải pháp:

1. Tạo một PDT transactions_agg gộp theo transaction_date và branch_id (như ví dụ incremental ở trên).
2. Vì bảng lớn và chỉ thêm dữ liệu mới, dùng incremental PDT với increment_key: transaction_date và increment_offset: 3 để bắt cả giao dịch về muộn trong 3 ngày.
3. Gắn PDT vào datagroup orders_daily với sql_trigger: SELECT CURRENT_DATE() — mỗi nửa đêm giá trị đổi, Looker xây thêm phần dữ liệu của ngày mới (và refresh 3 ngày offset) ở nền.
4. Các explore trên dashboard tham chiếu cùng datagroup qua persist_with, nên query cache cũng hết hạn đúng lúc.

Kết quả: sáng ra dashboard đọc từ bảng đã vật chất hoá, phản hồi tức thì; chi phí gộp chỉ trả một lần mỗi đêm và chỉ cho phần dữ liệu mới. Bước tiếp là dựng trực quan hoá trên khối này — xem Looker 6 — Dashboards & visualization.

Ghi nhớ

• Derived table = view có nguồn là truy vấn, không phải bảng thật. SQL-based (sql) linh hoạt tối đa; native (explore_source) dùng lại logic LookML đã mô hình hoá.
• Mặc định là ephemeral: gói thành subquery/CTE, tính lại mỗi lần chạy — hợp cho phép gộp nhẹ, dữ liệu luôn tươi, không tốn storage.
• PDT vật chất hoá bảng vào scratch schema của warehouse để đọc nhanh; cần bật PDT trên connection và quyền tạo bảng.
• Làm mới: datagroup_trigger (khuyến nghị, không bắt người dùng chờ) hơn persist_for (có thể chờ khi hết hạn); sql_trigger_value và interval_trigger là các lựa chọn khác. Datagroup thống nhất cả PDT lẫn query cache qua persist_with + max_cache_age.
• Incremental PDT (increment_key, increment_offset, filter {% incrementcondition %}) chỉ xây phần mới cho bảng lớn; chỉ dùng với sql + trigger-based, không dùng persist_for. increment_offset xử lý dữ liệu về muộn.
• PDT khác cache: PDT là bảng vật chất trong warehouse dùng chung nhiều query; cache là kết quả một query cụ thể.
• Đừng lạm dụng PDT — nó tốn storage và chi phí build. Transform nặng và mang tính nguồn sự thật nên đẩy về dbt; PDT dành cho tăng tốc explore.