roadmap
- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
Prepare for BI analyst interviews, from SQL screens and metric questions to dashboard and data-model cases. Covers querying, statistics, dimensional modeling, BI platforms, and privacy rules.
3229 of 3229 questions in your plan: 0 known, 0 in progress, 0 due, 0 skipped, 3229 new. 0 cut, outside your plan.
BI analyst loops are short on algorithms and long on judgment about numbers. The usual sequence is a recruiter call, a SQL screen taken live or as a take-home, a case round built on a business question, and a presentation of your own work, a dashboard or the take-home, to the people who would read it. Middle and senior candidates also get a modeling conversation: a messy source is handed over and a star schema asked for, often in front of the stakeholders themselves. The SQL questions are traps on familiar ground: a revenue total that doubles after a join to a one-to-many table, a `NOT IN` that returns nothing because the subquery held one `NULL`, the latest row per customer, which separates people who reach for a window function from people who reach for a self-join. The case round asks you to explain a metric that moved, and what is graded is the sequence of your checks: did the instrumentation change, did the denominator change, did the mix of segments shift while every segment held steady. Statistics questions ask whether a difference is real, and whether you know that a p-value speaks about the data under a null hypothesis, not about the odds that the null is true. Modeling questions turn on grain and history: what one row of the fact table is, and why a type 2 dimension needs a current-row filter in a report that wants today's attributes. From middle level up, expect who may see which rows of a dashboard, and what happens to a report when a customer asks to be erased. Seniority shows in what surrounds the number rather than in the query: which decision it feeds, its grain and its denominator, the two ways it could still be wrong, and a delivery date that holds anyway. In the presentation round it is one chart that makes a claim rather than a page that makes none.
[SQL](/topics/lang-sql) leads because the SQL screen leads, and the relational modules behind it explain the behaviour that screen tests: [three-valued logic](/topics/db-relational-model-three-valued-logic) is why a comparison with `NULL` drops rows, [normalization](/topics/db-relational-schema-design) is why source tables look the way they do, and constraints are what a warehouse usually lacks and an analyst checks by hand. [Indexes](/topics/db-relational-indexing), query plans and [materialized views](/topics/db-relational-views-server-logic-materialized-views) close the stretch; a dashboard that takes a minute to load is this material. Statistics follows, because producing a number is half the job and saying what it means is the other half. The stretch runs from [describing a distribution and diagnosing a metric change](/topics/found-math-statistics-descriptive) through probability to [inference](/topics/found-math-statistics-inference) and regression, so that confidence intervals and test power are in place before [experiment design](/topics/found-math-statistics-ab-testing) uses them; time series finishes it, where seasonality questions about a weekly dashboard belong. Python arrives after both, and the selection is deliberate: no classes, no concurrency, no web frameworks. The core language modules are what a notebook needs; the standard-library stretch favors what an analyst hits daily, [time zones and DST](/topics/lang-python-stdlib-dates-times) above all, because two reports that disagree on a day's total usually disagree about midnight. The module on [tables held in memory](/topics/data-libraries-concepts) then covers dataframe thinking, split-apply-combine, long versus wide, absent values, without committing to a library. Then the plan turns from reading data to shaping it. [Dimensional modeling](/topics/data-modeling-dimensional) supplies the modeling round's grain question and [slowly changing dimensions](/topics/data-modeling-scd) its history question; [columnar storage](/topics/db-analytical-storage) explains why those models are fast on an analytical engine and slow on a transactional one. [Orchestration concepts](/topics/data-orchestration-concepts), idempotency and backfills in particular, and then [dbt](/topics/data-dbt) describe how the model is built and refreshed, dbt being the tool through which analysts most often own the transformation layer. Only now do the [BI platforms](/topics/data-bi) appear, Excel and the Python app frameworks among them. The placement is the argument: a chart is the last layer, and everything above it in the plan decides whether the number on it is right. Machine learning appears only as problem framing, enough to recognise a prediction problem and hand it on. [Governance](/topics/data-governance) and the privacy regimes, [GDPR](/topics/proto-gdpr) and CCPA, sit after the dashboards because the analyst is usually the person who puts personal data in front of an audience. Last come the everyday Git a dbt project needs and working with a coding assistant, then the [behavioral round](/topics/found-behavioral) and the mechanics of the loop, from screen to offer.
Coming from Excel or a reporting tool, start with the SQL module and stay there until joins, grouping and window functions are routine, then take dimensional modeling before the rest of the warehouse stretch: the case round will ask for a star schema whether or not the job title says analytics engineer. Cut level to its junior end while you do. Already a senior analyst or an analytics engineer? The SQL and Python stretches are revision; the modeling, orchestration, governance and statistics modules are where a senior loop spends its time, and study order by topics keeps them together. Statistics is the usual weak spot of candidates who grew up in dashboards. With an interview in a week, raise importance to must know, leave study order where it opens and work the plan no further than Python; a last pass with study order switched to random shows whether the answers survive out of sequence. Feeding the warehouse is outside this plan: no distributed processing engine, no streaming platform, no scheduler product and no particular cloud warehouse. Building and operating those pipelines is the [Data Engineer roadmap](/roadmaps/data-engineer). When one tool carries the loop, the [Power BI](/roadmaps/power-bi) and [Tableau](/roadmaps/tableau) roadmaps go deeper than this plan does.
BI analyst loops are short on algorithms and long on judgment about numbers. The usual sequence is a recruiter call, a SQL screen taken live or as a take-home, a case round built on a business question, and a presentation of your own work, a dashboard or the take-home, to the people who would read it. Middle and senior candidates also get a modeling conversation: a messy source is handed over and a star schema asked for, often in front of the stakeholders themselves.
The SQL questions are traps on familiar ground: a revenue total that doubles after a join to a one-to-many table, a NOT IN that returns nothing because the subquery held one NULL, the latest row per customer, which separates people who reach for a window function from people who reach for a self-join. The case round asks you to explain a metric that moved, and what is graded is the sequence of your checks: did the instrumentation change, did the denominator change, did the mix of segments shift while every segment held steady. Statistics questions ask whether a difference is real, and whether you know that a p-value speaks about the data under a null hypothesis, not about the odds that the null is true. Modeling questions turn on grain and history: what one row of the fact table is, and why a type 2 dimension needs a current-row filter in a report that wants today's attributes. From middle level up, expect who may see which rows of a dashboard, and what happens to a report when a customer asks to be erased.
Seniority shows in what surrounds the number rather than in the query: which decision it feeds, its grain and its denominator, the two ways it could still be wrong, and a delivery date that holds anyway. In the presentation round it is one chart that makes a claim rather than a page that makes none.
SQL leads because the SQL screen leads, and the relational modules behind it explain the behaviour that screen tests: three-valued logic is why a comparison with NULL drops rows, normalization is why source tables look the way they do, and constraints are what a warehouse usually lacks and an analyst checks by hand. Indexes, query plans and materialized views close the stretch; a dashboard that takes a minute to load is this material.
Statistics follows, because producing a number is half the job and saying what it means is the other half. The stretch runs from describing a distribution and diagnosing a metric change through probability to inference and regression, so that confidence intervals and test power are in place before experiment design uses them; time series finishes it, where seasonality questions about a weekly dashboard belong.
Python arrives after both, and the selection is deliberate: no classes, no concurrency, no web frameworks. The core language modules are what a notebook needs; the standard-library stretch favors what an analyst hits daily, time zones and DST above all, because two reports that disagree on a day's total usually disagree about midnight. The module on tables held in memory then covers dataframe thinking, split-apply-combine, long versus wide, absent values, without committing to a library.
Then the plan turns from reading data to shaping it. Dimensional modeling supplies the modeling round's grain question and slowly changing dimensions its history question; columnar storage explains why those models are fast on an analytical engine and slow on a transactional one. Orchestration concepts, idempotency and backfills in particular, and then dbt describe how the model is built and refreshed, dbt being the tool through which analysts most often own the transformation layer.
Only now do the BI platforms appear, Excel and the Python app frameworks among them. The placement is the argument: a chart is the last layer, and everything above it in the plan decides whether the number on it is right. Machine learning appears only as problem framing, enough to recognise a prediction problem and hand it on. Governance and the privacy regimes, GDPR and CCPA, sit after the dashboards because the analyst is usually the person who puts personal data in front of an audience. Last come the everyday Git a dbt project needs and working with a coding assistant, then the behavioral round and the mechanics of the loop, from screen to offer.
Coming from Excel or a reporting tool, start with the SQL module and stay there until joins, grouping and window functions are routine, then take dimensional modeling before the rest of the warehouse stretch: the case round will ask for a star schema whether or not the job title says analytics engineer. Cut level to its junior end while you do. Already a senior analyst or an analytics engineer? The SQL and Python stretches are revision; the modeling, orchestration, governance and statistics modules are where a senior loop spends its time, and study order by topics keeps them together. Statistics is the usual weak spot of candidates who grew up in dashboards.
With an interview in a week, raise importance to must know, leave study order where it opens and work the plan no further than Python; a last pass with study order switched to random shows whether the answers survive out of sequence.
Feeding the warehouse is outside this plan: no distributed processing engine, no streaming platform, no scheduler product and no particular cloud warehouse. Building and operating those pipelines is the Data Engineer roadmap. When one tool carries the loop, the Power BI and Tableau roadmaps go deeper than this plan does.