CraftCourse logoCraftCourse
Sign in
Roadmaps›Analytics Engineer roadmap

Analytics Engineer Roadmap

Analytics Engineering sits in the gap that used to exist between data engineers, who built pipelines but rarely modeled the data for business use, and analysts, who wrote SQL but had no way to version, test or reuse it. The role exists because dbt made it possible to treat a warehouse's transformation logic like real software: version controlled, tested, documented and reviewed, rather than a folder of scheduled queries nobody fully understands anymore.

If you already write SQL comfortably as an analyst, this is usually the shortest realistic path into a higher paid, more technical data role, shorter than retraining as a full data engineer. The core shift is moving from writing one-off queries to building a layer of trusted, reusable models that every dashboard and analyst in a company depends on. Snowflake as the warehouse and Power BI or Looker as the consumption layer are the two ends of that pipeline; dbt is what connects them properly.

This path assumes you're already SQL literate and takes you from there through dimensional modeling, dbt itself, orchestration, and the semantic layer and BI integration work that makes a well modeled warehouse actually usable by the rest of the business.

Drag or scroll to explore, click a node to learn more · 8 sections · 17 courses · free and self paced

How to use this roadmap

  1. 1This roadmap assumes working SQL knowledge already, if joins, aggregations and subqueries aren't second nature yet, get comfortable there first.
  2. 2Go through sections in order, dimensional modeling before dbt matters, understanding the target shape of your data makes dbt models make sense instead of feeling arbitrary.
  3. 3dbt is the centerpiece of this path deliberately, don't rush through the dbt sections to get to BI tools faster, most analytics engineering job interviews focus heavily on dbt specifics.
  4. 4Each course's concepts are standalone 20 to 30 minute lessons, work through them as a checklist rather than trying to absorb a whole course in one sitting.
  5. 5Build a real dbt project on a free Snowflake trial as you go, working with a toy dataset end to end teaches more than reading about any single concept in isolation.
01

Analytics Engineering Landscape

What an Analytics Engineer Actually Doesbeginner

Where the role sits between data engineering and analytics, and why it exists.

  • Analytics Engineer vs Data Analyst: Building the modeled layer versus consuming it for analysis.
  • Analytics Engineer vs Data Engineer: Modeling data in the warehouse versus building the pipelines feeding it.
  • The Modern Data Stack: The common warehouse, transformation and BI tool combination in use today.
ELT vs ETLbeginner

Why modern analytics engineering loads raw data first and transforms it inside the warehouse.

  • ETL: Transforming data before it's loaded into the destination system.
  • ELT: Loading raw data first, then transforming it inside the warehouse.
  • Why ELT Won for Analytics: Cheap warehouse compute made in-warehouse transformation practical.
02

Data Warehouse Architecture

Data Warehouse Architecture with Snowflakebeginner

How Snowflake separates storage and compute, and what that means for how you design models.

  • Storage & Compute Separation: Snowflake scales query power independently from stored data.
  • Virtual Warehouses: Snowflake's independently scalable compute clusters.
  • Micro-Partitions: How Snowflake automatically organizes stored data for fast queries.
Layered Warehouse Designintermediate

The staging, intermediate and mart layer pattern nearly every dbt project is built on.

  • Staging Layer: Lightly cleaned, renamed raw source data, one to one with sources.
  • Intermediate Layer: Reusable transformation logic shared across final models.
  • Marts Layer: Final, business-facing models that dashboards actually query.
03

Dimensional Modeling

Dimensional Modeling Fundamentalsintermediate

The Kimball-style patterns that shape almost every analytics warehouse in production.

  • Star Schema: A fact table surrounded by descriptive dimension tables.
  • Fact Tables: Tables storing measurable business events, like orders or clicks.
  • Dimension Tables: Tables storing descriptive attributes, like customer or product details.
  • Grain: What a single row in a fact table actually represents.
Slowly Changing Dimensionsintermediate

How to track a dimension's history when its attributes change over time.

  • SCD Type 1: Overwriting old dimension values with new ones, no history kept.
  • SCD Type 2: Keeping full history with new rows and effective date ranges.
  • Surrogate Keys: Warehouse-generated keys used instead of natural source keys.
04

dbt Fundamentals

dbt Core Fundamentalsintermediate

The centerpiece skill of this whole path: turning raw tables into version controlled SQL models.

  • Models: SQL select statements dbt turns into tables or views.
  • Sources: How dbt references raw tables that exist outside its own models.
  • Refs & the DAG: How ref() builds dbt's dependency graph between models.
  • Materializations: Whether a model builds as a table, view, incremental or ephemeral.
Jinja Templating & Macros in dbtintermediate

Writing dynamic, reusable SQL instead of copy-pasting the same logic across models.

  • Jinja Basics: The templating syntax dbt uses inside SQL files.
  • Macros: Reusable pieces of SQL logic defined once, called everywhere.
  • Packages: Shared, installable dbt macro libraries like dbt_utils.
Incremental Models & Performanceadvanced

Processing only new or changed data instead of rebuilding an entire table every run.

  • Incremental Materialization: Only inserting or updating new rows instead of a full rebuild.
  • Merge & Insert Strategies: Different ways dbt applies incremental changes to a table.
  • Snapshots: dbt's built-in way to track SCD Type 2 history automatically.
