Redshift Spectrum External Tables: Glue Catalog, Partitioning and Keeping the Scan Bill Down

Redshift Spectrum lets you query files sitting in Amazon S3 from a Redshift warehouse, as if they were tables, without loading them first. It is the oldest and still the most widely used lakehouse path in Redshift: an external schema backed by the AWS Glue Data Catalog, external tables over Parquet or ORC files, and queries that join those external tables straight to your local warehouse tables.

Spectrum is easy to turn on and easy to make expensive. This tutorial covers the setup, the partitioning and file-layout decisions that drive 90% of the cost and latency, how to tell whether a query actually pushed work down to the Spectrum layer, and when you should stop using external tables and load the data instead.

When Spectrum is the right tool

Use Spectrum for:

• Cold or rarely queried history that you have unloaded out of the warehouse but still need to query occasionally.
• Raw landing-zone data you want to inspect or transform in place as the source of an ELT job, rather than staging it into the warehouse twice.
• Large, wide, shared datasets (clickstream, logs, third-party feeds) where only a few columns and a few days are ever touched.

Do not use Spectrum for:

• Tables in the hot path of a dashboard with a sub-second SLA.
• Anything joined row-for-row to a large local fact table on every query.
• Data that needs row-level updates or ACID semantics — that is what Apache Iceberg tables are for, and Redshift reads those through a different path.

Step 1: IAM role and external schema

Spectrum needs a role attached to the cluster or Serverless namespace that can read the S3 prefix and talk to Glue.

{
  "Version": "2012-10-17",
  "Statement": [
    { "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:ListBucket"],
      "Resource": ["arn:aws:s3:::acme-lake", "arn:aws:s3:::acme-lake/*"] },
    { "Effect": "Allow",
      "Action": ["glue:GetDatabase", "glue:GetDatabases", "glue:GetTable",
                 "glue:GetTables", "glue:GetPartition", "glue:GetPartitions"],
      "Resource": "*" }
  ]
}

Then create the external schema. CREATE EXTERNAL DATABASE IF NOT EXISTS makes the Glue database if it is missing:

CREATE EXTERNAL SCHEMA lake
FROM DATA CATALOG
DATABASE 'acme_lake'
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftSpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

Everything in the Glue database is now visible as lake.<table>. If your catalog is governed by Lake Formation, grant there instead of relying on the S3 policy — see the SageMaker Lakehouse and Lake Formation governance guide.

Step 2: Define the external table with real partitions

The single biggest Spectrum lever is partition pruning. Lay the data out with Hive-style partition prefixes:

s3://acme-lake/events/dt=2026-01-14/hour=09/part-0000.parquet

CREATE EXTERNAL TABLE lake.events (
  event_id      VARCHAR(64),
  user_id       BIGINT,
  event_type    VARCHAR(40),
  properties    SUPER,
  event_ts      TIMESTAMP
)
PARTITIONED BY (dt DATE, hour SMALLINT)
STORED AS PARQUET
LOCATION 's3://acme-lake/events/';

Note that partition columns are not repeated in the column list — they come from the path. Register partitions explicitly:

ALTER TABLE lake.events
ADD IF NOT EXISTS PARTITION (dt='2026-01-14', hour=9)
LOCATION 's3://acme-lake/events/dt=2026-01-14/hour=09/';

Doing that by hand does not scale. Three workable options:

1. A Glue crawler on a schedule. Simple, but slow and costly on buckets with millions of prefixes.
2. A small Lambda on S3 events or EventBridge that issues the ADD PARTITION through the Redshift Data API — the pattern in the Data API orchestration post.
3. Partition projection in Glue, where the catalog derives partition values from a range rule instead of listing them. Set table properties like projection.enabled=true, projection.dt.type=date, projection.dt.range=2024-01-01,NOW. Nothing to register, ever.

Verify what Redshift thinks exists:

SELECT schemaname, tablename, values, location
FROM SVV_EXTERNAL_PARTITIONS
WHERE tablename = 'events'
ORDER BY values DESC
LIMIT 20;

Step 3: File layout, because you are billed per byte scanned

Spectrum charges per terabyte scanned (on provisioned clusters; on Serverless the scan work consumes RPUs). The rules that matter:

