From DBA or Data Engineer to AI Engineer: A Realistic Path

If you spend your days tuning queries, managing pipelines, or keeping a production database alive, you already carry most of what an AI engineering role needs. What is missing is not a new career from scratch, it is a specific set of additions on top of what you already do well. Here is the path that actually gets you there, without pretending you need to become a research scientist first.

1. Treat Python as your primary language, not a scripting add-on

Most DBAs and data engineers already write Python for ETL glue code, but AI engineering asks for more: comfort with async code, working with SDKs like OpenAI's or Anthropic's, and writing code that calls external APIs reliably, with retries and error handling. If your Python has mostly lived inside pandas scripts, push it into building small services and command line tools before touching anything AI-specific.

2. Learn how LLMs actually work, at the level you need to use them well

You do not need to understand transformer internals to be effective. You do need to understand tokens and context windows, the difference between a base model and an instruction-tuned one, prompt structure, temperature and sampling parameters, and function or tool calling. This is the layer that separates someone who can call an API from someone who can design a reliable system around one.

3. Build on the database skills you already have: vector search and RAG

This is where your background gives you a real head start. Retrieval Augmented Generation is fundamentally a data engineering problem with a language model bolted on the end: chunk documents, generate embeddings, store and query vectors, and feed the results into a prompt. If you already know PostgreSQL, pgvector gets you there without learning a new database engine. If you work in Redshift or SQL Server environments, understanding how vector search differs from traditional indexing will make you far more useful on an AI team than someone coming in without any database grounding.

4. Get hands-on with a managed AI platform on the cloud you already use

If your infrastructure background is AWS, go deep on Amazon Bedrock: model access, knowledge bases, and agents. If you live in Azure, do the same with Azure OpenAI Service. The point is not to learn every platform, it is to learn one well enough to provision it, secure it with proper IAM, and monitor its cost and usage the way you already monitor a database.

5. Ship one project that proves you can build, not just describe

Interviews for AI engineering roles increasingly ask for a working example over a certificate. A small RAG chatbot over your own documents, using a vector-enabled Postgres and a Bedrock or Azure OpenAI model, is enough to demonstrate the full pipeline: ingestion, embedding, retrieval, and generation. I laid out this exact project, along with four others that mix cloud data warehousing and AI, in an earlier post on starter projects for an AI and data engineering portfolio.

6. Learn to evaluate and monitor AI systems, not just build them

A model that answers well in a demo can fail quietly in production: hallucinated answers, drifting retrieval quality, or cost spikes from runaway token usage. Employers increasingly want people who can set up evaluation pipelines and observability around an AI system, not just wire the initial integration. This is a natural extension of the monitoring instincts a DBA or data engineer already has, applied to a new kind of workload.

What This Path Actually Buys You

You are not competing with machine learning researchers for these roles, and you do not need to be one. Most AI engineering work in production is systems work: reliable pipelines, sound data models, and careful integration of a model into something people use. That is the job you have been doing all along, with a new component added on top. Start with the RAG project, since it touches every skill above at once, and let the rest follow from there.

If you are setting up a machine specifically for this kind of work, I covered the full setup, from picking a model to getting PostgreSQL, Docker, and PyTorch running natively, in an earlier post on configuring a Mac for data engineering and AI work.

Match the Model to the Task, Not the App: An Orchestrator-Worker Pattern in Python

Most apps that call an LLM send every request to the same model. That works until the bill arrives. A classification job that returns one word costs the same as a multi-step reasoning job, because you picked one model at design time and never revisited it. The fix is an orchestrator-worker setup: a small central router reads the incoming task, decides which tier of model it needs, and delegates to a worker. The router sits at the hub, each model tier is a spoke, and no worker talks to another. This post builds that in Python using both the Anthropic and OpenAI SDKs, so you can mix providers in the same pipeline.

1. Define the tiers before writing any code

Three tiers cover almost everything. Fast handles extraction, classification, tagging, and format conversion. Balanced handles summarization, SQL generation, and short analysis. Deep handles multi-step reasoning, code review, and anything where a wrong answer is expensive. Map each tier to a model from each provider so you can fail over between them.

# tiers.py
TIERS = {
    "fast":     {"anthropic": "claude-haiku-4-5", "openai": "gpt-5.6-luna"},
    "balanced": {"anthropic": "claude-sonnet-5",  "openai": "gpt-5.6-terra"},
    "deep":     {"anthropic": "claude-opus-4-8",  "openai": "gpt-5.6-sol"},
}
  

