Hi everyone, I'm Minh, an Analytics Engineer at Infinite Lambda, an AI and Data consulting company headquartered in London. Holistics, the team behind Holistics BI and dbdiagram.io, asked me to beta test their new thing: anfra. An open-source framework for building BI apps with coding agents. My first reaction was honestly pretty skeptical, partly because building the dashboard was never the hard part. Also, Anthropic recently launch Claude Dashboard, which also directly connect to your DWH. So I put it on a real star schema, and tried my best to have a honest review

TLDR

• Tools like anfra are exactly where I think where BI’s heading.

• anfra is basically dbt for BI apps. The semantic layer is text in git, a coding agent writes the dashboard, the CLI checks it.

• The agent writes the HTML. The metrics come from the semantic layer, so it can't invent its own math.

• First app: one lazy prompt, about 3 minutes. The four-tab "one airline vs the rest" version on 214,335 reviews: 6 more prompts, about 35 minutes.

• Interactions, lineage and exports come out of the box.

• It also showed me a data bug in about five seconds, which is exactly what a dashboard should do.

2. What anfra is

Three pieces:

• AML, the modelling language. Models, dimensions, measures, relationships, datasets. This is your semantic layer, and it's just files.

• AQL, the query language. Not SQL. Apps ask for measures by name and AQL works out the joins and the aggregation.

• Data Apps. One HTML file in apps/. That's the dashboard.

Then a CLI (anfra init, validate, show, query, serve) and a set of skills for Claude Code, Cursor and Codex, so the agent actually knows how to write all three.

The flow is very dbt-shaped:

1. Install the CLI + skills

2. anfra init

3. Connect the warehouse

4. Prompt the agent to build whatever app you want

Back to my kitchen analogy. The warehouse is the kitchen, the dataset is the dish, the dashboard is the plate. With anfra, the agent can plate as many dashboards as you want, but the recipe lives in AML, in git, where someone reviewed it. That's the "trusted" in their tagline.

3. Setup

3.1 The test data

My repo is skytrax_reviews_data. Skytrax reviews (AirlineQuality.com, closed now, so it's historical data) modelled with dbt into four star schemas: airline, airport, lounge and seat reviews. 214,335 fact rows, all in one DuckDB file on my laptop. Not huge, but real. Real nulls, real duplicates, real junk from the scrape. Good enough to break a tool.

3.2 Install, init, connect

curl -fsSL https://anfra.ai/install.sh | bash   # one binary into ~/.anfra/bin
anfra skills install --agent claude             # the skills, for Claude Code
anfra init anfra                                # the project, in an anfra/ folder

Connecting the warehouse is one YAML file:

# .anfra/data_sources.yml  (git-ignored, it holds credentials)
data_sources:
  skytrax:
    type: duckdb
    connection:
      path: <repo>/skytrax_reviews.duckdb

First query through it, plain SQL to check the connection:

anfra query -l sql -s skytrax "select count(*) as n_reviews, round(avg(average_rating),2) as avg_rating from airlines_reviews.fct_airline_review"

156,077 reviews, average rating 2.6. Then the usual loop:

anfra validate   # does the AML compile
anfra show       # the datasets, as the agent sees them
anfra serve      # the app, running locally

No account, no "request access". Install to first query took about 3 minutes, and honestly I didn't do it. I pasted the setup steps into Claude Code and said "can you install and try this out? connect to our duckdb instance here".

4. AML: where the math lives

A model is a table plus the dimensions and measures you want people to use. The agent wrote these, I reviewed them. Trimmed:

Model fct_airline_review {
  table_name: '"airlines_reviews"."fct_airline_review"'

  dimension recommended {
    type: 'truefalse'
    definition: @sql {{ #SOURCE.recommended }};;
  }

  measure recommend_rate {
    label: '% Recommended'
    description: 'Share of reviews where the reviewer recommends the airline.'
    definition: @sql CASE WHEN {{ recommended }} THEN 1.0 ELSE 0.0 END;;
    aggregation_type: 'avg'
  }
}

% Recommended is defined once, with a description, in a file I can review in a PR. Every chart that shows it asks for it by name. Nobody gets to redefine it in chart 4.

A dataset puts models together with their joins:

Dataset airline_reviews {
  models: [fct_airline_review, dim_airline]
  relationships: [
    relationship(fct_airline_review.airline_id > dim_airline.airline_id, true),
  ]
}

I ended up with six models (four facts, dim_airline, dim_airport) and four datasets, one per review type. The part I liked most here: the plugin has a hook that runs anfra validate after every AML edit. So the agent writes a model, the CLI yells at it, the agent fixes it. It checks its own homework before I ever see it.

5. AQL: asking for metrics, not writing SQL

The dashboard queries look like this:

explore {
  dimensions { period: fct_airline_review.date_submitted }
  measures { avg_rating: fct_airline_review.avg_rating }
  sorts { period asc }
}

No joins, no GROUP BY. You name the dimensions and measures, AQL figures out the rest from the dataset.

Fair warning, AQL is not SQL, and the skill literally tells the agent not to write it from SQL knowledge. There's a whole reference behind it: level of detail, nested aggregation, window functions, ranking, period comparisons. There's even an aql-writer subagent in Claude Code that writes and validates AQL on its own, so the reference doesn't eat your main context. So you don't really need to learn AQL to start. But you do need to read it in review, the same way you read the SQL an agent writes.

6. The dashboard

6.1 v1: one lazy prompt

My entire spec for v1:

okay, now try out anfra

That's it. No metric, no chart, no layout, because I wanted to see what it does when I give it nothing. It picked something small on its own: avg rating by airline, a monthly trend, click an airline to filter the trend. Then it wrote the models, ran anfra validate, checked the numbers against a direct SQL query (156,077 reviews, 2.599 avg, both match), opened the app in a headless browser and clicked a bar to make sure the cross-filter worked. About 3 minutes.

Avg rating by airline on the left, trend on the right, a seat type filter and a month / quarter / year toggle on top. Click a bar and the trend filters to that airline. I didn't write any of that wiring. anfra serve wraps it in a small app shell: a sidebar to search your apps, refresh, and download buttons. It also reloads the page whenever the app file or the AML changes, so you just leave it open next to the agent.

6.2 v2: one airline vs the rest

v1 answered "who's rated highest", which is nice, but nobody makes a decision with that. The question a real airline would ask is "are we better or worse than everyone else, and on what?" So I asked for that. These are my actual prompts, typos included:

i wants more metrics at the top and gotta be able to split it to compare a specific airlines vs how the rest of the airlines is doing
Let's create 4 dashboard, or one dashboard for 4 pages, i have 4 facts and i want to compare a specific airlines vs the rest on these 4 facts
rename call skytrax dashboard
change order from airlines to airport, to seat to lounge
add a date filter
can you add more charts and divides into sections? like avg rating, then a section for food and beverage, and section for ... a section for number of review

Six prompts, about 35 minutes. v2 is four tabs, one per review type. Pick an airline (or airport), and every metric shows it next to all the others combined.

British Airways, for example:

• 4,023 reviews, 2.6% of all airline reviews

• avg rating 2.76 vs 2.59 for the rest

• 39.7% recommended vs 37.4%

• biggest gap is ground service: 2.75 vs 2.36

Same thing for airports. London Heathrow beats the rest on every metric, and shopping is the biggest gap: 3.43 vs 2.57. Very on brand.

Seat and lounge reviews get the same layout with their own sub-ratings (legroom, recline, seat width for seats; comfort, cleanliness, catering for lounges).

My favourite detail: "the rest" is not a separate model. It's the same query with the filter flipped from is to is_not:

const app = Anfra.createApp({ title: 'Skytrax Dashboard' });
const grain = app.createDateDrill('grain', { default: 'quarter' });

const pick = app.createFilter('airline_pick', {
  dataset: 'airline_reviews', field: 'dim_airline.airline_name',
  default: { operator: 'is', values: ['british airways'] },
});
const rest = app.createFilter('airline_rest', {
  dataset: 'airline_reviews', field: 'dim_airline.airline_name',
  default: { operator: 'is_not', values: ['british airways'] },
});

Each filter gets attached to its queries with one app.mapControl(...) call. Plus a traveller type filter, a date range with 1y / 3y / 5y shortcuts, and the grain toggle.

And the look. The default anfra style is Basecoat UI, Inter, ECharts, and one violet accent. Paper and ink, one purple pen. I didn't ask for a theme and it still looks better than most dashboards I've shipped by hand.

6.3 The most-reviewed airline is "read more"

Look at the reviews chart. The airline with the most reviews in my whole dataset is "read more", with 6,716 reviews. That's not an airline, that's a button. Somewhere in the scrape the "read more" link got saved as the airline name, and it's sitting in the top 15 airports too. I didn't even spot it first: the agent flagged it while testing v1, before I'd looked at a single chart.

anfra didn't cause that. It just put it on the plate, and honestly that's the best thing a dashboard can do. Garbage in, garbage with a nicer font, except this time the font made the garbage obvious in five seconds. The fix belongs in dbt, not the dashboard.

7. What you get for free

• Interactions. Cross-filter on click, date drill, filters. All declared, none hand-wired.

• Lineage. "Where does this number come from?" traces a metric back to the fields, models and tables behind it. For an AE that's the whole job.

• The Inspect panel. In anfra serve it shows the page as a tree of blocks. You can point the agent at "this chart" instead of describing it in a paragraph.

• Right-click and downloads. Right-click a block for things like the underlying data. Any block or the whole page downloads as PNG or PDF, and a query can export its full result as CSV.

• Typos fail loud. Get a field name wrong in a filter and you get an error with a "did you mean", not a silently empty chart.

8. What I'd change

It's a beta, and they asked for honest feedback, so here's the honest part.

• I can't just send the link. My first question after v1 was "would that html work in GitHub Pages?" No. The page only works under anfra serve, which injects the SDK and runs every query against my local DuckDB. Static hosting gets you an empty page. To share it, you run the server somewhere (there's a Docker image), and the open-source server has no login, so anyone with the URL sees the data. I guess this is how they make money, by letting em host the dashboard for you

• Skills install guessed the wrong agent. anfra skills install put the skills in Codex only, because the claude CLI wasn't on my PATH. --agent claude fixed it, plus a /reload-plugins.

• Lineage can go stale. It reads a catalog, not the AML, so after an AML change you run anfra ingest or it shows the old definitions.

• Quotes inside quotes. table_name: '"airlines_reviews"."fct_airline_review"'. Small thing, but it's the kind of line that breaks at 5pm on a Friday.

9. Thoughts

It does the boring part first

In my Power BI vs Tableau post I said the fix for bad dashboards is boring: define the decision before you touch SQL. The anfra build skill is written for this. Before writing the app, the agent is supposed to restate the request as one result you can check, ask what the business words mean and which date counts, and list every query and filter for you to approve.

In my run it skipped all of that, because I said "try out anfra" and nothing else. Fair. It picked a sensible small app instead of asking me ten questions. But look at what happened next. v1 was pretty and useless. v2 only got good once I said the actual decision out loud: one airline vs the rest. The agent can build fast, but it can't define the decision for you. That part is still your job.

The bigger idea

It's the same thing I keep writing about. If something gets edited, reviewed and shared over and over, it should be text, in git, with a history. The resume, the laptop setup, the ERD, and now the dashboard. The models are files. The datasets are files. The dashboard is one HTML file. v1 to v2 was a ~600-line diff, and I can read every line of it.

And honestly, I think this is where BI is going. Clicking and drag-and-drop made sense when the person building the dashboard couldn't write code. That's not the bottleneck anymore: you say what you want in plain English, the agent writes it as code, and you review the diff like any other PR. I believe drag-and-drop BI will eventually go the way of the hand-built Excel report. Not dead, just not where the trusted numbers live.

The tool is just where the buttons are. The semantic layer is the source of truth.

Try it

• Website: anfra.ai

• Code: github.com/holistics/anfra

• Agent skills: github.com/holistics/anfra-skills

• Demos: demo.anfra.ai

• Holistics: holistics.io

• My setup: github.com/MarkPhamm/skytrax_reviews_data, under anfra/

If you try it and something looks weird, tell the Holistics team. If you enjoyed this, please react or share so I know to keep going. Thanks for reading this far.

Feel free to connect with me on LinkedIn and ask any question

Hi everyone, I’m Minh, an Analytics Engineer at Infinite Lambda, an AI and Data consulting company headquartered in London.

Every data project I have worked on has the same moment. Someone new joins, or a stakeholder asks “wait, how does orders connect to customers again?”, and I go looking for the ERD.

And the ERD is always one of these:

erd.png                  (from 6 months ago, 3 tables missing)
erd_v2.png               (someone's screenshot, half cropped)
schema_FINAL.drawio      (nobody has edit access)
a link to a diagram tool (needs a login, or a paid seat)

Surprisingly “un”professional ¯\_(ツ)_/¯

The funny part is the same thing I complained about in my resume article. We version control SQL, dbt, Terraform, even BI now. But the one picture that explains how the whole warehouse fits together lives in a Slack thread, as a PNG, a painfully slow draw.io diagram and, most of the time, it’s wrong.

So I built qdbd (quickdbd-cli). It’s a CLI that turns a schema written as plain text into an interactive ER diagram, one HTML file you can open anywhere, and it stays readable at 200+ tables. Then I built a VS Code extension on top of it, because I didn’t want to touch a terminal to look at a diagram either.

This post is about why, and how both of them work.

2. The pain point: sharing ERDs is weirdly hard

2.1 I actually like QuickDBD

I want to be clear first: I like QuickDBD. You type something like this:

Customer
-
CustomerId PK int
Email string UNIQUE

Order
-
OrderId PK int
CustomerId int FK >- Customer.CustomerId

and you get a diagram. No dragging boxes, no drawing arrows. Text in, picture out. For an AE, that’s exactly how it should work, because text is something I can put in git.

So why not dbml syntax? Well, I do like dbml, but I believe qdbd syntax is a bit more “simple” and “intuitive” . Also I was introduce to quickdbd before dbml, no naturally I’m a bit more used to using it.

2.2 Then you hit table 11

The free tier caps at 10 tables. From table 11 on, tables get renamed “Table N” and their columns become “...”.

10 tables. My bronze layer alone is more than 10 tables. Insurify’s dbt project was reaching 1500 models when I left. So either I ask em to fix the bug or I screenshot 10 tables at a time and stitch them together like it’s 2009.

2.3 Screenshots go stale the moment you send them

Even with a paid tool, the sharing flow is still: open the tool, export a PNG, drop it in Slack or Confluence.

That PNG is frozen. Add a column next week and the picture is already lying. Nobody updates it, because updating it means opening the tool, finding the diagram, re-exporting, re-uploading. So the ERD in the docs slowly becomes fiction.

2.4 Generated diagrams are unreadable past ~30 tables

The other option is to generate the diagram from the database or the dbt project. That one stays current, which is great, until you open it and it’s 150 boxes and 300 lines on top of each other. Technically correct, practically useless.

So you pick one:

• Hand-drawn: looks good, lives outside the repo, goes stale.

• Generated: stays current, unreadable at real size.

I wanted both. A diagram that lives next to my code, and that I can still read at 200 tables.

3. What qdbd is

3.1 The idea in one line

Schema is a text file in git. qdbd checks it, lays it out, and builds one self-contained HTML page you can search, filter, click through and send to anyone.

No server, no login, no “request access”. It’s a file. Open it in a browser.

3.2 It reads the syntax you already have

• QuickDBD syntax. The parser mirrors QuickDBD’s own, error messages included. If a file passes qdbd check --strict, it pastes back into QuickDBD unchanged. That was the #1 rule when I built it: anything that works in QuickDBD must work here.

• DBML, the syntax from dbdiagram.io. .dbml files work as they are, and qdbd convert turns one syntax into the other.

• A dbt project. More on that in section 5, because that’s the part I use the most.

3.3 Try it without installing anything

curl -O https://raw.githubusercontent.com/MarkPhamm/quickdbd-cli/main/examples/quickdbd-sample.txt
uvx --from quickdbd-cli qdbd serve quickdbd-sample.txt

A browser tab opens with the diagram and an editor next to it. Change the text and the diagram reloads. Drag tables around and the positions get saved.

To actually install it:

pipx install quickdbd-cli   # or pip / uv tool install

Python 3.11+, and that’s it. No Node, no Graphviz, no browser needed. I was pretty stubborn about this one, because “just install these 4 system dependencies first” is how a tool dies on a teammate’s laptop.

4. The CLI

4.1 qdbd build: the file you send

qdbd build erd/ -o erd.html

You can point it at one file or a whole folder. All files share one namespace, so a foreign key in marts.txt can point at a table in staging.txt.

What you get in the page:

• Search and jump to any table

• Click a table to focus it and its neighbours, everything else fades out

• dbt-style selectors like +orders+1, group:marts, tag:fact to filter down to the part you care about

• Shaded regions per file or group, a minimap, and collapsed views (full / keys only / header only)

• A path finder between two tables, with the SQL JOIN written for you

That last one is honestly my favourite. “How do I get from order_items to customers?” Click, click, here’s the join.

4.2 qdbd share: an actual link

qdbd share erd/

It uploads the page to a secret GitHub gist and prints a link. Run it again and it updates the same link, so the URL you pasted in Confluence last month is still the current diagram.

This is literally the thing I wanted at the start. One command, one link, always up to date.

If you’d rather publish from CI, there’s a GitHub Action that deploys the diagram to GitHub Pages on every push.

4.3 qdbd diff: ERDs in pull requests

qdbd diff --base main

It tells you what a branch changed: new tables, changed columns, removed stuff. The GitHub Action posts that as a PR comment, and qdbd build --diff-base main colours the changes right in the diagram: new tables green, changed columns amber.

So reviewing a schema change is not “read 200 lines of YAML and imagine it”. You look at the picture.

4.4 qdbd export: when you really need a PNG

Sometimes you do need an image, for a slide or a doc. qdbd export writes PNG, SVG, or an editable draw.io file, and --focus Table --depth 1 gives you just one table and its neighbours instead of the whole universe.

5. dbt: the part I use every day

5.1 Your dbt project already knows the ERD

If you write dbt tests, you already wrote down your keys:

• unique + not_null on a column, that’s basically a primary key

• a relationships test, that’s a foreign key

So qdbd reads them:

dbt docs generate
qdbd import dbt target/ -o erd/ --infer-fks

You get one .txt file per dbt folder, with PKs from your tests, FKs from your relationships tests, and --infer-fks filling the gaps from column names. Model descriptions show up as tooltips.

5.2 Readable at 150 tables, for real

The live demo I like to show is OHDSI’s dbt-synthea project: 145 tables, 235 relationships, all from tests. It’s in the demo gallery if you want to click around. Without selectors that would be a wall of boxes. With group: and +model+1, it’s the five tables you actually wanted.

6. The VS Code extension

6.1 Why an extension

The CLI is great, but I spend my whole day in VS Code (well, Cursor). Alt-tabbing to a terminal to run qdbd serve every time I want to look at a table got old really fast.

So I built the extension. Search QuickDBD in the VS Code Marketplace, or on Open VSX if you’re on Cursor, Windsurf or VSCodium.

6.2 What you get

• Syntax highlighting for QuickDBD and DBML

• Errors as you type, the same ones the CLI gives you

• Outline and F12 go-to-definition across files

• The diagram beside your editor, following your cursor. Move to a table in the text and the diagram jumps to it

• A sidebar that finds every schema in your workspace, with buttons for export, share, compare with a git ref, convert to and from DBML, and dbt import

6.3 No terminal, I mean it

If you don’t have qdbd installed, the extension installs it for you, into its own Python environment. You don’t need to know what pip is. Install the extension, open a .txt schema, the diagram shows up.

That matters a lot more than it sounds. The person who most needs to read the ERD is often not the person who wants to set up a Python environment.

7. Bonus: AI agents can read the schema too

qdbd mcp exposes the schema to Claude, Copilot and Cursor as tools. The agent can list tables and columns, ask for the join path between two tables and get SQL back, check its own edits to the schema, and diff against main.

The extension registers it automatically in VS Code. So when I ask an agent to write a query, it doesn’t guess the join keys anymore. It asks the schema.

8. Thoughts

8.1 Is this over-engineering?

Probably, idk, you decide - If your project has 8 tables, open QuickDBD and you’re done. You don’t need any of this.

But if you have a dbt project with 100+ models, a team that keeps asking “how do these connect?”, and an ERD in Confluence that was last updated by someone who left the company, then I think a diagram that lives in git and builds itself is worth it.

8.2 The bigger idea

It’s the same thing I keep writing about. If something gets edited, reviewed and shared over and over, it should be text, in git, with a history. The resume, the laptop setup, and now the ERD.

The picture is just a build artifact. The text is the source of truth.

8.3 Try it

• Docs and live demos: qdbd.dev

• PyPI: https://pypi.org/project/quickdbd-cli/

• Code: github.com/MarkPhamm/quickdbd-cli

• Extension: search QuickDBD in VS Code, Cursor or Windsurf

If you try it on your own dbt project and something looks weird, open an issue. Fair warning, I will probably ship the fix the same day, because I have zero chill about this project :”)

Feel free to dm me to learn more about it

But there is one file I have probably edited dozens of times without ever thinking seriously about version control: my resume.

A file where changing three or four lines can directly affect whether I get a recruiter screen, yet the standard workflow still somehow looks like this:

resume.pdf
resume_new.pdf
resume_DA_final.pdf
resume_DE_final_v2.pdf
resume_AE_final_real.pdf

Hi everyone, I’m Minh, an Analytics Engineer at Infinite Lambda, an AI and Data consulting company headquartered in London. Today I want to talk about a slightly controversial, and probably slightly over-engineered topic: version controlling your resume with Git.

Not because your resume needs CI/CD. Not because applying for jobs should become another project that you implement “Software engineering best pracetice”. I just think that if something is continuously iterated, tested, compared, and occasionally rolled back, then version control starts making a lot of sense.

TLDR

The main benefits for me are pretty simple:

• I know exactly what changed between versions and why.

• If an experiment does not work, I can roll it back.

• I can maintain separate AE / DE / DA versions without duplicating a bunch of files.

• Because the source is plain text, I can use Cursor or other AI agents directly inside the IDE.

The biggest benefit, though, is that Git turns resume iteration into a history I can actually inspect.

Treat your resume as a product (product centric mindset)

I think a resume is a product. Recruiter is your audience, your experiences/projects are the features that help the audience decide if they wanna hire you :”)

And if I continuously iterate a product, version controlling it feels pretty natural.

Yes, using LaTeX with pdflatex, Git, and Cursor requires slightly more technical knowledge than opening Word and editing a document. But for me, the one-hour-ish setup cost is completely worth it.

I’m also not going to argue that everyone should use LaTeX instead of Word for their resume. That depends entirely on your needs and preferences. Personally, I use LaTeX because I like the formatting and customization, but the interesting part here is not really LaTeX.

It is the history.

Suppose my current resume has a pretty decent interview rate. One day I decide to rewrite my entire Insurify experience, replace five bullets, remove a project, reorder my skills section, and push business impact much harder.

Then I apply to another 30 or 40 jobs and my interview rate seems worse. Is that statistically significant? Probably not =)))) I just feel like it got worse.

Obviously, I cannot conclude that the new resume caused it. The job market changes, the roles are different, the companies are different, timing is different, and there are a ridiculous number of confounding variables.

But the first question I still want to answer is:

Okay, so what the hell did I change?

If my resume is just a Word document or PDF that I keep overwriting, that question becomes surprisingly difficult to answer a few months later.

With Git, I can just look at my commit history:

Rewrite Insurify bullets around business impact
Add Infinite Lambda experience
Remove older project
Tailor skills toward Analytics Engineering
Update changelog.md

Then I can run git diff between two versions and see exactly what changed, line by line.

That is probably the biggest benefit for me. Git does not make my resume better. It does not magically improve my interview rate either. It makes the process of iterating on my resume inspectable.

Change → experiment → rollback

Let’s say I have a hypothesis:

Recruiters care more about business impact than implementation details.

So I rewrite my resume more aggressively, remove some of the technical details, and push the result to the front.

For example, maybe I change this:

Built an event-driven partner onboarding pipeline using SQS, EventBridge, Kinesis, Airflow, and Redshift.

into this:

Reduced partner onboarding time by ~90%, scaling automated onboarding to 150+ partners supporting $10M+ revenue.

Maybe the second one performs better. Maybe a technical hiring manager actually prefers the first one. Maybe there is literally no meaningful difference at all.

The point is that I am running an experiment.

If I test that version for a while and do not like the result, I do not have to sit there trying to remember, “Wait, how did I write that partner onboarding bullet two months ago?”

The old version is still there.

The mental model is basically the same as software:

change → ship → observe → keep / revert

Obviously, a resume is not a clean A/B testing environment. I do not have a proper control group, sample sizes are usually small, and interview rate is insanely noisy.

I’m not pretending I can attribute a change in response rate to a specific resume edit. But at least I preserve the state of each experiment instead of overwriting the baseline every time I try something new.

Branching - git OP feature

Most people probably do not have exactly one resume.

At different points in my job search, I was applying for Analytics Engineer, Data Engineer, and Data Analyst roles. Those jobs overlap quite a lot, but the emphasis is very different.

My AE resume might push dbt, dimensional modeling, analytics engineering, CI/CD, and data quality much harder. The DE version might emphasize Airflow, AWS, ingestion, streaming, and infrastructure. The DA version might focus more on SQL, reporting, experimentation, stakeholders, and business impact.

The normal workflow is to copy the file:

resume_AE.pdf
resume_DE.pdf
resume_DA.pdf

That works perfectly fine until I add a new job, update education, fix a typo, change one achievement, or realize all three versions need the same experience update.

Then I have to remember to update all three files. A few months later, I no longer know which one is actually ahead of the others.

Git branching solves this pretty cleanly:

main
analytics-engineer
data-engineer
data-analyst

main can hold the common version, while each branch contains role-specific changes. If I want to apply for a DE role, I check out data-engineer, compile the PDF, and use that version.

If I add a new experience that belongs in every resume, Git already has a workflow for bringing that change across branches.

One resume, multiple variants, but the history stays connected.

For me, this is probably where Git goes from “nice to have” to genuinely useful.

LaTeX + Cursor makes the workflow much better

Technically, Word and Google Docs already have version history, so I do not think people should move to LaTeX just because they want rollback.

I prefer LaTeX because the resume becomes plain text source code. Git diff becomes extremely clean, AI agents can read it properly, and the PDF is basically just a build artifact.

My setup is roughly:

MINH_PHAM_RESUME/
├── .claude/
│   ├── context/
│   │   ├── achievements.md
│   │   └── resume-suggestions.md
│   └── skills/
│       └── update-resume/
│           └── SKILL.md
├── .vscode/
│   └── tasks.json
├── jd/
│   └── demo_ae_jd
├── legacy/
│   └── minh_pham_resume.pdf
├── CLAUDE.md
├── minh_pham_resume.aux
├── Minh_Pham_Resume.log
├── minh_pham_resume.out
├── minh_pham_resume.pdf
├── Minh_Pham_Resume.tex
└── README.md

The workflow is basically:

LaTeX source → compile → PDF
Git → track every change
GitHub → remote history
Cursor → edit + agent

And plain text unlocks another thing I have started to like quite a lot: AI can work with the whole resume repository instead of just the final PDF.

I can ask Cursor something like:

Compare my Analytics Engineer and Data Engineer branches. What bullets are different?

Or:

Show me everything I changed in my resume during the last month.

Or:

Rewrite this bullet, but keep the same structure and do not remove the Redshift/dbt implementation detail.

The agent can see the source files, the Git diff, the commit history, and the changelog in the same context.

That is much more useful to me than uploading a PDF every time and re-explaining the entire background from scratch.

Commit messages are surprisingly useful too

One side effect I did not really expect is that Git forces me to be slightly more deliberate about why I am changing something.

Instead of just thinking, “This sentence sounds better,” I can commit something like:

refactor: move business impact before implementation details

or:

test: remove older projects to keep resume to one page

or:

ae: emphasize dbt modeling and data quality work

Yes, this is extremely nerdy =))))

But when I look back a few months later, I know not only what changed, but also why I changed it.

I also keep a very simple changelog.md for bigger experiments:

2026-09-15

- Rewrote Insurify section around business impact
- Removed streaming project
- Added Infinite Lambda experience
- AE version only

Is this necessary? Definitely not.

But if you are actively recruiting and changing your resume every week, I think having some record of the larger experiments is actually pretty useful.

Is this over-engineering?

Probably, idk, you decide. But is it worth it? Definitely yes!

If you update your resume twice a year, Word or Google Docs is completely fine. There is very little reason to introduce Git just for the sake of saying your resume is version controlled.

But if you are actively recruiting, maintaining multiple role variants, tailoring your resume often, and already know Git anyway, the incremental cost is basically zero.

What I find funny is that people in Data and Engineering are completely comfortable with the idea that:

“Code should be version controlled.”

We version control SQL. We version control dbt models. We version control infrastructure. We are increasingly version controlling BI as well.

Then the document that might decide whether we even get into the interview process is sitting in a folder called:

resume_final_final_v7_REAL.pdf

Thoughts

The interesting part here is not really LaTeX. LaTeX is just the implementation I happen to prefer.

The bigger idea is that I do not think a resume should be treated as a static file that I open every few months, change a few lines in, and forget again. It is an artifact I continuously iterate based on feedback from the market.

Once I look at it that way, a lot of software engineering practices start making sense. Version control tells me what changed. Commit messages document why I changed it. Branches let me maintain multiple variants. Rollback preserves the baseline when an experiment does not work. And AI agents can use the history to help me iterate faster.

I am still not at the point where I open a pull request and self-approve my own resume.

But Git? I think that part is completely worth it.

If data teams already version control transformations, infrastructure, and increasingly the BI layer, then the file that decides whether I get an interview probably deserves a git log too.

This is the last part. In part 1 I set up a Neon Postgres source, a Snowflake trial, and a Fivetran connection reading Postgres’ write-ahead log. In part 2 I let Fivetran clone my GitHub repo and run the dbt project on a schedule, with the daily job triggered by the ingestion sync finishing.

Everything is built. So this part is the exercise that proves it: edit a few rows in Neon and confirm they reach FIVETRAN_DEMO.SERVE without touching Fivetran, Snowflake, or dbt.

They do reach it. Five rows changed in Postgres, six rows moved through the sync, the dbt job fired on its own, and the reporting tables rebuilt.

And two of the numbers came out wrong.

Not wrong because the pipeline broke. Wrong because a Fivetran landing zone is not the same thing as a copy of your source table, and every model in this project quietly assumes it is. This is the part I would have gotten wrong in production, so it is the part I most wanted to write down.

Takeaway

• What an incremental CDC sync actually looks like: six rows instead of 2,747

• The two columns Fivetran adds to your tables, _FIVETRAN_SYNCED and _FIVETRAN_DELETED, and what they mean

• Why a single UPDATE arrives as two rows when your source table has no primary key

• Why soft deletes silently keep a deleted order line in your revenue total

• The one-line filter every staging model on a Fivetran landing zone should have, and why this project deliberately leaves it out

• Why “the row is still there” and “the row is still current” are different questions

Prerequisite

Parts 1 and 2, finished and working. Specifically: the Fivetran connection syncs l1_landing into FIVETRAN_DEMO, and the Daily-after-landing dbt job builds TRANSFORM and SERVE.

Check the Connections column on the Transformations list before you start. If Daily-after-landing says None, the connection ID in deployment.yml is wrong, the integrated schedule never resolved, and the interesting half of this test will not happen on its own.

Three scripts do all the work:

What is and is not incremental

Worth being precise about this before we start, because “the pipeline is incremental” is only half true.

The ingestion half is incremental. Postgres writes your UPDATE, INSERT, and DELETE to the WAL. Fivetran reads the change stream through replication_slot_01 and ships only those rows. It never re-reads the 1,651 rows in orderitems.

The transformation half is not. Every model in this project is materialized='table', so the dbt job drops and rebuilds all of them from scratch. Five models, a few thousand rows, a couple of seconds. At this size, incremental models would be complexity with no payoff.

That split is normal and it is usually the right one. Incremental ingestion is where the savings are, because that is the part that scans a production database. Incremental transformation is where the bugs are.

Step 1: Take a baseline

Run section 1 of cdc_verify.sql in Snowsight before you change anything:

SELECT CUSTOMERID, CUSTOMERNAME, ORDERCOUNT, REVENUE
FROM FIVETRAN_DEMO.SERVE.CUSTOMERREVENUE
WHERE CUSTOMERID = '28222';

Customer 28222 owns exactly one order, 800143, which keeps the arithmetic easy to check by hand.

Also write down the revenue for customer 27613, who is unrelated:

SELECT CUSTOMERID, REVENUE
FROM FIVETRAN_DEMO.SERVE.CUSTOMERREVENUE
WHERE CUSTOMERID = '27613';

27613 owns order 800000, and we are going to delete one 300.00 line from it. Keeping the update and the delete on different customers means we can track them separately when things get weird.

Step 2: Change the source

Run cdc_test.sql in the Neon SQL Editor, on fivetran_source, not neondb.

Nothing in that file is special. There is no Fivetran API call, no trigger, no CDC-aware syntax. It is three ordinary DML statements, which is exactly the point of log-based CDC: the source application does not know or care that a pipeline is watching.

An update, so a change is visible at the far end. CustomerName in SERVE.CUSTOMERREVENUE is built from first and last name, so a rename travels the full length of the pipeline:

UPDATE l1_landing.customers
SET lastname = 'Perez-CDC',
    updated_at = now()
WHERE customerid = '28222';

An insert, a new order for the same customer with two lines worth 115.00 total:

INSERT INTO l1_landing.orders (orderid, orderdate, customerid, employeeid, storeid, status, updated_at)
VALUES (900001, CURRENT_DATE, '28222', 507279, '1000', '02', now());

INSERT INTO l1_landing.orderitems (orderid, orderitemid, productid, quantity, unitprice, updated_at)
VALUES (900001, 1, 74900, 2, 40.00, now()),
       (900001, 2, 70518, 1, 35.00, now());

So 28222 should move from 1 order at 1509.00 to 2 orders at 1624.00.

Two details in that insert are deliberate. storeid is 1000, and orders_stg maps store 1000 to 'Online' while every seed store uses a different id, so this becomes the only Online row in the entire warehouse and is trivially easy to find. employeeid 507279 is a real employee, so the weekly model can still join it.

A delete, one line off the unrelated order:

DELETE FROM l1_landing.orderitems
WHERE orderid = 800000
  AND orderitemid = 4;

That line is worth 300.00. Naively, revenue for customer 27613 should drop by 300. Hold that thought.

The result tabs along the bottom are the per-statement receipts: UPDATE 1, INSERT 1, INSERT 2, DELETE 1, then the three source-side checks. Five rows changed, and Postgres has written every one of them to the WAL.

Step 3: Sync, and then do nothing

Open the Postgres connection in Fivetran and click Sync now.

Use Sync now, not Re-sync. Re-sync throws away the slot bookmark and reloads all 2,747 rows, which defeats the entire experiment. You can also just skip this click: the connection syncs on its own schedule, six hours by default, under Setup → Sync frequency. I click it because I am impatient, not because it is required.

Read that sync detail before you go anywhere near Snowflake, because it is the whole argument for logical replication in one screenshot.

Three tables. Six rows extracted, six loaded. About twenty seconds.

Compare it to the historical sync in part 1: eight tables, 2,747 rows. Fivetran did not re-read orderitems. It did not even look at products, dates, suppliers, or stores, because nothing in those tables generated a WAL event. It read the change stream through replication_slot_01, found events for three tables, and shipped exactly those rows.

Now the breakdown, which is the first thing that does not add up:

I changed five rows. Six moved. One UPDATE produced two rows. Park that too; it gets explained in a moment.

Then do nothing. Daily-after-landing uses the integrated schedule from part 2, so Fivetran queues the dbt job the instant the sync finishes. No cron waiting, no manual trigger, no orchestrator. That is the payoff for putting the connection ID in deployment.yml.

If it never fires, that ID is wrong.

Step 4: Read the landing tables

Section 2 of cdc_verify.sql. This is where the Fivetran-generated columns start earning their keep.

The insert

SELECT ORDERID, CUSTOMERID, EMPLOYEEID, STOREID, STATUS
FROM FIVETRAN_DEMO.POSTGRES_DEMO_L1_LANDING.ORDERS
WHERE ORDERID = 900001;

One row, exactly as expected. Inserts are boring, which is the correct behavior for inserts.

The delete

SELECT ORDERITEMID, QUANTITY, UNITPRICE, _FIVETRAN_DELETED
FROM FIVETRAN_DEMO.POSTGRES_DEMO_L1_LANDING.ORDERITEMS
WHERE ORDERID = 800000
ORDER BY ORDERITEMID;

Order item 4 is still there, flagged _FIVETRAN_DELETED = TRUE.

This is Fivetran’s default soft delete mode, and once you see it, it makes sense. Deleting rows from the warehouse destroys history: you could never answer “how many order lines did we have last Tuesday,” and a bad delete on the source would be unrecoverable downstream. Flagging preserves everything.

It also means your row count no longer matches the source, and any model that does not know about the flag is now summing a line that no longer exists.

The update, which is the interesting one

SELECT CUSTOMERID, LASTNAME, _FIVETRAN_DELETED, _FIVETRAN_SYNCED
FROM FIVETRAN_DEMO.POSTGRES_DEMO_L1_LANDING.CUSTOMERS
WHERE CUSTOMERID = '28222'
ORDER BY _FIVETRAN_DELETED;

Customer 28222 comes back twice, and the table now holds 101 rows for 100 customers:

Both rows carry the same _FIVETRAN_SYNCED, because one sync wrote both of them.

Here is why, and it goes all the way back to a detail from part 1.

These tables have no primary key. So Fivetran cannot key rows on customerid; nobody told it that column is unique. Instead it generates _FIVETRAN_ID, a hash of every column value in the row.

Now change lastname. The hash changes. From Fivetran’s point of view this is not the same row with a new value, it is a row it has never seen, and the row it used to have is gone. In soft delete mode the only correct response is: insert the new version, flag the old one.

That is why one UPDATE reported two rows in the sync detail.

This is also, finally, why replication.sql set REPLICA IDENTITY FULL back in part 1. Without it the WAL would not carry the complete old row, and Fivetran could not work out which version to retire. Every piece of that setup was for this moment.

Give the source table a primary key and none of this happens. You get one updated row, like you expected. The chain is: no primary key → hash-based identity → updates become insert-plus-soft-delete → your dimension table has multiple versions of every row that has ever changed.

Step 5: Confirm in SERVE

Section 3 of cdc_verify.sql, after the dbt job has run itself.

SELECT CUSTOMERID, CUSTOMERNAME, ORDERCOUNT, REVENUE
FROM FIVETRAN_DEMO.SERVE.CUSTOMERREVENUE
WHERE CUSTOMERID = '28222';

Both changes travelled. The baseline was 1 order at 1509.00, and it is now 2 orders at 1624.00, so the insert made it through the sync, through the staging models, through orders_fact, and into the mart. The rename is there too. Five rows changed in Postgres, and with one click on Sync everything downstream updated itself.

That is the thing working.

And the customer is in the report twice.

The new order is easy to find, since it is the only Online row in the warehouse:

SELECT ORDERID, CUSTOMERID, ORDER_CHANNEL
FROM FIVETRAN_DEMO.TRANSFORM.ORDERS_STG
WHERE ORDER_CHANNEL = 'Online';

Then check 27613, the customer whose 300.00 order line we deleted:

SELECT CUSTOMERID, REVENUE
FROM FIVETRAN_DEMO.SERVE.CUSTOMERREVENUE
WHERE CUSTOMERID = '27613';

Unchanged. The deleted line is still in the revenue total.

Why both numbers are wrong

Two surprises, one cause: no model in this project reads _FIVETRAN_DELETED.

The double-counted customer

customers_stg is this:

SELECT
    CustomerID,
    ...
    CONCAT(FirstName, ' ', LastName) AS CustomerName
FROM
    {{ source('landing', 'customers') }}

No filter. So it carries both versions of customer 28222 through to the mart, the current Perez-CDC row and the retired Perez row.

customerrevenue then joins that to orders_fact on CustomerID:

FROM {{ ref('orders_fact') }} OS
JOIN {{ ref('customers_stg') }} C ON OS.CustomerID = C.CustomerID
GROUP BY OS.CustomerID, C.CustomerName

Two customer rows share CustomerID = '28222', so the join fans out: every order matches both versions. Grouping by CustomerName then splits the result into two rows, each showing the full 1624.00.

Total revenue in this table is now overstated by 1624.00, and a dashboard built on it shows one person twice under two slightly different names. Nothing errored. Nothing looked wrong in Fivetran. The job said Succeeded.

The delete that did not reduce revenue

orderitems_stg has the same shape and the same gap:

SELECT
    ...
    Quantity * UnitPrice AS TotalPrice
FROM
    {{ source('landing', 'orderitems') }}

Order item 4 is still in the landing table with _FIVETRAN_DELETED = TRUE. The model does not look at that column, so it sums 300.00 that no longer exists in the source.

The fix

One line in each staging model:

WHERE COALESCE(_FIVETRAN_DELETED, FALSE) = FALSE

COALESCE because the column is NULL on rows that were never touched by a delete, and NULL = FALSE is not TRUE in SQL. Get that wrong and you filter out your entire table, which is a fun ten minutes.

cdc_verify.sql shows what the models would have seen:

SELECT COUNT(*) AS current_customer_rows
FROM FIVETRAN_DEMO.POSTGRES_DEMO_L1_LANDING.CUSTOMERS
WHERE COALESCE(_FIVETRAN_DELETED, FALSE) = FALSE;

100. Exactly right.

The alternative is switching the Fivetran connection to hard deletes, which drops rows instead of flagging them. That makes the landing tables match the source and the models correct as written, at the cost of throwing away the history that made soft deletes attractive in the first place.

Why this project leaves the bug in

The models come from upstream unchanged, and I decided to keep them that way, because this is a more useful thing to see than a working dashboard.

The lesson generalizes past Fivetran. A landing zone is not a copy of your source table. It is an append-mostly log of every version of every row your source has ever had. On that kind of table, “the row is still there” and “the row is still current” are different questions, and only _FIVETRAN_DELETED answers the second one.

Every staging model reading a Fivetran landing zone needs to answer the second question. Every one of these does not. Neither model is broken in a way that fails a test or throws an error; they are broken in the way that shows up as a number somebody trusts.

Step 6: Clean up

Run cdc_revert.sql in Neon and sync again. It undoes all three changes.

Notice that it restores exact seed values, updated_at included:

UPDATE l1_landing.customers
SET lastname = 'Perez',
    updated_at = '2025-06-14 18:29:55'
WHERE customerid = '28222';

Not now(). That timestamp is deliberate, and it follows directly from everything above. Fivetran keys these rows on a hash of all their values, so restoring the row to byte-identical seed values makes it hash back to the _FIVETRAN_ID of the row that was soft-deleted earlier, and Fivetran can revive that row instead of appending a third version. Use now() and you get customer 28222 three times.

That is a nice demonstration of hash-based identity, and also a warning: on a table with no primary key, an accidental round-trip edit is indistinguishable from no edit at all.

Even so, SERVE does not return to the exact baseline. The landing tables keep every version they retired during the test, so customerrevenue keeps double-counting. For a genuinely clean copy, re-sync just the customers and orderitems tables from the connection’s schema tab, which drops and reloads those two, then rerun the dbt job.

If nothing changed at all

SymptomUsual causeNothing moved in SnowflakeYou ran the edits against neondb instead of fivetran_sourceLanding queries return nothingYour landing schema is not called POSTGRES_DEMO_L1_LANDINGThe dbt job never firedWrong connection ID in deployment.yml; Connections column says NoneFivetran reports a slot errorRe-check replication_slot_01 with the queries at the bottom of replication.sql

What I took away from the whole series

Three parts, no Python, no Docker, no Terraform, no orchestrator. A Postgres source streaming its write-ahead log, a warehouse with a least-privilege service identity, a dbt project run from Git by the same vendor doing the ingestion, and a change in the source reaching a reporting table with one click and then rebuilding itself.

Against the version I built with Airflow, S3, and Terraform, I wrote something like two percent as much code. But the work did not disappear, it moved, and that is the actual lesson:

What I stopped having to know: how to schedule a DAG, how to write an IAM trust policy, how sts:AssumeRole works, how to make a scraper idempotent, how to partition a landing directory by date.

What I had to learn instead: what a replication slot is and why an abandoned one eats your disk, why replica identity exists, how Snowflake’s role hierarchy separates SECURITYADMIN from SYSADMIN, why service users use key pairs, and, most of all, what a CDC landing zone actually contains.

The second list is more transferable. IAM trust policies are an AWS fact. “Your warehouse table holds every version of every row and you have to filter for the current one” is true of Fivetran, and Airbyte, and Debezium, and anything else that reads a change log.

The managed stack did not make the pipeline simpler. It made the plumbing disappear and left me alone with the data modelling, which turned out to be where the only real bug was.

If you enjoyed this series, please react or share so I know to write more of them. Thanks for reading all three parts.

The repo, with every script and screenshot: MarkPhamm/fivetran_snowflake_dbt_demo.

• Part 1 - Ingestion: Neon, Snowflake, Fivetran

• Part 2 - dbt, run by Fivetran

• Part 3 - CDC (you are here)

Hi everyone, my name is Mark, an Analytics Engineer at Infinite Lambda, graduated from TCU in the summer of 2025, majoring in Computer System Analyst with minors in Math and Fintech.

I got a new M5 pro this week, and the moment I opened the lid I felt the specific dread of doing this again. Install the Command Line Tools. Wait. Install Homebrew. Wait. Try to remember whether I use eza or exa now. Find the Oh My Zsh install line. Google the zsh-autosuggestions clone URL for what must be the ninth time in my life. Open my old laptop, scroll through its .zshrc, and copy chunks across while hoping I do not also copy the three aliases that only worked because of a Docker container ID that no longer exists.

I have done that four times: personal laptop, school laptop, first work laptop, and now this one. Every time it takes the better part of a day, and every time the result is slightly different from the last one. That is the part that actually bothers me. Not the hours, the drift. I spend my working life telling people that if a transformation is not in version control it does not really exist, and then I configure the machine that runs those transformations by hand, from memory, with no record of what I did.

I also wrote about the productivity tools I used as an AE here.

So this time I wrote it down as code. The repo is mac_setup. It is not clever. There is no Ansible, no Nix, no 800-line install framework. It is a Brewfile, two zsh files, two JSON files for Cursor, and four bash scripts, and it takes a fresh Mac to a working machine in about twenty minutes, most of which is Homebrew downloading things while I make coffee.

Takeaway

• Why “my dotfiles are in a gist” is not the same thing as a reproducible machine

• What actually belongs in a setup repo and what does not (spoiler: your employer’s file paths do not)

• The four-script structure: bootstrap, link, configure, verify, and why verify matters more than it sounds

• How a Brewfile replaces the entire “install these 25 things” section of an onboarding doc

• The one trick I added this round: every git repo gets its own terminal background colour

The problem with how I used to do it

My old approach was a Google Doc. It had a list of apps, a few terminal commands, and a copy of my .zshrc pasted in at the bottom. It was genuinely better than nothing.

It also rots. A doc cannot tell you that Terraform left Homebrew core after the license change and now lives in hashicorp/tap. It cannot tell you that the docker_start_mwaa alias you have been carrying since 2024 refers to a container that stopped existing when you changed jobs. It cannot tell you that you wrote down Powerlevel10k but have actually been running the Jonathan theme this whole time, which I only discovered when I diffed the doc against the real file. A doc records intentions. A repo records the machine.

The other problem is that a doc cannot be run. Every line in it is a thing you have to read, understand, and retype at the exact moment you are least patient, which is hour three of setting up a laptop you just want to start working on.

What it installs

Everything lives in one Brewfile, which is Homebrew’s manifest format. One file, one command, whole machine.

The core: Git, GitHub CLI, uv for Python, Node, AWS CLI, and Terraform from the HashiCorp tap.

The terminal layer, which is the part I would miss most if it disappeared: Warp as the terminal, Oh My Zsh on top of zsh, zoxide, eza, fzf, ripgrep, bat, jq, tree, and lazygit.

The apps: Cursor, OrbStack instead of Docker Desktop, Raycast instead of Alfred, Rectangle, 1Password, DBeaver instead of SQLWorkbench, and Obsidian.

And the Python and SQL tooling that I want available everywhere rather than per-project: Black and SQLFluff, installed with uv tool install so they live in their own environments and never fight with a project’s dependencies.

A few of those are deliberate replacements for what my older setup notes recommended. DBeaver instead of SQLWorkbench, because SQLWorkbench wants you to manage a Java install and DBeaver ships its own runtime. OrbStack instead of Docker Desktop, because it starts in about two seconds and does not eat the battery. Raycast instead of Alfred. Cursor instead of whatever editor the doc said in 2023. I wrote those decisions down in docs/MIGRATION_NOTES.md rather than silently swapping them, because the next person to read the repo is me in two years, and I will want to know why.

How it runs

Four scripts, in order. There is a Makefile too, so make all runs the whole chain, but I usually go one at a time on a new machine so I can watch what happens.

Step 0 is the one thing that cannot be automated. Apple’s Command Line Tools ship a GUI installer and there is no honest way around it:

xcode-select --install

Then clone the repo:

git clone https://github.com/MarkPhamm/mac_setup ~/mac_setup
cd ~/mac_setup

Step 1: ./scripts/bootstrap.sh. This is the long one, and the only one that touches the network much. It checks that the Command Line Tools are actually there and stops with a useful message if they are not, installs Homebrew if it is missing, runs brew bundle against the Brewfile, installs Oh My Zsh, clones the two zsh plugins that are not Homebrew packages, installs Black and SQLFluff via uv tool, and installs the Codex CLI from npm. Every step is guarded by an existence check, so running it twice is safe and fast. That matters more than it sounds, because the first run of a setup script almost never is the only run.

#!/usr/bin/env bash
set -euo pipefail

REPO_ROOT="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"

echo "==> mac_setup bootstrap"

if ! xcode-select -p >/dev/null 2>&1; then
  echo
  echo "Apple Command Line Tools are not installed yet."
  echo "Run:"
  echo "  xcode-select --install"
  echo
  echo "Finish the macOS installer, then run this script again."
  exit 1
fi

echo "==> Apple Command Line Tools found: $(xcode-select -p)"

if ! command -v brew >/dev/null 2>&1; then
  echo "==> Installing Homebrew"
  /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
fi

# Homebrew's standard Apple Silicon location.
if [[ -x /opt/homebrew/bin/brew ]]; then
  eval "$(/opt/homebrew/bin/brew shellenv)"
fi

echo "==> Homebrew: $(brew --version | head -n 1)"

echo "==> Installing Brewfile packages"
brew bundle --file="$REPO_ROOT/Brewfile"

if [[ ! -d "$HOME/.oh-my-zsh" ]]; then
  echo "==> Installing Oh My Zsh"
  RUNZSH=no CHSH=no KEEP_ZSHRC=yes \
    sh -c "$(curl -fsSL https://raw.githubusercontent.com/ohmyzsh/ohmyzsh/master/tools/install.sh)"
else
  echo "==> Oh My Zsh already installed"
fi

ZSH_CUSTOM_DIR="${ZSH_CUSTOM:-$HOME/.oh-my-zsh/custom}"

if [[ ! -d "$ZSH_CUSTOM_DIR/plugins/zsh-autosuggestions" ]]; then
  echo "==> Installing zsh-autosuggestions"
  git clone https://github.com/zsh-users/zsh-autosuggestions \
    "$ZSH_CUSTOM_DIR/plugins/zsh-autosuggestions"
fi

if [[ ! -d "$ZSH_CUSTOM_DIR/plugins/zsh-syntax-highlighting" ]]; then
  echo "==> Installing zsh-syntax-highlighting"
  git clone https://github.com/zsh-users/zsh-syntax-highlighting.git \
    "$ZSH_CUSTOM_DIR/plugins/zsh-syntax-highlighting"
fi

echo "==> Installing global formatter/linter tools with uv"
uv tool install --upgrade black || uv tool upgrade black
uv tool install --upgrade sqlfluff || uv tool upgrade sqlfluff

if ! command -v codex >/dev/null 2>&1; then
  echo "==> Installing Codex CLI"
  npm install -g @openai/codex
else
  echo "==> Codex CLI already installed"
fi

# Ensure uv tool executables are discoverable.
uv tool update-shell || true

echo
echo "Bootstrap complete."
echo "Next:"
echo "  ./scripts/link-dotfiles.sh"
echo "  ./scripts/configure-git.sh"
echo "  exec zsh"
echo "  gh auth login"

Step 2: ./scripts/link-dotfiles.sh. This symlinks ~/.zshrc and ~/.zsh_aliases to the copies in the repo. Symlinks, not copies, and that is the whole point: when I improve an alias six months from now, I edit it in the repo, and the change is version controlled by construction. There is nothing to remember to sync back. Anything already sitting at those paths gets moved to a timestamped .backup first rather than clobbered.

It also touches an empty ~/.zshrc.local, which is where every secret, API key, employer-specific path, and machine-specific export goes. That file is never committed. The rule I follow is simple: if it would be wrong for a stranger to read it, or if it is only true on this one laptop, it goes in .zshrc.local. Everything else goes in the repo. My old .zshrc failed that test badly, which is exactly how a company file path ends up in a public gist.

Step 3: ./scripts/configure-git.sh. Small script. Sets up git lg, the pretty graph log, and prints my current git identity so I remember to set it. Then gh auth login and GitHub is wired up.

Step 4: ./scripts/verify.sh. This is the one I would tell you to steal if you steal nothing else. It loops through every command the setup is supposed to provide, prints OK or MISSING for each, checks whether ~/.zshrc is actually a symlink and where it points, and checks that each GUI app exists in /Applications.

It sounds trivial, and it is trivial. It is also the difference between “I think the setup worked” and knowing. The failure mode of a setup script is not a loud crash, it is one package quietly not installing, which you discover three weeks later in the middle of something else. Twenty lines of bash turns that into a table you read once.

The one new thing I added

Every repo now tints my terminal background its own colour.

I work across several repos in a normal day, usually with several Warp tabs open, and I have absolutely run a command in the wrong one. The fix is fifteen lines in .zshrc: hash the git repo root, turn the first three bytes of that hash into an RGB value clamped to a dark range so text stays readable, and set the background with an OSC 11 escape sequence. Outside a repo it resets.

