SQL & Data Transformation
This category evaluates your ability to write clean, performant, and complex analytical SQL queries. You must demonstrate mastery over window functions, Common Table Expressions (CTEs), complex join mechanics, and handling edge cases like out-of-order event data without relying on vendor-specific syntax wrappers.
- Write a query to rank transactions within customer sessions, given events that arrive out of order with lagging timestamps.
- Given tabular data, write a pivot-table query using plain SQL syntax without using built-in PIVOT functions.
- Write a SQL query using window functions and CTEs to calculate a rolling average across continuous time series data.
- Given an Employees table where each row's age was circularly shifted to the previous row's age, write a SELECT query to restore the original data.
- Write a query using joins and subqueries to find all departments with an average salary greater than 500.
Data Modeling & Architecture
These questions test your ability to translate real-world business domains into efficient, extensible schemas. Candidates must articulate clear trade-offs between normalization (3NF) and denormalization (Star/Snowflake), define granular business entities, and enforce strict idempotency and Slowly Changing Dimensions (SCD).
- Design a data model for an e-commerce platform: reason through grain, facts versus dimensions, and denormalization strategies.
- Design a vendor payment system from scratch: establish fact and dimension tables, business event granularity, and SCD type strategies.
- Design a denormalized data warehouse schema that remains idempotent under duplicate batch inserts.
- Design a clickstream data model tracking promotions and user interaction events at scale.
- Compare Star schema versus Snowflake schema design and justify when to normalize versus denormalize in an OLAP data warehouse.
System Design & Distributed Systems
This category evaluates your ability to architect fault-tolerant, scalable, end-to-end data pipelines. You will be expected to defend specific architectural choices (such as Lambda vs. Kappa or Kinesis vs. Kafka), articulate backpressure and consumer scaling strategies, and optimize compute engines like Apache Spark.
- Design an end-to-end data pipeline architecture to process 1 TB of incoming event data per day, comparing Spark, Hadoop, and Flink.
- Propose and defend a streaming system design at scale: explain key differences between AWS Kinesis and Apache Kafka, and how you handle backpressure and consumer scaling.
- Design a Change Data Capture (CDC) pipeline and defend your choice between Debezium, a custom solution, or managed AWS tooling.
- When serving a real-time executive dashboard, would you choose an OLAP or OLTP backend storage architecture, and why?
- How do broadcast joins differ from hash joins in Apache Spark, and how do you prevent data skew during large-scale shuffle operations?
Coding & Data Structures
Coding rounds test basic algorithms, string/array manipulations, and plain-language Python processing. Interviewers place heavy emphasis on how your code scales when processing millions of records in memory or via distributed APIs like PySpark.
- Re-implement a SQL pivot and deduplication logic using plain Python without relying on pandas.
- Solve an array/hashmap lookup problem (similar to two-sum logic) with optimal time complexity.
- Write a function that takes two strings and determines whether the second string is a valid rotation of the first.
- Solve a dataset transformation problem using PySpark on a sample data structure.
- Write a Python script using basic dictionaries and file operations to parse and extract structured insights from unstructured file logs.
Leadership Principles & Scenario Handling
At Amazon Services, behavioral questions carry as much weight as technical skills. Responses must be structured using the STAR method (Situation, Task, Action, Result) and provide concrete, data-backed evidence aligned with Leadership Principles like Ownership, Bias for Action, and Dive Deep.
- Tell me about a time you discovered a critical mistake or data quality issue in a production pipeline and how you resolved it.
- Describe a situation where you had to make a technical compromise or short-term decision for long-term architecture gain.
- Tell me about a time you had to make an urgent pipeline decision or proceed with a project without having all the required details.
- Describe a scenario where you experienced a misalignment between your engineering team's plans and your customer's needs. How did you resolve it?
- Walk me through how you would handle an end-to-end pipeline failure incident, including mitigation, stakeholder communication, and post-mortem documentation.