2. The orchestrator itself belongs on the cheap tier

The orchestrator runs on every request, before any real work happens. Whatever it costs gets multiplied across your entire traffic, and its latency lands in front of every response. Putting a deep model there means paying premium prices to decide what to do. Routing is short classification against a closed label set, which is precisely what the small models handle well.

The risk is that router errors are asymmetric. Sending a fast task to the deep tier wastes money and you see it in the bill. Sending a deep task to the fast tier produces a plausible wrong answer that flows downstream unnoticed. Three controls keep that in check: a closed label set instead of free-form output, a hard cap on max_tokens so the router cannot drift into explanation, and the validator gate covered further down, which catches the cases where the cheap tier was not enough.

import anthropic

client = anthropic.Anthropic()

ROUTER_PROMPT = """Classify the task into exactly one tier.
fast: extraction, classification, tagging, formatting
balanced: summarization, query generation, short analysis
deep: multi-step reasoning, code review, planning
Answer with the tier name only."""

def route(task_text):
    msg = client.messages.create(
        model="claude-haiku-4-5",
        max_tokens=8,
        system=ROUTER_PROMPT,
        messages=[{"role": "user", "content": task_text}],
    )
    tier = msg.content[0].text.strip().lower()
    return tier if tier in TIERS else "balanced"
  

The fallback on the last line matters. Routers misfire, and an unrecognized label should land on a safe middle tier rather than raise. And when your app already knows the task type because the user clicked a specific button or the pipeline stage is fixed, skip the router entirely. A dictionary lookup costs nothing and never misclassifies.

3. Wrap each worker behind one function signature

The two SDKs differ in shape. Anthropic uses messages.create with a separate system argument and a required max_tokens. OpenAI uses responses.create with an input list and a reasoning effort setting. Normalize both to take a prompt and return a string, and the orchestrator stops caring which vendor answered. The Anthropic Python SDK and the OpenAI model catalog document the current identifiers for each family.

import anthropic
from openai import OpenAI

anthropic_client = anthropic.Anthropic()
openai_client = OpenAI()

def call_anthropic(model, system, prompt, max_tokens=1024):
    msg = anthropic_client.messages.create(
        model=model,
        max_tokens=max_tokens,
        system=system,
        messages=[{"role": "user", "content": prompt}],
    )
    return msg.content[0].text

def call_openai(model, system, prompt, effort="low"):
    resp = openai_client.responses.create(
        model=model,
        instructions=system,
        input=prompt,
        reasoning={"effort": effort},
    )
    return resp.output_text
  

4. Dispatch, and allow one escalation

The orchestrator is now small. Pick the tier, pick the provider, run the call. Add a single escalation path: if the fast tier returns something your validator rejects, retry once on the next tier up. That ladder is a model cascade, and it should stay short. One retry, not a loop. Unbounded escalation turns a cost optimization into a cost multiplier.

LADDER = ["fast", "balanced", "deep"]

def run_task(prompt, system, validator, provider="anthropic"):
    tier = route(prompt)
    idx = LADDER.index(tier)

    for attempt_tier in LADDER[idx:idx + 2]:
        model = TIERS[attempt_tier][provider]
        if provider == "anthropic":
            out = call_anthropic(model, system, prompt)
        else:
            effort = "low" if attempt_tier == "fast" else "high"
            out = call_openai(model, system, prompt, effort)

        if validator(out):
            return {"tier": attempt_tier, "model": model, "output": out}

    raise ValueError("no tier produced a valid result")
  

The validator is the piece people skip, and it is what makes the whole design safe. For extraction, check that the JSON parses and the required keys exist. For SQL generation, run EXPLAIN against the target database. Without a validator you have no signal that the cheap tier was good enough, and the routing decision becomes a guess.

5. Log the tier, not just the answer

Store the tier, model, token counts, and validator result for every call. After a week you will see which task types actually escalate and which ones you over-provisioned from the start. That log is the only honest way to tune the routing prompt, and it usually reveals that a large share of traffic never needed anything above the fast tier.

6. A real pipeline: invoice ingestion

