Redshift query patterns and Spectrum

Lesson 2 of 5 in Analyzing Data.

Athena is billed by what it reads. Redshift is billed by what it runs — a cluster or a serverless workgroup you pay for whether a query is executing or not. That single difference reorders every piece of advice: in Athena the goal is to touch less data, and in Redshift the goal is to make the cluster you are already paying for do more work per second.

Which means performance questions in Redshift are almost always about one of three things, and the exam's job is to tell you which:

Data movement between nodes. A join whose two sides are distributed on different keys has to shuffle one of them across the network before it can match anything. Distribution style and sort keys are how you stop that — and they are a modelling decision made when the table is created, not a tuning knob you turn afterwards.

Queuing. Queries are not slow; they are waiting. Concurrency scaling and workload management address this, and they do nothing whatever for a slow query that is already running.

Reading data that is not in the cluster. Spectrum reaches into S3 and federated query reaches into an operational database. Both are useful; both introduce a boundary where pushdown either happens or quietly does not.

The diagnostic habit to build: before changing anything, establish whether the query spent its time waiting, shuffling, or reading. Those three have three different fixes and no overlap.

A Redshift query is slow. Where did the time go?

Interactive decision tree — outcomes:

Concurrency scaling, on the right queue
Peak-hour queuing with acceptable per-query runtime is the case concurrency scaling was built for: transient clusters absorb the burst, enabled per workload-management queue, with a daily credit that covers most clusters outright. It does nothing for a slow query — a bad join is just as slow on a transient cluster.
Workload management, with query monitoring rules
One runaway query starving everyone is a guardrail problem. Automatic WLM with query priorities separates ETL from ad-hoc; query monitoring rules then watch a running query’s metrics — execution time, rows scanned, nested-loop joins, spill — and log, hop or abort it.
Distribution and sort keys — a modelling change
Redistribution before a join is the classic warehouse cost. Co-locate the join by giving both sides the same distribution key, or replicate a small dimension to every node with DISTSTYLE ALL. Sort keys then reduce the blocks read for range-restricted scans. None of this is a query-time knob; it is why the modelling lesson matters here.
The predicate did not push down
If roughly the whole external table crossed into the cluster, Spectrum did not filter it — usually because the predicate is wrapped in a function, compares incompatible types, or sits in a construct Spectrum cannot evaluate. Rewrite it so the filter is a plain comparison against a column, and confirm with the external-scan row counts that the filtering moved outward.
The lake layout, not the warehouse
Pushdown worked; the scan was simply huge. The fix is in S3, and it is the previous lesson: partition the external table and constrain the partition columns, store Parquet rather than text, and compact small files. Note that partition projection does not help here — Spectrum reads the registered partition metadata.
Federated query used for the wrong volume
Every federated row crosses a database connection, so bulk extraction through federated query overloads the operational database and looks like a slow warehouse. Keep federation for small, fresh lookups; move bulk history with a proper load, and check SVL_FEDERATED_QUERY to see what the remote side was actually asked for.
A materialized view
A predictable, repeated aggregate is what materialized views are for: pre-compute it, refresh it incrementally where Redshift can, and let automatic query rewrite point existing queries at it. The next lesson covers the refresh behaviour, which is where the surprises live.

What to carry out of this lesson

Establish whether the query waited, shuffled, or read before changing anything. Those are three different fixes with no overlap.

Concurrency scaling fixes queuing, never slowness. Query monitoring rules are the guardrail against one runaway query.

Spectrum pushes projection and filter into the Spectrum layer, so only surviving rows enter the cluster — and when a predicate cannot push down you pay for the whole table twice over.

Federated query reaches RDS and Aurora, PostgreSQL and MySQL only, and is for small and current rather than large and historical.

Partition projection does not apply to Spectrum. A projected Athena table read through an external schema uses the registered partition metadata, which may be empty.

Redshift’s result cache never applies to a query touching external tables.

Key terms: Amazon Redshift Spectrum, Redshift federated query, Concurrency scaling, Redshift workload management, Predicate pushdown

Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.