PySpark Practice Questions & Study Strategy

1. Data Processing & Transformations

1.1 Customer Analytics Dataset

Dataset:

- customers.csv

- transactions.csv

Tasks:

- Load both datasets into Spark and perform:

* Schema inference vs manual schema

* Handling malformed records

- Calculate each customer’s:

* Total spend

* Average transaction value

* Most frequently purchased product

- Detect customers with >30% drop in monthly spending QoQ

2. Data Cleaning & Data Quality

2.1 Handling Dirty Logs

Log File Issues:

- Missing timestamps

- Mixed delimiters

- Null/empty user IDs

- Corrupted numeric fields

Tasks:

- Write PySpark code to detect and fix bad rows

- Replace null timestamps using forward-fill (last known timestamp)

- Profile data quality metrics:

* % nulls

* % outliers

* duplicate rows

3. Joins & Data Modeling

3.1 Star Schema Construction

Raw CSVs:

- orders

- order_items

- products

- suppliers

- customers

Tasks:

- Build a dimensional model:

* Dimensions: product, supplier, customer

* Fact: order_items_fact

- Use PySpark to enforce SCD-2

- Optimize join strategy using:

* broadcast joins

* partition pruning

* bucketing

4. Performance Optimization

4.1 Optimize 1 Billion+ Row ETL Job

Tasks:

- Identify performance bottlenecks in PySpark script

- Refactor using:

* caching

* coalesce vs repartition

* predicate pushdown

* Parquet partitioning

* optimizing shuffle-heavy operations

- Compare execution time (before vs after)

5. PySpark SQL & Window Functions

5.1 Sales Window Aggregations

Tasks:

- Compute rolling 7-day / 30-day sales windows

- Identify:

* top 5 products per month

* users with 7‑day activity streak

- Use window functions: lag, lead, dense_rank, ntile, cume_dist

6. Real-Time Streaming (Structured Streaming)

6.1 Kafka Data Stream

Tasks:

- Create a Structured Streaming job that:

* Reads JSON events

* Handles corrupt rows with badRecordsPath

* Deduplicates events using watermarking

* Aggregates events in 10‑minute windows

- Write results to:

* Delta Lake

* Parquet

* Kafka output topic

8. ETL Pipelines & Scheduling

8.1 Build an Incremental ETL Pipeline

Tasks:

- Read only new partitions daily from Parquet

- Detect updated rows using watermark or surrogate keys

- Write incremental data using merge (Delta Lake / Hudi)

9. Ride-Sharing Platform Analytics

Dataset:

- trip data

- driver data

- location zones

Tasks:

- Compute driver earnings per week

- Identify fraudulent trips with anomaly detection rules

- Build zone‑level metrics:

* peak hours

* avg pickup vs drop‑off delay

- Write final results into partitioned tables

DP‑700 Recommendations

YouTube channel:

https://youtu.be/jTDSP7KBavI

Suggested Study Strategy

Week 1: Review Study Guide & self‑paced modules

Week 2: Watch/read full video course; take notes

Week 3: Hands‑on practice with Microsoft Fabric

Final Week: Use Q&A, exam tips, mock questions

Suggested Platforms:

| Platform | What You Get | Notes |

|---------|--------------|-------|

| Microsoft Learn – Practice Assessments | Free official practice questions | Limited number of questions |

| Data Engineer Journey blog | “Ultimate Guide … 50 Practice Questions” | Topic‑based, good explanations |

| Certyiq | Free practice test | Good for timed mock experience |

| ITExams.com | Free Q&A | Some access available |