Hard-level sql questions from real data engineering interviews.
These hard sql questions are selected from real interviews at top companies. Each question includes a detailed expert answer and pro tip to help you nail your interview. This set leans toward senior-level depth (51 of 51 are tagged hard). Recurring themes are optimization, partition, and join — these patterns appear most often in real interviews and reward the deepest preparation. These questions have been reported across 46 companies including Incedo and EY. Average answer is around 2 minutes of reading — plan roughly 2 hours to work through the full set thoughtfully.
This collection contains 51 curated questions: 0 easy, and 51 hard. The distribution skews toward harder problems, reflecting the depth expected in senior-level interviews.
The most frequently tested areas in this set are optimization (41), partition (40), join (38), sql (37), bigquery (7), and snowflake (7). Focusing on these topics will give you the highest return on your preparation time.
Hard questions often appear in senior and staff-level rounds; attempt them after you're comfortable with the basics. For each question, try answering before revealing the solution. Use our AI Mock Interview to simulate real interview conditions and get instant feedback on your responses.
What is a Common Table Expression (CTE), and when would you use it?
What is the difference between a primary key and a unique key?
Explain Fact and Dimension Tables with examples.
Joins and window functions - INNER, LEFT, RIGHT, FULL OUTER, ROW_NUMBER(), RANK(), DENSE_RANK()
Difference Between Internal and External Tables in BigQuery
How do you optimize a long-running SQL query?
Cloud Architecture - explain
Consolidate hotel reviews and create a dashboard. Design a data model for the reviews.
Create Spark Session, read CSV, join, and write as table. Provide example code.
Data Warehouse Design from scratch
Describe a challenging project where you optimized a complex ETL process.
Describe a situation where you had to redesign a data model to meet changing business needs
Design a Custom API that can query a backend server and return customer data such as the number of orders placed by a user based on their user ID
Design a daily ETL pipeline to ingest API data into BigQuery.
Design a financial database system focusing on database models, schema design, partition keys, and query optimization techniques.
Design a relational data model for a sales database, incorporating normalization techniques
Design a structure (data model) that allows efficient querying of movies based on multiple search criteria (title, genre, actor, director).
Design the data model for an ETL pipeline that ingests data from a database and loads it into Snowflake
Designing backend architecture for SQL Warehouse?
Designing scalable data models - explain approach
Difference Between Truncate/Delete and Union/Union All – Performance and Usage
Explain BigQuery Architecture.
Articulate the architectural decisions, scalability trade-offs, and cost implications of designing an AWS data platform. How would you justify glue vs. EMR, Redshift vs. Athena, and when would each choice become cost-prohibitive at scale?
Explain the architectural rationale for using LeftAntiJoin vs. NOT IN vs. NOT EXISTS in a distributed context. When does LeftAntiJoin become a performance or scalability bottleneck, and how do broadcast vs. shuffle joins affect cost?
Explain the architectural trade-offs when optimizing a query on 100M+ rows: indexing vs. partitioning vs. materialized views. When does each approach become cost-prohibitive or operationally burdensome, and how do you quantify impact?
Explain bloom filters in Spark: how they reduce I/O and when they introduce false positives that hurt performance. What are the scalability and cost implications of enabling dynamic partition pruning and bloom filter pushdown at petabyte scale?
Design a star schema for retail analytics (e.g., Adidas). Explain the dimensional modeling choices, SCD strategy, and how you would scale this schema for global multi-currency, multi-region deployments. What are the refresh and storage cost implications?
Explain the Medallion Architecture (Bronze, Silver, Gold).
Given a CSV file with raw customer transactions, design an ETL pipeline that cleans data, aggregates total sales by region and product, and loads into target table
How can you automate data insertion into BigQuery using Python?
How do you design a scalable and fault-tolerant data warehouse on a cloud platform?
How to Use Dataflow with BigQuery
How would you design a data model for an e-commerce platform?
How would you optimize a SQL query for better performance when working with large datasets?
In Python, process a large CSV in chunks and remove duplicate records based on email and timestamp.
Indexing - True/False question on indexes and query optimization
Kafka Basics - architecture, topics, partitions, producers, consumers, Zookeeper
SQL Query Design: employees with highest salaries within each department
Schema Design: Star vs. Snowflake schema differences
Snowflake Tech Stack: Deployment on Azure, cluster sizing considerations, and overall data warehouse design?
Time and cost comparisons for executing the same query in Snowflake and Spark.
What are the benefits of BigQuery Warehouse?
What are the benefits of using a cloud data warehouse (e.g., Redshift, Snowflake) for analytics?
What are the key design principles for a cloud-based data warehouse?
What are the trade-offs between relational databases and NoSQL for financial data?
What considerations are important when designing a dimensional model for a ridesharing app?
What is CTE in SQL?
What is a Data Warehouse, and can you explain its Tier-1 and Tier-2 architecture?
What optimizations would you apply for partitioning strategies?
What strategies and technologies would you consider when designing a data warehouse architecture for efficient data storage and retrieval?
You need to design a Kafka topic for a logging service. How would you decide the number of partitions and the key for partitioning to balance throughput and ordering requirements?
The Data Engineering Interview Answer Vault bundles 750+ reviewed answers into 7 focused PDF volumes — SQL, Spark, Python, System Design, Cloud, Behavioral, and Data Modeling. Study on any device, no subscription required.
800+ hands-on courses — Grokking System Design, Coding Patterns, and AI mock interviews for your DE loop.
Turn any topic or your own notes into an interactive, personalized course in 60 seconds.
The book that gets data engineers through system-design rounds. Essential reading.
Some links below are affiliate links. If you buy through them we may earn a small commission at no extra cost to you — it helps keep DataEngPrep free.
Reading answers is step one. Get instant AI feedback on your answers, run mock interviews, and track readiness — built specifically for data engineering interviews.