Question 1

Write a query to find the top three highest-paid employees in each department using window functions.

Accepted Answer

**Architectural Logic:** Same pattern as ID 116—window + filter. DENSE_RANK for 'include ties'; ROW_NUMBER for exactly 3. **Query:** WITH ranked AS (SELECT employee_id, name, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) SELECT employee_id, name, department, salary FROM ranked WHERE rn <= 3 ORDER BY department, salary DESC....

Question 2

How do you see your career evolving in the next 3-5 years?

Accepted Answer

Situation: I've been Senior DE for 3 years. Task: Articulate realistic path. Action: I aim for Staff/Principal—owning org-wide data strategy. I'm developing cross-functional influence, distributed systems depth, mentorship (sponsored 2 engineers), and external presence....

Question 3

If your team disagrees on the approach to solving a problem, how do you manage the situation?

Accepted Answer

Situation: Team split on data modeling approach. Task: Facilitate fact-based decision. Action: I had each perspective documented; ran decision meeting with timebox. Suggested spike to validate. Used RACI for accountability. Documented decision and rationale. As lead, I made final call when deadlocked, explaining reasoning....

Question 4

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?

Accepted Answer

Architectural Logic: Indexing—speeds reads, slows writes, costs storage; partitioning—prunes at scan, requires partition design; materialized views—precompute, cost refresh. Why each: Index for point/range lookup on filter columns; partition for time/entity-based pruning; MV for repeated aggregations. Scalability: Indexes on wide tables → write amplification; over-partitioning → small-file problem; MVs → refresh time and storage....

Question 5

How would you handle nulls in a SQL join? Provide examples using COALESCE.

Accepted Answer

Nulls in SQL joins behave uniquely: NULL = NULL evaluates to NULL (not TRUE), so join keys with nulls typically don't match. Use COALESCE to normalize join keys. Example: SELECT * FROM orders o JOIN customers c ON COALESCE(o.customer_id, -1) = COALESCE(c.id, -1). For left joins where right-side columns may be null, use COALESCE in SELECT: COALESCE(c.name, 'Unknown Customer'). To include null-matching rows explicitly, use: ON (o.key = c.key OR (o.key IS NULL AND c.key IS NULL))....

Question 6

What are your expectations for the role beyond the salary?

Accepted Answer

**Situation**: Context of the challenge. **Task**: Your responsibility. **Action**: Specific steps, tools, collaboration. **Result**: Quantified outcome. Beyond salary, I expect: (1) Impact—working on problems that matter at scale. (2) Growth—learning from strong peers, mentorship, stretch projects. (3) Ownership—end-to-end responsibility for systems. (4) Work-life balance—sustainable pace, flexibility. (5) Technology—modern stack, investment in tooling....

Question 7

What is the most common performance bottleneck in Spark jobs, and how would you resolve it?

Accepted Answer

**#1 Bottleneck: Shuffle**. Network I/O for join, groupBy. Resolve: (1) Broadcast small tables. (2) Column pruning. (3) Partition by key. (4) Fix skew (salting). (5) Tune shuffle partitions. (6) AQE coalesce.

**Other Bottlenecks**: GC (reduce executor memory, G1), data skew, too few executors, source slow.

**Why Shuffle Dominates**: Moves data across network; serialization; often 70–90% of time.

**Scalability Trade-offs**: Each fix has limits. Combine....

Question 8

Write PySpark code to filter records based on specific conditions and add a calculated column.

Accepted Answer

**Code**:

```python
from pyspark.sql.functions import when, col
spark = SparkSession.builder.getOrCreate()
df = spark.read.parquet("/path")
result = df.filter(col("region") == "US").withColumn("category", when(col("amount") > 100, "high").otherwise("low"))
```

**Why**: Filter before calculated column reduces data. when/otherwise = built-in, optimized.

**Scalability Trade-offs**: Filter first. Built-in over UDF.

**Cost Implications**: Filter early = less data....

Bristol Myers Squibb Data Engineer Interview Questions

Reading isn't practice. Get AI feedback on your answers.

Other Companies

Bristol Myers Squibb Data Engineer Interview Questions

Reading isn't practice. Get AI feedback on your answers.

Other Companies