What is Analytics Engineering & BI?
Analytics Engineering & BI is the craft of turning raw data into trustworthy, reusable datasets and clear insights. It sits between data engineering and business analytics: you build reliable data models (think: clean, documented tables in the warehouse) and deliver metrics and dashboards people can trust.
Examples of problems this field solves
- Company has five versions of “Active Users” across teams — you define a single, reliable metric.
- Dashboards are slow or inconsistent — you refactor models and caching to speed them up.
- Executives ask the same ad-hoc questions weekly — you create curated datasets and BI views that answer them instantly.
- Data is scattered across tools — you centralize it in a warehouse and model it for downstream analytics.
Note: The fit test on this page is available to everyone; only logged-in users will have saved progress.
Who this is for
- Problem-solvers who enjoy organizing messy data into clear structures.
- People comfortable with logic, SQL, and documenting definitions.
- Those who like bridging engineering constraints with business needs.
- Curious learners who iterate based on stakeholder feedback.
Prerequisites
- Comfort with spreadsheets (Excel/Google Sheets basics: formulas, filters, pivot tables).
- Basic SQL (SELECT, WHERE, GROUP BY, JOIN).
- Beginner data literacy (types of charts, when to use a metric, basic statistics like mean/median).
Quick self-check
- I can join two tables by an ID and calculate weekly counts.
- I can explain the difference between a metric and a dimension.
- I can read a simple dbt model or SQL view and summarize what it does.
Learning path
1) Understand the business
Clarify key goals, events, and metrics. Map data sources to questions stakeholders ask.
2) Master SQL and modeling
Go beyond basic SELECTs: window functions, CTEs, and dimensional modeling patterns.
3) Build the transformation layer
Use tools like dbt to create tested, documented, and modular data models.
4) Deliver insights in BI
Design clean dashboards, define shared metrics, and ensure performance and clarity.
5) Operate and improve
Set up tests, alerts, docs, and feedback loops for continuous improvement.
Careers inside this direction
- Analytics Engineer — Builds reliable data models and pipelines in the warehouse, turning raw data into well-defined, reusable datasets any analyst or BI tool can trust. Best for: people who enjoy SQL, modeling, documentation, and collaborating with both engineers and business teams.
Where you can work
- Product companies: SaaS, fintech, marketplaces, gaming, healthtech, edtech.
- Data-driven teams in retail, logistics, telecom, media, and manufacturing.
- Agencies/consultancies implementing BI and data warehousing for clients.
- Startups building their first data stack or scaling analytics foundations.
Salary ranges by stage
- Junior: $55k–$85k/year
- Mid-level: $85k–$120k/year
- Senior/Lead: $120k–$170k+/year
Varies by country/company; treat as rough ranges.
Growth map
Foundation → Junior
- Solid SQL (joins, window functions, CTEs), basic modeling (staging, marts).
- Understands source systems and event tracking basics.
- Can produce a clean, documented dataset used by a small dashboard.
Junior → Mid
- Modular dbt projects, tests, and documentation at scale.
- Implements dimensional modeling (facts, dimensions, slowly changing dimensions where relevant).
- Performance tuning (partitioning, clustering, incremental models).
- Owns a domain end-to-end: from source ingestion assumptions to BI delivery.
Mid → Senior/Lead
- Defines metrics layer and governance standards.
- Designs analytical data architecture and review processes.
- Improves reliability: testing strategy, observability, data SLAs.
- Mentors others, aligns roadmap to business outcomes.
Tools & stack overview
- Data warehouses: BigQuery, Snowflake, Redshift, Databricks SQL.
- Transformation & modeling: dbt Core/Cloud, SQL, Python (pandas for helpers).
- BI & metrics: Looker/Looker Studio, Power BI, Tableau, Metabase, Superset; emerging metrics layers.
- Orchestration & version control: Airflow, Git/GitHub/GitLab.
- Quality & observability: dbt tests, Great Expectations, elementary/monitoring patterns.
- Documentation & collaboration: dbt docs, Notion/Confluence, issue trackers.
- Product analytics context: GA4, Amplitude, Mixpanel (for event semantics).
Beginner roadmap (6 weeks)
Week 1 — SQL essentials
- Practice SELECT, WHERE, GROUP BY, ORDER BY, LIMIT.
- JOIN types and when to use each.
- Mini task: Recreate a weekly active users table from raw events (user_id, event_time).
Week 2 — Intermediate SQL & data modeling
- Window functions (ROW_NUMBER, LAG), CTEs, subqueries.
- Dimensional modeling basics: facts, dimensions, grain, surrogate keys.
- Mini task: Build a sessions table and join to users and orders.
Week 3 — dbt basics
- Project structure: staging, intermediate, marts.
- Tests (unique, not null), documentation, sources, seeds.
- Mini task: Create a dbt model for orders with tests and a simple doc string.
Week 4 — BI dashboards that answer questions
- Define a KPI spec (name, owner, formula, grain, caveats).
- Design clear charts (choose the right visualization, avoid clutter).
- Mini task: Build a KPI dashboard with revenue, active users, conversion rate.
Week 5 — Performance, reliability, and review
- Incremental models, partitions, clustering.
- Add data quality checks and simple alerts.
- Mini task: Convert a heavy model to incremental and document the strategy.
Week 6 — Ship a portfolio project
- Pick a domain (e-commerce, SaaS, logistics) and model it end-to-end.
- Write a short README explaining business logic and trade-offs.
- Mini task: Present your dashboard and answer three stakeholder-style questions.
Practical projects
- Sales pipeline mart: Model leads, opportunities, and won deals; define conversion rate and time-to-close; deliver a BI dashboard with cohort and funnel views.
- Product usage analytics: Create user, session, and event models; compute weekly active users, retention, and feature adoption; add dbt tests and docs.
- Revenue analytics: Build orders, items, refunds, and subscriptions models; define Gross/Net Revenue and AOV; implement an incremental table for daily updates.
Common mistakes
- No single definition of metrics (multiple teams redefining KPIs). Fix: create and document a metrics layer with owners.
- Forgetting the grain: mixing daily and weekly aggregates leads to double counting. Fix: document grain in every model.
- Skipping tests and docs: models become fragile. Fix: add not_null/unique tests and fill descriptions as you go.
- Optimizing too early or too late: either over-engineering or slow dashboards. Fix: profile queries and iterate.
- Dashboards without a question: pretty but unused. Fix: tie every chart to a decision.
Mini project ideas
- Define and document three core KPIs for a mock business (formula, grain, caveats).
- Refactor a long SQL script into CTEs and add a window function to de-duplicate users.
- Add three dbt tests to an existing model and intentionally break one to see failure behavior.
- Design a one-page dashboard that answers a single question in under 10 seconds.
Next steps
Pick the profession below that matches your goals and start the roadmap. Use the fit test on this page for a quick signal, then dive into week 1.