SQL dan Pemodelan Data yang Boleh Dibesarkan Melepasi Papan Pemuka Pertama

Saya telah mewarisi tiga gudang data seperti ini sekarang dan diagnosisnya sentiasa sama. Terdapat pertanyaan yang seseorang tampalkan ke dalam DM Slack pada pukul 4 petang Jumaat. Pemasaran membina papan pemuka di atasnya. Jualan membina papan pemuka lain di atas itu. Enam bulan kemudian, CFO membuka kedua-duanya dalam mesyuarat yang sama, melihat dua nombor hasil yang berbeza, dan berhenti mempercayai pasukan data untuk baki suku tersebut.

Pembaikan bukan SQL yang lebih bijak. Ia adalah disiplin: lapisan, ujian, penamaan, dan jadual pemansuhan. Panduan ini adalah disiplin tersebut, ditulis untuk Data Analyst yang sudah tahu cara JOIN dan ingin berhenti dipanggil bertugas untuk "mengapa papan pemuka ini menunjukkan sesuatu yang berbeza."

Pertanyaan Thread Slack

Beginilah ia bermula. Seseorang dalam #revops mengemukakan soalan. Anda menulis pertanyaan 60 baris terhadap replika pengeluaran, menjatuhkan jawapan dalam thread, dan meneruskan. Pertanyaan itu disalin ke dalam notebook Mode. Notebook Mode menjadi jubin Looker. Jubin Looker menjadi carta board deck.

Sembilan puluh hari kemudian skema asas berubah, pertanyaan rosak secara senyap, dan tiada siapa yang perasan kerana carta masih dipaparkan, hanya dengan nombor yang buruk. Atau lebih teruk, pertanyaan tidak pernah rosak tetapi ia tidak pernah mempunyai definisi tunggal bernama "pelanggan aktif" dan kini tiga pasukan memandang tiga kiraan berbeza.

Pertanyaan thread Slack adalah baik sebagai pertanyaan thread Slack. Masalahnya adalah laluan dari "carian" kepada "papan pemuka asas" tanpa titik pemeriksaan di antara. Anda memerlukan titik pemeriksaan. Titik pemeriksaan adalah model.

Dua Definisi Hasil Adalah Peristiwa Kepercayaan

Hari di mana kewangan dan produk muncul dalam mesyuarat yang sama dengan dua nombor hasil yang berbeza, anda telah kehilangan setahun kepercayaan dan anda akan menghabiskan enam bulan untuk mendapatkannya semula. Saya telah menyaksikannya berlaku di tiga syarikat berbeza. Ini bukan masalah perkakasan. Snowflake tidak gagal anda. dbt tidak gagal anda. Yang gagal adalah tiada siapa yang membuat keputusan tentang apa maksud "hasil" sebelum lima orang menulis lima pertanyaan.

Ini adalah kejuruteraan yang boleh dicegah. Lapisan dan ujian mencegah masalah dua-hasil. Penamaan dan pemansuhan mencegah gudang data daripada reput. Kerjanya membosankan dengan sengaja. Jika tindanan data anda terasa menarik hari ke hari, sesuatu mungkin sedang terbakar.

Amalan SQL yang Sebenarnya Boleh Dibesarkan

Empat tabiat memisahkan Data Analyst yang boleh menyerahkan gudang data mereka daripada mereka yang kodnya mati sebaik mereka meninggalkan syarikat.

CTE berbanding subpertanyaan bersarang. Subpertanyaan bersarang adalah artifak tulis-sahaja. Anda boleh menulisnya, tetapi enam minggu kemudian anda tidak boleh membacanya, begitu juga orang seterusnya. CTE membolehkan anda menamakan setiap langkah, menyahpepijat setiap langkah, dan memikirkan satu model mental bagi setiap blok.

-- Jangan buat 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;

-- Buat 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. Ia juga boleh dinyahpepijat. Anda boleh menyerlahkan mana-mana CTE, menjalankannya secara bersendirian, dan mengesahkan bilangan baris. Itulah keseluruhan permainannya.

Ujian bernama pada setiap model. Bukan "saya akan tambah ujian kemudian." Pada setiap model, dalam PR yang sama. Empat yang membuktikan 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 ujian, lima belas baris YAML, dan anda akan menangkap sembilan puluh peratus kerosakan senyap yang jika tidak akan muncul dalam board deck. unique dan not_null pada setiap kunci utama. accepted_values pada setiap lajur berbentuk enum. relationships pada setiap kunci asing. Jika anda tidak menguji kunci, anda tidak mempunyai model. Anda mempunyai pertanyaan.

