mixedbehavioral· L52024
Tell me about a time you resolved a data incident that impacted downstream analytics: describe how you identified the root cause, communicated with stakeholders, and implemented a fix.
Amazon Leadership Principle: Ownership / Deliver Results. Interviewers expect a STAR-format answer. High-scoring responses cover: (1) how the candidate detected the issue (monitoring alert, stakeholder report, data quality check), (2) root cause diagnosis (upstream schema change, late data, pipeline bug), (3) immediate mitigation (rerun, hotfix, downstream team notification), (4) long-term fix (alerting, validation checks), (5) measurable outcome (SLA recovery time, stakeholder trust). DE-specific context expected: should mention data pipeline, ETL job, or analytics dependency.
Pythonphone screen python· L42025
Given a list of daily prices, find the maximum profit from buying and selling once, and return the buy and sell day indices
Write a function best_trade(prices) that returns a tuple (profit, buy_day, sell_day) representing the maximum profit achievable by buying on buy_day and selling on sell_day (where sell_day > buy_day). If no profitable trade exists, return (0, -1, -1). If multiple trades yield the same maximum profit, return the earliest buy day.
Example:
prices = [7, 1, 5, 3, 6, 4]
Output: (5, 1, 4) (buy at index 1 for price 1, sell at index 4 for price 6)
prices = [7, 6, 4, 3, 1]
Output: (0, -1, -1)
prices = [2, 4, 1, 7]
Output: (6, 2, 3) (buy at index 2 for price 1, sell at index 3 for…
Data Modelingonsite data modeling· L62025
Design a data model to track a product from the vendor to the Amazon warehouse to delivery to the customer
Amazon Data Engineer Interview Loop data modeling question. Candidate must design an end-to-end supply chain data model with entities for: vendors, purchase_orders, warehouse_inventory, shipments, delivery_events, and customers. Expected to define: grain of each table (one row per shipment leg vs one row per product unit), surrogate keys for each entity, foreign key relationships linking the supply chain stages, and how to handle split shipments where one order goes to multiple warehouses. Interviewer probed on normalization decisions and how to track product state transitions (ordered,…
SQLphone screen sql· L42023
Amazon DE phone screen: 4 SQL questions escalating from simple single-table output to multi-table joins, CTEs, and window functions; recruiter confirmed SQL + data modeling + Python (no algorithms)
Pipeline Architectureonsite pipeline architecture· L72025
How would you build a data pipeline around an AWS product that can handle increasing data volume?
Amazon Data Engineer Interview Loop system design question targeting principal-level candidates. Open-ended question requiring discussion of: choice of AWS services (Kinesis vs MSK for ingestion, Glue vs EMR for transformation, Redshift vs Athena for querying), auto-scaling strategies for each component, partitioning and compaction strategies for S3 data lake, cost optimization at scale, and monitoring/alerting for pipeline health. Interviewer expected candidates to discuss specific throughput numbers and back-of-envelope capacity calculations. Source provided limited follow-up detail.
mixedbehavioral· L62024
Describe a time when you improved the scalability or cost efficiency of a data pipeline: what was the problem, what changes did you make, and what was the measurable impact?
Amazon Leadership Principle: Frugality / Think Big. Expected STAR answer covers: (1) context of the pipeline (batch or streaming, scale of data, business criticality), (2) what triggered the review (cost spike, SLA miss, capacity planning), (3) specific technical changes made (partitioning, compression, query optimization, right-sizing clusters, caching), (4) quantified result (cost reduction %, throughput increase, latency improvement). Interviewers at L6+ look for cross-team influence and org-level impact. DE-specific framing required.
Pythononsite python· L52025
Given a JSON object with nested objects, write a function that flattens all the objects to a single key-value dictionary
Amazon Data Engineer Interview Loop coding question. Given a nested JSON/dict structure like {"a": 1, "b": {"c": 2, "d": {"e": 3}}}, flatten to {"a": 1, "b.c": 2, "b.d.e": 3} using dot-separated keys. Expected approach: recursive function that traverses the dict, building up a key prefix as it descends. Must handle: nested dicts at arbitrary depth, mixed value types (ints, strings, lists), and empty nested dicts. Follow-up may ask about iterative vs recursive approach and stack overflow concerns for deeply nested objects.
Data Modelingonsite data modeling· L52025
Implement a Type 2 Slowly Changing Dimension for customer profiles that preserves historical changes in attributes such as address and Prime membership status.
Schema design task: dim_customer table must track history of mutable attributes (shipping_address, prime_status, email). SCD Type 2 pattern requires: surrogate key (customer_sk), natural key (customer_id), effective_date, expiry_date (or is_current boolean), version_number. INSERT logic for new records, UPDATE logic to close old records (set expiry_date = current_date - 1 day, is_current = false). Interviewers follow up on: how to handle late-arriving records, index design for performance, and how downstream fact tables reference the dimension (by surrogate key not natural key). From Amazon…
SQLonsite sql· L6
Identify the top two highest-grossing products within each category for the year 2022; schema: product_spend(category VARCHAR, product VARCHAR, user_id INTEGER, spend DECIMAL, transaction_date DATETIME)
mixedbehavioral· L52023
Tell me about a time you were in a meeting and had a different opinion from everyone else in the room; what did you do and what was the outcome?
Amazon Leadership Principle: Have Backbone; Disagree and Commit. Expected STAR answer: (1) describe the context and the decision being made, (2) explain your opposing view and how you backed it with data or reasoning, (3) what happened — did you escalate, present a counter-proposal, or ultimately commit to the group decision, (4) final outcome and retrospective. For a DE role, this often surfaces in discussions about technology choices (e.g., Spark vs. dbt, warehouse vs. data lake), data modeling decisions, or build vs. buy choices. From IGotAnOffer Amazon DE interview guide.
