49. Late Data in the Raw Zone
Difficulty: Medium · Topics: Data Lake, Aggregations, Dates
The raw zone stores events in folders by the day they arrived. raw_events has event_id, event_ts ('YYYY-MM-DD HH:MM:SS', when the event happened, may be null) and ingest_date ('YYYY-MM-DD', the folder it landed in).
An event is late when its event date is more than 1 day before its ingest_date. For each ingest_date return events (all rows), late_events, oldest_event_date ('YYYY-MM-DD', the earliest event date in that folder) and reprocess_days: how many different event dates the late events belong to, which is how many downstream daily partitions must be rebuilt. Sort by ingest_date.
Row order matters for this problem. Your code is graded on 3 test cases, including hidden edge cases.
Sample data
raw_events
| event_id | event_ts | ingest_date |
|---|---|---|
| 1 | 2025-03-05 10:00:00 | 2025-03-05 |
| 2 | 2025-03-04 23:50:00 | 2025-03-05 |
| 3 | 2025-03-02 08:00:00 | 2025-03-05 |
| 4 | 2025-03-01 12:00:00 | 2025-03-05 |
| 5 | 2025-03-02 09:30:00 | 2025-03-05 |
| 6 | 2025-03-06 01:00:00 | 2025-03-06 |
| 7 | null | 2025-03-06 |
Expected output
| ingest_date | events | late_events | oldest_event_date | reprocess_days |
|---|---|---|---|---|
| 2025-03-05 | 5 | 3 | 2025-03-01 | 2 |
| 2025-03-06 | 2 | 0 | 2025-03-06 | 0 |
Hints
Hint 1
F.to_date("event_ts") gives the event date; F.datediff(F.to_date("ingest_date"), event_date) gives how many days late it is.Hint 2
Count late rows withF.sum(F.when(late, 1).otherwise(0)); a null event date is never late.Hint 3
F.countDistinct(F.when(late, event_date)) counts distinct dates among late rows only, because the when gives null for the others.Learn the concepts
- The medallion architecture · 3 min read. Bronze, silver and gold layers: what belongs in each, and where the pattern goes wrong.
- Data lake storage: formats and layout · 4 min read. CSV, JSON, Avro, ORC and Parquet compared, compression, and how to lay out folders in a data lake.
- Parsing dates and timestamps · 3 min read. to_date, to_timestamp and date_format patterns, and why yyyy and YYYY are not the same.
PySpark functions you'll practise
- groupBy
- agg
- sum
- count
- countDistinct
- min
- when / otherwise
- to_date
- datediff
- date_format
- orderBy
Related problems
- Find Files Spark Cannot Split · Medium · Data Lake
- How Much Does Each Query Read? · Medium · Data Lake
- Inventory a Partitioned Folder · Medium · Data Lake
- Monthly Retention by Cohort · Hard · Joins
- Find Partitions That Need Compaction · Easy · Data Lake
Browse
Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings · Data Lake · Lakehouse · Spark Performance
Difficulty: Easy · Medium · Hard · PySpark interview roadmap · Learn · All problems