Consider a service that receives scanned invoices and has to produce clean rows in a database. Four distinct jobs happen per document, and only one of them is hard. Classifying the document type is a label from a short list. Field extraction is bounded and schema-driven. Totals reconciliation is arithmetic plus judgment about rounding and tax lines. Resolving an ambiguous line item against a product catalog is where reasoning actually pays for itself. Hardcode the first three, and let the orchestrator decide only on the last one.

# pipeline.py

STAGE_TIERS = {
    "classify_doc":   "fast",
    "extract_fields": "fast",
    "reconcile":      "balanced",
}

def run_stage(stage, system, prompt, validator, provider="anthropic"):
    # known stage, no router call needed
    tier = STAGE_TIERS[stage]
    model = TIERS[tier][provider]
    ...  # same call plus escalation logic as run_task

def process_invoice(ocr_text):
    doc_type = run_stage(
        "classify_doc",
        system="Answer with one of: invoice, receipt, credit_note, other.",
        prompt=ocr_text[:2000],
        validator=is_known_doc_type,
    )
    if doc_type != "invoice":
        return route_to_manual_queue(ocr_text, doc_type)

    fields = run_stage(
        "extract_fields",
        system=EXTRACTION_SCHEMA_PROMPT,
        prompt=ocr_text,
        validator=parses_as_invoice_json,
    )

    if not totals_match(fields):
        fields = run_stage(
            "reconcile",
            system=RECONCILE_PROMPT,
            prompt=describe_mismatch(fields),
            validator=totals_match,
        )

    # only unresolved items reach the orchestrator and the expensive tiers
    for item in unmatched_items(fields):
        item["sku"] = run_task(
            prompt=catalog_match_prompt(item),
            system=CATALOG_PROMPT,
            validator=sku_exists,
        )["output"]

    return persist(fields)
  

Notice what the structure buys you. Every invoice pays for two fast calls. The balanced tier only runs when the arithmetic disagrees, and the deep tier only touches line items the catalog lookup could not resolve on its own. On a clean batch, most documents never leave the cheap tier, and the expensive models are reserved for the handful of cases that genuinely earn them.

Wrapping Up

Orchestrator-worker does not have to mean an elaborate framework. A tier table, a cheap orchestrator, one wrapper per SDK, and a validator gate get you most of the benefit in under a hundred lines. Both providers expose a low-cost tier that handles far more than people expect once the prompt is narrow enough.

One practical note before you ship: pin the versioned model identifiers in production rather than the family aliases. Aliases move when a vendor releases a new generation, and a routing table that silently changes tiers under you is a debugging session nobody enjoys.

Moving On-Prem PostgreSQL to the Cloud: Picking the Right Path for Big Tables

Every PostgreSQL migration eventually hits the same fork in the road. The database is small enough to dump and restore in a maintenance window, or it isn't. Once you cross into terabyte territory, or the business can't afford more than a few minutes of downtime, the simple approach stops being an option and you have to think harder. Here are the three paths that actually work, and when each one makes sense.

1. Dump and restore, the reliable baseline

For anything under a couple hundred GB, pg_dump piped into pg_restore is still the cleanest way to move a database. It carries the full schema, data, indexes, and sequences, and on decent hardware a mid-size database finishes in well under an hour. The one flag people forget is parallelism.

# dump in the directory format so restore can run in parallel
pg_dump -Fd -j 8 -f /dump/appdb sourcedb

# restore with the same worker count on the target
pg_restore -Fd -j 8 -d appdb /dump/appdb
  

The catch shows up with one dominant table. Parallelism here is per-table, so a single 2 TB table becomes one long serial stream while the other seven workers sit idle. That is the moment dump and restore starts feeling slow, and the reason the next two options exist.

2. Native logical replication for a live cutover

When downtime is the real constraint, PostgreSQL's built-in logical replication lets the target keep pace with the source while the application stays online. You publish on the old server, subscribe on the new one, and Postgres copies the initial snapshot and then streams every change until you decide to switch over.

-- on the source (wal_level must be 'logical')
CREATE PUBLICATION migration_pub FOR ALL TABLES;

-- on the target
CREATE SUBSCRIPTION migration_sub
  CONNECTION 'host=old-db port=5432 dbname=appdb user=repl password=...'
  PUBLICATION migration_pub;
  