05

dbt Testing & Documentation

dbt Testsintermediate

Catching broken data before it reaches a dashboard, built directly into the transformation layer.

  • Generic Tests: Built-in checks like unique, not null, and relationships.
  • Singular Tests: Custom SQL tests written for specific business logic.
  • Source Freshness: Alerting when upstream source data stops updating on schedule.
dbt Documentation & Lineageintermediate

Making a warehouse self-explaining instead of relying on tribal knowledge.

  • dbt Docs Site: Auto-generated documentation for every model and column.
  • Lineage Graph: A visual map of how models depend on each other.
  • YAML Model Descriptions: Where model and column documentation is actually written.
06

Git Workflow for Analytics Code

Git Workflow for Analytics Engineersintermediate

Treating SQL like real code: branches, pull requests and review before it hits production.

  • Branching for dbt Projects: Working on model changes in isolation before merging.
  • Pull Requests & Code Review: Getting SQL changes reviewed before they reach production.
  • dbt Slim CI: Running tests only on modified models to keep CI fast.
07

Orchestration

Orchestration Conceptsintermediate

Why analytics pipelines need real scheduling and dependency management, not just a cron job.

  • DAGs & Dependencies: Defining which tasks must complete before others can start.
  • Scheduling & Backfills: Running pipelines on a schedule, and re-running for past periods.
Running dbt with Airflow or Dagsteradvanced

Triggering and monitoring dbt runs as part of a larger scheduled pipeline.

  • Airflow with dbt: Using Airflow operators to trigger and monitor dbt runs.
  • Dagster Software-Defined Assets: Modeling dbt models directly as Dagster assets.
  • Pipeline Alerting: Getting notified when a scheduled dbt run fails.
08

Semantic Layers & BI Integration

Semantic Layersadvanced

Defining a business metric once so every tool and dashboard agrees on what it means.

  • Metrics Definitions: Centrally defining calculations like revenue or active users once.
  • dbt Semantic Layer: dbt's approach to defining and serving consistent metrics.
  • LookML: Looker's modeling language for defining a semantic layer.
Power BI Integration with the Warehouseintermediate

Connecting dbt-modeled tables to Power BI without duplicating transformation logic downstream.

  • DirectQuery vs Import Mode: Querying Snowflake live versus loading data into Power BI.
  • Power BI Data Modeling: Relationships and measures built on top of dbt marts.
  • Avoiding Duplicate Logic: Keeping business logic in dbt instead of re-built in Power BI.
Looker & BI Tool Comparisonintermediate

How Looker's semantic layer approach compares to a BI tool sitting directly on dbt marts.

  • Looker Explores: Looker's user-facing way of querying modeled data.
  • BI Tool Tradeoffs: Comparing Power BI, Looker and Tableau for analytics engineering fit.

Not satisfied with the roadmap? Create your own!

Sage

Frequently asked questions

A Data Analyst mostly consumes already-modeled data to answer business questions and build dashboards. An Analytics Engineer builds and maintains the modeled layer itself, the dbt models, tests and documentation that analysts and BI tools then query. It's a more technical, engineering-adjacent role sitting between data engineering and analytics.

Basic Python helps but is not the core requirement, dbt itself is SQL plus Jinja templating, not Python. Some teams use Python for dbt models via dbt's Python model support or for auxiliary scripting, but you can be a strong analytics engineer with strong SQL and dbt skills and only light Python.

The core analytics engineering skills, dimensional modeling, dbt, testing, semantic layers, transfer almost directly between warehouses since dbt abstracts most of the syntax differences. Snowflake is one of the most common warehouses analytics engineering roles are hired against, which is why this path centers it, but the underlying skill is portable.

Learn dbt Core first, it's free, open source, and teaches you the actual mechanics of models, tests and the DAG without any platform abstraction. dbt Cloud adds a hosted IDE, scheduling and a nicer docs UI on top of the same core concepts, worth knowing exists but not essential to learn first.

Prioritize whichever your target companies actually use, check job postings in your market. Power BI has broader enterprise adoption overall, while Looker's LookML semantic layer concepts map closely to what dbt's own semantic layer is trying to standardize, so learning Looker teaches semantic layer thinking especially well even if you end up using Power BI day to day.

Most SQL-literate analysts following this roadmap consistently reach a job ready level in 2 to 4 months, since the core new skills, dbt, dimensional modeling and Git, build directly on SQL you likely already know rather than requiring an entirely new foundation.

A semantic layer defines business metrics, like revenue or active users, once in a central place with consistent logic, so every dashboard and tool references the same definition instead of each analyst calculating it slightly differently. It's increasingly a core analytics engineering responsibility as companies try to stop metrics from disagreeing across reports.

You don't need both, the underlying orchestration concepts, DAGs, dependencies, scheduling, transfer between them. Airflow has wider existing adoption so it's worth knowing the concepts even if a specific job uses Dagster, and Dagster's asset-based model is increasingly popular in analytics engineering specifically because it maps naturally onto dbt models.

The analytics engineers who stand out are not the ones who know the most dbt syntax, they're the ones whose models a whole company can trust without double checking, because they're tested, documented and built on a warehouse structure that actually makes sense. Working through this roadmap in order, and building a real dbt project rather than just reading about one, is what gets you there.