Kelompok 4 dari 10
“Datanya dirapikan dan diambil bagaimana”
Analytics Engineer merapikan data kasir sebelum ada yang menghitungnya: nama menu diseragamkan dan transaksi batal dibuang. Analis lalu mengambil data yang sudah rapi dengan SQL. Di sini juga ada istilah data modeling, cara menyusun tabel supaya angkanya tidak berlipat.
Transformasi dan Analisis: lapisan 4 dan 5 dari 8. Lihat petanya
Satu kejadian atau satu benda di dalam tabel, ditulis memanjang ke samping.
Sering tertukar
Row, record, dan observation menunjuk hal yang sama. Pilihan katanya tergantung siapa yang bicara, dan isinya tetap sama.
Satu jenis keterangan yang dicatat untuk setiap baris.
Kolom "tanggal_pesanan" berisi tanggal, dan hanya tanggal, di seluruh baris.
Kumpulan baris dan kolom tentang satu hal saja.
Tabel pelanggan berisi orang. Tabel pesanan berisi pembelian. Keduanya dipisah, lalu disambung saat dibutuhkan.
Jenis isi kolom: angka, teks, tanggal, atau benar/salah. Kalau tipenya salah, hitungan ikut salah atau gagal.
Gaji yang ditulis 'Rp5 juta' terbaca sebagai teks, jadi tidak bisa dijumlahkan.
Daftar penjelasan setiap kolom: namanya, artinya, satuannya, dan nilai yang mungkin.
Channel: 1 = Horeca (hotel, restoran, kafe), 2 = Retail.
Juga disebut: pembersihan data
Pekerjaan merapikan data sebelum dihitung: menyeragamkan penulisan, membuang yang ganda, menambal yang kosong.
"Jakarta", "jakarta", dan "DKI Jakarta" harus jadi satu sebelum dihitung per kota.
Diagramnya: Medallion
Data yang berisi kesalahan: dobel, kosong, salah ketik, atau formatnya beda-beda. Kalau dipakai mentah, hasil analisis ikut salah.
Nama kota ditulis 'Jkt', 'Jakarta', dan 'DKI Jakarta' di kolom yang sama.
Juga disebut: data mentah
Data yang belum disentuh siapa pun, apa adanya seperti saat direkam.
Sering tertukar
Raw data belum diubah siapa pun, tapi itu tidak membuatnya benar. Salah ketik, nilai kosong, dan baris ganda paling sering ditemukan justru di raw data.
Menelusuri data mana yang relevan dengan masalah dan bagaimana kondisinya, sebelum mulai mengolah.
Mencari tabel yang mencatat retur dan sejak kapan kolomnya terisi.
Mengecek apakah data yang masuk sudah sesuai sumbernya dan masuk akal, sebelum dipakai.
Mencocokkan total penjualan di warehouse dengan total di aplikasi kasir.
Mengubah bentuk data supaya siap dipakai: membersihkan, menggabung, menghitung kolom baru, atau meringkas.
Menghitung omzet dari harga kali jumlah, lalu meringkasnya per bulan.
Juga disebut: nilai hilang
Nilai yang kosong atau tidak tercatat. Bisa tampil sebagai sel kosong, NA, tanda tanya, atau nol yang menyesatkan.
243.007 baris data Online Retail II tidak punya Customer ID.
Tanda bahwa nilainya tidak diketahui, bukan tanda bahwa nilainya nol.
Sering tertukar
Nol berarti "diukur, hasilnya nol". NULL berarti "belum pernah diukur". Rata-rata yang menghitung NULL sebagai nol akan turun tanpa alasan.
Baris yang isinya sama dan tercatat lebih dari sekali.
Satu pelanggan mendaftar dua kali dengan email berbeda. Sistem melihat dua orang, kenyataannya satu.
Diagramnya: Medallion
Menyeragamkan cara penulisan nilai yang sebenarnya sama, supaya terhitung sebagai satu kelompok.
'Data Analyst', 'Analis Data', dan 'DA' dijadikan satu judul baku.
Pola untuk mencari atau mengambil potongan teks, misalnya deretan angka di tengah kalimat. Di Sheets dipakai lewat REGEXEXTRACT dan REGEXREPLACE.
Pola [0-9.]+ mengambil 5.000.000 dari teks gaji "Rp5.000.000 - Rp7.000.000"
Ringkasan banyak baris jadi sedikit baris, dikelompokkan menurut kolom tertentu.
440 pelanggan grosir dirangkum jadi dua baris: 298 Horeca dan 142 Retail (data grosir Portugal).
Mengambil nilai dari tabel lain berdasarkan kunci yang sama di kedua tabel.
XLOOKUP mengambil nama cabang dari tabel cabang berdasarkan kode cabang.
Cara lama mencari nilai di tabel lain, dipakai di Excel yang belum punya XLOOKUP. Bedanya, INDEX-MATCH tidak terikat urutan kolom.
MATCH mencari posisi baris nama kota, INDEX mengambil nama provinsinya dari baris itu.
Rentang sel tanpa nomor baris akhir, misalnya A2:A. Baris baru yang ditambahkan nanti ikut terhitung tanpa rumusnya diubah.
Pivot dari Lowongan!A1:AK ikut membaca lowongan yang di-paste pekan depan.
Wide: satu baris per objek, tiap tahun jadi kolom. Long: satu baris per objek per tahun, nilainya di satu kolom.
Data indikator per wilayah disimpan long: kolom wilayah, indikator, tahun, nilai.
Bahasa untuk menulis query, dan skill yang paling sering diminta di lowongan pemula.
Sering tertukar
SQL
Hanya untuk bertanya kepada database, jadi cepat dipelajari.
Bahasa pemrograman umum: skrip, pipeline, sampai model.
Python dipakai untuk hampir apa saja, dari skrip sampai model. SQL hanya dipakai untuk bertanya kepada database, dan karena cakupannya sempit, SQL lebih cepat dipelajari.
Diagramnya: SQL
Permintaan ke database untuk mengambil data tertentu.
"Ambil semua pesanan bulan Agustus dari cabang Bandung" ditulis dalam SQL.
Diagramnya: SQL
SELECT menyebut kolom yang ingin ditampilkan. WHERE menyaring baris yang memenuhi syarat.
SELECT tanggal, harga FROM harga WHERE komoditas = 'Beras'
Membuang baris yang tidak dibutuhkan, dan menyisakan yang dicari.
Diagramnya: SQL
Perintah SQL untuk menampilkan nilai yang berbeda saja, tanpa pengulangan.
SELECT DISTINCT kota FROM pelanggan menampilkan daftar kota tanpa dobel.
Mengelompokkan baris yang nilainya sama, lalu menghitung satu ringkasan per kelompok.
GROUP BY provinsi menghasilkan satu baris per provinsi.
Menyaring hasil sesudah dikelompokkan. Jadi WHERE menyaring baris, sedangkan HAVING menyaring kelompok.
HAVING COUNT(DISTINCT market) >= 3: hanya provinsi yang datanya dari minimal tiga pasar.
Sering tertukar
Menyaring baris, sebelum dikelompokkan.
HAVING
Menyaring kelompok, sesudah GROUP BY.
Meringkas banyak baris jadi satu angka: jumlah, rata-rata, hitungan, terbesar, terkecil.
Lima ribu baris pesanan diringkas jadi satu angka omzet per bulan.
Diagramnya: SQL
Aturan 'kalau ini, maka itu' di dalam query, untuk membuat kolom baru dari kondisi.
CASE WHEN total >= 1000000 THEN 'besar' ELSE 'kecil' END.
Variasi SQL antar database. Dasarnya sama, tapi nama fungsi tertentu bisa berbeda.
Fungsi tanggal di DuckDB, BigQuery, dan MySQL ditulis sedikit berbeda.
Kolom yang isinya berbeda di setiap baris, dipakai untuk menunjuk satu baris secara pasti.
Nomor pesanan. Tidak ada dua pesanan dengan nomor sama.
Diagramnya: Star schema
Kolom yang isinya menunjuk ke baris di tabel lain.
Kolom "id_pelanggan" di tabel pesanan menunjuk siapa yang memesan, tanpa menulis ulang nama dan alamatnya.
Sering tertukar
Menunjuk satu baris di tabelnya sendiri, misalnya nomor pesanan.
Foreign key
Menunjuk baris di tabel lain, misalnya id_pelanggan di tabel pesanan.
Diagramnya: Star schema
Hubungan dua tabel lewat kolom kunci. Misalnya, satu pelanggan bisa punya banyak pesanan.
Tabel pelanggan terhubung ke tabel pesanan lewat ID pelanggan.
Menyambung dua tabel dengan mencocokkan kolom yang sama.
Tabel penjualan disambung ke tabel menu lewat kolom "id_menu", jadi tiap baris penjualan mendapat nama menunya.
Diagramnya: Star schema
Semua baris tabel kiri tetap disimpan walau tidak punya pasangan di tabel kanan, dan yang tidak berpasangan diisi kosong.
Semua pasar tetap muncul di hasil; pasar yang tidak punya data harga beras tampil kosong.
Sering tertukar
Hanya baris yang punya pasangan di kedua tabel yang tersisa.
LEFT JOIN
Semua baris tabel kiri tetap ada; yang tak berpasangan diisi kosong.
Jawaban atas pertanyaan "satu baris di tabel ini artinya apa".
Kalau satu baris berarti satu menu di dalam satu pesanan, maka pesanan berisi tiga menu akan muncul tiga kali.
Sering tertukar
Salah membaca grain adalah sebab paling sering angka jadi berlipat. Sebelum menghitung apa pun, pastikan Anda tahu satu baris itu mewakili apa.
Diagramnya: Star schema
Baris yang berlipat sendiri setelah join, karena satu baris di tabel kiri cocok dengan beberapa baris di tabel kanan.
Satu pesanan berisi tiga menu. Setelah disambung ke tabel isi_pesanan, pesanan itu jadi tiga baris, dan menghitungnya sebagai tiga pesanan salah.
Sering tertukar
Query yang kena fan-out tetap berjalan mulus tanpa peringatan apa pun, dan hanya angka hasilnya yang salah. Karena tidak ada tandanya, fan-out termasuk kekeliruan paling mahal di pekerjaan data sehari-hari.
Menumpuk hasil dua query atau dua tabel jadi satu, baris di bawah baris. Baris yang sama tidak dibuang.
Data hotel resort (H1) dan hotel kota (H2) ditumpuk jadi satu tabel hotel dengan UNION ALL BY NAME.
Query di dalam tanda kurung yang hasilnya dipakai oleh query di luarnya.
Mencari pasar yang rata-rata harganya di atas rata-rata semua pasar.
Common table expression: potongan query yang diberi nama dengan WITH, lalu dipakai seperti tabel di langkah berikutnya.
WITH bersih AS (...) SELECT ... FROM bersih.
Fungsi yang menghitung sesuatu per kelompok tanpa meringkas barisnya, jadi setiap baris tetap ada dan mendapat kolom hasil tambahan.
ROW_NUMBER untuk memberi urutan belanja tiap pelanggan; LAG untuk mengambil nilai bulan sebelumnya.
Window function yang memberi nomor urut 1, 2, 3 di dalam tiap kelompok, mengikuti urutan yang Anda tentukan.
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY InvoiceDate): nomor 1 adalah belanja pertama tiap pelanggan.
Fungsi SQL untuk memotong tanggal ke awal periode, misalnya awal bulan, supaya data bisa dikelompokkan per bulan.
DATE_TRUNC('month', DATE '2010-10-31') menghasilkan 2010-10-01.
Query yang disimpan dengan nama, lalu dipakai seperti tabel. Isinya tidak di-copy, jadi view selalu mengikuti tabel aslinya.
View kk berisi baris INDO-DAPOER tingkat kab/kota saja, dibuat dengan CREATE VIEW kk AS SELECT ...
Perintah SQL untuk membuat tabel baru, menambah baris, dan mengubah isi baris yang sudah ada.
CREATE TABLE ringkasan_harian, lalu INSERT hasil rekap setiap pagi.
Daftar tabel apa saja yang ada, isi kolomnya apa, dan bagaimana tabel-tabel itu terhubung.
Memecah satu tabel besar jadi beberapa tabel kecil, supaya keterangan yang sama tidak ditulis berulang dan tetap konsisten.
Alamat toko disimpan sekali di tabel toko, tidak diulang di setiap baris transaksi.
Merancang bentuk tabel dan hubungannya supaya data mudah dianalisis dan angkanya konsisten.
Memutuskan tabel penjualan dipisah dari tabel produk dan tabel toko.
Fact table berisi kejadian yang diukur; dimension table berisi keterangan untuk mengelompokkannya.
Fakta: setiap transaksi dan nilainya; dimensi: produk, toko, tanggal.
Sering tertukar
Fact table
Kejadian yang diukur: setiap transaksi dan nilainya.
Dimension table
Keterangan untuk mengelompokkan: produk, toko, tanggal.
Susunan tabel berbentuk bintang: satu fact table di tengah, dikelilingi dimension table.
Fakta penjualan di tengah, terhubung ke dimensi produk, toko, pelanggan, dan tanggal.
Cara menyusun data di warehouse dalam tiga lapis. Bronze: raw data. Silver: sudah dibersihkan. Gold: siap untuk dashboard.
Bronze transaksi mentah, silver transaksi tanpa duplikat, gold penjualan per toko per hari.
Alat untuk menulis transformasi data dengan SQL di dalam warehouse, lengkap dengan dokumentasi dan tes otomatis.
Query pembersihan disimpan sebagai model dbt dan dites setiap pagi.