Python Data Engineering Skill

Expert guidance for Python data engineering: DataFrame libraries (Polars, Pandas, PySpark), dbt Python models, API extraction, and data validation. Assumes Python proficiency.

Scope Constraints

SQL transforms in dbt: hand off to dbt-transforms
DLT pipeline config: hand off to data-integration
Kafka/Flink streaming: hand off to event-streaming
Dagster/Airflow orchestration: hand off to data-pipelines
General Python or web development: out of scope

When to Use

Activate when: choosing between DataFrame libraries, writing Polars/Pandas/PySpark transforms, building dbt Python models, building API extraction scripts, implementing data validation (Pydantic/Pandera/GX), optimizing DataFrame memory, or converting between DataFrame formats.

Model Routing

reasoning_demand	preferred	acceptable	minimum
medium	Sonnet	Sonnet, Opus	Sonnet

Core Principles

1. Type Safety First

Annotate all functions. Data pipelines process untrusted data — types catch errors before production.

from pydantic import BaseModel
from datetime import date
from decimal import Decimal
import polars as pl

class Order(BaseModel):
    order_id: str
    customer_id: str
    amount: Decimal
    order_date: date

def transform_orders(raw: pl.LazyFrame) -> pl.LazyFrame:
    return (
        raw.filter(pl.col("amount") > 0)
        .with_columns(
            pl.col("order_date").str.to_date("%Y-%m-%d"),
            pl.col("amount").cast(pl.Decimal(10, 2)),
        )
        .unique(subset=["order_id"])
    )

2. Immutable Transforms

Never mutate DataFrames in place. Return new DataFrames from every transformation for reproducibility and testability.

3. Lazy Evaluation When Possible

Prefer lazy evaluation (Polars LazyFrame, Spark DataFrame).

4. Memory Efficiency

Use appropriate dtypes (Int32 not Int64 when range allows). Stream/scan instead of loading entire files. Prefer columnar formats (Parquet, Arrow) over CSV/JSON.

5. Test Data Pipelines

Test transforms with small, representative fixtures. Use polars.testing.assert_frame_equal or pandas.testing.assert_frame_equal.

DataFrame Library Decision Matrix

Factor	Polars	Pandas	PySpark	DuckDB (Python)
Data size	Single machine (GB-TB via streaming)	Single machine (MB-GB)	Distributed cluster (TB-PB)	Single machine (GB-TB)
Speed	Very fast (Rust, multi-threaded)	Moderate (single-threaded)	Fast at scale (distributed)	Very fast (vectorized)
Memory	Efficient (Arrow-native, streaming)	Inefficient (copies, object dtype)	Efficient (distributed)	Efficient (out-of-core)
API style	Expression-based, method chaining	Index-based, mixed paradigms	SQL-like DataFrame API	SQL-first, DataFrame bridge
Lazy eval	Yes (LazyFrame)	No (eager only)	Yes (execution plan)	Yes (query plan)
dbt support	Via DataFrame return	Native (`dbt-core`)	Via `dbt-spark`	Via `dbt-duckdb`
Best for	New projects, performance-critical	Legacy code, ML integration	Big data, Databricks	Analytics, local dev

Polars (Primary)

import polars as pl

orders = (
    pl.scan_parquet("raw/orders/*.parquet")
    .filter(
        (pl.col("status").is_in(["completed", "shipped"]))
        & (pl.col("amount") > 0)
    )
    .with_columns(
        pl.col("order_date").str.to_date("%Y-%m-%d").alias("order_date_parsed"),
        pl.col("amount").cast(pl.Decimal(10, 2)),
        (pl.col("amount") * pl.col("tax_rate")).round(2).alias("tax_amount"),
    )
    .rename({"order_date_parsed": "order_date"})
    .unique(subset=["order_id"])
    .sort("order_date")
    .collect()
)

For aggregations, joins, window functions, streaming, Arrow interop, DuckDB bridge, and performance tuning, see Polars Patterns Reference.

Pandas (Legacy/Compatibility)

Use when integrating with ML libraries or maintaining existing codebases. Prefer method chaining and vectorized operations.

For method chaining, Arrow backend, memory optimization, chunked processing, and anti-patterns, see Pandas Patterns Reference.

PySpark (Distributed)

Use when data exceeds single-machine memory or running on Databricks/Spark infrastructure.

For DataFrame API, Pandas UDFs, Spark Connect, Delta Lake, partitioning, and caching, see PySpark Patterns Reference.

dbt Python Models

Use Python models for transforms difficult in SQL: complex statistics, ML scoring, API calls, complex string parsing, or external library integration. Use SQL for joins, filters, aggregations, window functions, CTEs, and standard ELT.

# models/intermediate/int_customer_rfm.py
def model(dbt, session):
    """RFM scoring — requires Pandas groupby + qcut."""
    dbt.config(materialized="table", packages=["scikit-learn==1.4.0"])
    orders = dbt.ref("stg_orders").to_pandas()
    # ... transform with Pandas/sklearn, return DataFrame
    return result  # dbt writes to warehouse automatically

On Snowflake, session is a Snowpark Session and dbt.ref() returns a Snowpark DataFrame (not Pandas).

API Extraction

Build typed clients with Pydantic models, pagination, retry (tenacity), and rate limiting. Use httpx for sync/async HTTP.

For typed client patterns, pagination (cursor/offset/link-header), rate limiting, async extraction, and complete pipeline examples, see Extraction Patterns Reference.

Data Validation

Pydantic for row-level validation (API responses, individual records)
Pandera for DataFrame-level validation (column types, constraints)
Great Expectations for suite-level validation (warehouse tables, CI gates)
dbt tests for model-level assertions in dbt projects

For Pydantic v2 patterns, Pandera schemas, Great Expectations checkpoints, and contract testing, see Data Validation Patterns Reference.

DataFrame Interoperability

Conversion	Method	Notes
Polars -> Pandas	`polars_df.to_pandas()`	Copies data
Pandas -> Polars	`pl.from_pandas(df)`	Zero-copy when possible
Polars <-> Arrow	`to_arrow()` / `pl.from_arrow()`	Zero-copy
Polars <-> DuckDB	`duckdb.sql("SELECT ... FROM df").pl()`	Zero-copy via Arrow
Spark -> Pandas	`spark_df.toPandas()`	Pulls to driver; use `limit()` for large data
Pandas -> Spark	`spark.createDataFrame(df)`	Enable Arrow: `spark.sql.execution.arrow.pyspark.enabled=true`

Security

Credentials: always os.environ["KEY"], never inline. Document required vars in .env.example. Use connection strings from env vars. Close httpx clients in finally blocks. Never store credentials in notebook cells.

See Security & Compliance Patterns for the full framework including security tiers.

Reference Files

Polars Patterns — LazyFrame, expressions, aggregations, joins, windows, streaming, Arrow interop, performance
Pandas Patterns — Arrow backend, method chaining, memory optimization, anti-patterns
PySpark Patterns — DataFrame API, Pandas UDFs, Spark Connect, Delta Lake, Databricks
Data Validation Patterns — Pydantic v2, Pandera, Great Expectations, contract testing
Extraction Patterns — httpx clients, async extraction, pagination, rate limiting, retry

python-data-engineering