How does Amazon Redshift execute a join between a local table and a Spectrum external table?

## Two tiers of execution A query mixing local and external tables runs in two places. **The Spectrum layer** is an AWS-managed scan fleet that reads your S3 objects. Redshift pushes work into it aggressively: column projection, comparison predicates (`=`, `<`, `>`, `BETWEEN`), pattern predicates like `LIKE`, `GROUP BY` clauses, `DISTINCT`, `LIMIT`, and aggregate functions such as `COUNT`, `SUM`, `AVG`, `MIN` and `MAX`. That means the fleet can often return a pre-aggregated result rather than raw rows. **The cluster** — your leader and compute nodes — does everything else. Crucially that includes **all joins**, plus window functions and any aggregation that must combine partial results. ## Why the join costs what it costs A local Redshift table has a distribution style, so its rows sit on a known slice. An external table has none: it is a stream of rows arriving from an outside fleet with no relationship to your slices. To join it to a local table, Redshift must first co-locate matching rows, and it has only two ways: - **Broadcast** (`DS_BCAST_INNER`): send a full copy of the smaller side to every node. Cheap when that side is genuinely small — a dimension of thousands of rows. - **Redistribute** (`DS_DIST_BOTH` / `DS_DIST_INNER`): hash both sides on the join key and shuffle them across the network. Necessary when both sides are large, and expensive. A plan looks roughly like this: ```text XN Hash Join DS_BCAST_INNER -> XN S3 Query Scan events -> S3 HashAggregate -> S3 Seq Scan spectrum.events location:"s3://my-lake/events" format:PARQUET -> XN Hash -> XN Seq Scan on dim_user ``` Read it bottom-up on the external branch: scan S3, aggregate in the Spectrum layer, hand the result to the cluster, join. ## The statistics problem The planner has no statistics for an external table. Without them it may guess badly and broadcast a 4-billion-row external side across every node — the classic Spectrum blow-up. Redshift lets you tell it: ```sql ALTER TABLE spectrum.events SET TABLE PROPERTIES ('numRows'='4200000000'); ``` Setting a realistic `numRows` (and refreshing it as the table grows) is one of the highest-leverage things you can do for mixed joins, because it drives join order and distribution choice. ## Shrink the external side first The governing principle: **do as much as possible in the Spectrum layer, and let as little as possible cross to the cluster.** Concretely: - Always put a partition predicate on the external table so fewer files are opened. - Project only the columns the join and the output need. - Pre-aggregate in a subquery over the external table so the join operates on a summary, not on raw events. If the query ultimately produces daily totals per user, aggregate to that grain in the external scan and join the small result to the dimension. - Keep the local dimension small enough to broadcast, or filter it before the join. The anti-pattern is `SELECT * FROM spectrum.big_table b JOIN local_dim d ON ...` with the only selective filter sitting on `d`. The filter lives on the wrong side: Spectrum cannot use it, so the entire external table is read and streamed. ## When to stop joining externally If the same external-to-local join runs repeatedly, stop paying for it repeatedly. Options, in escalating order: 1. A **materialized view over the external table**, refreshed on a schedule, so queries hit local storage. 2. `COPY` the hot partitions into a **local table** with a `DISTKEY` matching the join column and a `SORTKEY` on the filter column — now the join is co-located and there is no S3 scan at all. 3. Keep only cold history external, joined rarely, in a `UNION ALL` view with the local hot table. ## What to measure `SVL_S3QUERY_SUMMARY` shows `s3_scanned_bytes` versus `s3query_returned_bytes` — the ratio tells you how much filtering the Spectrum layer actually did. If returned bytes are large, the external side was not shrunk and the network transfer plus redistribution is your real cost. `EXPLAIN` tells you which side is being broadcast; if it is the external one, your `numRows` property is probably missing or wrong.