DEV Community: Vinicius Fagundes

Your warehouse isn't expensive. Your full table scans are.

Vinicius Fagundes — Tue, 30 Jun 2026 03:00:00 +0000

Every few months someone shows me a cloud bill that tripled and asks whether they should switch warehouses — BigQuery to Redshift, Redshift to BigQuery, the grass always cheaper on the other side. They almost never need to switch. They need to stop scanning everything. And to see why, you have to understand that the two warehouses charge you for completely different mistakes.

(Prices below are US list rates verified mid-2026. They drift — confirm current numbers on the vendor pages before you quote them in a meeting.)

Two warehouses, two completely different bills

BigQuery charges you for bytes scanned. It's serverless — no cluster to manage — and on-demand pricing is about $6.25 per terabyte your query reads (the first 1 TiB each month is free). You don't pay for a machine. You pay for how much data each query touches. That sounds harmless right up until someone does this:

-- BigQuery on-demand: this reads EVERY byte of a 10 TB table.
-- At ~$6.25/TB, that's ~$62.50. For one query.
SELECT * FROM events;

Sixty-odd dollars, one query. Now schedule a dashboard to run it hourly and you've built a machine that sets fire to four figures a day. The warehouse didn't do that. The SELECT * did.

Redshift charges you for compute you rent. With provisioned clusters you pay per node, per hour — whether or not a single query runs. The classic Redshift bill isn't a runaway query; it's a cluster humming along at 3% utilization all weekend, billing full price to do nothing:

A provisioned cluster bills 24/7 while it's up.
Idle Saturday 2 a.m., zero queries running? Still on the meter.

Redshift Serverless softens this — it bills per "RPU-hour" only while queries actually run, and stops when idle — but the trap just relocates: now an unbounded, badly-written query scales the compute up and you pay for the burst. Different shape, same root cause.

So: two warehouses, two opposite-looking bills — and one shared villain underneath. Both are downstream of a single question: are you reading only what you need, or dragging the whole table every time?

Fix #1: stop selecting columns you don't use

Because BigQuery is columnar — it stores each column separately — selecting fewer columns reads less data and costs less, directly and proportionally.

-- Scans all 200 columns. You used three of them.
SELECT * FROM events WHERE event_date = '2026-06-01';

-- Scans three columns. Same rows, a fraction of the bytes, a fraction of the cost.
SELECT user_id, event_type, amount
FROM events
WHERE event_date = '2026-06-01';

On a wide table this one change can cut a query's cost by 80–90%. SELECT * isn't a convenience — on a columnar warehouse it's a bill multiplier.

And before you run anything expensive, BigQuery will tell you the cost for free. Use the dry run:

# Estimates bytes scanned WITHOUT running (or charging for) the query.
bq query --dry_run --use_legacy_sql=false 'SELECT * FROM dataset.events'
# -> "Query will process 10.4 TB when run."  (i.e., ~$65. Maybe don't.)

There is no excuse for a surprise five-figure query when a one-line dry run shows you the bill in advance.

Fix #2: partition so you skip data you don't need

A partition splits a table into chunks (usually by date) so a query that filters on that column can skip whole chunks without reading them.

-- BigQuery: partition by day at create time
CREATE TABLE events_partitioned
PARTITION BY DATE(event_timestamp) AS
SELECT * FROM events;

Now the math changes completely. A query filtering to one day of a three-year table:

-- Without partitioning: scans 3 years of data to find one day.
-- With partitioning:    reads ONLY that day's partition.
SELECT user_id, amount
FROM events_partitioned
WHERE DATE(event_timestamp) = '2026-06-01';

On a 10 TB / 3-year table, that single-day query goes from scanning ~10 TB (~$62) to scanning a few GB (a few cents). Same answer. Same SQL shape. Three orders of magnitude cheaper, because you stopped reading data you were only going to throw away.

Clustering (sorting the data within partitions by a column you filter on a lot, like user_id) prunes even further within each partition. Partition by the thing you filter by date; cluster by the thing you filter by identity.

Fix #3: on Redshift, design how data is laid out

Redshift's levers are different because its cost is about how efficiently the cluster does work — which comes down to how the data is physically arranged across nodes.

CREATE TABLE events (
    user_id     BIGINT,
    event_date  DATE,
    amount      DECIMAL(10,2)
)
DISTKEY(user_id)     -- spreads rows across nodes by user_id, so joins on
                     -- user_id happen locally instead of shuffling data
                     -- across the network (network shuffle = slow = $$)
SORTKEY(event_date); -- keeps rows ordered by date, so a date filter can
                     -- skip whole blocks instead of scanning the table

A SORTKEY on event_date is Redshift's version of partition pruning: filter on a sorted column and the engine skips blocks it knows can't match. A bad DISTKEY is the silent Redshift killer — pick the wrong column and every join shuffles gigabytes across the network between nodes, and your "slow warehouse" is really a data-layout problem wearing a warehouse costume.

And if your raw data lives in S3 and you query it through Redshift Spectrum, the same bytes-scanned logic from BigQuery returns: Spectrum bills per TB scanned, so storing that data as Parquet (columnar, compressed) instead of CSV cuts the scan — and the bill — by a large factor, because Spectrum only reads the columns you ask for.

The decision: read less, before you migrate

Here's the part that saves people the migration. Both warehouses, stripped down, reward the exact same discipline:

→ Read only the columns you need. SELECT * is the most expensive habit in analytics, on either platform.
→ Read only the rows you need. Partition (BigQuery) or sort-key (Redshift) on what you filter by, so the engine skips the rest.
→ Estimate before you run. Dry-run on BigQuery; check the query plan on Redshift. Know the cost before you pay it.
→ Match the pricing model to the workload. Bursty and unpredictable → serverless/on-demand. Steady and heavy → provisioned/committed capacity. Paying on-demand prices for a 24/7 workload, or renting an idle cluster for a once-a-day job, is its own quiet waste.

The bill tracks how much data you move, not which logo is on the console. The gap between a well-architected warehouse and a careless one is routinely 10–20x on the same data and the same questions — and that gap is almost entirely query patterns and table design, not the vendor.

So before you migrate, go pull your single most expensive query. How much data does it actually touch — and how much of that do you actually use? The honest answer to that second question is usually where your "expensive warehouse" turns out to live.

I'm Vinicius Fagundes — principal data engineer, independent, and an MBA lecturer in São Paulo. Cutting cloud-warehouse bills without changing what teams can do with their data is a big part of what I do. That work lives at vf-insights.com.

Your p-value answered a question you didn't ask.

Vinicius Fagundes — Mon, 29 Jun 2026 03:00:00 +0000

You ran the A/B test. It came back p = 0.08. "Not significant." So you killed the feature and moved on. You may have just buried something that works — not because the math lied, but because it answered a different question than the one in your head.

This is the most common statistics mistake I see in data teams, and it's not about being bad at math. It's about a quiet mismatch between the question you're asking and the question the test is answering. Let's fix that, with code, and keep the examples generic — we'll use a plain conversion test, the kind every team runs.

What a p-value actually is (read this twice)

Here's the precise definition, and then the plain-English one.

Precise: a p-value is the probability of observing data at least as extreme as yours, assuming there is no real effect at all.

Plain: it answers "if nothing were going on, how surprised should I be by results this big?" A small p-value means "pretty surprised — random noise doesn't usually produce a gap this large."

Now read what it does not say. It does not tell you the probability that your effect is real. It does not tell you the probability you're wrong. Those are the questions you actually care about — and the p-value answers neither. It answers a question about a hypothetical world where the effect is zero. That's the mismatch, right there.

Let's run one.

import numpy as np
from scipy import stats

# Variant A: 120 conversions out of 2400.  Variant B: 145 out of 2400.
a_conv, a_n = 120, 2400
b_conv, b_n = 145, 2400

p_a, p_b = a_conv / a_n, b_conv / b_n          # 5.0% vs ~6.04%
p_pool   = (a_conv + b_conv) / (a_n + b_n)

# two-proportion z-test, computed by hand so nothing is hidden
se = np.sqrt(p_pool * (1 - p_pool) * (1/a_n + 1/b_n))
z  = (p_b - p_a) / se
p_value = 2 * (1 - stats.norm.cdf(abs(z)))

print(f"A: {p_a:.3%}   B: {p_b:.3%}")
print(f"z = {z:.2f},  p = {p_value:.3f}")

You'll get something like p ≈ 0.15. By the sacred 0.05 threshold, "not significant." Most teams stop here and conclude B doesn't work.

That conclusion is wrong, or at least unsupported. Watch.

"Not significant" means "not enough evidence," not "no effect"

"Not significant" does not mean "B is the same as A." It means "we don't have enough evidence to rule out luck as one possible explanation." Those are wildly different statements, and conflating them is how good ideas get killed.

Here's the proof that it's about sample size, not reality. Keep the conversion rates identical — 5.0% vs 6.04%, the exact same real-world effect — and just collect more data:

for scale in [1, 4, 10]:
    a_n2, b_n2 = a_n * scale, b_n * scale
    a_c2, b_c2 = round(p_a * a_n2), round(p_b * b_n2)
    p_pool = (a_c2 + b_c2) / (a_n2 + b_n2)
    se = np.sqrt(p_pool * (1 - p_pool) * (1/a_n2 + 1/b_n2))
    z  = (p_b - p_a) / se
    p  = 2 * (1 - stats.norm.cdf(abs(z)))
    print(f"sample x{scale:<2}  n={a_n2:>6}  p={p:.4f}")

Same effect every time. But the p-value marches from "not significant" to "highly significant" purely because the sample grew:

sample x1   n=  2400  p=0.15
sample x4   n=  9600  p=0.01
sample x10  n= 24000  p=0.0003

The effect never changed. Only the evidence did. So "not significant" was never a verdict about whether B works — it was a verdict about whether you'd collected enough data to tell. And "not enough data" is a collection problem, a sampling problem, a pipeline problem, long before it's a statistics problem. If your experiment is underpowered, no test will save you — the answer was decided when you chose the sample size.

What the Bayesian crowd does instead

This is where the other camp has a genuine point. Instead of asking "how surprised would I be if the effect were zero," the Bayesian approach asks the question you actually wanted answered all along: "given this data, how probable is it that B beats A, and by how much?"

The cleanest version, for conversion rates, uses a Beta distribution — and it's only a few lines because conversions are just successes-out-of-trials:

rng = np.random.default_rng(0)

# Beta(1,1) is a flat "I know nothing yet" prior.
# Update it with what we observed: conversions and non-conversions.
post_a = rng.beta(1 + a_conv, 1 + a_n - a_conv, 200_000)
post_b = rng.beta(1 + b_conv, 1 + b_n - b_conv, 200_000)

prob_b_better = (post_b > post_a).mean()
lift          = (post_b - post_a) / post_a

print(f"P(B beats A)          = {prob_b_better:.1%}")
print(f"median relative lift  = {np.median(lift):.1%}")
print(f"95% credible interval = "
      f"[{np.percentile(lift, 2.5):.1%}, {np.percentile(lift, 97.5):.1%}]")

This might tell you P(B beats A) ≈ 92%. Read that against where we started. The frequentist test said "not significant, p = 0.15" and you killed the feature. The Bayesian view says "there's about a 92% chance B is better" — which is a completely different business decision, from the same data.

Notice the other gift: a credible interval. A Bayesian 95% credible interval means what everyone wrongly thinks a confidence interval means — "there's a 95% probability the true lift is in this range." It directly describes your uncertainty about the effect size, which is usually the actual thing you're trying to decide on.

You don't have to pick a religion

I'm not here to recruit you to a camp. Frequentist methods are fast, standard, and fine — when you respect what they're telling you. The failure isn't using a p-value. The failure is reading "not significant" as "no effect" and treating a sample-size problem as a verdict from nature.

So, practically:

→ Power your test before you run it. Decide the smallest effect worth caring about and compute the sample size that can actually detect it. Half of all "inconclusive" tests were doomed at the planning stage — underpowered before a single user saw the variant.

→ Report the effect size and its uncertainty, not just a yes/no. "B is up ~1 point, 95% interval roughly [−0.3, +2.3]" tells a decision-maker far more than "not significant," which tells them almost nothing.

→ When the cost of waiting is high, ask the Bayesian question directly. "How likely is B to win, and by how much" is often the decision you're actually making — so compute that, instead of a threshold on a hypothetical null world.

→ Remember the test only sees the data your pipeline fed it. Garbage sampling, a logging bug, a biased assignment — the math will faithfully analyze bad data and hand you a confident wrong answer. The statistics are downstream of the data engineering, always.

The principle: know which question your number is answering before you let it make your decision. A p-value is a fine tool pointed at one specific question — just rarely the one you were actually asking.

Last time a test came back "not significant" — did you conclude the idea was dead, or that you just didn't have enough data yet? Those lead to opposite roadmaps, and most teams pick the first one without noticing they had a choice.

This touches statistical methods that get misused in high-stakes settings, so treat the code here as a teaching starting point, not a turnkey decision system for anything that actually matters — validate it against your own data and context.

I'm Vinicius Fagundes — principal data engineer, independent, and an MBA lecturer in São Paulo. I build the pipelines and experiment plumbing that feed analyses like these. That work lives at vf-insights.com.

Strong vs eventual consistency: you can't have it always-on and always-right. Pick one.

Vinicius Fagundes — Thu, 25 Jun 2026 03:00:00 +0000

You can't have it always-on and always-right. Pick one.

Every distributed system makes you choose, and most teams choose by accident — then act genuinely surprised when the tradeoff shows up at 2 a.m. as a customer screenshot of a wrong number. This post is the choice, stripped of the jargon that usually buries it, with real examples you can run.

The moment the choice gets forced

Here's the setup. Your data lives on more than one machine — a primary and a replica, two nodes, a cluster, whatever. You did that on purpose, for durability and scale. Most of the time those copies agree and nobody thinks about it.

Then the network between them hiccups. Not "goes down forever" — just a few seconds where node A can't reach node B. This is called a network partition, and here's the uncomfortable truth: it is not a question of if, it's when. Networks are not your friend. Packets drop, links saturate, a switch reboots.

In that moment — copies can't talk to each other, and a read request comes in — your system has exactly two options, and it cannot have both:

Refuse to answer until it's sure it has the latest, agreed-upon truth. (It stays correct, but it's briefly unavailable.)
Answer immediately with whatever this node has, and admit the value might be a few seconds stale. (It stays available, but it's briefly inconsistent.)

That's it. That's the whole famous "CAP theorem" in one sentence: when the network partitions (P), you choose between Consistency (C) and Availability (A). You don't get to keep both during the partition. Anyone selling you "always-on AND always-right" is hiding the moment the network forces the choice.

The two answers, in plain language

Strong consistency means every read sees the latest write. The instant a write succeeds, every subsequent read anywhere returns the new value. To guarantee that, the system sometimes has to say "I can't answer right now" — because the only honest alternative during a partition would be to risk handing you a stale value.

Eventual consistency means the system always answers, immediately, from whatever node you reached — and the copies reconcile "eventually," usually in milliseconds to seconds. The price: for that brief window, a read might return yesterday's value. Given a little time and no new writes, all copies converge to the same answer. Hence "eventual."

Neither is "correct." They're bets about which kind of pain hurts less for this specific data.

Make it concrete: the same read, two ways

DynamoDB lets you pick per-read, which makes the tradeoff wonderfully literal:

import boto3
table = boto3.resource("dynamodb").Table("accounts")

# Eventually consistent read (the DEFAULT): fast, cheap, might be slightly stale
table.get_item(Key={"id": "user-123"})

# Strongly consistent read: guaranteed latest, costs more, can fail during a partition
table.get_item(Key={"id": "user-123"}, ConsistentRead=True)

One boolean. ConsistentRead=True says "I need the truth, and I'll accept that this read might be slower or unavailable to get it." The default says "answer me fast, stale-by-a-moment is fine."

On the write side, relational databases expose the same dial through replication. In Postgres:

-- synchronous_commit = on  : the primary waits for the replica to confirm
--                            the write before telling the client "done."
--                            Stronger consistency, higher latency.
-- synchronous_commit = off : the primary confirms immediately and ships
--                            to the replica in the background.
--                            Lower latency, a window where the replica is behind.
SET synchronous_commit = on;

Same fundamental choice, opposite end of the pipe: wait for agreement (consistent, slower), or confirm now and reconcile later (available, faster).

The "read your own write" trap

Here's the bug eventual consistency hands you, and almost everyone hits it once. A user updates their profile, the page reloads, and their change isn't there — because the reload hit a replica that hadn't caught up yet.

update_profile(user_id, {"name": "New Name"})   # writes to the primary
profile = read_profile(user_id)                  # reads from a lagging replica
# profile["name"] is still the OLD name. The user thinks the save failed.

The data wasn't lost. The replica just hadn't received it yet. But your user doesn't know that — they retype it, hit save again, and now you've got a support ticket about a "broken" feature that's working exactly as designed. The fix is to route reads-after-writes to the primary, or use a session-consistency guarantee — but you only know to do that if you understood you chose eventual consistency in the first place.

How to actually pick (per piece of data, not per company)

The mistake is picking one religion for your whole system. You choose per data type, by asking what a stale or refused answer costs:

→ Account balance, inventory count, anything money or correctness-critical → lean strong. A stale balance is a double-spend, an overdraft, a lawsuit. Here you'd rather the system say "one moment" than confidently lie. Refuse and retry beats fast and wrong.

→ Like counts, view counts, feed ordering, "last seen" timestamps → lean eventual. Nobody is harmed if the like counter is off by three for two seconds. Availability wins easily; just answer.

→ Quorum systems give you a middle dial. In systems like Cassandra you tune it per query: require how many nodes must agree on a read (R) and a write (W) out of N copies. If R + W > N, any read is guaranteed to see the latest write — you've bought strong consistency by making reads or writes wait for more nodes. Lower the numbers and you trade that guarantee for speed and availability. The knob is right there in the query.

-- Cassandra: consistency is a per-statement choice
CONSISTENCY QUORUM;   -- wait for a majority — stronger, slower
CONSISTENCY ONE;      -- first node to answer wins — faster, possibly stale

The principle: every "always-on and always-right" architecture is hiding the exact moment it will be forced to break one of those two promises. Your job is to find that moment, decide on purpose which promise you break and for which data — and find out before your users do, not after.

So here's the real question: the system you ship today — which did it actually choose, strong or eventual? And did anyone choose it on purpose, or did it just default its way into a decision you'll meet at 2 a.m.?

I'm Vinicius Fagundes — principal data engineer, independent, and an MBA lecturer in São Paulo. I help teams make these tradeoffs deliberately instead of accidentally. That work lives at vf-insights.com.

Brute force isn't grit. It's the bill for a plan you skipped.

Vinicius Fagundes — Wed, 24 Jun 2026 03:00:00 +0000

Brute force isn't grit. It's the bill for a plan you skipped.

We've turned grinding into a personality trait. The all-nighter, the heroic debugging marathon, the Slack message at 1 a.m. that says "still on it, almost there." It photographs like dedication. Most of the time, it's a planning failure wearing a cape — and someone is paying the bill for it, usually the company, often the engineer's weekend.

I want to make this concrete, because "plan more, communicate better" is the kind of advice that's true and useless at the same time. So here's a real shape of it.

Three days against a locked door

An engineer I worked with lost three days to a pipeline that kept dying mid-run. Day one, he threw a bigger instance at it. Day two, he added retries, then a queue in front of it to smooth the load. Day three, he was tuning JVM memory flags at 11 p.m., exhausted, and — this is the part that matters — completely convinced he was close. Every hour of effort made him more certain the next hour would crack it.

The data was malformed upstream. One bad export from a source system, three days earlier. A single thirty-second question in Monday's standup — "hey, did anything change with the export job over the weekend?" — would have ended it before lunch on day one.

He didn't have an effort problem. He had the opposite. A mountain of effort, aimed with total commitment at a door that was never going to open by force. Because the door wasn't stuck. It was locked. And no amount of pushing opens a locked door — you have to stop, notice it's locked, and go find the key.

That's the whole job, the part that never makes it onto a résumé.

Why we do it anyway

Brute force feels productive in a way that thinking doesn't. When you're grinding, you can see the effort — commits, log lines, instances spun up. It looks like work and it feels like virtue. Stopping to think looks like nothing. Walking to someone's desk to ask a question feels like admitting you couldn't figure it out yourself.

So we optimize for the thing that looks like work instead of the thing that is the work. And the engineering culture cheers it on — we tell war stories about the heroic 3 a.m. fix and stay quiet about the thirty-second question that would have made the fix unnecessary.

Here's the reframe I'd burn into every junior engineer: the question you're avoiding because you'd rather just solve it yourself is usually the fastest path to the answer. Not the weak path. The fast one.

What "planning" actually means here

When I say planning, I don't mean a Gantt chart. I mean three small, unglamorous habits that prevent almost all of the heroic grinding:

→ Measure before you move. Before you optimize anything, find out where the time or the failure actually is. On the pipeline above, ten minutes of looking at which records were failing — instead of three days assuming it was a compute problem — would have pointed straight at the bad export. Profile first. Force later, if at all.

# The cheapest planning tool there is: look before you push.
# Instead of "the job is slow, make the cluster bigger," ask "slow WHERE?"
import time
for name, fn in [("extract", extract), ("transform", transform), ("load", load)]:
    t = time.perf_counter()
    fn()
    print(f"{name:<12} {time.perf_counter() - t:6.2f}s")

Three lines. They turn "the pipeline is slow" — an opinion — into "the transform is 90% of the runtime" — a fact you can act on. Most heroic optimization is solving a problem nobody confirmed existed.

→ State the assumption out loud before you build on it. "I'm assuming the upstream export hasn't changed." The second you say that to another human, half the time they go "...actually, it did." Assumptions die fast in conversation and live forever in your head.

→ Define done before you start. "This is working when the job completes under an hour with zero malformed records." Without that line, you don't know if you're three days from done or three minutes — so you grind, because grinding is what you do when you can't see the finish.

Communication isn't the soft part

This is the thing the bootcamps skip. We file communication under "soft skills," as if it sits next to the real engineering. On a hard problem, it is the engineering.

The thirty-second standup question wasn't a nicety. It was the single highest-leverage technical move available, and it was free. The engineer who asks "what am I missing here?" on day one looks slightly less impressive in the moment than the one who emerges triumphant on day three — and is worth ten times as much, because they shipped the same outcome and kept three days.

I teach this to MBA students who are mostly not engineers, and it lands the same way for all of them: the senior move was never pushing harder. Seniority isn't measured in how much force you can apply. It's measured in how quickly you can tell whether force is even the right tool — and how willing you are to stop and ask when it isn't.

Nobody remembers the all-nighter you didn't need to pull. They remember the thing shipped on time, by someone who looked at the door, saw the lock, and went and found the key.

When's the last time you ground for days on something a single question would have solved in a minute? Be honest about it — that memory is the most useful teacher you've got.

I'm Vinicius Fagundes — principal data engineer, independent, and an MBA lecturer in São Paulo. Seventeen years of fixing pipelines taught me that the slowest ones are usually a thinking problem before they're a compute problem. That work lives at vf-insights.com.

Aws Glue or Airflow? You're probably paying for both to do one job

Vinicius Fagundes — Tue, 23 Jun 2026 03:00:00 +0000

Glue or Airflow? You're probably paying for both to do one job.

It's the wrong question, and the wrong question quietly doubles your bill. Every couple of months someone asks me whether they should move their pipeline from Airflow to Glue, or the reverse, and the answer is almost always "neither, because you've misunderstood what each one is for." So let's fix that first, because once you see it, the cost mistakes become obvious.

Two different jobs that get confused for one

Picture a restaurant kitchen. There's a head chef calling out the order of dishes — appetizers first, mains when table four is ready, dessert last. And there are the line cooks actually chopping, searing, and plating. The chef coordinates. The cooks do the work. They are not interchangeable, and you wouldn't ask "should I hire a chef or a line cook?" You need the right amount of each.

That's Airflow and Glue.

Airflow is the head chef. It's an orchestrator. It decides what runs, in what order, and when — and then it waits. It does not move your data. It triggers a task, watches whether it succeeded, and triggers the next one. An Airflow DAG ("directed acyclic graph" — just a fancy term for "a list of steps with dependencies") looks like this:

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime

def extract():  ...
def transform(): ...
def load():     ...

with DAG("daily_sales", start_date=datetime(2026, 1, 1),
         schedule="0 2 * * *", catchup=False) as dag:

    e = PythonOperator(task_id="extract",   python_callable=extract)
    t = PythonOperator(task_id="transform", python_callable=transform)
    l = PythonOperator(task_id="load",      python_callable=load)

    e >> t >> l     # this line is the whole point: order and dependency

Read that last line out loud: extract, then transform, then load. That >> is Airflow's entire reason to exist — managing order and dependencies and retries and schedules. Notice it doesn't say anything about how the data gets transformed. That's not its job.

Glue is the line cook. It's managed Spark — actual compute that lifts and reshapes data. A Glue job does the chopping:

import sys
from awsglue.context import GlueContext
from pyspark.context import SparkContext

glueContext = GlueContext(SparkContext.getOrCreate())

# read raw data, transform it with Spark, write it back — this MOVES data
df = glueContext.create_dynamic_frame.from_catalog(
        database="raw", table_name="sales").toDF()

clean = (df.filter(df.amount > 0)
           .dropDuplicates(["order_id"])
           .groupBy("region").sum("amount"))

clean.write.mode("overwrite").parquet("s3://warehouse/sales_by_region/")

This code reads gigabytes, filters, dedupes, aggregates, and writes Parquet. That's compute. That's the work.

And here's the thing that resolves the whole "versus": you run Glue jobs from inside Airflow. They're not competitors on the same shelf. The chef tells the cook when to start.

from airflow.providers.amazon.aws.operators.glue import GlueJobOperator

run_glue = GlueJobOperator(task_id="transform_sales", job_name="clean_sales_job")

So where does the bill quietly double?

Now that the roles are clear, the two classic ways teams overpay are easy to see.

Mistake 1: a full orchestrator to babysit three cron jobs. Someone stands up Airflow — which means a scheduler, a webserver, a metadata database, all running 24/7 — to coordinate three independent daily jobs that have no real dependencies between them. Airflow is superb when you have a tangled DAG: forty tasks, branching, backfills, retries, SLAs. It is wild overkill for "run these three scripts every morning." That's a cron line:

# three independent jobs, no dependencies — this is the whole orchestrator you need
0 2 * * *  python extract_sales.py
0 3 * * *  python extract_users.py
0 4 * * *  python build_report.py

If your "DAG" is a straight line with no branching and the failure handling is "email me," you're paying the operational cost of a tool built for a problem you don't have.

Mistake 2: a Spark cluster to transform five gigabytes. This is the more expensive one. Someone spins up Glue — a distributed Spark cluster, billed per DPU-hour — to process a few gigabytes that pandas on a single modest box would crush in under a minute. Spark earns its cost when data genuinely doesn't fit on one machine and needs to be processed in parallel across a cluster. Below that threshold, you're paying cluster prices and cluster cold-start latency to do laptop work.

# 5 GB that fits in memory? You don't need a cluster.
import pandas as pd

df = pd.read_parquet("s3://raw/sales/")
clean = (df[df.amount > 0]
           .drop_duplicates("order_id")
           .groupby("region")["amount"].sum())
clean.to_parquet("s3://warehouse/sales_by_region/")

Same result as the Glue job above, no cluster, no DPU-hours, no cold start.

The actual decision

Stop asking "Glue or Airflow." Ask two separate questions, because they're answering two separate needs:

→ Do I have real orchestration complexity? Branching, dependencies, backfills, retries across many tasks, schedules that interact. Yes → you want an orchestrator (Airflow, or a lighter one like Prefect, or your cloud's native scheduler). No, it's a few independent jobs → cron or a managed schedule is plenty.

→ How much data is actually moving in the transform? Tens of GB or more, or it genuinely needs parallelism → managed Spark like Glue earns its keep. A few GB that fits in memory → pandas, DuckDB, or plain SQL in your warehouse will be faster and cheaper, with none of the cluster overhead.

These are independent dials. A real pipeline might be: a managed schedule (no Airflow) triggering a small Python job (no Glue), because it's three steps and four gigabytes. Another might be: full Airflow orchestrating a dozen Glue jobs, because it's a forty-task DAG over terabytes. Both are correct. The expensive mistake is reaching for the heavyweight version of either dial when your workload only turned one of them up.

Seventeen years in, the single most common thing I see on a cloud bill isn't a slow pipeline. It's a stack architected for a scale the company hasn't reached yet — Spark clusters idling, orchestrators babysitting cron jobs, all of it provisioned for the data volume someone hopes to have in two years.

What's running in your stack right now that's sized for a problem you don't actually have yet? Go look at your least-utilized component first — that's usually where the answer is hiding.

I'm Vinicius Fagundes — principal data engineer, independent, and an MBA lecturer in São Paulo. Right-sizing over-built data stacks is a big part of what I do. If this sounds like yours, that work lives at vf-insights.com.

Your model isn't underfitting. Your features are lazy.

Vinicius Fagundes — Tue, 23 Jun 2026 00:21:32 +0000

Here's the scene I've watched play out on a dozen teams. Accuracy plateaus. Someone rips out the logistic regression, drops in XGBoost, and waits for the jump. It doesn't come — or it comes with two points you can't explain to anyone. So the week disappears into hyperparameter tuning, and you end up with a slower, heavier, less interpretable model that's barely better than where you started.

The model was almost never the bottleneck. The features were.

This post is the long, practical version of that argument. We'll define the two camps in plain language, run real code, look at when boosting genuinely wins, and then walk through the failure mode nobody warns you about — the one where the fancy model is "winning" because it's quietly cheating.

A note before we start: keep your examples generic. We'll predict a numeric target — think demand, a quantity, a score on a tabular dataset. The principles are the same everywhere, and you should validate them on your own data.

The two camps, in plain terms

Linear / logistic regression fits a straight-line relationship: each feature gets a weight (a coefficient), and the prediction is a weighted sum. Logistic regression is the same idea bent for classification — it outputs a probability.

from sklearn.linear_model import LogisticRegression

model = LogisticRegression(max_iter=1000)
model.fit(X_train, y_train)

# the whole model, readable in one line per feature:
for name, weight in zip(feature_names, model.coef_[0]):
    print(f"{name:<20} {weight:+.3f}")

That loop is the entire model. A positive weight means "more of this pushes the prediction up," and you can hand that table to a stakeholder and defend every number. The cost: it assumes the relationship is roughly linear and that features act independently. Real data often isn't that polite.

Gradient boosting (XGBoost, LightGBM, sklearn's GradientBoostingClassifier) builds hundreds of small decision trees, each one correcting the mistakes of the last. It captures nonlinearity and feature interactions for free, and on messy tabular data it usually wins on raw accuracy.

from xgboost import XGBClassifier

model = XGBClassifier(n_estimators=300, max_depth=4, learning_rate=0.05)
model.fit(X_train, y_train)

The cost is the mirror image: it's a black box. You can't read it the way you read coefficients, it will happily overfit if you let it, and — this is the part that bites — it will exploit any leakage in your data with terrifying enthusiasm.

When boosting genuinely wins

Let me be fair to boosting, because it deserves it. Build a dataset with a real interaction effect — where the target depends on two features multiplied together, not added — and watch what happens.

import numpy as np
from sklearn.model_selection import cross_val_score
from sklearn.linear_model import LogisticRegression
from xgboost import XGBClassifier

rng = np.random.default_rng(0)
n = 5000
x1 = rng.normal(size=n)
x2 = rng.normal(size=n)

# the signal lives in the INTERACTION: x1 * x2, not x1 + x2
logit = 3 * (x1 * x2)
y = (rng.uniform(size=n) < 1 / (1 + np.exp(-logit))).astype(int)
X = np.column_stack([x1, x2])

lr  = LogisticRegression()
xgb = XGBClassifier(n_estimators=200, max_depth=3, learning_rate=0.1)

print("logreg:", cross_val_score(lr,  X, y, cv=5, scoring="roc_auc").mean())
print("xgb:   ", cross_val_score(xgb, X, y, cv=5, scoring="roc_auc").mean())

Logistic regression will score around chance here — close to 0.5 AUC — because there's no straight-line relationship between either feature alone and the target. Boosting will score much higher, because trees can split on x1 and then split on x2 inside that branch, which is exactly an interaction.

That's the honest case for boosting: when the signal is nonlinear or lives in interactions, and you don't know that ahead of time. Trees find structure you didn't hand-engineer.

But notice the catch in that last sentence — "you didn't hand-engineer." What if you had?

The plot twist: features close the gap

Give the linear model the interaction term explicitly, and it catches right up:

# hand the interaction to the linear model as a feature
X_better = np.column_stack([x1, x2, x1 * x2])

print("logreg + feature:", cross_val_score(lr, X_better, y, cv=5,
                                            scoring="roc_auc").mean())

One engineered column — x1 * x2 — and the "weak" model is now competitive with boosting, while staying fully interpretable. You can look at the coefficient on that interaction term and know what the model learned.

This is the whole thesis in one experiment. Boosting wasn't smarter. It was compensating for a feature you forgot to create. The accuracy gap between a simple model and a complex one is very often just the complex model rediscovering, internally and opaquely, a feature you could have written by hand.

Better features beat a better algorithm, and they cost less to run and far less to trust.

The failure mode nobody warns you about: leakage

Here's where boosting's enthusiasm turns dangerous. Data leakage is when information sneaks into your features that wouldn't actually be available at prediction time — usually because it's downstream of the very thing you're predicting.

A concrete example. Say you're predicting whether an order will be cancelled. Someone adds a feature refund_amount. It's wildly predictive — accuracy jumps ten points. Ship it!

Except refunds only happen after a cancellation. At the moment you actually need to predict, refund_amount is always zero. You've trained a model to predict cancellations using a column that only exists because of cancellations. In production it's useless, and you won't find out until the numbers quietly fall apart.

# This "feature" is the answer wearing a disguise.
# It is only populated after the event you're trying to predict.
df["refund_amount"]   # leaks the target

Why does this matter more for boosting? Because a linear model spreads its attention across features and a single leaky column produces one suspiciously huge coefficient you might actually notice. Boosting will find the leak, latch onto it, and route most of its trees through it — handing you a gorgeous validation score that's pure fiction. The more powerful the model, the more efficiently it exploits a mistake in your data.

There's a subtler version too — preprocessing leakage — where you compute something over the whole dataset before splitting:

# WRONG: scaler sees the test set's statistics before you split
X_scaled = StandardScaler().fit_transform(X)
X_train, X_test = train_test_split(X_scaled)

# RIGHT: fit preprocessing on train only, inside a pipeline
from sklearn.pipeline import make_pipeline
pipe = make_pipeline(StandardScaler(), LogisticRegression())
scores = cross_val_score(pipe, X, y, cv=5)   # scaler refits on each fold's train

A Pipeline isn't a style preference. It's the thing that keeps test information from bleeding into training, and it's the difference between a validation score you can believe and one you can't.

So how do I actually choose?

Here's the decision I'd hand a junior engineer, in order:

→ Start with the simple model. Logistic or linear regression, clean features, a real cross-validation setup. This is your baseline and your sanity check — if it scores absurdly well, you probably have leakage, and the simple model made it easy to spot.

→ Spend your effort on features, not models. Interactions, ratios, time-since-event, sensible encodings. Most of the accuracy you're chasing lives here. Every feature you engineer by hand is one the black box doesn't have to reconstruct opaquely.

→ Reach for boosting when the simple model plateaus and you've ruled out leakage and you've exhausted obvious features. Now you're using boosting for what it's actually good at — nonlinearity you genuinely can't hand-engineer — instead of as a band-aid over lazy features.

→ When you do use boosting, demand interpretability back. Feature importances, SHAP values, partial dependence. If you can't explain why it predicts what it predicts, you can't catch it when it's wrong.

The principle underneath all of it: model choice is a data decision, not a leaderboard contest. A clean regression on good features will beat boosting on dirty ones almost every time, and it'll be cheaper to run and easier to defend. XGBoost won't save you from a pipeline that feeds it lies. Nothing will.

When your accuracy last stalled — did you reach for a new model, or did you go back and interrogate the features first? I'm curious which instinct fired, because it tells you a lot about where you are in this.

I'm Vinicius Fagundes — principal data engineer, independent, and an MBA lecturer in São Paulo. I build and fix the data pipelines that feed models like these. If this is your world, this is the work I do at vf-insights.com.

Your pipeline isn't slow. Your parser is.

Vinicius Fagundes — Wed, 17 Jun 2026 17:47:20 +0000

8 hours. That's how long a nightly batch job took when I picked it up two weeks ago. The team had already thrown a bigger cluster at it. Twice. It barely moved.

By the end it ran in 47 minutes. Same cluster. Same algorithms. I didn't touch the "smart" part of the pipeline at all.

The whole win was in how the data entered the system. The parser. The first few stages nobody ever profiles because they look boring.

This post is the long version of that fix — written so that if you've only been doing this for a year, you can follow every line. We'll define the jargon as we go, measure before we touch anything, and then walk through the five things that are almost certainly eating your runtime, each with before-and-after code you can run.

First, what is "the layer"? (30 seconds for newcomers)

A data pipeline is just a chain of stages. Raw data goes in one end, something useful comes out the other:

raw files  ->  parse  ->  transform  ->  aggregate / model  ->  output

Parse (also called ingestion or deserialization) means turning raw text — a JSON file, a CSV, a log line — into objects your program can work with, like Python dictionaries and lists. The bytes {"id": 7} on disk become the dict {"id": 7} in memory. That conversion is not free.
Transform is where you clean, reshape, join, and compute features.
Model / aggregate is the part people think of as "the real work" — the algorithm, the SQL, the math.

Here's the thing almost nobody internalizes early: that first box, parse, often costs more than all the others combined. And because it sits at the front, everything downstream pays its tax. A slow parser doesn't just slow down parsing — it slows down every stage that waits on it.

So when a pipeline is slow, the instinct is to make the smart parts faster. Usually the smart parts were never the problem.

Rule 1: never optimize what you haven't measured

Before you change a single line, find out where the time actually goes. This is the most important habit in this whole post, and it's three lines of code.

import time
from contextlib import contextmanager

@contextmanager
def stage(name):
    start = time.perf_counter()
    yield
    print(f"{name:<18} {time.perf_counter() - start:6.2f}s")

Now wrap each stage of your pipeline in it:

import json

with stage("read + parse"):
    records = [json.loads(line) for line in open("events.jsonl")]

with stage("transform"):
    rows = [transform(r) for r in records]

with stage("aggregate"):
    result = aggregate(rows)

You'll see something like this, and it's the whole story:

read + parse        372.10s
transform            48.30s
aggregate            12.05s

Parsing is 86% of the runtime. The "data science" everyone wanted to optimize is the bottom two lines.

When you need more detail — which function inside parsing is slow — reach for the profiler that ships with Python:

import cProfile
cProfile.run("run_pipeline()", sort="cumulative")

Read the top few rows of the output. If you see names like loads, decode, raw_decode, or scanstring near the top, that's the JSON parser, and you've just confirmed your bottleneck is ingestion — not the cluster, not Spark, not your SQL.

Everything below is what to do once the profiler points at parsing.

The five things quietly eating your night

1. You parse the same data five times

This is the most common one, and it hides because each stage looks reasonable on its own. The bug only shows up when you zoom out.

def clean_stage():
    data = [json.loads(l) for l in open("events.jsonl")]   # parse #1
    return [r for r in data if r["status"] == "ok"]

def feature_stage():
    data = [json.loads(l) for l in open("events.jsonl")]   # parse #2 (!)
    return build_features(data)

def report_stage():
    data = [json.loads(l) for l in open("events.jsonl")]   # parse #3 (!!)
    return summarize(data)

Three functions, each independently re-reading and re-parsing the same file from scratch. If parsing is 80% of your cost, you just paid it three times. I've seen pipelines pay it seven or eight times across a DAG because each task was written by a different person on a different day.

The fix is embarrassingly simple: parse once, pass the result down.

records = [json.loads(l) for l in open("events.jsonl")]   # parse ONCE

cleaned   = clean_stage(records)
features  = feature_stage(cleaned)
report    = report_stage(features)

In a real orchestrator (Airflow, dbt, Spark), "parse once" usually means land the parsed data somewhere — a temp table, a cached DataFrame, a Parquet file — so the next task reads the cheap version instead of re-parsing the raw source. More on that in #5.

2. You hydrate the whole object to use three fields

Say each record is a big nested blob, but your job only ever touches three values.

def parse(line):
    obj = json.loads(line)   # builds the ENTIRE nested tree in memory
    return obj               # ...and you carry all of it downstream

You can't avoid parsing the line to find your fields — but you can avoid carrying the whole thing. Pull out what you use and drop the rest at the boundary:

import orjson  # a C-based JSON parser, typically several times faster than the stdlib

def parse(line):
    obj = orjson.loads(line)
    return {
        "user_id": obj["user"]["id"],
        "ts":      obj["event"]["timestamp"],
        "amount":  obj["event"]["payload"]["amount"],
    }

Two wins here, and it's worth being precise about which is which:

orjson swaps the slow pure-Python decode path for a fast C one. That's a real parse-time speedup, and it's a one-line change — pip install orjson, change json to orjson.
The field projection doesn't make the parse cheaper (you still parse the line). It makes everything after the parse cheaper: less memory held, smaller objects to shuffle and serialize between stages, and no repeated deep lookups like obj["event"]["payload"]["amount"] happening over and over in later code. You do the deep walk once, here, and hand downstream a flat little dict.

Small objects move faster through every stage. A flat 3-key dict is cheaper to pass to Spark, pickle to a worker, or write to disk than a 200-key nested tree.

3. You load a 10 GB file just to read it once

If a single JSON document is bigger than your RAM, json.loads(f.read()) will either crash or swap to disk and crawl. You don't need the whole thing in memory at once — you need one record at a time. That's called streaming, and ijson does it:

import ijson

with open("huge.json", "rb") as f:
    for record in ijson.items(f, "item"):   # yields one array element at a time
        process(record)

ijson walks the file as a stream and hands you records as it finds them, so memory stays flat whether the file is 100 MB or 100 GB. The "item" argument is the path to the elements you want — for a top-level JSON array [ {...}, {...} ], each element is item.

If your data is newline-delimited JSON (one object per line — the .jsonl format), you don't even need a library; just iterate the file line by line and parse each line, which is already streaming:

with open("events.jsonl") as f:
    for line in f:            # one line in memory at a time
        record = orjson.loads(line)
        process(record)

The lesson: never pull a whole dataset into memory to look at it piece by piece.

4. You let Spark guess your schema

This one is a Spark-specific tax that surprises a lot of people. When you do this:

df = spark.read.json("s3://bucket/events/")

Spark doesn't know the structure of your JSON, so before it can read the data it launches an extra pass over the files just to figure out the schema — what columns exist and what types they are. Then it reads everything again to actually load it. On a big dataset that inference pass is a full, expensive scan you're paying for silently, and on wide/nested JSON it can also blow up driver memory.

Tell Spark the schema up front and it skips the guessing pass entirely:

from pyspark.sql.types import StructType, StructField, StringType, LongType, DoubleType

schema = StructType([
    StructField("user_id", StringType()),
    StructField("ts",      LongType()),
    StructField("amount",  DoubleType()),
])

df = spark.read.schema(schema).json("s3://bucket/events/")

Same result, one less scan, lower memory, and a bonus: an explicit schema means a malformed record fails loudly instead of silently inferring the wrong type and poisoning everything downstream. (The same idea applies to spark.read.csv(..., inferSchema=True) — inference always costs an extra read.)

5. The real fix: JSON is the wrong shape to begin with

Everything above is treating symptoms. Here's the cause.

JSON is row-oriented and untyped text. Every time anything reads it, it has to be parsed again from scratch, and the types re-derived. There is no way to read "just the amount column" — to get one field out of every record, the parser still has to chew through every record. You pay the full parse cost on every single read, forever.

The fix is to pay that cost exactly once, at ingestion, and convert the raw JSON into a columnar, typed format. The standard choice is Parquet.

import pyarrow.json as paj
import pyarrow.parquet as pq

# At ingestion, ONCE: parse the JSON a single time and write Parquet
table = paj.read_json("events.jsonl")
pq.write_table(table, "events.parquet")

From then on, every downstream job reads the Parquet, and reading Parquet is a different universe:

import pyarrow.parquet as pq

# reads ONLY these two columns off disk; the rest is never touched
df = pq.read_table("events.parquet", columns=["user_id", "amount"])

Why this is so much faster, in plain terms:

Columnar means values for each column are stored together, instead of record-by-record. So "give me the amount column" reads one contiguous chunk and ignores the other 197 columns. In row-oriented JSON, the same request has to read and parse every record in full.
Typed means the column already knows it's a 64-bit integer. No text-to-number parsing on read. The types are stored in the file.
Compressed + indexed means Parquet keeps min/max stats per chunk ("row group"), so a filter like amount > 1000 can skip entire chunks without reading them. That's called predicate pushdown — the filter is pushed down into the read itself.

The mental model: JSON makes every reader redo the work. Parquet does the work once and lets every reader skip to exactly what it needs. If your raw data lands as JSON and gets read more than once — and it always does — converting it to columnar at the front door is usually the single highest-leverage change you can make.

The pattern behind all five: normalize once at the front door

Step back and every fix above is the same move. Do the expensive, messy work — parsing, type-casting, field extraction, schema enforcement — once, at the boundary where data enters your system. Produce one clean, typed, columnar version. Then let every downstream stage read that cheap version instead of fighting the raw input over and over.

raw JSON  ->  [ parse + flatten + type + write Parquet ]  ->  clean columnar table
                          (pay this ONCE)                          |
                                                                   v
                                          every model / report / job reads this

This is why the 8-hour job became a 47-minute job without touching the algorithms. The old version re-parsed raw nested JSON inside every stage, inferred the schema on every read, and dragged the full objects through the whole DAG. The new version parsed once, landed typed Parquet, and let everything downstream read flat typed columns. Nothing got smarter. The work just moved to the layer it belonged in — and stopped being repeated.

The principle

If your parser is wrong, every layer downstream pays for it. Your model is only as fast as the data feeding it, and your data science is only as good as the shape the data arrives in. You can have a perfect algorithm sitting behind a parser that re-reads 10 GB of JSON five times a night, and you will spend your week buying bigger clusters to fix a problem that lives in the first ten lines of the job.

So before you reach for more compute, profile the thing and find out where the time actually goes. Nine times out of ten it's hiding at the front door, in the boring stage nobody looks at.

Last time you chased a slow pipeline — when you finally measured it, where did the time actually turn out to be hiding? I collect these stories.

I'm Vinicius Fagundes — principal data engineer, independent, and an MBA lecturer in São Paulo. I spend my days making slow, expensive, fragile pipelines fast, cheap, and boring. If this sounds like your stack, this is the work I do at vf-insights.com.

SQL or Python? The line is sharper than you think (with code)

Vinicius Fagundes — Tue, 16 Jun 2026 02:35:15 +0000

Most engineers reach for Python first. Wrong instinct.

I've spent 17 years inside other people's pipelines — financial services, payments, ecommerce, gaming — and the same bug keeps showing up wearing two different costumes. Either someone wrote a Python loop doing what a single window function solves in milliseconds, or they buried a procedural decision tree inside a 60-line CASE statement that no one can test or read.

Same root cause. Wrong language for the job.

So let me give you the rule I actually teach, and then earn it with code — the cases where Python turns simple SQL into a nightmare, and the cases where SQL turns simple Python into one.

The one-line rule

If you're declaring what data you want, use SQL. If you're describing how to transform it, step by step, use Python.

SQL is declarative and set-at-once. You describe the shape of the result and the engine figures out the execution plan — joins, memory, spill to disk, parallelism across cores. You don't manage any of that.

Python is imperative and row-by-row. You control the algorithm and you can reach the outside world — an API, a model, a file on S3. But you also own the memory, the ordering, and the state. Nobody parallelizes it for you.

That's the whole fork. Everything below is just consequences.

Part 1 — When SQL is trivial and Python fights you

These are the set operations. Joins, aggregations, ranking, windows. The moment you find yourself writing a loop to do one of these, stop.

1. "Give me the latest row per customer"

Dedup-to-latest is the single most common request I see done badly.

-- Snowflake / BigQuery / Databricks / DuckDB
SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY updated_at DESC
) = 1;

One window function. QUALIFY filters on the window result directly — no subquery, no extra CTE. (On Postgres/MySQL/SQL Server, which don't have QUALIFY yet, you wrap the ROW_NUMBER() in a CTE and filter WHERE rn = 1. Same idea, one more line.)

Now Python, no pandas:

latest = {}
for row in orders:
    cid = row["customer_id"]
    current = latest.get(cid)
    if current is None or row["updated_at"] > current["updated_at"]:
        latest[cid] = row

result = list(latest.values())

Honestly? That's fine for one key and one tie-breaker. It's readable. But watch what happens when reality shows up: now you dedup on (customer_id, region) and the tie-break is updated_at DESC, id DESC. In SQL you add two words to the ORDER BY. In Python you're now writing comparison branches by hand, and that's where the bugs move in.

The SQL doesn't get more complex as the rule gets more complex. The Python does.

2. Moving averages and running totals

Trailing average per account, last 7 rows:

SELECT
    account_id,
    event_date,
    amount,
    AVG(amount) OVER (
        PARTITION BY account_id
        ORDER BY event_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS trailing_avg
FROM transactions;

One frame spec. (For a true time-based window — "last 7 calendar days" — you use RANGE ... INTERVAL where the dialect supports it; ROWS counts rows, not days.)

The pure-Python version has to sort, group by partition, and hand-roll a sliding window:

from collections import defaultdict, deque

by_account = defaultdict(list)
for row in sorted(transactions, key=lambda r: (r["account_id"], r["event_date"])):
    by_account[row["account_id"]].append(row)

results = []
for account_id, rows in by_account.items():
    window = deque(maxlen=7)          # auto-evicts the oldest
    for row in rows:
        window.append(row["amount"])
        avg = sum(window) / len(window)   # len() handles the partial window at the start
        results.append({**row, "trailing_avg": avg})

It works. But look at everything you just took ownership of: the sort, the partitioning, the eviction, the partial-window edge case at the start of each group. The SQL engine did all of that for free and ran every partition in parallel and spilled to disk if the data didn't fit in memory. Your Python loaded the whole thing into one machine's RAM and ran single-threaded.

3. Sessionization — the gaps-and-islands problem

Group events into sessions, where a gap over 30 minutes starts a new one. This is the example I use in class because it looks scary in SQL and isn't.

WITH marked AS (
    SELECT
        user_id,
        event_time,
        CASE
            WHEN DATEDIFF('second',
                          LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time),
                          event_time) > 1800
              OR LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
            THEN 1 ELSE 0
        END AS is_new_session
    FROM events
),
numbered AS (
    SELECT
        user_id,
        event_time,
        SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
    FROM marked
)
SELECT
    user_id,
    session_id,
    MIN(event_time) AS session_start,
    MAX(event_time) AS session_end,
    COUNT(*)        AS event_count
FROM numbered
GROUP BY user_id, session_id;

(The DATEDIFF signature is the part that moves between dialects: Snowflake DATEDIFF('second', a, b), Databricks timestampdiff(SECOND, a, b), Postgres EXTRACT(EPOCH FROM b - a).)

The trick is the running SUM over a 0/1 flag — every time a new session starts, the cumulative sum ticks up, and that number becomes the session id. No loop. The engine does it across every user at once.

Here's the Python:

from collections import defaultdict

events_by_user = defaultdict(list)
for e in events:
    events_by_user[e["user_id"]].append(e)

sessions = []
for user_id, evs in events_by_user.items():
    evs.sort(key=lambda e: e["event_time"])
    prev = None
    current = None
    for e in evs:
        if prev is None or (e["event_time"] - prev).total_seconds() > 1800:
            current = {
                "user_id": user_id,
                "session_start": e["event_time"],
                "session_end": e["event_time"],
                "event_count": 0,
            }
            sessions.append(current)
        current["session_end"] = e["event_time"]
        current["event_count"] += 1
        prev = e["event_time"]

And now I'll say the thing most "SQL vs Python" posts won't: this Python is arguably more readable than that two-CTE SQL, if you've never seen the gaps-and-islands pattern. So readability isn't the argument here. Scale and locality are.

The SQL runs next to the data, parallel across users, spilling to disk when it overflows. The Python pulls every event into one process and runs it on one core. At a million rows you won't notice. At two billion, the Python is a 3 a.m. page and the SQL is a coffee break. That difference — where the work physically runs — is the real reason to keep set operations in SQL, not the line count.

Part 2 — When Python is trivial and SQL fights you

Flip it. The moment your logic needs the outside world, branches hard, or depends on a value it just computed, you've left set-operation territory. Forcing it back into SQL is how you get the unreadable monsters.

4. Enriching each row from an API or a model

import requests

def enrich(rows):
    for row in rows:
        resp = requests.get(
            "https://api.example.com/company",
            params={"domain": row["email_domain"]},
            timeout=5,
        )
        data = resp.json() if resp.ok else {}
        row["company_size"] = data.get("employees")
        row["industry"] = data.get("industry")
    return rows

SQL has no clean answer for this. You can wrap the call in a UDF, but now you've smuggled a network round-trip into a warehouse scan and you pay for it on every row, every run, with no easy retry or backoff. The same is true for running a model per row, or reading a file per record. The instant a row needs to talk to something outside the database, the database is the wrong layer.

5. Many independent business rules

Shipping fee: domestic vs international, member discount, express surcharge, weekend surcharge, an oversized-international case that needs a manual quote. Five rules that combine. In SQL this becomes a CASE tree, and the trap is that the rules multiply:

SELECT
    order_id,
    CASE
        WHEN country NOT IN ('BR','US','CA') AND weight_kg > 30 THEN NULL  -- manual quote
        WHEN is_member AND express     THEN base_rate * 0.9 + 12 + 0.5 * weight_kg
        WHEN is_member AND NOT express THEN base_rate * 0.9
        WHEN express                   THEN base_rate + 12 + 0.5 * weight_kg
        ELSE base_rate
    END AS shipping_fee   -- now add the weekend surcharge to FOUR of these branches...
FROM orders;

Every new independent rule doubles the branch count, and a rule like "weekend surcharge" has to be pasted into several branches at once. That's a bug farm.

In Python the rules stay independent and additive:

DOMESTIC = {"BR", "US", "CA"}
WEEKEND_SURCHARGE = 8.0

def shipping_fee(order):
    if order["country"] not in DOMESTIC and order["weight_kg"] > 30:
        return None  # oversized international -> manual quote

    fee = order["base_rate"]
    if order["is_member"]:
        fee *= 0.9
    if order["express"]:
        fee += 12 + 0.5 * order["weight_kg"]
    if order["created_at"].weekday() >= 5:   # Saturday or Sunday
        fee += WEEKEND_SURCHARGE
    return round(fee, 2)

Each rule is one line and doesn't touch the others. And here's the part that settles it for me: you can unit-test this.

def test_member_express_weekend():
    order = {"country": "BR", "weight_kg": 2, "base_rate": 100,
             "is_member": True, "express": True,
             "created_at": datetime(2026, 6, 13, 10, 0)}  # a Saturday
    assert shipping_fee(order) == 100 * 0.9 + 12 + 1 + 8

You can't pytest a CASE expression. When the logic is the product, that's not a small thing.

6. When row N depends on the computed value of row N-1

This is the cleanest boundary in the whole post.

Running balance, floored at zero — the balance can never go negative, and a withdrawal that would overdraw just lands you at zero. The obvious SQL is a cumulative sum:

SELECT ts, amount,
       SUM(amount) OVER (ORDER BY ts) AS balance   -- WRONG once there's a floor
FROM ledger;

It's wrong, and it's wrong in an interesting way. A plain window SUM adds up the raw inputs. But the floor makes each step depend on the clamped result of the previous step, not the raw number. Window functions can see the prior row's input; they can't see their own prior output. So the moment the computation feeds itself, the window function can't express it.

Pure SQL forces you into a recursive CTE:

WITH ordered AS (
    SELECT ts, amount, ROW_NUMBER() OVER (ORDER BY ts) AS rn FROM ledger
),
walk AS (
    SELECT rn, ts, amount, GREATEST(amount, 0) AS balance
    FROM ordered WHERE rn = 1
    UNION ALL
    SELECT o.rn, o.ts, o.amount, GREATEST(w.balance + o.amount, 0) AS balance
    FROM walk w
    JOIN ordered o ON o.rn = w.rn + 1
)
SELECT ts, amount, balance FROM walk ORDER BY ts;

That's correct. It also processes one row per recursion step — no parallelism — and on a few million rows it gets slow or trips the recursion limit.

The Python is three lines:

balance = 0.0
out = []
for tx in sorted(ledger, key=lambda t: t["ts"]):
    balance = max(balance + tx["amount"], 0.0)
    out.append({**tx, "balance": balance})

The principle: when each row depends on a value you computed for the row before — not its raw input, the computed result — you're doing iteration, not set reduction. SQL can technically limp through it. It's still the wrong tool.

The cheat sheet

What the problem looks like	Reach for
Join / filter / aggregate across many rows	SQL
Rank, dedup, "latest per group"	SQL — window + `QUALIFY`
Running totals, moving averages, sessionization	SQL — window functions
You're declaring what you want	SQL
Per-row call to an API, a model, or a file	Python
Many independent business rules	Python
Logic that needs to be unit-tested in isolation	Python
Row N depends on the computed value of row N-1	Python
You're describing how to transform it, step by step	Python

How I actually decide in production

Push the set reduction as far upstream as it'll go. Join, filter, and aggregate in SQL — in the warehouse, in dbt — where it's parallel and cheap and the optimizer does the hard part. Shrink the data to the smallest correct set before it ever reaches Python. Then drop into Python only for the procedural tail: the enrichment, the branching, the model call, the stateful walk.

The internet will tell you one of these is "faster." That's the lazy framing. They're not competing — they're a relay. The skill is knowing exactly where the baton changes hands.

One number to make it concrete. A pipeline I inherited did its core join as a Python row-loop — nested iteration over data the warehouse could have joined in its sleep. Rewritten as a single SQL join plus one window pass, same data, same cluster, it dropped from 8 hours to 47 minutes. Nothing got smarter. The work just moved to the layer it belonged in.

So before you write the loop, ask the only question that matters: am I declaring what I want, or describing how to build it? Get that right and the language picks itself.

What's the worst case you've seen — a Python loop that should've been one query, or a CASE statement that should've been a function? I want the war story.

I'm Vinicius Fagundes — principal data engineer, independent, and an MBA lecturer in São Paulo. I rebuild slow, expensive, and fragile data pipelines for a living. If your stack looks like any of the above, this is the work I do at vf-insights.com.

Fraud Detection and Recommendation Are the Same Pipeline. Most Teams Build Two.

Vinicius Fagundes — Fri, 15 May 2026 03:00:00 +0000

Fraud asks: which signal means stop?
Recommendation asks: which signal means go?

The behavioral signal layer underneath both is identical.
Most companies build it twice — and pay for it twice.

What both systems actually need

Strip both systems to their fundamentals:

Fraud detection pipeline:

Collect behavioral events (clicks, sessions, device, velocity, IP)
Enrich with context (account history, graph relationships, geolocation)
Build features (velocity signals, anomaly indicators, identity consistency)
Score: is this transaction fraudulent?

Recommendation engine pipeline:

Collect behavioral events (clicks, sessions, device, purchases, dwell time)
Enrich with context (purchase history, category affinity, recency)
Build features (preference signals, recency decay, cross-category behavior)
Score: which item should this user see next?

Steps 1–3 are the same work. The event pipeline, enrichment layer, and feature store are architecturally identical. Only the scoring layer diverges.

The unified architecture

              ┌─────────────────────────────────────┐
              │         BEHAVIORAL EVENTS            │
              │  clicks · sessions · device ·        │
              │  velocity · purchases · affinity      │
              └──────────────┬──────────────────────┘
                             │
              ┌──────────────▼──────────────────────┐
              │           EVENT PIPELINE             │
              │         (Kafka / Kinesis)             │
              └──────────────┬──────────────────────┘
                             │
              ┌──────────────▼──────────────────────┐
              │            FEATURE STORE             │
              │  velocity | identity | recency |      │
              │  affinity | anomaly | preference      │
              └──────────┬───────────────┬───────────┘
                         │               │
           ┌─────────────▼──┐       ┌───▼─────────────┐
           │  FRAUD MODEL   │       │   REC MODEL      │
           │  "STOP signal" │       │   "GO signal"    │
           │  risk_score    │       │   ranked_items   │
           └────────────────┘       └─────────────────┘

The split happens at the model layer. Everything upstream is shared infrastructure.

What happens when teams build separately

When fraud and recommendation run separate pipelines:

Two event schemas — identity resolution fails at the join. The device key that fraud uses to detect account takeover is not the same key the recommendation team uses for personalization.
Two feature stores — velocity signals that flag fraud never reach the recommendation team. High-velocity behavior is an anomaly signal for fraud and a high-intent signal for growth. Two teams, same signal, different conclusions, zero shared infrastructure.
Two identity graphs — the clean device graph built by the fraud team is unused by recommendation. The fragmented profiles that hurt recommendation accuracy are the same fragmentation the fraud team already solved.
Two data teams — double the cost, double the latency, double the maintenance.

The practical payoff of a unified layer

Fraud signals improve recommendation quality.

A user exhibiting high-velocity behavior across multiple unresolved device IDs is either a bot or a high-intent buyer in the final decision window. The recommendation engine should know which — and it can, if it reads from the same feature store.

Recommendation signals improve fraud detection.

A user whose purchase pattern suddenly diverges from their personalized ranking history is a behavioral anomaly. That divergence signal is already computed by the recommendation feature pipeline. The fraud model should consume it.

Identity resolution fixed once, applied everywhere.

Session stitching and device graph resolution built for fraud is the same graph the recommendation engine needs to resolve cross-device journeys. Build it once.

Build order if you're starting from scratch

# 1. Single event schema — shared between fraud and growth
event = {
    "user_id": "...",       # resolved identity key
    "device_id": "...",     # raw device
    "session_id": "...",
    "event_type": "...",    # click | purchase | login | flag
    "timestamp": "...",
    "context": {}           # category, value, geolocation, etc.
}

# 2. Single feature store — consumed by both models
features = {
    "velocity_1h": ...,     # fraud: anomaly | rec: intent signal
    "velocity_24h": ...,
    "identity_score": ...,  # fraud: consistency | rec: coverage
    "recency": ...,         # fraud: account age | rec: freshness
    "affinity_vector": ..., # fraud: behavioral baseline | rec: preference
}

# 3. Split at the model layer only
fraud_score = fraud_model.predict(features)
ranked_items = rec_model.rank(features, catalog)

The principle

Fraud and recommendation are not two different data problems. They are two different decisions made from the same behavioral signal.

Stop building the pipeline twice.

Have you seen teams successfully share infrastructure between fraud and recommendation, or is it always siloed? Curious what the actual blockers look like — organizational or technical.

vf-insights.com

Why Your Recommendation Engine Passes Every Test and Fails in Production

Vinicius Fagundes — Thu, 14 May 2026 14:19:25 +0000

Offline metrics look clean.
CTR is flat.
Conversion is down.

The problem isn't the model. It's what the model is ranking against.

This pattern shows up across recommendation engine audits:

The team ships a new model version. Offline retrieval score improves. A/B test shows neutral-to-positive CTR. Three months later, conversion is still flat.

In 20 audits, 18 teams answered with a model name. Two answered with a pipeline diagram.

The two that answered with a pipeline diagram had fixed the problem.

Why offline metrics lie

Offline retrieval metrics measure how well your model ranks items against a historical behavior sample. They cannot measure two things:

Whether the behavioral signals feeding the model are fresh
Whether those signals belong to the right user

Both failures are silent. The model scores look correct. The production output is fiction.

The staleness gap

signal_age     = current_timestamp - last_event_timestamp
relevance_loss ≈ f(signal_age, behavior_drift_rate)

Customer behavior shifts daily — sometimes hourly around promotions, seasonal events, or price changes. If your embeddings refresh weekly, you are ranking users against a week-old snapshot of their preferences.

The model is ranking a ghost.

From a real rebuild:

Metric	Value
Embedding refresh cadence	Weekly
Customer behavior shift	Daily (sometimes hourly)
Real retrieval accuracy	38% (not the 0.91 offline score)
After pipeline rebuild (same model)	87%
CAC reduction, next quarter	−34.7%

No model changes. Same architecture. Zero new training data. The pipeline feeding the model was wrong.

The identity gap

The staleness problem is the first layer. The identity problem is the second.

How many device keys does your pipeline assign to one customer on a cross-device journey?

customer_A (mobile)  → profile_1 → ranking_1
customer_A (desktop) → profile_2 → ranking_2
customer_A (app)     → profile_3 → ranking_3

Three different recommendation strategies for one converting customer. The engine ranks each in isolation, because the identity layer never merged them.

The fix is upstream of the model: session stitching, device graph resolution, cross-channel event merging. None of it requires retraining.

The 4 audit checks

Run these before scheduling the next training run:

01 — Signal freshness
What is the median age of behavioral signals entering your model at serving time? If signal_age > behavior_drift_window, your ranking is stale by definition.

02 — Identity coverage
What percentage of your user base has more than one unmerged identity key? Run this query on your feature store. The number is almost always higher than expected.

03 — Offline-online metric gap
If offline accuracy is high but online CTR is flat, the distribution shift is coming from the pipeline, not the model. The serving distribution doesn't match the training distribution because the signals changed.

04 — Conversion window alignment
What is the gap between signal capture and recommendation serving? Is it shorter than the typical purchase decision window for your category? If not, you are recommending into the wrong moment.

The principle

A recommendation engine ranks signals. If the signals are stale or fragmented across unresolved identities, the ranking is precise noise.

Fix the pipeline. The model is the last thing to change.

How fresh are the behavioral signals entering your recommendation model at serving time? That single number explains most of the offline-online gap I've seen — curious what it looks like in your stack.

vf-insights.com

Building Your First Data Warehouse in Databricks — End to End 🎉

Vinicius Fagundes — Mon, 11 May 2026 03:00:00 +0000

Building Your First Data Warehouse in Databricks — End to End 🎉

This is it. The article the entire series has been building toward.

We've covered Databricks fundamentals, Apache Spark, Delta Lake, DBFS, DataFrames, SQL, and the Medallion Architecture. Now we wire everything together into a real, working data warehouse — from raw data ingestion all the way to queryable Gold tables.

By the end of this article you'll have a functioning Lakehouse with Bronze, Silver, and Gold layers, a database registered in the Databricks catalog, and the ability to query your warehouse like a real data engineer.

Let's build it.

What We're Building

We'll build a Sales Data Warehouse using a publicly available dataset. Here's the full architecture:

CSV Files (raw sales data)
        ↓
   🥉 BRONZE
   bronze.sales_raw
   Raw Delta table, append-only
        ↓
   🥈 SILVER
   silver.sales
   Cleaned, deduplicated, enriched
        ↓
   🥇 GOLD
   gold.monthly_revenue     — Revenue by region and month
   gold.product_performance — Top products by sales volume
   gold.customer_segments   — Customers segmented by spend tier
        ↓
   SQL queries / BI tool

Step 0: The Dataset

We'll use the Online Retail dataset — a real e-commerce transaction dataset available in Databricks sample data.

It contains ~500,000 rows of UK retail transactions with these columns:

Column	Type	Description
`InvoiceNo`	String	Order ID
`StockCode`	String	Product code
`Description`	String	Product name
`Quantity`	Integer	Units ordered
`InvoiceDate`	String	Order date and time
`UnitPrice`	Double	Price per unit
`CustomerID`	Double	Customer identifier
`Country`	String	Customer country

Step 1: Set Up Your Databases

Start a new notebook. This will be your setup notebook — run it once to create the structure.

# notebook: 00_setup

# Create the three layer databases
spark.sql("CREATE DATABASE IF NOT EXISTS bronze")
spark.sql("CREATE DATABASE IF NOT EXISTS silver")
spark.sql("CREATE DATABASE IF NOT EXISTS gold")

# Create the mount point directories
dbutils.fs.mkdirs("/mnt/warehouse/bronze/")
dbutils.fs.mkdirs("/mnt/warehouse/silver/")
dbutils.fs.mkdirs("/mnt/warehouse/gold/")

print("✅ Databases and directories created.")

Now check the Databricks Data tab — you should see three new databases: bronze, silver, and gold.

Step 2: Bronze — Ingest Raw Data

Create a new notebook: 01_bronze_ingestion

# notebook: 01_bronze_ingestion

from pyspark.sql.functions import current_timestamp, input_file_name, lit

print("Starting Bronze ingestion...")

# -------------------------------------------------------
# Read the raw CSV from Databricks sample datasets
# -------------------------------------------------------
raw_df = spark.read.csv(
    "/databricks-datasets/online_retail/data-001/data.csv",
    header=True,
    inferSchema=True
)

print(f"Raw rows ingested: {raw_df.count():,}")
raw_df.printSchema()

# -------------------------------------------------------
# Add Bronze metadata columns
# -------------------------------------------------------
bronze_df = raw_df \
    .withColumn("_ingested_at", current_timestamp()) \
    .withColumn("_source_file", input_file_name()) \
    .withColumn("_source_system", lit("online_retail_csv"))

# -------------------------------------------------------
# Write to Bronze Delta table
# -------------------------------------------------------
bronze_df.write \
    .format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .save("/mnt/warehouse/bronze/sales_raw/")

# Register in catalog
spark.sql("""
    CREATE TABLE IF NOT EXISTS bronze.sales_raw
    USING DELTA
    LOCATION '/mnt/warehouse/bronze/sales_raw/'
""")

# Quick validation
count = spark.read.format("delta").load("/mnt/warehouse/bronze/sales_raw/").count()
print(f"✅ Bronze table written. Total rows: {count:,}")

Run the cell. You should see output similar to:

Raw rows ingested: 541,909
✅ Bronze table written. Total rows: 541,909

Let's peek at what we landed:

display(spark.read.table("bronze.sales_raw").limit(10))

You'll see messy data — nulls in CustomerID, negative quantities (returns), zero-price rows. That's fine. Bronze captures reality. Silver fixes it.

Step 3: Silver — Clean and Enrich

Create a new notebook: 02_silver_transformation

# notebook: 02_silver_transformation

from pyspark.sql.functions import (
    col, upper, trim, round, to_timestamp,
    year, month, when, current_timestamp
)

print("Starting Silver transformation...")

# -------------------------------------------------------
# Read from Bronze
# -------------------------------------------------------
bronze = spark.read.table("bronze.sales_raw")
print(f"Bronze rows: {bronze.count():,}")

# -------------------------------------------------------
# Cleaning rules
# -------------------------------------------------------
silver = bronze \
    \
    `# 1. Drop rows with null CustomerID (anonymous sessions)`
    .dropna(subset=["CustomerID"]) \
    \
    `# 2. Drop duplicates on InvoiceNo + StockCode`
    .dropDuplicates(["InvoiceNo", "StockCode"]) \
    \
    `# 3. Remove returns (negative quantities) and zero-price items`
    .filter(col("Quantity") > 0) \
    .filter(col("UnitPrice") > 0) \
    \
    `# 4. Cast and clean types`
    .withColumn("CustomerID", col("CustomerID").cast("integer")) \
    .withColumn("InvoiceDate", to_timestamp(col("InvoiceDate"), "M/d/yyyy H:mm")) \
    .withColumn("UnitPrice", round(col("UnitPrice"), 2)) \
    \
    `# 5. Derive new columns`
    .withColumn("TotalAmount", round(col("Quantity") * col("UnitPrice"), 2)) \
    .withColumn("Description", upper(trim(col("Description")))) \
    .withColumn("Year", year(col("InvoiceDate"))) \
    .withColumn("Month", month(col("InvoiceDate"))) \
    .withColumn("Tier",
        when(col("TotalAmount") >= 500, "High Value")
        .when(col("TotalAmount") >= 100, "Mid Value")
        .otherwise("Low Value")
    ) \
    \
    `# 6. Rename to snake_case`
    .withColumnRenamed("InvoiceNo",   "invoice_id") \
    .withColumnRenamed("StockCode",   "product_code") \
    .withColumnRenamed("Description", "product_name") \
    .withColumnRenamed("Quantity",    "quantity") \
    .withColumnRenamed("InvoiceDate", "invoice_date") \
    .withColumnRenamed("UnitPrice",   "unit_price") \
    .withColumnRenamed("CustomerID",  "customer_id") \
    .withColumnRenamed("Country",     "country") \
    .withColumnRenamed("TotalAmount", "total_amount") \
    .withColumnRenamed("Year",        "year") \
    .withColumnRenamed("Month",       "month") \
    .withColumnRenamed("Tier",        "tier") \
    \
    `# 7. Drop Bronze metadata`
    .drop("_ingested_at", "_source_file", "_source_system") \
    \
    `# 8. Add Silver metadata`
    .withColumn("_processed_at", current_timestamp())

print(f"Silver rows after cleaning: {silver.count():,}")

# -------------------------------------------------------
# Write to Silver Delta table
# -------------------------------------------------------
silver.write \
    .format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .partitionBy("year", "month") \
    .save("/mnt/warehouse/silver/sales/")

spark.sql("""
    CREATE TABLE IF NOT EXISTS silver.sales
    USING DELTA
    LOCATION '/mnt/warehouse/silver/sales/'
""")

print(f"✅ Silver table written.")
display(spark.read.table("silver.sales").limit(5))

Expected output:

Bronze rows: 541,909
Silver rows after cleaning: 397,924
✅ Silver table written.

We dropped ~144,000 rows — nulls, returns, zero-price items. What remains is clean, trusted data.

Step 4: Gold — Build Business Tables

Create a new notebook: 03_gold_aggregations

We'll build three Gold tables.

Gold Table 1: Monthly Revenue by Country

# notebook: 03_gold_aggregations

from pyspark.sql.functions import sum, count, avg, countDistinct, round

silver = spark.read.table("silver.sales")

# -------------------------------------------------------
# Gold 1: Monthly Revenue by Country
# -------------------------------------------------------
monthly_revenue = silver \
    .groupBy("year", "month", "country") \
    .agg(
        round(sum("total_amount"), 2).alias("total_revenue"),
        count("invoice_id").alias("total_orders"),
        round(avg("total_amount"), 2).alias("avg_order_value"),
        countDistinct("customer_id").alias("unique_customers")
    ) \
    .orderBy("year", "month", "total_revenue", ascending=[True, True, False])

monthly_revenue.write \
    .format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .save("/mnt/warehouse/gold/monthly_revenue/")

spark.sql("""
    CREATE TABLE IF NOT EXISTS gold.monthly_revenue
    USING DELTA
    LOCATION '/mnt/warehouse/gold/monthly_revenue/'
""")

print("✅ gold.monthly_revenue written.")
display(monthly_revenue.limit(10))

Gold Table 2: Product Performance

# -------------------------------------------------------
# Gold 2: Product Performance
# -------------------------------------------------------
product_performance = silver \
    .groupBy("product_code", "product_name") \
    .agg(
        round(sum("total_amount"), 2).alias("total_revenue"),
        sum("quantity").alias("units_sold"),
        count("invoice_id").alias("times_ordered"),
        countDistinct("customer_id").alias("unique_buyers"),
        round(avg("unit_price"), 2).alias("avg_unit_price")
    ) \
    .orderBy("total_revenue", ascending=False)

product_performance.write \
    .format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .save("/mnt/warehouse/gold/product_performance/")

spark.sql("""
    CREATE TABLE IF NOT EXISTS gold.product_performance
    USING DELTA
    LOCATION '/mnt/warehouse/gold/product_performance/'
""")

print("✅ gold.product_performance written.")
display(product_performance.limit(10))

Gold Table 3: Customer Segments

# -------------------------------------------------------
# Gold 3: Customer Segments
# -------------------------------------------------------
customer_segments = silver \
    .groupBy("customer_id", "country") \
    .agg(
        round(sum("total_amount"), 2).alias("lifetime_value"),
        count("invoice_id").alias("total_orders"),
        round(avg("total_amount"), 2).alias("avg_order_value"),
        countDistinct("product_code").alias("unique_products_bought")
    ) \
    .withColumn("segment",
        when(col("lifetime_value") >= 5000, "VIP")
        .when(col("lifetime_value") >= 1000, "Loyal")
        .when(col("lifetime_value") >= 200,  "Regular")
        .otherwise("Occasional")
    ) \
    .orderBy("lifetime_value", ascending=False)

customer_segments.write \
    .format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .save("/mnt/warehouse/gold/customer_segments/")

spark.sql("""
    CREATE TABLE IF NOT EXISTS gold.customer_segments
    USING DELTA
    LOCATION '/mnt/warehouse/gold/customer_segments/'
""")

print("✅ gold.customer_segments written.")
display(customer_segments.limit(10))

Step 5: Query Your Data Warehouse

Open the SQL Editor in Databricks. Your warehouse is live. Start querying.

-- What were the top 5 revenue months?
SELECT
    year,
    month,
    SUM(total_revenue)     AS monthly_revenue,
    SUM(total_orders)      AS monthly_orders,
    SUM(unique_customers)  AS monthly_customers
FROM gold.monthly_revenue
GROUP BY year, month
ORDER BY monthly_revenue DESC
LIMIT 5;

-- What are the top 10 best-selling products?
SELECT
    product_name,
    total_revenue,
    units_sold,
    unique_buyers
FROM gold.product_performance
LIMIT 10;

-- How are customers distributed by segment?
SELECT
    segment,
    COUNT(*)                   AS customer_count,
    ROUND(AVG(lifetime_value), 2) AS avg_lifetime_value,
    ROUND(AVG(total_orders), 1)   AS avg_orders
FROM gold.customer_segments
GROUP BY segment
ORDER BY avg_lifetime_value DESC;

-- Which countries generate the most revenue?
SELECT
    country,
    ROUND(SUM(total_revenue), 2) AS total_revenue,
    SUM(total_orders)             AS total_orders
FROM gold.monthly_revenue
GROUP BY country
ORDER BY total_revenue DESC
LIMIT 10;

You're querying a real data warehouse. Built by you. From scratch.

Step 6: Validate Your Warehouse

Good data engineers always validate. Run these checks before calling it done:

# notebook: 04_validation

print("=== DATA WAREHOUSE VALIDATION ===\n")

# Row counts across layers
bronze_count = spark.read.table("bronze.sales_raw").count()
silver_count = spark.read.table("silver.sales").count()

print(f"🥉 Bronze rows:  {bronze_count:>10,}")
print(f"🥈 Silver rows:  {silver_count:>10,}  ({silver_count/bronze_count:.1%} of bronze)")
print()

# Gold table counts
for table in ["gold.monthly_revenue", "gold.product_performance", "gold.customer_segments"]:
    count = spark.table(table).count()
    print(f"🥇 {table}: {count:,} rows")

print()

# Null checks on Silver
from pyspark.sql.functions import col, sum as spark_sum

silver = spark.read.table("silver.sales")
null_counts = silver.select([
    spark_sum(col(c).isNull().cast("int")).alias(c)
    for c in ["invoice_id", "customer_id", "total_amount", "invoice_date"]
])

print("Null counts on critical Silver columns:")
display(null_counts)

# Revenue sanity check
total_revenue = silver.agg({"total_amount": "sum"}).collect()[0][0]
print(f"\nTotal Silver revenue: £{total_revenue:,.2f}")
print("\n✅ Validation complete.")

Step 7: Optimize Your Tables

Now that everything is built, run maintenance on your Gold tables for faster queries:

%sql

-- Compact small files
OPTIMIZE gold.monthly_revenue;
OPTIMIZE gold.product_performance;
OPTIMIZE gold.customer_segments;

-- Speed up common filter patterns
OPTIMIZE gold.monthly_revenue     ZORDER BY (year, month, country);
OPTIMIZE gold.product_performance ZORDER BY (total_revenue);
OPTIMIZE gold.customer_segments   ZORDER BY (segment, country);

What You've Built

Let's look at the complete picture:

📁 Databases created:
   bronze / silver / gold

📄 Tables created:
   bronze.sales_raw          — 541,909 rows  (raw, as-is)
   silver.sales              — 397,924 rows  (clean, enriched)
   gold.monthly_revenue      — aggregated by year/month/country
   gold.product_performance  — aggregated by product
   gold.customer_segments    — aggregated by customer

🏗️ Architecture:
   Medallion (Bronze → Silver → Gold)
   All tables in Delta format
   Silver partitioned by year/month
   Gold tables OPTIMIZE'd with ZORDER

🔍 Queryable via:
   Databricks SQL Editor
   Any BI tool via JDBC/ODBC connector
   Databricks notebooks

Where to Go From Here

You've built your first data warehouse in Databricks. Here's what to explore next:

Orchestration: Take your four notebooks and wire them into a Databricks Workflow — a scheduled pipeline that runs Bronze → Silver → Gold automatically on a schedule or trigger.

Incremental loads: Update the Bronze ingestion to load only new files, and update Silver to use MERGE instead of overwrite — real production pipelines are incremental.

Unity Catalog: In production Databricks, Unity Catalog provides centralized access control, data lineage, and governance across all your tables.

Databricks SQL Warehouses: Connect Power BI, Tableau, or Looker directly to your Gold tables via a SQL Warehouse endpoint.

dbt on Databricks: Use dbt to manage your Silver and Gold transformations with version control, testing, and documentation built in.

Series Complete 🎉

You went from zero to a working data warehouse in Databricks. That's not a small thing.

Medallion Architecture: Designing Bronze, Silver, and Gold Layers

Vinicius Fagundes — Mon, 04 May 2026 03:00:00 +0000

Medallion Architecture: Designing Bronze, Silver, and Gold Layers

Before you write a single transformation, you need a blueprint.

The Medallion Architecture is the design pattern used by data teams worldwide to organize data in a Lakehouse. It's the blueprint Databricks themselves recommend, and it's the foundation of everything we'll build in the final article of this series.

Understanding it deeply — not just the names of the layers, but why they exist — is what separates pipelines that scale from pipelines that become a maintenance nightmare six months later.

What is the Medallion Architecture?

The Medallion Architecture organizes data into three progressive layers, each named after a metal:

Raw Sources
    ↓
 🥉 BRONZE  — Raw data, exactly as received
    ↓
 🥈 SILVER  — Cleaned, validated, enriched data
    ↓
 🥇 GOLD    — Aggregated, business-ready data
    ↓
Consumers (BI tools, ML models, APIs)

Each layer has a clear purpose. Data flows in one direction — from raw to refined — and each step adds value.

The core idea is simple: never transform in place. Instead of overwriting raw data with cleaned data, you keep every layer. Raw data is always preserved. If something breaks downstream, you can reprocess from the source without re-ingesting from external systems.

The Bronze Layer: Raw Data As-Is

What it is

Bronze is your landing zone. Data arrives here exactly as it came from the source — no cleaning, no filtering, no transformation.

Think of Bronze as your archive. It's the single source of truth for what you received, when you received it, and from where.

What goes in

Files dropped by upstream systems (CSV, JSON, XML, Avro, Parquet)
API responses stored as-is
Database snapshots
Streaming event data (Kafka, Kinesis)

What it looks like

/mnt/bronze/
  ├── sales/
  │   ├── year=2024/month=01/day=01/
  │   │   └── sales_20240101_143022.csv
  │   └── year=2024/month=01/day=02/
  │       └── sales_20240102_143055.csv
  ├── customers/
  │   └── customers_full_20240101.json
  └── products/
      └── products_20240101.parquet

The rules of Bronze

1. Never delete raw data. Raw files are your safety net. Storage is cheap — losing source data is not.

2. Add ingestion metadata. When you land data in Bronze, enrich it with columns that help you debug later:

from pyspark.sql.functions import current_timestamp, lit, input_file_name

df_bronze = df_raw \
    .withColumn("_ingested_at", current_timestamp()) \
    .withColumn("_source_file", input_file_name()) \
    .withColumn("_source_system", lit("sales_db"))

3. Store as Delta. Even raw data should be Delta. You get transaction safety, history, and the ability to query it reliably.

4. No business logic here. Bronze is not the place to rename columns, apply rules, or make assumptions about the data. That's Silver's job.

Ingestion Patterns

Full load: Ingest the entire dataset every run. Simple, but expensive for large tables.

df = spark.read.csv("/mnt/landing/sales/", header=True)
df.write.format("delta").mode("overwrite").save("/mnt/bronze/sales/")

Incremental load: Ingest only new or changed records. More efficient, requires a watermark (timestamp or ID).

last_ingested = "2024-01-14"

df = spark.read.csv("/mnt/landing/sales/") \
    .filter(f"file_date > '{last_ingested}'")

df.write.format("delta").mode("append").save("/mnt/bronze/sales/")

The Silver Layer: Clean, Validated, Trusted Data

What it is

Silver is your cleaned and conformed layer. This is where raw data becomes trustworthy.

If Bronze answers "what did we receive?", Silver answers "what is actually true?"

What happens in Silver

Operation	Example
Deduplication	Remove duplicate order records
Null handling	Fill missing regions with `'Unknown'`
Type casting	Cast string dates to `DateType`
Standardization	Uppercase all product names, normalize phone formats
Validation	Reject rows where `amount < 0`
Enrichment	Join with a reference table to add region names
Schema enforcement	Ensure all expected columns are present

What it looks like

from pyspark.sql.functions import col, upper, round, when, to_date, current_timestamp, lit

# Read from Bronze
bronze = spark.read.format("delta").load("/mnt/bronze/sales/")

# Transform to Silver
silver = bronze \
    .dropDuplicates(["order_id"]) \
    .dropna(subset=["order_id", "customer_id", "amount"]) \
    .filter(col("amount") > 0) \
    .withColumn("order_date", to_date(col("order_date"), "yyyy-MM-dd")) \
    .withColumn("product", upper(col("product"))) \
    .withColumn("amount", round(col("amount"), 2)) \
    .withColumn("region",
        when(col("region").isNull(), "Unknown")
        .otherwise(col("region"))
    ) \
    .withColumn("tier",
        when(col("amount") >= 5000, "Premium")
        .when(col("amount") >= 1000, "Standard")
        .otherwise("Basic")
    ) \
    .withColumn("_processed_at", current_timestamp()) \
    .drop("_source_file", "_ingested_at")  # Drop Bronze metadata

# Write to Silver
silver.write \
    .format("delta") \
    .mode("overwrite") \
    .partitionBy("year", "month") \
    .save("/mnt/silver/sales/")

The rules of Silver

1. One Silver table per source entity. sales_silver, customers_silver, products_silver — clean versions of your source tables.

2. Still row-level granularity. Silver doesn't aggregate. Every row still represents one event, one order, one customer record.

3. Idempotent transformations. Running the Silver pipeline twice should produce the same result as running it once. No side effects.

4. Document your cleaning rules. Silver is where business decisions are baked in (amount < 0 is invalid). These rules should be documented and reviewed by the business.

The Gold Layer: Business-Ready Data

What it is

Gold is your serving layer. It contains aggregated, pre-joined, business-meaningful tables — built specifically for consumption by BI tools, dashboards, ML models, or APIs.

If Silver is clean data, Gold is useful data. The difference is intent.

What goes in Gold

Gold tables are shaped by consumer needs. Common patterns:

Aggregated metrics

from pyspark.sql.functions import sum, count, avg, countDistinct

monthly_revenue = silver_sales \
    .groupBy("region", "year", "month") \
    .agg(
        sum("amount").alias("total_revenue"),
        count("order_id").alias("total_orders"),
        avg("amount").alias("avg_order_value"),
        countDistinct("customer_id").alias("unique_customers")
    )

Wide denormalized tables (flat joins)

# Join sales + customers + products into one wide table
# BI tools love these — no runtime joins needed
wide_sales = silver_sales \
    .join(silver_customers, on="customer_id", how="left") \
    .join(silver_products, on="product_id", how="left") \
    .select(
        "order_id", "order_date", "amount",
        "customer_name", "customer_email", "customer_country",
        "product_name", "product_category"
    )

KPI summary tables

%sql

CREATE OR REPLACE TABLE gold.kpi_daily AS
SELECT
    order_date,
    region,
    SUM(amount)                 AS daily_revenue,
    COUNT(order_id)             AS daily_orders,
    COUNT(DISTINCT customer_id) AS active_customers,
    SUM(amount) / COUNT(order_id) AS avg_order_value
FROM silver.sales
GROUP BY order_date, region;

The rules of Gold

1. Named for the consumer, not the source. gold.monthly_revenue_by_region, gold.customer_360, gold.kpi_daily — the name should tell a business person exactly what's inside.

2. Optimized for reads. Use OPTIMIZE and ZORDER on the columns most commonly filtered:

%sql
OPTIMIZE gold.kpi_daily ZORDER BY (order_date, region);

3. Multiple Gold tables are normal. Finance, Marketing, and Operations may each have their own Gold tables from the same Silver source. That's fine — it's the point. One clean Silver layer, many business-specific Gold views.

Folder Structure and Naming Conventions

Consistency here pays dividends. A structure that works for teams of all sizes:

/mnt/
  ├── bronze/
  │   ├── sales/
  │   ├── customers/
  │   └── products/
  │
  ├── silver/
  │   ├── sales/
  │   ├── customers/
  │   └── products/
  │
  └── gold/
      ├── monthly_revenue/
      ├── customer_360/
      └── kpi_daily/

Naming rules worth following:

Convention	Example
Lowercase everywhere	`sales_silver` not `Sales_Silver`
Layer suffix on table names	`sales_bronze`, `sales_silver`, `sales_gold`
Business names on Gold	`monthly_revenue`, `customer_360`, `kpi_daily`
Partition folders as key=value	`year=2024/month=01/`
Timestamps in ingestion file names	`sales_20240115_143022.csv`

How Data Flows Between Layers

Let's visualize the full picture:

External Source (DB, API, SaaS)
          ↓  [Ingestion: Fivetran, Airbyte, custom script]
     🥉 BRONZE
     Raw Delta tables
     Append-only or full refresh
     Preserves source data exactly
          ↓  [Transformation: PySpark / SQL notebook]
     🥈 SILVER
     Clean Delta tables
     Row-level, validated, enriched
     One table per entity
          ↓  [Aggregation: PySpark / SQL notebook]
     🥇 GOLD
     Business Delta tables
     Aggregated, wide, KPI-ready
     One table per use case
          ↓  [Serving: SQL endpoint, BI connector]
     Consumers
     Tableau / Power BI / Looker
     ML models / APIs / reports

Each arrow is a pipeline step — a notebook or a Workflow job. In the next article, we'll wire all of this together into a running end-to-end system.

Common Mistakes to Avoid

❌ Skipping Bronze and writing straight to Silver
If your source changes or a bug is introduced, you have no raw data to reprocess from. Always land data in Bronze first.

❌ Putting business logic in Bronze
Bronze is a mirror of the source. The moment you rename a column or apply a rule in Bronze, you've hidden what the source actually sent you.

❌ Aggregating in Silver
Silver should always be row-level. Aggregations belong in Gold. Mixing them makes Silver harder to reuse for multiple consumers.

❌ Building Gold tables no one uses
Gold tables have a cost — storage, compute, maintenance. Build them for real consumers. Don't pre-aggregate everything "just in case."

❌ Not partitioning Silver and Gold tables
Large tables without partitioning mean full scans on every query. Partition by date at minimum.

Wrapping Up

Here's what to take away from this article:

The Medallion Architecture organizes data into Bronze (raw), Silver (clean), and Gold (aggregated) layers
Bronze is your archive — raw, immutable, with ingestion metadata
Silver is your trusted layer — deduplicated, validated, enriched, row-level
Gold is your serving layer — aggregated, consumer-shaped, BI-ready
Data flows one direction only: Bronze → Silver → Gold
Consistent naming, folder structure, and partitioning are not optional — they're the foundation of maintainable pipelines

In the final article, we put all ten chapters together and build a complete end-to-end data warehouse in Databricks from scratch.