Data Engineering interviews often move beyond basic SQL and ETL questions once you reach the mid-to-senior level.

Interviewers want to know whether you can reason about real-world data problems:

• How do you identify performance bottlenecks?
• How do you handle skewed data?
• How do Spark partitions work?
• When should you use repartition or coalesce?
• How do you solve complex SQL problems involving consecutive events?
• What happens internally when a Spark job runs?

This article covers some of the most important questions that test these skills.

1. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

All three are SQL window functions used to assign rankings to rows.

Consider:

ROW_NUMBER()

Assigns a unique sequential number to every row.

ROW_NUMBER() OVER (
    ORDER BY salary DESC
)

Possible output:

Even when values are tied, every row gets a unique number.

This is useful when you want to keep exactly one record.

For example, to get the latest record for each customer:

WITH ranked AS (
    SELECT
        customer_id,
        transaction_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY transaction_date DESC
        ) AS rn
    FROM transactions
)
SELECT *
FROM ranked
WHERE rn = 1;

RANK()

Assigns the same rank to tied values but skips subsequent ranks.

100000 → 1
90000  → 2
90000  → 2
80000  → 4

The rank 3 is skipped because two employees share rank 2.

DENSE_RANK()

Also assigns the same rank to tied values, but does not skip ranks.

100000 → 1
90000  → 2
90000  → 2
80000  → 3

Quick Summary

Interview Tip: If the interviewer asks for the “top 3 salaries” and wants to include everyone tied at the third salary, DENSE_RANK() is often more appropriate than ROW_NUMBER().

2. How do you find the top 3 employees by salary in each department?

Use a window function.

WITH ranked_employees AS (
    SELECT
        employee_id,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)
SELECT *
FROM ranked_employees
WHERE salary_rank <= 3;

The important concept here is that:

PARTITION BY department

resets the ranking for every department.

So the ranking happens independently inside each department.

3. How do you find customers with consecutive purchases?

Suppose we have:

customer_id
transaction_date
amount

We want to find customers who made purchases in at least three consecutive months.

For example:

Customer A
January
February
March

This customer qualifies.

But:

Customer B
January
March
April

does not have three consecutive months.

This is a classic gaps-and-islands problem.

A common approach is:

1. Convert transactions into monthly granularity.
2. Remove duplicate purchases within the same month.
3. Order the months for each customer.
4. Identify consecutive month groups.
5. Find groups containing at least three months.

A simplified approach can use date arithmetic and window functions.

The important point is that LAG() compares rows, not necessarily calendar intervals.

For example:

LAG(transaction_date)
OVER (
    PARTITION BY customer_id
    ORDER BY transaction_date
)

returns the previous transaction for that customer.

It does not mean “the transaction exactly one day or one month ago.”

That distinction is extremely important in SQL interviews.

4. What is the difference between LAG(column, 4) and looking four days back?

This is a common interview trap.

Consider:

LAG(timestamp, 4)
OVER (
    PARTITION BY user_id
    ORDER BY timestamp
)

This means:

Return the timestamp from four rows earlier.

It does not mean:

Return the timestamp from four days earlier.

Suppose a user has:

Jan 1
Jan 2
Jan 10
Jan 20
Jan 25

LAG(timestamp, 4) for Jan 25 returns Jan 1 because Jan 1 is four rows behind.

The dates are not four days apart.

If you need to compare calendar dates, you need explicit date arithmetic:

transaction_date - INTERVAL '4 days'

or database-specific date functions.

Interview Tip: Always distinguish between row offsets and time intervals.

5. What is a rolling average?

A rolling average calculates an average over a moving window.

For example:

SELECT
    trip_id,
    timestamp,
    speed,
    AVG(speed) OVER (
        PARTITION BY trip_id
        ORDER BY timestamp
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_avg_speed
FROM trips;

For:

20
30
40
50

The output is:

20
25
30
40

For the fourth row:

AVG(30, 40, 50) = 40

The important detail is:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

This is a row-based window.

It does not necessarily represent the previous two minutes or two days.

If timestamps are irregular, three rows could represent three seconds or three hours.

6. What is data skew in Spark?

Data skew occurs when data is distributed unevenly across Spark partitions.

Imagine a transaction table containing:

Customer 100 → 500 million records
Other customers → 100 records each

If we join this table using customer_id, the records for Customer 100 may end up concentrated in one or a few partitions.

One executor then has significantly more work than others.

You may see:

Executor 1 → 10 seconds
Executor 2 → 12 seconds
Executor 3 → 15 seconds
Executor 4 → 45 minutes

This is a classic sign of skew.

The overall job cannot finish until the slow task finishes.

7. What is salting, and when do you use it?

Salting is a technique used to distribute highly skewed keys across multiple partitions.

Suppose:

customer_id = 100

has 500 million records.

Instead of joining everything using:

customer_id = 100

we introduce an additional salt key.

For example:

customer_id = 100, salt = 0
customer_id = 100, salt = 1
customer_id = 100, salt = 2
...

The large dataset is distributed across multiple salted keys.

If joining with a smaller table, we replicate the smaller table across the salt values and join on:

customer_id
AND salt

This allows the heavily skewed key to be processed across multiple partitions instead of concentrating the workload in one partition.

Interview Tip: Salting is not the same as repartitioning.

8. What is the difference between repartitioning and coalesce?

repartition()

Used to redistribute data across partitions.

df.repartition(10)

It generally causes a shuffle.

You can also partition based on a column:

df.repartition("customer_id")

This redistributes records according to the partitioning key.

Because repartitioning involves shuffle, it can be expensive.

coalesce()

Usually used to reduce the number of partitions without a full shuffle.

df.coalesce(5)

It is useful when you have too many partitions and want to reduce them before writing output.

Simplified comparison

9. How do you choose a partition column?

There is no universal rule such as:

“Always choose the column with the highest number of distinct values.”

The correct choice depends on several factors:

• Query patterns
• Cardinality
• Data distribution
• Frequency of filtering
• File sizes
• Number of partitions
• Risk of small files
• Data skew

Suppose you have:

customer_id → 1,000 distinct values
country     → 100 distinct values
gender      → 2 distinct values

Choosing customer_id may be reasonable for some workloads, but not automatically correct.

If one customer represents 50% of the data, it could actually create skew.

A good partitioning strategy balances:

High enough cardinality for parallelism + even data distribution + common query access patterns.

10. How do you troubleshoot a slow Spark job?

Suppose a 1 TB dataset takes three hours to process.

Don’t immediately restart the cluster.

A structured approach is better.

Step 1: Check Spark UI

Look at:

• Jobs
• Stages
• Tasks
• Task duration
• Shuffle read
• Shuffle write
• Input size
• Spill
• Executor utilization

Find the stage where execution time is concentrated.

Step 2: Check for data skew

Look for:

Most tasks → 10 seconds
One task → 30 minutes

This often indicates skew.

Possible solutions include:

• Salting
• AQE skew join optimization
• Better partitioning
• Broadcast joins

Step 3: Check the query plan

Use:

df.explain("formatted")

Look for:

• Exchange
• Sort
• BroadcastHashJoin
• SortMergeJoin
• Unexpected shuffles

Step 4: Optimize joins

If one table is small enough:

from pyspark.sql.functions import broadcast
result = large_df.join(
    broadcast(small_df),
    "customer_id"
)

This can avoid a large shuffle.

However, broadcasting a table that is too large can cause memory problems.

Step 5: Check partitioning

Too few partitions can limit parallelism.

Too many partitions can create scheduling overhead and small files.

The goal is balanced partition sizes and sufficient parallelism.

11. What is AQE in Spark?

AQE stands for Adaptive Query Execution.

It allows Spark to optimize the physical execution plan using runtime statistics.

AQE can help with problems such as:

• Data skew
• Incorrect initial partition sizing
• Shuffle partition optimization
• Converting certain join strategies at runtime

For example, Spark may initially plan a sort-merge join.

After seeing the actual runtime data, AQE may determine that one side is small enough for a broadcast join.

AQE can also split skewed partitions to reduce the impact of data skew.

The important distinction is:

Salting is a manual technique implemented by the developer. AQE is a Spark runtime optimization mechanism.

12. What is the difference between narrow and wide transformations?

Narrow Transformation

Each output partition depends on a small number of input partitions.

Examples:

filter()
select()
map()

These can generally be pipelined without a shuffle.

Wide Transformation

Data needs to move between partitions.

Examples:

groupBy()
join()
distinct()
orderBy()

These typically involve shuffle.

For example:

df.filter(...)
  .select(...)
  .groupBy(...)
  .count()

Spark can pipeline the filter() and select() operations before the shuffle required by groupBy().

The groupBy() introduces a stage boundary because data must be redistributed.

13. What happens when you run a Spark transformation?

Consider:

df.filter(df.salary > 50000) \
  .groupBy("department") \
  .count()

Spark does not immediately execute each transformation.

Transformations are lazy.

Spark builds a logical execution plan.

When an action such as:

count()

is called, Spark creates the execution plan and executes the required computation.

The execution involves:

Application
    ↓
Driver
    ↓
DAG / Execution Plan
    ↓
Stages
    ↓
Tasks
    ↓
Executors

The key interview point is:

Transformations define what should be done. Actions trigger execution.

14. Does every show() recompute the data?

Potentially, yes.

Suppose:

df = spark.read.table("transactions")
filtered = df.filter(...)
filtered.show()
filtered.show()

Without caching, Spark may execute the required computation again for each action.

If the same DataFrame is reused multiple times:

filtered.cache()

or:

filtered.persist()

may help.

However, caching is not automatically beneficial.

You should consider:

• Dataset size
• Reuse frequency
• Available executor memory
• Storage level
• Cost of recomputation

Caching a huge DataFrame that is used only once can waste resources.

15. Final Interview Takeaway

A strong Data Engineer should be able to connect concepts rather than memorize definitions.

For example:

Slow Spark Job
      ↓
Spark UI
      ↓
Identify Slow Stage
      ↓
Check Shuffle
      ↓
Check Data Skew
      ↓
Check Join Strategy
      ↓
Check Partitioning
      ↓
Apply Broadcast / Salting / AQE
      ↓
Measure Improvement

Similarly, for SQL:

Business Requirement
      ↓
Identify Grain
      ↓
Deduplicate
      ↓
Window Functions
      ↓
Date Logic
      ↓
Gaps & Islands
      ↓
Aggregate and Filter

The strongest candidates don’t just know that LAG(), RANK(), or repartition() exist. They understand when to use them, why to use them, and what happens internally.

That is often the difference between someone who has learned Data Engineering concepts and someone who has actually worked with Data Engineering systems in production.

Follow me on medium

Read all Data Engineering Tutorials here