SQL dan Pemodelan Data yang Berskala Melampaui Dasbor Pertama

Saya sudah mewarisi tiga gudang data seperti ini dan diagnosisnya selalu sama. Ada sebuah kueri yang seseorang tempel ke DM Slack pukul 4 sore pada hari Jumat. Marketing membangun dasbor di atasnya. Sales membangun dasbor lain di atas dasbor itu. Enam bulan kemudian, CFO membuka keduanya dalam rapat yang sama, melihat dua angka pendapatan berbeda, dan berhenti mempercayai tim data untuk sisa kuartal itu.

Solusinya bukan SQL yang lebih cerdas. Ini adalah disiplin: lapisan, pengujian, penamaan, dan jadwal penghentian. Panduan ini adalah disiplin tersebut, ditulis untuk analis yang sudah tahu cara JOIN dan ingin berhenti bertugas siaga untuk "mengapa dasbor ini mengatakan sesuatu yang berbeda."

Kueri Thread Slack

Begini awalnya. Seseorang di #revops mengajukan pertanyaan. Kamu menulis kueri 60 baris terhadap replika produksi, menjatuhkan jawabannya di thread, dan melanjutkan. Kueri itu disalin ke notebook Mode. Notebook Mode menjadi tile Looker. Tile Looker menjadi grafik deck board.

Sembilan puluh hari kemudian skema dasarnya berubah, kueri diam-diam rusak, dan tidak ada yang menyadarinya karena grafik masih dirender, hanya dengan angka yang buruk. Atau lebih parah, kueri tidak pernah rusak tetapi tidak pernah memiliki satu definisi bernama tentang "pelanggan aktif" dan sekarang tiga tim menatap tiga hitungan berbeda.

Kueri thread Slack bagus sebagai kueri thread Slack. Masalahnya adalah jalur dari "pencarian" ke "dasbor penting" tanpa titik pemeriksaan di antaranya. Kamu butuh titik pemeriksaan. Titik pemeriksaan itu adalah sebuah model.

Dua Definisi Pendapatan adalah Peristiwa Kepercayaan

Hari ketika finance dan product hadir dalam rapat yang sama dengan dua angka pendapatan berbeda, kamu kehilangan kepercayaan selama setahun dan kamu akan menghabiskan enam bulan untuk mendapatkannya kembali. Saya melihat hal ini terjadi di tiga perusahaan berbeda. Ini bukan masalah tooling. Snowflake tidak gagalmu. dbt tidak gagalmu. Yang gagal adalah bahwa tidak ada yang membuat keputusan tentang apa arti "pendapatan" sebelum lima orang menulis lima kueri.

Ini adalah rekayasa yang bisa dicegah. Lapisan dan pengujian mencegah masalah dua-pendapatan. Penamaan dan penghentian mencegah gudang data membusuk. Pekerjaannya membosankan dengan sengaja. Jika stack data-mu terasa menarik dari hari ke hari, sesuatu mungkin sedang terbakar.

Praktik SQL yang Benar-Benar Berskala

Empat kebiasaan membedakan analis yang bisa menyerahkan gudang data mereka dari analis yang kodenya mati begitu mereka pergi.

CTE dibanding subkueri bersarang. Subkueri bersarang adalah artefak yang hanya bisa ditulis. Kamu bisa menulisnya, tapi enam minggu kemudian kamu tidak bisa membacanya, begitu pula orang berikutnya. CTE memungkinkanmu memberi nama setiap langkah, men-debug setiap langkah, dan berpikir tentang satu model mental per blok.

-- Jangan lakukan ini
SELECT customer_id, SUM(amount)
FROM (
  SELECT * FROM orders
  WHERE status = 'completed'
    AND created_at >= (SELECT MIN(created_at) FROM orders WHERE ...)
) o
JOIN (SELECT * FROM customers WHERE deleted_at IS NULL) c
  ON o.customer_id = c.id
GROUP BY 1;

-- Lakukan ini
WITH completed_orders AS (
  SELECT *
  FROM {{ ref('stg_shopify__orders') }}
  WHERE status = 'completed'
),

