A Redshift Spectrum query is billed per terabyte scanned from S3 — what drives that number up?

## Where the charge comes from On a provisioned Amazon Redshift cluster, Spectrum is a **separate line item from the cluster itself**. You already pay for the nodes by the hour; Spectrum adds a charge proportional to the volume of data its scan fleet reads out of your S3 buckets for each query. Bytes are counted per query, rounded up, and a query that runs a thousand times pays a thousand times — there is no automatic caching of external scans. (Redshift Serverless bills compute in RPU-seconds rather than by the node-hour, so treat the reasoning below as the provisioned-cluster model and confirm current pricing for whichever you run.) The number to reason about is therefore always the same one: **how many bytes must Spectrum physically read from S3 to answer this query?** Every optimisation is a way of making that number smaller. ## Lever 1 — file format decides whether columns can be skipped Parquet and ORC store data column by column within row groups or stripes. When a query references three of forty columns, Spectrum reads three column chunks per row group and ignores the rest. Delimited text and JSON store data row by row: to reach one field the reader must decode the whole record, so **every column of every file touched is scanned and billed**. This is usually the single largest factor. Converting a raw CSV landing zone to Parquet routinely collapses a query's scanned bytes by an order of magnitude, and the effect grows with table width. ## Lever 2 — partition pruning eliminates whole prefixes A partitioned external table records, per partition, which S3 prefix holds its files. A predicate on the partition column lets Redshift discard non-matching partitions in the catalog, before opening a single object: ```sql -- scans one prefix SELECT count(*) FROM spectrum.events WHERE event_date = DATE '2026-08-01'; -- scans every registered partition SELECT count(*) FROM spectrum.events WHERE event_type = 'purchase'; ``` The second query filters on a data column, not a partition column, so Spectrum must read every file to evaluate it. The filter still runs — pushed down to the Spectrum layer, so few rows come back over the network — but the bytes were already scanned and already billed. **Filtering cheaply and scanning cheaply are different things.** ## Lever 3 — projection `SELECT *` reads every column chunk. This bites hardest when the wide select is hidden: a view defined as `SELECT * FROM spectrum.events`, a BI tool that materialises all fields before its own filter, or a `SELECT *` inside a CTE whose outer query needs two columns. Always project explicitly against external tables, and check what your BI layer actually sends. ## Lever 4 — compression You are billed on bytes read from S3, which are the *compressed* bytes. Snappy or zstd inside Parquet therefore reduces both the bill and the wall-clock time. Note the interaction with format: gzip-compressed CSV is small on disk but still row-based, so it scans every column — and gzip is not splittable, so it also kills parallelism. ## The multiplier nobody costs: repetition A single 4 TB scan is a one-off. The same query wired into a dashboard that refreshes every five minutes is a recurring charge that dwarfs the cluster. Two standard answers: - **Materialise it.** Redshift supports materialized views over external tables; the refresh scans S3 once and every dashboard query then reads local Redshift storage. - **Load the hot slice.** If the last 90 days serve 95% of queries, `COPY` those into a local table and leave only the deep history external. ## Diagnosing it After a query runs, `SVL_S3QUERY_SUMMARY` reports what the Spectrum layer actually did: `s3_scanned_bytes`, `s3_scanned_rows`, the rows and bytes returned to the cluster, and how many files and splits were involved. `SVL_S3PARTITION` reports `total_partitions` versus `qualified_partitions` — the direct measure of whether pruning worked. If scanned bytes are large but returned bytes are tiny, you are paying to read data you immediately throw away, and the fix is layout (partitioning, format), not the query's filter. ## The month-over-month surprise When the same query scans ten times more than it did last month, the causes are almost always: more partitions accumulated and the query has no partition predicate; someone widened the table so `SELECT *` now costs more; an upstream job started writing CSV instead of Parquet; or a Glue crawler registered a second copy of the data under the same table.