Data Pipeline & ETL Expert
English | Bahasa Indonesia
English
Description
A specialized skill for building robust data architectures, Analytics Engineering, and ETL (Extract, Transform, Load) or ELT pipelines. It covers modern data stack orchestration (Airflow, Dagster), transformation tools (dbt), and high-performance OLAP databases (BigQuery, Snowflake, ClickHouse, DuckDB).
Trigger Conditions
- When designing reporting dashboards or analytics infrastructure for a SaaS.
- When moving large volumes of data from transactional databases (PostgreSQL/MySQL) to a data warehouse.
- When the user asks about "dbt", "Airflow", "ELT", or "Analytics Engineering".
- When building local or edge analytics using DuckDB.
Core Architectural Guidelines
1. ELT over ETL
Prefer Extract-Load-Transform (ELT) over traditional ETL.
- Extract & Load: Use tools like Airbyte or Fivetran to dump raw data directly into the Data Warehouse.
- Transform: Perform transformations inside the Data Warehouse using SQL (via dbt) to leverage the warehouse's massive compute power.
2. Analytics Engineering with dbt
Treat SQL like software engineering.
- Use
dbt (Data Build Tool) to version control your SQL transformations.
- Implement tests (
not_null, unique) on critical tables.
- Use Jinja templating in dbt to DRY up complex SQL queries.
3. Data Orchestration (Airflow vs Dagster)
- Apache Airflow: The industry standard for scheduling and monitoring complex DAGs (Directed Acyclic Graphs). Best for Python-heavy teams.
- Dagster: A modern alternative focused on data assets rather than just tasks. Use Dagster when you want better local testing and asset-driven lineage.
4. OLAP Database Selection
- BigQuery / Snowflake: Best for massive scale, fully managed cloud data warehousing.
- ClickHouse: Best for real-time, sub-second analytical queries on massive event streams.
- DuckDB: Best for local analytics, embedded analytical pipelines, or processing parquets in edge environments (Node.js/Python).
Orchestration & Integration
- Pairs with
data-telemetry-expert to process the raw telemetry events captured by PostHog/OpenTelemetry.
- Complements
python-programming-expert as Python is the lingua franca of data engineering.
- Works with
cron-scheduler-expert when simpler, non-DAG cron jobs are sufficient for small ETL tasks.
Bahasa Indonesia
Deskripsi
Panduan khusus untuk membangun arsitektur data yang kuat, Analytics Engineering, dan pipeline ETL/ELT. Mencakup orkestrasi (Airflow, Dagster), alat transformasi (dbt), dan database OLAP berkinerja tinggi (BigQuery, Snowflake, ClickHouse, DuckDB).
Kondisi Pemicu
- Saat merancang infrastruktur analitik atau dashboard pelaporan untuk SaaS.
- Saat memindahkan data bervolume besar dari database transaksional ke Data Warehouse.
- Saat membangun analitik lokal yang cepat menggunakan DuckDB.
Panduan Arsitektur Inti
1. ELT lebih disukai daripada ETL
- Extract & Load: Pindahkan data mentah (raw data) langsung ke Data Warehouse (menggunakan Airbyte/Fivetran).
- Transform: Lakukan transformasi data di dalam Data Warehouse menggunakan SQL (dbt) untuk memanfaatkan kekuatan komputasi gudang data yang masif.
2. Analytics Engineering dengan dbt (Data Build Tool)
Perlakukan transformasi data (SQL) layaknya rekayasa perangkat lunak. Gunakan dbt untuk version control, pengujian otomatis (not_null, unique), dan dokumentasi skema data Anda.
3. Orkestrasi Data (DAG)
Gunakan Apache Airflow atau Dagster untuk menjadwalkan dan memonitor alur kerja data yang kompleks (DAG). Dagster sangat direkomendasikan untuk pendekatan modern yang berpusat pada aset data (asset-driven orchestration).
4. Pemilihan Database OLAP
- BigQuery / Snowflake: Gudang data cloud fully-managed untuk analitik skala masif.
- ClickHouse: Sangat cepat untuk kueri analitik real-time. Cocok untuk data event stream/log.
- DuckDB: SQLite untuk analitik. Sangat cepat untuk memproses file Parquet atau CSV secara lokal maupun di lingkungan edge/serverless (via Python/Node.js).
Integrasi Orkestrasi
- Bekerja sama dengan
data-telemetry-expert untuk memproses data mentah yang dikumpulkan.
- Melengkapi
python-programming-expert dalam menulis skrip orkestrasi Airflow/Dagster.
1---2name: data-pipeline-etl-expert3description: Expert guide for Data Pipelines, ETL/ELT, and Analytics Engineering. Covers dbt, Apache Airflow, Dagster, BigQuery, ClickHouse, and DuckDB / Panduan ahli untuk Data Pipelines, ETL/ELT. Mencakup dbt, Airflow, Dagster, BigQuery, ClickHouse, dan DuckDB.4---56# Data Pipeline & ETL Expert78[English](#english) | [Bahasa Indonesia](#bahasa-indonesia)910---1112<a name="english"></a>13## English1415### Description16A specialized skill for building robust data architectures, Analytics Engineering, and ETL (Extract, Transform, Load) or ELT pipelines. It covers modern data stack orchestration (Airflow, Dagster), transformation tools (dbt), and high-performance OLAP databases (BigQuery, Snowflake, ClickHouse, DuckDB).1718### Trigger Conditions19- When designing reporting dashboards or analytics infrastructure for a SaaS.20- When moving large volumes of data from transactional databases (PostgreSQL/MySQL) to a data warehouse.21- When the user asks about "dbt", "Airflow", "ELT", or "Analytics Engineering".22- When building local or edge analytics using DuckDB.2324### Core Architectural Guidelines2526#### 1. ELT over ETL27Prefer Extract-Load-Transform (ELT) over traditional ETL.28- **Extract & Load**: Use tools like Airbyte or Fivetran to dump raw data directly into the Data Warehouse.29- **Transform**: Perform transformations *inside* the Data Warehouse using SQL (via dbt) to leverage the warehouse's massive compute power.3031#### 2. Analytics Engineering with dbt32Treat SQL like software engineering.33- Use `dbt` (Data Build Tool) to version control your SQL transformations.34- Implement tests (`not_null`, `unique`) on critical tables.35- Use Jinja templating in dbt to DRY up complex SQL queries.3637#### 3. Data Orchestration (Airflow vs Dagster)38- **Apache Airflow**: The industry standard for scheduling and monitoring complex DAGs (Directed Acyclic Graphs). Best for Python-heavy teams.39- **Dagster**: A modern alternative focused on data assets rather than just tasks. Use Dagster when you want better local testing and asset-driven lineage.4041#### 4. OLAP Database Selection42- **BigQuery / Snowflake**: Best for massive scale, fully managed cloud data warehousing.43- **ClickHouse**: Best for real-time, sub-second analytical queries on massive event streams.44- **DuckDB**: Best for local analytics, embedded analytical pipelines, or processing parquets in edge environments (Node.js/Python).4546## Orchestration & Integration47- Pairs with `data-telemetry-expert` to process the raw telemetry events captured by PostHog/OpenTelemetry.48- Complements `python-programming-expert` as Python is the lingua franca of data engineering.49- Works with `cron-scheduler-expert` when simpler, non-DAG cron jobs are sufficient for small ETL tasks.5051---5253<a name="bahasa-indonesia"></a>54## Bahasa Indonesia5556### Deskripsi57Panduan khusus untuk membangun arsitektur data yang kuat, Analytics Engineering, dan pipeline ETL/ELT. Mencakup orkestrasi (Airflow, Dagster), alat transformasi (dbt), dan database OLAP berkinerja tinggi (BigQuery, Snowflake, ClickHouse, DuckDB).5859### Kondisi Pemicu60- Saat merancang infrastruktur analitik atau dashboard pelaporan untuk SaaS.61- Saat memindahkan data bervolume besar dari database transaksional ke Data Warehouse.62- Saat membangun analitik lokal yang cepat menggunakan DuckDB.6364### Panduan Arsitektur Inti6566#### 1. ELT lebih disukai daripada ETL67- **Extract & Load**: Pindahkan data mentah (raw data) langsung ke Data Warehouse (menggunakan Airbyte/Fivetran).68- **Transform**: Lakukan transformasi data *di dalam* Data Warehouse menggunakan SQL (dbt) untuk memanfaatkan kekuatan komputasi gudang data yang masif.6970#### 2. Analytics Engineering dengan dbt (Data Build Tool)71Perlakukan transformasi data (SQL) layaknya rekayasa perangkat lunak. Gunakan dbt untuk version control, pengujian otomatis (`not_null`, `unique`), dan dokumentasi skema data Anda.7273#### 3. Orkestrasi Data (DAG)74Gunakan Apache Airflow atau Dagster untuk menjadwalkan dan memonitor alur kerja data yang kompleks (DAG). Dagster sangat direkomendasikan untuk pendekatan modern yang berpusat pada aset data (asset-driven orchestration).7576#### 4. Pemilihan Database OLAP77- **BigQuery / Snowflake**: Gudang data cloud fully-managed untuk analitik skala masif.78- **ClickHouse**: Sangat cepat untuk kueri analitik real-time. Cocok untuk data event stream/log.79- **DuckDB**: SQLite untuk analitik. Sangat cepat untuk memproses file Parquet atau CSV secara lokal maupun di lingkungan edge/serverless (via Python/Node.js).8081## Integrasi Orkestrasi82- Bekerja sama dengan `data-telemetry-expert` untuk memproses data mentah yang dikumpulkan.83- Melengkapi `python-programming-expert` dalam menulis skrip orkestrasi Airflow/Dagster.
roedyrustam