Spectrum & External Data

## What Spectrum actually is Amazon Redshift stores its own tables on the cluster's compute nodes (on RA3 node types, in Redshift Managed Storage backed by S3 but owned by Redshift). **Redshift Spectrum** is a separate, AWS-managed fleet of scan workers that sits between your cluster and raw files you keep in your own S3 buckets. When a query touches an external table, the leader node compiles a plan in which the S3 portion of the work — opening files, decoding them, projecting columns, applying filters, and often partial aggregation — is dispatched to that Spectrum fleet. Only the surviving rows stream back to your compute nodes, where joins, window functions and final aggregation happen. The practical consequence: **cold or rarely-queried data never has to be loaded**. There is no `COPY` step, no duplicate copy of the data, no ingest pipeline to babysit, and the same files stay readable by Athena, EMR, Glue jobs or Spark. ## The two halves: metadata and data Spectrum needs two things that live outside Redshift. 1. **Metadata** — the table definition (columns, types, file format, partition columns, S3 location). This lives in the **AWS Glue Data Catalog**, in an Apache Hive metastore, or in a catalog Redshift creates for you. 2. **Data** — the files themselves, in an S3 bucket **in the same AWS Region as the cluster**. Cross-Region is a classic failure. You wire both up with one statement: ```sql CREATE EXTERNAL SCHEMA spectrum FROM DATA CATALOG DATABASE 'lake_db' IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole' CREATE EXTERNAL DATABASE IF NOT EXISTS; ``` `spectrum` is now a schema name inside your Redshift database, but it is a *pointer*: every table you see in it is really a Glue table. The IAM role must be attached to the cluster (or namespace) and must allow the catalog reads (`glue:GetTable`, `glue:GetPartitions` and friends) plus `s3:GetObject` and `s3:ListBucket` on the data prefixes. Least privilege here matters — this role is the only thing standing between a Redshift user and every object the role can read. ## Defining external tables If a Glue crawler already registered the dataset, the tables simply appear. Otherwise you declare one: ```sql CREATE EXTERNAL TABLE spectrum.events ( event_id bigint, user_id bigint, event_type varchar(64), amount decimal(12,2) ) PARTITIONED BY (event_date date) STORED AS PARQUET LOCATION 's3://my-lake/events/'; ``` Supported formats include Parquet, ORC, Avro, JSON and delimited text. Columnar formats (Parquet, ORC) are strongly preferred because Spectrum can then read only the columns the query mentions. A partitioned external table reads **only partitions that are registered in the catalog**. New S3 prefixes are invisible until a crawler or an explicit statement adds them: ```sql ALTER TABLE spectrum.events ADD IF NOT EXISTS PARTITION (event_date='2026-08-01') LOCATION 's3://my-lake/events/event_date=2026-08-01/'; ``` Forgetting this is the single most common Spectrum bug: the query succeeds and quietly returns nothing for the new day. ## What external tables are not An external table is not a Redshift table with a different storage location. It has: - **no distribution style and no sort key** — you cannot co-locate it with a local table for a join, and there are no local zone maps; - **no column compression settings you control** — compression is whatever the files already use; - **no row-level DML** — `UPDATE` and `DELETE` do not apply; - **no automatic statistics** — the planner has no idea how big it is unless you tell it, via `ALTER TABLE spectrum.events SET TABLE PROPERTIES ('numRows'='...')`. And on a provisioned cluster, querying it is **separately billed by bytes scanned from S3**, on top of the cluster you are already paying for. That cost line is why "just leave it external" is a decision, not a default. ## Checking what you have Redshift exposes the catalog through system views: `SVV_EXTERNAL_SCHEMAS`, `SVV_EXTERNAL_TABLES`, `SVV_EXTERNAL_COLUMNS` and `SVV_EXTERNAL_PARTITIONS`. If a query returns no rows, `SVV_EXTERNAL_PARTITIONS` is the first place to look — it tells you exactly which prefixes the catalog believes exist.