Amazon Redshift

Follow one table from load to query. A batch lands in S3 as many files; `COPY` splits them across slices, and each slice writes compressed column blocks for the rows its distribution style sends there. New rows are appended unsorted until a vacuum, automatic or manual, folds them into sort order. A dashboard query then connects to the leader node, which plans it against table statistics, and WLM decides whether it runs now, waits in a queue, or is sent to a concurrency-scaling cluster. On the compute nodes each slice scans only the blocks its zone maps cannot rule out, joins locally where tables share a DISTKEY or one side is replicated, and moves rows where they do not. The leader assembles the partial results and returns them. The rest of the hub attaches to that path. Spectrum and federated queries widen the scan step beyond the cluster: Spectrum reads S3 in a separate fleet and hands filtered rows back, where the join happens, so file format and partitioning decide what it reads. UNLOAD runs the path in reverse, and on RA3 managed storage sits under the blocks, filling the local cache on demand. The physical design is declared on the tables themselves, and the load is shaped to match it: ```sql -- two large tables distributed on their join column: the join stays on each slice CREATE TABLE orders (order_id BIGINT, customer_id BIGINT, order_date DATE) DISTKEY (order_id) SORTKEY (order_date); CREATE TABLE order_lines (order_id BIGINT, sku VARCHAR(32), amount DECIMAL(12,2), order_date DATE) DISTKEY (order_id) SORTKEY (order_date); -- small dimension replicated to each node: joins to it never move rows CREATE TABLE customers (customer_id BIGINT, region VARCHAR(32)) DISTSTYLE ALL; -- many files under one prefix, split across slices COPY order_lines FROM 's3://example-bucket/order_lines/2026-09-28/' IAM_ROLE DEFAULT FORMAT AS PARQUET; ``` Every choice here has a price. A query joining `order_lines` to another large table on `sku` would redistribute; a handful of huge orders would skew one slice; and date filters prune well only while new rows keep getting sorted in.

Redshift has no release numbers a candidate is expected to quote; AWS patches it continuously. What interviewers ask about is how recommended practice shifted, because many clusters and older answers predate it. This guide assumes RA3 nodes or Serverless, automatic WLM, and tables created with the current defaults. - **Storage model.** DC2 and earlier node types kept data on local disks, so data volume dictated node count. RA3 moved the durable copy into managed storage, and Serverless removed cluster sizing altogether. - **Workload management.** Hand-tuned manual WLM queues with fixed slots and memory gave way to automatic WLM steered by priorities. - **Physical design.** Tables created without an explicit distribution style or sort key now get AUTO, and Redshift can change them over time; vacuum and analyze also run in the background. Interviewers still test the reasoning AUTO applies. - **Upserts.** The staging-table pattern once ended in `DELETE ... USING` plus `INSERT`; a native `MERGE` statement now does the same in one step. When an answer depends on one of these, say which world you are describing.

Redshift sits in AWS's analytics stack and is usually placed against two kinds of neighbour. Among cloud warehouses it competes with Snowflake, Google BigQuery and Databricks SQL. The separating trade-off is control: Redshift exposes nodes, slices and key choices and rewards teams that tune them, while the others manage physical layout and scale compute with less for you to decide. Serverless narrows that gap without removing the key choices. Inside AWS it is weighed against Amazon Athena, which queries the same S3 files through the same Glue Data Catalog with no cluster at all. Athena fits occasional, ad-hoc exploration of a lake; Spectrum fits when those files must be joined with data already in Redshift. Around the warehouse sit S3 as the lake, Glue for the catalog and ETL, Kinesis and Amazon MSK for streaming ingestion, and tools such as dbt that run SQL models inside the warehouse. Operational data stays in RDS or Aurora, reached through federated queries or replicated in.