active_customers AS (
  SELECT *
  FROM {{ ref('stg_shopify__customers') }}
  WHERE deleted_at IS NULL
),

revenue_by_customer AS (
  SELECT
    c.customer_id,
    SUM(o.amount) AS lifetime_revenue
  FROM active_customers c
  LEFT JOIN completed_orders o
    ON o.customer_id = c.customer_id
  GROUP BY c.customer_id
)

SELECT * FROM revenue_by_customer;

Versi kedua lebih panjang. Tapi bisa di-debug. Kamu bisa menyorot CTE mana pun, menjalankannya sendiri, dan memverifikasi jumlah baris. Itulah seluruh permainannya.

Pengujian bernama di setiap model. Bukan "saya akan menambahkan pengujian nanti." Di setiap model, dalam pull request yang sama. Empat yang terbukti nilainya:

# models/marts/_marts.yml
version: 2

models:
  - name: dim_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['active', 'churned', 'paused']
      - name: account_owner_id
        tests:
          - relationships:
              to: ref('dim_users')
              field: user_id

Empat pengujian, lima belas baris YAML, dan kamu akan menangkap sembilan puluh persen kerusakan diam yang seharusnya muncul di deck board. unique dan not_null pada setiap kunci utama. accepted_values pada setiap kolom berbentuk enum. relationships pada setiap kunci asing. Jika kamu tidak menguji kunci-kuncinya, kamu tidak punya model. Kamu punya kueri.

Satu model sumber kebenaran tunggal per entitas bisnis. Satu dim_customers. Satu fct_orders. Satu dim_users. Bukan dim_customers_v2, bukan dim_customers_marketing, bukan dim_customers_for_finance_q3. Jika marketing dan finance membutuhkan potongan pelanggan yang berbeda, mereka mendapatkan kolom yang berbeda di dim_customers yang sama, atau mereka membangun lapisan mart di atasnya. Mereka tidak boleh mengkloning tabel.

Klon itulah yang menghasilkan masalah dua-pendapatan. Hentikan di tempat.

Fungsi window dibanding self-join. Jika kamu melakukan self-join tabel ke dirinya sendiri untuk menghitung "tanggal pesanan sebelumnya" atau "peringkat dalam pelanggan", kamu bekerja terlalu keras. LAG(), LEAD(), ROW_NUMBER(), dan RANK() melakukan pekerjaan yang sama dalam satu kali proses dengan setengah baris dan sepersepuluh biaya gudang data.

Lapisan Pemodelan Data: Staging, Intermediate, Marts

Ini adalah bagian yang tidak ingin dilakukan siapa pun karena terasa seperti pekerjaan ekstra. Ini bukan pekerjaan ekstra. Ini adalah pekerjaan yang membuat segalanya sesudahnya menjadi mungkin.

Staging (stg_). Satu model per tabel sumber. Ganti nama kolom ke konvensi proyekmu. Cetak ulang tipe data. Tidak lebih dari itu. Tidak ada penggabungan tabel, tidak ada logika bisnis, tidak ada agregasi. Tugas lapisan staging adalah memberi setiap model hilir tampilan sumber yang bersih, dapat diprediksi, dan bernama dari satu tabel sumber. Jika kamu melewatkan staging karena "hanya penggantian nama," kamu akan menyesalinya di hari Stripe mengganti nama kolom atau sinkronisasi Fivetran-mu mengubah sensitivitas huruf besar.

-- models/staging/stripe/stg_stripe__charges.sql
WITH source AS (
  SELECT * FROM {{ source('stripe', 'charges') }}
),

renamed AS (
  SELECT
    id              AS charge_id,
    customer        AS customer_id,
    amount / 100.0  AS amount_usd,
    status          AS charge_status,
    created::TIMESTAMP AS charged_at
  FROM source
)

SELECT * FROM renamed;

Itulah seluruh modelnya. Membosankan. Itulah intinya.