Satu model sumber kebenaran tunggal bagi setiap entiti perniagaan. Satu dim_customers. Satu fct_orders. Satu dim_users. Bukan dim_customers_v2, bukan dim_customers_marketing, bukan dim_customers_for_finance_q3. Jika pemasaran dan kewangan memerlukan potongan pelanggan yang berbeza, mereka mendapat lajur yang berbeza pada dim_customers yang sama, atau mereka membina mart hiliran di atasnya. Mereka tidak boleh mengklon jadual.

Klon itulah yang menyebabkan masalah dua-hasil. Bunuh ia apabila kelihatan.

Fungsi tetingkap berbanding cantuman diri. Jika anda melakukan cantuman jadual sendiri untuk mengira "tarikh pesanan sebelumnya" atau "kedudukan dalam pelanggan", anda bekerja terlalu keras. LAG(), LEAD(), ROW_NUMBER(), dan RANK() melakukan kerja yang sama dalam satu laluan dengan separuh baris dan sepersepuluh kos gudang data.

Lapisan Pemodelan Data: Pementasan, Pertengahan, Mart

Bahagian ini adalah yang tidak mahu dilakukan oleh sesiapa kerana ia terasa seperti kerja tambahan. Ia bukan kerja tambahan. Ia adalah kerja yang menjadikan segala-galanya selepasnya mungkin.

Pementasan (stg_). Satu model bagi setiap jadual sumber. Namakan semula lajur mengikut konvensyen projek anda. Ubah suai jenis. Tiada yang lain. Tiada cantuman jadual, tiada logik perniagaan, tiada pengagregatan. Kerja lapisan pementasan adalah untuk memberikan setiap model hiliran pandangan yang bersih, boleh diramal, dan dinamakan bagi satu jadual sumber. Jika anda melangkau pementasan kerana "ia hanya penamaan semula", anda akan menyesalinya pada hari Stripe menamakan semula lajur atau penyegerakan Fivetran anda mengubah sensitiviti huruf.

-- 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 keseluruhan model. Ia membosankan. Itulah intipatinya.

Pertengahan (int_). Di sinilah cantuman jadual, logik perniagaan, dan blok binaan yang boleh digunakan semula berada. Model pertengahan tidak pernah terdedah kepada BI. Ia wujud untuk memastikan model mart anda boleh dibaca dan untuk membolehkan anda menggunakan semula bahagian-bahagian. Jika tiga mart semuanya memerlukan "hasil berulang bulanan bagi setiap pelanggan", itulah model int_customer_mrr_monthly, dibina sekali, dirujuk 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;

Mart (fct_ / dim_). Inilah yang ditanya oleh alat BI. Dinormalkan balik, boleh dibaca oleh perniagaan, dan dinamakan seperti cara eksekutif menamakan mereka. fct_ untuk jadual fakta (pesanan, caj, sesi). dim_ untuk jadual dimensi (pelanggan, produk, pekerja). Lapisan mart anda adalah API gudang data anda. Layan perubahan padanya seperti perubahan API: berversi, dikomunikasikan, dimansuhkan dengan notis.

Pasukan data sepuluh orang biasanya berakhir dengan antara 200 hingga 400 model merentasi tiga lapisan ini. Kira-kira 50-60% pementasan, 25-30% pertengahan, 15-20% mart. Jika bilangan mart anda melonjak melepasi nisbah itu, anda melakukan logik perniagaan dalam mart yang sepatutnya berada dalam pertengahan.

Diagnostik "Dua Definisi Hasil"

Anda boleh menjalankan ini dalam dua puluh minit. Lakukan sebelum menulis model baharu dalam gudang data yang anda warisi.

1. Tarik setiap jubin papan pemuka, laporan BI, dan pertanyaan tersimpan yang mempunyai perkataan "hasil" dalam tajuk atau lajurnya.
2. Bagi setiap satu, salin SQL ke dalam satu dokumen.
3. Bezakan klausa WHERE, syarat cantuman jadual, dan sasaran SUM.
4. Buat jadual: nama papan pemuka, pemilik, definisi, sumber.
5. Bawa kepada kewangan. Dapatkan satu definisi yang dikomitkan secara bertulis.
6. Bina satu model fct_revenue yang sepadan dengan definisi yang dikomitkan.
7. Pindahkan setiap papan pemuka ke model itu. Mansuhkan pertanyaan lama.

