Part 6: Python Pipelines 🐍

Time: ~10 minutes | Prerequisites: Part 5 completed (quality tests running)

So far, every pipeline you have written has been pure SQL. That covers most use cases — but sometimes you need the flexibility of Python. Multi-step transformations, programmatic query building, intermediate variables, logging — Python pipelines let you do all of that while still writing to the same Iceberg tables.

In this part, you will create a Python pipeline that computes per-vehicle launch statistics using DuckDB and PyArrow.

When to Use Python vs SQL

Common reasons to reach for Python:

• Multi-step logic — fetch data, compute intermediate results, combine them
• Dynamic SQL — build queries programmatically based on runtime conditions
• Logging — emit progress messages to the run log for debugging
• PyArrow operations — column-level transformations using Arrow’s compute functions
• JSON / string parsing — complex parsing that’s awkward in SQL

Create the Python Pipeline

Let’s build launch_vehicle_stats — a Silver pipeline that computes per-vehicle performance statistics from the bronze launch data.

# @description: Launch vehicle performance statistics
# @merge_strategy: full_refresh
 
import json
from datetime import datetime
 
# Fetch launch data from the bronze table
launches = duckdb_conn.execute(f"""
    SELECT vehicle, outcome, payload_mass_kg, launch_date
    FROM {ref('bronze.mission_log')}
""").fetch_arrow_table()
 
log.info(f"Processing {len(launches)} launches")
 
# Compute per-vehicle statistics
stats = duckdb_conn.execute("""
    SELECT
        vehicle,
        COUNT(*) AS total_launches,
        SUM(CASE WHEN outcome = 'success' THEN 1 ELSE 0 END) AS successes,
        ROUND(100.0 * SUM(CASE WHEN outcome = 'success' THEN 1 ELSE 0 END) / COUNT(*), 1) AS success_rate_pct,
        SUM(payload_mass_kg) AS total_payload_kg,
        MIN(launch_date) AS first_launch,
        MAX(launch_date) AS last_launch
    FROM launches
    GROUP BY vehicle
    ORDER BY total_launches DESC
""").fetch_arrow_table()
 
log.info(f"Computed stats for {len(stats)} vehicles")
result = stats

Processing 25 launches
Computed stats for 8 vehicles

The result Contract

Every Python pipeline must set the result global to a PyArrow Table. This is the data that RAT writes to Iceberg. If your pipeline finishes without setting result — or sets it to something other than a pa.Table — the run will fail.

# Minimal valid pipeline — builds a table from scratch
result = pa.table({
    "id": [1, 2, 3],
    "name": ["Alice", "Bob", "Charlie"],
})

Available Globals

When your pipeline.py executes, RAT injects these globals into the execution scope. These are the only tools you need — no imports required for the basics.

Security Sandbox

Python pipelines run in a sandboxed environment. RAT blocks dangerous operations to prevent pipelines from interfering with the runner process or accessing the host system.

Blocked Built-in Functions

Allowed Imports

You can import these modules — they are explicitly allowed:

Blocked Imports

Everything else is blocked — notably os, sys, subprocess, socket, http, requests, urllib, pathlib, importlib, ctypes, multiprocessing, and threading.

AST Validation

RAT also performs static analysis on your code before execution. It blocks double-underscore attribute access (e.g., obj.__class__.__bases__) to prevent sandbox escape through Python’s object model.

Query the Results

Let’s verify the vehicle statistics are correct.

SELECT
    vehicle,
    total_launches,
    success_rate_pct,
    total_payload_kg,
    first_launch,
    last_launch
FROM default.silver.launch_vehicle_stats
ORDER BY total_launches DESC

SELECT vehicle, success_rate_pct
FROM default.silver.launch_vehicle_stats
WHERE success_rate_pct < 100
ORDER BY success_rate_pct ASC

What You Built

In this part, you:

• ✅ Created a Python pipeline (launch_vehicle_stats) using DuckDB + PyArrow
• ✅ Learned all 10 available globals and how they work
• ✅ Understood the result contract — every Python pipeline must set it to a PyArrow table
• ✅ Explored the security sandbox — what’s allowed and what’s blocked
• ✅ Queried the results to verify the computed statistics

Your pipeline graph now includes both SQL and Python pipelines. In the next part, you’ll wire them together with triggers so they run automatically.

Next: In Part 7, you’ll set up triggers to automate your entire pipeline chain — cron schedules, event-driven triggers, webhooks, and more.