Career roadmap
Analytics Engineer
Own the transformation layer: turn raw warehouse tables into trustworthy, documented, tested models.
Before you start Analytics Eng
- SQL including window functions
- Git basics
- Understanding of how a business uses its data
The modern data stack
Understand where your layer sits and what the layers either side guarantee.
Columnar storage and separated compute change how you write SQL.
- Columnar storage and pruning
- Compute and storage separation
- Clustering, partitioning and sort keys
- Cost model per query
Why transformation moved into the warehouse, and what that implies.
- Ingestion tools and managed connectors
- Raw, staging and mart layers
- Idempotent transformations
- Full refresh versus incremental
The defining tool of the role. Fluency is assumed in interviews.
- Models, refs and the DAG
- Sources and freshness checks
- Materialisations and their trade-offs
- Project structure conventions
The 'engineer' half of the title: version control, review, CI.
- Branching and pull request review
- CI running dbt build
- Environments: dev, staging, production
- Code review for SQL
Query patterns that are correct on a laptop and ruinous on a billion rows.
- Window functions and qualify
- Avoiding fan-out joins
- Incremental predicates
- Reading a warehouse query plan
BuildLoad a raw source into a warehouse and build your first three transformation models on top.
Data modelling
The intellectual core of the role: designing models the whole company relies on.
Kimball still underpins most warehouse design, and interviews expect the vocabulary.
- Facts and dimensions
- Grain declaration and why it matters
- Star versus snowflake
- Conformed dimensions across marts
Handling attributes that change over time is where models get subtle.
- Slowly changing dimensions
- Snapshots and point-in-time joins
- Event versus state modelling
- Late-arriving data
A well-layered project is readable by someone who joins next year.
- Staging, intermediate and mart layers
- Naming conventions that scale
- Reusable macros without over-abstraction
- Managing model sprawl
Defining a metric once, used everywhere, is the promise of the role.
- Semantic layer concepts
- Metric definitions in code
- Avoiding duplicated business logic
- Serving metrics to BI tools
Warehouse bills are a common reason analytics engineers get attention.
- Incremental model design
- Partitioning and clustering strategy
- Query cost profiling
- Materialisation choice by usage
BuildModel a business domain from raw events to a documented mart, with grain declared everywhere.
Testing and reliability
Trust is the product. A wrong dashboard costs more than a late one.
The habit that distinguishes analytics engineering from ad-hoc SQL.
- Uniqueness, not-null, relationship tests
- Accepted values and custom tests
- Testing business logic assumptions
- Test severity and thresholds
Catching silent failures where data arrives but is wrong.
- Freshness and volume anomaly detection
- Schema change detection
- Row count reconciliation to source
- Alert routing and ownership
Upstream schema changes are the leading cause of broken pipelines.
- Contract definition with producers
- Breaking change detection
- Versioning downstream models
- Negotiating with source system owners
Nobody trusts a table they cannot trace to a source.
- Column-level descriptions
- Lineage graphs and impact analysis
- Data catalogue integration
- Keeping docs current automatically
Running the DAG reliably, with retries and sensible dependencies.
- Scheduling and dependency management
- Airflow or Dagster integration
- Backfills without duplication
- Failure handling and reruns
BuildAdd tests, freshness checks and alerting across a project so a bad load fails loudly.
Serving the business
Models nobody uses are wasted work. Adoption is part of the job.
Where your models meet the people who consume them.
- Exposing marts to BI tools
- Avoiding logic duplication in dashboards
- Row-level security in the warehouse
- Performance for interactive queries
Reducing the queue of ad-hoc requests is the measurable outcome.
- Designing for analyst consumption
- Certified datasets
- Training analysts on the model
- Measuring self-service adoption
Who can see which columns is increasingly a legal question.
- PII classification and masking
- Role-based warehouse access
- Retention policies
- Audit requirements
Pushing modelled data back into operational tools closes the loop.
- Syncing to CRM and marketing tools
- Idempotency and sync failure handling
- Field mapping and ownership
- Monitoring downstream sync health
The role sits between three groups with different priorities.
- Negotiating with data engineers
- Gathering requirements from analysts
- Explaining trade-offs to stakeholders
- Prioritising a shared backlog
BuildMigrate a BI dashboard onto your models and retire the duplicated logic behind it.
Interview preparation
Interviews centre on SQL, modelling decisions, and how you keep data trustworthy.
Advanced SQL, usually with a modelling twist rather than a puzzle.
- Window functions and deduplication
- Point-in-time joins
- Incremental logic correctness
- Explaining query cost
Given a business domain, design the models out loud.
- Identifying grain and keys
- Fact and dimension decisions
- Handling history requirements
- Trade-offs you deliberately made
A dashboard shows a wrong number. Walk through your investigation.
- Tracing lineage back to source
- Distinguishing model bug from source issue
- Preventing recurrence with tests
- Communicating the incident
Direct dbt questions: materialisations, macros, incremental strategy.
- Choosing a materialisation
- Incremental strategies and their risks
- Macros and packages
- Project structure decisions
A public dbt repository is the clearest possible evidence for this role.
- Public project with docs generated
- Tests covering real assumptions
- A written modelling decision record
- Before/after cost or performance work
BuildA public dbt project with tests, docs and a written modelling rationale.
Analytics Eng tools on your CV
- dbt
- Snowflake / BigQuery
- SQL
- Git
- Airflow / Dagster
- Looker
- Great Expectations
What Analytics Eng employers ask to see
- A public dbt project with tests and documentation
- A dimensional model with grain documented per table
- A data quality incident write-up and the test that now prevents it
- A warehouse cost reduction with measured savings
Created by dbt and the modern warehouse, and now a standard role on data teams. Sits between analysts and data engineers, and pays closer to engineering.
Content last reviewed 2026-08-31. Guidance only — no institute or paid placement is endorsed anywhere in this book.