Morgan Stanley Data Engineer Interview Questions (2026)

**Section 1 — The Context (The 'Why')** The primary challenge for this design in SQL is balancing scale, cost, and reliability. At scale, naive approaches fail: single points of failure cause cascades, schema evolution breaks consumers, and over-provisioning explodes cost. Failure modes include silent data loss from non-idempotent writes, cascading job failures from tight coupling, and operational burden from manual intervention. What makes this hard is the tension between latency requirements and operational simplicity. **Section 2 — The Diagram** ``` [Sources] --> [Ingest] --> [Transform] --> [Serve] Batch|Stream | | | v v v v [Staging] [Curate] [Marts] [BI|API] ``` **Section 3 — Component Logic** The **Ingest** layer isolates source systems using **partitioning strategies** (date, tenant) for efficient incremental loads. **Idempotency** is enforced via batch_id or watermark to enable safe retries without duplicates. The **Transform** layer applies business logic with **data skew mitigation** (salting for high-cardinality joins) and **backpressure handling** for streaming to prevent memory exhaustion. **TTL policies** on staging control retention and move cold data to cheaper storage. The **Serve** layer exposes via warehouse or API. **Fan-out patterns** let multiple consumers (BI, ML, API) read the same dataset without re-processing. **Exactly-once semantics** at load require merge keys and idempotent writes; use (business_key, batch_id) as idempotency key. **Partitioning strategies** must align with query patterns for partition pruning. Each component's role: Ingest decouples sources, Transform curates, Serve delivers. Why these choices: partitioning enables scale, idempotency enables retry, TTL controls cost, fan-out enables reuse. **Section 4 — The Trade-offs (The 'Senior' part)** - **CAP Theorem**: We choose AP (Availability + Partition Tolerance) for ingest and transform because eventual consistency is acceptable for analytics. Stale-by-minutes data is fine for dashboards. CP for the serve layer when BI requires accurate numbers. During partition events we cannot afford downtime. - **Cost vs. Performance**: Glue ($0.44/DPU-hr) vs EMR ($0.10/hr + EC2): Glue wins for bursty jobs under 2hr; EMR for sustained 8hr+ saving ~60%. MSK $0.21/broker-hr for streaming. S3 ~$0.023/GB. Right-size to avoid over-provisioning. - **Blast Radius**: Component failure triggers retry. DLQ isolates poison messages. Blast radius bounded by partition or consumer group. Self-heal: orchestration retries; idempotent sinks ensure consistency. If primary fails: downstream stale but no data loss with replay. **Section 5 — Pro-Tip** **Pro-Move**: Quantify with latency, cost, error rate. **Red Flag**: Vague answers without trade-offs or specific services. **Supplemental (Senior Context)**: In production, monitor partition skew, consumer lag, and merge duration. Use correlation IDs for traceability across pipeline stages. Schema evolution: prefer additive changes only; use Schema Registry for streaming to enforce compatibility. Consider data contract tests in CI to catch breaking changes early. Budget 10-20% overhead for replication, checkpoint storage, and DLQ. Data quality gates at each layer prevent bad data propagation. Right-size resources: profile before scaling; over-provisioning wastes budget. Document runbooks for common failures: broker restart, consumer rebalance, sink timeout. Establish SLOs per stage: ingest latency, transform duration, serve freshness. Review partition key choice: avoid high-cardinality keys that cause explosion; use composite keys (date, tenant) for balanced distribution. Test failure injection: kill executors, broker, sink to validate recovery. Optimize for the common case: most queries filter by date. Cold start mitigation: pre-warm connections, cache dimension lookups. Alert on lag exceeding 1hr, error rate above 1%. Cost optimization: lifecycle policies, spot instances, partition pruning. Lineage tracking enables impact analysis. Idempotency keys for replay. Backpressure handling prevents slow consumers from blocking producers. Fan-out patterns allow multiple consumers without re-processing. Exactly-once semantics require replayable source and idempotent sink. Data skew mitigation via salting for high-cardinality joins. Partitioning strategies must align with query patterns for pruning. CAP trade-off: AP for ingest and transform; CP for serve when BI needs accuracy. Blast radius bounded by partition and consumer group. Measure and iterate: latency percentiles, cost per record, error rate. Principal engineer tip: quantify before and after optimizations. Red flag: describing architecture without trade-offs. Glue versus EMR: Glue for bursty sub-2hr jobs; EMR for sustained 8hr+ saving 60%. MSK for Kafka; S3 for lake storage. Self-heal: orchestration retries; idempotent sinks ensure consistency. If primary fails, downstream goes stale but no data loss with replay. Design for operability: runbooks, dashboards, alerts. Avoid tight coupling between stages. Incremental processing reduces compute versus full refresh. Watermark-based deduplication enables idempotency. Partition evolution: add new partitions without rewriting. Retention policies balance cost and compliance. Test at scale: use production-size samples for validation. Always document trade-offs.