The analytics engineer

A role that did not exist in 2015

Analytics engineering appeared because two jobs were failing at their boundary. Data engineers delivered raw tables and stopped. Analysts wrote the same 200-line join in fifteen different notebooks, each slightly different, and the company had fifteen answers to one question.

The analytics engineer sits in that gap. They take raw, ugly source data and turn it into a set of clean, tested, documented tables that everybody else builds on. They write almost no application code and almost no machine learning. They write SQL, in a disciplined way, with version control and tests.

The week

You own a directed graph of transformations. Raw orders, customers and payments come in at the top. At the bottom sit a handful of tables the whole company uses: dim_customer, fct_orders, fct_subscription_events. In between are dozens of intermediate models, each one doing one comprehensible thing.

An ordinary week: a source system adds a column and silently changes an enum value; a test fails at 6am and you find out before the sales team does; an analyst asks for a field that does not exist and you decide whether to add it here or tell them to compute it themselves; you rename something and spend an afternoon finding everything downstream that depended on the old name.

The idea that carries the role

A transformation should be idempotent and re-runnable. Run it once, run it ten times, delete the output and run it again: the result must be identical. That single property is what makes a data platform trustworthy, and it is why analytics engineers rebuild tables from source rather than updating them in place.

The second idea is that data can be tested like code. Not “does the query run”, but assertions about the data itself:

-- every order must belong to a customer that exists
select o.order_id
from fct_orders o
left join dim_customer c on c.customer_id = o.customer_id
where c.customer_id is null

If that returns any rows, the build fails. A team with a hundred such tests finds out about broken data from a red build at 06:15, not from a director in a meeting at 11:00. This is the whole value proposition of the role, stated in one sentence.

The stack

• dbt is the industry standard, and dbt Core is free and open source; the paid Cloud product adds scheduling and a web interface you do not need to learn the craft. Alternatives: SQLMesh, or plain SQL files run by a scheduler.
• A warehouse: Snowflake, BigQuery, Databricks, Redshift. To practise free: DuckDB on your laptop reads CSV and Parquet files and speaks proper analytical SQL, and BigQuery and Snowflake both have free tiers.
• Git, seriously. This role lives in pull requests.
• A scheduler: Airflow, Dagster, Prefect, or a cron job. All have free open-source versions.

You can build a complete, credible analytics engineering portfolio project on a laptop with DuckDB, dbt Core and a public dataset, at zero cost. Very few applicants do, which is exactly why it works.

Two routes in

From analyst. The overwhelmingly common one. You are already writing the SQL; you add version control, tests and a transformation tool. Six focused months is realistic.

From software engineering. You already have Git, testing and code review; you learn the warehouse and the dimensional modelling ideas: facts, dimensions, grain, slowly changing dimensions. Kimball’s dimensional modelling vocabulary is forty years old and still what these tables are named after.

The failure that ends the first year

Building a beautiful graph nobody uses. It is possible to spend four months producing 180 exquisitely tested models while analysts keep querying the raw tables directly, because your models do not contain the field they need and nobody asked them.

The role is a service role. Its output is other people’s speed. If analysts are not migrating onto your models, that is the only metric that matters and everything else is decoration.

The limitation

This is a enjoyable job with a narrow ceiling in most companies. One or two analytics engineers can serve a large organisation, so there are fewer senior seats than in data engineering or analysis, and the progression is usually sideways: into data engineering, into platform work, or into leading a data team. Worth knowing before you specialise hard at 23.