DEV Community: K HUNNURJI RAO

Spark Is Lazy, and That One Fact Explains Most Beginner Confusion

K HUNNURJI RAO — Sun, 01 Nov 2026 16:39:23 +0000

You write five lines of PySpark. You hit run. It finishes in a blink.

You think, "wow, that was fast."

Then you add .show() at the end, and suddenly everything takes ten seconds. Or worse, an error appears on a line that looks completely fine.

If this has happened to you, the reason is one idea: Spark is lazy. It doesn't do work when you write a line. It does work when you ask for a result.

Once this clicks, a lot of "weird" Spark behaviour stops being weird. Let's see it with real output.

I ran everything below with PySpark 4.3.0 in local mode (Java 17, Python 3.14). Nothing fancy, just pip install pyspark.

The Zomato cart

Think of ordering food on an app.

You add a biryani to the cart. You add a raita. You remove the raita. Nothing is cooked yet. You're only building a list of what you want.

Then you press "Place order". Now the kitchen actually starts.

Spark works the same way:

Transformations are the cart. filter, select, withColumn, join, groupBy. They describe what you want and return immediately.
Actions are "Place order". show, count, collect, take, write. These make Spark actually run.

The data

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.master("local[2]").appName("lazy").getOrCreate()

orders = spark.createDataFrame(
    [(1, "paid", 500), (2, "paid", 1200), (3, "refunded", 300),
     (4, "paid", 800), (5, "pending", 150)],
    ["order_id", "status", "amount"],
)

Five orders. Nothing special.

Experiment 1: transformations do no work

paid = orders.filter(F.col("status") == "paid")
big = paid.withColumn("amount_with_gst", F.col("amount") * 1.18)

Two lines. No output, no table, no data processed. big is not a result. It's a plan.

You can even ask Spark to show the plan, still without running anything:

big.explain()

