47. Inventory a Partitioned Folder

Difficulty: Medium · Topics: Data Lake, Aggregations, Strings

files is a listing of a Parquet table in a data lake: path and size_mb. The table is partitioned Hive-style, for example s3://lake/orders/order_date=2025-03-01/country=IN/part-00000.parquet.

For every partition return order_date, country, data_files and total_mb (rounded to 1 decimal). Count only data files: paths ending in .parquet where no folder or file name starts with _ or . (that excludes _SUCCESS markers, .crc checksums and unfinished _temporary writes). Spark writes a null partition value as __HIVE_DEFAULT_PARTITION__: return it as a real null.

Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

files

path	size_mb
s3://lake/orders/order_date=2025-03-01/country=IN/part-00000.parquet	120.5
s3://lake/orders/order_date=2025-03-01/country=IN/part-00001.parquet	98.25
s3://lake/orders/order_date=2025-03-01/country=IN/.part-00000.parquet.crc	0.01
s3://lake/orders/order_date=2025-03-01/country=US/part-00002.parquet	64
s3://lake/orders/order_date=2025-03-01/country=__HIVE_DEFAULT_PARTITION__/part-00003.parquet	2.4
s3://lake/orders/_SUCCESS	0
s3://lake/orders/order_date=2025-03-02/country=IN/part-00000.parquet	110

Expected output

order_date	country	data_files	total_mb
2025-03-01	IN	2	218.8
2025-03-01	US	1	64
2025-03-01	null	1	2.4
2025-03-02	IN	1	110

Hints

Hint 1

Pull a partition value out of the path with F.regexp_extract("path", r"/order_date=([^/]+)/", 1).

Hint 2

A name starting with _ or . always follows a slash: F.col("path").rlike("/[_.]") finds them.

Hint 3

Turn the placeholder into null with F.when(col == "__HIVE_DEFAULT_PARTITION__", None).otherwise(col), then group and aggregate.

Learn the concepts

• Partitioning done right · 4 min read. When to partition a table, how to choose the column, and how over-partitioning backfires.
• 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.
• regexp_extract · 3 min read. Pull fields out of text with a regular expression, and what it returns when nothing matches.

PySpark functions you'll practise

• groupBy
• agg
• sum
• count
• when / otherwise
• filter
• withColumn
• regexp_extract

Related problems

• Find Files Spark Cannot Split · Medium · Data Lake
• Late Data in the Raw Zone · Medium · Data Lake
• Find Partitions That Need Compaction · Easy · Data Lake
• How Much Does Each Query Read? · Medium · Data Lake
• Conditional Aggregation · Medium · Aggregations

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