_project_bg() {
  local git_root
  git_root=$(git rev-parse --show-toplevel 2>/dev/null) || {
    printf '\e]111\e\\'
    return
  }
  local hash=$(printf '%s' "$git_root" | md5 -q)
  local r=$(( 16#${hash:0:2} % 71 + 30 ))
  local g=$(( 16#${hash:2:2} % 71 + 30 ))
  local b=$(( 16#${hash:4:2} % 71 + 30 ))
  printf '\e]11;#%02x%02x%02x\e\\' $r $g $b
}

autoload -Uz add-zsh-hook
add-zsh-hook chpwd _project_bg
_project_bg

Because the colour comes from a hash of the path, it is deterministic. The dbt repo is the same slightly-green black on every machine I clone it to, forever, and I recognise a tab before I read the prompt. It hooks into chpwd, so it fires on every directory change. Two notes if you copy it: your terminal has to support OSC 11, which Warp, iTerm2, WezTerm, and Kitty do and Apple’s Terminal does not, and md5 is macOS, so on Linux you want md5sum.

This is the kind of thing I would never have bothered to set up when configuration was a manual chore, because it would have evaporated at the next laptop. When the config is a repo, a fifteen-line quality-of-life hack is permanent, and the calculation of what is worth doing changes completely.

What I deliberately left out

The base setup is generic on purpose. No dbt adapters, no Airflow, no Spark, no Kubernetes CLI. Those are all things I use, and none of them are in the Brewfile.

The reason is that the warehouse adapter is a project decision, not a machine decision. The day I put dbt-snowflake in the base setup is the day the repo stops being about my Mac and starts being about one job. I keep a docs/NEXT_LAYER.md listing what the data-platform layer would contain when a project needs it. A list of candidates is cheap. An install you have to un-choose later is not.

Same logic for anything employer-shaped. The dbt aliases in the repo default to a state directory of prod_dbt_artifacts but read $DBT_STATE_DIR, so a project overrides it in .zshrc.local. The alias that downloads a production manifest reads $DBT_MANIFEST_URL instead of a hardcoded company link. The alias is portable; the URL is yours.

Was it worth it

The repo took an evening to write with Claude. The setup it replaces took a day, and I did it four times.

But the hours are not really the argument, because I would have spent that evening fiddling with dotfiles regardless, and everyone who writes a setup repo knows it does not pay for itself as fast as they claim. The argument is that my machine is now a thing I can read, diff, and roll back. When I change a tool, there is a commit explaining it. When something breaks, verify.sh tells me what. When I get the next laptop, or a contractor asks how I have my environment set up, the answer is a URL instead of an afternoon.

Which is, word for word, the argument I make about data pipelines all day. It felt overdue to apply it to the laptop the pipelines are written on.

The repo is here. Fork it, rip out my apps, put yours in. The Brewfile is the only file most people need to change.

Hi again everyone, my name is Mark, an Analytics Engineer at Infinite Lambda, graduated from TCU in the summer of 2025, majoring in Computer System Analyst with minors in Math and Fintech. In this series, I’m rebuilding my ingestion pipeline with zero infrastructure.

If you missed part 1 - ingestion, here’s the short version:

I have an OMS sample database on Neon Postgres, a Snowflake trial, and a Fivetran connection streaming eight tables between them using logical replication. Snowflake currently holds an exact, uncleaned copy of the source in a schema called POSTGRES_DEMO_L1_LANDING.

That’s the EL in ELT. Now we do the T in ELT.

The part I did not expect when I started this project: I never install dbt to run this pipeline. Fivetran Transformations for dbt Core clones my GitHub repo over SSH, generates its own profiles.yml from the Snowflake destination credentials I already entered, and runs the jobs I define in a YAML file at the repo root. There is no dbt Cloud account. There is no Airflow. There is no VM.

My last project needed Astronomer and Docker to get a scheduler.

This one needs a deploy key and a 27-line YAML file.

This part covers the Snowflake schemas dbt writes into, the GitHub deploy key setup, connecting the project in Fivetran, and the deployment.yml that defines the jobs. At the end there is an optional section on running dbt locally, which you only need if you want to test model changes before pushing them.

Takeaway

• How Fivetran Transformations for dbt Core works, and how it differs from dbt Cloud

• Why the identity that runs your models is the destination user, and what breaks when you forget that

• Deploy keys: giving a service read-only access to one repository instead of handing it your GitHub account

• Defining jobs in deployment.yml, including a schedule that fires when an ingestion sync finishes rather than on a clock

• dbt tags and selectors, and why +tag:daily builds five models instead of one

• The two failure modes you will hit, and how to tell a permission problem from a naming problem

Prerequisite

Finish part 1. Every model here reads the landing schema, so if the initial sync has not run, all of them fail on a missing table. You should also have some previous exposure to dbt, if not, checkout my dbt fundamental repo that run dbt on a local duckdb instance here .

You will also need three things carried over from part 1:

1. The landing schema name exactly as SHOW SCHEMAS IN DATABASE FIVETRAN_DEMO reported it. Mine is POSTGRES_DEMO_L1_LANDING.

2. The Postgres connection ID, the two-word system name Fivetran gave the connection. Mine is verdure_rephrase. Not the display name.

3. Admin rights on a GitHub repository, because you have to add a deploy key. Fork the repo or push your own copy.

Overview

What dbt actually is here

dbt turns SQL files into tables. As I’ve mentioned before, I do expect some knowledge about dbt before this article so I won’t dig too deep into the dbt part. You write SELECT statements, reference other models with ref() and raw sources with source(), and dbt figures out the dependency order, runs everything against your warehouse in that order, and can test the results.

That is the whole idea. It does not extract, it does not load, it does not move data between systems. It sends SQL to Snowflake and Snowflake does the work.

The shape of the transformation layer

FIVETRAN_DEMO.POSTGRES_DEMO_L1_LANDING   Fivetran-synced OMS tables (part 1)
        |
        v
FIVETRAN_DEMO.TRANSFORM      staging models + orders_fact
        |
        v
FIVETRAN_DEMO.SERVE          customerrevenue, emp_weekly_sales

Three layers, seven models. The models come from sleekdata/oms_fivetran_transforms and are deliberately simple, because the interesting part of this article is the orchestration, not the SQL.

The staging layer is thin on purpose: rename, cast, add one derived column, nothing else. customers_stg is the entire pattern:

SELECT
    CustomerID,
    FirstName,
    LastName,
    ...
    CONCAT(FirstName, ' ', LastName) AS CustomerName
FROM
    {{ source('landing', 'customers') }}

That source() call is what points at the Fivetran replica. Look closely at it, because it is where part 3 goes wrong.

What Fivetran Transformations is

This is the piece I want to be precise about, because the naming is confusing. There’re 3 main things we need to differentiate:

1. dbt Core is the free open-source CLI.

2. dbt Cloud is dbt Labs’ paid hosted product with a scheduler, an IDE, and a UI. Fivetran

3. Transformations for dbt Core is a third thing: Fivetran runs dbt Core for you, on their infrastructure, from your Git repo.

Concretely, on every project sync Fivetran:

1. Clones your repository over SSH using a deploy key you install

2. Generates a profiles.yml from the Snowflake destination credentials you already entered in part 1

3. Reads deployment.yml from your repo root and creates or updates the jobs it finds

4. Runs those jobs on their schedules, capturing the dbt output into a run log

Point 2 has a consequence that causes the most common failure in this article, so I will state it twice, once here and once when it matters:

Your scheduled models are built by FIVETRAN_USER and FIVETRAN_ROLE, not by a dbt user. Fivetran reuses the destination identity. Your local profiles.yml is never read by Fivetran and is gitignored.

Point 3 means your schedules are version controlled next to your models, which I like a lot more than clicking around a scheduler UI.

Step 1: Create the dbt schemas in Snowflake

First confirm part 1 actually finished:

SELECT COUNT(*) FROM FIVETRAN_DEMO.POSTGRES_DEMO_L1_LANDING.CUSTOMERS;

100 rows, or stop and go fix part 1.

Now run snowflake/sql/dbt_setup.sql in Snowsight, selecting the whole script. It creates:

• Schemas TRANSFORM and SERVE inside FIVETRAN_DEMO

• DBT_ROLE and DBT_USER, for optional local development

• Read grants across the whole database, including future schemas and tables

• Write grants on TRANSFORM and SERVE for both DBT_ROLE and FIVETRAN_ROLE

Three things in that script deserve attention.

The future grants

GRANT USAGE ON FUTURE SCHEMAS IN DATABASE FIVETRAN_DEMO TO ROLE DBT_ROLE;
GRANT SELECT ON FUTURE TABLES IN DATABASE FIVETRAN_DEMO TO ROLE DBT_ROLE;

Fivetran creates schemas and tables on its own schedule, after this script runs. Without future grants you would have to re-grant every time a connector added a table. This is a small thing that saves real annoyance.

The FIVETRAN_ROLE grants on TRANSFORM and SERVE

GRANT USAGE ON SCHEMA TRANSFORM TO ROLE FIVETRAN_ROLE;
GRANT CREATE TABLE, CREATE VIEW ON SCHEMA TRANSFORM TO ROLE FIVETRAN_ROLE;

Here is the second telling. FIVETRAN_ROLE was created in part 1 to land raw data. It has no reason to write into your transformation schemas, and it would not occur to you to grant it, right up until your first dbt job dies on:

Object does not exist or not authorized

Because Fivetran generates its profile from the destination credentials, the scheduled dbt run authenticates as FIVETRAN_USER. If that role cannot create tables in TRANSFORM, nothing builds. This is the number one reason a first Fivetran dbt job fails, and the error message points at an object rather than at the actual cause.

Ownership

Snowflake only lets the owning role replace a table, and whichever identity builds a model first owns it.

dbt_setup.sql grants DBT_ROLE to FIVETRAN_ROLE, so if you build models locally first, Fivetran inherits and can take them over. The reverse is not automatic. If Fivetran built them first and you now want to run dbt locally, hand the objects over once:

USE ROLE ACCOUNTADMIN;
GRANT OWNERSHIP ON ALL TABLES IN SCHEMA FIVETRAN_DEMO.TRANSFORM TO ROLE DBT_ROLE COPY CURRENT GRANTS;
GRANT OWNERSHIP ON ALL VIEWS  IN SCHEMA FIVETRAN_DEMO.TRANSFORM TO ROLE DBT_ROLE COPY CURRENT GRANTS;
GRANT OWNERSHIP ON ALL TABLES IN SCHEMA FIVETRAN_DEMO.SERVE     TO ROLE DBT_ROLE COPY CURRENT GRANTS;
GRANT OWNERSHIP ON ALL VIEWS  IN SCHEMA FIVETRAN_DEMO.SERVE     TO ROLE DBT_ROLE COPY CURRENT GRANTS;

Or drop the two schemas and re-run dbt_setup.sql. Nothing in TRANSFORM or SERVE is a source of truth; they rebuild from the landing schema in seconds.

Step 2: Point the project at your landing schema

This is a one-line edit and it is the difference between everything working and every model failing.

Open dbt_project.yml:

vars:
  fivetran_database: FIVETRAN_DEMO
  fivetran_schema: POSTGRES_DEMO_L1_LANDING

Those variables feed models/customer_rev/source.yml:

sources:
  - name: landing
    database: "{{ var('fivetran_database') }}"
    schema: "{{ var('fivetran_schema') }}"

The value ships as POSTGRES_DEMO_L1_LANDING because my Fivetran connection is named postgres_demo and Fivetran prefixes the destination schema with the connection name. If you named your connection something else, or chose Fivetran’s source-naming option, yours is different. Use whatever SHOW SCHEMAS IN DATABASE FIVETRAN_DEMO reported in part 1.

Get this wrong and you see:

Schema 'FIVETRAN_DEMO.L1_LANDING' does not exist or not authorized

Read that message carefully when it happens. “does not exist or not authorized” is Snowflake being unable to distinguish the two, and the instinct is to go re-check grants. It is almost always the name.

The schema override macro

While you are in dbt_project.yml, notice this:

models:
  oms_dbt_proj:
    customer_rev:
      +schema: "{{ 'TRANSFORM' }}"
      customerrevenue:
        +schema: "{{ 'SERVE' }}"
        +tags: ['daily']

By default dbt would take those and concatenate them onto your target schema, so TRANSFORM becomes something like PROD_TRANSFORM. That default exists for good reasons on teams, but here it would mean the schemas I created in step 1 are not the schemas dbt writes to.

macros/generate_schema_name.sql turns that off:

{% macro generate_schema_name(custom_schema_name, node) -%}
    {{ custom_schema_name if custom_schema_name else target.schema }}
{%- endmacro %}

Use the custom schema name verbatim if there is one, otherwise the target’s. This is why the Default Schema Name field we fill in Fivetran shortly barely matters: every model in this project overrides it.

Commit and push before you connect the project in Fivetran.

Step 3: Give Fivetran read access to your repo

Transformations → Add transformation → select your Snowflake destination. On the project type screen, hover dbt Core and click Connect project.

The Quickstart data models block at the top of that page is a different feature, prebuilt packages for common SaaS sources. Ignore it. What you want is under Orchestrate your custom data models.

The deploy key

Fivetran generates its own SSH key pair for Git access and shows you the public half.

This is the third key pair in this project and it is unrelated to the other two. Worth keeping straight:

Nothing is reused between them, and that is the point. Each one can be revoked on its own.

Copy the public key, then in GitHub go to your repository → Settings → Deploy keys → Add deploy key:

Title it fivetran, paste the key, and leave “Allow write access” unchecked. Fivetran only clones. It has no reason to push, and an unchecked box is the cheapest security control in this entire project.

A deploy key is scoped to one repository, which is the whole reason to use one instead of a personal access token. If it leaks, someone can read this repo. That is the blast radius.

Finish the configuration

Back in Fivetran:

The SSH URL matters. The HTTPS URL will not authenticate with a deploy key, and the error you get is a generic clone failure.

Leave Project path empty because dbt_project.yml is at the repo root, which is where Fivetran looks by default. Some people nest their dbt project in a subdirectory; if you do, that field is where you say so.

dbt deps is a no-op here since there is no packages.yml, but leaving it on costs nothing and means you do not have to remember it the day you add a package.

Save & Test, then Done. Fivetran syncs the project in a couple of minutes.

Step 4: Define the jobs in deployment.yml

Jobs live in deployment.yml at the repo root. Fivetran re-parses this file on every project sync and creates or updates the jobs it finds. This is my favorite part of the setup: the schedule lives in Git next to the models, and changing it is a pull request rather than a click in someone’s UI.

The whole file:

jobs:
  - name: Daily-after-landing
    schedule:
      type: integrated
      integrations:
        - verdure_rephrase # replace with your postgres connection id
    steps:
      - name: run daily models
        command: dbt run --select +tag:daily

  - name: Weekly-3am
    schedule:
      type: cron
      entries: ["0 3 * * 0"]
    steps:
      - name: run weekly models
        command: dbt run --select +tag:weekly

You must change one value: replace verdure_rephrase with your own Postgres connection ID, the two-word system name from part 1. Find it under Connections → your Postgres connection → Setup, or read it out of the browser URL. It is not postgres_demo.

Why type: integrated is the interesting one

The cron job is ordinary. Sunday at 3am, run the weekly models.

type: integrated is different: it does not run on a clock at all. It runs when that Fivetran connection finishes a sync. New data lands, models rebuild. That is the correct trigger, and it is the thing that is genuinely hard to build yourself.

In my Airflow project I approximated it with Datasets and a lot of careful DAG wiring. Here it is three lines of YAML, because the tool doing the ingestion is the same tool running the transformation and it simply knows when the sync finished.

This is also the mechanism that makes part 3 work without me clicking anything.

Why +tag:daily builds five models

The tags come from dbt_project.yml: customerrevenue is tagged daily, emp_weekly_sales is tagged weekly. Only two models are tagged at all.

But the selector is +tag:daily, not tag:daily. The leading + means “this model and everything it depends on.” So the daily job builds:

customers_stg  ─┐
orders_stg     ─┼─→ orders_fact ─→ customerrevenue
orderitems_stg ─┘

Five models. employees_stg and emp_weekly_sales are not in that dependency graph, so they sit out and wait for Sunday.

This is dbt’s actual value in one line of config. I did not write the build order anywhere. dbt read the ref() calls in the SQL, built the DAG, and worked out that orders_fact cannot run before orderitems_stg.

Commit and push. The jobs appear under Transformations after the next project sync.

Step 5: Run it — part 2 checkpoint

Trigger Daily-after-landing manually from the Transformations tab rather than waiting for the next Neon sync

Both jobs should end on Succeeded.

Now look at the Connections column, because it is the fastest way to check that step 4 actually worked. Daily-after-landing shows postgres_demo, which means the integrated schedule resolved your connection ID. If it says None, the ID in deployment.yml is wrong and the job will never fire on its own. It will still run when you click the button, which makes this a quiet failure that you would not notice until part 3.

Weekly-3am showing None is correct. It is on cron and is not tied to a connection.

Open the job and read the Run log:

Two details in there are worth noticing.

target='prod'. That is Fivetran’s generated profile, not the dev target from any local profiles.yml. Concrete proof that Fivetran built its own connection config from the destination and never looked at your file.

Five models, not seven. PASS=5. Exactly the dependency closure of +tag:daily we worked out above.

Confirm in Snowsight:

SELECT COUNT(*) FROM FIVETRAN_DEMO.SERVE.CUSTOMERREVENUE;

The pipeline is now end to end: a row changes in Neon, Fivetran ships it to Snowflake, the sync completing triggers the dbt job, and the reporting tables rebuild. Nobody ran anything.

When it fails

Almost every failure is one of two things, and they present identically:

Object does not exist or not authorized

Read which object the message names.

The first is a permission problem. The second is a naming problem. Snowflake refuses to tell you which, so the object name is your only signal.

Step 6 (optional): Run dbt locally while you develop

You only need this to test model changes before pushing them. Scheduled builds never touch this setup. Skip it entirely if you are just following along.

A key pair for DBT_USER

Same pattern as part 1, different file. Do not reuse Fivetran’s key:

mkdir -p ~/.snowflake
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out ~/.snowflake/dbt_rsa_key.p8 -nocrypt
openssl rsa -in ~/.snowflake/dbt_rsa_key.p8 -pubout -out ~/.snowflake/dbt_rsa_key.pub
chmod 600 ~/.snowflake/dbt_rsa_key.p8

Copy the public key body, one line, no BEGIN / END:

grep -v -- '-----' ~/.snowflake/dbt_rsa_key.pub | tr -d '\n' | pbcopy

Paste it into snowflake/sql/dbt_keypair.sql and run as SECURITYADMIN.

DBT_USER exists so your laptop never holds the destination’s private key. If your machine is compromised, the attacker gets a role that can rebuild two schemas, not the credential that writes your entire warehouse.

Install dbt in the repo

Python 3.10 to 3.13; the project pins 3.12. Install into a virtualenv in the repo, not system-wide:

uv sync
source .venv/bin/activate

Or with the stdlib: python3.12 -m venv .venv, activate, python -m pip install -r requirements.txt. On Windows the activate script is .venv\Scripts\activate. Check with which dbt, which should end in .venv/bin/dbt.

Point dbt at Snowflake

Copy profiles.yml.example to profiles.yml in the repo root (already gitignored) or to ~/.dbt/profiles.yml. Set account to your <ORG>-<ACCOUNT> and give an absolute path to the private key:

authenticator: snowflake_jwt
private_key_path: /Users/YOUR_USER/.snowflake/dbt_rsa_key.p8

dbt reads the key by path. Do not paste the key into the YAML. The profile name must stay oms_dbt_proj to match dbt_project.yml.

Build

dbt debug
dbt run
dbt test

dbt debug should end in All checks passed! with user: DBT_USER and role: DBT_ROLE. Compare that to the target='prod' in the Fivetran run log: two different identities, same tables.

dbt run builds all seven models. dbt test is currently a no-op, because models/schema.yml has descriptions but no tests yet. That is a gap, and adding unique and not_null tests to the staging models is the obvious first contribution if you want to extend this project.

One thing to be ready for: local and scheduled runs write the same tables, and Snowflake only lets the owning role replace a table. If a local run fails on ownership after Fivetran has built the models, see the ownership handover in step 1.

What I actually think about this setup

The honest comparison against how I would have done it before:

That last row is the trade. Fivetran Transformations runs dbt commands. It does not run your Python, it does not call an API, it does not branch on a condition. The moment your pipeline needs a step that is not dbt, you are back to needing an orchestrator.

For a pipeline that is only “sync, then transform,” which describes a genuinely large share of analytics work, giving up that flexibility in exchange for deleting the entire scheduling layer is a good deal. For anything more complex it is not, and it is worth knowing which one you are building before you pick.

Outro

You now have a complete pipeline. Data changes in Neon, Fivetran replicates it into Snowflake, the sync completing triggers a dbt job, and the reporting tables in SERVE rebuild, all without a server of your own and without a line of Python.

The obvious question is whether it actually works, and that is part 3. We go back to Neon, change five rows, click nothing, and follow those rows all the way to SERVE.

The changes arrive. But two of the numbers are wrong, in ways that are entirely the models’ fault and not the pipeline’s, and figuring out why is the most useful thing in this whole series.

If you enjoyed this, please react or share. See you in part 3.

Feel free to connect with me and ask any question

Hi everyone, my name is Mark, an Analytics Engineer at Infinite Lambda, graduated from TCU in the summer of 2025, majoring in Computer System Analyst with minors in Math and Fintech.

Last time I wrote about rebuilding my junior-year project: a scraper, an S3 landing zone, Terraform for the infrastructure, Airflow to orchestrate it, and a COPY INTO at the end to get the data into Snowflake. It worked, and I learned a lot writing it. It was also about 1,500 lines of Python, YAML, and HCL standing between a source system and a warehouse table.

So this project asks the opposite question: how much of that can I delete if I let a managed vendor do the extract and the load?

The answer turned out to be “almost all of it, and the interesting problems move somewhere else.” There is no Python in this repo. There is no Airflow, no Docker, no Terraform. There are SQL scripts, dbt models, and a YAML file. The work that remains is permissions, key pairs, and understanding what a change-data-capture (CDC) stream actually does to your tables, which is honestly the part I understood least before I started.

This is part 1 of three. In this part we do the ingestion: stand up a Postgres source on Neon, create a Snowflake trial and the objects Fivetran needs to write into, and connect the two so eight tables land in the warehouse.

Note: the data here is the SleekMart OMS educational sample. It is completely unrelated to Infinite Lambda’s business.

Takeaway

The full repo is here. This part’s takeaway:

• What ELT actually means in practice, and why the “T” moving into the warehouse changes how you set up the “EL”

• How Postgres logical replication works: WAL, publications, replication slots, pgoutput, and replica identity. These four words are the whole ingestion layer

• Why you create a dedicated role, user, warehouse, and database for a third-party tool instead of handing it your admin login

• Key-pair authentication for a service account, which is what Snowflake now expects and what most tutorials still skip

• How a managed connector differs from a pipeline you wrote yourself: what you gain, and what you now have to reason about instead

My junior-year self would have solved this by writing a Python script with psycopg2 and pandas.to_sql, running it on a cron, and re-reading every table every night. That works until the table has ten million rows. What Fivetran does instead is read Postgres’ own change log (WAL), which is a fundamentally different idea and worth understanding even if you never pay for Fivetran.

Prerequisite

You do not need Python. You do not need Docker. You do not need to know Terraform.

You need to be comfortable opening a browser, pasting SQL into a console, and running two or three openssl commands in a terminal. Basic SQL helps for the verification queries, but you are mostly copying scripts out of the repo.

You will create three free accounts as we go: Neon, Snowflake, and Fivetran. None of them ask for a credit card to start.

Overview

Clone the repo here to follow along. Here is the whole thing:

Reading it left to right:

Two things about that diagram are worth pointing out before we start.

1. Neon is only the source. dbt never talks to Neon. It only ever reads the Snowflake replica. If you come from the ETL world where transformation happens in flight, this feels backwards, and that reversal is the entire point of ELT: copy first, cheaply and dumbly, then reshape inside a warehouse that is good at reshaping.

2. Fivetran appears twice. It does the extract-load in part 1, and in part 2 it also clones your Git repo and runs your dbt project on a schedule. That surprised me. I had assumed I would need dbt Cloud or an orchestrator. You do not.

The series is split like this:

Finish part 1 before you start part 2. Every dbt model in part 2 fails on a missing table if the landing schema is not there.

Step 1: Create the Neon source database

Neon is serverless Postgres. It is not a Postgres-flavored thing, it is actual PostgreSQL with the storage layer separated from the compute layer, which is what lets it branch instantly and scale to zero when idle. It is now part of Databricks. For us it is just “a real Postgres that is always reachable and free,” which is exactly what Fivetran needs to extract from.

Why not run Postgres locally in Docker? Because Fivetran is a SaaS product. It has to be able to reach your database from the internet, and tunneling into your laptop is a much worse afternoon than clicking “create project.”

1. Sign up at console.neon.tech/signup. GitHub, Google, or email all work.

2. Create a project (I called mine fivetran demo) and pick a region near you. My project is on AWS us-east-2, and I mention that because you will want Fivetran’s processing region to be nearby later.

3. Your project starts with a default branch (production) and a default database called neondb.

Now create the database we actually use. Do not use neondb. Under Postgres Database → Databases → Add database, name it fivetran_source and keep the default owner.

You cannot create it from inside a fivetran_source session, obviously, so do it in the Console. If you would rather use SQL, run this while connected to neondb:

CREATE DATABASE fivetran_source;

I am being heavy-handed about this because running SQL against neondb instead of fivetran_source is the single most common way to lose twenty minutes in this tutorial. Every time something later “did not work,” check the database dropdown first.

Step 2: Load the OMS sample data

The sample is SleekMart OMS, an order management system: customers, orders, order lines, products, suppliers, stores, employees, and a date dimension. Eight tables, 2,747 rows. Small enough to sync in half a minute, big enough that the joins in part 2 are real joins.

Everything lives in a schema called l1_landing. Identifiers are unquoted and lowercase so they match Postgres’ folding rules.

Open Postgres Database → SQL Editor and set the database dropdown to fivetran_source.

Create the tables

Paste source/sql/schema.sql and run it. You should get Statement executed successfully for each CREATE.

One thing to notice in that file: none of these tables have a primary key.

CREATE TABLE IF NOT EXISTS l1_landing.customers (
    customerid VARCHAR(10),
    firstname VARCHAR(50),
    lastname VARCHAR(50),
    ...
    updated_at TIMESTAMP
);

customerid looks like a key and behaves like one, but Postgres has not been told that. This is a completely ordinary thing to find in a real source system, and it has consequences that show up three steps from now and again in part 3. Keep it in the back of your head.

Insert the rows

Still on fivetran_source, paste source/sql/insert.sql and run it. It is about 180k characters, so the editor may warn you that history will be truncated. The inserts still run.

Expected counts:

Confirm

SELECT * FROM l1_landing.customers;

100 rows. That is the checkpoint for the source.

If you get nothing, you are on neondb. I warned you.

Step 3: Turn on logical replication

This is the step that makes the whole thing incremental, and it is the step worth actually understanding rather than copy-pasting.

The problem

Fivetran has to answer one question on every sync: what changed since last time?

The naive answer is query-based sync: SELECT from the tables and diff against what you already have, using Postgres metadata like xmin to narrow it down. It works. It also means scanning the source on a schedule forever, and it is bad at detecting deletes, because a row that is gone does not show up in a SELECT.

The better answer is to stop asking Postgres what changed and instead read the log Postgres already writes.

WAL

Every Postgres instance maintains a write-ahead log. Before a change hits the table, it goes into the WAL. Postgres needs this for crash recovery and physical replication. Conceptually it is an activity log:

10:01 INSERT order 101
10:05 INSERT order 102
10:10 UPDATE order 101 NEW -> PAID
10:15 DELETE order 102

That is exactly the stream a data pipeline wants. Change data capture is nothing more than “read that log instead of scanning the tables.”

The catch is that by default the WAL records physical changes (”bytes on page 387 changed”), which is useless to an external tool. Setting wal_level = logical tells Postgres to record enough detail that the row-level events can be reconstructed. That is all that setting does, and it is why Neon exposes it as a project-level switch rather than something you can run as SQL.

Publication and replication slot

Two more objects, and the distinction between them is the thing to memorize:

The publication is an allow-list. This demo uses FOR ALL TABLES because it is a demo; in production you would name the tables.

The slot is a bookmark. Say the WAL events are numbered 100 through 105 and Fivetran has consumed through 102. The slot records that. Next sync, Fivetran reconnects and Postgres hands it 103 onward. Just as importantly, Postgres will not discard WAL that the slot still needs, so Fivetran can be offline for a while without losing data.

That guarantee is also the operational risk. Create a slot, then abandon it, and Postgres keeps every WAL segment since the bookmark, forever, and your storage grows:

day 1:  █
day 2:  ███
day 3:  ███████
day 4:  ████████████

So: one connector per slot, and clean up slots you stop using.

pgoutput is the last piece. The WAL is not a convenient stream of rows, so something has to decode it. pgoutput is Postgres’ built-in logical decoding plugin and the one Fivetran uses.

The whole picture:

                      POSTGRES
                         │
                         │ rows change
                         ▼
                       WAL
                         │
              wal_level = logical
                         │
                         ▼
                     pgoutput
                         │
            ┌────────────┴────────────┐
            ▼                         ▼
      publication_01         replication_slot_01
       "WHAT tables?"          "WHERE am I?"
            │                         │
            └────────────┬────────────┘
                         ▼
                      Fivetran
                         │
                         ▼
                      Snowflake

If that diagram makes sense, you understand the ingestion layer. The longer version with more diagrams is in source/postgres_cdc.md; you do not need it to finish the setup, but read it afterwards.

Enable it in Neon

Settings → Logical Replication → Enable.

Two warnings that are real and not boilerplate:

• This cannot be reverted. It changes wal_level for the whole project.

• It restarts compute, so you get a brief disconnect, and a connected subscriber keeps compute from scaling to zero, which uses more of your free credits.

Confirm from the SQL Editor on fivetran_source:

SHOW wal_level;

You want logical.

Create the publication and slot

Paste and run source/sql/replication.sql. It does four things:

1. Sets REPLICA IDENTITY FULL on all eight tables. Remember the missing primary keys? Here is where that bites. When an UPDATE or DELETE hits the WAL, Postgres needs to describe which row changed. With a primary key that is easy: “row with customerid = 123.” Without one, Postgres by default has nothing useful to log.

REPLICA IDENTITY FULL says: log the entire old row. Then a downstream consumer can match on all the values instead of a key.

ALTER TABLE l1_landing.customers REPLICA IDENTITY FULL;

The cost is more WAL volume per change, which for 2,747 rows is nothing and for a billion-row production table would be a real decision. The right fix in production is a primary key.

2. Creates the publication:

DROP PUBLICATION IF EXISTS publication_01;
CREATE PUBLICATION publication_01 FOR ALL TABLES;

3. Creates the slot:

SELECT *
FROM pg_create_logical_replication_slot('replication_slot_01', 'pgoutput');

Publication before slot. That is the order Fivetran’s setup guide requires. Postgres itself does not strictly care, but follow the documented sequence.

4. Prints sanity checks for wal_level, the publication, and the slot.

Write down these two names exactly. You type them into Fivetran later, and publication_1 will not find publication_01:

• Publication: publication_01

• Slot: replication_slot_01

Step 4: Create a Snowflake trial

Snowflake’s trial is 30 days with about $400 in credits, no credit card. That is enormously more than this project uses.

Pick the AI Data Cloud trial, not the Cortex Code CLI one. You need a real warehouse so Fivetran can write tables.

1. Go to signup.snowflake.com and leave AI Data Cloud For Enterprise selected.

2. Fill in name, work email, and why you are signing up. A personal name is fine.

3. Click the activation link in the email (check spam).

4. Choose a cloud, region, and edition. Cloud and region cannot be changed later, so pick something close to you and to your Neon project. Enterprise is the normal trial edition and is fine.

5. Set your admin username and password. This is your login, not the service user we create next.

6. Sign in at app.snowflake.com.

You land in Snowsight as ACCOUNTADMIN, with a default warehouse called COMPUTE_WH and some sample data. Credits expire after 30 days and the account suspends, so do not put anything you care about here.

Step 5: Create the Snowflake objects Fivetran writes into

Snowflake is empty. Before Fivetran can load anything, it needs somewhere to load to and an identity to load as.

The temptation is to hand Fivetran your ACCOUNTADMIN login and move on. Do not. Fivetran is a third party with a persistent connection to your warehouse, and the whole point of this step is that if that credential leaks, the blast radius is one database and one extra-small warehouse.

Open a Snowsight worksheet, paste snowflake/sql/fivetran_setup.sql, select the entire script (Snowsight only runs what is highlighted), and Run.

Four objects come out of it:

A few decisions in that script that are worth calling out.

Its own warehouse. Do not point Fivetran at COMPUTE_WH. A dedicated XSMALL warehouse that suspends after 60 seconds means Fivetran’s loads never queue behind your ad-hoc queries, and you can see exactly what the pipeline costs. Given that trial credits are finite, AUTO_SUSPEND = 60 is the single most useful line in the file.

A narrow set of grants. USAGE on the warehouse, then CREATE SCHEMA, MONITOR, USAGE on the database. That is it. Fivetran creates the landing schema itself, so you do not pre-create l1_landing.

Load format defaults:

ALTER USER IDENTIFIER($USER_NAME) SET BINARY_INPUT_FORMAT = 'BASE64';
ALTER USER IDENTIFIER($USER_NAME) SET TIMESTAMP_INPUT_FORMAT = 'AUTO';

These two prevent a class of failure that presents as “bad data” rather than “missing privilege,” which is a much more annoying thing to debug.

Role granted to SYSADMIN, so you can inspect Fivetran’s objects from a normal admin session without becoming the service user.

Attach a key pair

FIVETRAN_USER is a service user with no password, and Snowflake is discontinuing username/password auth for destinations anyway. So it authenticates with an RSA key pair.

The mental model that took me a minute: Snowflake never generates or downloads a private key for you. You generate the pair on your machine, register the public half with Snowflake, and give the private half to Fivetran.

mkdir -p ~/.snowflake
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out ~/.snowflake/fivetran_rsa_key.p8 -nocrypt
openssl rsa -in ~/.snowflake/fivetran_rsa_key.p8 -pubout -out 
~/.snowflake/fivetran_rsa_key.pub
chmod 600 ~/.snowflake/fivetran_rsa_key.p8

Copy the public key body to your clipboard, without the BEGIN / END lines, on one line:

grep -v -- '-----' ~/.snowflake/fivetran_rsa_key.pub | tr -d '\n' | pbcopy

Paste it into snowflake/sql/fivetran_keypair.sql and run it as SECURITYADMIN:

USE ROLE SECURITYADMIN;
ALTER USER FIVETRAN_USER SET RSA_PUBLIC_KEY = '<PASTE_PUBLIC_KEY_HERE>';

In part 2 we generate a second pair for a dbt user. Do not reuse this one. Separate pairs mean you can rotate one without breaking the other, and it is the difference between “rotate the Fivetran key” and “take the whole pipeline down.”

Verify and grab your host

Run snowflake/sql/verify.sql. It confirms the four objects and the grants, then prints the account identifiers:

SELECT
    CURRENT_ORGANIZATION_NAME() AS organization_name,
    CURRENT_ACCOUNT_NAME() AS account_name,
    CURRENT_ACCOUNT() AS account_locator,
    CURRENT_REGION() AS region;

Your Fivetran Host is:

<organization_name>-<account_name>.snowflakecomputing.com

A hyphen between org and account, not a dot. This trips up a lot of people, me included. There is an older <locator>.<region> form floating around in blog posts that will fail the host test.

Step 6: Connect the Postgres source in Fivetran

Sign up for a free Fivetran account, then Connections → Add connection → PostgreSQL.

Before you start, open two browser tabs: Neon’s Connect modal, and the output of verify.sql. You will be copying from both.

Connection name

I used postgres_demo. Pick carefully, because this name becomes part of your destination schema name and cannot be changed later.

By default Fivetran prefixes the destination schema with the connection name, so Neon’s l1_landing arrives in Snowflake as POSTGRES_DEMO_L1_LANDING. This matters a lot in part 2: the dbt project has that name hardcoded in a variable, and if yours differs, every model fails.

Networking

Open Connect in the Neon sidebar. Branch production, database fivetran_source, role neondb_owner, and turn connection pooling off.

You get a URI like:

postgresql://neondb_owner:PASSWORD@ep-....us-east-2.aws.neon.tech/fivetran_source?sslmode=require

Do not paste that whole string into one Fivetran field. Split it:

Connection method is Connect directly.

The hostname must not contain -pooler. Neon’s pooled endpoint runs through PgBouncer, and logical replication needs a persistent session, so the two are incompatible. If pooling was on when you copied the URI, go back and turn it off rather than editing the string by hand.

Database access

Look at the Database field in that screenshot: it starts empty. It does not default to anything. Leave it blank and the connection test fails with a message that does not obviously say “you left the database blank.”

Set Data processing location near your Neon project. Mine is AWS us-east-2, so I picked the closest US AWS region Fivetran offers.

Incremental sync

Here is where the names from step 3 go:

Fivetran is literally going to look up those objects in Postgres. Exact match or nothing.

Save and test

You want green on the certificate, connecting, setup, and Testing logical replication.

Checking configuration values will probably warn you, and on Neon Free that is expected:

You cannot change these on Neon the way you could on self-hosted Postgres. Click Continue. Creating BIT_XOR aggregate showing a dash is also fine.

If something actually fails:

Step 7: Connect the Snowflake destination

Destinations → Add destination → Snowflake. Note this is a destination, not a source connector; they are different parts of the Fivetran UI.

Copy the full private key, BEGIN and END lines included:

pbcopy < ~/.snowflake/fivetran_rsa_key.p8

Two mistakes to avoid. First, this is the .p8 file, not the .pub. Snowflake already has the public half; Fivetran needs the private one. Second, Role is technically optional in the form, but fill it in anyway so Fivetran is not silently relying on the user’s default role.

Save & Test. You want all six:

Debugging guide: Host Connection failing is almost always the hostname format. Permission Test failing means re-run fivetran_setup.sql. Private key rejected means you pasted the .pub, or the public key in Snowflake does not match this private key.

Step 8: The first sync

Both connections test clean, but nothing has moved yet. The Postgres connection sits Paused until you tell it which tables to take.

1. Click Review connection schema.

2. Select the l1_landing schema, or the eight OMS tables individually. Leave Fivetran’s system tables alone.

3. Save, then unpause and sync.

This is where the design pays off. Fivetran does a historical sync first, a straight copy of everything currently in the tables, and then switches to reading the WAL for everything after. Phase one is the snapshot, phase two is the change stream, and you never think about the handoff.

Roughly half a minute, eight tables, 2,747 rows extracted and loaded.

Part 1 checkpoint

In Snowsight:

SHOW SCHEMAS IN DATABASE FIVETRAN_DEMO;

SELECT COUNT(*) FROM FIVETRAN_DEMO.POSTGRES_DEMO_L1_LANDING.CUSTOMERS;

You want 100.

SHOW SCHEMAS is the important one. Write down the landing schema name exactly as Snowflake reports it. Because my connection is called postgres_demo, mine is POSTGRES_DEMO_L1_LANDING, not L1_LANDING. Part 2 has to be told which one you got, and if you skip this you spend an hour reading Schema 'FIVETRAN_DEMO.L1_LANDING' does not exist or not authorized and looking for a grant problem that is actually a naming problem.

While you are in Fivetran, note the connection’s ID: the two-word system name Fivetran assigned it, visible in the connection’s Setup tab and in the browser URL. Mine is verdure_rephrase. That is not the display name postgres_demo, and part 2 needs it to trigger dbt off this connection.

You will also see a FIVETRAN_*_STAGING schema appear. That is Fivetran’s internal staging area. Ignore it.

Look at that screenshot one more time and notice the first column of the result set: _FIVETRAN_ID. Fivetran added columns to your tables. Because these tables have no primary key, Fivetran generates _FIVETRAN_ID as a hash of every column value and uses it to identify rows. There is also _FIVETRAN_SYNCED and _FIVETRAN_DELETED.

Those three columns are the most interesting thing in this entire project, and part 3 is essentially one long argument about them. For now, just notice they are there.

What this cost, in effort

Comparing honestly against my last project’s ingestion layer:

The work does not disappear, it relocates. I did not write a line of extract-load logic, and in exchange I had to actually learn what a replication slot is and how Snowflake’s role hierarchy works. I think that is a good trade, and I think the second set of things is more transferable than the first.

Outro

That is the ingestion half. You have a real Postgres source streaming its change log, a warehouse with a dedicated least-privilege service identity, and eight tables replicating between them with no code and no server of your own.

Right now Snowflake holds an exact, uncleaned copy of Neon. In part 2 we turn that into reporting tables with dbt, and the twist is that we do not run dbt ourselves: Fivetran clones the Git repo and runs it on a schedule, using a deploy key and a deployment.yml. No dbt Cloud, no orchestrator.

Then in part 3 we go back to Neon, change five rows, and follow them all the way to the reporting tables. That is where _FIVETRAN_DELETED stops being trivia and starts being a bug in the dashboard.

If you enjoyed this, please react or share so I know to keep going. Thanks for reading this far.

Feel free to connect with me and ask any question

And I honestly don’t think it matters that much.

Unless you’re joining a company tomorrow and you already know they run Power BI, don’t lose sleep over it. You will switch BI tools multiple times in your career whether you like it or not.

I’ve used Tableau. I’ve used Power BI. I’ve dashboarded in Streamlit like a psychopath. I’ve used Mode and Hex, and those two are my favorites. And tomorrow my company could decide to migrate everything to Sigma for all I know.

The tool changes. The principles don’t.

It’s the same reason I don’t tell people to memorize Spark syntax before understanding distributed computing. Learn the fundamentals first. The rest is just finding where the buttons are.

Because every dashboard, in every tool, is trying to answer exactly one question:

Can someone make a better decision because of what you built?

If yes, good dashboard. If no, you made expensive wall art.

So what even is a dashboard

Anytime you’re displaying information to help someone make a decision, congrats, you’re building a dashboard. Doesn’t matter if it’s Tableau, a Google Sheet, or a sticky note on a monitor.

Back to my kitchen analogy: the warehouse is the kitchen, the dataset is the dish, and the dashboard is the plate that actually hits the table. It’s the only part of your work most people will ever see. Nobody walks into the kitchen to compliment your beautiful incremental model. They judge the plate. And if the dataset underneath is garbage, the dashboard is just garbage with a nicer font. I’ve said this before, I’ll say it forever.

ACES: what a good dashboard looks like

The best framework I’ve found comes from Matt David’s book How to Design a Dashboard (it’s free from The Data School, go read it). He says an optimal dashboard is Accurate, Clear, Empowering, and Succinct. ACES.

Here’s what each one means when you’re the one building.

1. Accurate (A)

A dashboard lives and dies on trust. If viewers doubt the accuracy, they will not use it to make decisions. Full stop. And it’s actually worse than that: one broken dashboard makes people doubt ALL your data. Then everyone crawls back to their personal spreadsheets and now there are six versions of revenue in every meeting. Congratulations, you just un-invented the data warehouse.

Accuracy breaks down into three things.

1.1 Data Quality

Two questions to ask about every dashboard:

Is the data displayed correctly? Every single number on that screen needs to be right. Not “directionally right.” Right. Viewers can’t verify your numbers, they take them on faith, and faith does not survive a second incident. And if a dashboard IS broken and you can’t fix it right now, flag it loudly: slap [BROKEN] or [UNDER REPAIR] in the title. People assume any dashboard they stumble onto is correct unless you tell them otherwise.

Is the data displayed ALL of the data? Most dashboards are not real time. Batch processing says hi. If your pipeline runs every 12 hours and someone opens the dashboard at 11am, today’s numbers look like the business drove off a cliff. Cue the panic Slack thread. The fix costs you one text box: “Last updated 6am, refreshes every 12 hours.” That single line will save you a hundred “why is revenue down 60%???” messages that are really just “the dbt run hasn’t finished yet.”

1.2 Metric Comprehension

A metric has to be understood before it can be interpreted accurately. “Revenue” sounds universal until finance, marketing, and product each calculate it differently. Gross or net? Refunds in or out? Booked or recognized?

Same with “conversion rate.” Converting from what to what? Sessions to leads? Leads to sales? Every team fills in that blank differently and then argues about it in a meeting like it’s a personality trait.

The best practice from the book: put the formula, note, or definition for any non-standard metric directly below the metric. On the dashboard itself. Not in a wiki nobody reads, not in your head. If someone has to DM you to know what a number means, that number might as well not exist.

1.3 Visualization Design

Here’s the sneaky one: you can display perfectly correct data and still lie with the chart. Most of the crime happens on the Y-axis.

Bar charts start at zero. No exceptions. Bars communicate through size. Truncate the axis to start at 80 and a bar of 84 looks double a bar of 82, when the real difference is 2%. That’s not “emphasis,” that’s bias baked into pixels.

Line charts get a “good range” instead. Lines communicate through slope, not size, so zooming in is fine, actually encouraged. A line plotted on a 0 to 1M axis when the data lives between 8K and 24K is just a flat line at the bottom of the chart. All the variability, which is the entire point of a line chart, becomes invisible.

2. Clear (C)

Clarity is speed to insight. Every second someone spends squinting, decoding your color scheme, or DMing you “wait, what am I looking at” is the dashboard failing at its job. Four things to get right.

2.1 Fonts

Fonts should not be decorative. The goal is legibility, this is a dashboard, not a wedding invitation. Boring sans serif (Arial, Helvetica, Verdana), sized so nobody has to squint and nothing is shouting. If anyone ever notices your font choice, you chose wrong.

2.2 Colors

Two rules: easy and consistent.

Easy: muted tones, easy on the eyes, and no single color visually overweighting the others. Paint one series neon red and everyone reads it as an incident, even when it’s just “the West region.”

Consistent: same metric, same color, every chart. If paid search is blue in one chart, it better be blue in every chart, because your brain connects data across charts through color whether you want it to or not. And don’t freestyle hex codes at 5pm on a Friday, use a color picker tool.

2.3 Context

A chart with no context is a Rorschach test. Give people:

• A descriptive title. “Operations Cost over Time by Department” beats “Chart 3”.

• Categorical labels, so nobody’s guessing which slice is which.

• Value labels when it’s hard to eyeball against the axis.

And the rule from the book that I think about constantly: the more explanation a dashboard needs, the less effective it is.

2.4 Layout

People in most countries read left to right, top to bottom. Your dashboard gets read the same way whether you designed for it or not. So design for it: most important number goes top left. Least important goes bottom right, the retirement home for charts nobody asked about.

3. Empowering (E)

This is the one nobody measures, and it’s the entire point. Does anyone actually USE this thing to make decisions? The end users are the judges here, not you. Two ways to check.

3.1 Usage

Do they view it regularly? Most BI tools give you view logs, so actually check them. If views are dropping, don’t take it personally, take it as data, and go ask:

• Is the dashboard still useful to them?

• Is it missing something critical?

• Where are they getting their numbers instead? (There is ALWAYS an instead. It’s usually a spreadsheet. Find it.)

• What would make this thing serve them better?

A dashboard nobody opens is just compute burning money on a schedule.

3.2 Decision Making

Does it factor into actual decisions? My favorite test in the whole book: ask the viewer, “if this number went to zero tomorrow, or doubled, what would you do?”

If the answer is “nothing,” that chart is decoration. Cut it or rethink it. If the answer is “I’d reallocate budget by Friday,” congrats, you built a real dashboard.

4. Succinct (S)

If your dashboard has stuff hiding below the fold, you have too much stuff.

The entire value proposition of a dashboard is seeing everything at once so your brain can compare. Human working memory is tiny. The moment someone scrolls, chart 1 evaporates from their head while they read chart 7. At that point you’ve built a report with extra steps.

Two questions to ask about every chart on the page:

Is this data necessary? Not interesting. Necessary. (See the zero-or-doubled test above.)

Can it live in a smaller space without losing clarity? A giant line chart that exists to communicate one number should be a single value stat. Shrink it and free up the real estate.

And keep it relevant: every chart should relate to the dashboard’s purpose, and related data should sit close together. A weather widget is useful. A spend forecast is useful. They do not belong on the same dashboard. If people need more granularity, link out to a drill-down dashboard instead of cramming everything onto one screen.

The process: Define, Prototype, Build, Deploy

ACES tells you what good looks like. This process is how you actually get there. The book breaks it into four steps, Define → Prototype → Build → Deploy, and the whole trick is that SQL doesn’t show up until step three.

Because here’s what actually happens in the wild: someone asks for a dashboard, you get excited, you start pulling data, and fifteen charts later you’ve shipped a Frankenstein wall of bar charts that answers everything in general and nothing in particular. I’ve done it. You’ve done it. The fix is boring as hell: slow down at the start.

1. Define

Figure out who this is for and what decision it supports before you touch a keyboard.

Every dashboard project has four characters: you (the designer), the audience, the Point Person (the one who requested it and owns the business need), and the Data Gatekeeper (the data team member who knows the schema and where the bodies are buried; at my company that role is literally me).

The Point Person will show up with vibes: “I want visibility into the marketing funnel.” Vibes are not metrics. Your job is the interrogation: What does success look like? What decision are you trying to make? What would you actually DO if this number doubled?

Then squeeze every metric into something queryable: formula, aggregation, group by, filters. Write it all down. The book calls this a Metric Spreadsheet, and it’s exactly as unsexy and as useful as it sounds. “SUM(amount), grouped by month, excluding free trials” is a spec. “Show me revenue” is a prayer.

2. Prototype

Pen. Paper. No SQL.

Not gonna lie, hand-drawing bar charts like a kindergartner feels ridiculous. But a bad idea costs 30 seconds on paper and three days in a BI tool. Sketching also forces you to think about design instead of getting sucked into whether the numbers are right. That part comes later.

Sketch it, show the Point Person, and ask the uncomfortable questions: Do you actually need to know this? Is the takeaway of each chart obvious? Which chart grabs your eye first, and should it? What’s missing?

Then iterate. The goal is a useful dashboard, not defending your first draft. Check your ego at the whiteboard.

Quick chart-picking cheat sheet while you sketch: single value for headline KPIs, bar for comparing categories, line for trends over time, bar-line when you need both. Keep it to about three chart types per dashboard, viewership actually drops off after that. And 3D charts are banned. Forever.

3. Build

NOW you write SQL.

First, find the data. Three scenarios: you have it, it’s messy, or it doesn’t exist. It is almost always messy, so budget time for the boring checks: nulls and blanks, obviously fake values (John Doe, 123-456-7890, birthdays before 1919), outliers, and my personal enemy, category misspellings, because SQL will happily treat ‘Hamburger’, ‘hamburger’, and ‘Hanburger’ as three different businesses.

Whatever you decide to do with the mess (ignore, delete, impute), document the decision so the next person can reproduce your numbers instead of discovering them like ancient ruins.

If the data straight up doesn’t exist, you either instrument new tracking (talk to engineering, set expectations, it will take a while) or you use a proxy metric. Be careful with proxies. Measure “return visits” as a stand-in for “customers like us” and you’ll slowly start optimizing for visits instead of happiness. Proxy metrics quietly redirect entire companies.

And get a code review on your queries. You review dbt PRs, review your dashboard SQL too. The Data Gatekeeper exists for exactly this.

4. Deploy

Shipping is not the end. It’s the start of a maintenance contract you didn’t know you signed.

Match delivery to decision cadence. Daily decisions? Scheduled 8am email. Ad hoc decisions? Just share the link, don’t spam inboxes. Threshold-based decisions? Set an alert, and pick the threshold carefully or you’ll train everyone to ignore it.

Then maintain the damn thing. Schemas change, sources get re-architected, someone renames a column and your chart quietly starts serving garbage with full confidence. Put a recurring review on the calendar: are the queries still right, is anyone still looking, is anything slow enough to optimize. And archive dashboards nobody has viewed in 90 days (after a warning email). Dead dashboards still run queries, and your warehouse pays for every single one.

TLDR

Stop stressing about which BI tool to learn. Learn this instead:

• A dashboard exists to support a decision. No decision, no dashboard.

• ACES: Accurate (data quality + metric definitions + honest axes), Clear (fonts, colors, context, layout), Empowering (usage + the “would you do anything if this doubled” test), Succinct (nothing below the fold).

• Define → Prototype → Build → Deploy. Design on paper before you write a single line of SQL.

• The dashboard is the plate that hits the table. Serve a dish, not a buffet.

The tool is just where the buttons are. This is the actual job.

In case you wonder wtf is this. Hey, I’ve never said that my drawing (or my hand writing) was any good. But well, it got the job done. Things finally “click” when you spent time draw it out and tried to understand everything through a wall of text. I STRONGLY recommend this method for anyone who’s studying Data Engineering concepts or any technical concepts in general. Grab a dashboard, imagine you’re teaching someone, then explain everything clearly.

This article is a way that I revise my knowledge about Spark for people who have tried to learn Spark before but just, couldn’t get it (me before this article). I not the best at drawing to I will just let ChatGPT do the work.

I also assume that you understand the fundamental different of distributed computing (why it exists) and how Spark is different from Hadoop (writing to disk vs processing in memory). This article is a bit more deep dive on the architecture of Spark itself. For each topic, I would go over the details and highlight the key TLDR by bolding it.

Well, enough said, let’s dig into the flow how I spend 3 hours on this topic. This article will cover the first part of shuffling - Aggregation

Shuffling - Aggregation

There are two main type of aggregation in Spark

• Hash aggregate

• Sort aggregate

The key idea

• HashAggregate: "I need to remember every group because groups can appear anywhere."

• SortAggregate: “I only need to remember the current group because sorting guarantees I will never see it again after I move on.”

Tradeoff

So SortAggregate trades:

extra CPU (sorting)
        +
        ↓
less memory usage

for- HashAggregate:

more memory
        +
        ↓
faster aggregation

This is why Spark has both strategies. They solve the same aggregation problem but with different memory/CPU tradeoffs.

Key idea of Aggregation

Aggregation (GROUP BY, COUNT, SUM, etc.) typically happens in three stages:

1. Partial aggregation – Each input partition aggregates its local rows.

   1. Usually uses Hash Aggregate when possible.

   2. Falls back to Sort Aggregate if hashing isn’t appropriate or memory is constrained.

2. Shuffle – Data is repartitioned by the grouping key.

3. Final aggregation – Partial results are merged to produce the final output.

Hash Aggregation

Let’s understand Hash Aggregation by having a super simple example.

SELECT state, COUNT(*)
FROM customers
GROUP BY state;

1. The input partition:

Given that our input partition look like the following:

Partition 1
-----------
CA
TX
CA

Partition 2
-----------
CA
WA
TX

Partition 3
-----------
TX
WA
CA

2. Partial Aggregation Step

Let’s just take a look at partition 1:

Partition 1
-----------
CA
TX
CA

the expected output would be

CA: 2
TX: 1

The question is how does Spark do this, with data up to billions of rows? In hash map case, it will go over each individual values and build the hash map in memory.

Start with an empty HashMap:

{}

Read CA:

CA does not exist → create entry

{
  CA: 1
}

Read TX:

TX does not exist → create entry

{
  CA: 1,
  TX: 1
}

Read CA:

CA already exists → update count

{
  CA: 2,
  TX: 1
}

Final Partial Aggregation:

CA → 2
TX → 1

This same process happens independently on all input partitions before the shuffle stage. So at the end, each partition will have its own local HashMap:

Partition 1
-----------
CA → 2
TX → 1


Partition 2
-----------
CA → 1
WA → 1
TX → 1


Partition 3
-----------
TX → 1
WA → 1
CA → 1

3. Shuffle Step

The problem:

The same key exists across multiple partitions. Spark needs to bring all values for the same key together before it can do the final aggregation.

For example:

CA → 2   (from Partition 1)
CA → 1   (from Partition 2)
CA → 1   (from Partition 3)

must end up in the same place.

The solutions

Therefore, shuffle is needed. Spark usually applies a partitioning function (usually hash partitioning) on the grouping key:

shuffle_partition = hash(state) % number_of_shuffle_partitions -- default to 200

Spark default the number_of_shuffle_partitions to 200, but for the sake of simplicity, let’s only look at 3. We will talk about how AQE coalesce these (shuffle) partitions into smaller number of shuffle partitions in later part.

Example with 3 shuffle partitions:

hash(CA) % 3 → Shuffle Partition 1
hash(TX) % 3 → Shuffle Partition 2
hash(WA) % 3 → Shuffle Partition 3

Now Spark redistributes the data:

Shuffle Partition 1
-------------------
CA → 2
CA → 1
CA → 1


Shuffle Partition 2
-------------------
TX → 1
TX → 1
TX → 1


Shuffle Partition 3
-------------------
WA → 1
WA → 1

The key idea:

Shuffle does not aggregate. It only moves data so that all rows with the same grouping key are located in the same partition.

4. Final Aggregation Step

After the shuffle phase, all records with the same grouping key are now in the same shuffle partition:

Shuffle Partition 1
-------------------
CA → 2
CA → 1
CA → 1


Shuffle Partition 2
-------------------
TX → 1
TX → 1
TX → 1


Shuffle Partition 3
-------------------
WA → 1
WA → 1

Now Spark performs the final aggregation. Each shuffle partition builds another HashMap and combines the partial results:

Shuffle Partition 1

Start with empty HashMap:

{}

Read CA → 2:

{
  CA: 2
}

Read CA → 1:

{
  CA: 3
}

Read CA → 1:

{
  CA: 4
}

Final result from Shuffle Partition 1:

CA → 4

The same process happens for the other shuffle partitions:

Shuffle Partition 2

TX → 1 + 1 + 1 = 3


Shuffle Partition 3

WA → 1 + 1 = 2

Final aggregated dataframe:

State | Count
-------------
CA    | 4
TX    | 3
WA    | 2

Sort Aggregation

Now that we understand Hash Aggregation, let’s focus on the key difference.

With Hash Aggregation, Spark builds an in-memory HashMap:

state → count

Example:

{
  CA: 2,
  TX: 1
}

With Sort Aggregation, Spark does not build a HashMap.

Instead, Spark:

1. Sorts the rows by the grouping key.

2. Scans the sorted rows and aggregates consecutive values.
   

Example:

Before sorting:

CA
TX
CA

After sorting:

CA
CA
TX

Spark then scans the sorted data:

Read CA:
First value → start a new group

Current Group:
CA → 1

Read CA:
Same key as previous row → increment count

Current Group:
CA → 2

Read TX:
New key detected → finish previous group and start a new one

Output:
CA → 2

Current Group:
TX → 1

End of partition:

Final Partial Aggregation:

CA → 2
TX → 1

The shuffle and final aggregation steps are the same as Hash Aggregation. With this, Spark does not have to keep a hash map in memory, but the trade off is that it will need to sort the data in each input partition first

Comparison

Hash Aggregate:

• HashAggregate builds an in-memory hash table keyed by the group key. The value is an aggregation buffer updated in-place (e.g. running SUM). No sorting needed — fast and preferred.

• For HashAggregate to work efficiently, Spark uses the UnsafeRow format — an off-heap binary representation that bypasses the JVM garbage collector (part of Project Tungsten).

• This format works best with fixed-size types that can be updated in-place in the hash table: integers, longs, doubles, dates, booleans.

Sort aggregation

• SortAggregate sorts the input data by the group key first, then iterates over it linearly to accumulate groups. Slower because of the sort pass.

• Variable-length types like String are not easily mutable in a fixed-size memory slot. When any aggregation buffer holds a String (or ArrayType, MapType), Spark falls back to SortAggregate.

Hope this is all helpful :) In the next part we will still gonna cover shuffling, but with a different beast - Joins

In case you wonder wtf is this. Hey, I’ve never said that my drawing (or my hand writing) was any good. But well, it got the job done. Things finally “click” when you spent time draw it out and tried to understand everything through a wall of text. I STRONGLY recommend this method for anyone who’s studying Data Engineering concepts or any technical concepts in general. Grab a dashboard, imagine you’re teaching someone, then explain everything clearly.

This article is a way that I revise my knowledge about Spark for people who have tried to learn Spark before but just, couldn’t get it (me before this article). I not the best at drawing to I will just let ChatGPT do the work.

I also assume that you understand the fundamental different of distributed computing (why it exists) and how Spark is different from Hadoop (writing to disk vs processing in memory). This article is a bit more deep dive on the architecture of Spark itself. For each topic, I would go over the details and highlight the key TLDR by bolding it.

Well, enough said, let’s dig into the flow how I spend 3 hours on this topic.

The Input

Think about the input similar to your

pandas.read_csv()

Similarly, Spark does the same thing - but at distributed scale. It can read data from Parquet files, CSVs, JSON files, Kafka streams, databases via JDBC, cloud object storage (such as S3 or GCS), and lakehouse formats like Delta Lake and Iceberg. Regardless of the source, Spark loads the data into a DataFrame with a unified API. Here are a few examples:

-- local files
df = spark.read.csv("customers.csv", header=True)

-- s3 bucket
spark.read.csv("s3://bucket/data.csv")

-- kakfa stream
spark.readStream \
    .format("kafka") \
    .option("subscribe", "orders") \
    .load()

Quite similar to Pandas right, I guess there’s nothing too crazy here. One thing that I wanted to note is that Parquet on disk is compressed (SNAPPY, dictionary encoding). In-memory DataFrame when reading it could be ~8-10x larger.

Key idea: Spark can read from mutiple sources and create a Dataframe (similar to what pandas does)

The Input partition

Again, we are talking about the INPUT partition. When I first learn Spark, I was easily confuse about when the articles talk about “partitions”. Turn out there’s input partitions and shuffle partitions. These are 2 really different concepts.

                         +-------------+
                        /|  partition  |
                       / +-------------+
                      /
+---------+          /   +-------------+
|         |---------+--->|  partition  |
|  input  |          \   +-------------+
|         |           \
+---------+            \ +-------------+
 parquet                \|  partition  |
                         +-------------+
                          \
                           \+-------------+
                            |  partition  |
                            +-------------+

When Spark reads data into a DataFrame, it splits the data into partitions. You don’t need to know what these partitions does yet! Just remember that:

Spark never processes the DataFrame as one giant object. It always breaks it into multiple input partitions

You can actually control how many input partitions are created by configuring spark.sql.files.maxPartitionBytes

spark.conf.set("spark.sql.files.maxPartitionBytes", 256 * 1024 * 1024) # 256MB

# default is 128MB

So for example, let’s say we have a 2GB dataset and we set the maxPartitionBytes, we will have

2,048 MiB / 128 MiB = 16 input partitions # if max size is 128
2,048 MiB / 256 MiB = 8 input partitions # if max size is 256

Nice, what happen if it’s 3000MB? Spark will round up and maximize the size of each input partition.

3,000 / 256 = 11.71875

=> 12 input partitions

Partitions 1–11: ~256 MiB
Partition 12:     ~184 MiB

There will always be trade of between high vs low number of input partitions

• High: fewer tasks but more memory per task and higher spill risk (don’t worry we will talk about this later)

• Low: Scheduling overhead - More tasks mean more scheduling (we will also talk about this later)

Jobs, Stages, and Tasks

        +------------+
        |    Jobs    |
        +------------+
              |
              v
        +------------+
        |   Stages   |
        +------------+
              |
              v
        +------------+
        |   Tasks    |  <--- smallest
        +------------+       executable
                             step

When you execute an action (show(), collect(), write(), count()), Spark creates a job. This job will be submited to the Spark master (dw about this).

Then the job will be split into stages, and each stages will be split into tasks. Tasks is a smallest executable step in Spark

Order: Job → Stages → Tasks

The workers

I also have a hard time understanding this concepts at first, but turns out it’s not that complicated. The worker are just a bunch of resources that’s INDEPENDENT from the spark cluster itself.

Spark cluster can utilize some (or all of these resources) but they exist independently from the cluster itself. For example: Imagine two EC2 instances, each with 8 vCPUs and 16 GB of RAM. The instances exist before any Spark application starts and remain running after it finishes. They may also run other workloads.

+----------------------+  +----------------------+
| EC2 worker 1         |  | EC2 worker 2         |
| Available resources  |  | Available resources  |
+----------------------+  +----------------------+

Do not confuse workers with executors

The EC2 instances are the underlying worker machines. Executors are temporary processes created for a particular Spark application.

The Spark Submit

spark-submit sends your application code and configuration to the cluster. It is the command that starts a Spark application, not the component that processes the data.

spark-submit \
  --master spark://master-host:7077 \
  app.py

spark-submit submits your application and configuration to the cluster manager. In Spark Standalone, that cluster manager is the Spark Master. The driver then requests executors and coordinates execution of your code across them.

For other cluster manager, configuration might differ

Spark Standalone -> Spark Master
Amazon EMR       -> YARN ResourceManager
Kubernetes       -> Kubernetes API

The Spark Cluster (Spark Master, Driver, Executor)

A Spark cluster usually contain of 3 components:

• Spark Master: resource manager

• Driver: brain of one Spark application

• Executor: do the work

Submit job
    |
    v
+------------------+       requests resources        +------------------+
|                  | ------------------------------> | Executor JVM #1  |
|  Spark Master    |                                 +------------------+
|                  | ------------------------------> +------------------+
+------------------+                                 | Executor JVM #2  |
                                                     +------------------+
          |
          | starts / manages application
          v
+------------------+
| Driver JVM       |
|                  |
| Schedules tasks  | ------------------------------> Executors
+------------------+

1. Spark master

Spark Master = cluster resource manager

It knows which worker machines have available CPU and memory. It allocates those resources to Spark applications.

The name Spark Master specifically applies to Spark Standalone. On EMR, this role is usually handled by YARN ResourceManager instead.

2. Driver (JVM process)

Driver = brain of one Spark application

The driver is a JVM process. In cluster mode it usually runs on a worker node. In client mode it runs on the machine that submitted the application.

• Runs your main Python or Scala code

• Converts operations into jobs, stages, and tasks

• Requests executors

• Sends tasks to executors

• Collects task results and status

There is normally one driver for each Spark application.

3. Executors (JVM process)

Executor = worker process for one Spark application

An executor is a JVM process launched on a worker node for a specific Spark application.

• Run tasks

• Process partitions

• Cache intermediate data

• Send results and status back to the driver

• An application can have many executors, and a worker machine can run multiple executor processes. Executors belong to a particular Spark application and disappear when that application ends.

Ways to run a Spark application

Since we have just cover the 3 main component of SparkStandalone (the Spark Master, Driver, and Executors) let’s take a look at 3 different ways to run

1. Local mode

The driver and executors run on the same machine. No Spark cluster is used.

One machine
+--------------------------+
| Driver                    |
| Executors / worker threads|
+--------------------------+

Example:

spark-submit --master local[*] app.py

Your Laptop
──────────────────────────────

spark-submit
      |
      |
      ▼

Spark Driver JVM
──────────────────────────────
|
├── SparkContext
├── DAG Scheduler
├── Task Scheduler
|
├── Executor (inside same JVM)
|      |
|      ├── Thread 1 → Task
|      ├── Thread 2 → Task
|      ├── Thread 3 → Task
|      └── Thread 4 → Task
|
└── Memory / Cache


(no cluster manager)
(no workers)
(no remote executors)

2. Client mode

The driver runs on the machine where spark-submit is executed. The executors run on the worker nodes in the cluster.

Client machine                 Spark cluster
+-------------+               +-------------+
| Driver      | ------------> | Executors   |
+-------------+               +-------------+

The client machine must remain connected while the application is running.

3. Cluster mode

The driver runs inside the Spark cluster, usually on one of the worker nodes. The machine that runs spark-submit can disconnect after submitting the application.

Submission machine             Spark cluster
+-------------+               +----------------------+
| spark-submit| ------------> | Driver + Executors   |
+-------------+               +----------------------+

The Executors (Cores and memory)

Let’s say we have 2 8CPU cores, 16GB RAM VM. The Spark application requests the following executor configuration from the cluster manager.

• spark.executor.instances=2 → 2 executor JVMs across the cluster.

• spark.executor.cores=4 → 4 cores per executor

• spark.executor.memory=8g → 8 GB memory allocate to each executor

• spark.cores.max=8 → caps total cores (standalone Spark is greedy by default).

Here would be the architecture diagram

                         Spark Standalone cluster

+----------------------------------+    +----------------------------------+
| Worker VM 1                      |    | Worker VM 2                      |
| 8 CPU cores, 16 GB RAM           |    | 8 CPU cores, 16 GB RAM           |
|                                  |    |                                  |
| +------------------------------+ |    | +------------------------------+ |
| | Executor JVM 1               | |    | | Executor JVM 2               | |
| | 4 CPU cores                  | |    | | 4 CPU cores                  | |
| | 8 GB memory                  | |    | | 8 GB memory                  | |
| +------------------------------+ |    | +------------------------------+ |
|                                  |    |                                  |
| Spare: 4 CPU cores, 8 GB RAM     |    | Spare: 4 CPU cores, 8 GB RAM     |
+----------------------------------+    +----------------------------------+

We can also set

• spark.task.cpus=1 (default, not in code) → Number of cores per task. Usually 1 core will handle 1 tasks

1 input partitions → 1 task → handles by 1 executor

Therefore in the example above, we will have 8 concurrent tasks, each task handle a partition in a stage:

• 2 executor * 4 cores = 8 concurrent tasks (assuming 1 task is handled by 1 core)

Memory Per Executor

Memory overhead

So let’s say you request 8gb of memory for a executor

spark.executor.memory = 8g

This means:

Each executor JVM gets 8 GB of JVM heap memory.

It does not mean the whole container/process only uses 8 GB. There is also memory overhead.

Executor Container Memory
================================================

spark.executor.memory
(JVM Heap)
8 GB

+ 

spark.executor.memoryOverhead
(Off-heap / non-JVM)
~0.1 × executor memory
(minimum 384 MB)

================================================

Total memory requested from worker
≈ 8.8 GB

Outside the JVM Heap

Outside JVM heap, there are memory buffer includes:

• Python worker process (PySpark)

• Arrow

• Native libraries

• JVM native memory

• Thread stacks

• Network buffers

Inside the JVM Heap

Inside the “8gb” itself, Spark divides the heap into two major regions - Spark Memory and User memory

• Spark memory

  • Execution Memory

    • HashAggregate

    • Sort

    • Join

    • Shuffle

  • Storage Memory

    • Cached DataFrames/RDDs

    • Broadcast variables

• User memory

  • Java/Python objects

  • UDF variables

  • Temporary collections (ArrayList, HashMap, etc.)

  • Driver/executor metadata

  • Third-party libraries

JVM Heap (8 GB)
================================

Spark Memory (60%)
Execution + Storage

≈ 4.8 GB


User Memory (40%)
User objects, metadata, UDFs, etc.

≈ 3.2 GB

The ratio:

spark.memory.fraction = 0.6

controls the Spark region.

Borrowing

Spark Memory (~60% of the heap) is a shared pool split into:

• Execution Memory → HashAggregate, joins, sort, shuffle

• Storage Memory → cache(), persist(), broadcast

There fore, Execution can borrow unused Storage memory and Storage can use free memory when Execution isn’t using it.

Outro

Today I spent several hours learning Spark, hope this help everyone

Recursive CTEs is hard, and I’m trying my best to explain it to you.

Question:

Here’s the Leetcode question: Unfortunately you will need a premium account to access it.

Write a solution to analyze the organizational hierarchy and answer the following:

1. Hierarchy Levels: For each employee, determine their level in the organization (CEO is level 1, employees reporting directly to the CEO are level 2, and so on).

2. Team Size: For each employee who is a manager, count the total number of employees under them (direct and indirect reports).

3. Salary Budget: For each manager, calculate the total salary budget they control (sum of salaries of all employees under them, including indirect reports, plus their own salary).

Return the result table ordered by the result ordered by level in ascending order, then by budget in descending order, and finally by employee_name in ascending order.

The result format is in the following example.

+-------------+---------------+------------+--------+-------------+
| employee_id | employee_name | manager_id | salary | department  |
+-------------+---------------+------------+--------+-------------+
| 1           | Alice         | null       | 12000  | Executive   |
| 2           | Bob           | 1          | 10000  | Sales       |
| 3           | Charlie       | 1          | 10000  | Engineering |
| 4           | David         | 2          | 7500   | Sales       |
| 5           | Eva           | 2          | 7500   | Sales       |
| 6           | Frank         | 3          | 9000   | Engineering |
| 7           | Grace         | 3          | 8500   | Engineering |
| 8           | Hank          | 4          | 6000   | Sales       |
| 9           | Ivy           | 6          | 7000   | Engineering |
| 10          | Judy          | 6          | 7000   | Engineering |
+-------------+---------------+------------+--------+-------------+

Output:

+-------------+---------------+-------+-----------+--------+
| employee_id | employee_name | level | team_size | budget |
+-------------+---------------+-------+-----------+--------+
| 1           | Alice         | 1     | 9         | 84500  |
| 3           | Charlie       | 2     | 4         | 41500  |
| 2           | Bob           | 2     | 3         | 31000  |
| 6           | Frank         | 3     | 2         | 23000  |
| 4           | David         | 3     | 1         | 13500  |
| 7           | Grace         | 3     | 0         | 8500   |
| 5           | Eva           | 3     | 0         | 7500   |
| 9           | Ivy           | 4     | 0         | 7000   |
| 10          | Judy          | 4     | 0         | 7000   |
| 8           | Hank          | 4     | 0         | 6000   |
+-------------+---------------+-------+-----------+--------+

Explanation:

• Organization Structure:

  • Alice (ID: 1) is the CEO (level 1) with no manager

  • Bob (ID: 2) and Charlie (ID: 3) report directly to Alice (level 2)

  • David (ID: 4), Eva (ID: 5) report to Bob, while Frank (ID: 6) and Grace (ID: 7) report to Charlie (level 3)

  • Hank (ID: 8) reports to David, and Ivy (ID: 9) and Judy (ID: 10) report to Frank (level 4)

• Level Calculation:

  • The CEO (Alice) is at level 1

  • Each subsequent level of management adds 1 to the level

• Team Size Calculation:

  • Alice has 9 employees under her (the entire company except herself)

  • Bob has 3 employees (David, Eva, and Hank)

  • Charlie has 4 employees (Frank, Grace, Ivy, and Judy)

  • David has 1 employee (Hank)

  • Frank has 2 employees (Ivy and Judy)

  • Eva, Grace, Hank, Ivy, and Judy have no direct reports (team_size = 0)

• Budget Calculation:

  • Alice’s budget: Her salary (12000) + all employees’ salaries (72500) = 84500

  • Charlie’s budget: His salary (10000) + Frank’s budget (23000) + Grace’s salary (8500) = 41500

  • Bob’s budget: His salary (10000) + David’s budget (13500) + Eva’s salary (7500) = 31000

  • Frank’s budget: His salary (9000) + Ivy’s salary (7000) + Judy’s salary (7000) = 23000

  • David’s budget: His salary (7500) + Hank’s salary (6000) = 13500

  • Employees with no direct reports have budgets equal to their own salary

Note:

• The result is ordered first by level in ascending order

• Within the same level, employees are ordered by budget in descending order then by name in ascending order

Solution

When I look at this type of question, my obvious initial thoughts would be to use recursive cte. But the question is how? Then I realized another way to think of this is tree traversal. But we will be traversing differently in 2 different ctes

                        Alice
                      /       \
                  Bob         Charlie
                 /   \        /     \
            David   Eva   Frank    Grace
              |             /  \
            Hank         Ivy   Judy

Part 1: Generating levels using bfs

| Level | Employees                |
| ----: | ------------------------ |
|     0 | Alice                    |
|     1 | Bob, Charlie             |
|     2 | David, Eva, Frank, Grace |
|     3 | Hank, Ivy, Judy          |

Okay so let’s simplify this question, ignore the later 2 columns and only care about levels, how can we generate a level column?

| employee_id | employee_name | level |
| ----------- | ------------- | ----- |
| 1           | Alice         | 1     |
| 2           | Bob           | 2     |
| 3           | Charlie       | 2     |
| 4           | David         | 3     |
...
and so on

Naturally we would start at the root of the tree

select
    *,
    0 as level
from employees 
where manager_id is null

| employee_id | employee_name | manager_id | salary | department | level |
| ----------- | ------------- | ---------- | ------ | ---------- | ----- |
| 1           | Alice         | null       | 12000  | Executive  | 0     |

Now it’s the harder part, we wants to find all the direct reports from this “current” manager. The manager would be the “root” and direct reports would be the leaf

select
    leaf.*, 
    1 as level 
from employees root
left join employees leaf 
on root.employee_id = leaf.manager_id
where root.manager_id is null
order by 1

| employee_id | employee_name | manager_id | salary | department  | level |
| ----------- | ------------- | ---------- | ------ | ----------- | ----- |
| 2           | Bob           | 1          | 10000  | Sales       | 1     |
| 3           | Charlie       | 1          | 10000  | Engineering | 1     |

Nice! let’s say we wants to keep doing this until we were able to traverse around all the leaf of the tree, how would we do that? We will need a technique call recursive ctes

with recursive calc_level as (

    select 
        employee_id,
        employee_name,  
        1 as level
    from employees root
    where manager_id is null 

    union all 

    select
        leaf.employee_id,
        leaf.employee_name, 
        level + 1 as level 
    from employees leaf -- find all the leaf belong to the current root
    join calc_level root  
    on root.employee_id = leaf.manager_id
)
select * from calc_level

Let’s slow it down, this is, in my humble opinion, a really complex concept. The key is that the SQL engine does not literally rerun the query against the entire calc_level table every time. Internally, it maintains a working table.

Think of it like this:

• Result table = everything we’ve found so far.

• Working table = only the rows found in the previous iteration.

The algorithm is essentially:

result  = anchor query
working = anchor query

while working is not empty:
    new_rows = recursive_query(working)
    add new_rows to result
    working = new_rows

A good way of understanding this concepts is to generate a series from 1-n using SQL, it would be something like this:

with recursive numbers as (
    select 
        0 as number
    
    union all 
    
    select 
        number + 1 
    from numbers 
    where number < 10
)

select * from numbers

1. Step 1: Anchor base line for level 1

SELECT employee_id, employee_name, 1 AS level
FROM employees
WHERE manager_id IS NULL;

Result:

| id | name  | level |
| -- | ----- | ----- |
| 1  | Alice | 1     |

Now

Result table
------------
Alice

Working table
-------------
Alice

2. Step 2: The recursive queries - create level 2

The recursive query is

SELECT
    leaf.employee_id,
    leaf.employee_name,
    root.level + 1
FROM employees leaf
JOIN calc_level root
ON root.employee_id = leaf.manager_id;

Conceptually, the engine substitutes the working table for calc_level.

So it’s really doing

employees
JOIN
Working table (Alice)
-- Find all direct report of Alice

which is

Alice
   |
employees.manager_id = 1

It finds

Bob
Charlie

Now

Result
------
Alice -- level 1
Bob -- level 2
Charlie -- level 2

Working
-------
Bob -- level 2
Charlie -- level 2

Notice Alice is not in the working table anymore.

5. Step 3: Keep going with the same logic on working table

Now at the end the engine tries

employees
JOIN
(Hank, Ivy, Judy) 
-- find all employee where manager id is Hank, Ivy, Judy

None of them manage anyone.

The working table becomes empty.

Working
-------
(empty)

The recursion stops.

Question: Why doesn’t it keep expanding Alice forever?

Because Alice is no longer in the working table after iteration 1. If the engine kept joining against the entire accumulated result every iteration, it would keep finding Bob and Charlie again:

Iteration 2
Alice -> Bob

Iteration 3
Alice -> Bob   <-- duplicate
Bob -> David

Iteration 4
Alice -> Bob   <-- duplicate again
Bob -> David   <-- duplicate again
...

That would either produce duplicates forever, which create chaos and run-time error.

Finalize part 1: Generating levels

with recursive calc_level as (

    select 
        employee_id,
        employee_name,  
        1 as level
    from employees root
    where manager_id is null 

    union all 

    select
        leaf.employee_id,
        leaf.employee_name, 
        level + 1 as level 
    from employees leaf -- find all the leaf belong to the current root
    join calc_level root  
    on root.employee_id = leaf.manager_id
)
select * from calc_level

This would give you exactly what we wanted

| employee_id | employee_name | level |
| ----------- | ------------- | ----- |
| 1           | Alice         | 1     |
| 2           | Bob           | 2     |
| 3           | Charlie       | 2     |
| 4           | David         | 3     |
| 5           | Eva           | 3     |
| 6           | Frank         | 3     |
| 7           | Grace         | 3     |
| 8           | Hank          | 4     |
| 9           | Ivy           | 4     |
| 10          | Judy          | 4     |

Part 2: Generate tree and all possible “Subtree”

Now, in order to find team size and total, we would need to have a table of all possible subtree, then aggregate it to the employee_id level that is in order to calculate this:

| employee_id | employee_name | team_size | budget |
| ----------- | ------------- | --------: | -----: |
| 1           | Alice         |         9 |  84500 |
| 3           | Charlie       |         4 |  41500 |
| 2           | Bob           |         3 |  31000 |
| 6           | Frank         |         2 |  23000 |
| 4           | David         |         1 |  13500 |
| 7           | Grace         |         0 |   8500 |
| 5           | Eva           |         0 |   7500 |
| 9           | Ivy           |         0 |   7000 |
| 10          | Judy          |         0 |   7000 |
| 8           | Hank          |         0 |   6000 |

We need to be able to generate this:

Manager 1 (Alice)

                        Alice
                      /       \
                  Bob         Charlie
                 /   \        /      \
            David   Eva   Frank     Grace
              |             /  \
            Hank         Ivy   Judy


Manager 2 (Bob)

                  Bob
                /     \
            David     Eva
              |
            Hank


Manager 3 (Charlie)

                 Charlie
                /       \
            Frank      Grace
            /    \
         Ivy    Judy


Manager 4 (David)

            David
              |
            Hank


Manager 5 (Eva)

             Eva


Manager 6 (Frank)

            Frank
           /     \
        Ivy      Judy


Manager 7 (Grace)

            Grace


Manager 8 (Hank)

            Hank


Manager 9 (Ivy)

             Ivy


Manager 10 (Judy)

            Judy

| manager_id | subordinate_id | salary |
| ---------- | -------------- | ------ |
| 1          | 1              | 12000  |
| 1          | 2              | 10000  |
| 1          | 3              | 10000  |
| 1          | 4              | 7500   |
| 1          | 5              | 7500   |
| 1          | 6              | 9000   |
| 1          | 7              | 8500   |
| 1          | 8              | 6000   |
| 1          | 9              | 7000   |
| 1          | 10             | 7000   |
...
and so on for all manager_id

After that, we would be able to aggregate it into the manager_id granularity

with recursive calc_all_direct_report as (
    select 
        employee_id as manager_id, 
        employee_id, 
        salary
    from employees 
    -- where manager_id is null 

    union all 

    select 
         root.manager_id, 
         leaf.employee_id,
         leaf.salary
    from employees leaf 
    join calc_all_direct_report root on 
    root.employee_id = leaf.manager_id
)

We would use the same technique. Here we would anchor root.manager_id to traverse and find all subtree. The traversal for manager_id = 1 is exactly similar to the one before, so let’s try and see an example with manager_id = 2.

Original tree:

                  Bob
                /     \
            David     Eva
              |
            Hank

1. Anchor starts Bob

From:

SELECT employee_id AS manager_id, employee_id
FROM employees

You get:

manager_id = 2
employee_id = 2

Meaning:

Bob is the root of this subtree

2. Find Bob’s direct reports

Join:

root.employee_id = leaf.manager_id

Bob manages:

David (id=4)
Eva   (id=5)

Add:

manager_id = 2
employee_id = 4

manager_id = 2
employee_id = 5

3. Expand David and Eva

Now current employees are:

David
Eva

David manages:

Hank (id=8)

Add:

manager_id = 2
employee_id = 8

Important: still manager_id = 2 because we are still building Bob’s subtree.

Final output for Bob:

        Bob (2)
       /      \
  David (4)  Eva (5)
      |
   Hank (8)

Table:

manager_id | employee_id
-----------+------------
2          | 2
2          | 4
2          | 5
2          | 8

The key idea is

manager_id = 2  <-- stays fixed (the tree owner)
employee_id     <-- moves down the tree

From here, you would get all possible subtree in one table and we can roll up to the manager_id level

| manager_id | employee_id | salary |
| ---------- | ----------- | ------ |
| 1          | 1           | 12000  |
| 1          | 10          | 7000   |
| 1          | 9           | 7000   |
| 1          | 8           | 6000   |
| 1          | 7           | 8500   |
| 1          | 6           | 9000   |
| 1          | 5           | 7500   |
| 1          | 4           | 7500   |
| 1          | 2           | 10000  |
| 1          | 3           | 10000  |
| 2          | 2           | 10000  |
| 2          | 8           | 6000   |
| 2          | 5           | 7500   |
| 2          | 4           | 7500   |
| 3          | 6           | 9000   |

Putting it all togher

The hardest part is done, now we have 2 ctes: calc_level and calc_all_direct_report

| employee_id | employee_name | level |
| ----------- | ------------- | ----- |
| 1           | Alice         | 1     |
| 2           | Bob           | 2     |
| 3           | Charlie       | 2     |
| 4           | David         | 3     |
...

and

| manager_id | employee_id | salary |
| ---------- | ----------- | ------ |
| 1          | 1           | 12000  |
| 1          | 10          | 7000   |
| 1          | 9           | 7000   |
| 1          | 8           | 6000   |
| 1          | 7           | 8500   |
| 1          | 6           | 9000   |
| 1          | 5           | 7500   |
| 1          | 4           | 7500   |
| 1          | 2           | 10000  |
| 1          | 3           | 10000  |
| 2          | 2           | 10000  |
| 2          | 8           | 6000   |
| 2          | 5           | 7500   |
| 2          | 4           | 7500   |
| 3          | 6           | 9000   |
...

Bring it all together we have

with recursive calc_level as (

    select 
        employee_id,
        employee_name,  
        1 as level
    from employees root
    where manager_id is null 

    union all 

    select
        leaf.employee_id,
        leaf.employee_name, 
        level + 1 as level 
    from employees leaf -- find all the leaf belong to the current root
    join calc_level root  
    on root.employee_id = leaf.manager_id
), 

calc_all_direct_report as (
    select 
        employee_id as manager_id, 
        employee_id, 
        salary
    from employees 
    -- where manager_id is null 

    union all 

    select 
         root.manager_id, 
         leaf.employee_id,
         leaf.salary
    from employees leaf 
    join calc_all_direct_report root on 
    root.employee_id = leaf.manager_id
), 


direct_report_roll_up as (
    select 
        manager_id, 
        count(employee_id) - 1 team_size, 
        sum(salary) budget
    from calc_all_direct_report
    group by 1 
    order by 1
)

select 
    employee_id, 
    employee_name, 
    level, 
    team_size, 
    budget
from calc_level
join direct_report_roll_up 
on calc_level.employee_id = direct_report_roll_up.manager_id
order by 3, 5 desc, 2

That’s not the real me. That’s not how I talk, and it’s not what I actually want to talk about. After weeks of grinding through Spark, all I want to say is: fuck it. I don’t want to write about data the polite way anymore. I want these blogs to actually sound like me, so, sorry in advance for the language, but this is how every fucking post from now on is going to read. More personal. Still data. Just with way more swearing and way more hot takes.

Lately I keep getting asked what I actually do as an AE. How many leads did you get? Are you on the phone all day? Nope, none of that, because I’m an Analytics Engineer, not some handsome guy in sales.

So today I'm going to try to explain my job like you're five.

Data Team

Analytics Engineer is a member of the data team, not Account Executive. So no, I’m not closing deals, I’m not hitting quota, and I’m definitely not the guy who emails you “just circling back!” four times. If you came here for sales tips, wrong blog, sorry.

TLDR: the data team is to help people make better decisions. That’s it. That’s the whole reason we exist. Some VP wants to know if a campaign is bleeding money, some PM wants to know if the new feature is actually getting used, and our job is to put a real number in front of them instead of a vibe. Decisions, not dashboards for the sake of dashboards.

And Analytics Engineer, along with Data Engineers, are the ones responsible for creating the dataset for the downstream consumers. Downstream meaning everyone who eats what we cook: Data Analysts, Data Scientists, and a long parade of stakeholders who all swear their question is “quick.” It’s never quick. The dataset is the product. If the dataset is garbage, every chart, every model, every “data driven” decision on top of it is also garbage, just garbage with a nicer font. Garbage in, garbage out, except now it’s in a Tableau dashboard so it looks official.

So when people ask what I do, the honest version is: I’m the person making sure the number you’re about to bet a budget on isn’t a lie.

Data Warehouse

Think simply, this is where people store data. A warehouse is where you store your inventory, and in this case the inventory is data. Boxes of it, stacked to the ceiling, some of it useful and some of it nobody’s touched since 2021 but we’re too scared to delete it.

But it’s more than just a storage closet. The warehouse is the source of truth. When two people show up to a meeting with two different numbers for the same thing, the warehouse is supposed to be the thing that settles the fight. It’s where the data lives, the one place everyone agrees to point at and say “okay, that’s the real number.” No more “well my spreadsheet says...” Your spreadsheet is wrong. The warehouse is right. That’s the deal.

In our case the warehouse is Redshift, but honestly it doesn’t matter if it’s Redshift, Snowflake, BigQuery, whatever. They all do the same job, they just argue about pricing.

And going back to the food thing: the warehouse is the kitchen. It’s where the raw ingredients get dropped off, where I do all the prep and cooking, and where the finished dishes sit ready to go out. Everything happens in here. If the kitchen’s a mess, the food’s a mess, and everybody downstream is eating off the floor.

Traditional Data Roles

• DE (Producer)

• DA/DS & many more (Consumer)

Picture the data warehouse as a kitchen. The DA/DS are the customers out front, hungry, waiting for food. The DE is the one who does everything in the back: goes hunting for the ingredients (get data from the source), drives the truck back (load it into the kitchen), and cooks it (transform). One person, three jobs. Seems like a lot, right?

It is a lot. And for years that was just the gig. One guy hunting, driving, and cooking. The food came out fine, but the kitchen was on fire every single day.

For a small place it actually works. Five ingredients, one pipeline, one guy who knows where all the bodies are buried. He’s a hero and the food’s good.

Then the company grows. Now it’s not five sources, it’s fifty. Salesforce, Stripe, Google Ads, Meta, and some vendor API still returning XML in the year of our lord 2026. Your one hero is now hunting fifty animals, driving fifty trucks, cooking fifty dishes, and the second a single pipeline dies at 2am the whole kitchen goes dark. One person physically cannot do this. The old way breaks.

Quick detour on why it was built this way in the first place.

ETL

The old way had a name: ETL. Extract, Transform, Load, in that order. You pulled the data out, cooked it on some server outside the warehouse, and only loaded the finished dish once it was done. Why cook before loading? Because storage and compute used to cost a fortune. Dumping raw garbage into the warehouse was like renting a mansion to store your trash, so you trimmed everything down on the way in. And the only person who could build and babysit that whole machine was the DE. That’s why he was god. The cooking was welded to the pipeline, and the pipeline was one fragile, holy artifact nobody else could touch.

ELT - Cloud Data Warehouse

Then cloud warehouses showed up and changed the math. Redshift, Snowflake, BigQuery. Storage got cheap as dirt and compute scaled on demand. Suddenly there was zero reason to cook before loading. Just dump it all in raw, the warehouse can take it.

sources

So somebody flipped two letters and the entire industry changed. ETL became ELT. Extract, Load, Transform. Get the raw ingredients into the kitchen first, cook later, inside the warehouse, with plain SQL.

And that flip is the whole reason my job exists.

Once the T moved to the end and lived inside the warehouse, it split clean off from the rest. Extract and Load got so easy a button does it now (hi Fivetran, hi Airbyte). But Transform got bigger and uglier, because now every raw source on earth is sitting in your kitchen and somebody has to turn that mountain of raw chicken into an actual menu. Not the DE, he’s busy keeping the trucks running. Not the DA/DS, they’re out front with the customers.

It’s a new role. The person who only cooks.

The Analytics Engineer.

That’s me. That’s the Analytics Engineer.

The DE backs the truck up and dumps raw ingredients in the walk-in, then walks off the second the data lands in the warehouse. They don’t give a fuck if it’s a clean table or five levels of nested JSON. Not their problem anymore.

Then there’s the AE, the line cook who turns that pile into something a human can actually put in their mouth. Raw events, ugly JSON, five tables that all kind of mean “user” but not really. I clean it, I prep it, I combine it, and I label the damn containers so the next person isn’t standing there guessing whether that’s revenue or a refund. By the time the DA/DS walk into the kitchen, the dishes are plated and the menu makes sense. They decide what to serve and who to serve it to. I’m the one making sure none of it gives the customer food poisoning.

The thing that turned this from “guy who writes SQL in a corner” into an actual job title is dbt (Thanks god dbt was created). dbt is basically the recipe book plus the health inspector. Every transform is a recipe: version controlled, tested, documented. So when someone asks “wait, why is this number different?” I can point at the exact recipe instead of shrugging. This work always existed, it just used to live in 4000 lines of cursed stored procedures that one guy understood, and that guy quit in 2019.

But here’s the part I actually like. I’m not just the cook. At a start up like Insurify I’m the spanner (Netflix also call their AEs spanners), the tool you grab when you don’t know what else to grab. Some days I’m building the dashboard myself instead of handing it off. Some days a pipeline’s on fire and I’m doing the DE’s job, hunting and driving the truck because somebody has to. Some days I’m sitting with a stakeholder figuring out what the question even is. The lines are blurry as hell and honestly that’s the whole appeal. I’m not locked in one station. I float wherever the kitchen needs me, and that flexibility is what makes this job actually fun instead of just SQL all day. So that’s the five year old version:

• DE go hunting (extract), getting foods into the kitchen (load)

• AE cook (Transform)

• DA/DS run the dining room.

None of us are calling you about your car insurance.

• When Spark performs an aggregation (like SUM or AVG), it doesn’t send every raw record across the network.

• Instead it runs a partial aggregation locally on each partition first (Map phase), writing out summarized results as shuffle files. The reduce tasks then read those files and perform a final aggregation to produce the result.

Partition 1: [a,a,b,b,b] → partial: {a:2, b:3}
Partition 2: [a,b,b,b]   → partial: {a:1, b:3}
                    ↓  shuffle by key
Final merge: {a:3, b:6}

• This significantly reduces network I/O — executors exchange summaries rather than raw rows. That’s also why you’ll see the aggregation operator appear twice in the physical plan: once for partial and once for final.

• Spark has 2 aggregation strategy

  • HashAggregate builds an in-memory hash table keyed by the group key. The value is an aggregation buffer updated in-place (e.g. running SUM). No sorting needed — fast and preferred.

  • SortAggregate sorts the input data by the group key first, then iterates over it linearly to accumulate groups. Slower because of the sort pass.

HashAggregate

• HashAggregate builds an in-memory hash table keyed by the group key. The value is an aggregation buffer updated in-place (e.g. running SUM). No sorting needed — fast and preferred.

• For HashAggregate to work efficiently, Spark uses the UnsafeRow format — an off-heap binary representation that bypasses the JVM garbage collector (part of Project Tungsten).

• This format works best with fixed-size types that can be updated in-place in the hash table: integers, longs, doubles, dates, booleans.

Sort aggregation

• SortAggregate sorts the input data by the group key first, then iterates over it linearly to accumulate groups. Slower because of the sort pass.

• Variable-length types like String are not easily mutable in a fixed-size memory slot. When any aggregation buffer holds a String (or ArrayType, MapType), Spark falls back to SortAggregate.

Joins Strategies

Spark supports several physical join strategies — Sort Merge Join (SMJ), Shuffle Hash Join (SHJ), and Broadcast Hash Join (BHJ). The optimizer picks one based on table sizes and configuration

• Sort merge join:

  1. Step 1: Scan and Shuffle (AQE coalesce shuffling partition here)

  2. Step 2: Sort

  3. Step 3: Merge

  4. Step 4: Aggregation and Output

• Shuffle hash join

  • Step 1: Scan and Shuffle (AQE coalesce shuffling partition here)

  • Step 2: Build

  • Step 3: Probe

  • Step 4: Aggregation and Output

• Broadcast hash join

  • Step 1: Scan and Broadcast

  • Step 2: Build (Build left)

  • Step 3: Probe

  • Step 4: Aggregation and Output

• Bucket join

  • Step 1: Write bucket

  • Step 2: Scan

  • Step 3: Merge

• Broadcast Nested Loop Join

Joins Strategies: Sort Merge Join

SMJ is Spark’s default strategy for joining two large tables. It is not unique to Spark — it has long existed in the database world. The execution happens in three phases.

Phases

1. Phase 1: Shuffle

   • Before the join can happen, rows with the same join key from both tables must end up on the same physical partition.

   • Spark performs an Exchange (shuffle) to redistribute data across the cluster by join key

   • If AQE is enabled, an AQEShuffleRead node follows, coalescing small partitions to keep the join efficient.

2. Phase 2: Sort: O(n log n) + O(m log m)

   • Once data is partitioned by key, each partition is sorted locally. Both branches of the join tree show an explicit Sort operator. This is what makes the merge phase possible.

3. Phase 3: Merge (O (m+n))

   • With both sides partitioned identically and sorted, the join itself is a linear scan through both sorted streams simultaneously:

     • Compare the keys at the current pointer of each stream

     • If keys match → produce a joined row

     • If left key is smaller → advance left pointer

     • If right key is smaller → advance right pointer

Join Strategies: Shuffle Hash Join

SMJ is the preferred join strategy today — but the crown used to belong to Shuffle Hash Join (SHJ). No Sort nodes — that’s the key difference from SMJ.

• SHJ requires the build side of every partition to fit entirely in memory. If a partition is too large (e.g. due to data skew), the executor throws an OutOfMemoryError. SMJ, by contrast, can safely spill to disk — making it more reliable

Phases

1. Phase 1: Shuffle

   • Similar to SMJ, Before the join can happen, rows with the same join key from both tables must end up on the same physical partition.

2. Phase 2: Build

   • Once data is partitioned, Spark identifies the smaller of the two datasets within each partition. It builds an in-memory hash table from that smaller side, using the join key as the hash key.

3. Phase 3: Probe

   • Spark reads through the larger dataset. For each row, it hashes the join key and looks up a match in the hash table. If there is a match, the rows are combined.

Enabling

Two configs are required:

spark.sql.join.preferSortMergeJoin = false
spark.sql.adaptive.maxShuffledHashJoinLocalMapThreshold = 256mb

maxShuffledHashJoinLocalMapThreshold sets the maximum per-partition size allowed to build a hash table. Default is 0 — SHJ is never used. Setting it to 256mb means partitions up to 256 MB can use SHJ.

Join Strategies: Broadcast Hash Join

Broadcast Hash Join (BHJ) is the fastest join strategy — it eliminates the shuffle entirely.

For SMJ and SHJ, both tables go through a shuffle to co-locate matching keys. BHJ takes a different approach: if one table is small enough to fit entirely in memory, Spark sends it to all executors.

Each executor then builds a local hash table from that broadcast copy and handles the join entirely with its own partition of the large table — no network exchange needed.

1. Phase 1: Broadcast

   • The left side (orders, the smaller table) is scanned and broadcast to all executors

   • he right side (lineitem) is simply scanned locally -- no shuffle, no broadcast

2. Phase 2: Build

   • Each executor receives the broadcast orders table and builds an in-memory hash table from it -- identical to Shuffle Hash Join’s build phase, except every executor builds the same hash table from the same complete data

3. Phase 3: Probe

   • Spark reads through the larger dataset. For each row, it hashes the join key and looks up a match in the hash table. If there is a match, the rows are combined.

Join Strategies: Bucket Join

In SMJ and SHJ, Spark shuffles data at join time to co-locate matching keys. A bucket join shifts that shuffle to write time instead.

Bucketing distributes data across a fixed number of buckets based on the hash of a column value. If both tables are bucketed by the same key with the same number of buckets, rows with matching keys already live in the same bucket — Spark can skip the shuffle entirely at join time.

Requirements for a bucket join to work

1. Both tables bucketed on the same column with the same bucket count

2. Metadata registered in the Hive metastore via saveAsTable()

3. SparkSession must have enableHiveSupport() active

4. Read via spark.table(), not spark.read.parquet()

Why bucketBy() requires saveAsTable()

bucketBy() only works with saveAsTable() — it does not work with write.parquet().

When you join bucketed tables later, Spark needs to know which column was used for bucketing and how many buckets there are. That metadata must be stored somewhere persistent — the Hive metastore.

• saveAsTable("my_table") — writes the data AND registers the bucketing metadata in the metastore

• write.parquet(path) — writes data only, no metastore, bucketing info is lost

How to write a bucketed table

df.write \\
    .bucketBy(8, "o_orderkey") \\
    .sortBy("o_orderkey") \\
    .format("parquet") \\
    .saveAsTable("orders_bucketed")

Bucketing shifts that work to write time. Data is distributed across buckets by hashing the join key — rows with the same key always land in the same bucket number. When both tables are bucketed by the same key with the same bucket count, Spark knows bucket 1 of lineitem contains the same keys as bucket 1 of orders. No runtime shuffle needed.

Image from Vu Trinh’s article

• Static vs dynamic allocation: Spark provides two ways to allocate resources for an application.

  1. Static allocation (default) — each application gets a fixed set of executors reserved for the duration of the SparkContext. Resources are held even when idle.

  2. Dynamic allocation — the application can return executors to the cluster when no longer needed and request them back when demand rises.

spark.dynamicAllocation.enabled = true

• Request policy

  • When pending tasks have been waiting for spark.dynamicAllocation.schedulerBacklogTimeout seconds, Spark requests more executors. If the backlog persists, it requests again every spark.dynamicAllocation.sustainedSchedulerBacklogTimeout seconds.

  • Requests are made in exponential rounds — 1 executor, then 2, 4, 8, etc.

  • Scale more at first → more rapidly

• Remove policy

  • An executor is removed when it has been idle for spark.dynamicAllocation.executorIdleTimeout seconds (default 60s).

Dynamic Allocation config

spark.dynamicAllocation.enabled                       = true
spark.dynamicAllocation.minExecutors                  = 1
spark.dynamicAllocation.maxExecutors                  = 4
spark.dynamicAllocation.executorIdleTimeout           = 60s
spark.dynamicAllocation.schedulerBacklogTimeout       = 1s
spark.dynamicAllocation.sustainedSchedulerBacklogTimeout = 1s

• spark.dynamicAllocation.enabled = true Turns on dynamic executor scaling. Spark can add or remove executors based on workload.

• spark.dynamicAllocation.minExecutors = 1 Spark will keep at least 1 executor alive, even when the cluster is mostly idle.

• spark.dynamicAllocation.maxExecutors = 4 Spark can scale up to at most 4 executors when more parallel work shows up.

• spark.dynamicAllocation.executorIdleTimeout = 60s If an executor sits idle for 60 seconds, Spark can remove it to save resources.

• spark.dynamicAllocation.schedulerBacklogTimeout = 1s If tasks are waiting in the scheduler backlog for 1 second, Spark starts requesting more executors.

• spark.dynamicAllocation.sustainedSchedulerBacklogTimeout = 1s If that backlog keeps continuing for another 1 second window, Spark keeps asking for more executors again.

Catalyst Optimizer

Catalyst is Spark’s query optimizer, designed around functional programming constructs in Scala. It provides a general library for representing query plans as trees and applying rules to transform them. Catalyst supports both rule-based and cost-based optimization:

• Rule-Based Optimization (RBO) — applies a fixed set of predefined rules based on query structure and schema, without considering actual data distribution.

• Cost-Based Optimization (CBO) — uses statistics about the data (table sizes, data distribution) to estimate the cost of multiple candidate plans and pick the cheapest one. Requires accurate statistics to be available.

Catalyst Optimizer process

1. Analysis — Spark starts with an unresolved logical plan (column names and table references are not yet validated). It uses Catalyst rules and the Catalog — the central metadata store for databases, tables, and functions — to resolve attributes, check column names, and infer types.

2. Logical Optimization — once the plan is resolved, Spark applies rule-based rewrites: predicate pushdown, projection pruning, null propagation, constant folding, and more.

3. Physical Planning — Spark takes the optimized logical plan and generates one or more physical plans (e.g. hash join vs sort-merge join). It selects the final plan using a cost model.

4. Code Generation — Spark generates Java bytecode for execution using Scala’s quasiquotes, which allow programmatic construction of ASTs compiled into bytecode at runtime. This is especially effective for CPU-bound, in-memory operations.

Projection pruning

• Only columns actually used in the query are read from the file. If your Parquet file has 50 columns but you only select 3, Spark reads only those 3.

Predicate pushdown

• Spark moves filter operations as early as possible — ideally down to the file reader so unneeded rows are never loaded into memory.

• In the physical plan you will see PushedFilters: [IsNotNull(status), EqualTo(status,OPEN)] on the scan node, not after the scan.

Caching

• Spark recomputes a DataFrame from scratch every time an action is called on it. If you use the same DataFrame for multiple actions → caching stores the result after the first action so subsequent actions reuse it.

• Caching also gives you visibility into the real in-memory size of your data. Parquet files are compressed on disk (typically with Snappy). When Spark reads them, it decompresses and deserializes the data into memory — the in-memory size is always larger than the on-disk size.

df = spark.read.parquet("big_file/")  # not loaded yet
df.count()    # reads file, computes count, result discarded
df.count()    # reads file AGAIN — same work repeated

• With caching:

df.cache()
df.count()    # reads file, computes, stores in memory
df.count()    # reuses cached copy — no file I/O

cache() vs persist()

df.cache()
# equivalent to:
df.persist(StorageLevel.MEMORY_AND_DISK)

cache() is a shortcut. persist() lets you choose the storage level.

• Important: caching is lazy: Calling cache() or persist() does NOT immediately store anything. The data is cached the first time an action touches it.

df.cache()      # registers intent, nothing stored yet
df.count()      # this action triggers materialization → now cached
df.filter(...)  # uses cached data

• When to cache

  • You call multiple actions on the same DataFrame

  • The DataFrame is expensive to recompute (large file scan + transformations)

  • The DataFrame fits (at least partially) in memory

• When NOT to cache

  • You only use the DataFrame once

  • The DataFrame is larger than available memory (thrashing)

  • The DataFrame is derived from a streaming source

• Local mode — the entire Spark application runs on a single machine, achieving parallelism through multiple threads. No cluster required.

• Client mode — the driver remains on the client machine that submitted the application. The client machine must maintain the driver process throughout the application’s execution. Used for interactive sessions and debugging.

• Cluster mode — the driver process is launched on a worker node alongside the executor processes. The cluster manager handles all processes. The submitting machine can disconnect after submission. Used for production batch jobs.

Spark Submit

spark-submit is the command-line tool for launching Spark applications on a cluster. It packages your script, sends it to the cluster manager, and starts the Driver and Executors.

1. spark-submit connects to the Master node via the cluster URL.

2. The Master finds available Worker nodes with enough resources.

3. The Worker launches Executor processes — JVM processes that will run your tasks.

4. Your Driver (running in client mode on the machine that called spark-submit) coordinates the job, sends tasks to executors, and collects results.

5. When the job finishes, executors are released and the Driver exits.

Spark Submit Basic syntax

spark-submit \\
  --master <master-url> \\
  --deploy-mode <client|cluster> \\
  --name <app-name> \\
  --conf key=value \\
  path/to/script.py

Key flags

• --master

  • Description: Cluster manager URL

  • Example: spark://spark-master:7077

• --deploy-mode

  • Description: Where Driver runs

  • Example: client or cluster

• --name Description:

  • App name (appears in UI & History Server)

  • Example: my-etl-job

• --executor-memory

  • Description: Memory per executor

  • Example: 4g

• --executor-cores

  • Description: Cores per executor

  • Example: 2

• --num-executors

  • Description: Number of executors

  • Example: 2

• --conf

  • Description: Any Spark config

  • Example: spark.sql.shuffle.partitions=8

Executor Configuration & Static Allocation

An executor is a JVM process on a worker node. Each executor:

• Holds a fixed number of CPU cores (tasks run in parallel = number of cores)

• Has a fixed amount of memory (heap + overhead)

• Lives for the duration of the application (static allocation)

Key configs

spark.executor.instances   # how many executors to launch - eg: 2
spark.executor.cores       # CPU cores per executor: eg: 2
spark.executor.memory      # JVM heap per executor (e.g. "4g")

Executor memory - on heap

executor.memory (on-heap) is split into 3 buckets: reserved (300MB fixed), unified (~60%), and user (~40%).

• reserved memory (300MB fixed)

• unified memory is the main working area: shared between execution (compute) and storage (cache) - define by spark.memory.fraction

  • Execution (joins, shuffles) has priority: it can take memory from storage if needed.

    • 1-spark.memory.storageFraction

  • Storage (cached data) is flexible: uses free space but gets evicted under pressure.

    • spark.memory.storageFraction

• user memory is unmanaged: large custom objects here can still cause OOM. (1- spark.memory.fraction

On-heap total        : 4096 MB
  Reserved           :  300 MB  (fixed)
  Remaining          : 3796 MB
  User memory        : 3796 × 0.4 ≈ 1518 MB
  Unified memory     : 3796 × 0.6 ≈ 2278 MB
    Storage (initial): 2278 × 0.5 ≈ 1139 MB
    Execution        : 2278 × 0.5 ≈ 1139 MB

Executor memory - off heap

• Spark off-heap memory stores Spark’s internal working data outside the JVM heap to reduce garbage collection pauses and Java object overhead.

• It is mainly used for execution tasks like shuffle, sort, joins, and aggregation, plus serialized cached data used by Spark SQL/DataFrames.

• It can improve performance, but it still counts against real executor/container memory, so you must size memory overhead correctly.

Scheduling Modes

• FIFO (default): Jobs are queued in order of submission. The first job gets priority on all available resources. If it doesn’t consume everything, later jobs can start immediately — but if it uses all resources, later jobs wait until it finishes.

• Fair: Spark assigns tasks between jobs in a round-robin fashion, ensuring equal resource sharing. A short job submitted while a long job is running can start receiving resources immediately without waiting for the long job to finish.

• Pools

  • You can group jobs into named pools and control how resources are distributed between them. Each pool has three configurable properties:

    1. Scheduling mode (default: FIFO) — controls scheduling behavior within the pool. Can be FIFO or FAIR.

    2. Weight (default: 1) — controls the pool’s share of cluster resources relative to other pools. A pool with weight=2 gets double the resources of a pool with weight=1.

    3. minShare (default: 0) — a minimum number of CPU cores the scheduler always tries to satisfy for the pool before redistributing extra resources by weight.

<!-- fairscheduler.xml --> # example config
<allocations>
  <pool name="high_priority">
    <weight>2</weight>
    <minShare>2</minShare>
  </pool>
  <pool name="default">
    <weight>1</weight>
    <minShare>0</minShare>
  </pool>
</allocations>

• When to use

  • Sequential batch ETL - FIFO

  • Interactive queries (notebooks) - Fair

  • Mixed priority jobs - Fair + pools

RDD

• RDD stands for Resilient Distributed Dataset. It is Spark’s foundational data abstraction — the thing every higher-level API (DataFrames, Datasets) compiles down to under the hood.

• RDDs are lazy — nothing runs until you call an action. Remember this! this is important

RDD Characteristics

• Immutable: once created, its data never changes, new transformation = new RDD

  1. Concurrent processing - immutability keeps data consistent across multiple tasks and threads without complex locks or race conditions.

  2. Lineage and fault tolerance - each transformation records how it was derived. If a partition is lost, Spark recomputes it by replaying the lineage. Mutable data would make this impossible.

  3. Functional programming - transformations become pure functions: same input always produces the same output.

• Partitioned: the data is split into chunks called partitions (can be process by different executor)

• In-memory by default: keeps data in memory for as long as possible (avoiding disk IO)

• RDDs are lazyyyyy, this help Spark refine the plan before action.

RDD properties

• List of partitions: How the data is split for parallel processing

• Computation function: How to compute the data for each partition

• Dependencies: Which parent RDD(s) this was derived from

• Partitioner (optional): For key-value RDDs: how keys are mapped to partitions

• Preferred locations (optional): Where partitions should ideally be computed (e.g., HDFS block locations)

RDD Operation (Transformation vs Actions)

• Transformations — lazy:

  • Transformations define how data should be changed, but they do not run immediately

  • Because RDDs are immutable, every transformation produces a new RDD.

  • Examples: map(f) , filter(f) , flatMap(f)

• Actions — eager

  • Actions tell Spark to actually execute the accumulated transformations and return a result.

  • Examples: count() , collect() , take(n) , reduce(f) , saveAsTextFile(path)

Spark execution anatomy

• Job — a series of transformations triggered by an action. It represents the entire workflow from start to finish.

• Stage — a segment of a job that executes without shuffling data. A job is split into multiple stages whenever a transformation requires redistributing data across partitions (a shuffle).

• Task — the smallest unit of execution. Each stage is divided into tasks, one per partition, running in parallel across executors.

  • A partition is Spark’s unit of parallelism

  • An RDD is divided into partitions

  • Each partition is processed independently by a different executor (or thread in local mode).

• DAG — Spark builds a Directed Acyclic Graph of stages from RDD dependencies, scheduling them in topological order.

Controlling partitions

• numSlices=n can determine the number of partition you have

• Repartitioning after creation

  • repartition(n): Increase or decrease — causes a full shuffle

  • coalesce(n): Decrease only — avoids a shuffle by merging local partitions

• rule of thumb

  • Too few partitions → some executors sit idle, work is uneven

  • Too many partitions → scheduling overhead dominates, tasks finish too fast to be worth the overhead

  • A good rule of thumb: 2–4 partitions per CPU core in your cluster

Narrow vs Wide Transformations

• A shuffle is when Spark needs to redistribute data across partitions - moving records between executors over the network. It is the most expensive operation in Spark because it involves disk writes and network I/O.

• Narrow dependencies:

  • A transformation has a narrow dependency when each output partition depends on at most one input partition**. No data needs to move between executors.**

  • These transformations stay in the same stage — Spark can pipeline them together.

  • Examples: map, filter, flatMap, coalesce

• Wide dependencies:

  • A transformation has a wide dependency when each output partition may depend on multiple input partitions. Data must be shuffled across the network.

  • Wide transformations create a stage boundary in Spark’s execution plan — the current stage must complete before the next one can begin.

  • Examples: groupByKey, reduceByKey, join, distinct, repartition

Stage boundaries

• Stage boundaries happen when there’s a wide transformation, the current stage must complete before the next one can begin.

rdd2 = rdd.map(...)          # narrow — same stage
rdd3 = rdd2.filter(...)      # narrow — same stage
rdd4 = rdd3.groupByKey()     # WIDE — new stage begins here (shuffle)
rdd5 = rdd4.map(...)         # narrow — same stage as rdd4

• When a wide transformation (shuffle) appears, you’ll see extra indentation in the debug string — that marks a stage boundary:

(1) PythonRDD[...] []           ← stage 2 starts here
 |  ShuffledRDD[...] []
 +-(1) PythonRDD[...] []        ← stage 1
    |  ParallelCollectionRDD[]

Lineage and Fault Tolerance

• Spark does not replicate data across nodes the way Hadoop MapReduce does. Instead, it tracks lineage — a record of every transformation applied to produce each RDD.

• If a partition is lost (e.g., an executor dies), Spark can reconstruct it by replaying only the transformations for that partition, starting from the original data source. No data replication needed, no writing intermediate results to disk.

Computer Architecture Basics

• Core = independent processing unit in a CPU. More cores = more things at once.

• Process = a running program with its own isolated memory (e.g., a Spark executor is a JVM process).

• Thread = lightweight unit of work inside a process. Threads share the process’s RAM.

• Multithreading = multiple threads running in one process on different cores. This is how executors run tasks.

• JVM = Java Virtual Machine, the process that runs Spark code. Even PySpark sends instructions to a JVM via Py4J.

• RAM = fast, volatile memory for active data (~100 ns access). Disk = slow, persistent storage (~0.1 ms SSD).

• OOM = Out Of Memory. Spark spills to disk first; if that’s not enough, the executor crashes.

Spark Cluster Architecture - Cluster Manager & Executor

• The Spark cluster is JVM processes (driver + executors) that run on top of those machines.

• Including Cluster Manager and Executor

• The cluster manager (standalone master at :7077) brokers resources between them.

• Ports: :8080 Master Web UI, :7077 Master RPC, :18080 History Server.

• History Server = read-only UI that persists after jobs finish. Reads event logs from shared spark-events volume.

Resource Request (instances , cores, memory , maxPartitionBytes )

• A Spark Job divided into Stages

  • A Stage divided into Tasks

• Machines offer resources (eg: workers: 4 cores, 4g each). Spark app requests a subset.

• spark.executor.instances=2 → 2 executor JVMs across the cluster.

• spark.executor.cores=2 → 2 cores per executor → 2 tasks in parallel per executor.

• spark.executor.memory="2g" → JVM heap per executor. Usable = (2048-300) × 0.6 = 1048 MB.

• spark.cores.max=4 → caps total cores (standalone Spark is greedy by default).

• spark.sql.files.maxPartitionBytes=128MB → partition size → determines number of tasks. eg set by 128 * 1024 * 1024

• spark.task.cpus=1 (default, not in code) → cores per task. Tasks per executor = executor.cores ÷ task.cpus.

Partitions

• A partition = a chunk of data processed by 1 task.

• Execution hierarchy: Application → Job → Stage → Task.

• Set by spark.sql.files.maxPartitionBytes=128MB → partition size → determines number of tasks. eg set by 128 * 1024 * 1024

• Shuffle boundaries (groupBy, join, repartition) create new stages.

  • Larger partitions = fewer tasks but more memory per task and higher spill risk.

  • Smaller partitions = more tasks but lighter workload per task.

Caching

• Parquet on disk is compressed (SNAPPY, dictionary encoding). In-memory DataFrame is ~8-10x larger.

• Caching (df.cache() + df.count()) materializes data in RAM. Visible in History Server Storage tab.

• If cached data exceeds executor memory, partitions spill to disk.

Execution Plan

• Spark uses two-phase aggregation: partial aggregate (map-side) → shuffle → final aggregate (reduce-side).

  • HashAggregate = fast (in-memory hash map), used for numeric/fixed-size types.

  • SortAggregate = slower (requires sorting), used for variable-size types like Strings.

• Exchange (shuffle) = redistribute data by key across executors. Controlled by spark.sql.shuffle.partitions (default 200).

• AQE (Adaptive Query Execution) = runtime optimization that coalesces small shuffle partitions.

Hash Aggregate (Better)

• Builds a hash table where each key maps to an aggregation buffer that gets updated in-place.

• No sorting needed — just hash the key and update the buffer

• Requires a hash table in memory (can be large)

Sort Aggregate (Fall back)

• Sorts the data by the grouping key first, then iterates over it. When the key changes, the previous group is complete.

• Requires sorting the data first (expensive)

• Memory-efficient: only holds one group in memory at a time

• When does Spark fall back on SortAggregate?

  • Case 1: Hash table doesn’t fit in memory: If the number of unique keys is very large, the hash table grows too big for the executor’s memory.

  • Case 2: Non-fixed-size data types in the aggregation buffer: For HashAggregate, Spark uses UnsafeRow format — a compact, off-heap memory representation that bypasses JVM garbage collection (part of Project Tungsten). This format works by allocating fixed-size memory slots for each aggregation buffer. If the column doesn’t have fixed-size memory, it doesn’t work.

I’m an Analytics Engineer at Insurify, and I’m borderline obsessed with tech and productivity tools.

Not in the fake “morning routine, optimized water bottle, 17-tab Notion dashboard” way. I mean the kind of obsession that comes from doing real work every day and getting annoyed by tiny bits of friction that keep slowing you down. A shortcut you have to remember manually. A formatter you forget to run. A command you type over and over. A workflow that should take one keystroke but somehow takes five.

Over time, I’ve built a stack of small systems that make my day faster, cleaner, and less mentally taxing. Better keybindings in Cursor and VS Code. Shell aliases that remove repetitive commands. CLI tools that actually make the terminal enjoyable to use. Little workflow upgrades that seem minor on their own, but add up hard over weeks and months.

This is a look into that setup: the shortcuts, configs, and tools I actually use, why I use them, and where they save me time. Nothing theoretical. Just practical stuff that makes shipping code and moving through projects less annoying.

If you care about developer ergonomics, terminal workflows, and cutting useless friction out of your day, this is for you.

Keybinding.json

Pushing code only to realize you haven’t run sqlfluff/ black is annoying. The keybinding.json file solves that problem. The keybinding.json file is a configuration file used in VS Code/Cursor to customize keyboard shortcuts. It allows you to override default keyboard bindings or create new ones to enhance your development workflow. Example such as:

• Terminal integration shortcuts (Cmd+1) — switch around easily from panel to terminal

• File formatting for Python (using Black) and SQL (using SQLFluff)

• Composer mode activation (cmd+i)

• Combined save and format operations (cmd+shift+s)

[
    {
        "key": "cmd+i",
        "command": "composerMode.agent"
    },
    {
        "key": "cmd+1",
        "command": "workbench.action.terminal.focus"
    },
    {
        "key": "cmd+1",
        "command": "workbench.action.focusActiveEditorGroup",
        "when": "terminalFocus"
    },
    {
        "key": "cmd+shift+c",
        "command": "copyRelativeFilePath",
        "when": "editorFocus"
    },
    {
        "key": "cmd+shift+f",
        "command": "workbench.action.tasks.runTask",
        "args": "SQLFluff Fix Current File",
        "when": "editorTextFocus && resourceExtname == .sql"
    },
    {
        "key": "cmd+shift+l",
        "command": "workbench.action.tasks.runTask",
        "args": "SQLFluff Lint Current File",
        "when": "editorTextFocus && resourceExtname == .sql"
    },
    {
        "key": "cmd+shift+b",
        "command": "workbench.action.terminal.sendSequence",
        "args": {
            "text": "black ${file}\\n"
        },
        "when": "editorTextFocus && resourceExtname == .py"
    },
    {
        "key": "cmd+s",
        "command": "workbench.action.files.save"
    },
    {
        "key": "cmd+shift+s",
        "command": "runCommands",
        "args": {
            "commands": [
                {
                    "command": "workbench.action.terminal.sendSequence",
                    "args": {
                        "text": "sqff ${file}\\nsqfl ${file}\\n"
                    }
                },
                {
                    "command": "workbench.action.files.save"
                }
            ]
        },
        "when": "editorTextFocus && resourceExtname == .sql"
    },
    {
        "key": "cmd+shift+s",
        "command": "runCommands",
        "args": {
            "commands": [
                {
                    "command": "workbench.action.terminal.sendSequence",
                    "args": {
                        "text": "black ${file}\\n"
                    }
                },
                {
                    "command": "workbench.action.files.save"
                }
            ]
        },
        "when": "editorTextFocus && resourceExtname == .py"
    },
    {
        "key": "cmd+shift+e",
        "command": "workbench.action.terminal.sendSequence",
        "args": {
            "text": "python /Users/user/insurify/dbt/tests/completeness/model_yaml_matching_local.py\\n"
        }
    },
    {
        "key": "ctrl+z",
        "command": "workbench.action.terminal.sendSequence",
        "args": {
            "text": "\\u001f"
        } // sends Ctrl+/ (Undo in readline)
    },
    {
        "key": "ctrl+shift+z",
        "command": "workbench.action.terminal.sendSequence",
        "args": {
            "text": "\\u001f"
        } // also map Redo to the same undo-sequence
    }
]

Example usage

In my development workflow, one of the most time-saving keyboard shortcuts I’ve configured is cmd+shift+s. This powerful shortcut automates code formatting and saving in a single keystroke.

The shortcut is configured to perform different actions based on the file type:

• For Python files (.py): It runs Black formatter and then saves the file

• For SQL files (.sql): It runs SQLFluff formatter and then saves the file

This automation eliminates the repetitive task of manually formatting code before saving, ensuring consistent code style across projects.

Alias

Aliases in a .zshrc file are custom shortcuts for longer commands, making your terminal experience more efficient and personalized. They follow the syntax alias shortcut="original command". For example, with an alias like alias ll="ls -la", typing ll will execute the longer ls -la command.

These aliases save significant time by reducing keystrokes for repetitive tasks and standardizing workflows. You can add new aliases at any time by editing your .zshrc file and running refresh (which itself is an alias for source ~/.zshrc).

# docker alias
#------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
alias docker_clean="docker system prune -a && docker volume prune && docker image prune -a"
alias docker_start_mwaa="docker start 0f74edd6386b && docker start c3855cb70002"
alias docker_stop_all="docker stop $(docker ps -q)" 
alias docker_remove_all_images="docker rmi $(docker images -q)"

#-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

# dbt alias

## dbt run 
dbt_run() {
  dbt run -s "$@" -f
}

dbt_run_operation() {
  dbt run-operation "$1" --args "${2:-{}}"
}
alias dbt_run_defer="dbt run --defer --state prod_dbt_artifacts -s"
alias dbt_run_defer_favor_state="dbt run --defer --state prod_dbt_artifacts --favor-state -s"
alias dbt_run_changed="dbt run --defer --state prod_dbt_artifacts --select state:modified+"

## dbt test 
alias dbt_test="dbt test -s"
alias dbt_test_defer="dbt test --defer --state prod_dbt_artifacts -s"
alias dbt_test_changed="dbt test --defer --state prod_dbt_artifacts --select state:modified+"

## dbt build 
alias dbt_build_changed="dbt build --defer --state prod_dbt_artifacts --select state:modified+"
alias dbt_build_defer="dbt build --defer --state prod_dbt_artifacts -s"
alias dbt_build_defer_favor_state="dbt build --defer --state prod_dbt_artifacts --favor-state -s"
alias dbt_build_all="dbt build --defer --state prod_dbt_artifacts --select + --resource-type=model"

## dbt clone
alias dbt_clone="dbt clone --state prod_dbt_artifacts -f --resource-type=model -s"

## dbt seed
alias dbt_seed="dbt seed -s"

## dbt parse
alias dbt_parse_fix="dbt parse --no-partial-parse"

## Run freshness checks
alias dbt_freshness="dbt source freshness"

## get_ manifest
alias get_dbt_manifest="cd ~/insurify/dbt/ && curl -L -o json path" 

find_dir() {
  rg --files | xargs -n1 dirname | grep -i "$1" | sort -u
}

#-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# sql fluff alias
alias sqfl="sqlfluff lint"
alias sqff="sqlfluff fix"

#-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# .zshrc alias
alias refresh="source ~/.zshrc"
export PATH="$PATH:/Users/minh.pham/.local/bin"

#-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# .git alias

git config --global alias.lg "log --color --graph --pretty=format:'%Cred%h%Creset -%C(yellow)%d%Creset %s %Cgreen(%cr) %C(bold blue)<%an>%Creset' --abbrev-commit"
alias git_delete_merged_branches='git checkout -q master && git for-each-ref refs/heads/ "--format=%(refname:short)" | while read branch; do mergeBase=$(git merge-base master $branch) && [[ $(git cherry master $(git commit-tree $(git rev-parse "$branch^{tree}") -p $mergeBase -m _)) == "-"* ]] && git branch -D $branch; done'
alias git_branch_clean="git branch --merged | grep -v master | xargs git branch -d "
gitall() {
    git add .
    if [ "$1" != "" ] # or better, if [ -n "$1" ]
    then
        git commit -m "$1"
    else
        git commit
    fi
    git push
}

# Use eza instead of ls
alias ls="eza --group-directories-first"
alias ll="eza -l --group-directories-first"
alias la="eza -la --group-directories-first"

CLI Tools

For me, CLI tools are where productivity gains become real. I use tools like Warp, zoxide, eza, Oh My Zsh, and lazygit because they make the terminal faster, cleaner, and way less painful to work in all day. They cut down repetitive commands, make navigation easier, and turn common tasks like moving between projects, checking files, or handling Git into something that feels almost automatic. None of them are necessary, but once they’re part of your workflow, it’s hard to go back.

Warp: AI CLI Tools

Warp is a modern terminal with AI capabilities that enhances your command-line experience.

Installation:

# macOS (using Homebrew)
brew install --cask warp

Features:

• AI command suggestions

• Command history search

• Improved terminal UI with blocks and workspaces

Zoxide

Zoxide is a smarter cd command that helps you navigate directories faster by tracking your most used directories.

Installation:

# macOS (using Homebrew)
brew install zoxide

# Add to shell (already configured in your .zshrc)
eval "$(zoxide init zsh --cmd cd)"

Usage:

• Use cd as normal (it’s configured to replace cd in your .zshrc)

• Jump to frequent directories by typing part of the path: cd proj

• So imagine you’re in the dbt repo, you wanna quickly open up airflow, just “cd airflow” even though airflow doesn’t exist in the current directory, zoxide will find it

Eza

Eza is a modern replacement for the ls command with improved colors and features.

Installation:

# macOS (using Homebrew)
brew install eza

Usage:

• Already configured as aliases in your .zshrc

• ls: Lists files with directories first

• ll: Long listing format

• la: Shows all files including hidden ones

Oh My Zsh

Oh My Zsh is a framework for managing your Zsh configuration with themes and plugins.

Installation:

sh -c "$(curl -fsSL <https://raw.githubusercontent.com/ohmyzsh/ohmyzsh/master/tools/install.sh>)"

Usage:

• Already configured in your .zshrc with Powerlevel10k theme

• Add plugins by editing the plugins array in .zshrc

# Path to your Oh My Zsh installation.
export ZSH="$HOME/.oh-my-zsh"

ZSH_THEME="jonathan"

source $ZSH/oh-my-zsh.sh

Jonathan Theme

Configuration:

Your .zshrc is configured with:

ZSH_THEME=”jonathan”

If you want to switch themes later, just update the ZSH_THEME value in your .zshrc and run:

refresh

If you want, I can also rewrite the full CLI Tools section so it stays consistent with the fact that you use Jonathan theme instead of Powerlevel10k.

Lazygit

Lazygit is a simple terminal UI for git commands, making git operations more visual and intuitive.

Installation:

# macOS (using Homebrew)
brew install lazygit

Usage:

• Run lazygit in any git repository

• Navigate with arrow keys, perform actions with keyboard shortcuts

• Press ? for help

Lzop

Lzop is a file compressor similar to gzip but optimized for speed.

Installation:

# macOS (using Homebrew)
brew install lzop

Usage:

• Compress: lzop filename

• Decompress: lzop -d filename.lzo

• View compressed file: lzop -c -d filename.lzo

Keyboard Shortcuts

Keyboard shortcuts have probably been the biggest game changer in my workflow because they let me move around without friction. Instead of constantly reaching for the mouse or breaking focus, I can jump between files, the terminal, the editor, and search almost instantly. That speed sounds small, but it adds up fast when you’re coding all day. Once the most common actions are mapped to muscle memory, everything feels smoother, faster, and a lot less interruptive.

Cursor/VS Code Shortcuts

• Command Palette: cmd+shift+p - Access all commands and settings

• Quick Open: cmd+p - Quickly open files in your project

• Toggle Sidebar: cmd+b - Hide or show the sidebar

• Split Editor: cmd+\\ - Split the current editor into two panes

• Copy File Name: cmd+alt+f - Copy the current file name

• Settings: cmd+, - Open settings

• Find & Replace: cmd+shift+h - Find and replace across entire directory

• Select Line: cmd+l - Highlight the current line

• Comment/Uncomment: cmd+/ - Toggle comments for selected lines

• Terminal: `ctrl+`` - Open integrated terminal

Terminal

• Delete Word: ctrl+w - Delete the word before the cursor

• Clear Line: ctrl+u - Clear the entire line before the cursor

• Clear Screen: ctrl+l - Clear the terminal screen

• End of Line: ctrl+e - Move cursor to end of line

• Beginning of Line: ctrl+a - Move cursor to beginning of line

• Search History: ctrl+r - Search through command history

• Move Cursor Back: ctrl+b - Move cursor one character back

• Move Cursor Forward: ctrl+f - Move cursor one character forward

• Stop Current Process: ctrl+c - Terminate the current running command

• Beginning of Word: option+b (or alt+b) - Move cursor to beginning of current word

• End of Word: alt+f - Move cursor to end of the current word

• Precise Cursor Movement: hold option + click - Move directly to clicked position within command line

• Tab Completion: tab - Complete commands and filenames

• Command History: up - Navigate through previous commands

Other useful commadn

• List Processes: ps -ax - Show all active processes

• Git Branch Cleanup: git fetch --prune --all - Remove deleted branches from git checkout autocomplete

• Pipe Command: | - Send output of first command as input to second command (e.g., ps -ax | grep vpn)

Outro

That’s pretty much my current setup. None of these tools or shortcuts are revolutionary on their own, but together they make my workflow faster, smoother, and a lot less annoying. Hope this helps, and maybe gives you a few ideas for cleaning up your own setup.

Today I want to tackle a question I hear all the time: “I don’t know what type of SQL to learn. There are so many – MySQL, PostgreSQL, Oracle, SQL Server!”

Bro, just learn ANSI SQL.

Period. Specifically, start with ANSI SQL, the standard. Why? Because once you know the fundamentals of SQL, you can work with any database engine out there. Let’s break down why standard SQL is the way to go, in a language we can all understand (and maybe with a joke or two along the way).

The SQL Dialect Dilemma (Too Many Flavors!)

When I first dipped my toes into data, I was bewildered by the buffet of “SQLs” on the menu. Oracle SQL, MySQL, T-SQL, PL/SQL, PostgreSQL… it felt like I had to pick the one right database religion. 😵 Spoiler: I didn’t. Instead, I learned SQL basics – SELECT, JOIN, WHERE, GROUP BY – the stuff that’s the same everywhere. And guess what? It didn’t matter that my first database was MySQL and later I used Redshift at work. The core language was the same.

Think of SQL dialects like ice cream flavors. Sure, chocolate chip vs. rocky road have slight differences (one’s got chocolate bits, the other marshmallows), but they’re all ice cream at heart. As one wise commenter put it, SQL databases are “different flavors of the exact same thing – think vanilla ice cream with different toppings.” If you know one flavor, you can pick up another easily. In other words: SQL is SQL. Don’t overthink the flavor; focus on the ice cream. 🍨

What Exactly Is ANSI SQL?

ANSI SQL (sometimes called “Standard SQL”) is the official standard language for relational databases, defined way back by the American National Standards Institute. It’s basically the agreed-upon core of SQL that most databases implement. Think of ANSI SQL as the grammar rules of the SQL language. Just like English has grammar that most writers follow (even if some have unique slang), databases follow ANSI SQL with their own twists.

No database implements the standard 100% perfectly (vendors love their proprietary features). Reasons vary: they add new features not in the standard yet, or they just want to lock you into their ecosystem. Examples of dialects (i.e. those twists on standard SQL) include:

• T-SQL (Transact-SQL) – Microsoft SQL Server’s flavor, with extra goodies for stored procedures.

• PL/SQL (Procedural SQL) – Oracle’s flavor, integrates procedural programming.

• PL/pgSQL – PostgreSQL’s procedural extension of SQL.

• MySQL’s SQL – pretty close to standard, with a few quirks (e.g. LIMIT clause for top-N results, which other databases might handle with different syntax).

• Redshift/BigQuery/Snowflake - cloud data-warehouse syntax are really close with ANSI-SQL, with a touch of extra function like QUALIFY or Semi-structured data functions (PARSE_JSON, TO_VARIANT)

Each of these has some non-standard extensions or functions. But here’s the kicker: the foundation – creating tables, inserting data, querying with SELECT/JOIN – all of that is standardized. If you learn ANSI SQL first, you’ve covered 80-90% of what you’ll use day-to-day in any SQL database. The remaining 10-20% are like dialect-specific idioms you can pick up as needed.

All SQL Dialects = Same Core, Small Tweaks

Let’s address the fear: “What if I invest time in SQL X and later need SQL Y? Will my knowledge be useless?” Absolutely not! The core concepts carry over. In my career, I’ve bounced between MySQL, PostgreSQL, Redshift, and DuckDB (and yes, those are very different under the hood – from big cloud warehouses to tiny in-process DBs). Yet I never felt like I had to re-learn SQL from scratch for each. I might have to adjust to a different function name or date format function, but a SELECT is a SELECT everywhere.

In fact, SQL skills are highly transferable across systems. The syntax and commands you use in ANSI SQL are widely recognized across MySQL, SQL Server, Oracle, PostgreSQL – you name it. It’s like knowing how to drive: once you can drive a sedan, you can drive an SUV or a pickup with a bit of practice. The buttons might be in different places, but the steering wheel and pedals work the same. 🚗

To give a concrete example: at my company (Insurify), we recently considered migrating our data warehouse from Amazon Redshift to Snowflake. Big change under the hood, but our team wasn’t terrified. Why? Because our analysts already speak fluent SQL. The flavor might shift (Redshift has some PostgreSQL-ish traits; Snowflake is ANSI SQL with its own functions), but our analytical queries and skills carry over with minimal changes. We spend our energy tuning performance or learning new features, not relearning how to write SELECT statements.

Why Starting with Standard SQL Makes Sense

If you’re just beginning, here’s why I firmly believe you should start with ANSI/standard SQL:

• Widely Applicable Skills: You’ll build a foundation that works on any relational database. Your knowledge isn’t tied to one vendor. Tomorrow if you switch from, say, SQLite to Oracle, your skills come with you.

• Core Concepts First: Focusing on standard SQL forces you to master the basics – querying data (DQL), updating/inserting (DML), creating tables (DDL). These are the bread-and-butter of any database work. Advanced, platform-specific tricks can come later.

• Easier to Learn Dialect Nuances: Once you know the core, learning a dialect’s quirks (like T-SQL’s @@IDENTITY or Oracle’s DECODE() function) is way easier. It’s just learning a new idiom on top of a language you already speak.

• Transferable in Jobs and Interviews: Employers rarely care which SQL dialect you know; they care that you can use SQL to get answers. Mastering standard SQL showcases logical thinking and data savvy. It’s an “industry standard” skill that proves you can adapt to whatever database the job uses. In technical interviews too, you’ll often be asked to write SQL without focusing on a specific dialect – they expect you to use standard SQL syntax.

Now, does this mean vendor-specific knowledge is useless? Not at all. Eventually, if you work with a database like Oracle or SQL Server, you’ll learn their extensions (like Oracle’s analytic functions or SQL Server’s T-SQL tricks). But that’s the icing, not the cake. Get the cake right (ANSI SQL), then worry about the icing later.

My Two Cents on Getting Started

1. Pick One Database to Practice: I recommend an easy one like PostgreSQL or MySQL for beginners – both are free and widely used. Even DuckDB or SQLite are fantastic for learning; they’re lightweight and require almost no setup (you can query a CSV in seconds). The key is to start writing queries. Don’t agonize over choosing “the perfect database.” As one experienced developer noted about their career using Access, SQL Server, Teradata, and BigQuery: “They each have their own nuances, but if you know one, it’s pretty easy to pick up the others… focus on getting good with SQL, then apply that to different DBMS”. In short: just start somewhere, anywhere.

2. Learn by Doing: Spin up a small sample database and practice the basics. Create a table, insert some data, write SELECTs with various filters and joins. There’s no substitute for getting your hands dirty. You’ll quickly see that SELECT name FROM customers WHERE city=’Boston’ works the same on any SQL-compliant database.

3. Solve some Leetcode: Practice by solving Leetcode question, divide up questions into different topics and solve them gradually

4. Don’t Get Lost in the Details: It’s easy to fall into a rabbit hole of differences (Yes, MS SQL Server uses TOP 10 instead of LIMIT 10, Oracle uses FETCH FIRST 10 ROWS ONLY – whoop-de-doo). These are minor syntax tweaks. Google them when you encounter them. Focus your energy on understanding what your query should do – the logic, not memorizing every dialect’s keywords.

5. Use Resources That Teach Standard SQL: Many online tutorials (SQLBolt, Mode tutorials, Khan Academy, etc.) teach SQL in a vendor-neutral way or let you choose which dialect to practice on. Start with those. Once you’re comfortable, you can explore specifics of, say, PL/SQL if your job requires Oracle, but you’ll find it much easier then.

Conclusion: SQL is SQL – Just Start

At the end of the day, don’t let the multitude of SQL flavors paralyze you. The biggest threat to your success isn’t picking the “wrong” database to learn on – it’s never starting at all. SQL is a foundational skill in the data world, and the fundamentals are universal. Learn the standard syntax and you’ll gain confidence knowing you can step into any SQL environment and find your way. As I often say, a good data engineer or analyst is defined by how well they understand the data and the queries – not by memorizing obscure dialect syntax.

So grab a scoop of that sweet, standard ANSI SQL. 🍦 Once you’ve savored it, you can try other flavors as needed – but you’ll realize they all taste familiar. Happy querying, and until next time, keep it simple and standard!

– Mark

Introduction

If you’ve worked with Python for a minute, you’ve met pip – the tried-and-true package installer that’s been around forever. But in recent years, new tools like Poetry and uv have arrived on the scene, each promising to make Python project management a smoother ride. I remember the confusion when I first heard about Poetry (“why not just use pip?”) and then again when the buzz around uv started (“do we really need another tool?”). In this article, I’ll break down these three approaches to Python dependencies and environments, in plain English and with a dose of personal experience. By the end, you’ll know when to stick with pip, when to embrace Poetry, and why the new “uv” might just blow your socks off.

The Project Scenario: a simple data app

To make this comparison concrete, let’s consider a tiny project I’ll call my_data_app. It’s a simple data analysis app that uses Pandas for data manipulation and DuckDB for running SQL queries on the data. (DuckDB is like a lightweight in-process database that plays nicely with Pandas DataFrames – perfect for local analytics.) We’ll also use Pytest for writing a couple of tests.

So our project needs three main packages: pandas, duckdb, and pytest. The code itself might look something like this (just to set the stage, don’t worry about the details too much):

# src/main.py (for example)
import pandas as pd
import duckdb

def load_data():
    # Create a sample DataFrame
    df = pd.DataFrame({”id”: [1, 2, 3, 4], “value”: [10, 20, 30, 40]})
    return df

def query_data(df: pd.DataFrame):
    con = duckdb.connect()  # in-memory DuckDB instance
    con.register(”mytable”, df)           # make the DataFrame queryable
    result = con.execute(”“”
        SELECT id, value, value * 2 AS value2
        FROM mytable
        WHERE value >= 20
        ORDER BY id DESC;
    “”“).df()
    return result

if __name__ == “__main__”:
    data = load_data()
    print(”Input:\\n”, data)
    output = query_data(data)
    print(”Query result:\\n”, output)

This little script creates a DataFrame, then uses DuckDB’s SQL engine to filter and augment the data. Nothing too fancy – but enough to illustrate how pip, Poetry, and uv handle project setup differently. Let’s dive into each tool using this project as an example.

1. pip – The OG Approach

pip is the OG of Python package management. It’s the standard installer that comes with Python (via ensurepip) and it’s extremely straightforward: you want a package, you run pip install <package> and it fetches it from PyPI. Done. No bells or whistles. And sometimes, that’s all you need.

Using pip for our my_data_app example might go something like this: we’d first create a virtual environment for the project, activate it, then install our packages:

# 1. Create a virtual environment (isolated Python installation)
python3 -m venv .venv

# 2. Activate the virtual environment
source .venv/bin/activate    # (On Windows, use “.venv\\\\Scripts\\\\activate”)

# 3. Install the required packages inside this venv
pip install pandas duckdb
pip install pytest

# 4. (Optional) Freeze the exact versions to a requirements file
pip freeze > requirements.txt

A quick rundown for newcomers: the virtual environment step is critical. A virtual environment is like a sandboxed Python installation just for this project – it has its own interpreter and its own site-packages directory where installed libraries go. When you activate it, your shell’s PATH is modified so that python and pip refer to the ones inside .venv. This keeps your project’s libraries isolated from your system Python, preventing version conflicts (and “it works on my machine” headaches). You can see that the .venv folder gets created in our project directory; it contains a bin (or Scripts on Windows) folder with the Python executable and a lib folder with all the packages we installed. When we’re done, we can deactivate the venv and go back to our system’s Python.

After installing pandas, duckdb, and pytest with pip, I used pip freeze > requirements.txt to generate a requirements file. This requirements.txt is just a snapshot of all the packages in our environment (including dependencies of the ones we installed) with pinned versions. It might look like:

duckdb==1.4.1
numpy==1.26.0      # numpy is a dependency of pandas and duckdb
pandas==2.3.3
pytest==8.4.2
python-dateutil==2.9.0.post0   # pulled in by pandas
pytz==2023.3               # pulled in by pandas

The idea is that if someone else wants to replicate this environment, they can use the same Python version, create their own venv, and run pip install -r requirements.txt to get those exact versions. In practice, this works, though pip doesn’t actively “lock” sub-dependency versions unless you froze them. It’s a bit manual: whenever you add or update a package, you’re supposed to update the requirements file (often by freezing again). There’s no concept of separate “dev” vs “prod” dependencies in a plain requirements.txt – you might maintain multiple files (like requirements-dev.txt) if you want to differentiate.

Pros of pip: It’s simple and ubiquitous. Everyone using Python has pip, and every Python tutorial starts with pip install .... There’s almost nothing new to learn. For small projects or quick scripts, pip + venv gets the job done with minimal fuss.

Cons of pip: By itself, pip is pretty bare-bones for larger projects. You have to remember to set up and activate the venv (pip won’t do it for you). You have to manage that requirements.txt manually. There’s no built-in resolution of conflicting requirements beyond pip’s own dependency solver (which operates one pip install at a time), and no lock file to guarantee that two installs a week apart get the exact same set of sub-dependencies. In short, pip on its own is lacking some of the “project management” features that bigger tools provide.

(Side note: pip has gotten some improvements over the years – for example, pip install can now use a pyproject.toml for build requirements, and there are third-party tools like pip-tools to help with lock files – but in the context of this article, pip refers to the basic usage as above.)

2. Poetry – The All-in-One Project Tool

After wrestling with pip and manual venvs for a while, I discovered Poetry, and it felt like leveling up. Poetry is often described as a Python project manager or a higher-level dependency tool. It still uses pip under the hood for installing, but it wraps a lot of functionality to provide a more streamlined experience for building and maintaining projects. In essence, Poetry handles dependency resolution, lock file generation, virtual environment management, and packaging all in one. It turns your project into something that’s easier to share and reproduce by anyone.

How would we use Poetry for my_data_app? First, you’d install Poetry itself (for example via pipx install poetry, so that you have a poetry command available globally). Then in your project folder, instead of manually making a venv and writing a requirements file, you do:

poetry init

This command walks you through creating a pyproject.toml file, which is the central configuration for your project. You’d give your project a name, a version, a description, and then list its dependencies (and their version constraints). You can also specify your Python version. For our example, the pyproject.toml created by Poetry might include:

[tool.poetry]
name = “my_data_app”
version = “0.1.0”
description = “A simple data app using pandas and DuckDB”
authors = [”Your Name <you@example.com>”]

[tool.poetry.dependencies]
python = “^3.11”       # means Python 3.11.x
pandas = “^2.0”        # means pandas 2.x
duckdb = “^1.4”        # means duckdb 1.4.x

[tool.poetry.group.dev.dependencies]
pytest = “^8.0”        # dev dependency (not needed in production)

[build-system]
requires = [”poetry-core”]
build-backend = “poetry.core.masonry.api”

Don’t worry about the exact TOML syntax – the key point is that Poetry has now recorded what our project is and what it needs. Notice it has separate sections for main dependencies vs dev dependencies (like pytest). This is already nicer than a single requirements.txt because we have clarity on what’s needed for running the app versus what’s only for development/testing. Poetry will also create a lock file called poetry.lock once we install everything.

Speaking of installing: after poetry init, we run:

poetry install

And just like that, Poetry will do a few things:

• Resolve all the dependency versions that fit the criteria (e.g., find the latest pandas 2.x, duckdb 1.4.x, etc. that are mutually compatible).

• Create a new virtual environment for the project (unless one already exists). By default, Poetry will make a venv in a cache directory (usually under ~/.cache/pypoetry/virtualenvs/ with a name like my_data_app-abcdefgh-py3.11), but you can configure it to create the venv in your project folder if you prefer.

• Install the exact versions of packages it resolved, into that virtual environment.

• Generate the poetry.lock file pinning those exact versions (including sub-dependencies).

From then on, anyone who checks out your project can just run poetry install and they’ll get the same versions thanks to the lock file. Poetry’s lock file ensures reproducible installs – a cornerstone for consistent environments. As the Poetry documentation puts it, the lock file “locks dependency versions, ensuring reproducible environments”realpython.comrealpython.com. It should be committed to your version control.

Using Poetry day-to-day is pretty nice. You don’t even need to explicitly activate the venv it creates. If you want to run something in the project’s environment, you can do poetry run <command> (for example, poetry run python src/main.py or poetry run pytest). That automatically uses the project’s venv. If you prefer an interactive session, poetry shell will spawn a new shell with the venv activated (so your prompt shows (my_data_app-py3.11) or similar). Essentially, Poetry handles the venv for you; you just have to remember to use poetry run or poetry shell when working on the project.

Want to add a new package? poetry add flask (for a normal dependency) or poetry add --group dev black (to add, say, Black as a dev-only tool). This updates the pyproject and lock file in one go. Need to update packages? poetry update will refresh everything within the allowed version constraints you set. And when it’s time to ship your code, Poetry can even build distributable artifacts (wheels/sdists) with poetry build, and publish to PyPI with poetry publish – all using the info from pyproject.toml. It’s a one-stop shop for project management.

Pros of Poetry: It brings order and structure. You get a lock file for guaranteed repeatability, environment isolation without the manual steps, easy separation of dev vs prod requirements, and built-in support for packaging and publishing. It’s fantastic for applications and services where you want to be confident that what runs in production is exactly what you tested locally. The pyproject.toml is also becoming a standard place to configure Python projects (PEP 518 and PEP 621), so Poetry is aligned with modern Python packaging standards (albeit using its own [tool.poetry] sections for specifics). Many teams (including mine) have adopted Poetry to avoid the “it works on my machine” syndrome and dependency hell. As one tutorial succinctly says, “Poetry is a dependency manager that ensures consistent package versions and simplifies Python project setup”realpython.com.

Cons of Poetry: There’s a bit of a learning curve coming from pip. It’s an additional tool you have to install and get used to. While not complicated, it abstracts a lot – which can occasionally obscure what’s going on (for example, if something’s wrong with your venv, Poetry manages it behind the scenes). The dependency resolver, while usually fine, can be slower than pip for complex setups. And in some edge cases, Poetry’s stricter structure can be confusing – like needing to name your packages and structure your project in a certain way. It’s also a fairly heavy tool (written in Python, with a bunch of its own dependencies), whereas pip is part of Python itself.

A little gotcha (and fix): The first time I used Poetry on a simple script project, I ran poetry install and got a warning: “The current project could not be installed: No file/folder found for package …”. Huh? Everything looked fine to me. What I learned is that Poetry was trying to install my project as a package (because by default it assumes you’re making a library or app that should be importable). It couldn’t find a proper Python package folder that matches the project name, hence the warning. There are a couple of ways to fix this:

• If you’re just using Poetry for dependency management and don’t actually need to import your own project as a module, you can disable the project packaging. Add package-mode = false under [tool.poetry] in pyproject.toml, which tells Poetry “don’t bother trying to install this project as a library.” antoine-does-ai.com.

• Alternatively, you can run poetry install --no-root, which installs the dependencies but skips installing the project itself.

• The more “proper” fix (if you do want your project to be an installable package) is to structure your project with a package directory. For example, if your project is named my_data_app, create a my_data_app/ folder in src/ with an __init__.py inside. Or explicitly tell Poetry where your packages are in pyproject.toml. For instance:

[tool.poetry]
name = “my_data_app”
version = “0.1.0”
packages = [{ include = “my_data_app”, from = “src” }]

• This way, Poetry knows your code lives under src/my_data_app/. If those names don’t align, you’ll get the “no file/folder for package” errorantoine-does-ai.comantoine-does-ai.com.

Poetry’s strictness with project structure can be annoying for simple one-off projects, but it comes from a good place: treating your project like a proper package can pay off in the long run (you can easily install it elsewhere, or publish it). That said, if I’m just hacking together a quick analysis script, I’ll sometimes use Poetry’s --no-root option or set package-mode=false to keep things simple.

3. uv – The New Speed Demon

Now let’s talk about uv – the newest tool of the bunch. If Poetry felt like a level-up, uv feels like an experiment from the future that’s quickly becoming production-ready. It’s built by the folks at Astral (the same team behind the ridiculously fast Ruff linter), and the big selling point is speed. In fact, uv is written in Rust, and its goal is to be an extremely fast, all-in-one Python package and project manager (astral.sh). Think of uv as trying to combine the best of pip, virtualenv/venv, and Poetry into one unified tool – and doing it much faster. The Astral team even described uv as a step toward a “Cargo for Python” (Cargo is Rust’s package manager), meaning one tool to manage environments, dependencies, and builds.

When they first released uv (early 2024), they benchmarked it and the numbers were eye-popping: uv was reported to be 8–10× faster than pip/pip-tools for resolving dependencies, and about 80–115× faster with a warm cache. Yes, you read that right – up to eighty times faster in some scenarios. How? Basically by using a highly optimized dependency resolver and installer (Rust shines at this), caching wheels aggressively, and even using some filesystem tricks like hardlinks to avoid redundant copies. It’s also distributed as a single binary, so there’s no interpreter startup overhead and no need to have pip already working. It’s fair to say uv’s performance focus is obsessive. (As a side effect, uv’s name might evoke ultraviolet – fitting for something this fast, and indeed they hinted that “uv” can stand for ultraviolet or universal, while conveniently being a short name available on PyPI (lwn.net)

Using uv on our example project will feel a bit like a hybrid of pip and Poetry workflows. First, you’d install uv itself – for example via the installer script

curl -sSL <https://astral.sh/uv/install.sh> | sh 

or with Homebrew, or even pipx. Once you have the uv command, here’s how you’d set up my_data_app:

1. Create a virtual environment for the project using uv’s own command:

uv venv .venv

1. This is analogous to python -m venv .venv, but it’s actually even faster (the uv devs built their own venv creator that’s about 80× faster than Python’s, by doing it in Rust). By default, uv venv .venv will pick the current Python version to create an environment in the .venv folder. If you wanted a specific Python, you could do uv venv .venv --python=3.11 for example.

2. Declare your project’s dependencies in a pyproject.toml. Unlike Poetry, uv embraces the PEP 621 standard for project metadata. So instead of [tool.poetry] sections, you use the generic [project] section that other tools (like Flit or Hatch) also use. For example, our pyproject.toml for uv might look like:

[project]
name = “my_data_app”
version = “0.1.0”
description = “A simple data app using pandas and DuckDB”
requires-python = “>=3.11”
dependencies = [
    “pandas>=2.0,<3.0”,
    “duckdb>=1.4,<2.0”
]

[project.optional-dependencies]
dev = [ “pytest>=8.0,<9.0” ]

[build-system]
requires = [”poetry-core>=1.5.0”]
build-backend = “poetry.core.masonry.api”

[tool.uv.dependency-groups.dev]
include = [”dev”]

1. A lot of that is boilerplate (and uv can actually auto-generate this via commands like uv init). The key parts: under [project] we listed our main dependencies and Python version. We also defined an “optional dependency group” named “dev” that includes pytest – this is how we specify dev requirements in a standard way. The [tool.uv.dependency-groups.dev] section is a uv-specific hint telling uv that when we say “install dev dependencies,” it should look at the “dev” group. Essentially, uv recognizes that dev group and can install it on demand.

2. Install (sync) the environment: Once the pyproject is set up, you run:

uv sync

1. This is uv’s equivalent of pip install or poetry install. It will resolve versions for all your dependencies (very quickly, thanks to Rust) and then install them into the .venv we created. It also writes out a lock file called uv.lock. The lock file serves the same purpose as Poetry’s lock: capturing exact versions and hashes for reproducibility. The format is a bit different (TOML-based, with sections for each package including the exact URL and hash of the wheel or source distribution). One interesting thing about uv.lock is that it’s platform-aware – it can include multiple wheels for different platforms. For example, the lock entry for DuckDB might list a wheel for macOS/ARM, one for macOS/x86, one for manylinux, etc., along with their hashesastral.shastral.sh. This means the same lock file can be used on different OSes, and uv will pick the appropriate wheel. Pretty cool for cross-platform teams.

   After uv sync, you’ll have your packages installed and a lock file created. If a teammate pulls the repo, they just run uv sync and uv will recreate the venv and install everything exactly as specified in uv.lock. (In CI, you’d likely run uv sync --frozen or uv sync --locked to ensure it fails if the lock file isn’t honored, similar to poetry install --no-update behavior.)

2. Using the environment: You have options. You can activate the .venv like a normal venv (source .venv/bin/activate) and then run Python. Or, you can use uv run to run commands inside the venv without explicitly activating it. For example, uv run python src/main.py would run our script using the venv’s interpreter. uv run pytest would run pytest from the venv. This is very much like poetry run. So uv, while not forcing you to, encourages a similar approach of not manually activating environments if you don’t want to.

Day-to-day, uv can also manage your dependencies after the initial setup. If you want to add a package: uv add flask (and add --group dev if it’s a dev-only dependencies). That updates pyproject.toml and uv.lock together. Need to upgrade something? Edit pyproject or use uv lock --upgrade package_name to bump it, then uv sync. Under the hood, uv can function as a drop-in for some pip commands too: for example uv pip install -U somepkg will install and update the lock file accordingly, and uv pip freeze shows installed packages. In fact, uv’s CLI has a whole uv pip ... namespace to mirror pip and pip-tools commands for familiarity. But the recommended way is to use its higher-level project commands.

Pros of uv: Speed, speed, speed.

Did I mention speed? When I first tried uv on a moderately complex project, I was floored by how fast the environment got built. It’s especially noticeable in continuous integration environments or when you’re frequently creating fresh venvs. Another pro is consolidation: uv aims to replace the trio of python -m venv, pip, and pip-tools with a single unified tool. That means less context switching and fewer tools to install. It also has neat features like managing multiple Python versions (there’s a uv python install command to install Python interpreters, similar to pyenv). The use of standard pyproject [project] metadata means you’re not locked into uv; that config can be understood by other tools as well. And because uv is still evolving, it’s quickly incorporating community feedback – it’s an exciting space to watch.

Cons of uv: The flip side of being new is that it’s not yet as battle-tested as pip or Poetry. The uv project is moving fast (no pun intended) and while it’s reached a stable release, you might encounter minor bugs or missing features around the edges. Also, not everyone on your team may be familiar with it yet – convincing colleagues to adopt uv might require some evangelism, whereas Poetry has become quite mainstream in Python circles. Another potential con is that because uv does so much, it can feel a bit magical or heavy – but honestly, Poetry set the precedent there, and uv at least is extremely efficient in doing its work. I’d say the main reason you wouldn’t switch to uv (yet) is inertia or caution: pip/Poetry are working fine for you, so why risk a new workflow? That’s perfectly valid. But if you’re the curious type (or you maintain CI pipelines where every second saved is gold), uv is definitely worth a look.

4. Choosing the Right Tool (pip vs Poetry vs uv)

So, with three different ways to manage a Python project, how do you choose? It really comes down to the scope and needs of your project:

• Use pip (with a manual venv and requirements.txt) for simplicity: If this is a small script, a one-off analysis, or just a quick experiment, pip is often the path of least resistance. It’s already there, it’s quick to get going, and you won’t confuse future readers (or yourself) with additional config files. Just remember to create a virtual environment to avoid polluting your system Python! Pip is great when you don’t have a ton of dependencies or when you’re okay managing them by hand. For example, a short data script that only uses requests and pandas – you can pip install those, maybe pin the versions if needed, and be on your way.

• Use Poetry when you’re building a structured application, library, or anything long-lived. If your project is going to be deployed, or needs consistent environments across multiple machines, or will be worked on by a team, Poetry gives you that reliability. The lock file ensures that the “works on my machine” is reproducible, the pyproject.toml makes dependencies and metadata explicit, and having dev vs prod dependencies separate is a huge plus for larger projects. Also, if you think you might eventually publish the project (open-source library, internal package, etc.), Poetry sets you up with the packaging basics out of the box. In our my_data_app case, if this were an actual service or a tool I planned to maintain, I’d likely choose Poetry to manage it, so that any collaborator can clone the repo and just run poetry install to get started.

• Try uv if you value speed or are managing very large projects/monorepos, or if you’re just eager to use the latest-and-greatest in Python tooling. Especially in continuous integration scenarios, uv can drastically cut down install times. If your project has dozens of packages, or if you find yourself frequently creating new virtualenvs, uv’s performance pays off. It’s also a good fit if you like the idea of having one tool that does it all (no need for pip, pipenv, virtualenv, etc., in theory). That said, since uv is relatively new, I’d say it’s great for personal projects or forward-looking teams. Conservative or heavily-regulated environments might wait a bit longer for uv to mature. I’ve started using uv for some of my side projects and have been impressed – it hasn’t completely replaced Poetry for me yet, but I wouldn’t be surprised if it becomes the standard in the next year or two given its momentum (the GitHub repo already has tens of thousands of stars, which is a good sign of community interest).

To summarize the differences, here’s a quick comparison of pip, Poetry, and uv for key aspects:

• Config File: Pip uses a simple requirements.txt (a flat list of packages==versions). Poetry uses pyproject.toml with its own [tool.poetry] tables (plus a poetry.lock). uv also uses pyproject.toml, but in a PEP 621 standard way (the [project] table), plus its uv.lock. In practice, Poetry’s config might not be directly usable by other tools, whereas uv’s config is more universal.

• Lockfile: Pip doesn’t have a native lock file (you can treat a fully pinned requirements.txt as one, but there’s no resolver ensuring consistency beyond manual freezing). Poetry’s poetry.lock is a detailed lockfile listing exact versions of everything, ensuring reproducible builds. uv’s uv.lock similarly pins exact versions (and hashes) but also includes multi-platform info and is optimized for speed. Both Poetry and uv expect you to commit the lockfile to version control for team use.

• Virtual Environment Management: With pip alone, you manage venvs manually (using venv or virtualenv). Poetry will auto-create and manage a venv for your project – you typically let it handle activation via poetry run or poetry shell. uv is somewhat in-between: you explicitly create a venv with uv venv, but then uv will automatically use that environment for any uv run or uv sync commands (so it feels seamless once set up). If you prefer the old activate/deactivate dance, you can still do that with any of these tools; none of them prevent you from activating the venv if you want to.

• Ecosystem and Extras: Pip is minimal but universal. Poetry has a rich ecosystem (plugins, a robust CLI, active community) and has been around longer than uv. uv is cutting-edge and integrates other Astral tools (for example, it works nicely with Ruff, etc.), and it might introduce new features as it grows (like easier Python version management, etc.). If you need to publish packages or manage complex constraints, Poetry currently has more documentation and community knowledge base. uv, however, is catching up fast and aims to cover those use cases too (its developers are actually maintainers of another tool called Rye, and they’re folding lessons from that into uv’s design).

Finally, let me put it in personal terms: for a throwaway script or tiny automation, I still just use pip in a venv, because it’s one or two installs and done. For any project that I anticipate maintaining or sharing, Poetry is my default choice – it gives me peace of mind that everything is pinned and that setting up on a new machine is trivial. For new projects where I’m feeling adventurous (or if I’m really annoyed at how long pip install is taking in CI), I’ve been giving uv a shot, and it’s impressive. In a year’s time, who knows – I might be using uv for everything and reminiscing about the days of waiting 30 seconds for pip to resolve dependencies. 😄

Outro:

There’s no one-size-fits-all, but the good news is Python’s tooling has come a long way. We’re no longer stuck with just “install globally and pray nothing conflicts.” Whether you choose pip, Poetry, or uv, you’re adopting practices that isolate and pin your environment, which is a huge win for reliability. Each tool has its niche: pip for quick-and-dirty tasks, Poetry for robust project management, and uv for speed and forward-thinking workflows. Try them out on different projects and see what feels right. And hey, you don’t have to pick a permanent allegiance – it’s totally fine to use pip on one project and Poetry or uv on another, based on needs. The real goal is to make dependency management one less headache in our development life. Happy coding, and may your environments be clean and your dependencies conflict-free!