Partitioning, compression and optimization: DEA-C01 study notes and practice questions
What to know about Partitioning, compression and optimization for the DEA-C01 exam, from the Data Store Management domain (26% of the exam guide), then 2 free questions to test it. Part of the DEA-C01 study guide.
Partitioning questions describe slow or expensive queries over S3 and ask how to lay out the data.
How partitions work
Data is stored under prefixes such as s3://lake/sales/year=2026/month=10/day=24/.
A query that filters on year, month or day reads only matching prefixes (partition pruning), so it scans and costs less.
Choose partition columns that appear in WHERE clauses most often, usually dates, then perhaps region or tenant.
Bucketing (CLUSTERED BY) groups rows by a high-cardinality column within files, which helps filters and joins on IDs.
Getting the balance right
Problem
Symptom
Fix
Too many partitions (for example, by user ID or by minute)
Slow planning, tiny files, catalog limits
Use a coarser partition and bucket on the high-cardinality column
Too many small files
Each query opens thousands of objects
Compact files to roughly 128 MB to 1 GB; use Glue groupFiles, Iceberg compaction or Firehose buffering
Partitions not registered
New data missing from results
Crawler, MSCK REPAIR TABLE, ALTER TABLE ADD PARTITION, or partition projection
Filter not on a partition column
Full scan
Rewrite the query or repartition
Partition projection
Athena calculates partition values from rules in table properties (date ranges, integers, enums) instead of reading the catalog. It suits highly partitioned tables with predictable values and removes the need to add partitions as data lands.
Writing partitioned data
Glue and Spark: partitionBy("year","month"); Glue jobs can update the catalog as they write.
Firehose: dynamic partitioning uses keys from the records to build prefixes.
Athena CTAS and INSERT INTO can write partitioned Parquet.
Iceberg uses hidden partitioning, so queries filter on the timestamp and the table handles the layout.
Common traps
Partitioning by a column nobody filters on.
Partitioning by a unique ID, creating millions of partitions.
Forgetting that new partitions need registering when there is no crawler or projection.
A solar installer stores inverter readings in Amazon S3 as Parquet, partitioned by day under Hive-style prefixes such as dt=2026-10-19/. The Athena table has dt as its partition column and also has an event_time timestamp column.
Analysts report that a query for one day of readings scans the whole table. Their queries filter only with WHERE event_time BETWEEN two timestamps.
Which change will reduce the data that each query scans?
Choose one answer.
AA. Add a filter on the dt partition column for the same day, so that Athena reads only that partition's files.
BB. Run MSCK REPAIR TABLE before each query, so that Athena loads all of the table's partitions into the Data Catalog.
CC. Add a LIMIT clause to each query, so that Athena stops reading files once it has returned enough rows.
DD. Rewrite the Parquet files as gzip-compressed CSV, so that each file is smaller and faster to read.
Show the answer and explanation
Answer: A
A. Right: Correct. Athena prunes partitions only when the query filters on the partition column. A predicate on dt limits the scan to the matching day, while the event_time filter still trims rows within it. Learn more: Partition your data (opens in a new tab)
B. Wrong: MSCK REPAIR TABLE adds Hive-style partitions that are missing from the catalog. The partitions are already registered here, and loading them does not make a query on event_time skip any of them. Learn more: MSCK REPAIR TABLE (opens in a new tab)
C. Wrong: Athena charges for the data a query scans, and a LIMIT clause does not stop it from reading every partition before filtering. The scan stays the same size.
D. Wrong: CSV is a row format, so Athena must read every column, and the query would still scan every day. The change makes scans larger, not smaller.
Explanation
Partitioning saves scan cost only when queries filter on the partition column. A filter on a regular column such as event_time does not tell Athena which dt folders it can skip, so adding a dt predicate limits the scan to one day. Repairing partitions, LIMIT clauses and CSV files do not reduce the scan.
A logistics broker stores shipment events in Amazon S3 as Parquet, partitioned first by customer_id and then by day. It has about 30,000 customers, so each day adds 30,000 partitions, and most of them hold a single file of a few kilobytes.
Most Athena queries filter on a date range across all customers, and some also filter on one customer_id. Queries spend most of their time listing partitions and opening tiny files.
The team wants fewer, larger files while keeping both query patterns fast.
Which change meets these requirements?
Choose one answer.
AA. Add an hour level under each day partition, so that each query can prune its data more finely within the day.
BB. Rewrite the data with CTAS partitioned by day only, and bucket each day by customer_id into a fixed number of files.
CC. Keep the layout, and turn on partition projection with an injected type for customer_id, so that Athena skips the partition lookups.
DD. Rewrite the files as gzip-compressed JSON in the same partitions, so that each file takes less space and less time to read.
Show the answer and explanation
Answer: B
A. Wrong: A finer partition level multiplies the partition count and makes each file even smaller. Date-range queries would list and open more objects, not fewer.
B. Right: Correct. Partitioning by day keeps date-range pruning with far fewer partitions, and bucketing places each customer's rows in one of a fixed set of larger files. For a filter on one customer_id, Athena reads only the files in that customer's bucket. Learn more: What is bucketing? (opens in a new tab)
D. Wrong: The partition and file counts stay the same, and JSON is a row format that makes Athena read every column. The option keeps the real problem and loses columnar reads.
Explanation
Partitioning on a high-cardinality column such as customer_id creates huge numbers of tiny partitions and files. Partitioning by the column most queries filter on, here the day, and bucketing by the high-cardinality column gives fewer, larger files while equality filters on customer_id still read only one bucket. Finer partitions, injected projection and compressed JSON keep the small-file problem.
Want more on Partitioning, compression and optimization? The 5 full-length practice sets have 5 more Partitioning, compression and optimization questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →