06 Dashboard yang mustahil

Hasil akhir

Dalam sekitar 25 menit Anda akan membangun funnel konversi per-menit yang menjawab dalam milidetik, berapa pun banyaknya riwayat yang dicakupnya.

Dashboard-nya

Dashboard yang dibangun modul ini adalah funnel konversi: view_item -> add_to_cart -> begin_checkout -> purchase, dipecah berdasarkan menit, kategori device, dan negara. Di seluruh export:

Tersebar di 109 negara. Keempat hitungan itu, dipecah per menit, kategori device, dan negara, adalah keseluruhan bentuk query yang akan ditembakkan sebuah live ops screen di setiap page load.

Modul ini mengukur keempat hitungan konversi itu, ditambah estimasi distinct-user, tidak pernah revenue. Revenue ada di dataset ini -- 5.692 event purchase semuanya membawa ecommerce.purchase_revenue_in_usd yang terisi, totalnya 362.165 USD -- tapi jarang: 5.692 baris itu sekitar 0,13% dari export 4.295.584 baris, jadi panel revenue per-menit akan kosong di hampir setiap bucket. Hitungan konversi padat di setiap menit, device, dan negara, itulah sebabnya itu yang dilacak funnel modul ini.

Mengapa tidak ada sort key yang menyelamatkan ini

Modul 05 adalah point lookup: satu user_pseudo_id, dan sort key yang memimpin dengan kolom itu membiarkan ClickHouse skip hampir seluruh tabel. Query dashboard ini berbentuk berbeda. Ia mengelompokkan setiap baris di dalam window satu hari kalender berdasarkan menit, kategori device, dan negara, sehingga tidak ada satu predikat equality pun untuk dipimpin sort key. Hal terdekat dengan sebuah filter adalah batas window itu sendiri (event_time >= ... AND event_time < ...), dan bq.events_tuned, tabel yang dibangun modul 04, disortir berdasarkan (event_date, event_name, user_pseudo_id), yang membantu lookup satu-user, bukan range scan per-menit di seluruh user.

Ukur biaya mentahnya

Jalankan agregat mentah untuk satu hari kalender terhadap tabel itu, dengan condition cache dimatikan, dengan cara yang sama seperti modul 05 mengajari Anda mengukur:

SELECT
  toStartOfMinute(event_time) AS minute,
  device_category, geo_country,
  sum(toUInt64(event_name = 'view_item'))      AS views,
  sum(toUInt64(event_name = 'add_to_cart'))    AS carts,
  sum(toUInt64(event_name = 'begin_checkout')) AS checkouts,
  sum(toUInt64(event_name = 'purchase'))       AS purchases,
  uniq(user_pseudo_id)                          AS users
FROM bq.events_tuned
WHERE event_time >= '2025-12-01 00:00:00' AND event_time < '2025-12-02 00:00:00'
GROUP BY minute, device_category, geo_country
SETTINGS use_query_condition_cache = 0;

Baca read_rows langsung dari results bar console-nya, dengan cara yang sama seperti diajarkan modul 05.

Diukur terhadap bq.events_tuned: 4.295.584 baris terbaca, setiap baris yang dimiliki tabelnya, untuk window satu hari. Itu punya dua sebab:

• Sebuah agregat per-menit-di-seluruh-user tidak punya satu value pun untuk dituju sebagaimana filter user_pseudo_id bisa. Ini struktural terhadap pertanyaannya sendiri.
• Tabel ini sama sekali tidak membawa partisi tanggal, jadi filter range pada event_time juga tidak bisa melakukan pruning per hari.

Partisi yang sudah dimiliki BigQuery

Pembaca yang teliti akan memperhatikan sebab kedua di atas dan seharusnya tidak perlu mencarinya: pruning _TABLE_SUFFIX milik BigQuery sendiri adalah justru yang membiarkan versi query-nya memindai hanya 4.455.256 byte alih-alih seluruh dataset. ClickHouse akan melakukan pruning yang sama dengan satu baris tambahan di definisi tabel, PARTITION BY toYYYYMMDD(event_date).

Layak dinyatakan dengan jelas: ClickHouse bahkan tidak memakai keuntungan yang bisa dimilikinya di sini. Tanpa partisi itu, query mentah yang tidak dipartisi di atas tetap menjawab dalam 52 ms melawan 0,98 dtk BigQuery yang sudah di-prune. Menambahkan partisi akan membuat query mentahnya lebih cepat lagi. Bukan keduanya yang sedang dibangun modul ini. Materialized view di bawah adalah lever yang berbeda, lebih kuat lagi.

Langkah 1: Bangun materialized view-nya

Bangun tabel AggregatingMergeTree untuk menyimpan hasil agregasinya, dan materialized view yang menjaganya tetap current di setiap insert:

CREATE TABLE bq.funnel_agg
(
  minute          DateTime,
  device_category LowCardinality(String),
  geo_country     LowCardinality(String),
  views           AggregateFunction(sum, UInt64),
  carts           AggregateFunction(sum, UInt64),
  checkouts       AggregateFunction(sum, UInt64),
  purchases       AggregateFunction(sum, UInt64),
  users           AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree
ORDER BY (minute, device_category, geo_country);

CREATE MATERIALIZED VIEW bq.funnel_mv TO bq.funnel_agg AS
SELECT
  toStartOfMinute(event_time)                          AS minute,
  device_category,
  geo_country,
  sumState(toUInt64(event_name = 'view_item'))         AS views,
  sumState(toUInt64(event_name = 'add_to_cart'))       AS carts,
  sumState(toUInt64(event_name = 'begin_checkout'))    AS checkouts,
  sumState(toUInt64(event_name = 'purchase'))          AS purchases,
  uniqState(user_pseudo_id)                            AS users
FROM bq.events_tuned
GROUP BY minute, device_category, geo_country;

AggregatingMergeTree ada untuk menyimpan hasil sebuah pengelompokan alih-alih menghitungnya ulang. Kolomnya yang bertipe AggregateFunction(...) tidak menyimpan sum final atau distinct count final -- mereka menyimpan state parsial yang bisa di-merge, yang justru membiarkan banyak update inkremental kecil bergabung jadi jawaban benar yang sama yang akan dihasilkan satu agregasi bulk. Materialized view-nya adalah yang menjaga state itu current: ia bukan saved query yang dijalankan ulang atas permintaan, ia menempel pada bq.events_tuned dan menyala otomatis di setiap batch yang di-insert ke sana, menulis state parsial baru untuk bucket (minute, device_category, geo_country) mana pun yang disentuh batch itu.

Tulis dengan kombinator -State yang cocok untuk setiap agregat (sumState, uniqState) dan baca dengan kombinator -Merge yang cocok (sumMerge, uniqMerge) -- AggregatingMergeTree mensyaratkan kombinator yang memproduksi sebuah state parsial harus dibalik oleh kombinator merge yang cocok, bukan agregat apa pun yang kebetulan terdengar serupa.

INSERT INTO bq.funnel_agg
SELECT
  toStartOfMinute(event_time)                          AS minute,
  device_category,
  geo_country,
  sumState(toUInt64(event_name = 'view_item'))         AS views,
  sumState(toUInt64(event_name = 'add_to_cart'))       AS carts,
  sumState(toUInt64(event_name = 'begin_checkout'))    AS checkouts,
  sumState(toUInt64(event_name = 'purchase'))          AS purchases,
  uniqState(user_pseudo_id)                            AS users
FROM bq.events_tuned
GROUP BY minute, device_category, geo_country;

Langkah 2: Konfirmasi kemenangannya

Jalankan query dashboard terhadap bq.funnel_agg, untuk window satu hari yang sama yang Anda ukur di "Ukur biaya mentahnya":

SELECT
  minute, device_category, geo_country,
  sumMerge(a.views)     AS views,
  sumMerge(a.carts)     AS carts,
  sumMerge(a.checkouts) AS checkouts,
  sumMerge(a.purchases) AS purchases,
  uniqMerge(a.users)    AS users,
  round(sumMerge(a.carts) / nullIf(sumMerge(a.views), 0), 4) AS cart_rate
FROM bq.funnel_agg AS a
WHERE minute >= '2025-12-01 00:00:00' AND minute < '2025-12-02 00:00:00'
GROUP BY minute, device_category, geo_country
ORDER BY minute DESC, device_category, geo_country
SETTINGS use_query_condition_cache = 0;

Baca read_rows langsung dari results bar console-nya, dengan cara yang sama seperti baseline mentahnya:

Ini bukan kemenangan tuning seperti modul 05. Tidak ada sort key, tidak ada partisi, tidak ada pilihan codec yang mengubah scan seluruh tabel jadi lebih murah untuk query yang harus mengelompokkan setiap baris di window-nya. Yang mengubah 4.295.584 baris terbaca jadi 16.384 adalah jenis objek yang sama sekali berbeda: sebuah tabel yang sudah menyimpan jawabannya, dijaga current oleh sebuah view alih-alih dihitung ulang oleh sebuah query. BigQuery tidak punya apa pun yang mengisi peran ini. Scheduled query atau summary table membawa Anda sebagian jalan, tapi tidak satu pun dari keduanya memperbarui dirinya sendiri, secara inkremental, baris demi baris, saat event baru mendarat. Gap itu adalah argumen keseluruhan workshop ini dalam satu angka.

Verifikasi view-nya tidak mengubah data

Mengagregasi ke materialized view seharusnya tidak pernah mengubah apa yang dikatakan event yang mendasarinya -- hanya seberapa cepat Anda bisa menanyakannya. Sebelum mempercayai kemenangan read_rows dari Langkah 2, buktikan bahwa bq.funnel_agg menghasilkan persis empat hitungan konversi yang sama untuk window ini seperti menghitungnya langsung dari bq.events_tuned. Fingerprint melakukan itu sebagai satu angka: groupBitXor melipat cityHash64 dari setiap baris jadi satu value, sehingga dua himpunan hasil penuh bisa dibandingkan dengan membandingkan satu angka alih-alih men-scroll baris.

Kenapa urutan tidak penting

groupBitXor atas cityHash64 bersifat komutatif dan asosiatif, jadi fingerprint hanya bergantung pada himpunan baris yang dimasukkan, tidak pernah pada urutan. Ini properti yang sama yang sudah diandalkan pengecekan modul 05.

Kenapa users ditinggalkan dari pengecekan

Pengecekan ini dengan sengaja meninggalkan users (estimasi distinct-visitor uniq/uniqMerge) keluar.

uniq adalah aproksimasi, dan state internalnya yang di-merge tidak dijamin keluar bit-identik antara jalur backfill bulk dan jalur inkremental per-insert, bahkan ketika keduanya konvergen ke estimasi visible yang sama. Menyertakannya akan berisiko menandai jawaban yang benar-benar benar sebagai salah karena perbedaan representasi sketch, bukan perbedaan nyata dalam data yang mendasarinya. views, carts, checkouts, dan purchases adalah sum eksak tanpa risiko seperti itu, dan itulah yang benar-benar dibandingkan pengecekan ini.

Kenapa setiap field dikoalesc dulu

Setiap field di kedua sisi dibungkus ifNull, dan itu bukan dekorasi.

cityHash64 mengembalikan NULL kalau argumen apa pun NULL, dan groupBitXor diam-diam melewati input NULL alih-alih error. Jadi kolom nullable dengan NULL di dalamnya menjatuhkan baris itu dari fingerprint sementara count() tetap menghitungnya, dan kolom yang NULL sepanjang keseluruhan mengempiskan fingerprint jadi \N. Kedua sisi pengecekan ini membaca tabel yang Anda bangun sendiri, jadi sebuah NULL akan meracuni keduanya, dan kedua value \N itu akan terbaca sama: pengecekan yang lolos sambil tidak membuktikan apa pun, yang lebih buruk dari yang gagal.

NULL adalah sentinel di sini, bukan value: kedua sisi memetakannya ke '' untuk string dan 0 untuk angka sebelum hashing, dan kedua grouping key dikoalesc di dalam subquery sehingga sebuah NULL dan sebuah '' mendarat di grup yang sama di kedua sisi.

Jalankan pengecekan terhadap view Anda

SELECT
  count() AS row_count,
  groupBitXor(cityHash64(ifNull(toString(minute, 'UTC'), ''),
                         device_category, geo_country,
                         ifNull(views, 0), ifNull(carts, 0),
                         ifNull(checkouts, 0), ifNull(purchases, 0))) AS fingerprint
FROM (
  SELECT
    f.minute                      AS minute,
    ifNull(f.device_category, '') AS device_category,
    ifNull(f.geo_country, '')     AS geo_country,
    sumMerge(f.views) AS views, sumMerge(f.carts) AS carts,
    sumMerge(f.checkouts) AS checkouts, sumMerge(f.purchases) AS purchases
  FROM bq.funnel_agg AS f
  WHERE f.minute >= '2025-12-01 00:00:00' AND f.minute < '2025-12-02 00:00:00'
  GROUP BY minute, device_category, geo_country
);

Jalankan pengecekan yang sama terhadap baseline mentah

Lalu hitung bentuk yang sama langsung dari bq.events_tuned, dipakai ulang di sini sebagai baseline mentah. Kedua fingerprint itu seharusnya cocok:

SELECT
  count() AS row_count,
  groupBitXor(cityHash64(ifNull(toString(minute, 'UTC'), ''),
                         device_category, geo_country,
                         ifNull(views, 0), ifNull(carts, 0),
                         ifNull(checkouts, 0), ifNull(purchases, 0))) AS fingerprint
FROM (
  SELECT
    toStartOfMinute(t.event_time) AS minute,
    ifNull(t.device_category, '') AS device_category,
    ifNull(t.geo_country, '')     AS geo_country,
    sum(ifNull(t.event_name, '') = 'view_item')      AS views,
    sum(ifNull(t.event_name, '') = 'add_to_cart')    AS carts,
    sum(ifNull(t.event_name, '') = 'begin_checkout') AS checkouts,
    sum(ifNull(t.event_name, '') = 'purchase')       AS purchases
  FROM bq.events_tuned AS t
  WHERE t.event_time >= '2025-12-01 00:00:00' AND t.event_time < '2025-12-02 00:00:00'
  GROUP BY minute, device_category, geo_country
);

Ketidakcocokan berarti bq.funnel_agg menjawab pertanyaan yang berbeda, bukan cuma lebih cepat -- hampir selalu backfill yang hilang dari Langkah 1, bukan fungsi agregatnya sendiri.

Selesai kalau

bq.funnel_agg menyimpan riwayat yang sudah di-backfill, query dashboard dan pengecekan baseline mentah Anda menghasilkan fingerprint yang cocok, dan Anda bisa mengatakan dalam satu kalimat kenapa materialized view inkremental adalah lever di sini ketika sort key adalah lever di modul 05. Lanjut ke 07 Ajukan pertanyaan ke data Anda kalau Anda sudah siap.