The Analytics Engineer's Toolkit

Every few months someone posts a diagram of the modern data stack with forty logos on it, and a few hundred people conclude they're forty tools behind. They aren't. Most of those logos solve a problem they don't have yet.

Tools are downstream of method. A tool earns its place when the job it does is a job you actually have, and the manual version has started to hurt. Before that, it's overhead — setup, debugging, explaining. So read a toolkit backwards: not "what should I learn," but "what job is this, and do I have it yet?"

The substrate: SQL

SQL appears in 68.4% of data engineer postings and 44.7% of analyst postings — more than any other single skill in either role (datanerd.tech, Luke Barousse's analysis of real job ads). Everything above it is scaffolding: dbt orders it, the warehouse executes it, the BI tool generates it badly. It never stops earning its place, which makes it strange that we stop learning it so early — SELECT, JOIN, GROUP BY, then on to tools, then two years of queries that are correct by accident. Depth in SQL compounds. Breadth across logos doesn't.

Transformation as software: dbt

dbt shows up in about 10.6% of postings — small next to SQL, and one of the most informative numbers on the list: a team that names dbt has stopped treating SQL as a pile of scripts. ref() derives run order from your code instead of you maintaining it, materializations move a model from view to table to incremental without touching its logic, and tests and docs sit beside the model.

-- models/marts/fct_orders.sql
select
    o.order_id,
    o.customer_id,
    o.ordered_at::date as order_date,
    sum(i.amount)      as order_amount
from {{ ref('stg_orders') }} o
join {{ ref('stg_order_items') }} i using (order_id)
group by 1, 2, 3

That ref() is what turns a folder of SQL into engineering. It earns its place at your second dependent model, and it's premature when one query feeds one chart — a graph with a single node.

Getting the data in: dlt, Airbyte, Fivetran

Extract and load: rows out of a source you don't control, into a warehouse you do. The hard parts never appear in the demo — schema evolution (the source adds a column at 3am), incremental loading, merge semantics (upsert without duplicating). A hand-rolled script is honestly fine with one stable API and a full refresh that finishes fast. It stops being fine at the second source, or when full refresh starts costing real money — that's when you notice your script has quietly become a framework, badly. dlt is the cheapest step up: a library, not a platform, so you keep your code and get schema evolution and merge for free. Airbyte and Fivetran buy connectors and a UI, billed in effort or in invoices.

Where it runs: DuckDB and the cloud warehouse

Snowflake appears in 25.7% of postings, BigQuery and Redshift in most of the rest. The job is unglamorous: store the tables, execute the SQL. Develop locally in DuckDB, deploy to the cloud. DuckDB runs in-process, costs nothing, and handles far more than most people's "big data" — a few hundred million rows on a laptop is unremarkable. One dbt project can target both: same models, different profile. The cloud warehouse earns its place on concurrency, governance, and scale past one machine — not on "it's what real companies use." It's premature on 2 GB of data and one user.

Proving it works: tests and contracts

Generic tests — unique, not_null, accepted_values, relationships — are four lines of YAML and catch the failures that actually happen: a join that fanned out, a key that went null, a foreign key pointing at nothing. Singular tests encode the business rules: revenue is never negative, nothing ships before it's ordered. Contracts pin the shape — names, types, nullability — so a breaking change fails at build time instead of in front of a VP.

Test the keys, the grain, and the rules the business would notice — testing every column is noise, and noise trains people to ignore failures.

Docs and lineage

dbt docs generate gives you a searchable site and a lineage graph out of code you already wrote. The lineage graph is the cheapest trust-builder you will ever ship: when someone asks where a number comes from, you show them the path from source to dashboard instead of describing it from memory. It earns its place the moment a second person has to trust your numbers — on a portfolio, that's the moment you publish.

Orchestration: Airflow

Airflow shows up in 17.2% of postings. The job: run things on a schedule, in order, with retries, and tell someone when it breaks. It earns its place when the schedule has consequences — ingestion must finish before transformation, a 2am failure has to retry at 2:15 and page a human at 3. It's premature on day one: dbt build in a GitHub Action on a cron covers a surprising number of real pipelines, for an afternoon of work rather than a quarter. Airflow is a distributed system; running it is a job in itself.

The semantic layer: MetricFlow, Cube

Define a metric once, serve it everywhere — BI, notebook, API. The problem it solves: metric divergence. Finance says revenue was $4.1M, the exec dashboard says $3.8M, and both are "right" because one of them excludes refunds. A semantic layer turns that meeting into a code review. What it does not solve is data quality; it will happily serve a consistent, well-governed, universally agreed wrong number. Fix the model first. Premature with one consumer — earned the day one word means two things in two rooms.

Observability: Elementary

The job is watching the data itself over time — volume, freshness, distribution — and flagging anomalies nobody wrote a test for. Contracts catch structure; observability catches content. No contract notices that yesterday's load was 40% lighter than every previous Tuesday, or that a column 3% null since launch is suddenly 30% null; you only learn those thresholds by watching the series. It's earned once the pipeline runs unattended, and premature before you have tests at all — anomaly detection with no not_null checks is a smoke alarm in a house with no walls.

Git and CI: the floor

Git is named in 11.7% of postings, which mostly tells you the other 88% assume it. The job is history, review, and reversibility. CI is what changes how the work feels: a pull request that runs dbt build against a slice of data in its own schema turns "trust me, I checked" into a diff and a green check, reviewable by someone who wasn't inside your head when you wrote it. Non-negotiable, and it comes before the warehouse, before the orchestrator, before everything.

The whole toolkit, in one table

What matters more than any of it

None of this is the hard part. The hard part is knowing what one row of your table means, and what question the business is actually asking.

Grain comes first: one row per what? Per order, per order line, per customer per day? Nearly every double-counted revenue figure I've seen traces back to two tables joined at different grains. No tool catches that for you — a unique test catches it only if you already knew what the key should be, which is the same thing as knowing the grain.

The business question comes second. "Build me a churn dashboard" is a request for an artifact, not a question. The question underneath — are the customers from the March campaign staying longer than the ones from January? — determines the grain, the model, and whether you needed a semantic layer at all.

Get those two right and the toolkit is a week's worth of conveniences. Get them wrong and you'll ship a beautifully orchestrated, fully tested, semantically consistent answer to the wrong question — on time, and with excellent lineage.