RougeWarehouse | Proving a Redshift Resize Before You Commit: Workload Replay with Redshift Test Drive

Every Redshift resize conversation ends in the same stalemate. Someone wants to move from ra3.4xlarge x 8 to Serverless, or from 12 nodes to 6, or off a legacy cluster onto a new one after a migration — and nobody can say what it will do to the 4am ELT window or the Monday morning dashboard storm. Synthetic benchmarks like TPC-DS answer a question you don't have. The only workload that matters is yours.

Redshift Test Drive is the AWS-published tooling for exactly this: it captures the SQL your cluster actually ran, then replays it against one or more candidate configurations and compares the results. This tutorial covers how we run it on client engagements, including the parts that quietly invalidate a test run if you skip them.

What Test Drive actually is

Two utilities, usually run in sequence:

Workload Replay — takes a time window of captured activity from a source warehouse and re-executes it against a target, preserving connection identity, statement order per session and (optionally) original inter-query timing.
Node Config Compare — an orchestration layer around Replay. You describe N candidate configurations in a config file, and it provisions each one from a snapshot, replays the same workload against all of them, and emits comparable per-query and aggregate metrics.

Both are Python tools you run from an EC2 instance or a container; Node Config Compare drives a Step Functions state machine and CloudFormation stacks on your behalf. Nothing runs inside your production cluster except the extraction queries.

Step 1: make sure the source is actually being recorded

Replay is only as good as the capture. Two sources of truth:

Audit logging to S3, enabled on the source cluster or workgroup. The connection log, user log and user activity log are what Test Drive parses. The user activity log is the one that contains query text, and it is off by default — it requires the enable_user_activity_logging parameter set to true in the cluster parameter group (or the equivalent on Serverless).
System tables (SYS_QUERY_HISTORY, STL_*) as a fallback, which is fine for shorter windows but retention is limited to a few days.

-- Confirm the parameter is on
SHOW enable_user_activity_logging;

-- Sanity-check that the window you want is still in system tables
SELECT date_trunc('hour', start_time) AS hr,
       COUNT(*) AS queries,
       COUNT(DISTINCT user_id) AS users
FROM SYS_QUERY_HISTORY
WHERE start_time > DATEADD(day, -3, GETDATE())
GROUP BY 1
ORDER BY 1;

If you turn user activity logging on today, you cannot replay last month. Plan the capture window before you plan the test. On most engagements we ask the client to enable it, then wait for a full business cycle — one weekday peak plus one month-end close if the warehouse has one.

Step 2: pick a window that represents something

Choose the window by intent, not by convenience:

Decision you're making	Window to capture
Can fewer/smaller nodes survive peak BI?	Busiest 2–3 hours of dashboard concurrency
Will the nightly ELT still finish in time?	Full batch window, edge to edge
Should we move to Serverless?	Both, replayed separately
Post-migration parity vs the old platform	The mix that the business signed off on

Resist the urge to replay 24 hours "to be safe". Long windows cost more, take longer to iterate on, and dilute the signal you care about — a batch window that regresses 40% disappears into a daily average.

Step 3: extract the workload

git clone https://github.com/aws/redshift-test-drive.git
cd redshift-test-drive
pip install -r requirements.txt

Extraction is driven by a YAML config. The essentials:

# config/extract.yaml (abridged)
source_cluster_endpoint: "prod-dw.abc123.us-east-1.redshift.amazonaws.com:5439/warehouse"
region: "us-east-1"
redshift_user: "replay_admin"
start_time: "2026-03-10T01:00:00+00:00"
end_time:   "2026-03-10T05:30:00+00:00"
log_location: "s3://acme-redshift-audit/prod-dw/"
workload_location: "s3://acme-testdrive/workloads/nightly-elt-2026-03-10"
unload_system_table_queries: ""

python3 -m core.extract config/extract.yaml

The output in workload_location is a directory of per-connection SQL plus a manifest. Read it before you replay. This is the point at which you discover that 60% of your "workload" is a monitoring tool polling SVV_TABLE_INFO every fifteen seconds, or that one analyst's runaway notebook accounts for half the query count. Filter those out; they will otherwise dominate your comparison metrics.

Step 4: get the target to a matching starting state

This is the step people skip, and it is the step that makes replay results meaningless when skipped.

