Master Every Technical Round of the Data Analyst Interview
BI tools, data modelling, ETL concepts, and system thinking. Go into every technical round knowing exactly what to expect — and how to answer with confidence.
Most data analyst interviews beyond SQL and Python include one or more of these technical rounds. Know which ones your target company uses — and prepare accordingly.
BI Tools & Dashboarding
Build a dashboard live in Power BI or Tableau, or walk through one you’ve built. Interviewers test whether you can turn raw data into a decision-ready chart with the right filters, KPIs, and drill-downs.
Explore BI topics →Data Modelling
Design a schema for a given business problem — star schema, fact and dimension tables, normalisation. Common at companies with data warehouses (Redshift, BigQuery, Snowflake).
Explore modelling topics →ETL & Data Pipeline Concepts
Explain how data flows from source to warehouse. You don’t need to write Airflow code, but you must explain ELT vs ETL, staging layers, incremental loads, and common pipeline problems.
Explore ETL topics →System & Analytical Thinking
Design a metrics system for a new product, think through a data quality issue, or propose an analytics architecture. This tests how you reason — not just what you know.
Explore frameworks →The tools interviewers actually test — and what they look for in each one.
Power BI
The most tested BI tool in UK and Indian data analyst interviews. Know how to build a report from scratch in under 20 minutes.
- Connecting to CSV, SQL, and Excel
- Building visuals — bar, line, card, slicer
- DAX — CALCULATE, SUMX, RELATED
- Row-level security basics
- Publishing to Power BI Service
Tableau
Common at product and consulting companies. Interviewers test LOD expressions, calculated fields, and storytelling with data.
- Connecting data sources and blending
- Dimensions vs measures
- LOD expressions (FIXED, INCLUDE, EXCLUDE)
- Dual-axis and combined charts
- Dashboard actions and filters
Looker / Looker Studio
Increasingly common at tech startups. Looker uses LookML — a modelling layer you’ll be asked to explain conceptually.
- Looker Studio — connect and visualise
- LookML concepts: explores, views, joins
- Dimensions and measures in LookML
- Drilling into reports
Excel & Google Sheets
Still tested at mid-size companies. Pivot tables, VLOOKUP, and dynamic charts are the big three.
- Pivot tables with grouping and % of total
- VLOOKUP vs INDEX-MATCH vs XLOOKUP
- Conditional formatting for dashboards
- Dynamic charts linked to pivot tables
DAX Fundamentals
Power BI’s formula language. Interviewers ask you to write or explain 3–5 DAX measures on the spot.
- CALCULATE and filter context
- Time intelligence — DATESYTD, SAMEPERIODLASTYEAR
- RANKX for ranked measures
- DIVIDE for safe division
Dashboard Design
It’s not just about the tool — interviewers evaluate your design thinking: which chart, which KPI, what goes on page 1.
- Choosing the right chart type
- Designing for the right audience
- KPI cards vs trend lines
- Colour, contrast, and accessibility
Schema design questions are asked at companies with proper data warehouses. Know the vocabulary and be able to sketch a model from a business problem in 5 minutes.
Star Schema
The default warehouse model — one central fact table surrounded by denormalised dimension tables. Fast for analytics queries.
- Fact table — events and measures
- Dimension tables — who, what, where, when
- Foreign key relationships
- Why it’s fast for GROUP BY
Snowflake Schema
A star schema with normalised dimension tables. More space-efficient but adds JOIN complexity — know the trade-offs.
- Splitting city → country dimension
- Reduced storage vs query complexity
- When to use vs star schema
Normalisation
1NF, 2NF, 3NF — OLTP databases are normalised. Know why warehouses deliberately denormalise for performance.
- 1NF — atomic values, no repeating groups
- 2NF — no partial dependencies
- 3NF — no transitive dependencies
- OLTP vs OLAP trade-off
Keys & Relationships
Primary, foreign, surrogate, and natural keys. Interviewers use these to test whether you understand how tables relate.
- Natural vs surrogate keys
- Composite keys
- One-to-many vs many-to-many
- Bridge tables for M2M
Cloud Warehouses
BigQuery, Snowflake, and Redshift are common in interviews. Understand the architecture even if you haven’t used them daily.
- Columnar storage and why it’s fast
- Partitioning and clustering
- Compute vs storage separation
- Materialised views
Slowly Changing Dimensions
SCD Type 1, 2, and 3 — how to handle dimension changes over time. A classic senior analyst question.
- Type 1 — overwrite (no history)
- Type 2 — new row per change (full history)
- Type 3 — add column (limited history)
- When each type is appropriate
You don’t need to build pipelines to talk about them fluently. These concepts come up in every senior data analyst interview.
ETL vs ELT
ETL transforms before loading (traditional). ELT loads raw first, then transforms in the warehouse (modern cloud approach). Know why the shift happened.
- ETL — Informatica, SSIS, Talend
- ELT — dbt, BigQuery, Snowflake
- Why ELT wins for cloud warehouses
Batch vs Streaming
Batch processes data in scheduled chunks; streaming processes events in near real-time. Know the trade-off for each use case.
- Batch — Airflow, cron, Spark
- Streaming — Kafka, Kinesis, Flink
- Lambda and Kappa architectures
Incremental Loads
Full refresh vs incremental — loading only new or changed rows since the last run. Critical for large tables.
- Watermark columns (updated_at)
- CDC — Change Data Capture
- Upsert patterns (MERGE statement)
dbt Basics
dbt (data build tool) is the modern standard for ELT transformation. Increasingly mentioned in data analyst job descriptions.
- Models — SQL SELECT as a model
- ref() for dependency management
- Tests — not_null, unique, accepted_values
- Materialisation — view vs table
Airflow & Orchestration
Apache Airflow is the most common orchestration tool. Know what a DAG is and how you’d describe the concept in an interview.
- DAG — Directed Acyclic Graph
- Operators — Python, Bash, SQL
- Sensors, dependencies, retries
- Monitoring and alerting
Data Quality
How do you know your pipeline is producing correct data? Interviewers love this question — most candidates can’t answer it well.
- Null checks and row count reconciliation
- Great Expectations / dbt tests
- Anomaly detection alerts
- Data lineage tracking
These questions have no single right answer — they test how you structure your thinking. Use frameworks and always anchor to business impact.
Metric Design Framework
When asked “how would you measure X?”, follow this structure: define the goal → identify the primary metric → identify guardrail metrics → define the denominator (per user? per session?) → account for segmentation (device, region, user cohort).
Root Cause Analysis Framework
For “a metric dropped — diagnose it”: first confirm the data isn’t broken → check if it’s global or segment-specific → rule out external factors → break the metric into its components → form and test hypotheses. Never guess before ruling out data issues.
Data Architecture Thinking
For “design an analytics system for X”: identify sources → choose ingestion layer (batch/stream) → define warehouse model (star schema) → define transformation layer (dbt) → define consumption layer (BI tool or API) → define quality checks at each layer.
A/B Test Design
Define the hypothesis → choose the primary metric and guardrails → calculate sample size → choose randomisation unit (user, session, device) → define success criteria → plan for novelty effects and leakage → plan the ship or no-ship decision.
Stakeholder Communication
A common but underestimated technical question: “how do you present a complex analysis to a non-technical stakeholder?” Lead with the insight, not the method. One chart, one headline. Numbers in context. Recommend a decision.
A common take-home task: build a reusable SQL view that feeds a BI dashboard. Interviewers look for clarity, correct grain, and meaningful KPIs.
ETL design question — explain and write the SQL for an incremental load that only processes new or updated rows.
These questions have been asked at Google, Amazon, Flipkart, Swiggy, and more — grouped by difficulty and topic.
🖥️ BI & Dashboard Questions
What is the difference between a dimension and a measure in Power BI / Tableau?
You have daily sales data by product and region. Design a Power BI dashboard for the VP of Sales — describe the pages, visuals, and KPIs you would include.
Write a DAX measure to calculate month-to-date revenue compared to the same period last year, expressed as a percentage change.
Your Power BI dashboard is running very slowly for the finance team. Walk through how you would diagnose and fix the performance issue.
Explain what a LOD expression is in Tableau and give an example of when you’d use FIXED vs INCLUDE vs EXCLUDE.
⭐ Data Modelling Questions
What is the difference between a fact table and a dimension table? Give a real example from an e-commerce business.
Design a data model for a food delivery platform like Swiggy. Identify at least 4 fact and dimension tables, and explain the grain of each fact table.
What is a Slowly Changing Dimension? Explain Type 1, 2, and 3 with an example using a customer’s address.
When would you use a snowflake schema instead of a star schema? What are the query performance implications?
🔄 ETL & Pipeline Questions
What is the difference between ETL and ELT? Which is more common in modern cloud data stacks and why?
Explain what an incremental load is and how you would implement one. What column would you use to track which rows to load?
Your daily data pipeline failed at 2am and the dashboard shows stale data. Walk me through how you would investigate and fix it.
What is dbt and how does it fit into the modern data stack? Explain models, refs, and tests to someone who hasn’t used it.
You discover your daily order count in the warehouse is 15% lower than the source system. How do you investigate and fix this data discrepancy?
You don’t need to master all of these — but you should be able to speak confidently about each one and know when to use it.
Power BI
Microsoft BI. Most common in UK, banking, consulting interviews.
Must KnowTableau
Popular at product companies and consulting firms globally.
Must KnowLooker
Google Cloud’s BI tool. Growing in startups and tech companies.
Good to KnowSnowflake
Cloud warehouse. Star schema, clustering, and compute concepts.
Must KnowBigQuery
Google’s serverless warehouse. Common at fintech and GCP shops.
Good to Knowdbt
SQL transformation tool. Now mentioned in most modern DA job specs.
Must KnowAirflow
Pipeline orchestration. Know DAGs conceptually — not just for engineers.
Good to KnowKafka
Streaming platform. Understand publish-subscribe pattern conceptually.
AwarenessThese are the questions candidates struggle with most — with the structured answers that impress interviewers.
The terms you must be able to define clearly in a technical interview — no hesitation.
| Term | What it means | Where it comes up |
|---|---|---|
| Star Schema | Central fact table surrounded by denormalised dimension tables | Data modelling round |
| Grain | The most atomic event represented by one row in a fact table | Data modelling round |
| SCD Type 2 | Track historical changes in a dimension by adding a new row per change | Data modelling round |
| ETL | Extract, Transform, Load — transform before loading to warehouse | ETL round |
| ELT | Extract, Load, Transform — load raw, transform inside warehouse | ETL round |
| Incremental load | Load only new/changed rows since the last pipeline run | ETL round |
| DAG | Directed Acyclic Graph — Airflow’s model for pipeline task dependencies | ETL round |
| dbt model | A SQL SELECT statement that dbt materialises as a table or view | ETL round |
| Columnar storage | Stores data by column rather than row — fast for aggregation queries | Warehouse / modelling |
| DAX CALCULATE | Power BI formula that changes the filter context of a measure | BI tools round |
| LOD expression | Tableau formula that controls which dimensions define the aggregation level | BI tools round |
| Partition pruning | BigQuery/Snowflake skips irrelevant partitions — reduces query cost | Cloud warehouse round |
A focused 2-week plan to go from “I’ve heard of these tools” to “I can answer any technical round question confidently.”
Days 1–3: BI Tools
Install Power BI Desktop (free). Load a CSV dataset and build a complete report: KPI cards, bar chart by category, line trend, and a slicer. Publish to Power BI Service. Write 3 DAX measures: total revenue, MoM growth, and a RANKX measure.
Days 4–5: Data Modelling
Draw a star schema for two businesses: an e-commerce platform and a ride-hailing app. Identify fact tables, dimension tables, grain, and foreign keys. Practice explaining SCD Type 2 out loud using a customer address example.
Days 6–8: ETL Concepts
Read the dbt documentation introduction and set up a free dbt Cloud project. Understand ETL vs ELT, incremental load patterns, and what a DAG is in Airflow. Practice explaining each concept in 60 seconds — no jargon.
Days 9–11: Practice Questions
Answer all 14 questions from the question bank above — out loud, timed at 3 minutes per answer. Record yourself and review. Focus on questions where you hesitate or use filler words instead of structured answers.
Days 12–14: Mock Interview
Book a mock technical round with our mentors. We simulate a real 45-minute technical interview — BI, data modelling, and ETL questions — with live feedback on your answers and a written report of exactly what to improve before your actual interview.
Ready to crack your technical round?
Book a free mock technical interview with our expert mentors — BI, data modelling, ETL, and system thinking all in one session.
Book Free Technical Mock