The everyday API: select/selectExpr and filter for rows and columns, withColumn/lit/cast/when to shape them, and sort/distinct/limit — plus the null and dedupe traps, chained into a readable, performant pipeline.
⏱ 24 min readTopics chapter readerLevel · Structured APIs
01 · Orientation
What You'll Master Here
pick columns (select/selectExpr), keep rows (filter), compute new columns (withColumn/lit/cast/when), then maybe order/trim. Do the cheap, shrinking steps first, and treat null as a value that needs a deliberate decision at every step.
⏱ 4 min · Topic 1 of 8
This is the bread and butter of Spark: the handful of DataFrame operations you will use in literally every job. select, selectExpr, and filter to choose columns and rows; withColumn, lit, cast, when/otherwise, withColumnRenamed, and drop to shape columns; and sort, distinct, and limit to order and trim.
None of this is hard, but doing it cleanly, knowing which operations are cheap (narrow) versus expensive (wide), and knowing where nulls quietly change your answer, is what separates readable, fast, correct code from slow, tangled, silently-wrong code. Recall from Chapter 5 that wide transformations trigger a shuffle while narrow ones do not — that distinction is exactly what tells you which of these everyday operations to reach for freely and which to use sparingly. We will also cover chaining operations into a clear pipeline.
Every operation comes with input and output so you can see exactly what it does, including the traps: a null that disappears from a filter, a cast that returns null instead of erroring, a dropDuplicates that keeps a different row each run.
Core mental model
Most jobs are: pick columns (select/selectExpr), keep rows (filter), compute new columns (withColumn/lit/cast/when), then maybe order/trim. Do the cheap, shrinking steps first, and treat null as a value that needs a deliberate decision at every step.
Why it matters
These operations make up the large majority of day-to-day Spark transformations. Writing them clearly, ordering them well (filter early, sort late), and knowing their edge cases is what makes your jobs readable, efficient, and trustworthy.
select / selectExpr
Choose / compute the columns to keep, with typed Columns or SQL-expression strings.
filter / where
Keep only the rows matching a condition (identical methods).
withColumn
Add or replace one column from an expression.
orderBy / sort
Sort rows; a wide transformation (it shuffles).
Common mistake
Filtering and selecting late, after heavy steps. You carry more data than needed into joins/shuffles; filter and select early to shrink data first.
Better habit
Filter and select as early as possible to reduce data.
Use withColumn for derived fields; select to project.
Remember orderBy and distinct are wide (shuffles), use them only when needed.
The big idea
A few composable operations cover most transformations. The skill is ordering them well, shrink early with filter/select, avoid needless wide ops like sort and distinct, and handle nulls and casts deliberately rather than by accident.
Remember this
select/selectExpr/filter/withColumn/cast/when/rename/drop/sort/distinct/limit cover most transformations. Apply the cheap, data-shrinking ones early; reserve wide ones (sort, distinct) for when you truly need them; and watch nulls at every step.
Practice2 prompts
Which of these are narrow and which are wide: select, filter, orderBy, withColumn, distinct?
Why should filter usually come before a join?
02 · Rows & columns
select, selectExpr & filter
select trims the table sideways (columns); filter trims it downward (rows). selectExpr is the same trim, just spelled in SQL instead of Column objects. Trim early, work on less.
⏱ 7 min · Topic 2 of 8
select chooses (or computes) which columns to keep, a projection. filter (or its identical twin where) keeps only the rows matching a condition. Together they answer "which columns and which rows do I want?", and both are narrow, so they are cheap and pipeline together.
select takes typed Column expressions: col("amount") * 0.9, useful when columns are built programmatically or composed from variables. selectExpr takes plain SQL-expression strings instead: selectExpr("amount * 0.9 as net", "upper(country) as cc"). Under the hood selectExpr just parses each string into the same Column expression and calls select, it is sugar for select(expr(...)), so it produces the identical Catalyst plan. Use whichever reads better for the line in front of you.
filter conditions use column comparisons (col("amount") > 0), combined with & (and), | (or), and ~ (not), each condition wrapped in parentheses. Because select/selectExpr and filter are cheap and shrink data, they should usually come first in a job, less data flows into the expensive steps (joins, aggregations) downstream.
Core mental model
select trims the table sideways (columns); filter trims it downward (rows). selectExpr is the same trim, just spelled in SQL instead of Column objects. Trim early, work on less.
Why it matters
Projecting only needed columns enables Parquet column pruning (Chapter 7); filtering early shrinks every downstream shuffle. These two habits alone speed up most jobs, and choosing select vs selectExpr deliberately keeps code both fast and readable.
projection (select)
Choosing/computing the output columns.
selectExpr
select where each argument is a SQL-expression string; parses to the same plan as select.
filter / where
Row predicate; the two names are interchangeable.
& | ~
and / or / not for combining conditions (parenthesise each condition).
select — trims sideways (columns)
countryamount(drops status)
filter — trims downward (rows)
amount > 0country != "FR"
select and filter trim the table from two different directions, and both are narrow (no shuffle).
select drops the status column; filter keeps amount > 0 AND country ≠ FR. The DE 0-row and UK −5 row fail amount > 0; the FR row fails the country condition.
select projects to country and amount; filter keeps rows where amount > 0 AND country is not FR. Note the parentheses around each condition, required when combining with &. Both operations are narrow, so they cost almost nothing and pipeline together.
selectExpr parses each string into the same expression select would build, then calls select. The Catalyst plan is identical either way; selectExpr is just a terser, SQL-flavoured front end.
selectExpr("amount * 0.9 as net", "upper(country) as cc") is terse SQL-string syntax for exactly what select((col("amount")*0.9).alias("net"), upper(col("country")).alias("cc")) builds. Same plan, different spelling.
select vs selectExpr
Aspect
select
selectExpr
Input
Typed Column expressions
SQL-expression strings
Best for
Programmatic / composable columns built from variables
Terse, SQL-shaped one-liners
Plan produced
Same Catalyst plan
Same Catalyst plan (parses to the same expression, then calls select)
Filtering on a column that can be null without thinking about it. A null-valued row silently disappears instead of erroring; see the null-in-filter edge case below before shipping a filter on a nullable column.
Better habit
Select only the columns you need (helps Parquet pruning).
Filter as early as possible.
Parenthesise each condition when combining with & / |.
Interview note
"How do you make a join cheaper?" One strong answer: "select only needed columns and filter rows before the join, so far less data is shuffled."
Remember this
select picks columns (typed Columns), selectExpr does the same from SQL strings (same plan), filter/where picks rows; all are narrow and cheap. Apply them early to shrink data, and parenthesise combined conditions.
Practice2 prompts
Write a filter keeping rows where status = "ok" OR amount > 1000.
Rewrite orders.select(col("amount").cast("double").alias("amount_d")) as a selectExpr one-liner.
03 · Silent data loss
Nulls Inside filter
TRUE, FALSE, and "I don't know" (null). Only TRUE survives. A condition and its negation can both be "I don't know" on the same row, so negating a filter is not a safe way to get the complementary rows.
⏱ 4 min · Topic 3 of 8
Spark uses three-valued logic: a condition can be TRUE, FALSE, or NULL (unknown), and filter keeps a row only when its condition is TRUE. A row whose condition evaluates to NULL is not TRUE, so it is dropped, the same as a FALSE row, even though you never told Spark to exclude it.
This bites hardest with != and ==. filter(col("country") != "FR") looks like "keep everyone except France", but a row where country is null evaluates that comparison to NULL, not TRUE, so it silently vanishes too. Worse, negating the condition does not rescue it: filter(~(col("country") != "FR")) also evaluates to NULL on that row (NOT NULL is still NULL), so the null row is dropped by both filter(cond) and filter(~cond). It falls through every branch.
Spark's deeper null-equality semantics, why == on two nulls is null rather than true, and the eqNullSafe (<=>) operator that fixes it, belong to Chapter 9's nulls section; this section only covers the filter-specific trap. The fix here is to handle the null explicitly: filter((col("country") != "FR") | col("country").isNull()) if nulls should stay, or use isNull()/isNotNull() to test for it directly.
Core mental model
Think of filter conditions as having three outcomes, not two: TRUE, FALSE, and "I don't know" (null). Only TRUE survives. A condition and its negation can both be "I don't know" on the same row, so negating a filter is not a safe way to get the complementary rows.
Why it matters
A filter that silently drops null rows shrinks your dataset without an error, a warning, or a row-count check catching it unless you specifically look. This is one of the most common sources of "the numbers don't add up" bugs in production Spark jobs.
three-valued logic
A condition evaluates to TRUE, FALSE, or NULL (unknown); only TRUE rows survive filter.
isNull / isNotNull
Explicit null tests; the safe way to include or exclude nulls in a filter.
NOT NULL = NULL
Negating an unknown is still unknown, so a null row fails both a filter and its negation.
A null country disappears from both sides of the filterworked example
kept (country != "FR") returns only id=1. negated (NOT(country != "FR")) returns only id=2. Row id=3 (null country) appears in NEITHER result: NULL != "FR" is NULL, and NOT NULL is still NULL, so it never evaluates to TRUE on either side.
The null-country row is dropped by the filter AND by its logical opposite. To keep it on the "kept" side, you must ask for it explicitly: (col("country") != "FR") | col("country").isNull().
Common mistake
Assuming filter(~cond) returns exactly the rows filter(cond) excluded. Rows where cond is NULL are missing from both results; verify counts add up (kept + negated should equal the original row count if that is the invariant you need).
Better habit
Before shipping a filter on a nullable column, decide explicitly what should happen to nulls.
Use isNull()/isNotNull() rather than relying on != or == to surface nulls correctly.
Sanity-check row counts (filtered + excluded == original) when nulls are possible.
A filter and its negation can both drop the same row
filter(cond) and filter(~cond) are not complementary when cond can be NULL. A null-valued row fails both, because NOT NULL is still NULL, not TRUE. For deeper null-equality rules (== vs eqNullSafe), see Chapter 9's nulls section.
Remember this
filter keeps only TRUE rows; a NULL condition is dropped, and so is its negation. Handle nulls explicitly with isNull()/isNotNull() rather than trusting != or a negated filter to catch them.
Practice2 prompts
Given the orders table above, write a filter that keeps the null-country row alongside the non-FR rows.
Why does filtered_count + negated_count not always equal the original row count when nulls are present?
04 · Shaping
withColumn, lit, cast & when
withColumn = add/replace a column; lit = "this Python value, as a Column"; cast = "treat this column as a different type"; when/otherwise = a column-level if/elif/else where a missing else means null, not "leave it alone".
⏱ 7 min · Topic 4 of 8
Several operations reshape your columns. withColumn(name, expr) adds a new column (or replaces an existing one of the same name) from an expression. withColumnRenamed(old, new) renames a column. drop(*cols) removes columns you no longer need. Each call returns a new DataFrame (immutability), so you chain them.
Three building blocks make those expressions powerful. lit(value) wraps a plain Python scalar into a Column, needed wherever a Column is required positionally, for example withColumn("source", lit("batch")) or a literal branch value inside when. You usually do not need lit for ordinary comparisons or arithmetic: col("amount") > 100 and col("amount") * 2 already work without it, because Column operators auto-wrap a scalar on the right-hand side. lit becomes necessary when the scalar is the first operand, or an API positionally expects a Column instead of a raw value. lit(None) is untyped, so cast it (e.g. lit(None).cast("string")) if you need it to carry a concrete type.
.cast converts a column's type: col("amount").cast("double") or col("amount").cast(DoubleType()). when/otherwise (from pyspark.sql.functions) build conditional columns: when(cond1, val1).when(cond2, val2).otherwise(default), evaluated top to bottom, first match wins. If you omit .otherwise(...), any row that matches none of the when conditions gets null, not an error and not the original value, so an absent otherwise is a silent default you may not have intended.
Core mental model
withColumn = add/replace a column; lit = "this Python value, as a Column"; cast = "treat this column as a different type"; when/otherwise = a column-level if/elif/else where a missing else means null, not "leave it alone".
Why it matters
Deriving and tidying columns is half of any ETL job, and casts and conditional columns are where wrong types and silent nulls creep in. Knowing exactly what a failed cast does, and what an unmatched when row becomes, is the difference between a job that looks fine and one that is quietly wrong.
withColumn
Add or replace a single column from an expression.
lit
Wrap a plain scalar into a Column; needed when the scalar is a positional argument or the first operand.
cast
Convert a column to a different type, e.g. col("amount").cast("double").
try_cast
A null-safe cast that returns null on failure even under Spark 4.0 ANSI mode (instead of throwing).
when / otherwise
Conditional column expression; first matching when wins; missing otherwise yields null for unmatched rows.
ADD
withColumn("net", …)
add or replace
→↓
RENAME
withColumnRenamed(...)
relabel
→↓
REMOVE
drop("status")
no longer needed
The three column-shaping moves: add/replace with withColumn, relabel with withColumnRenamed, remove with drop. Each returns a new DataFrame.
net = amount × 0.9 is added; country → country_code is renamed; status is dropped. Column order reflects the operations.
withColumn("net", …) adds a derived column; withColumnRenamed relabels country; drop removes status. Each returns a new DataFrame. For many new columns at once, prefer a single select(...) with all expressions over a long withColumn chain.
lit, cast, and when/otherwise togetherworked example
SQL
Input data
orders (the input — amount arrives as STRING, one bad value)4 rows
priced.show() (the output, default Spark 3.x ANSI=off)
id
amount
source
tier
1
200
batch
high
2
80
batch
mid
3
10
batch
low
4
NULL
batch
low
"n/a" cannot cast to double, so under Spark 3.x defaults it becomes null (no error) rather than failing the job. That null amount fails both when conditions (a comparison against null is not true), so it falls to otherwise("low"), not to null. lit("batch") fills every row identically since it is a constant, not a column reference.
cast("double") silently nulls the unparseable "n/a" under Spark 3.x defaults; when/otherwise then treats that null amount as neither >= 150 nor >= 50 (a null comparison is not true), so it falls through to otherwise. lit("batch") shows a literal value materialised into every row.
cast behavior on bad input: Spark 3.x vs Spark 4.0
Spark version
spark.sql.ansi.enabled default
col("amount").cast("double") on "n/a"
Spark 3.x
false
Returns null, silently, no error raised
Spark 4.0
true
Throws a runtime exception (invalid cast) unless you opt out
Common mistake
Chaining dozens of withColumn calls. Each call adds a Project node to the logical plan; a long Python loop of withColumn calls can make Catalyst's analyzer/plan-build step take minutes (or stack overflow) before a single task runs, this is plan-build cost, not shuffle cost. Accumulate expressions and apply one select(*exprs), or use withColumns({...}) (Spark 3.3+), instead.
Writing a when chain with no .otherwise(...) and assuming unmatched rows keep their original value. Unmatched rows silently become null, not an error, not the prior value. Always add .otherwise(...) unless null really is the intended default.
Trusting that a failed cast() will raise an error you can catch. Under Spark 3.x defaults a failed cast is a silent null, your job keeps running with bad data quietly nulled out; under Spark 4.0 it throws instead. Know which version and ANSI setting you are running, and consider try_cast where null-on-failure is the desired behavior.
Better habit
Use withColumn for one or a few derived fields; switch to one select for many.
Always pair a when/...when chain with an explicit .otherwise(...).
Check spark.sql.ansi.enabled (and the Spark version) before relying on cast() failure behavior.
cast does not fail the same way on every Spark version
Spark 3.x (ansi.enabled=false by default): a failed cast returns null, silently. Spark 4.0 flips the default to ansi.enabled=true: the same failed cast throws at runtime. Use try_cast when you explicitly want null-on-failure regardless of the ANSI setting, and never assume "no exception" means "no bad data".
When you do (and do not) need lit
col("amount") > 100 and col("amount") * 2 work without lit, Column operators auto-wrap the right-hand scalar. You need lit when the value is the first operand, a constant column on its own (withColumn("source", lit("batch"))), or a branch value some API expects as a Column.
Remember this
withColumn adds/replaces, lit lifts a scalar into a Column, cast converts types (silently null in 3.x, throws in 4.0 ANSI mode), when/otherwise builds conditional columns with first-match-wins and null-by-default if otherwise is missing. For many derived columns, prefer one select over a long withColumn chain.
Practice3 prompts
Add two derived columns in a single select instead of two withColumns.
Write a when/otherwise that tiers orders into "high"/"mid"/"low", then explain what happens to a null amount row without an otherwise.
Why is col("amount") > 100 valid without lit, but withColumn("source", "batch") is not (it needs lit("batch"))?
05 · Order & trim
Sorting, Distinct & Limit
select/filter/withColumn are free movers; orderBy and distinct make everyone swap cards (a shuffle); dropDuplicates picks a winner per key by chance, not by rule, so do not depend on which row it keeps.
⏱ 7 min · Topic 5 of 8
Three more everyday operations, but with a performance and determinism twist. orderBy / sort sorts rows; dropDuplicates / distinct removes duplicate rows; limit(n) keeps the first n rows. The catch: orderBy and distinct are wide transformations, they shuffle data across the cluster, so use them deliberately.
limit is cheaper but still triggers work; a global limit(n) plans as LocalLimit on each partition followed by a GlobalLimit that consolidates results onto a single partition, so it is cheap for previews but not strictly narrow (no full shuffle of all the data, but a final narrowing step still happens). It is great for previews; full exchange-operator mechanics are Chapter 17 territory.
distinct should be applied after you have filtered down, deduplicating a huge dataset is an expensive shuffle. dropDuplicates(subset) is the targeted version, dedupe on a key instead of the whole row, but it has a sharp edge: it keeps an arbitrary surviving row per key, not deterministically the first or the latest one, and that choice can change between runs. And global orderBy on a massive DataFrame is costly; often you only need ordering within groups (a window function, Chapter 12) or no ordering at all.
Core mental model
select/filter/withColumn are free movers; orderBy and distinct make everyone swap cards (a shuffle); dropDuplicates picks a winner per key by chance, not by rule, so do not depend on which row it keeps.
Why it matters
A stray global sort, a distinct on raw data, or a dropDuplicates you assumed was deterministic, can dominate a job's runtime or quietly change which row survives between runs. Knowing these costs and behaviors is a direct, easy correctness and performance win.
orderBy / sort
Sorts rows; wide (shuffle). Global sort on big data is expensive.
distinct / dropDuplicates
Removes duplicate rows; wide (shuffle); distinct == dropDuplicates over all columns.
limit(n)
Keeps the first n rows; consolidates via LocalLimit→GlobalLimit, cheap-ish, ideal for previews.
dropDuplicates(subset)
Dedupe based on specific columns; keeps an arbitrary, non-deterministic row per key.
Narrow (cheap) — pipeline freely
selectfilter / wherewithColumndroprenamelimit*
Wide (shuffle) — use sparingly
orderBy / sortdistinctdropDuplicates
Among the everyday operations, most are narrow (no shuffle) and cheap. orderBy and distinct are wide — they shuffle the whole dataset — so apply them late and sparingly. limit consolidates to one partition (cheap for previews) but is not strictly narrow.
distinct → {UK, DE, FR}; orderBy → [DE, FR, UK]; limit(2) → [DE, FR]. Two wide operations (distinct, orderBy) ran here, fine on this tiny data, but something to minimise at scale.
distinct removes duplicate countries (a shuffle), orderBy sorts them (another shuffle), and limit(2) keeps the first two. On small data this is fine; at scale, each wide step costs, so filter/select first and avoid unnecessary sorts.
dropDuplicates(subset) keeps an arbitrary row, not "latest"worked example
SQL
Input data
orders (the input — two rows share order_id=1, different statuses)3 rows
deduped.show() (one possible output — NOT guaranteed to be this one)
order_id
status
updated_at
1
pending
2026-06-01
2
pending
2026-06-02
order_id=1 kept the "pending" row here, but on a different run (different partitioning, different task scheduling) it could just as easily keep "shipped". dropDuplicates does not promise first, last, or any particular row.
If you need a deterministic "keep latest by updated_at", do not reach for dropDuplicates(subset); use a Window ordered by updated_at with row_number() and filter row_number() == 1 (Chapter 12 owns window functions in depth).
Common mistake
Calling distinct on a raw, huge DataFrame. A massive shuffle; filter/select to shrink first, or dedupe on a key with dropDuplicates(subset).
Global orderBy when you only need order within groups. Use a window function (Chapter 12) instead of sorting the entire dataset.
Using dropDuplicates(["id"]) and assuming it keeps the latest row. It keeps an arbitrary row, non-deterministic across runs; use Window + row_number() (Chapter 12) for a deterministic "keep latest" rule.
Sorting on a nullable column without thinking about where nulls land. Default ASC puts nulls first and DESC puts nulls last, which can surprise a "top N" query; override with asc_nulls_last() / desc_nulls_first() when that default is wrong for your use case.
Better habit
Treat orderBy and distinct as costs; minimise them.
Dedupe on a key (dropDuplicates(["id"])) only when "any matching row" is truly acceptable; otherwise use a Window.
Be explicit about null ordering (asc_nulls_last, desc_nulls_first) whenever a sorted column can be null.
Interview note
Knowing that orderBy and distinct are shuffles, and that dropDuplicates does not deterministically keep "the latest" row, signals real production experience, not just API knowledge. A strong answer names Window + row_number() as the deterministic alternative.
Remember this
orderBy and distinct are wide (shuffles); limit consolidates to one partition but is not strictly narrow. dropDuplicates(subset) keeps an arbitrary, non-deterministic row per key, use Window + row_number() when you need "keep latest". orderBy puts nulls first on ASC, last on DESC by default.
Practice2 prompts
Rewrite a "dedupe a huge table, keep the latest by updated_at" step so it is deterministic.
You sort a nullable amount column DESC for a "top 10" report. Where do null amounts land, and how would you push them out of the top 10?
06 · Putting it together
Chaining Operations Readably
shrink first (filter/select), shape in the middle (withColumn), aggregate/sort last. Column names are matched case-blind, like a filing system that ignores capitalisation until two folders collide.
⏱ 6 min · Topic 6 of 8
Real transformations combine many of these operations. Because each returns a new DataFrame, you chain them, and the readable convention in PySpark is to wrap the chain in parentheses, one operation per line. This reads top-to-bottom like a recipe and is easy to edit.
Order matters for performance, not just style: put narrow, shrinking operations (filter, select) first, and wide operations (groupBy, orderBy) last, so the expensive steps see the least data. The diagram below extends that same pipeline shape, source → shrink → shape → wide-last, that you will reuse for every transformation you write.
One name-resolution gotcha belongs here too: Spark's column matching is case-insensitive by default (spark.sql.caseSensitive = false), so col("Country") resolves the column country. That is usually harmless until two columns differing only in case meet, most often after a join, at which point Spark raises an "ambiguous reference" error rather than silently picking one. This is about how Spark resolves identifiers, not about string values inside a column, "UK" and "uk" as data are still compared case-sensitively unless you normalise them (Chapter 9).
Core mental model
A transformation is a readable, top-to-bottom pipeline: shrink first (filter/select), shape in the middle (withColumn), aggregate/sort last. Column names are matched case-blind, like a filing system that ignores capitalisation until two folders collide.
Why it matters
Clear chaining is what makes Spark code maintainable; good ordering is what makes it fast. Case-insensitive column resolution is a small detail that turns into a confusing "ambiguous reference" error at the worst time, right after a join, if you do not know to expect it.
method chaining
Linking operations because each returns a new DataFrame.
parenthesised chain
The readable PySpark style: wrap in ( ) with one operation per line.
operation ordering
Placing narrow/shrinking ops before wide/expensive ones.
round (alias)
Imported as sround here to avoid clashing with Python's built-in round.
spark.sql.caseSensitive
Defaults to false: column name matching ignores case; column VALUES are still compared case-sensitively.
READ
source
raw rows
→↓
NARROW
filter → select
shrink early
→↓
SHAPE
withColumn
derive fields
→↓
WIDE (last)
groupBy / orderBy
on the least data
A well-ordered transformation: shrink the data early with filter/select, shape it in the middle with withColumn, and leave any wide step (groupBy, orderBy) for last when the data is smallest.
The DE 0-amount row is filtered out first; net = round(amount × 0.9, 2) is derived; country is renamed; the final select projects three columns.
Read the chain top to bottom: filter (shrink) → withColumn (derive) → rename (tidy) → select (project). Wrapping in parentheses with one op per line is the readable PySpark convention, and ordering narrow-before-wide keeps it efficient.
Common mistake
Writing one giant unparenthesised line. Hard to read and edit; use the parenthesised, one-op-per-line style.
Building many derived columns with a Python for loop of withColumn calls. Each withColumn adds a Project node to the logical plan; a loop of hundreds builds a deeply nested plan that can take minutes for Catalyst's analyzer alone to resolve, before a single task runs. This is a plan-build (analysis) time cost, not a runtime shuffle cost. Fix: accumulate Column expressions in a list and apply one select(*exprs), or use withColumns({...}) (Spark 3.3+) which adds many columns in a single plan node.
Joining two DataFrames that each have a column differing only by case (e.g. country and Country). Spark resolves both names to the same identifier under default case-insensitivity and raises an "ambiguous reference" error; rename one before the join.
Better habit
Wrap chains in parentheses, one operation per line.
Order narrow-before-wide for performance.
Accumulate expressions and use one select/withColumns instead of looping withColumn.
Production reality
Readable chains are easier to review and debug, and because Spark is lazy, splitting a chain over many lines costs nothing at runtime; it is all one optimised plan.
withColumn-in-a-loop is a planning-time bug, not a runtime one
A long Python loop calling withColumn repeatedly grows the logical plan one Project node per call. The cost shows up as the job appearing to hang before any task starts, Catalyst is still analyzing the plan. Replace the loop with one select(*exprs) or withColumns({...}).
Remember this
Chain operations in a readable parenthesised block, one per line, and order them narrow-before-wide so expensive steps see the least data. Build many derived columns with one select/withColumns, not a withColumn loop, that loop's cost is plan-build time, not a shuffle. Column names match case-insensitively by default; values do not.
Practice2 prompts
Reorder a chain so the filter happens before a groupBy, and explain the benefit.
You have a list of 200 (name, expression) pairs to add as columns. Write the one-select version instead of a 200-iteration withColumn loop, and explain why the loop version is slow before any task even runs.
07 · Recap
Consolidating Core Operations
choose what matters (select/filter), tell it clearly (withColumn/cast/when), and only pay the expensive shuffle price (sort/distinct) once, at the end, on the smallest data possible.
⏱ 4 min · Topic 7 of 8
You now have the full everyday toolkit: select/selectExpr and filter to choose columns and rows, withColumn/lit/cast/when-otherwise to derive and shape columns, withColumnRenamed/drop to tidy, and orderBy/distinct/dropDuplicates/limit to order and trim, each with its cost (narrow vs wide) and its edge cases (nulls, casts, non-determinism) called out.
The throughline across all of it is the same: shrink early, shape in the middle, pay for wide operations only at the end and only on data you have already reduced, and treat every null, cast, and dedupe as a decision you made on purpose rather than a default you stumbled into.
Use the table below as a quick-reference the next time you are writing a transformation chain, and the practice prompts to rehearse the edge cases that actually show up in interviews and production incidents.
Core mental model
A Spark transformation is a small, ordered story: choose what matters (select/filter), tell it clearly (withColumn/cast/when), and only pay the expensive shuffle price (sort/distinct) once, at the end, on the smallest data possible.
Why it matters
These operations are the vocabulary every later chapter assumes you have fluent: aggregations (Chapter 10), joins (Chapter 11), and window functions (Chapter 12) are all built from select/filter/withColumn fundamentals plus the null and shuffle awareness from this chapter.
Quick reference: cost and key edge case
Operation
Cost
Key edge case
select / selectExpr
Narrow
Same Catalyst plan either way; selectExpr just parses a string
filter / where
Narrow
A NULL condition is dropped, and so is its negation
withColumn / lit / cast / when
Narrow
Missing otherwise → null; cast on bad data is null (3.x) or throws (4.0 ANSI)
orderBy / sort
Wide (shuffle)
ASC = nulls first, DESC = nulls last, by default
distinct / dropDuplicates
Wide (shuffle)
dropDuplicates(subset) keeps an arbitrary, non-deterministic row
limit(n)
Consolidates to 1 partition
Cheap for previews; not strictly narrow
Common mistake
Treating this chapter's operations as "just syntax" rather than internalising the cost and null edges. You will write code that runs and even looks correct in a small test, then silently drops rows or mis-tiers data once it meets nulls or scale in production.
Re-deriving the same handful of habits chapter after chapter instead of making them automatic now. Slower review cycles and repeated bugs; filter-early, otherwise-always, and dedupe-with-Window-when-deterministic-matters should become reflexes before Chapter 10.
Better habit
Default order: filter/select to shrink, withColumn/cast/when to shape, orderBy/distinct only at the end.
Always ask "what happens to null here?" for every filter, cast, and when chain you write.
Reach for select(*exprs) or withColumns({...}) instead of looping withColumn; reach for Window + row_number() instead of trusting dropDuplicates to pick "the right" row.
Study tip
Re-run this chapter's null-in-filter and dropDuplicates examples yourself with a small local DataFrame, then change one value and predict the output before running it. That habit catches more bugs than re-reading the docs.
Remember this
Core DataFrame operations are simple individually; the senior skill is sequencing them (narrow-before-wide) and handling their edge cases (nulls, casts, non-deterministic dedupe) on purpose, every time.
Practice2 prompts
In one paragraph, explain to a teammate why dropDuplicates(["id"]) is not a safe way to "keep the latest" row.
Hands-on (20–30 min): take a small orders DataFrame with a few null countries, a few bad-string amounts, and duplicate order_ids; write one parenthesised chain that filters nulls explicitly, casts amount safely, tiers orders with when/otherwise (with an otherwise), and deduplicates deterministically with a Window instead of dropDuplicates. Compare your output to what dropDuplicates would have produced.
08 · Next Chapter
Next Chapter
You can now select, filter, shape, cast, and chain DataFrame operations cleanly, efficiently, and with an eye on nulls.
⏱ 3 min · Topic 8 of 8
Next chapter
Working with Different Data Types
You can now select, filter, shape, cast, and chain DataFrame operations cleanly, efficiently, and with an eye on nulls.
Next, Chapter 9 handles the messy reality of real columns in depth: strings, dates and timestamps, the full null toolkit (na.fill, na.drop, coalesce, eqNullSafe), and complex types (arrays, maps, structs), with the built-in functions that tame them.