• Columnar formats only. Parquet or ORC. A CSV or JSON external table reads every byte of every column, every time.
• Target 128 MB–512 MB files. Thousands of tiny files will dominate your runtime with request overhead; a handful of multi-gigabyte files prevents parallelism. Compact the landing zone on a schedule.
• Sort within files on your common filter column so Parquet row-group min/max statistics can skip row groups.
• Compress with Snappy or ZSTD. Gzipped CSV is not splittable; gzipped Parquet is fine but Snappy reads faster.
• Keep the column list narrow in the query. SELECT * on a 200-column external table is the most common way teams blow their Spectrum budget.

Step 4: Confirm the pushdown actually happened

Spectrum can push filters, projections, aggregations and some joins down to the Spectrum fleet, so only a small result comes back to your cluster. Check the plan:

EXPLAIN
SELECT e.event_type, COUNT(*) AS n
FROM lake.events e
WHERE e.dt BETWEEN '2026-01-07' AND '2026-01-14'
  AND e.event_type IN ('checkout', 'purchase')
GROUP BY 1;

You want to see S3 Seq Scan with a Filter applied at the S3 level, plus S3 HashAggregate or S3 Aggregate nodes. If the aggregate only appears above the XN nodes, every row is being dragged into the cluster. The same plan-reading habits from the EXPLAIN walkthrough apply here.

Then check what the query actually cost:

SELECT query, segment, SUM(s3_scanned_bytes) / 1073741824.0 AS gb_scanned,
       SUM(s3_scanned_rows) AS rows_scanned,
       SUM(files) AS files_touched,
       MAX(max_request_duration) / 1000000.0 AS slowest_request_s
FROM SVL_S3QUERY_SUMMARY
WHERE query = pg_last_query_id()
GROUP BY 1, 2;

SVL_S3QUERY_SUMMARY is the file you open after every Spectrum complaint. If files_touched is in the tens of thousands, your compaction is broken. If gb_scanned barely moves when you narrow the date range, your partition pruning is broken — usually because the predicate is on a timestamp column inside the file rather than on the dt partition column.

The predicate trap

-- BAD: no partition pruning, scans everything
WHERE e.event_ts >= '2026-01-07'

-- GOOD: prunes on the partition, then refines
WHERE e.dt >= '2026-01-07'
  AND e.event_ts >= '2026-01-07 00:00:00'

Casting or applying a function to the partition column (WHERE DATE_TRUNC('month', dt) = ...) also defeats pruning. Keep partition predicates plain and literal.

Step 5: Guardrails

Cap the damage before an analyst finds the limit for you:

CREATE EXTERNAL SCHEMA ...;  -- as above

-- WLM / query monitoring rule: abort anything scanning more than 2 TB
-- (set via the parameter group or Serverless usage limits)

Use a query monitoring rule on spectrum_scan_size_mb or spectrum_scan_row_count with an abort action in the queue analysts run in, and set a Redshift Spectrum usage limit in the console to alert or disable at a monthly byte threshold. Pair that with the monitoring runbooks so an overrun pages someone instead of surfacing on the bill.

Step 6: Know when to load instead

A useful rule of thumb: if a dataset is queried more than a few times a day, by more than one team, with joins to local dimensions, the per-scan cost and latency of Spectrum will exceed the storage cost of holding it in RA3 managed storage. Materialize the hot slice and leave the tail external:

CREATE MATERIALIZED VIEW mv_events_recent
AUTO REFRESH NO
AS
SELECT dt, event_type, user_id, COUNT(*) AS n
FROM lake.events
WHERE dt >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY 1, 2, 3;

Materialized views over external tables cannot auto-refresh incrementally, so schedule REFRESH MATERIALIZED VIEW after the daily partition lands. The trade-offs here are the same ones in the materialized view guide and the cost-tuning checklist.

Quick checklist

• External schema on Glue, IAM role scoped to one bucket prefix.
• Hive-style partitions, registered by projection or automation — never by hand.
• Parquet/ORC, 128 MB–512 MB files, Snappy or ZSTD, sorted within file.
• Predicates on partition columns, unwrapped and literal.
• EXPLAIN shows S3 Aggregate; SVL_S3QUERY_SUMMARY confirms bytes and files.
• Query monitoring rule plus a Spectrum usage limit.
• Promote hot slices into managed storage; leave the tail in S3.

Spectrum is a cost-control tool that quietly becomes a cost problem when the lake layout is left to whatever the producing job happened to write. If you want the layout, catalog automation and guardrails designed once and done properly, our Data Integration & Migration and Redshift Performance Optimization teams do exactly this work — get in touch.