Automating Cloud Data Pipelines with Python, DuckDB, and GitHub Actions

Building production analytics data pipelines traditionally required maintaining complex, expensive infrastructure: Apache Airflow clusters, dedicated PostgreSQL instances, AWS Glue jobs, and Snowflake data warehouses. For small to mid-sized engineering teams processing gigabytes to tens of gigabytes of analytical data daily, this infrastructure introduces massive operational overhead and thousands of dollars in monthly cloud hosting bills.

In this architecture guide, we demonstrate how to build zero-cost serverless ETL pipelines that extract, transform, and publish analytics using Python, DuckDB's in-process OLAP engine, and GitHub Actions as an automated workflow orchestrator.

1. Why Traditional Cloud Data Warehousing is Overkill

Traditional data stacks rely on dedicated compute clusters running 24/7. When handling batch analytics that only run once every hour or once daily, your database sits idle 95% of the time while incurring continuous hourly costs.

Traditional ETL Stack:
[Data Sources] ──> [Airflow Worker] ──> [AWS S3 Staging] ──> [Snowflake / BigQuery]
Cost: $300 - $1,500 / month | Setup: Days of Terraform & IAM policies

By leveraging DuckDB—an in-process columnar database built specifically for analytical workloads—we can execute lightning-fast vectorized SQL queries directly inside an ephemeral GitHub Actions runner with 7GB of RAM and 4 virtual CPU cores for zero dollars.

Serverless DuckDB Stack:
[Data Sources] ──> [GitHub Actions Runner + DuckDB] ──> [Parquet File on S3 / CDN]
Cost: $0 / month | Setup: 1 YAML file + 1 Python script

2. DuckDB: The SQLite of Analytical Data

Unlike SQLite, which is row-oriented and optimized for transactional operations (OLTP), DuckDB is columnar and vectorized. It processes operations on millions of rows simultaneously using SIMD CPU instructions and zero-copy data streaming.

Key Architectural Advantages

1. Direct Parquet & S3 Reading: DuckDB can query remote, compressed Parquet files directly over HTTPS without loading them into memory first.
2. Out-of-Core Processing: If a dataset exceeds available RAM, DuckDB streams data to disk partitions gracefully without crashing with out-of-memory (OOM) exceptions.
3. Zero Configuration: No server daemon, no port forwarding, no credentials to manage locally. It runs as a self-contained Python package.

3. Designing the End-to-End Pipeline

Here is a complete, production-grade Python script that extracts raw API records, cleans and aggregates the data using DuckDB SQL, and exports compressed Apache Parquet partitions.

# pipeline/etl_job.py
import os
import duckdb
import requests
import datetime
import pyarrow.parquet as pq

RAW_DATA_URL = "https://api.github.com/repos/torvalds/linux/commits?per_page=100"
OUTPUT_DIR = "data/processed"

def extract_raw_records():
    print("⬇️ Extracting commit activity from GitHub API...")
    headers = {"Accept": "application/vnd.github.v3+json"}
    response = requests.get(RAW_DATA_URL, headers=headers)
    response.raise_for_status()
    return response.json()

def transform_and_export(records):
    os.makedirs(OUTPUT_DIR, exist_ok=True)
    today = datetime.date.today().isoformat()
    
    # Initialize in-process DuckDB engine
    con = duckdb.connect(database=":memory:")
    
    # Register JSON records as a virtual table
    con.execute("CREATE TABLE raw_commits AS SELECT * FROM records")
    
    # Run high-performance vectorized transformation
    query = """
        SELECT 
            commit.author.name AS author_name,
            commit.author.email AS author_email,
            CAST(commit.author.date AS TIMESTAMP) AS committed_at,
            commit.message AS commit_message,
            sha,
            LENGTH(commit.message) AS message_length
        FROM raw_commits
        WHERE commit.author.name IS NOT NULL
        ORDER BY committed_at DESC
    """
    
    transformed_df = con.execute(query).fetch_df()
    print(f"✅ Transformed {len(transformed_df)} records via DuckDB.")

    # Export partitioned, Snappy-compressed Parquet
    output_file = os.path.join(OUTPUT_DIR, f"commits_{today}.parquet")
    con.execute(f"COPY ({query}) TO '{output_file}' (FORMAT PARQUET, COMPRESSION SNAPPY)")
    print(f"📦 Exported compressed Parquet to {output_file}")

    # Summary analytics
    summary = con.execute("""
        SELECT 
            COUNT(DISTINCT author_name) AS unique_contributors,
            AVG(message_length) AS avg_message_len,
            MIN(committed_at) AS oldest_commit,
            MAX(committed_at) AS latest_commit
        FROM ({query})
    """).fetchall()
    
    print("📊 Pipeline Run Summary:", summary)

if __name__ == "__main__":
    raw_data = extract_raw_records()
    transform_and_export(raw_data)

4. Scheduling with GitHub Actions

GitHub Actions provides 2,000 free runner minutes per month for private repositories and unlimited minutes for public repositories. We create a cron workflow that checks out the repository, installs dependencies, runs DuckDB, and commits or uploads the resulting Parquet files.

# .github/workflows/daily-pipeline.yml
name: Daily Serverless Data Pipeline

on:
  schedule:
    # Run daily at 03:00 UTC
    - cron: '0 3 * * *'
  workflow_dispatch:

jobs:
  run-pipeline:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout Repository
        uses: actions/checkout@v4

      - name: Setup Python 3.11
        uses: actions/setup-python@v4
        with:
          python-version: '3.11'
          cache: 'pip'

      - name: Install Pipeline Dependencies
        run: |
          pip install duckdb pandas requests pyarrow

      - name: Execute DuckDB Extraction & Transformation
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
        run: |
          python pipeline/etl_job.py

      - name: Commit Processed Parquet Data
        run: |
          git config --global user.name "dataops-bot"
          git config --global user.email "dataops-bot@users.noreply.github.com"
          git add data/processed/
          if git diff --staged --quiet; then
            echo "No new data changes."
          else
            git commit -m "chore(data): auto-update Parquet partitions [skip ci]"
            git push origin main
          fi

5. Performance Benchmarks: DuckDB vs. Pandas vs. PostgreSQL

In our benchmarking processing a 15GB CSV file containing 25 million records on a standard 4-core, 8GB machine:

Frequently Asked Questions (FAQ)

Can DuckDB directly query files in AWS S3 or Cloudflare R2?

Yes. DuckDB includes an official httpfs extension that supports querying remote files over HTTP/S3 directly:

INSTALL httpfs;
LOAD httpfs;
SET s3_region='us-east-1';
SELECT * FROM 's3://my-bucket/analytics/*.parquet' WHERE status = 'completed';

What happens when dataset size exceeds available runner memory?

DuckDB uses streaming out-of-core algorithms. When memory reaches the configured threshold (e.g. SET max_memory='6GB'), it dynamically spills intermediate hash tables and sort buffers to temporary disk storage without failing the job.

Is DuckDB suitable for high-concurrency multi-user websites?

DuckDB is designed for single-process analytical (OLAP) processing, not thousands of concurrent transactional writes (OLTP). For multi-user concurrent applications, use PostgreSQL or SQLite for transactions, and periodically replicate data into DuckDB or Parquet files for complex analytical reporting.