Intermediate (int_). Di sinilah penggabungan tabel, logika bisnis, dan blok pembangun yang dapat digunakan kembali tinggal. Model intermediate tidak pernah diekspos ke BI. Mereka ada untuk menjaga model mart-mu tetap dapat dibaca dan memungkinkan kamu menggunakan kembali bagian-bagian. Jika tiga mart semuanya membutuhkan "monthly recurring revenue per pelanggan," itu adalah model int_customer_mrr_monthly, dibangun sekali, direferensikan tiga kali.

-- models/intermediate/int_customer_mrr_monthly.sql
WITH subscriptions AS (
  SELECT * FROM {{ ref('stg_stripe__subscriptions') }}
),

months AS (
  SELECT * FROM {{ ref('int_calendar_months') }}
),

active_in_month AS (
  SELECT
    m.month_start,
    s.customer_id,
    s.monthly_amount_usd
  FROM months m
  LEFT JOIN subscriptions s
    ON m.month_start BETWEEN s.started_at AND COALESCE(s.canceled_at, '9999-12-31')
)

SELECT
  customer_id,
  month_start,
  SUM(monthly_amount_usd) AS mrr_usd
FROM active_in_month
GROUP BY 1, 2;

Marts (fct_ / dim_). Inilah yang dikueri alat BI. Denormalisasi, dapat dibaca bisnis, dan dinamai dengan cara seorang eksekutif akan menamainya. fct_ untuk tabel fakta (pesanan, tagihan, sesi). dim_ untuk tabel dimensi (pelanggan, produk, karyawan). Lapisan mart-mu adalah API gudang datamu. Perlakukan perubahan padanya seperti perubahan API: berversi, dikomunikasikan, dihentikan dengan pemberitahuan.

Tim data sepuluh orang biasanya berakhir dengan antara 200 dan 400 model di ketiga lapisan ini. Kira-kira 50-60% staging, 25-30% intermediate, 15-20% mart. Jika jumlah mart-mu melampaui rasio tersebut, kamu melakukan logika bisnis di mart yang seharusnya ada di intermediate.

Diagnostik "Dua Definisi Pendapatan"

Kamu bisa menjalankan ini dalam dua puluh menit. Lakukan sebelum menulis model baru di gudang data yang kamu warisi.

1. Tarik setiap tile dasbor, laporan BI, dan kueri tersimpan yang memiliki kata "pendapatan" dalam judul atau kolomnya.
2. Untuk setiap satu, salin SQL-nya ke dalam satu dokumen.
3. Bandingkan klausa WHERE, kondisi JOIN, dan target SUM.
4. Buat tabel: nama dasbor, pemilik, definisi, sumber.
5. Bawa ke finance. Dapatkan satu definisi yang dikomitmenkan secara tertulis.
6. Bangun satu model fct_revenue yang sesuai dengan definisi yang dikomitmenkan.
7. Migrasikan setiap dasbor ke model tersebut. Hentikan kueri lama.

Sebagian besar tim menemukan tiga hingga lima definisi pendapatan berbeda dalam audit pertama. Terkadang semuanya "benar" untuk tujuan sempit tertentu, tetapi hanya satu yang akan dipertahankan CFO dalam panggilan pendapatan. Itulah yang ada di model. Yang lain ditulis ulang sebagai tampilan di atasnya (vw_revenue_marketing_attributed, vw_revenue_booking_basis) sehingga jelas bahwa itu adalah potongan dari angka yang sama, bukan angka terpisah.

Jalankan audit yang sama pada "pelanggan aktif", "churn", "ARR", dan "MRR". Kamu akan menemukan masalah yang sama. Perbaiki dengan cara yang sama.

Konvensi Penamaan dbt

Penamaan bukan hal sepele. Ini adalah cara analis berikutnya mengorientasikan diri dalam gudang datamu tanpa harus bertanya kepadamu.

