Interview questions, practised as a mock interview
- 1Pick your roleThen the technology the interview is about. Each one is its own mock interview.
- 2Answer out loudOne question at a time, easy first. Say your answer before you show the one here.
- 3Mark itGot it, or review again. Next time, practise only the ones you marked.
Your role
Your role
Data Engineer
- Databricks
- How does Delta Lake ensure ACID compliance on Databricks?
- Explain the difference between Apache Spark and Databricks
- AWS
- What are the core components of the AWS data lake architecture?
- What is the difference between batch processing and stream processing? Provide examples of AWS services used for each.
- Azure
- A pipeline copies data from Blob Storage to Azure SQL Database. If the source file schema changes (e.g., a new column is added), how can ADF handle this without manual intervention?
- How can you ensure data consistency when updating a Delta Lake table in Azure Data Lake Storage Gen2 using ADF, especially when dealing with concurrent updates?
- PySpark
- What are RDDs, and how are they different from DataFrames and Datasets?
- What is a Spark job, and what are stages and tasks within it?
- SQL
- Can a table have multiple primary keys?
- Can a table have multiple UNIQUE constraints?
- System design
- Design a pipeline that brings the orders, customers and products tables from the company's PostgreSQL database into a lakehouse for daily reporting, with history and room to grow.
- Design a pipeline for website and app click events, about 50,000 events per second at peak, that feeds a dashboard updated every minute and keeps all raw events for analysis.
- Kafka and streaming
- How does Kafka keep messages in order, and what breaks that order? A team wants every event for one customer processed in the order it happened, and plans to add partitions later as traffic grows.
- What do acks=all and min.insync.replicas do together? A topic has 3 copies. Which settings lose no acknowledged message when a broker dies, and what happens to producers when 2 of the 3 brokers are down?
- dbt
- Why does dbt use ref() and source() instead of table names? A new engineer has written FROM analytics.stg_orders in a model and asks why the reviewer wants {{ ref('stg_orders') }}.
- How do you choose a dbt materialization? A project builds every model as a table, the nightly run now takes 3 hours, and a dashboard on a 2-billion-row events model is the slowest part.
- Airflow
- What is the data interval in Airflow? A new daily Dag with start_date set to today did not run today, and a colleague asks how to load the last 30 days it missed.
- An Airflow task failed after loading half its rows, was retried, and the table now has duplicates. Another task loads 'yesterday' with datetime.now(), and a rerun last week loaded the wrong day. How do you make these tasks safe to rerun?
Data Analyst
- Databricks
- What is a Databricks cluster?
- Barbara needs to join data from a Delta table with data residing in a cloud storage location. How can this be efficiently accomplished within a Databricks notebook, considering data access patterns and potential performance bottlenecks?
- AWS
- What are the core components of the AWS data lake architecture?
- What is the difference between batch processing and stream processing? Provide examples of AWS services used for each.
- SQL
- Can a table have multiple primary keys?
- Can a table have multiple UNIQUE constraints?
Data Scientist
- AWS
- What are the core components of the AWS data lake architecture?
- What is the difference between batch processing and stream processing? Provide examples of AWS services used for each.
- Azure
- Azure AI services (formerly Cognitive Services). Are they only accessible via REST APIs?
- What are the primary methods for deploying machine learning models as web services in Azure?
- SQL
- Can a table have multiple primary keys?
- Can a table have multiple UNIQUE constraints?
AI Engineer
- Agents
- What is an AI agent, and how is it different from a single chatbot call?
- Walk through the agent loop: what happens between the request and the final answer?
- LLM apps and RAG
- What is RAG, and when should you use it instead of fine-tuning or putting everything in the prompt?
- How do you write a prompt for a production LLM feature?
- Evaluation and safety
- What is the difference between jailbreaking and prompt injection, and how do defences differ?
- How do you build an evaluation set for an LLM feature?
- Prompting
- In Azure OpenAI, what distinguishes fine-tuning from prompt engineering as ways to adapt a pre-trained model?
- What are some key principles for crafting effective prompts for large language models (LLMs)?
- System design
- Walk me through a retrieval-augmented generation (RAG) pipeline, step by step. Where does it usually fail?
- Your LLM feature works in testing, but at the busiest hour of the day a share of requests fail with HTTP 429. How do you handle it?
Questions that trip people up
- hardYou've set up a Lakeflow declarative pipeline (formerly Delta Live Tables). If you modify the source data while the pipeline is running, what will happen to the pipeline's execution?It depends on the kind of table reading the source and the kind of change: appended data is picked up by the next update, while updates or deletes to a source that a streaming table reads make that flow fail, unless you tell it to skip them.Read the answer
- hardYou have a Delta table that's frequently updated. You need to perform an analytical query that requires a consistent snapshot of the data. You use df = spark.read.table("my_delta_table"). Are you guaranteed to get a consistent snapshot? What are the alternatives to ensure consistency?Yes for a single query: a query on a Delta table reads one committed version of the table, so concurrent writes never give it a half-written or mixed view.Read the answer
- hardHow can you implement disaster recovery for your Azure data platform, ensuring minimal data loss and downtime in case of an outage?Decide the recovery time (RTO) and recovery point (RPO) each part of the platform needs, then give every layer (storage, databases, compute and pipelines) a matching copy in a second region and a tested, scripted way to switch to it.Read the answer
- hardDesign a prompt to generate a summary of a technical article on renewable energy. What considerations would you take into account to ensure accuracy and completeness?Give the model the full article, say who the summary is for and what it must cover, require that every statement comes from the article, and fix the length and structure.Read the answer
- hardDescribe your strategy for enhancing the performance of Spark applications.I start by measuring: find the slow stages in the Spark UI, see whether time goes to reading, shuffling, skew, spilling or Python code, and fix the biggest cause first.Read the answer
- hardHow do you optimize a slow-performing SQL query? What tools or techniques would you use?Find the queries that actually cost the most, read their execution plans to see where the time goes, then fix the cause, usually a missing or unusable index, too much data read, a bad join or stale statistics, and measure again.Read the answer
How to use the answers
Each mock interview starts with the easy questions and ends with the hard ones. The easy ones check that you know the words. The hard ones ask you to choose between 2 ways of doing something and say why, which is what most interviews for a senior role come down to.
An answer here is one good answer, not the only one. If yours reaches the same point another way, it counts. If an interviewer asks a follow-up, it is usually about the trade-off the answer names.
A word you do not know is in the concepts, and the quizzes cover the same subjects in shorter questions.