Data Engineering & Databases

Python's unique strength is bridging software engineering and data work: the same language writes the API, the ETL pipeline, and the analysis. This page covers the data stack's view-vs-copy traps in NumPy and Pandas, the database layer with SQLAlchemy 2.0, and why this bridge keeps Python indispensable.

NumPy and Pandas: Views, Copies, and Gotchas

The data stack has its own expert-level traps, almost all rooted in one question: am I looking at a view or a copy? A NumPy slice is a view -- it shares memory with the original, so writing through it mutates the source:

import numpy as np

a = np.arange(10)
b = a[2:5]            # a VIEW, not a copy -- shares the same buffer
b[0] = 999            # mutates a[2] too!
a[2]                  # 999

c = a[2:5].copy()     # explicit copy when you need independence
# Fancy indexing (a[[1,3,5]]) and boolean masks DO copy. Plain slices don't.

This is also the performance story: vectorise instead of looping. A Python for loop over an array pays interpreter overhead per element; a vectorised expression runs the loop in C. Broadcasting applies an operation across shapes without materialising intermediates:

# Slow: Python-level loop
result = np.array([x * 2 + 1 for x in a])
# Fast: one C-level pass, no per-element Python
result = a * 2 + 1
# Broadcasting: (1000, 3) - (3,) subtracts the row from every row, no copy of it
centered = points - points.mean(axis=0)

Pandas' notorious SettingWithCopyWarning is the same view/copy ambiguity surfacing. Chained indexing (df[mask]["col"] = ...) may write to a temporary that is discarded, so the assignment silently does nothing. Always assign through a single .loc:

df[df.age > 18]["category"] = "adult"     # BUG: chained -- may hit a copy, no-ops
df.loc[df.age > 18, "category"] = "adult" # correct: one indexer, writes in place

For new work at scale, Polars sidesteps much of this: it is immutable by default (no view/copy footguns), multi-threaded, and its lazy scan_* API lets the query optimiser push filters and projections down before reading data -- often an order of magnitude faster than Pandas on large files.

Databases and SQLAlchemy 2.0

A senior backend engineer owns the data layer's correctness and performance. SQLAlchemy 2.0's unified select() API and typed ORM are the modern baseline:

from sqlalchemy.orm import Mapped, mapped_column, relationship, Session
from sqlalchemy import select

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(unique=True)
    orders: Mapped[list["Order"]] = relationship(back_populates="user")

with Session(engine) as session:
    stmt = select(User).where(User.email == email)
    user = session.scalars(stmt).one()

The single most common ORM performance bug is the N+1 query: you load N rows, then lazily access a relationship inside a loop, firing one query per row. The fix is eager loading -- one join or one batched IN query:

for user in session.scalars(select(User)):   # 1 query
    print(user.orders)                        # +1 query PER user -- N+1!

# Fix: tell the ORM to load orders up front
from sqlalchemy.orm import selectinload, joinedload
users = session.scalars(
    select(User).options(selectinload(User.orders))  # 2 queries total, regardless of N
).all()

Other essentials experts get right:

• Connection pooling. Opening a connection per request is slow and exhausts the database. The pool reuses connections; size it deliberately (pool_size, max_overflow) and recycle stale ones (pool_recycle) to survive a database that drops idle connections.
• Transactions and the unit of work. A Session accumulates changes and flushes them as one transaction on commit. Keep transactions short; never hold one open across a slow external call.
• Migrations belong in Alembic, version-controlled and reviewed -- never hand-edit production schema. Autogenerate, then read the diff; autogeneration misses some changes (column type tweaks, server defaults).

Python for Data Engineering

Python's unique strength is bridging the gap between software engineering and data work. The same language writes the API, the ETL pipeline, and the ML training script:

import polars as pl

# Polars for fast data processing (Rust-backed)
df = (
    pl.scan_csv("transactions.csv")
    .filter(pl.col("amount") > 100)
    .group_by("merchant_id")
    .agg([
        pl.col("amount").sum().alias("total"),
        pl.col("amount").count().alias("count"),
        pl.col("amount").mean().alias("average"),
    ])
    .sort("total", descending=True)
    .collect()
)

This bridge between backend engineering and data is why Python remains indispensable despite its performance limitations. In NZ, nearly every data team uses Python, and backend engineers who can also write data pipelines are highly valued.