Two things save you real pain on big datasets. First, tables without a primary key need a REPLICA IDENTITY FULL or a unique index, otherwise updates and deletes won't replicate. Second, that same large table problem returns. Logical replication syncs each table with its own worker, so your biggest table still copies as a single stream. A common workaround is to pre-load the huge table with pg_restore, then attach it to the subscription with copy_data = false so replication only handles the incremental changes.

3. Managed DMS when you want the plumbing handled

Both AWS and Google Cloud offer a managed migration service that wraps logical replication for PostgreSQL, including from on-premises sources. Google's Database Migration Service supports CDC via pglogical and can open multiple parallel subscriptions across tables during the initial load and the ongoing CDC phase. AWS DMS does the same using logical replication slots with the test_decoding or pglogical plugin. Both handle slot management and give you a controlled cutover step.

The shared limitation is worth knowing before you commit: for tables without a primary key, both services replicate the initial snapshot and INSERT operations only. UPDATE and DELETE changes during CDC are dropped and need to be handled separately. Beyond that, parallelism is still table-level on both platforms, so the single dominant large table remains the bottleneck either way.

How to actually choose

Small database and a maintenance window you can spare? Dump and restore, done. Need the source online with minimal downtime and comfortable managing slots yourself? Native logical replication. Want the same low-downtime result without babysitting the replication setup? Reach for the managed DMS on your target cloud. In all three cases the single dominant table is what decides your timeline, so measure it first and plan to pre-seed it on its own rather than hoping a migration tool will parallelize what the engine simply won't.

Setting Up a Mac for Data Engineering and AI Work

If you work with data pipelines, SQL, notebooks, or machine learning models, a Mac with Apple Silicon is genuinely one of the best machines you can have as a daily driver. The unified memory architecture means your CPU and GPU share the same memory pool, which matters a lot when you are running Docker containers, a local Postgres instance, Jupyter, and an AWS CLI session all at once without the machine breaking a sweat. This post covers which model to pick and how to get the whole stack running from scratch. Some of the product links below are affiliate links, meaning I may earn a small commission if you purchase through them, at no extra cost to you.

1. Picking the right model

The current MacBook line runs entirely on M5 chips. For most data engineers doing cloud-first work — Redshift, Glue, dbt, Airflow — the MacBook Air M5 with 16GB is more than enough. If you run heavier local workloads like training models, spinning up multiple containers, or working with large DataFrames in memory, the MacBook Pro 14" M5 Pro with 24GB is the right step up. The Pro has active cooling, which matters when you push it hard for extended periods. The 36GB M5 Max is only worth it if local deep learning training is a regular part of your day, otherwise you are paying for headroom you will rarely use.

2. First thing: Homebrew and Xcode tools

Everything else in this setup depends on Homebrew, the package manager for macOS. Before installing it, you need Apple's command line developer tools, which also gives you Git.

# Install Xcode command line tools
xcode-select --install

# Install Homebrew
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Add Homebrew to your PATH (Apple Silicon path)
echo 'eval "$(/opt/homebrew/bin/brew shellenv)"' >> ~/.zprofile
eval "$(/opt/homebrew/bin/brew shellenv)"

3. Python environment management with uv

Forget installing Python directly or relying on conda for everything. The current best practice on Apple Silicon is uv, a fast Python package and project manager that handles Python versions and virtual environments without polluting your global install. It is significantly faster than pip and plays well with Jupyter.

# Install uv
brew install uv

# Create a new project environment
mkdir my-de-project && cd my-de-project
uv init

# Add packages (same idea as pip install)
uv add pandas sqlalchemy boto3 jupyterlab scikit-learn

# Launch Jupyter inside the project environment
uv run --with jupyter jupyter lab

4. Core tools via Homebrew

These cover the daily stack for data engineering: database clients, cloud CLIs, container runtime, and a better terminal.

# AWS CLI and tools
brew install awscli

# PostgreSQL client (psql without the full server)
brew install libpq
brew link --force libpq

# Docker via OrbStack (lighter than Docker Desktop on Apple Silicon)
brew install orbstack

# Git, jq, and wget
brew install git jq wget

# VS Code
brew install --cask visual-studio-code

5. PyTorch with Metal GPU acceleration

Apple Silicon GPUs run PyTorch through the Metal Performance Shaders (MPS) backend, which gives you real GPU acceleration for model training without needing CUDA. It works natively on macOS 12.3 or later and PyTorch picks it up automatically.