Kebanyakan pasukan mendapati tiga hingga lima definisi hasil yang berbeza dalam audit pertama. Kadang-kadang semuanya "betul" untuk tujuan tertentu yang sempit, tetapi hanya satu antara mereka adalah yang akan dipertahankan oleh CFO dalam panggilan pendapatan. Itulah yang berada dalam model. Yang lain ditulis semula sebagai paparan di atasnya (vw_revenue_marketing_attributed, vw_revenue_booking_basis) supaya jelas bahawa ia adalah potongan nombor yang sama, bukan nombor berasingan.

Jalankan audit yang sama pada "pelanggan aktif", "peralihan pelanggan", "ARR", dan "MRR". Anda akan mendapati masalah yang sama. Betulkan dengan cara yang sama.

Konvensyen Penamaan dbt

Penamaan bukan perkara kecil. Ia adalah cara Data Analyst seterusnya mengorientasikan diri mereka dalam gudang data anda tanpa bertanya kepada anda.

• stg_{sumber}__{entiti}. Garis bawah berganda memisahkan sumber daripada entiti. stg_stripe__charges, stg_hubspot__contacts, stg_shopify__orders. Garis bawah berganda adalah isyarat visual bahawa bahagian kiri adalah nama sistem.
• int_{entiti}_{kata kerja}. Menerangkan apa yang model lakukan. int_orders_pivoted, int_customers_enriched, int_sessions_attributed.
• fct_{peristiwa} dan dim_{entiti}. Contoh: fct_orders, fct_charges, fct_page_views, dim_customers, dim_products, dim_dates.
• Alias sumber dalam ujian, bukan alias lajur dalam SQL mart. dim_customers.customer_id anda sepatutnya customer_id, bukan dim_customers__customer_id.

Akhiran _v2 adalah tanda amaran. Jika anda melihat dim_customers_v2.sql bersebelahan dengan dim_customers.sql, seseorang takut memecahkan yang asal dan menghantar klon. Penversian adalah milik git, bukan nama fail. Langkah yang betul adalah: tulis semula dim_customers, hantar perubahan pemecahan dengan nota migrasi, mansuhkan tingkah laku lama dalam kalender. Menyimpan kedua-duanya selama-lamanya menjamin masalah dua-hasil akan kembali dengan pakaian yang berbeza.

Kawalan Versi dan Disiplin Log Perubahan

Tiada penggabungan terus ke main. Tidak sekali-kali. Walaupun untuk pembetulan kesilapan taip sebaris. Sebaik sahaja anda mengukir pengecualian, ia menjadi peraturan.

Setiap PR mempunyai entri CHANGELOG sebaris dalam CHANGELOG.md:

## 2026-04-29
- `fct_orders`: ditambah lajur `is_first_order`. Mempengaruhi: marketing-attribution, lifecycle-emails dashboards. (@camellia)
- `breaking: dim_customers`: dinasmakan `acct_owner` kepada `account_owner_id`. Mempengaruhi: semua CRM dashboards. Migrasi: lihat #1284. (@camellia)
- `stg_stripe__charges`: tangani `customer_id` null dari pembayaran tetamu. (@camellia)

Tiga peraturan untuk CHANGELOG:

1. Setiap PR menambah entri. Tiada PR dihantar tanpa satu. CI boleh menguatkuasakan ini.
2. Perubahan skema ditag breaking: dan dimaklumkan dalam #data-changes sebelum PR digabungkan.
3. Medan "mempengaruhi" menamakan papan pemuka atau model hiliran, bukan frasa samar seperti "pelbagai laporan". Jika anda tidak dapat menamakan apa yang ia pengaruhi, anda tidak mengetahui graf hiliran anda cukup baik untuk menggabungkan.

Perubahan skema juga mendapat pemansuhan beransur dalam kalender. Lajur lama kekal selama 30 hari dengan komen, lajur baru ditambah secara selari, pengguna hiliran berpindah, kemudian lajur lama dijatuhkan. Tiada pengecualian untuk penamaan semula yang "mudah". Penamaan semula yang mudah adalah cara anda mendapati seseorang membina aliran Zapier di atas gudang data anda dan tiada siapa yang memberitahu anda.

Papan Pemuka berbanding Ad Hoc: Bila Perlu Bina Apa

Peraturan keputusan: adakah nombor ini akan diminta semula dalam 30 hari?

• Ya maka ia adalah papan pemuka. Nombor itu berada di belakang model. Papan pemuka mempunyai pemilik.
• Tidak maka hantar CSV. Pautkan SQL dalam thread permintaan atau PR. Teruskan.

