skip to content

How do you decide whether a dataset stays external in S3 or gets loaded into Redshift managed storage?

level: principalimportance: should knowfreq 45%

answer

  1. the deciding number is not the data size
  2. one meter charges to hold, the other to read
  3. only one side has sort keys and distribution
  4. the same files can serve more than one engine
  5. most real answers are a boundary, not a choice

basics

~20 s

Drive it from query volume, not data volume. Data queried repeatedly by dashboards belongs in local Redshift storage where sort keys, distribution and materialized views apply; rarely-scanned history and data other engines must also read belongs in S3 behind Spectrum.

solid answer

~50 s

The costs are shaped differently, and that decides it. Local Redshift Managed Storage is billed by volume held over time and queried for free once the cluster is paid for; Spectrum on a provisioned cluster adds a charge **per query, per byte scanned**. So a 50 TB archive touched twice a month is obviously external, while a 2 TB table hit by a dashboard every five minutes is obviously local — even though the archive is far bigger. Performance points the same way: only local tables get sort keys, distribution keys, zone maps and column encodings you control, so interactive latency lives locally. External wins on **shared access** — the same S3 files stay readable by Athena, EMR and Spark, so you keep one copy — and on avoiding pipeline work for data nobody has asked for yet. The mature answer is usually tiered: hot months local, deep history external, joined through a view, with a job that ages partitions out.

code

sql · 9 lines
sql
-- hot window local, deep history external, one view for consumers
CREATE VIEW events_all AS
SELECT event_date, user_id, event_type, amount
FROM   public.events_hot            -- SORTKEY(event_date), DISTKEY(user_id)
WHERE  event_date >= DATE '2026-06-01'
UNION ALL
SELECT event_date, user_id, event_type, amount
FROM   spectrum.events              -- partitioned Parquet in S3
WHERE  event_date <  DATE '2026-06-01';

go deeper

for a junior

Recall the basic split: data queried often should live inside Redshift, while rarely-touched history can stay in S3 and be read through Spectrum when needed.

for a middle

Explain the two cost meters — storage held per month versus bytes scanned per query — and the physical-design levers such as sort keys and distribution that only local tables have.

for a senior

Show you would implement the tiered design: measure how far back queries reach, set the boundary there, and automate unload, partition registration and local cleanup behind one union view.

for a principal

Own the whole trade: cost attribution across teams, which engines must read the same files, the reliability difference between loudly failing loads and silently wrong external reads, and when to revisit the boundary.

## Reframe the question Teams instinctively ask "is this dataset too big to load?" That is the wrong axis. The right one is **how often will it be read, by whom, and how fast must the answer come back?** Storage volume is nearly free on both sides; repeated scanning is not. ## The cost asymmetry On a provisioned Redshift cluster there are two very different meters: - **Local (Redshift Managed Storage)**: you pay for the gigabytes held, per month. Queries against it consume the cluster you are already paying for, so an additional query is marginally free. - **External (Spectrum)**: you pay per query for the bytes read out of S3, *plus* the cluster. There is no automatic caching, so the same dashboard query pays every time it runs. Run the arithmetic in the direction that matters: monthly cost of holding the data locally, versus bytes-scanned-per-query multiplied by queries-per-month. A dashboard refreshing every five minutes issues on the order of nine thousand queries a month; if each scans even a few hundred gigabytes, the external option loses badly. Conversely, a compliance archive queried during two audits a year will never justify local residence. (Redshift Serverless meters compute in RPU-seconds rather than node-hours, so if you run Serverless, restate the same comparison in that unit before deciding.) ## The performance asymmetry Local tables get physical-design levers that external tables simply do not have: - a **`SORTKEY`**, giving zone-map skipping on the filter column; - a **`DISTKEY`**, letting large joins run co-located with no redistribution; - **column encodings** chosen by `ANALYZE COMPRESSION`; - **statistics** from `ANALYZE`, so the planner picks sane join orders; - eligibility for the cluster's own caching and for local materialized views. An external table has none of these. Its physical layout is whatever the writing pipeline produced, its joins always require data movement, and its planner input is whatever `numRows` you remembered to set. That is why interactive, low-latency, high-concurrency workloads belong local — the levers that make them fast only exist there. ## What external buys you It is not only a fallback: - **One copy, many engines.** Files in S3 registered in the Glue Data Catalog are readable by Athena, EMR, Glue jobs and Spark, and by other Redshift clusters. Loading creates a Redshift-only copy that must then be kept in sync. - **No pipeline for unloved data.** Raw event streams land in S3 anyway. Registering an external table over them costs a DDL statement; building a curated load costs engineering time forever. - **Schema flexibility.** Column definitions are metadata over files, so adding a column is a catalog change rather than a table rebuild and reload. - **Elastic, off-cluster scan capacity.** A one-off historical backfill runs in the Spectrum fleet rather than saturating your compute nodes. ## The tiered pattern Most mature deployments do not choose; they tier. 1. Keep the recent window — often the last 3 to 13 months — in a **local table** with a sort key on the date column and a distribution key matching its dominant join. 2. Keep everything older as **partitioned Parquet in S3** behind a Spectrum external table. 3. Expose one **view** that `UNION ALL`s the two, so consumers write one query and the split is invisible. 4. Run a scheduled job that unloads ageing local partitions to S3, registers the partition in the catalog, and deletes the local rows. The cut point is set by measurement: look at how far back queries actually reach. If 97% of queries touch the last 90 days, the boundary is around 90 days, not around some storage number. ## Second-order factors worth raising - **Governance and lineage.** Who owns the S3 prefix, the catalog entry, and the retention policy? External data has more owners and therefore more ways to break. - **Silent breakage.** An external table depends on files someone else writes. An upstream change of format, a missing partition registration or a lifecycle rule that expired objects all surface as wrong results, not errors. Local tables fail loudly at load time. - **Concurrency.** External scans do not benefit from the same caching, so a hundred concurrent dashboard users against Spectrum is both slow and expensive. - **Portability.** External keeps data in an open format under your control; loading commits it to one engine's storage. ## How to present the decision Bring numbers, not preferences: queries per month against the dataset, bytes scanned per query, the local storage footprint, and the p95 latency the consumers require. Those four figures usually make the answer obvious, and they make the boundary reviewable later when access patterns change — which they will.

  • What measurements would you bring to this decision rather than opinions?
    Queries per month against the dataset, average bytes scanned per query, the local storage footprint in gigabyte-months, and the p95 latency consumers require. Multiply scan volume by query count and compare it to the storage cost; check the latency requirement against what an S3 scan can deliver. Also chart how far back queries actually reach — that sets the hot/cold boundary.
  • How do you expose a hot-local plus cold-external split without making every consumer aware of it?
    Define one view that `UNION ALL`s the local table and the external table with non-overlapping date ranges, and point all consumers at the view. A scheduled job unloads ageing local partitions to S3, registers the new partition in the catalog, deletes the local rows, and shifts the boundary predicate. Consumers never change their SQL.
  • What can break in an external table that cannot break in a local one?
    Everything you do not control. Upstream changes file format or column order, a partition never gets registered, an S3 lifecycle rule expires objects, or a Glue crawler infers a different type. Each of these produces silently wrong or missing results rather than an error. A local load fails loudly at COPY time, which is a meaningful reliability advantage.

saying these in an interview costs you the question

  • Deciding purely on dataset size rather than query frequency
  • Forgetting that Spectrum charges every time a query runs
  • Expecting sort keys or distribution keys on external tables
  • Assuming external data is always cheaper because S3 is cheap
  • Ignoring that other engines may need to read the same files

context

resolving links…