Restore the target from a snapshot taken at (or just before) the start of the capture window. Replay the write statements against a warehouse whose data is a month newer and your MERGE statements will touch the wrong row counts.
Match the sort/dist state. If the source had AUTO table optimization churning, note it; two targets restored from the same snapshot start identical, which is what you want for A/B comparison.
Decide on write replay. Test Drive can replay COPY/INSERT/UPDATE/DELETE. For an ELT-window test you want writes on, so isolate the target in a non-production account with its own S3 copies of the source files. For a read-only BI concurrency test, exclude writes entirely.
Recreate users. Replay authenticates as the original users where it can; missing roles produce permission errors that look like performance data but aren't.

Step 5: describe the candidates and run Node Config Compare

# node_config_compare (abridged)
SNAPSHOT_ID: "rs:prod-dw-2026-03-10-01-00"
SNAPSHOT_ACCOUNT_ID: "111111111111"
WORKLOAD_LOCATION: "s3://acme-testdrive/workloads/nightly-elt-2026-03-10"
CONFIGURATIONS:
  - TYPE: "Provisioned"
    NODE_TYPE: "ra3.4xlarge"
    NUMBER_OF_NODES: 8      # incumbent, the control
  - TYPE: "Provisioned"
    NODE_TYPE: "ra3.4xlarge"
    NUMBER_OF_NODES: 4
  - TYPE: "Provisioned"
    NODE_TYPE: "ra3.16xlarge"
    NUMBER_OF_NODES: 2
  - TYPE: "Serverless"
    BASE_RPU: 64
  - TYPE: "Serverless"
    BASE_RPU: 128

Always include the current configuration as a control arm. Absolute replay numbers differ from production (no cache warmth, no competing traffic, different concurrency scaling behaviour); the ratio between the control and each candidate is the number you can defend in a meeting.

Launch it, then let it run — each arm provisions, restores, replays and tears down:

python3 -m core.node_config_compare config/node_config_compare.yaml

Step 6: read the results honestly

Test Drive writes per-query and aggregate metrics to S3 and builds comparison tables you can point QuickSight at. The four numbers we actually report to clients:

p50 and p95 elapsed time per query class, not overall. Batch and interactive queries have opposite failure modes; averaging them hides both.
Total workload wall-clock, which is the only metric that answers "does the ELT still finish by 6am".
Error and timeout counts per arm. A candidate that is 30% faster because 12% of its queries failed is not faster. Diff the failures against the control arm before you look at any timing.
Queue time vs execution time. A smaller cluster usually regresses on queueing first, which is fixable with WLM and concurrency scaling; regressing on execution time means you genuinely removed capacity you needed.

For Serverless arms, pair the timing with billed RPU-seconds. A 128-RPU base that finishes in half the time of 64 RPU costs roughly the same and is unambiguously better; one that finishes 20% faster for double the RPU-seconds is a deliberate trade, not a win.

Common ways a replay lies to you

Result caching. Repeated identical queries in the window can return from cache on one arm and not another. Disable it for the replay (enable_result_cache_for_session = off) if you want execution comparisons, or leave it on deliberately if you're modelling real user behaviour — just don't mix the two across arms.
Cold blocks. RA3 and Serverless both fetch from managed storage on first touch. Give each arm the same warm-up treatment, or none.
Unpreserved timing. Replaying queries back-to-back instead of at original intervals turns a comfortable workload into a concurrency stress test. That's a valid test — label it as one.
Serverless auto-scaling noise. Serverless arms scale on demand, so a single run can be unrepresentative. Repeat the Serverless arms at least twice.
Data drift between arms. Restore every arm from the same snapshot ID. Never restore arm 3 from a fresher snapshot because the old one aged out mid-project.

Cost and duration

You pay for every arm for the duration of provisioning, restore and replay. A four-arm comparison of a three-hour batch window typically lands somewhere in the low hundreds of dollars and a working day of elapsed time. Against the cost of an annual reserved-instance commitment on the wrong node type, or a Serverless migration that has to be rolled back, that is rounding error — and it is the cheapest way we know to turn a resize argument into a decision.

Where this fits in a project

We run Test Drive at three points: before a right-sizing change, before an RA3-to-Serverless move, and immediately after a legacy-platform migration to prove parity on the customer's own query mix rather than on a vendor benchmark. In all three cases the deliverable is the same — a comparison table with a control arm, a recommended configuration, and the specific queries that regressed so someone can decide whether they matter.

If you're staring at a resize decision and would rather not guess, get in touch. Our senior Redshift consultants do this as a fixed-scope engagement, and the output is yours whether you hire us for the migration or not.