== Physical Plan ==
*(1) Project [order_id#0L, status#1, amount#2L, (cast(amount#2L as double) * 1.18) AS amount_with_gst#3]
+- *(1) Filter (isnotnull(status#1) AND (status#1 = paid))
   +- *(1) Scan ExistingRDD[order_id#0L,status#1,amount#2L]

Read it from the bottom up. Scan the data, filter to paid, then add the GST column. That's your cart.

Now the action:

big.show()

+--------+------+------+---------------+
|order_id|status|amount|amount_with_gst|
+--------+------+------+---------------+
|       1|  paid|   500|          590.0|
|       2|  paid|  1200|         1416.0|
|       4|  paid|   800|          944.0|
+--------+------+------+---------------+

Only now did Spark actually touch the data.

Why would anyone design it this way?

Because Spark gets to see your whole plan before running any of it.

Look at the plan above. Your two separate lines (filter and withColumn) were combined into a single stage, marked *(1). Spark didn't run your filter, wait, then run your column step, wait again. It planned the whole thing as one pass.

If it ran each line the moment you wrote it, it couldn't do that. On a few rows you won't notice. On billions of rows, the difference between one pass and three is a big deal.

Experiment 2: every action starts over

Here's the part that surprises people. Each action recomputes the plan from the start.

To prove it, I'll count how many times Spark processes a row. I use an accumulator, which is a counter that Spark workers can add to, and a function that bumps it every time it runs.

from pyspark.sql.types import IntegerType

acc = spark.sparkContext.accumulator(0)

def double_it(x):
    acc.add(1)
    return x * 2

d = orders.withColumn("double", F.udf(double_it, IntegerType())("amount"))

print("before:", acc.value)
d.agg(F.sum("double")).collect()
print("after action 1:", acc.value)
d.agg(F.sum("double")).collect()
print("after action 2:", acc.value)

before: 0
after action 1: 5
after action 2: 10

Look at the three numbers.

0 after defining d. Building the DataFrame ran nothing.
5 after the first action. Five rows, five calls.
10 after the second action. Spark did the same work again.

Spark didn't remember the result. It has no idea you plan to use it twice. It only has the recipe.

Now with cache():

acc2 = spark.sparkContext.accumulator(0)

def double_it2(x):
    acc2.add(1)
    return x * 2

d2 = orders.withColumn("double", F.udf(double_it2, IntegerType())("amount")).cache()

d2.agg(F.sum("double")).collect()
print("cached, after action 1:", acc2.value)
d2.agg(F.sum("double")).collect()
print("cached, after action 2:", acc2.value)

cached, after action 1: 5
cached, after action 2: 5

The second action reused the stored result. Still 5.

A small honesty note: accumulators inside transformations are fine for a demo like this, but don't build real logic on them. If Spark retries a task, the count can go up.

So when should you cache? When one DataFrame feeds more than one action and the work to build it is expensive. If you use it once, cache just wastes memory.

Experiment 3: errors show up late

This is the one that wastes the most debugging time. I'll add a function that fails on one value:

def bad(x):
    return int("abc") if x == 300 else x

broken = orders.withColumn("fixed", F.udf(bad, IntegerType())("amount"))
print("built broken df ok, no error yet")

built broken df ok, no error yet

No error. The bug is in the plan, but nothing has run. Now:

broken.show()

PythonException: ... ValueError: invalid literal for int() with base 10: 'abc'

The failure appears at .show(). The bug was a line you wrote earlier. In a long notebook, the error line and the cause can be many cells apart.

Not all mistakes wait, though. Spark checks column names when you build the plan:

orders.select("nope")

AnalysisException: [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column, variable, or function
parameter with name `nope` cannot be resolved. Did you mean one of the following?
[`amount`, `order_id`, `status`].

That one fails immediately, even though there is no action. Spark can check names and types without reading the data. What it can't know until it runs is what's inside your rows or what your Python functions do with them.

Rule of thumb: a typo fails early. A bad value fails late.

Common mistakes

1. Sprinkling count() to debug. Each count() is an action. Each one recomputes the whole plan. Ten of them can mean ten full runs. If you're debugging, .limit(20).show() or one cache() first is kinder to the cluster.

2. Timing the wrong line. "This join is slow." No. The join returned instantly. The write at the end paid for everything. Time actions, not transformations.

3. collect() on big data. It's an action that pulls everything to one machine. On five rows, fine. On millions, you'll run out of memory.

4. Caching everything. Cache only what you reuse. Call unpersist() when you're done.

The interview version

If someone asks "What is lazy evaluation in Spark?", you can say:

"Transformations like filter and select don't run immediately. Spark just records them as a plan. Work happens when I call an action like show, count or write. That lets Spark optimise the whole plan together, but it also means each action recomputes from the start unless I cache."

That's a solid answer, and now you can back it up with an example.

Quick recap

Transformations build a plan. Actions run it.
explain() shows the plan without running anything.
Every action recomputes unless you cache().
Name typos fail early. Bad data fails late.

Try it yourself

If you'd like to practise DataFrame operations after this, I've put some PySpark problems on LakeBench, a platform I'm building: lakebench.in/pyspark-practice. The free ones are the beginner set.

Quick question for you. What's the longest you've waited on a Spark job, only to find out the bug was three cells up?

If You Know SQL, You Already Know Most of pandas

K HUNNURJI RAO — Sun, 01 Nov 2026 15:55:44 +0000

You open a pandas tutorial and the first thing you see is df.groupby(...).agg(...), then something called .loc, then a reset_index() that nobody explained.

And you think, "I already know how to do this. I just know it in SQL."

Good news: you do. Most of pandas is SQL wearing different clothes. Once you see the mapping, you stop memorising pandas and start translating.

So let's do exactly that. Same small data, SQL on the left, pandas on the right, and the real output. At the end, we'll look at the four places where they behave differently, because that's where beginners lose marks.

I ran everything below with Python 3.14, pandas 3.0.2 and SQLite 3.53. The SQL results come from SQLite and the pandas results come from pandas, so when I say they match, I actually compared them.

The data

Two tables: customers and orders.

import pandas as pd
import numpy as np

customers = pd.DataFrame({
    "id": [1, 2, 3, 4],
    "name": ["Asha", "Ravi", "Meera", "Kiran"],
    "city": ["Pune", "Delhi", "Pune", "Mumbai"],
})

orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104, 105, 106],
    "customer_id": [1, 1, 2, 3, 3, 5],
    "amount": [500, 1200, 300, 800, None, 150],
    "status": ["paid", "paid", "refunded", "paid", "pending", "paid"],
})

 order_id  customer_id  amount   status
      101            1   500.0     paid
      102            1  1200.0     paid
      103            2   300.0 refunded
      104            3   800.0     paid
      105            3     NaN  pending
      106            5   150.0     paid

Two things are planted on purpose. Order 105 has a missing amount. Order 106 belongs to customer 5, who doesn't exist in customers. Real data is always like this.

(If you want to run the SQL side too, orders.to_sql("orders", conn) with a sqlite3 connection loads these tables in one line.)

1. SELECT columns

SELECT order_id, amount FROM orders;

orders[["order_id", "amount"]]

Two square brackets. The outer one is "get from the DataFrame", the inner one is a list of column names. If you write one bracket with one name, you get a Series (a single column) instead of a table.

2. WHERE

SELECT * FROM orders WHERE status = 'paid' AND amount >= 500;

orders[(orders["status"] == "paid") & (orders["amount"] >= 500)]

Output, same for both:

 order_id  customer_id  amount status
      101            1   500.0   paid
      102            1  1200.0   paid
      104            3   800.0   paid

Three changes to remember:

= becomes ==
AND / OR become & / |
every condition goes inside its own parentheses

That last one is not optional. Try it without the brackets:

orders[orders["status"] == "paid" & orders["amount"] >= 500]

TypeError: Cannot perform 'rand_' with a dtyped [float64] array and scalar of type [bool]

Python evaluates & before ==, so it tries to AND a string with a column. The error message makes no sense until you know why. Now you know.

3. ORDER BY and LIMIT

SELECT * FROM orders ORDER BY amount DESC LIMIT 3;

orders.sort_values("amount", ascending=False).head(3)

 order_id  customer_id  amount status
      102            1  1200.0   paid
      104            3   800.0   paid
      101            1   500.0   paid

4. GROUP BY

SELECT status,
       COUNT(*)      AS n,
       COUNT(amount) AS n_amount,
       SUM(amount)   AS total,
       AVG(amount)   AS avg_amt
FROM orders
GROUP BY status;

orders.groupby("status").agg(
    n=("order_id", "size"),
    n_amount=("amount", "count"),
    total=("amount", "sum"),
    avg_amt=("amount", "mean"),
).reset_index()

Output from pandas:

  status  n  n_amount  total  avg_amt
    paid  4         4 2650.0    662.5
 pending  1         0    0.0      NaN
refunded  1         1  300.0    300.0

The pattern name=("column", "function") is called named aggregation, and it's the closest thing pandas has to SUM(amount) AS total. Learn this one form and you can skip a lot of confusing older syntax.

.reset_index() turns the group keys back into a normal column. Without it, status becomes the row label. Think of it as "give me a plain table again".

Notice the pending row: total is 0.0 in pandas. Hold that thought, because SQL says something different. We'll get to it.

5. HAVING

pandas has no HAVING. You group first, then filter the grouped result, which is what HAVING was doing all along.

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

t = orders.groupby("customer_id", as_index=False).agg(total=("amount", "sum"))
t[t["total"] > 1000]

 customer_id  total
           1 1700.0

as_index=False does the same job as reset_index() here.

6. JOIN

SELECT c.name, o.order_id, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.id;

orders.merge(customers, left_on="customer_id", right_on="id", how="inner")[
    ["name", "order_id", "amount"]
]

 name  order_id  amount
 Asha       101   500.0
 Asha       102  1200.0
 Ravi       103   300.0
Meera       104   800.0
Meera       105     NaN

Order 106 is gone, because customer 5 doesn't exist. That's an inner join: only rows that match on both sides. Change how="inner" to how="left" and the order comes back:

SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

orders.merge(customers, left_on="customer_id", right_on="id", how="left")[
    ["order_id", "name"]
]

 order_id  name
      101  Asha
      102  Asha
      103  Ravi
      104 Meera
      105 Meera
      106   NaN

how takes "inner", "left", "right" and "outer". That's INNER, LEFT, RIGHT and FULL OUTER.

"Customers who never ordered"

A favourite interview question. In SQL it's a LEFT JOIN and WHERE ... IS NULL:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_id IS NULL;

Same idea in pandas:

m = customers.merge(orders, left_on="id", right_on="customer_id", how="left")
m[m["order_id"].isna()][["name"]]

 name
Kiran

IS NULL becomes .isna().

7. CASE WHEN

SELECT order_id, amount,
       CASE WHEN amount >= 1000 THEN 'big'
            WHEN amount >= 300  THEN 'mid'
            ELSE 'small' END AS size
FROM orders;

o = orders.copy()
o["size"] = np.select(
    [o["amount"] >= 1000, o["amount"] >= 300],
    ["big", "mid"],
    default="small",
)
o[["order_id", "amount", "size"]]

 order_id  amount  size
      101   500.0   mid
      102  1200.0   big
      103   300.0   mid
      104   800.0   mid
      105     NaN small
      106   150.0 small

np.select takes a list of conditions, a list of results, and a default. Just like WHEN ... THEN ... ELSE. Order matters in both: the first match wins.

Notice order 105 (missing amount) landed in small. Comparing NaN to anything is false, so it fell through to ELSE. SQL does the same, since a comparison with NULL is never true. Same result, same reason.

8. COALESCE

SELECT order_id, COALESCE(amount, 0) AS amount FROM orders;

orders.assign(amount=orders["amount"].fillna(0))

fillna(0) is COALESCE(x, 0). Row 105 now shows 0.0.

9. Window functions

SELECT order_id,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders;

o = orders.copy()
o["rn"] = (
    o.sort_values("amount", ascending=False)
     .groupby("customer_id")
     .cumcount() + 1
)
o[["order_id", "rn"]]

 order_id  rn
      101   2
      102   1
      103   1
      104   1
      105   2
      106   1

Sort first (that's the ORDER BY), group (that's the PARTITION BY), then cumcount() numbers the rows inside each group starting from 0. We add 1 to match ROW_NUMBER. This one is the least obvious translation, so it's worth saving.

One small thing to know: SQLite puts NULLs last in a descending sort, and pandas also puts missing values last by default, so both agree that order 104 gets rn = 1 for customer 3. Other databases can place NULLs differently, so check before you trust a window function across engines.

Where pandas and SQL disagree

This is the part that costs people marks. All of these I ran on the data above.

1. Summing nothing

Order 105 is the only pending order, and its amount is missing.

SELECT SUM(amount) FROM orders WHERE status = 'pending';

SQL returns NULL. pandas:

orders[orders["status"] == "pending"]["amount"].sum()

returns 0.0. SQL says "I don't know", pandas says "zero". If you export that to a report, the two numbers mean different things. If you want SQL's behaviour, pass min_count=1: .sum(min_count=1) returns NaN when there is nothing to add.

2. Not-equal and missing values

SELECT COUNT(*) FROM orders WHERE amount != 500;

len(orders[orders["amount"] != 500])

SQL gives 4 because NULL != 500 is neither true nor false, so the row is dropped. pandas gives 5 because NaN != 500 is True. Same question, different counts. If missing values matter, filter them out explicitly with .notna().

3. COUNT vs size

orders.groupby("status")["amount"].count()   # paid 4, pending 0, refunded 1
orders.groupby("status").size()              # paid 4, pending 1, refunded 1

.count() skips missing values, like COUNT(amount). .size() counts rows, like COUNT(*). Beginners reach for .count() and quietly lose rows.

4. Checking for missing values

(orders["amount"] == None).sum()   # 0
orders["amount"].isna().sum()      # 1

== None finds nothing. It's the same mistake as writing = NULL in SQL, just in a different place. Use .isna(), your IS NULL.

Cheat sheet

SQL	pandas
`SELECT a, b`	`df[["a", "b"]]`
`WHERE x = 1 AND y > 2`	`df[(df["x"] == 1) & (df["y"] > 2)]`
`ORDER BY a DESC LIMIT 3`	`df.sort_values("a", ascending=False).head(3)`
`GROUP BY ... SUM(x) AS t`	`df.groupby("g").agg(t=("x", "sum"))`
`HAVING`	filter the grouped result
`JOIN` / `LEFT JOIN`	`df.merge(other, on=..., how="inner" / "left")`
`IS NULL`	`.isna()`
`COALESCE(x, 0)`	`.fillna(0)`
`CASE WHEN`	`np.select(conditions, results, default=...)`
`COUNT(*)` / `COUNT(x)`	`.size()` / `.count()`
`COUNT(DISTINCT x)`	`.nunique()`
`ROW_NUMBER() OVER (PARTITION BY g ORDER BY x)`	`sort_values("x").groupby("g").cumcount() + 1`

Try it yourself

If you'd like to practise this translation, I've put some pandas problems on LakeBench, a platform I'm building. Two free ones that match this article: GroupBy with Multiple Aggregations and Merge DataFrames with Different Join Types.

A good exercise is to take any SQL query you've already written and translate it line by line, then compare the output. Which one felt most awkward in pandas, the window function or the join?

Second Highest Salary in SQL: 4 Ways, and Which Ones Quietly Break

K HUNNURJI RAO — Sun, 01 Nov 2026 15:50:59 +0000

"Write a query to find the second highest salary."

If you have prepared for any SQL round, you have seen this one. And you probably memorised an answer.

Here's the thing. Most memorised answers work on the sample table in the tutorial and break on the interviewer's follow-up. Two things trip people up: ties (two people with the same top salary) and no second row (a department with one employee).

So let's do it properly. Same small table, four solutions, and we'll see which ones survive.

The data

I ran everything below in SQLite 3.53. The syntax is standard enough that it works in PostgreSQL and MySQL 8 too, with a couple of small notes where it differs.

CREATE TABLE employees(id INTEGER, name TEXT, dept TEXT, salary INTEGER);

INSERT INTO employees VALUES
(1,'Asha','Eng',90000),
(2,'Ravi','Eng',90000),
(3,'Meera','Eng',75000),
(4,'Kiran','Sales',60000),
(5,'Neha','Sales',55000),
(6,'Tara','HR',50000);

Notice what I did on purpose. Asha and Ravi both earn 90000, so the top salary is tied. Tara is alone in HR.

The correct answer for the whole company is 75000. Keep that in mind.

Way 1: ORDER BY with LIMIT and OFFSET

This is the one most people write first.

SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Output:

Wrong. It sorted the rows 90000, 90000, 75000... and skipped one row, which was the first 90000. The second row is the other 90000.

The fix is DISTINCT, so duplicates collapse before you skip:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Output:

Correct now. In SQL Server the same idea is OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY.

Way 2: Max of everything below the max

No sorting, no window functions. Just ask: what is the biggest salary that is smaller than the biggest salary?

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Output:

This handles ties without any extra work, because salary < max throws away every row tied at the top. It's also easy to explain out loud, which matters in an interview.

The catch is that it only gives you the second highest. If the interviewer then says "okay, now the 5th highest", you have to nest it again and again. Not nice.

Way 3: DENSE_RANK

This is the version that scales to any N.

SELECT DISTINCT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

Output:

Want the 5th highest? Change rnk = 2 to rnk = 5. That's it.

Why DENSE_RANK and not RANK or ROW_NUMBER? Let's look at all three on our data.

name	salary	ROW_NUMBER	RANK	DENSE_RANK
Asha	90000	1	1	1
Ravi	90000	2	1	1
Meera	75000	3	3	2
Kiran	60000	4	4	3
Neha	55000	5	5	4
Tara	50000	6	6	5

I ran ROW_NUMBER() and RANK() on this table to get those numbers. (ROW_NUMBER's order between Asha and Ravi can swap, since they tie.)

ROW_NUMBER never ties, so "rank 2" is just the other 90000. Wrong.
RANK ties, but then skips. After two people at rank 1, the next rank is 3. There is no rank 2 at all, so you get an empty result.
DENSE_RANK ties and doesn't skip. Meera is rank 2. Correct.

Rule of thumb: for "Nth highest value", use DENSE_RANK.

Dropping the DISTINCT is also useful: if you want the people earning the second highest salary, just select name too:

SELECT name, salary
FROM (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

Output:

Meera|75000

Way 4: The correlated subquery

You'll see this in older tutorials and some interviews, because it works without window functions.

SELECT name, salary
FROM employees e
WHERE 2 = (
  SELECT COUNT(DISTINCT salary)
  FROM employees
  WHERE salary >= e.salary
);

Output:

Meera|75000

Read it like this: for each employee, count how many distinct salaries are at least as high as theirs. If that count is 2, they are the second highest.

It's correct, and it's also slow on big tables, because the inner query runs once per row. Good to understand, not what I'd write today if window functions are available.

The trap everyone misses: what if there is no second salary?

Our HR department has only Tara. Ask for the second highest salary in HR and see what each method returns.

Way 1 with LIMIT/OFFSET:

SELECT salary FROM (
  SELECT salary FROM employees
  WHERE dept = 'HR'
  ORDER BY salary DESC LIMIT 1 OFFSET 1
) t;

Output: no rows at all.

Way 2 with MAX:

SELECT MAX(salary)
FROM employees
WHERE dept = 'HR'
  AND salary < (SELECT MAX(salary) FROM employees WHERE dept = 'HR');

Output: one row containing NULL.

These look similar but behave differently. An empty result and a NULL are not the same thing to the application reading your query. Some interviewers specifically ask for "return NULL if there is no second highest salary". If so, wrap the Way 1 query in a subquery in the SELECT list, which turns "no rows" into NULL:

SELECT (
  SELECT DISTINCT salary
  FROM employees
  WHERE dept = 'HR'
  ORDER BY salary DESC
  LIMIT 1 OFFSET 1
) AS second_highest;

Output:

NULL

Good to know before you walk into the room, not after.

Second highest per department

The follow-up that comes right after is almost always "now do it for each department". This is where DENSE_RANK shines, because you only add PARTITION BY:

SELECT dept, name, salary
FROM (
  SELECT name, dept, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

Output:

Eng|Meera|75000
Sales|Neha|55000

HR doesn't appear because it has no rank 2, which is the right behaviour here. Asha and Ravi both being rank 1 in Eng doesn't push Meera out of rank 2.

What I'd say in the interview

"I'd use DENSE_RANK so ties don't break it, and it works for any N."
"If window functions aren't allowed, I'd use MAX with a subquery on salary < MAX(salary)."
"And I'd ask whether they want NULL or an empty result when there is no second salary."

That third line is what sounds like experience. Asking about the edge case before the interviewer does is a good habit in any SQL question.

Try it yourself

If you want to practise a few more like this, I've put together SQL problems on LakeBench, a platform I'm building. "Top three salaries" is free and is a good warm-up for the ranking idea: lakebench.in/studio/lb-sql-05. The exact "Second highest salary" problem is lb-sql-20. There's also a page of window function problems at akebench.in/learn/sql-warehousing/window-functions.

Which of the four would you have written first? I'd bet on Way 1.