Amazon Redshift for Data Engineers

Architecture, distribution and sort keys, COPY/UNLOAD, MERGE and SCD2, Spectrum, zero-ETL and streaming ingestion, performance, security and the Data API.

1. Redshift Architecture

Amazon Redshift is a massively parallel (MPP), columnar data warehouse. A leader node parses and plans queries; compute nodes, each split into slices, store data and execute in parallel. RA3 nodes separate compute from Redshift Managed Storage on S3, and Redshift Serverless removes cluster management entirely (billed in RPU-hours, scales automatically).

2. Table Design: Distribution & Sort Keys

CREATE TABLE gold.fact_transactions (
  txn_id        VARCHAR(40)    NOT NULL,
  account_key   BIGINT         NOT NULL ENCODE az64,
  merchant_key  INT            ENCODE az64,
  txn_date      DATE           NOT NULL ENCODE az64,
  channel       VARCHAR(16)    ENCODE zstd,
  amount        DECIMAL(18,2)  ENCODE az64
)
DISTSTYLE KEY
DISTKEY (account_key)               -- co-locate with dim_account for joins
COMPOUND SORTKEY (txn_date, account_key);   -- date range filters skip blocks

CREATE TABLE gold.dim_channel (channel_key SMALLINT, channel VARCHAR(16))
DISTSTYLE ALL;                      -- small dimension copied to every node

• Sort keys let Redshift skip blocks using zone maps (min/max per 1 MB block). Lead with the column most often used in range filters, usually a date. SORTKEY AUTO lets Redshift decide.
• Compression encodings: AZ64 for numbers and dates, ZSTD for strings, or ENCODE AUTO.

3. Loading & Unloading Data

-- fastest bulk path: parallel COPY from S3
COPY gold.fact_transactions
FROM 's3://acme-lake-prod/gold/fact_transactions/dt=2026-10-26/'
IAM_ROLE 'arn:aws:iam::111122223333:role/redshift-s3-read'
FORMAT AS PARQUET;

-- CSV with options and a manifest listing exact files
COPY staging.customers
FROM 's3://acme-lake-prod/exports/customers/manifest.json'
IAM_ROLE default
CSV IGNOREHEADER 1 GZIP DATEFORMAT 'auto' TIMEFORMAT 'auto'
MANIFEST
MAXERROR 0;

-- export query results back to the lake
UNLOAD ('SELECT * FROM gold.daily_sales WHERE sale_date = ''2026-10-26''')
TO 's3://acme-exports/daily_sales/2026-10-26/'
IAM_ROLE default
FORMAT AS PARQUET
PARTITION BY (region);

• Load many files of similar size (a multiple of the slice count) so every slice works in parallel. A single huge file loads on one slice.
• Check failures in SYS_LOAD_ERROR_DETAIL (or STL_LOAD_ERRORS on provisioned clusters).
• Auto-copy (COPY … JOB CREATE) continuously loads new files that land under an S3 prefix.
• Avoid single-row INSERTs for bulk data: each is a separate small transaction and very slow.

4. Upserts & SCD Type 2 with MERGE

-- upsert from a staging table loaded by COPY
MERGE INTO gold.dim_customer
USING staging.customers s
ON gold.dim_customer.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET name = s.name, city = s.city, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, name, city, updated_at)
                      VALUES (s.customer_id, s.name, s.city, s.updated_at);

-- SCD Type 2 in one transaction: close changed rows, then insert new versions
BEGIN;

UPDATE gold.dim_customer_scd2 d
SET is_current = FALSE, effective_to = CURRENT_DATE - 1
FROM staging.customers s
WHERE d.customer_id = s.customer_id
  AND d.is_current
  AND (d.city <> s.city OR d.segment <> s.segment);

INSERT INTO gold.dim_customer_scd2 (customer_id, city, segment, effective_from, effective_to, is_current)
SELECT s.customer_id, s.city, s.segment, CURRENT_DATE, DATE '9999-12-31', TRUE
FROM staging.customers s
LEFT JOIN gold.dim_customer_scd2 d
  ON d.customer_id = s.customer_id AND d.is_current
WHERE d.customer_id IS NULL;            -- new customers and the ones just closed

COMMIT;

5. Spectrum, Zero-ETL & Streaming Ingestion

-- query S3 data registered in the Glue Catalog without loading it
CREATE EXTERNAL SCHEMA lake
FROM DATA CATALOG DATABASE 'silver'
IAM_ROLE 'arn:aws:iam::111122223333:role/redshift-spectrum'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

SELECT c.segment, SUM(t.amount)
FROM lake.transactions t                 -- in S3
JOIN gold.dim_customer c ON c.customer_id = t.account_id   -- in Redshift
WHERE t.dt = '2026-10-26'
GROUP BY 1;

-- streaming ingestion: a materialized view over a Kinesis stream
CREATE EXTERNAL SCHEMA kds FROM KINESIS
IAM_ROLE 'arn:aws:iam::111122223333:role/redshift-streaming';

CREATE MATERIALIZED VIEW rt.txn_events AUTO REFRESH YES AS
SELECT approximate_arrival_timestamp,
       JSON_PARSE(kinesis_data) AS payload
FROM kds."txn-events"
WHERE CAN_JSON_PARSE(kinesis_data);

• Zero-ETL integrations replicate Aurora, RDS, DynamoDB and some SaaS sources into Redshift continuously, without building pipelines.
• SUPER columns store semi-structured JSON; query with PartiQL dot and bracket notation (payload.customer.id).
• Data sharing gives other clusters, workgroups or accounts live, read-only access without copying.

6. Performance & Maintenance

7. Security & Access from Python

• Encryption at rest with KMS; TLS in transit; deploy in private subnets.
• IAM roles for COPY/UNLOAD/Spectrum (no keys in SQL); federated users via IAM Identity Center.
• Fine-grained control: column-level GRANTs, row-level security policies, dynamic data masking, and Lake Formation for Spectrum tables.
• Audit logging to CloudWatch or S3 (connections, user activity).

# Redshift Data API: no drivers, no persistent connections (good for Lambda / Step Functions)
import time, boto3

rsd = boto3.client("redshift-data")
resp = rsd.execute_statement(
    WorkgroupName="analytics",  # Serverless; use ClusterIdentifier for provisioned
    Database="dev",
    Sql="SELECT channel, SUM(amount) FROM gold.fact_transactions WHERE txn_date = :d GROUP BY 1",
    Parameters=[{"name": "d", "value": "2026-10-26"}],
)
while rsd.describe_statement(Id=resp["Id"])["Status"] not in ("FINISHED", "FAILED", "ABORTED"):
    time.sleep(1)
rows = rsd.get_statement_result(Id=resp["Id"])["Records"]

8. Common Interview Questions