Question 1

Explain the differences between Repartition and Coalesce. When would you use each?

Accepted Answer

**Repartition(n)**: Full shuffle; creates exactly n partitions. Can increase or decrease. **Coalesce(n)**: Merges partitions without full shuffle; only decreases. **Why it matters**: Shuffle is expensive—network and disk I/O. Coalesce avoids shuffle when reducing partitions by merging within existing partitions. **When Repartition**: Increasing partitions, fixing skew (repartition by key), or before a join to align partition counts....

Question 2

Explain the types of triggers in ADF, including schedule, tumbling window, and event-based triggers.

Accepted Answer

Schedule: Fixed cadence (cron, every N mins). Predictable batch windows; simple ops. Tumbling window: Fixed non-overlapping intervals; fires once per window. Ideal for idempotent, exactly-once semantics—no overlap means no double-processing. Event-based: Fires on blob created, queue message, etc. Enables near real-time pipelines....

Question 3

Joins and window functions - INNER, LEFT, RIGHT, FULL OUTER, ROW_NUMBER(), RANK(), DENSE_RANK()

Accepted Answer

Joins: INNER = intersection only; LEFT = all left + matching right (NULL fill); RIGHT = mirror of LEFT; FULL OUTER = union of both. Why it matters: Join choice affects result cardinality and NULL handling—wrong join = wrong business logic (e.g., LEFT to preserve all customers even without orders). Window functions: ROW_NUMBER() = unique rank 1,2,3; RANK() = ties same rank, gaps after; DENSE_RANK() = ties same rank, no gaps....

Question 4

Optimization techniques - partitioning, caching, broadcast joins, bucketing

Accepted Answer

Partitioning: by key (date, region) for pruning. Caching: cache repeatedly used DataFrames. Broadcast joins: for small dimension tables, broadcast to avoid shuffle. Bucketing: co-locate data by key for join efficiency. Example: df.repartition('date').cache(); broadcast_df = broadcast(small_df); df.join(broadcast_df, 'id'). **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead....

Question 5

Removing duplicates - ROW_NUMBER() or DISTINCT

Accepted Answer

ROW_NUMBER(): use when you need to pick one row per group (e.g., keep latest). More control than DISTINCT. DISTINCT: removes duplicates across all columns; use when entire row is duplicate. ROW_NUMBER with PARTITION BY key, ORDER BY ts DESC, filter rn=1 gives deterministic dedup. DISTINCT is simpler but doesn't let you choose which duplicate to keep. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead....

Question 6

Serverless vs. Dedicated SQL pools

Accepted Answer

Dedicated SQL pool (Synapse): provisioned compute, fixed cost, predictable perf, your own resources. Serverless: pay per query, auto-scale, no management. Use dedicated for heavy, consistent workloads; serverless for ad-hoc, variable load. Serverless has cold start; dedicated always ready. Cost: dedicated = always-on; serverless = usage-based. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead....

Question 7

Write a query for second-highest salary using LIMIT, OFFSET, or ROW_NUMBER()

Accepted Answer

ROW_NUMBER: SELECT salary FROM (SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) rn FROM employees) WHERE rn = 2. LIMIT/OFFSET: SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1. **Why**: ROW_NUMBER more portable; LIMIT/OFFSET simpler. Handle single-row (no second) with NULL or empty....

Nihilent SQL Interview Questions

Difficulty Breakdown

Key Topics Covered

How to Use This Guide

Companies asking these questions

All 7 Questions

More Interview Prep Guides

Unlock All Expert Answers