Menjajal SQLMesh

SQLMesh dirilis awal Maret lalu. SQLMesh adalah sebuah framework DataOps. Di situsnya terpampang, "DataOps = Data + DevOps".

SQLMesh diprakarsai salah satunya oleh mantan insinyur data di Airbnb, organisasi laba yang juga jadi tempat lahirnya Airflow dan SuperSet. Dia juga yang bikin SQLGlot. Selain moncer di bidang teknologi, dia sepertinya juga seorang mantan juara dunia rubik.

Jadi hewan apa sebenarnya DataOps ini? Saya pun kurang jelas. Kalau boleh menduga, sepertinya, sih, upaya untuk membawa CI/CD dan praktik terbaik pengembangan perangkat lunak ke bidang data.

Kedudukan SQLMesh dalam modern data stack adalah sebagai alat transformasi, laiknya dbt. Visi misi meraka mirip, kalau memang tak boleh disebut sama. Membawa software engineering workflow & best practice ke bidang analitika data.

Saya mulai jadi pemakai dbt-core dan dbt-cloud sejak sekitar tiga tahun lalu. Dokumentasi mereka super bagus dan lengkap, komunitas besar dan diisi oleh orang-orang piawai dalam bidangnya. Meski begitu, ada saja hal-hal terbitan mereka yang bikin saya mengernyitkan dahi.

Pertama, bagaimana mereka mempromosikan FULL REFRESH tiap kali jalan, alih-alih model inkremental. Ini bikin biaya gudang data jadi bengkak.

Kedua, adalah kueri seperti ini:

with customers as (
    select * from {{ ref('stg_customers') }}
),
orders as (
    select * from {{ ref('stg_orders') }}
)
...

Geli. Kalau tak hati-hati, ini bisa bikin kita kehilangan pekerjaan.

Ketiga, dbt membikin replika di gudang data untuk tiap insinyur analitika. Ini juga bikin biaya bengkak.

SQLMesh dibikin juga berangkat dari masalah-masalah itu, terutama masalah ketiga. Silakan baca blog mereka tentang Virtual Data Environments untuk tahu lebih lanjut tentang hal ini.

Gudang data lokal

Tahun lalu, berkat penyesuai/adaptor dbt-duckdb, saya bisa bikin proyek kecil iseng-iseng untuk bikin gudang data di laptop. SQLMesh juga bisa membantu melakukan hal serupa. Bahkan, contoh proyek awal mereka pakai DuckDB sebagai mesin penyimpanan dan eksekusi.

Bikin gudang data lokal dan personal itu seru. Cari set data yang menarik, olah pakai SQL, bikin visualisasi. Seperti rekreasi.

Cari set data yang menarik ini yang agak susah.

Berkat sebuah ✨kebetulan yang ajaib✨, saya punya set data super menarik. Set data ini yang kupakai untuk menjajal SQLMesh.

SQLMesh bicara SQL dan Python. Persis dbt. Keunggulannya, tak perlu ada {{ ref('nama_tabel') }} untuk membuat SQLMesh ngerti dari mana data diambil. Cukup seperti menulis SQL biasa. Tiap model adalah satu file .sql. Di dalam file tersebut, model didefinisikan dengan cara:

MODEL (
  name source.detail_laporan,
  dialect duckdb,
  kind FULL,
  tags [source, parquet]
);

Nama model adalah nama yang ada dalam file, bukan nama file.

SQLMesh, karena dibikin pakai SQLGlot, bisa menerjemahkan kueri kita ke rasa-rasa SQL yang lain. Misal dari DuckDB ke BigQuery, atau ke Snowflake. Ini canggih, tapi juga bikin saya gak pede untuk pakai trik aneh-aneh di SQL.

Penerjemahan dari satu dialek ke dialek lain bisa gagal:

SELECT UNNEST([1,2,3]) -- Kueri valid DuckDB

SELECT EXPLODE([1, 2, 3]) -- Terjemahan SQLMesh/SQLGlot ke BigQuery

SQLMesh mempunyai konsep Audits. Dalam dbt, audit disebut sebagai 'test'. Audit dipakai untuk memvalidasi hasil keluaran model setelah dijalankan.

Di SQLMesh, audit adalah kueri SQL yang tidak mengembalikan baris data apa pun. Kueri dibikin untuk mencari data yang tidak valid.

AUDIT (
  name tegaskan_jumlah_id_lhkpn_sama,
  dialect duckdb
);

SELECT x-y
FROM (SELECT COUNT(DISTINCT @kolom) AS x FROM @this_model)
CROSS JOIN (SELECT COUNT(DISTINCT @kolom) AS y FROM @tabel)
WHERE x-y > 0

MODEL (
  name intermediate.nik_detail_laporan,
  dialect duckdb,
  kind FULL,
  tags [intermediate],
  audits [tegaskan_jumlah_id_lhkpn_sama(kolom=id_lhkpn, tabel=source.detail_laporan)]
);

Audit tersebut mencoba membandingkan jumlah id_lhkpn unik di tabel hulu source.detail_laporan dengan tabel hilir nik_detail_laporan, setelah mengalami badai perubahan, untuk memastikan tak ada data yang tak bertambah atau berkurang di model akhir. Dari kueri di atas juga bisa dilihat, SQLMesh mendukung macro.

