Workflow Automation

Windmill for Data Engineering: Building Reproducible ETL Scripts with Python and SQL

Nov 2, 2026 · 2 views
sqlautomationpythondata engineeringReproducibilityETLwindmillWorkflow

Modern data engineering is no longer just about moving data from point A to point B; it is about building resilient, observable, and reproducible systems. As data stacks grow in complexity, the need for tools that bridge the gap between agile scripting and robust workflow management becomes critical. Windmill has emerged as a powerful solution for this exact problem, offering a self-hostable platform that allows developers to build, deploy, and schedule complex workflows using familiar languages like Python and SQL. In this guide, we will explore how to leverage Windmill to create ETL scripts that are not only efficient but also fully reproducible and maintainable.

Why Choose Windmill for ETL Pipelines?

Traditional ETL tools often suffer from two major drawbacks: a steep learning curve involving proprietary graphical interfaces, or the fragility of standalone scripts that lack version control and monitoring. Windmill solves both by treating workflows as code. It provides a low-code/no-code interface for orchestration while allowing deep customization through code steps. This hybrid approach ensures that data engineers can rapidly prototype with SQL and Python, while DevOps teams can manage deployments via Git.

Key benefits include:

  • Code-First Approach: Every step is a script or API call, making it easy to integrate with existing CI/CD pipelines.
  • Native Python and SQL Support: Direct access to your data stores without heavy SDK dependencies.
  • Reproducibility: Exact input parameters and code versions are logged for every execution, ensuring you can debug past issues.
  • Self-Hosted Security: Keep your data and code within your infrastructure boundaries.

Architecting a Reproducible ETL Workflow

A reproducible ETL workflow in Windmill typically consists of three main stages: Extraction, Transformation, and Loading. The critical component for reproducibility is the input validation and configuration management. By defining typed inputs in Windmill, you ensure that every run of the pipeline knows exactly what parameters to use, eliminating "works on my machine" scenarios.

Step 1: Extracting Data with SQL

Windmill allows you to connect directly to your database (PostgreSQL, MySQL, etc.) and execute SQL queries as workflow steps. This is ideal for the Extraction phase. Consider a simple query to fetch raw order data.


# Windmill Step: SQL Query
# Connect to your PostgreSQL instance

def extract_raw_orders(conn, start_date: str, end_date: str):
    query = f"""
        SELECT 
            order_id, 
            customer_id, 
            amount, 
            created_at 
        FROM 
            public.orders 
        WHERE 
            created_at BETWEEN '{start_date}' AND '{end_date}'
    """
    return conn.execute(query).fetchall()

Step 2: Transforming with Python

Once data is extracted, Python steps shine in the Transformation phase. Windmill’s Python runtime supports standard libraries and even pandas for more complex manipulations. To ensure reproducibility, avoid hardcoding logic; instead, pass configuration objects or use versioned libraries.


# Windmill Step: Python Transformation

import pandas as pd
from datetime import datetime

def transform_orders(raw_data: list[dict]):
    df = pd.DataFrame(raw_data)
    
    # Calculate derived metrics
    df['order_year'] = df['created_at'].dt.year
    df['is_high_value'] = df['amount'] > 100
    
    # Clean data: Remove nulls in critical fields
    df = df.dropna(subset=['order_id', 'customer_id'])
    
    return df.to_dict('records')

Step 3: Loading with Versioned Scripts

For the Loading phase, best practice dictates using idempotent operations. In Windmill, you can write a Python step that upserts data into your data warehouse (like Snowflake or BigQuery). Using an upsert ensures that re-running the pipeline for the same date range does not create duplicates, a key aspect of reproducibility.


# Windmill Step: Python Loading (Idempotent Upsert)

import sqlalchemy

def load_orders(transformed_data: list[dict], warehouse_conn):
    engine = sqlalchemy.create_engine(warehouse_conn.url)
    for record in transformed_data:
        stmt = sqlalchemy.dialects.postgresql.upsert('analytics.orders', record)
        with engine.begin() as conn:
            conn.execute(stmt)

Best Practices for Reproducibility

  1. Version Control Inputs: Use Windmill’s input schemas to define required parameters. Log these inputs alongside the workflow run metadata.
  2. Pin Dependencies: In Python steps, use `requirements.txt` managed by Windmill to ensure the same library versions are used across environments.
  3. Immutable Data Patterns: Whenever possible, use partitioned tables or snapshot strategies in your destination database to preserve historical data states.
  4. Test Locally First: Windmill supports local execution of steps. Always test Python transformations on sample data before deploying the full workflow.

Conclusion

Building ETL pipelines with Windmill shifts the paradigm from brittle scripts to managed, reproducible workflows. By combining the flexibility of Python and the efficiency of SQL within a unified platform, data engineers can build systems that are easier to debug, scale, and maintain. The emphasis on typed inputs and versioned code ensures that every data point in your warehouse can be traced back to its exact source and transformation logic. As your data stack grows, adopting a code-first, workflow-centric approach like Windmill will pay dividends in reliability and developer velocity.

Share:
Related Posts