Building ETL Pipelines with Apache Airflow and DuckDB

Modern data engineering workflows often require lightweight, high-performance local data processing pipelines without the heavy infrastructure overhead of distributed clusters.

By combining Apache Airflow for robust directed acyclic graph (DAG) workflow orchestration with DuckDB for fast in-process analytical SQL execution, you can build lightning-fast local ETL pipelines in Python.

Core Architecture of Airflow + DuckDB ETL

The local ETL architecture leverages two powerful Python-native tools designed for developer efficiency and analytical speed:

• Apache Airflow Scheduler: Orchestrates and monitors task dependencies, retries, and scheduled executions across Extract, Transform, and Load stages.
• DuckDB Vectorized Engine: Acts as an in-process SQL OLAP database, executing high-performance columnar transformations directly over Parquet, CSV, and Pandas dataframes.
• Local Storage Integration: Eliminates network round-trips by reading raw source files, executing SQL transforms in-memory, and persisting cleaned tables to local Parquet storage.

Traditional Warehouses vs. DuckDB + Airflow

Python Implementation Blueprint

Here is how to define an Apache Airflow DAG that utilizes DuckDB to extract CSV data, transform records, and load analytical tables in Python:

from datetime import datetime, timedelta
import duckdb
from airflow import DAG
from airflow.operators.python import PythonOperator

def run_duckdb_etl(**context):
    # Connect to local DuckDB file-based database
    conn = duckdb.connect(database='analytics.duckdb', read_only=False)
    
    # 1. Extract & Create staging table from raw CSV
    conn.execute("""
        CREATE TABLE IF NOT EXISTS staging_events AS 
        SELECT * FROM read_csv_auto('data/raw_events.csv');
    """)
    
    # 2. Transform: Aggregate daily active users and total transaction volume
    conn.execute("""
        CREATE OR REPLACE TABLE aggregated_daily_metrics AS 
        SELECT 
            CAST(event_timestamp AS DATE) AS event_date,
            COUNT(DISTINCT user_id) AS active_users,
            SUM(purchase_amount) AS total_revenue
        FROM staging_events
        GROUP BY event_date;
    """)
    
    print("ETL pipeline executed successfully in DuckDB.")
    conn.close()

default_args = {
    'owner': 'data_engineer',
    'retries': 1,
    'retry_delay': timedelta(minutes=5),
}

with DAG(
    dag_id='duckdb_local_etl_pipeline',
    default_args=default_args,
    start_date=datetime(2026, 1, 1),
    schedule_interval='@daily',
    catchup=False
) as dag:
    
    etl_task = PythonOperator(
        task_id='run_duckdb_transformations',
        python_callable=run_duckdb_etl
    )

ETL Pipeline Best Practices

When building local analytical data pipelines with Apache Airflow and DuckDB, follow these engineering guidelines:

• Manage DuckDB Concurrency: DuckDB is designed for single-writer access; ensure your Airflow DAG tasks execute sequential writes or use read-only connections for parallel workers.
• Leverage Parquet Formats: Store source and target staging files in compressed Parquet format rather than CSV to maximize DuckDB scan speeds and reduce memory footprint.
• Idempotent DAG Design: Write idempotent transformation queries (using CREATE OR REPLACE TABLE or partition overwrites) so failed Airflow tasks can be safely re-run without duplicating records.