Ada juga Test di SQLMesh. Cara kerjanya mirip dengan tes unit dalam pengembangan perangkat lunak. Berbeda dengan audit, tes dilakukan sebelum model dijalankan. Saya tak mencobanya karena pendefinisiannya terlalu rumit (YAML). Mungkin akan kupakai ketika mereka mendukung .csv sebagai data masukan.

SQLMesh punya UI Editor yang ciamik betul. Ini juga agaknya jadi alasan kenapa mereka menggempita. Jejak data bisa divisualisasikan sampai ke level kolom dan CTE. Sayangnya, jejak data ini tak bisa diterbitkan sebagai sesuatu yang terpisah dari editornya.

Yang bikin menarik, SQLMesh tak butuh information_schema atau metadata lain untuk bikin jejak data ini, mereka bangun hanya dari hasil pembacaan AST dari kueri SQL tiap model.

Dengan visualisasi macam ini, praktisi data jadi gampang untuk tahu dari mana data berasal. Untuk bikin model staging.ambil_ulang, saya ambil data dari tiga tempat: source.ringkasan_harta, source.gagal, dan source.laporan. Dua yang terakhir dapat data dari file parquet yang ada di lokal. CTE pun tampil ketika perlu tahu lebih detail dari mana kolom berasal.

SQLMesh menarik buat proyek senang-senang

Saya masih gak pede pakai SQLMesh buat kerjaan di awan, karena selain menerjemahkan ke dialek lain, SQLMesh (kayaknya) juga berusaha melakukan optimasi kueri. Apa yang kita tulis dan apa yang kita eksekusi bisa jadi beda. Buat saya, ini ide mengerikan.

Tapi SQLMesh menarik buat proyek senang-senang. Bikin gudang data lokal dengan set data yang menarik. Sumber data yang saya pakai adalah enam file parquet. Sebelum dienamkan pakai DuckDB, ada 7rb lebih file parquet, hasil scraping dari situs web.

MODEL (
  name source.harta,
  dialect duckdb,
  kind FULL,
  tags [source, parquet]
);

SELECT DISTINCT
    data_id::VARCHAR AS id_data,   
    id::BIGINT AS id_harta,
    parent_id::BIGINT AS id_induk_harta,
    UPPER(kategori)::VARCHAR AS kategori_harta,
    UPPER(harta_deskripsi)::VARCHAR AS deskripsi_harta,
    UPPER(harta_nilai)::BIGINT AS nilai_harta
FROM read_parquet('../data/harta.parquet')

Untuk struktur proyek, saya ambil inspirasi dari dbt:

> tree ~/Projects/lhkpn/staged/sqlmesh-lhkpn
├── audits
│   └── tegaskan_jumlah_id_lhkpn_sama.sql
├── config.yaml
├── macros
│   ├── __init__.py
│   └── __pycache__
│       └── __init__.cpython-311.pyc
├── models
│   ├── intermediate
│   │   ├── detail_harta_alat_transportasi_mesin.sql
│   │   ├── detail_harta_tidak_bergerak.sql
│   │   └── nik_detail_laporan.sql
│   ├── seeds
│   │   ├── kecamatan.sql
│   │   ├── kota_kabupaten.sql
│   │   └── provinsi.sql
│   ├── sources
│   │   ├── detail_laporan.sql
│   │   ├── gagal.sql
│   │   ├── harta.sql
│   │   ├── laporan.sql
│   │   ├── ringkasan_harta.sql
│   │   └── tabel_situs.sql
│   └── staging
│       ├── ambil_ulang.sql
│       ├── daerah.sql
│       ├── harta_alat_transportasi_mesin.sql
│       ├── harta_bergerak_lainnya.sql
│       ├── harta_kas_setara_kas.sql
│       ├── harta_lainnya.sql
│       ├── harta_surat_berharga.sql
│       ├── harta_tidak_bergerak.sql
│       ├── hutang.sql
│       ├── nik_daerah.sql
│       └── nik_id_lhkpn_diratakan.sql
├── requirements.txt
├── seeds
│   ├── kecamatan.csv
│   ├── kota_kabupaten.csv
│   └── provinsi.csv
└── tests

Proyek ini masih setengah matang. Belum kelar. Perlu tambah beberapa audit dan transformasi data lainnya. Repositori bisa diakses di:

GitHub - adityawarmanfw/sqlmesh-lhkpn: SQLMesh untuk proyek senang-senang, bikin gudang data lokal.

SQLMesh untuk proyek senang-senang, bikin gudang data lokal. - GitHub - adityawarmanfw/sqlmesh-lhkpn: SQLMesh untuk proyek senang-senang, bikin gudang data lokal.

GitHubadityawarmanfw

Meski jejak data tak bisa diterbitkan terpisah, SQLMesh tetap bisa memvisualisasikannya meski tanpa tersambung ke gudang data.

Menjalankan di lokal:

python -m venv .env
source .env/bin/activate
git clone https://github.com/adityawarmanfw/sqlmesh-lhkpn.git
cd sqlmesh-lhkpn
pip install -r requirements.txt
sqlmesh ui

Proyek gudang data lokal macam begini bisa terwujud berkat DuckDB yang bisa baca file parquet dari lokal dan daring. Ngomong-ngomong soal DuckDB, aku bikin situs web buat orang-orang belajar SQL pakai DuckDB Wasm.