Introduction
Working with big data in AWS Glue and PySpark often raises many practical questions. This blog post answers common challenges with code examples and best practices for ETL workflows, caching, joins, sampling, and partitioning, making it easier for you to handle large datasets efficiently.
Q1: Which AWS service is used to monitor Glue ETL jobs?
Answer:
The primary service is Amazon CloudWatch, which collects:
- Logs: Job run logs including Spark logs and errors
- Metrics: Job success/failure, run duration, DPU usage
- You can also create CloudWatch Alarms to alert on failures or long-running jobs.
Other supporting services:
- AWS Glue Console: Job history and status
- CloudTrail: Tracks API calls for auditing
- Glue Data Quality / Data Mesh: Governance and validation
Q2: What is Glue Data Mesh and are there similar services?
Answer:
- Glue Data Mesh enables secure, cross-account data sharing with governance.
- Related AWS services:
- AWS Lake Formation: Access control and governance
- Amazon DataZone: Enterprise data catalog & sharing
- AWS Clean Rooms: Privacy-preserving analytics collaborations
- Athena + Lake Formation: Federated querying across accounts
Use case: Enables decentralized ownership while maintaining security and compliance.
Q3: How can I perform a left anti join in MySQL and PySpark?
Answer:
MySQL (no direct LEFT ANTI JOIN):
SELECT e.*
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
PySpark DataFrame:
df_emp.join(df_dept, df_emp.dept_id == df_dept.dept_id, "left_anti").show()
Tip: Returns rows in the left table with no matching row in the right table.
Q4: How do I check if an RDD or DataFrame is cached?
Answer:
RDD:
rdd.is_cached
rdd.getStorageLevel()
DataFrame:
df.cache() # Cache in memory
df.count() # Trigger caching
df.is_cached # True/False
df.storageLevel # Storage details
Always perform an action (like
count()orshow()) to materialize the cache.
Q5: What is the purpose of SparkContext.parallelize()?
Answer:
- Converts a local Python collection into a distributed RDD.
- Enables parallel processing across the cluster.
Example:
rdd = sc.parallelize([1,2,3,4], numSlices=2)
rdd2 = rdd.map(lambda x: x*2)
rdd2.collect()
Tip: Great for testing small datasets or simulating distributed processing.
Q6: How to sample large datasets for testing?
Answer:
- Random fraction sampling:
df_sample = df.sample(fraction=0.01, seed=42)
- Limit number of rows:
df_sample = df.limit(10000)
- Source-level sampling: Read only a few files/partitions from S3/Parquet.
- Stratified sampling: Maintain proportional representation with
sampleBy().
Tip: For 70 TB datasets, start with 0.01%-0.1% of the data for testing.
Q7: Does partitionBy create subdirectories for multiple columns?
Answer:
Yes. Writing a DataFrame with multiple partition columns creates nested directories:
df.write.partitionBy("year", "month").parquet("s3://bucket/sales-data/")
Directory structure:
s3://bucket/sales-data/year=2025/month=01/
s3://bucket/sales-data/year=2025/month=02/
Tip: Partitioning improves query performance, but avoid too many small partitions.
Q.8 Find Customer who generated max revenue
%sql
-- Create Orders table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE
);
-- Create Order Details table
CREATE TABLE order_details (
order_detail_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
unit_price DECIMAL(10,2)
);%sql
-- Insert sample data into Orders
INSERT INTO orders (order_id, customer_id, order_date) VALUES
(1, 101, '2025-01-01'),
(2, 102, '2025-01-03'),
(3, 101, '2025-01-05'),
(4, 103, '2025-01-07'),
(5, 102, '2025-01-08');
-- Insert sample data into Order Details
INSERT INTO order_details (order_detail_id, order_id, product_id, quantity, unit_price) VALUES
(1, 1, 201, 2, 100.00), -- Order 1 by customer 101 -> 200
(2, 1, 202, 1, 150.00), -- -> 150, total 350
(3, 2, 203, 3, 50.00), -- Order 2 by customer 102 -> 150
(4, 3, 204, 5, 20.00), -- Order 3 by customer 101 -> 100
(5, 4, 205, 1, 500.00), -- Order 4 by customer 103 -> 500
(6, 5, 206, 2, 80.00); -- Order 5 by customer 102 -> 160Solution
%sql
select o.customer_id, sum(od.quantity * od.unit_price) as revenue
from orders o
join order_details od
on o.order_id = od.order_id
group by o.customer_id
order by revenue desc
limit 1;Customer wise and order wise max revenue
%sql
with cte1 as (
select o.customer_id, sum(od.quantity*od.unit_price) as customer_revenue
from orders o
join order_details od
on o.order_id = od.order_id
group by o.customer_id
),
cte2 as (
select o.customer_id, o.order_id, sum(od.quantity*od.unit_price) as order_revenue
from orders o
join order_details od
on o.order_id = od.order_id
group by o.customer_id, o.order_id
)
select c1.customer_id, c1.customer_revenue, c2.order_id, c2.order_revenue
from cte1 c1
join cte2 c2
on c1.customer_id = c2.customer_id
order by c1.customer_revenue desc
limit 1
Conclusion
This Q&A guide covers the most common PySpark and AWS Glue questions for working with large-scale ETL pipelines. From caching and sampling to joins and partitioning, these tips help you design efficient, scalable, and testable workflows.
Srinimf 