Kesilapan adalah membina papan pemuka untuk setiap permintaan ad hoc. Enam bulan kemudian anda mempunyai 400 papan pemuka, 350 daripadanya tidak dibuka sejak hari ia dihantar, dan Data Analyst yang membinaanya sudah meninggalkan syarikat.

Papan pemuka mempunyai ruang pemilik. Bukan "pasukan data". Sebuah nama. Jika papan pemuka wujud tanpa pemilik bernama, arkibkannya. Kerja pemilik adalah untuk menjawab "adakah ini masih betul?" apabila pihak berkepentingan bertanya. Jika tiada siapa memilikinya, tiada siapa boleh menjawab, dan anda kembali kepada masalah kepercayaan.

SQL sekali guna masuk ke dalam folder analyses/ dalam projek dbt anda, dikomit dan disemak tetapi tidak dimodelkan. Itu memberikan anda jejak kertas tanpa mengembungkan gudang data.

Jadual Pemansuhan

Gudang data reput seperti loteng reput, perlahan kemudian sekaligus. Jadual yang mencegahnya:

• Semakan suku tahunan papan pemuka mengikut tarikh dilihat terakhir. Kebanyakan alat BI mendedahkan ini. Tarik senarai setiap suku.
• 60 hari tidak dilihat: sepanduk pemansuhan beransur. Mesej "papan pemuka ini dijadualkan untuk pengarkiban pada YYYY-MM-DD" yang kelihatan. Maklumkan pemilik. Memaksa keputusan.
• 90 hari tidak dilihat: diarkibkan. Pindahkan ke folder archive/. Tandakan model asas enabled: false dalam konfigurasi dbt supaya ia berhenti dibina.

# models/marts/legacy/_legacy.yml
models:
  - name: fct_orders_2024_legacy
    config:
      enabled: false
    description: |
      Diarkibkan 2026-04-29, digantikan oleh fct_orders.
      Ditanya terakhir 2026-01-12. Pemilik meninggalkan syarikat 2025-S4.

Selepas setahun jadual ini, anda akan memotong bilangan model sebanyak 30-40% tanpa sebarang aduan hiliran. Model yang anda potong sudah mati. Anda hanya menjadikannya rasmi supaya ia berhenti memakan pengiraan dan mengelirukan Data Analyst baharu.

Perangkap Biasa

Empat yang paling kerap saya lihat, disusun mengikut kerosakan yang ditimbulkan:

1. Pemodelan sebelum bersetuju tentang definisi. Bina audit. Dapatkan definisi. Kemudian bina model. Urutan yang lain menghasilkan masalah dua-hasil pada gudang data yang segar.
2. Melangkau pementasan kerana "ia hanya penamaan semula". Melangkau pementasan bermakna setiap model hiliran rosak pada hari skema sumber berubah. Pementasan adalah penampan kejutan. Bayar cukai yang membosankan.
3. Membiarkan fail _v2 berganda. Setiap _v2 adalah pengakuan bahawa seseorang takut memecahkan yang asal. Betulkan yang asal. Komunikasikan perubahan. Hantar satu model.
4. Tiada pemilik pada model kritikal. fct_revenue tanpa pemilik adalah liabiliti dengan fail SQL. Ruang pemilik adalah wajib dalam YAML model anda, sama seperti ujian.

Apa yang "Selesai" Kelihatan

Anda telah membina disiplin ini apabila:

• Setiap metrik perniagaan mempunyai tepat satu model yang mentakrifkannya.
• Setiap model mempunyai sekurang-kurangnya ujian unique dan not_null pada kunci utamanya.
• Setiap papan pemuka mempunyai pemilik bernama.
• Data Analyst baharu boleh menjejaki mana-mana nombor pada mana-mana papan pemuka kembali ke sumbernya (melalui mart, pertengahan, pementasan, sumber) dalam masa kurang daripada sepuluh minit, tanpa bertanya kepada sesiapa.
• CHANGELOG memberitahu anda apa yang berubah minggu lepas tanpa membuka Slack.
• Perbelanjaan pengiraan anda adalah tetap atau berkurangan walaupun bilangan model anda berkembang, kerana anda memansuhkan perkara yang mati.

Itulah penanda arasnya. Ia membosankan. Ia juga adalah cara anda berhenti menjadi pasukan yang dihubungi pada pukul 11 malam kerana board deck tidak diselaraskan.

Ketahui Lebih Lanjut

• Templat Deskripsi Kerja Data Analyst
• Terjemahan Pihak Berkepentingan: Permintaan Samar kepada Analisis yang Boleh Dihantar
• Reka Bentuk Papan Pemuka yang Mendorong Keputusan
• Perangkap Biasa Data Analyst