# Install PyTorch (Apple Silicon native build)
uv add torch torchvision torchaudio

# Verify MPS is available in Python
import torch
print(torch.backends.mps.is_available())  # should print True

# Move a model to the MPS device
device = "mps" if torch.backends.mps.is_available() else "cpu"
model = model.to(device)

One thing worth knowing: MPS does not support every PyTorch operation yet. If you hit an unsupported op, set the environment variable PYTORCH_ENABLE_MPS_FALLBACK=1 and PyTorch will silently fall back to CPU for that specific operation while keeping everything else on the GPU.

6. One limitation to consider

Macs do not support CUDA, and that is a real constraint if your production training runs on NVIDIA GPUs or if your team uses CUDA-specific libraries. The practical answer most engineers land on is using the Mac for development, prototyping, and running notebooks, then pushing actual training jobs to AWS SageMaker, Google Colab, or a cloud GPU instance. The unified memory architecture makes the Mac excellent for loading large quantized models locally — a 7B or 13B parameter model fits comfortably in 24GB unified memory — but for serious fine-tuning or multi-GPU training, cloud is still the right call.

A solid machine that gets out of your way

The real reason data engineers gravitate toward Macs is not any single spec — it is the combination of a Unix shell that works the way you expect, excellent battery life, and hardware that handles a full data stack locally without fan noise or thermal throttling on everyday tasks. Getting to a productive environment takes less than an hour with Homebrew, uv, and OrbStack in place. After that, you have PostgreSQL, Docker, AWS CLI, Jupyter, and PyTorch with GPU acceleration all running natively on Apple Silicon, which is a genuinely capable local setup for most data and AI workflows.

PostgreSQL 18 Finally Makes BUFFERS the Default. Here Is Why That Matters

You run EXPLAIN ANALYZE on a slow query, stare at the plan, and something still feels off. The estimated rows look reasonable, the node timings add up, but you cannot tell whether the database is reading everything from memory or hammering the disk. That missing piece has always been BUFFERS, and for years you had to remember to type it manually. PostgreSQL 18 finally turns it on by default.

1. What BUFFERS actually tells you

The BUFFERS option reports how many 8 kB blocks were satisfied from PostgreSQL's shared buffer cache (hit) versus read from disk (read). It also tracks blocks that were written back to disk during the query (written) and any temporary blocks used by sort or hash operations. Per the official EXPLAIN documentation, the counts are cumulative: a parent node includes the buffer usage of all its children, so the top-level line gives you the whole-query picture at a glance.

2. The old way vs PostgreSQL 18

Before version 18, BUFFERS was off by default and required the parenthesized syntax to combine it with ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM   orders
WHERE  customer_id = 42;

Starting with PostgreSQL 18, EXPLAIN ANALYZE alone is enough, buffer information is included automatically. The PostgreSQL 18 EXPLAIN reference confirms this change: "Buffers information is automatically included when ANALYZE is used."

3. Reading the output

Here is a trimmed example of what you will see under a scan node:

Bitmap Heap Scan on orders
  (actual time=0.012..1.430 rows=980 loops=1)
  Buffers: shared hit=12 read=210

A high read count relative to hit means the data is not in shared buffers and the query is going to disk. The ratio between the two is the fastest signal you have for deciding whether to raise shared_buffers, add caching, or reconsider an index.

4. Spotting temp block usage

Temp blocks appear when a sort or hash operation cannot fit in work_mem and spills to disk. They show up as a separate line in the plan:

Sort  (actual time=45.2..48.9 rows=50000 loops=1)
  Sort Method: external merge  Disk: 7104kB
  Buffers: shared hit=2240 temp read=889 written=889

Any temp read or temp written value above zero is a sign that the operation spilled to disk. Increasing work_mem for that session (or globally if it happens often) is usually the fix. The Using EXPLAIN guide in the PostgreSQL docs walks through how to interpret these lines in context.

Wrapping Up

The BUFFERS option turns EXPLAIN ANALYZE from a timing report into a genuine I/O diagnostic. With PostgreSQL 18 enabling it automatically, there is one fewer thing to remember when chasing down a slow query. If you are still on an older version, make EXPLAIN (ANALYZE, BUFFERS) your default habit. The hit vs read ratio and any temp blocks in the output will often tell you more than the node timings alone.

