PWC questions:
Union vs union all in SQL ?
Both UNION and UNION ALL are used to combine the results of two or
more SELECT queries, but the key difference lies in how they handle
duplicate rows.
📌 1. UNION – Removes Duplicates
Combines the result sets and removes duplicates.
Performs sorting internally to eliminate repeated rows.
Slightly slower than UNION ALL due to the deduplication step.
✅ Example:
SELECT city FROM employees
UNION
SELECT city FROM customers;
This returns a list of unique cities present in both employees and
customers.
📌 2. UNION ALL – Includes Duplicates
Combines all rows from the result sets, including duplicates.
Does not sort or filter duplicate records.
Faster and more efficient when you want full output.
✅ Example:
SELECT city FROM employees
UNION ALL
SELECT city FROM customers;
This returns all cities, including duplicates if a city appears in both tables.
🔍 Key Differences:
Feature UNION UNION ALL
Duplicates Removes duplicates Keeps all
duplicates
Performance Slower (due to sorting) Faster
Use Case When only unique records needed When all
records are required
Sorting Overhead Yes (internally) No
🧠 Real-World Example:
Tables:
orders_2023 | order_id | customer | |----------|----------| | 1 |A ||2
|B |
orders_2024 | order_id | customer | |----------|----------| | 2 |B ||3
|C |
Query:
SELECT * FROM orders_2023
UNION
SELECT * FROM orders_2024;
➡ Output: Rows with order_id 1, 2, and 3 (no duplicates)
SELECT * FROM orders_2023
UNION ALL
SELECT * FROM orders_2024;
➡ Output: Rows with order_id 1, 2, 2, 3 (includes duplicates)
✅ Summary
> Use UNION when you want to eliminate duplicates from combined
results.
Use UNION ALL when you want better performance and don’t mind
duplicates or you specifically need them.
-------------------------------------------------------------------------------------------------------
oo----
What is Photon Acceleration in Databricks and How Does It Work?
🔍 Definition:
Photon is a next-generation query engine developed by Databricks that
accelerates performance for SQL and Spark workloads using native C++
execution. It's designed to speed up data processing dramatically,
especially for Delta Lake and Apache Spark SQL operations.
⚙️How Photon Acceleration Works:
1. ✅ Native Vectorized Engine:
Photon is written in C++, which is faster than JVM-based execution in
traditional Spark.
It uses vectorized processing, meaning it processes multiple rows of data
at once using SIMD (Single Instruction, Multiple Data) on modern CPUs.
2. Bypasses Spark’s JVM Bottleneck:
Traditional Spark runs on the JVM (Java Virtual Machine), which introduces
memory overhead and garbage collection.
Photon runs outside the JVM, directly on hardware, which results in lower
latency and higher throughput.
3. 🧠 Optimized for Delta Lake and SQL:
Photon works especially well with Delta Lake tables.
Supports Spark SQL, DataFrame APIs, and DBSQL (Databricks SQL).
It integrates with Catalyst (Spark's SQL optimizer) and supports existing
query plans.
4. 🔄 Columnar Format:
Photon processes data in a columnar in-memory format, which improves
compression and speeds up analytical queries (e.g., GROUP BY, JOIN,
AGGREGATE).
5. 🧪 Auto-Enable on Databricks:
Photon is enabled by default on most Databricks Premium/Enterprise tiers.
No code change is needed — it automatically kicks in for compatible
workloads.
📈 Performance Benefits of Photon
Metric Improvement with Photon
Query Latency Up to 20x faster
Data Throughput Significantly higher
Resource Efficiency Lower CPU usage
Cost OptimizationFaster queries → Lower compute cost
🎯 When Should You Use Photon?
Large-scale data warehouse or BI workloads.
ETL pipelines processing Delta Lake tables.
SQL queries with heavy joins, filters, aggregations.
Lakehouse architecture running on Databricks Runtime 11.0+.
---
✅ Summary
> Photon Acceleration is a high-performance engine built in C++ by
Databricks to boost Spark SQL and Delta Lake workloads.
It works by bypassing the JVM, leveraging vectorized execution, and
optimizing memory handling — leading to massive speedups and cost
savings for SQL and ETL jobs on Databricks.
------------------------------------
o-----------------------------------------------------------------------
RDD VS Dataframe vs Dataset
Here's a detailed and interview-ready comparison of RDD, DataFrame,
and Dataset in Apache Spark — focusing on practical usage,
performance, and internal mechanics 👇
🔍 What Are They?
Concept Description
Low-level Resilient Distributed Dataset – raw distributed
RDD
collection of objects
DataFra High-level abstraction over RDDs, like a table with named
me columns
Type-safe version of DataFrame (in Scala/Java only) with
Dataset
compile-time checks
🧱 1. RDD (Resilient Distributed Dataset)
✅ Characteristics:
Fundamental Spark abstraction
Immutable, distributed collection of objects
Supports functional programming style (like map, filter, reduce)
No optimization – no Catalyst, no Tungsten
🔧 Example:
rdd = [Link]([("Alice", 25), ("Bob", 30)])
filtered = [Link](lambda x: x[1] > 26)
🧠 Use When:
You need low-level transformations
You're working with unstructured data
You require fine-grained control
📊 2. DataFrame
✅ Characteristics:
Schema-based (like a SQL table)
Internally uses RDDs but adds Catalyst Optimizer and Tungsten
Engine for speed
Can be created from structured data like CSV, JSON, Parquet, JDBC,
etc.
Most performant in terms of execution and memory usage
🔧 Example:
df = [Link]("[Link]", header=True, inferSchema=True)
[Link]([Link] > 26).select("name").show()
🧠 Use When:
You're working with structured data
You want optimized performance
You're performing SQL-like operations
📦 3. Dataset (Scala/Java only, not available in PySpark)
✅ Characteristics:
Combines the type-safety of RDDs and the optimizations of
DataFrames
Allows compile-time checks (good for large codebases in Scala)
Uses Catalyst and Tungsten internally
Not available in Python (PySpark uses DataFrames for similar use
cases)
🔧 Example (Scala):
scala
CopyEdit
case class Person(name: String, age: Int)
val ds = [Link]("[Link]").as[Person]
[Link](_.age > 26).show()
🧠 Use When:
You're using Scala/Java
You want both compile-time type safety and runtime
optimization
🚀 Performance Comparison
Feature RDD DataFrame Dataset
Ease of Use Low High Medium
Type Safety Yes No Yes (Scala only)
Performanc High (Catalyst + High (same as
Low
e Tungsten) DF)
Optimizatio Manu
Automatic (Catalyst) Automatic
n al
Use in
Yes Yes ❌ Not available
PySpark
✅ When to Use What?
Best
Scenario
Choice
DataFram
Simple ETL, SQL, BI reports
e
Complex logic with full control RDD
Type-safe, compile-time checks
Dataset
(Scala only)
DataFram
Python-based structured data apps
e
🎯 Summary
Use RDD when you need fine-grained control or work with
unstructured data.
Use DataFrame for high performance, ease of use, and SQL-
style operations.
Use Dataset if you're in Scala and need type safety +
performance.
Let me know if you want real-time examples comparing execution
time across the three, or a visual Spark DAG view of each!
-------------------------------o-------------------------------------------------
🔍 How to Optimize Joins Between Two DataFrames in PySpark or
Spark
Joining large DataFrames is common in big data processing, but it’s also
performance-intensive. If not handled correctly, it can lead to out-of-
memory errors, shuffling, and long runtimes.
Here’s how to optimize DataFrame joins like an experienced Data Engineer
👇
✅ 1. Choose the Right Join Type
Different join types have different costs:
inner – Most efficient
left, right, outer – More expensive
cross – Extremely expensive
👉 Use inner joins when possible to reduce computation.
python
CopyEdit
[Link](df2, on="id", how="inner")
✅ 2. Broadcast Smaller DataFrame
When one DataFrame is small enough to fit in memory, use broadcast
to avoid a full shuffle.
python
CopyEdit
from [Link] import broadcast
joined_df = large_df.join(broadcast(small_df), on="id")
🔧 This avoids shuffling the large DataFrame and replicates the smaller one
across executors.
✅ 3. Partitioning and Bucketing
Repartition on join key before joining to avoid skewed or
inefficient shuffling.
python
CopyEdit
df1 = [Link]("join_key")
df2 = [Link]("join_key")
Bucketing is effective when reading from tables: ensure both tables
are bucketed by the same key.
✅ 4. Filter Early
Apply .filter() or .where() before joining to reduce data volume.
python
CopyEdit
filtered_df1 = [Link]("status = 'active'")
filtered_df2 = [Link]("type = 'paid'")
result = filtered_df1.join(filtered_df2, on="id")
✅ 5. Use Select to Reduce Columns
Only include required columns in each DataFrame before joining.
python
CopyEdit
df1_small = [Link]("id", "amount")
df2_small = [Link]("id", "customer_name")
This reduces memory usage and speeds up the join.
✅ 6. Check for Skew and Use Salting (if needed)
When data is heavily skewed on join keys (e.g., many rows with same
key), apply salting:
python
CopyEdit
# Add a random salt column before join to balance the data
from [Link] import rand, concat_ws
df1 = [Link]("salt", (rand() * 10).cast("int"))
df2 = [Link]("salt", (rand() * 10).cast("int"))
# Join on both key and salt
[Link](df2, on=["id", "salt"])
✅ 7. Enable AQE (Adaptive Query Execution)
If using Spark 3.0+, enable AQE for automatic join strategy optimization:
python
CopyEdit
[Link]("[Link]", True)
AQE can:
Dynamically switch join strategies (e.g., shuffle → broadcast)
Handle skewed joins more efficiently
✅ Summary
Optimization
Purpose
Technique
broadcast() Avoid shuffling for small DataFrames
Repartition on join key Minimize shuffle cost and data skew
Filter before join Reduce data volume early
Select only needed Lower memory pressure and improve
columns performance
Salting Handle skewed joins
AQE Let Spark auto-optimize joins (3.0+)
----------------------------------------ooooooooo---------------------------------------
🔍 How to Handle Null Values in Apache Spark (PySpark)?
In real-world data, null (missing) values are very common. Spark
provides powerful and flexible methods to handle them efficiently during
transformation and analysis.
✅ 1. Detecting Null Values
Use isNull() or isNotNull() to filter rows with nulls.
python
CopyEdit
# Filter rows where 'age' is null
[Link]([Link]()).show()
# Filter rows where 'salary' is not null
[Link]([Link]()).show()
✅ 2. Drop Null Values
Use dropna() to remove rows with nulls.
python
CopyEdit
# Drop rows where any column is null
[Link]()
# Drop rows where all columns are null
[Link](how="all")
# Drop rows where specific columns are null
[Link](subset=["name", "age"])
✅ 3. Fill Null Values
Use fillna() to replace nulls with a default value.
python
CopyEdit
# Fill all numeric nulls with 0
[Link](0)
# Fill nulls in specific columns
[Link]({"age": 25, "city": "Unknown"})
✅ 4. Replace Nulls with Column Mean/Median
You can fill missing values with statistical summaries:
python
CopyEdit
mean_age = [Link]([Link]("age")).collect()[0][0]
df = [Link]({"age": mean_age})
✅ 5. Using when and otherwise
Use [Link]() to conditionally handle nulls.
python
CopyEdit
from [Link] import when, col
df = [Link]("status", when(col("score").isNull(), "missing")
.otherwise("valid"))
✅ 6. Drop Duplicate Null Rows (Optional)
python
CopyEdit
[Link]()
🧠 Null Handling Best Practices:
Scenario Recommended Strategy
Nulls in critical fields Use dropna() or validation
Nulls in optional or sparse
Use fillna() with defaults
fields
Aggregations on null
Use [Link]() or when()
columns
Filter out or replace
Nulls causing join issues
beforehand
🔧 Example:
python
CopyEdit
from [Link] import col
df = [Link]([
(1, "Alice", None),
(2, None, 4000),
(3, "Bob", 3000),
(4, None, None)
], ["id", "name", "salary"])
# Drop rows where all columns are null
[Link](how='all').show()
# Fill nulls
[Link]({"name": "Unknown", "salary": 0}).show()
✅ Summary
Handling nulls in Spark is critical for data quality. Use isNull(), dropna(),
fillna(), or when().otherwise() based on your use case.
Choose strategies that preserve valid data while eliminating or
imputing missing values intelligently.
---------------------------------------------------oooooooo--------------------------------