• stg_{sumber}__{entitas}. Dua garis bawah memisahkan sumber dari entitas. stg_stripe__charges, stg_hubspot__contacts, stg_shopify__orders. Dua garis bawah adalah petunjuk visual bahwa sisi kiri adalah nama sistem.
• int_{entitas}_{kata_kerja}. Mendeskripsikan apa yang dilakukan model. int_orders_pivoted, int_customers_enriched, int_sessions_attributed.
• fct_{peristiwa} dan dim_{entitas}. Contoh: fct_orders, fct_charges, fct_page_views, dim_customers, dim_products, dim_dates.
• Aliasing sumber dalam pengujian, bukan aliasing kolom dalam SQL mart. dim_customers.customer_id-mu seharusnya customer_id, bukan dim_customers__customer_id.

Sufiks _v2 adalah tanda bahaya. Jika kamu melihat dim_customers_v2.sql di samping dim_customers.sql, seseorang takut merusak yang asli dan mengirimkan fork. Versi kontrol ada di git, bukan di nama file. Langkah yang tepat adalah: tulis ulang dim_customers, kirimkan perubahan yang melanggar dengan catatan migrasi, hentikan perilaku lama pada kalender. Mempertahankan keduanya selamanya menjamin masalah dua-pendapatan akan kembali dengan wajah berbeda.

Disiplin Kontrol Versi dan Log Perubahan

Tidak ada penggabungan langsung ke main. Pernah. Bahkan untuk perbaikan satu baris typo. Begitu kamu membuat pengecualian, itu menjadi aturan.

Setiap pull request memiliki entri CHANGELOG satu baris di CHANGELOG.md:

## 2026-04-29
- `fct_orders`: menambahkan kolom `is_first_order`. Mempengaruhi: marketing-attribution, lifecycle-emails dashboards. (@camellia)
- `breaking: dim_customers`: mengganti nama `acct_owner` menjadi `account_owner_id`. Mempengaruhi: semua dasbor CRM. Migrasi: lihat #1284. (@camellia)
- `stg_stripe__charges`: menangani `customer_id` null dari pembelian tamu. (@camellia)

Tiga aturan pada CHANGELOG:

1. Setiap pull request menambahkan entri. Tidak ada pull request yang dikirim tanpanya. CI dapat menegakkan ini.
2. Perubahan skema diberi tag breaking: dan diberitahukan di #data-changes sebelum pull request digabungkan.
3. Kolom "mempengaruhi" menyebutkan dasbor atau model hilir, bukan frasa samar seperti "berbagai laporan". Jika kamu tidak bisa menyebutkan apa yang mempengaruhi, kamu tidak cukup mengetahui grafik hilirmu untuk melakukan penggabungan.

Perubahan skema juga mendapatkan penghentian bertahap dalam kalender. Kolom lama tetap ada selama 30 hari dengan komentar, kolom baru ditambahkan secara paralel, konsumen hilir bermigrasi, kemudian kolom lama dihapus. Tidak ada pengecualian untuk penggantian nama yang "mudah". Penggantian nama yang mudah adalah cara kamu menemukan bahwa seseorang membangun alur Zapier di atas gudang datamu dan tidak ada yang memberi tahumu.

Dasbor vs Satu Kali: Kapan Membangun Apa

Aturan keputusan: apakah angka ini akan ditanyakan lagi dalam 30 hari?

• Ya maka itu adalah dasbor. Angka tersebut berada di belakang model. Dasbor memiliki pemilik.
• Tidak maka kirimkan CSV-nya. Tautkan SQL dalam thread permintaan atau pull request. Lanjutkan.

Kesalahannya adalah membangun dasbor untuk setiap permintaan ad hoc. Enam bulan kemudian kamu punya 400 dasbor, 350 di antaranya tidak dibuka sejak hari pengirimannya, dan analis yang membangunnya sudah meninggalkan perusahaan.

Dasbor memiliki kolom pemilik. Bukan "tim data". Sebuah nama. Jika dasbor ada tanpa pemilik yang disebutkan namanya, arsipkan. Tugas pemilik adalah menjawab "apakah ini masih benar?" ketika pemangku kepentingan bertanya. Jika tidak ada yang memilikinya, tidak ada yang bisa menjawab, dan kamu kembali ke masalah kepercayaan.

