SQL rewrite 'for performance' shipped an identical plan and identical runtime
Symptoms
- Team converted 300 lines of DataFrame code to spark.sql()
- Runtime unchanged within noise
- The ticket promised a 30% gain
Interactive Spark interview questions on Spark SQL: the same engine, a different keyboard. Practice with the matching lesson. SQL strings and the DataFrame API compile to the same plan. Temp views, the catalog, and when names actually resolve.
Question 1 of 9
Two teammates write the same aggregation — one in spark.sql(), one in the DataFrame API. Which one runs faster?
Answer it out loud, then reveal. Play steps through like the simulators.
Case 1 of 4 · symptoms
Symptoms
Indexed as FAQ. Open any item if you prefer a list to Play.
Showing 9 of 9 questions.
Two teammates write the same aggregation — one in spark.sql(), one in the DataFrame API. Which one runs faster?
Spark SQL: the same engine, a different keyboard · tap to open the answer
Say this first: Neither. Both go through Catalyst and end up as the same physical plan.
Why: A SQL string and a chain of DataFrame calls both become an unresolved logical plan, then get analyzed, optimized, and planned by the same code path. Run explain('formatted') on both and you see the same FileScan, Exchange, and HashAggregate nodes.
Common mistake: Claiming the DataFrame API is 'closer to the engine' so it skips a layer that costs runtime.
Follow-up they will ask: So what would actually make the two versions differ?
Your notebook created a temp view; the next notebook on the same cluster can't see it. Explain.
Spark SQL: the same engine, a different keyboard · tap to open the answer
Say this first: createOrReplaceTempView is session-scoped, and the other notebook has its own SparkSession.
Why: Temp views live in the session catalog and disappear with the session. createGlobalTempView registers into the global_temp database, shared across sessions of the same application, and must be referenced as global_temp.name. Neither is in the metastore — only saveAsTable creates something another job can find tomorrow.
Common mistake: Assuming a temp view is a table because SELECT * worked in the same notebook.
Follow-up they will ask: When would you reach for a global temp view instead of just writing a table?
A managed table and an external table both show up in SHOW TABLES. What changes the moment someone runs DROP TABLE?
Spark SQL: the same engine, a different keyboard · tap to open the answer
Say this first: Dropping a managed table deletes the data files; dropping an external table only removes the metadata.
Why: A managed table's location is owned by the catalog, so DROP removes the metastore entry and the underlying directory. An external table keeps its files at the LOCATION you declared — DROP leaves the data and you can re-register it. DESCRIBE EXTENDED shows Type: MANAGED vs EXTERNAL along with the Location.
Senior depth: DESCRIBE EXTENDED before any DROP in a runbook. Managed is fine for data the pipeline owns; external is for data other systems also write.
Common mistake: Saying DROP TABLE never deletes data because 'Spark doesn't own storage'.
Follow-up they will ask: How do you verify which kind you are about to drop?
A scheduled SQL pipeline fails with an AnalysisException and Spark UI shows zero jobs for that run. Where do you look, and what is it definitely not?
Spark SQL: the same engine, a different keyboard · tap to open the answer
Say this first: It failed in Catalyst on the driver — name and type resolution — so it is not an executor or data-volume problem.
Why: Analysis resolves tables, columns, and functions against the catalog before a single stage is submitted, so a resolution failure means no job, no stage, and nothing in executor logs. spark.sql() builds and analyzes its plan when called, so the guilty statement is the first one referencing the renamed column or dropped table — not the write at the bottom of the notebook.
Senior depth: File permissions, corrupt Parquet footers, and Delta schema-on-write mismatches — those need a task to run. Resolution failures never get that far.
Common mistake: Digging through executor stderr for a query that never submitted a task.
Follow-up they will ask: Which failures do get deferred until execution time?
What does CACHE TABLE do that CREATE OR REPLACE TEMP VIEW does not?
Spark SQL: the same engine, a different keyboard · tap to open the answer
Say this first: A temp view registers a name over a plan; CACHE TABLE materialises the data into executor storage and runs immediately.
Remember these
Why: A temp view is a catalog entry pointing at a logical plan, so referencing it three times means executing it three times. CACHE TABLE takes the same plan, executes it, and stores the result as an InMemoryRelation held by the executors' block managers, which then appears on the Storage tab with a Fraction Cached figure. It is eager by default — the statement itself triggers the job — whereas CACHE LAZY TABLE defers until the first query touches it. After caching, the physical plan of a query over that name shows InMemoryTableScan instead of a FileScan, which is the one-glance confirmation that the cache is being used. UNCACHE TABLE releases it; so does losing the session, because both views and caches are session-scoped and are not metastore objects.
CREATE OR REPLACE TEMP VIEW busy AS SELECT * FROM orders WHERE region = 'US';
CACHE TABLE busy;
SELECT count(*) FROM busy; -- InMemoryTableScanCommon mistake: Calling a temp view a cache and being puzzled that three queries over it cause three scans.
Follow-up they will ask: When is caching that view the wrong move?
After a runtime upgrade your SQL pipeline starts throwing arithmetic overflow and cast errors on data it accepted for years. What changed?
Spark SQL: the same engine, a different keyboard · tap to open the answer
Say this first: ANSI SQL mode turns silently-null results into errors for invalid casts, overflow, and division by zero.
Remember these
Why: With spark.sql.ansi.enabled off, Spark's historical behaviour is permissive: cast('abc' as int) yields null, an integer overflow wraps or nulls, and x / 0 returns null. With ANSI mode on, each of those raises instead — CAST_INVALID_INPUT, ARITHMETIC_OVERFLOW, DIVIDE_BY_ZERO — so the first run after the flag flips surfaces every latent data quality problem at once. This is a correctness improvement and an operational shock, which is why it should be rolled out deliberately rather than inherited from a runtime default. The migration path is per-expression, not global: try_cast, try_divide, try_add, and try_sum give you the lenient result explicitly, so the code states which nulls are intentional. Check the Environment tab to confirm the effective value before arguing about behaviour, since cluster defaults and session settings both contribute. Keeping ANSI mode on with explicit try_* calls is the version that still catches real bugs.
SELECT try_cast(raw_qty AS INT) AS qty,
try_divide(revenue, units) AS unit_price
FROM bronze.sales;Common mistake: Turning ANSI mode back off globally instead of marking the handful of expressions that are meant to be lenient.
Follow-up they will ask: How would you find every expression that depends on lenient casting before flipping the flag?
A filter on a partitioned table still read every partition. Give three reasons pruning can fail.
Spark SQL: the same engine, a different keyboard · tap to open the answer
Say this first: A function or cast applied to the partition column, a predicate on a non-partition column, or a join-derived filter that needs dynamic partition pruning.
Remember these
Why: Static pruning requires the optimizer to evaluate the predicate against partition values before the scan, so it must see the bare partition column on one side. Wrapping it defeats that: if the table is partitioned by a string event_date and you write where year(event_date) = 2024, or where cast(event_date as date) >= '2024-01-01', the predicate lands in DataFilters rather than PartitionFilters and every directory is read. The second case is simply the wrong column — filtering on a high-cardinality data column prunes nothing at the directory level, though Parquet row-group statistics or Delta data skipping may still help inside the files. The third case is a filter that exists only through a join, as when a date dimension restricts the fact table; that needs spark.sql.optimizer.dynamicPartitionPruning.enabled, and it only applies when the dimension side is broadcastable, which is why it silently stops working once the dimension grows past spark.sql.autoBroadcastJoinThreshold. The evidence is always the same two places: PartitionFilters on the FileScan node, and the number of files read plus Input Size / Records on the Stages page.
-- prunes
WHERE event_date >= '2024-01-01'
-- does not prune
WHERE year(event_date) = 2024Senior depth: I read PartitionFilters before I read runtime. Pruning is binary and visible in the plan, so there is never a reason to speculate. The DPP-stops-working case is worth planning for: dimensions grow, broadcast eligibility disappears, and a query that pruned for a year starts full-scanning with no code change.
Common mistake: Writing a predicate against a derived expression of the partition column and reporting that 'partitioning does not work'.
Follow-up they will ask: How do you confirm dynamic partition pruning actually fired?
A query defines a CTE and references it three times; the source table is scanned three times. Why isn't a CTE a cache?
Spark SQL: the same engine, a different keyboard · tap to open the answer
Say this first: A CTE is a name for a subquery that Catalyst may inline at each reference — it carries no materialisation guarantee.
Remember these
Why: In Spark, a WITH clause introduces a logical name, and the optimizer is free to substitute the subquery at every reference site, which is usually what you want because it lets predicates and projections push into each use independently. The cost is that identical work can be planned more than once. Spark will sometimes avoid repeating it: when the shared subtree ends in a shuffle, the planner can reuse the exchange and you see ReusedExchange in the physical plan pointing back at the original Exchange. With no shuffle in the shared part — a plain filtered scan, for example — there is nothing to reuse and each reference becomes its own FileScan. If you need a real materialisation, say so: CACHE TABLE over the subquery, persist() on the DataFrame equivalent, or write a temporary table when the subquery is expensive and reused by several queries. Read the SQL tab's plan and count FileScan nodes against the same path; that number, not the CTE count, tells you what you are paying.
CACHE TABLE recent AS
SELECT * FROM orders WHERE order_ts > current_date() - INTERVAL 7 DAYS;
SELECT region, count(*) FROM recent GROUP BY region;Senior depth: Inlining is usually the right default — a materialised CTE would block the pushdown that makes each reference cheap. The judgement call is whether the shared part is expensive and the per-reference pushdown is weak. Only then is paying for materialisation worth it, and then I prefer a written table over a cache so the cost is visible and bounded.
Common mistake: Treating a CTE as an optimisation fence that guarantees the subquery runs once.
Follow-up they will ask: How do you count how many times the same table is scanned in one query?
A BI tool is about to fire four thousand small SQL queries an hour at your Spark cluster. What do you tell the team?
Spark SQL: the same engine, a different keyboard · tap to open the answer
Say this first: A batch Spark cluster is the wrong serving layer; route interactive SQL to a warehouse-style endpoint and pre-aggregate what the dashboard needs.
Remember these
Why: Every one of those queries pays full Catalyst planning, job and stage submission, and a shuffle whose partition count was tuned for batch, so a query touching a few megabytes still spends seconds in fixed overhead. Concurrency makes it worse rather than better: all sessions share one driver, so planning and the listener bus become the contention point long before executor CPU does, and one user's heavy query can stall everyone. Batch Spark also has no result cache, so the identical dashboard tile recomputes on every refresh. The architecture that works is a serving tier — a SQL warehouse endpoint with result caching and fast startup — fed by pre-aggregated Delta tables that the batch pipeline maintains. Push the heavy grouping into the nightly job so dashboard queries read narrow, already-aggregated rows with partition pruning and Z-ordering or liquid clustering on the filter columns. If a genuinely sub-second interactive requirement remains, that belongs in a serving database, not in Spark at all.
Senior depth: Draw the line at the grain the dashboard queries. If the BI tool never asks for row-level detail, no interactive query should ever touch the fact table. The architectural failure is letting the serving layer query the batch layer's physical model, and it will keep costing money regardless of how the compute is sized.
Common mistake: Scaling the batch cluster up to absorb dashboard concurrency, which does nothing about driver-side planning contention.
Follow-up they will ask: Which part of the dashboard workload would you still leave on the batch cluster?