If reading raw plan text is not your thing, I built PlanTrace exactly for this: a free visualizer that turns EXPLAIN output from PostgreSQL and Redshift into an interactive node graph. Buffer counts, node timings, and scan types all rendered in one color-coded view. Paste your plan and see everything at once, no indentation squinting required.

5 SQL Tricks Worth Remembering Before You Close the Laptop

Disclosure: this post may contain links to books as an affiliate link. If you purchase through it, this site may earn a small commission at no extra cost to you.

It is Friday. The sprint is done, the deploys are out, and nobody is scheduling a meeting in the next 30 minutes. This one is short — five SQL tricks that are genuinely useful, easy to forget, and satisfying to rediscover. No deep dives, no architecture decisions. Just good SQL to carry into next week.

1. Fill missing dates in a report with generate_series (PostgreSQL)

Your daily sales query skips dates with zero transactions. Instead of patching this in application code, generate a full date spine in the database and left join your data against it. The generate_series function handles this cleanly — no temp tables, no loops.

SELECT
    d.day::date,
    COALESCE(SUM(s.amount), 0) AS total_sales
FROM
    generate_series(
        '2025-05-01'::timestamp,
        '2025-05-31'::timestamp,
        '1 day'::interval
    ) AS d(day)
LEFT JOIN sales s
    ON s.sale_date = d.day::date
GROUP BY d.day
ORDER BY d.day;
  

2. Get the last day of any month without a calendar lookup (SQL Server)

Month-end reporting always needs the last day of the month, and it changes every month, and February exists. EOMONTH handles leap years, 30-day months, and everything else automatically. The optional second argument lets you offset forward or backward.

-- Last day of the current month
SELECT EOMONTH(GETDATE())  -- 2025-05-31
 
-- Last day of the previous month
SELECT EOMONTH(GETDATE(), -1) -- 2025-04-30
 
-- First day of the current month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) -- 2025-05-01
  

3. Use a CTE to avoid repeating yourself (PostgreSQL & SQL Server)

If you find yourself copy-pasting the same subquery twice in one statement, a Common Table Expression cleans it up immediately. CTEs are not just for readability — they also prevent you from calculating the same thing twice and getting different results if the underlying data changes mid-query.

WITH monthly_totals AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount)                       AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS pct_of_total
FROM monthly_totals
ORDER BY month;
  

4. Spot duplicate rows instantly with GROUP BY and HAVING

Before running a deduplication job, it pays to know exactly which keys are duplicated and how many copies exist. This pattern works identically across PostgreSQL, SQL Server, MySQL, and Redshift — no library needed, just standard SQL.

SELECT
    customer_id,
    email,
    COUNT(*) AS occurrences
FROM customers
GROUP BY
    customer_id,
    email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;
  

Add more columns to the GROUP BY to tighten the definition of "duplicate" for your specific case. The result set tells you exactly what to target before touching any data.

5. LAG() to compare a row against the previous one (PostgreSQL & SQL Server)

Week-over-week, day-over-day, month-over-month — any time you need to compare a metric against the prior row, LAG() saves you a self-join. It is a window function available in PostgreSQL, SQL Server, MySQL 8+, and Redshift.

SELECT
    sale_date,
    daily_revenue,
    LAG(daily_revenue) OVER (ORDER BY sale_date)
        AS prev_day_revenue,
    ROUND(
        (daily_revenue - LAG(daily_revenue) OVER (ORDER BY sale_date))
        * 100.0
        / NULLIF(LAG(daily_revenue) OVER (ORDER BY sale_date), 0),
    2) AS pct_change
FROM daily_sales
ORDER BY sale_date;
  

The NULLIF(..., 0) wrapper on the denominator prevents a division-by-zero error on days where the previous revenue was zero — a small detail that saves a lot of Friday-afternoon debugging.

Wrapping Up

None of these are exotic — generate_series, EOMONTH, CTEs, HAVING, and LAG() are bread-and-butter SQL. They just tend to get forgotten under deadline pressure and rediscovered on a quiet Friday. Keep them close. The one worth memorizing cold: NULLIF on every division denominator. Division by zero is always the error you find in production.

If this kind of thing clicks for you and you want more of it — the patterns, the thinking behind the queries, the instinct for when to reach for a window function versus a subquery — Practical SQL, 2nd Edition by Anthony DeBarros is one of the best books on the subject. It is written for people who want to actually understand their data, not just run queries.