Learning path
Analytics Engineer learning path: everything to study, topic by topic
Assesses the ability to transform raw data into trusted, well-modeled analytics-ready datasets. Bridges data engineering and data analysis.
Short answer
To learn Analytics Engineer, work through 6 modules and 24 topics in the order they build on each other. It starts with SQL & Transformation, then moves on to Data Modeling and Testing & Documentation. Use the map as a checklist, or measure your level and study only what is missing.
What you will study
Topics are grouped by module. Open one to see its objectives and key concepts.
1 · SQL & Transformation4 topics
Advanced SQL (Window Functions/CTEs)
Writing analytical SQL using window functions, common table expressions, recursive queries, and advanced aggregation patterns to transform raw data into meaningful outputs
Objectives
- Write window functions to compute running totals and rank-based metrics
- Refactor complex subqueries into readable CTE chains
Key concepts
- window functions (ROW_NUMBER, RANK, LAG, LEAD)
- common table expressions
- recursive CTEs
- QUALIFY clause
- analytical aggregations
Data Transformation Patterns
Applying common transformation patterns including pivoting, unpivoting, deduplication, type casting, date spine generation, and surrogate key creation
Objectives
- Design a deduplication strategy for event data with late-arriving records
- Build a date spine to fill gaps in time-series reporting
Key concepts
- pivot and unpivot
- deduplication strategies
- surrogate key generation
- date spine tables
- type casting and coercion
dbt Core Concepts
Understanding dbt project structure, model materializations, ref/source functions, the DAG, and the build/run/test lifecycle
Objectives
- Explain when to use each dbt materialization type and the trade-offs involved
- Describe how the dbt DAG resolves model dependencies during a build
Key concepts
- dbt project structure
- materializations (table, view, incremental, ephemeral)
- ref() and source() functions
- the dbt DAG
- dbt run/build/test lifecycle
Jinja Templating in dbt
Using Jinja macros, control structures, and variables within dbt to create DRY, reusable SQL transformations and custom schema tests
Objectives
- Write a Jinja macro to generate pivot columns dynamically from a source table
- Use dbt variables and environment vars to parameterize models across environments
Key concepts
- Jinja syntax in dbt
- macros and reusable logic
- control structures (if/for)
- dbt variables and env_var
- custom materializations
2 · Data Modeling4 topics
Staging/Intermediate/Marts Layers
Organizing dbt projects into staging, intermediate, and marts layers with clear ownership boundaries, naming patterns, and materialization strategies
Objectives
- Design a three-layer dbt project structure for a SaaS company with five source systems
- Define clear boundaries for what logic belongs in staging vs intermediate vs marts
Key concepts
- staging layer (1:1 source mapping)
- intermediate layer (business logic)
- marts layer (consumer-ready)
- layer ownership
- cross-layer dependencies
Dimensional Modeling in Practice
Applying Kimball dimensional modeling principles including star schemas, fact and dimension tables, grain definition, conformed dimensions, and bridge tables
Objectives
- Design a star schema for a multi-currency e-commerce platform with order-level grain
- Identify when to use a bridge table vs a factless fact table for many-to-many relationships
Key concepts
- star schema design
- fact vs dimension tables
- grain definition
- conformed dimensions
- bridge and outrigger tables
Slowly Changing Dimensions
Implementing SCD Type 1, 2, and 3 strategies using dbt snapshots and incremental models, handling late-arriving data and retroactive corrections
Objectives
- Implement an SCD Type 2 dimension using dbt snapshots with valid_from/valid_to columns
- Handle late-arriving dimension changes that affect previously loaded fact records
Key concepts
- SCD Type 1 (overwrite)
- SCD Type 2 (history tracking)
- SCD Type 3 (previous value)
- dbt snapshots
- late-arriving dimensions
Naming Conventions & Style Guides
Establishing and enforcing consistent naming conventions, SQL style guides, and model organization standards across the analytics engineering team
Objectives
- Draft a naming convention guide that covers models, columns, and sources for a 10-person team
- Set up SQLFluff or sqlfmt to enforce SQL style in CI
Key concepts
- model naming patterns
- column naming standards
- SQL style guide
- dbt project conventions
- linting and enforcement
3 · Testing & Documentation4 topics
Data Testing (Generic & Custom)
Writing and maintaining generic schema tests (unique, not_null, accepted_values, relationships) and custom data tests in dbt to catch data quality issues before they reach consumers
Objectives
- Design a test suite that catches null keys, duplicate rows, and referential integrity violations
- Write a custom generic test that validates date ranges never overlap for SCD2 dimensions
Key concepts
- generic schema tests
- custom singular tests
- custom generic tests
- test severity levels
- test selection and tags
Data Quality Assertions
Implementing row-count checks, freshness monitoring, distribution validations, and anomaly detection as part of the data pipeline
Objectives
- Implement source freshness checks that alert when upstream data is stale beyond 6 hours
- Design a distribution validation that catches revenue anomalies exceeding 3 standard deviations
Key concepts
- row count assertions
- source freshness checks
- distribution validation
- anomaly detection thresholds
- dbt expectations package
Documentation as Code
Maintaining model documentation in YAML files, generating data dictionaries, and using dbt docs to create a living catalog of the data warehouse
Objectives
- Set up a documentation workflow that requires descriptions for all public-facing mart models
- Generate a dbt doc site and use it to onboard a new analyst in under 30 minutes
Key concepts
- YAML-based documentation
- column-level descriptions
- dbt docs generate/serve
- doc blocks and markdown
- documentation coverage metrics
Data Contracts
Defining explicit agreements between data producers and consumers on schema, SLAs, freshness guarantees, and allowed breaking changes
Objectives
- Draft a data contract between the product events pipeline and the analytics mart layer
- Implement schema enforcement that blocks breaking changes without a migration plan
Key concepts
- contract structure and versioning
- schema enforcement
- SLA definitions
- breaking change policies
- contract testing in CI
4 · Cloud Data Platforms4 topics
Snowflake/BigQuery/Redshift
Understanding the architecture, pricing models, and SQL dialect differences across major cloud data warehouses and choosing the right platform for workload requirements
Objectives
- Compare Snowflake, BigQuery, and Redshift for a workload with 500 concurrent analysts
- Explain how storage-compute separation impacts cost optimization strategies
Key concepts
- Snowflake virtual warehouses
- BigQuery slots and on-demand pricing
- Redshift RA3 and Spectrum
- storage vs compute separation
- cross-platform SQL differences
Cloud Cost Optimization for Analytics
Reducing warehouse spend through query optimization, warehouse sizing, auto-suspend, clustering, partitioning, and resource monitoring
Objectives
- Design a warehouse sizing strategy that reduces Snowflake credits by 30% without impacting SLAs
- Set up resource monitors and alerts to prevent runaway query costs
Key concepts
- warehouse auto-suspend and auto-resume
- query cost estimation
- clustering keys
- partition pruning
- resource monitors and alerts
Materialized Views & Performance
Using materialized views, incremental models, and query caching to balance freshness requirements against compute cost and query latency
Objectives
- Choose between a materialized view and an incremental dbt model for a dashboard with 15-minute SLA
- Design a refresh strategy that balances data freshness with compute budget
Key concepts
- materialized views vs tables
- incremental model patterns
- query result caching
- refresh strategies
- staleness vs cost trade-offs
Semantic Layer & Metrics
Defining business metrics in a centralized semantic layer using dbt metrics, Looker LookML, or similar tools to ensure consistent metric definitions across the organization
Objectives
- Define 'monthly recurring revenue' as a semantic layer metric consumable by three different BI tools
- Design a governance process for adding and modifying metric definitions
Key concepts
- metric definitions (measures + dimensions)
- dbt metrics / MetricFlow
- LookML modeling
- single source of truth for KPIs
- semantic layer consumers (BI, notebooks, APIs)
5 · Operations & Governance4 topics
CI/CD for Data Models
Building continuous integration and deployment pipelines for dbt projects, including slim CI, deferred state, and automated testing in pull requests
Objectives
- Set up a slim CI pipeline that only tests modified models and their downstream dependents
- Design a rollback strategy for a failed dbt deployment that corrupted a production mart
Key concepts
- slim CI with state:modified
- deferred runs against production
- PR-based testing
- deployment orchestration
- rollback strategies
Data Lineage & Impact Analysis
Tracking data lineage from source to dashboard, performing impact analysis before schema changes, and using lineage to debug data quality issues
Objectives
- Use dbt lineage to identify all downstream models affected by a source schema change
- Debug a dashboard metric discrepancy by tracing lineage from mart back to raw source
Key concepts
- column-level lineage
- dbt DAG as lineage graph
- impact analysis for schema changes
- lineage-based debugging
- cross-system lineage (source → warehouse → BI)
Version Control for Data
Managing dbt projects with Git workflows including branching strategies, code review processes, and handling migrations for schema-breaking changes
Objectives
- Design a Git branching strategy for a dbt project with 5 concurrent feature streams
- Create a migration plan for renaming a widely-used column without breaking downstream consumers
Key concepts
- Git branching for dbt
- PR review best practices
- migration scripts for breaking changes
- environment promotion (dev → staging → prod)
- conflict resolution in SQL models
Access Control & PII Handling
Implementing role-based access control, column-level security, PII masking, and compliance frameworks for sensitive data in the warehouse
Objectives
- Design an RBAC model that allows analysts to query aggregate PII metrics without seeing raw values
- Implement dynamic masking for email and phone columns based on user role
Key concepts
- RBAC in data warehouses
- column-level security
- dynamic data masking
- PII classification and tagging
- GDPR/CCPA compliance patterns
6 · Advanced Practices4 topics
Reverse ETL Patterns
Syncing transformed warehouse data back to operational tools like CRMs, marketing platforms, and customer success systems using reverse ETL pipelines
Objectives
- Design a reverse ETL pipeline that syncs lead scores from the warehouse to Salesforce hourly
- Handle conflict resolution when warehouse data contradicts CRM data entered by sales reps
Key concepts
- reverse ETL architecture
- audience syncing to CRM/marketing
- incremental sync strategies
- data activation use cases
- tools (Census, Hightouch, Polytomic)
Real-Time Analytics Modeling
Modeling data for near-real-time analytics using streaming ingestion, micro-batch patterns, and hybrid batch-streaming architectures
Objectives
- Design a hybrid architecture that serves both real-time fraud detection and daily batch reporting
- Model clickstream events for sub-minute dashboard refresh without exceeding compute budget
Key concepts
- streaming ingestion (Kafka, Kinesis)
- micro-batch incremental models
- Lambda and Kappa architectures
- real-time dashboards
- event-driven analytics
Data Mesh Implementation
Applying data mesh principles — domain ownership, data as product, self-serve platform, and federated governance — to scale analytics engineering across an organization
Objectives
- Design a data mesh migration plan for a company moving from a centralized analytics team
- Define data product SLAs and quality standards for a domain team publishing to a mesh catalog
Key concepts
- domain ownership of data products
- data as a product principles
- self-serve data platform
- federated computational governance
- interoperability standards
Analytics Engineering Team Design
Structuring analytics engineering teams, defining roles and responsibilities, establishing onboarding programs, and building a culture of data quality
Objectives
- Design an analytics engineering team structure for a 200-person company with 4 product lines
- Build an onboarding program that ramps a new analytics engineer to first production PR in 2 weeks
Key concepts
- team topology (embedded vs centralized)
- analytics engineer vs data engineer vs analyst
- onboarding playbooks
- code review culture
- career ladders for analytics engineers
Levels: Junior, Mid, Senior, and Expert
Explain it in your own words
Graded by AI · no account · 1 questionNo options to guess from: you write, the AI grades against the topic's rubric and tells you what level your answer reads at.
Already studying Analytics Engineer?
The assessment measures topic by topic, and your plan skips what you already know.
Open to read. An account is only needed to be measured.
Frequently asked questions
Where should I start studying Analytics Engineer?
Start with SQL & Transformation: Advanced SQL (Window Functions/CTEs), Data Transformation Patterns, and dbt Core Concepts. The map orders topics by what they depend on, so each one prepares the next.
How long does a full Analytics Engineer assessment take?
A full assessment of this path takes about 50 minutes. Study time depends on where you start; a diagnostic shows which topics you can skip.
Do I need an account to use this page?
No. The map, objectives and key concepts are open. You only need an account to be measured, get your gap by topic and receive a plan.