SQL satu kali masuk ke folder analyses/ dalam proyek dbt-mu, dikomit dan ditinjau tetapi tidak dimodelkan. Itu memberikanmu jejak dokumen tanpa membengkakkan gudang data.

Jadwal Penghentian

Gudang data membusuk seperti loteng membusuk, perlahan lalu sekaligus. Jadwal yang mencegahnya:

• Tinjauan kuartalan dasbor berdasarkan tanggal terakhir dilihat. Sebagian besar alat BI mengekspos ini. Tarik daftar setiap kuartal.
• 60 hari tidak dilihat maka banner penghentian. Pesan "dasbor ini dijadwalkan untuk diarsipkan pada YYYY-MM-DD" yang terlihat. Memberi tahu pemilik. Memaksa sebuah keputusan.
• 90 hari tidak dilihat maka diarsipkan. Pindahkan ke folder archive/. Tandai model dasarnya enabled: false dalam konfigurasi dbt agar berhenti dibangun.

# models/marts/legacy/_legacy.yml
models:
  - name: fct_orders_2024_legacy
    config:
      enabled: false
    description: |
      Diarsipkan 2026-04-29, digantikan oleh fct_orders.
      Terakhir dikueri 2026-01-12. Pemilik meninggalkan perusahaan pada Q4-2025.

Setelah setahun jadwal ini, kamu akan memotong jumlah modelmu sebesar 30-40% tanpa keluhan hilir. Model yang kamu hapus sudah mati. Kamu hanya membuatnya resmi agar model itu berhenti menghabiskan komputasi dan membingungkan analis baru.

Kesalahan Umum

Empat yang paling sering saya lihat, diurutkan berdasarkan seberapa besar kerusakan yang ditimbulkannya:

1. Pemodelan sebelum menyepakati definisi. Lakukan audit. Dapatkan definisinya. Kemudian bangun modelnya. Urutan sebaliknya menghasilkan masalah dua-pendapatan di gudang data baru.
2. Melewatkan staging karena "hanya penggantian nama". Melewatkan staging berarti setiap model hilir rusak di hari skema sumber berubah. Staging adalah peredam kejut. Bayar pajak yang membosankan.
3. Membiarkan file _v2 berkembang biak. Setiap _v2 adalah pengakuan bahwa seseorang takut merusak yang asli. Perbaiki yang asli. Komunikasikan perubahannya. Kirimkan satu model.
4. Tidak ada pemilik pada model kritis. Sebuah fct_revenue tanpa pemilik adalah kewajiban yang dilampirkan dengan file SQL. Kolom pemilik wajib ada dalam YAML model-mu, sama seperti pengujian.

Seperti Apa "Selesai" Itu

Kamu telah membangun disiplin ini ketika:

• Setiap metrik bisnis memiliki tepat satu model yang mendefinisikannya.
• Setiap model memiliki setidaknya pengujian unique dan not_null pada kunci utamanya.
• Setiap dasbor memiliki pemilik yang disebutkan namanya.
• Analis baru bisa menelusuri angka mana pun di dasbor mana pun kembali ke sumbernya (melalui mart, intermediate, staging, sumber) dalam waktu kurang dari sepuluh menit, tanpa bertanya kepada siapa pun.
• CHANGELOG memberi tahu kamu apa yang berubah minggu lalu tanpa membuka Slack.
• Pengeluaran komputasimu datar atau menurun sementara jumlah modelmu bertumbuh, karena kamu menghentikan hal-hal yang sudah mati.

Itulah standarnya. Membosankan. Itulah juga cara kamu berhenti menjadi tim yang dipanggil pukul 11 malam karena deck board tidak cocok.

Pelajari Lebih Lanjut

• Template Job Description Data Analyst
• Penerjemahan Pemangku Kepentingan: Dari Permintaan Samar ke Analisis yang Bisa Dikirimkan
• Desain Dasbor yang Mendorong Keputusan
• Kesalahan Umum Data Analyst