A partitioned Redshift Spectrum external table still scans every file — how do you diagnose it?

## First, prove it Guessing wastes a lot of money on a per-terabyte-scanned service. Redshift records exactly what the Spectrum layer did: ```sql SELECT query, total_partitions, qualified_partitions, assignments FROM svl_s3partition WHERE query = pg_last_query_id(); ``` `total_partitions` is how many partitions the table has registered; `qualified_partitions` is how many survived elimination. Equal values mean **no pruning at all**. Pair it with `SVL_S3QUERY_SUMMARY` (`files`, `s3_scanned_bytes`, `s3query_returned_bytes`) to see the volume you paid for versus the volume you used. `EXPLAIN` also helps: the `S3 Seq Scan` node shows the filters Redshift intends to push down. ## Cause 1 — the predicate is on a data column This is the most common and the most invisible. The table is partitioned by `event_date`, and the query filters on `event_type`. The filter *works* — Spectrum evaluates it in its own layer and only matching rows cross the network, so the query looks efficient from the cluster's perspective. But every registered partition was opened and read to evaluate it. Partition elimination is a **catalog-level** decision made from partition column values; a data-column predicate cannot participate. ## Cause 2 — a function or cast hides the partition column ```sql -- no pruning: the column is inside a function WHERE substring(event_date_str, 1, 7) = '2026-08' -- no pruning: cast on the left side WHERE CAST(event_date AS varchar) LIKE '2026-08%' -- prunes WHERE event_date >= DATE '2026-08-01' AND event_date < DATE '2026-09-01' ``` The rule is the familiar sargability rule from any database: keep the partition column bare on one side of the comparison and put every transformation on the literal side. ## Cause 3 — type mismatch between the catalog and the query Glue crawlers frequently infer partition columns as `string`. If the external table declares `PARTITIONED BY (event_date varchar(10))` and the query compares it to a `date` literal, an implicit conversion is applied to the column and pruning can be lost. Declare the partition column with the type you will actually query with, or compare with a matching string literal. ## Cause 4 — the restriction only exists through a join ```sql SELECT ... FROM spectrum.events e JOIN dim_calendar c ON e.event_date = c.d WHERE c.is_month_end; ``` The set of dates is not known when partitions are eliminated. Do not rely on it. Resolve the dates in a separate step and inline them as literals, or generate an explicit `IN` list or `BETWEEN` range on the partition column. ## Cause 5 — the partitions are not registered the way you think Run `SELECT * FROM svv_external_partitions WHERE tablename = 'events';`. Two failure shapes appear here. Either partitions are **missing** — new prefixes landed and no crawler or `ALTER TABLE ... ADD PARTITION` ran, so the query silently returns nothing for recent days — or a partition's `LOCATION` points at a prefix that also contains other partitions' files, so "pruning" a partition still reads their objects. Partition locations must be disjoint. ## Cause 6 — over-partitioning Pruning is not free. A table partitioned by hour and by tenant can accumulate hundreds of thousands of catalog entries. Redshift must fetch and filter that partition list before the scan starts, and the resulting files are tiny — so per-file open and request overhead dominates. If `SVL_S3PARTITION` shows a huge `total_partitions` and `SVL_S3QUERY_SUMMARY` shows thousands of small files per query, the answer is coarser partitioning plus a compaction job, not more partitions. ## The layered fix 1. Partition on the column queries actually restrict — usually the event date — at a granularity that yields substantial files per partition. 2. Keep that column bare in predicates and typed consistently with the literals. 3. For the *second* common filter, rely on columnar format so those columns are skipped, and on sorted-within-file data so row groups are skippable — not on adding another partition level. 4. Register partitions as part of the pipeline that writes the files, so the catalog never lags the data. 5. Re-measure `qualified_partitions` after each change. It is the only number that proves the fix.