{"course":{"slug":"ai-careers","title":"Working in AI","url":"https://addaly.com/learn/ai-careers","level":"beginner","track":"work"},"lesson":{"slug":"analytics-engineer-in-depth","title":"The analytics engineer","url":"https://addaly.com/learn/ai-careers/analytics-engineer-in-depth","position":12,"of":85,"minutes":9,"answer":"Analytics engineering makes data trustworthy by treating transformations as idempotent, version-controlled, tested code, and its only real success metric is whether other people build on your tables.","keyIdea":"Analytics engineering makes data trustworthy by treating transformations as idempotent, version-controlled, tested code, and its only real success metric is whether other people build on your tables."},"body":{"format":"markdown","text":"## A role that did not exist in 2015\n\nAnalytics 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.\n\nThe 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.\n\n## The week\n\nYou 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.\n\nAn 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.\n\n## The idea that carries the role\n\n**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.\n\nThe second idea is that **data can be tested like code**. Not \"does the query run\", but assertions about the data itself:\n\n```sql\n-- every order must belong to a customer that exists\nselect o.order_id\nfrom fct_orders o\nleft join dim_customer c on c.customer_id = o.customer_id\nwhere c.customer_id is null\n```\n\nIf 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.\n\n## The stack\n\n- **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.\n- **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.\n- **Git**, seriously. This role lives in pull requests.\n- **A scheduler**: Airflow, Dagster, Prefect, or a cron job. All have free open-source versions.\n\nYou 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.\n\n## Two routes in\n\n**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.\n\n**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.\n\n## The failure that ends the first year\n\nBuilding 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.\n\nThe 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.\n\n## The limitation\n\nThis 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."},"check":{"question":"An analytics engineer has built 180 tested models over four months. Analysts still query the raw source tables directly. What is the correct conclusion?","options":[{"text":"The analysts need training on the new models and the fix is documentation and a workshop.","correct":false,"tempting":"Assumes the models are right and adoption is a communication problem, which is the most common self-flattering diagnosis in this role."},{"text":"The project has failed at its actual purpose so far, because the only measure of the work is whether others build on it, and the first question is which fields analysts need that the models do not contain.","correct":true},{"text":"Access to raw tables should be revoked so analysts are forced onto the modelled layer.","correct":false,"tempting":"Forces adoption without fixing the gap, which produces workarounds and resentment rather than trust."},{"text":"The models are fine and the tests prove it; adoption will follow naturally as the team grows.","correct":false,"tempting":"Confuses tests passing with the models being useful; a test only checks that the data matches its own assertions."}]},"prev":{"slug":"data-analyst-in-depth","title":"The data analyst","url":"https://addaly.com/learn/ai-careers/data-analyst-in-depth"},"next":{"slug":"data-engineer-in-depth","title":"The data engineer","url":"https://addaly.com/learn/ai-careers/data-engineer-in-depth"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'The analytics engineer', in Working in AI. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/ai-careers/analytics-engineer-in-depth (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"The analytics engineer","courseTitle":"Working in AI","module":{"index":2,"slug":"the-roles-in-depth","title":"The roles, one at a time"},"answer":null,"keyIdea":"Analytics engineering makes data trustworthy by treating transformations as idempotent, version-controlled, tested code, and its only real success metric is whether other people build on your tables.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"a-role-that-did-not-exist-in-2015","runs":[{"text":"A role that did not exist in 2015"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-week","runs":[{"text":"The week"}]},{"t":"p","runs":[{"text":"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: "},{"text":"dim_customer","code":true},{"text":", "},{"text":"fct_orders","code":true},{"text":", "},{"text":"fct_subscription_events","code":true},{"text":". In between are dozens of intermediate models, each one doing one comprehensible thing."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-idea-that-carries-the-role","runs":[{"text":"The idea that carries the role"}]},{"t":"p","runs":[{"text":"A transformation should be idempotent and re-runnable.","b":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"The second idea is that "},{"text":"data can be tested like code","b":true},{"text":". Not “does the query run”, but assertions about the data itself:"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- every order must belong to a customer that exists\nselect o.order_id\nfrom fct_orders o\nleft join dim_customer c on c.customer_id = o.customer_id\nwhere c.customer_id is null"},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-stack","runs":[{"text":"The stack"}]},{"t":"ul","items":[[{"text":"dbt","b":true},{"text":" 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."}],[{"text":"A warehouse","b":true},{"text":": 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."}],[{"text":"Git","b":true},{"text":", seriously. This role lives in pull requests."}],[{"text":"A scheduler","b":true},{"text":": Airflow, Dagster, Prefect, or a cron job. All have free open-source versions."}]]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"two-routes-in","runs":[{"text":"Two routes in"}]},{"t":"p","runs":[{"text":"From analyst.","b":true},{"text":" The overwhelmingly common one. You are already writing the SQL; you add version control, tests and a transformation tool. Six focused months is realistic."}]},{"t":"p","runs":[{"text":"From software engineering.","b":true},{"text":" 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."}]},{"t":"h2","id":"the-failure-that-ends-the-first-year","runs":[{"text":"The failure that ends the first year"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-limitation","runs":[{"text":"The limitation"}],"role":"limit"},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"An analytics engineer has built 180 tested models over four months. Analysts still query the raw source tables directly. What is the correct conclusion?"}],"options":[{"text":[{"text":"The analysts need training on the new models and the fix is documentation and a workshop."}],"correct":false,"tempting":[{"text":"Assumes the models are right and adoption is a communication problem, which is the most common self-flattering diagnosis in this role."}]},{"text":[{"text":"The project has failed at its actual purpose so far, because the only measure of the work is whether others build on it, and the first question is which fields analysts need that the models do not contain."}],"correct":true,"tempting":null},{"text":[{"text":"Access to raw tables should be revoked so analysts are forced onto the modelled layer."}],"correct":false,"tempting":[{"text":"Forces adoption without fixing the gap, which produces workarounds and resentment rather than trust."}]},{"text":[{"text":"The models are fine and the tests prove it; adoption will follow naturally as the team grows."}],"correct":false,"tempting":[{"text":"Confuses tests passing with the models being useful; a test only checks that the data matches its own assertions."}]}],"order":[3,0,2,1],"why":null},"prev":{"slug":"data-analyst-in-depth","title":"The data analyst","minutes":9},"next":{"slug":"data-engineer-in-depth","title":"The data engineer","minutes":10}}}