Amazon Redshift is AWS's columnar, massively parallel SQL data warehouse. It speaks a PostgreSQL-derived dialect, stores each column separately in 1 MB disk blocks, and splits every table across compute slices so that a scan or aggregate runs on all slices at once. It is built for large scans, joins and aggregates over data that was loaded in bulk. It is not built for single-row lookups or high-rate single-row writes, and treating it like an application database is the most common way to get a bad first month.

The mental model the docs bury: a Redshift query is fast when each slice can answer its part of the question without asking another slice for data. Everything you design in a table definition, the distribution style and the sort key, exists to make that true. The engine will run a badly laid out join, but it will do so by copying rows between nodes first, and that copying is where your runtime and your RPU-hours go.

There are two ways to run it. A provisioned cluster is a set of RA3 nodes you size and pay for by the hour. Redshift Serverless gives you a namespace and a workgroup. The namespace holds the data side: databases, users, roles, permissions and encryption keys. The workgroup holds the compute side: RPU capacity, VPC, subnets, security groups and the endpoint. You size the workgroup with a base capacity measured in Redshift Processing Units (RPUs), and it scales up when a workload needs more. In both modes data lives in Redshift managed storage, billed separately from compute.

Two table-design primitives carry most of the weight.

Distribution style decides which slice stores which row. The styles are AUTO, EVEN, KEY and ALL. EVEN spreads rows round-robin. KEY places rows by the hash of one column, the DISTKEY, so rows that share a key value sit together. ALL copies the whole table to every node. The docs recommend distributing a fact table and its largest joined dimension on their common column, noting that a fact table can have only one distribution key, and making small dimensions ALL when they cannot be collocated. They also warn that ALL multiplies storage and increases load time. If you specify nothing you get AUTO, where Redshift uses automatic table optimization to pick and change the style as the table grows and as it observes your queries.

Sort keys decide the on-disk order of rows. Redshift keeps the minimum and maximum value of each 1 MB block in metadata, called zone maps, and uses them to skip blocks that cannot match a range predicate. The documentation's example is five years of data sorted by date queried for one month, where up to 98 percent of blocks can be skipped. Sort keys can be compound (prefix-weighted, the default for manual keys) or interleaved (equal weight, up to 8 columns, with a slower VACUUM REINDEX). The current recommendation on the docs page is to create tables with SORTKEY AUTO and let automatic table optimization decide.

What AWS operates: hardware, patching, automatic table maintenance, scaling in Serverless, snapshots. What you operate: the schema, the distribution and sort choices where you override AUTO, your load process, your workload isolation, and your cost caps.

Pick Redshift when the same large joins run all day and you want control over how the data is laid out. Pick Athena when the data already sits in S3 and queries are occasional. Pick Aurora when the workload is transactional and the analytics are small. If you are already deep in Athena, the earlier deep dive on it in this newsletter covers that side; this article stays with the warehouse.

The pricing dimensions, from the Redshift pricing page for US East (N. Virginia):

Serverless compute is $0.375 per RPU-hour, billed per second with a 60-second minimum charge, and there is no charge for startup time. A 4-RPU workgroup therefore runs at $1.50 per hour while active. Provisioned RA3 nodes are billed per node-hour; the pricing page I could retrieve lists ra3.4xlarge at $3.26 per hour on demand, and I did not get prices for the other node sizes, so check the page for the node you need. Managed storage is $0.024 per GB-month. Redshift Spectrum, which queries S3 data from a provisioned cluster, is $5.00 per TB scanned. Concurrency scaling on provisioned clusters accrues up to one free hour of credit per day, with extra usage billed per second at the on-demand rate. Eligible new Serverless users get a $300 credit that expires after 90 days.

The one that surprises people is the 60-second minimum. A dashboard that fires fifty one-second queries is billed as fifty minutes of compute, not fifty seconds. The second surprise is that scaling up does not scale back down on its own: the AWS blog on 4-RPU workgroups says that after scaling above 4 RPUs the warehouse stays at the higher level for consistent performance. Set a maximum capacity and a usage limit (daily, weekly or monthly RPU-hour caps) before you hand the endpoint to a BI tool.

From the Redshift quotas page:

The 4-RPU option is documented as designed for lighter workloads; the blog notes that complex queries and high-concurrency or wide-table workloads may need more, and cites benchmarks where 32 RPUs significantly improve complex query performance. Treat 4 RPUs as the tutorial size and the start of a ramp, not a production default.

The deliverable: a Serverless warehouse at 4 RPUs, loaded with a synthetic orders fact table and customers dimension from S3, then the same join planned under three layouts. You will watch the EXPLAIN operator change from a broadcast to a collocated join.

A note on honesty: I wrote this code against the current AWS CLI reference, the Data API documentation and the Redshift Database Developer Guide, and I did not execute it for this article. The EXPLAIN operators are documented behavior; the exact cost numbers you see will differ from the documentation's example, and I make no claim about specific output values.

The identity running the script needs, at minimum: redshift-serverless:CreateNamespace, CreateWorkgroup, GetNamespace, GetWorkgroup, DeleteWorkgroup, DeleteNamespace; redshift-data:ExecuteStatement, DescribeStatement, GetStatementResult; redshift-serverless:GetCredentials if you authenticate with IAM; secretsmanager:GetSecretValue on the admin secret if you use the managed password; iam:CreateRole, AttachRolePolicy, PassRole for the load role; and s3:CreateBucket, PutObject, DeleteObject on your tutorial bucket.

Create the role Redshift uses to read the data. Its trust policy lets the Redshift service assume it:

AmazonS3ReadOnlyAccess is broad. In anything real, replace it with a policy scoped to your bucket.

The --manage-admin-password flag has Redshift keep the admin credentials in Secrets Manager, so there is no password in your shell history. The --max-capacity 8 caps how far the workgroup can scale while you experiment. Wait until the workgroup status is AVAILABLE:

Grab the secret ARN for the Data API calls. Confirm the field name in your own get-namespace output, since I am relying on the documented response shape:

The script writes 4 gzip files of orders and 1 file of customers, then uploads them. Splitting the fact table into several files lets COPY load slices in parallel; the loading best practices recommend a file count that is a multiple of the slice count and files of roughly 1 MB to 1 GB compressed, and a tutorial-sized load will be well under that, so do not read anything into its timing.

A small helper submits a statement, polls until it finishes, and returns rows. The statuses to handle are FINISHED, FAILED and ABORTED. Statements are asynchronous, which is why the poll loop exists.

Create the tables with three layouts. Version A uses EVEN on both, so no table is placed by the join key. Version B makes both KEY on customer_id, the join column. Version C keeps orders on KEY and turns the small customers table into ALL. Orders also gets a sort key on order_date in versions B and C.

The COPY form follows the documented parameters: IAM_ROLE default uses the namespace default role you set at creation, CSV and GZIP describe the files, and REGION names the bucket Region.

Now plan the same join three ways and print the join operator lines.

What to expect, from the documentation's description of redistribution operators. For EVEN/EVEN, neither table is placed by the join key, so the plan shows a redistribution of the inner table: DS_BCAST_INNER (a copy of the whole inner table broadcast to every node) or DS_DIST_BOTH (both tables redistributed). The docs call both "not good". For KEY/KEY on customer_id the plan should show DS_DIST_NONE, meaning matching rows already share a slice. For KEY/ALL the plan should show DS_DIST_ALL_NONE, because the inner table is on every node.

The documentation's own example is instructive: changing the dimension tables to DISTSTYLE ALL turned DS_BCAST_INNER into DS_DIST_ALL_NONE and dropped the plan's total cost from 3272334142.59 to 14142.59. Your numbers will differ, but the shape of the change is the point. When you compare runtimes, discard the first run of each query, since the docs note it includes compilation time.

You should see diststyle values of EVEN, KEY(customer_id) and ALL, and sortkey1 equal to order_date on orders_key. Note that EXPLAIN will not show you block skipping from the sort key. The sort key helps range predicates such as WHERE order_date BETWEEN ..., and the way to confirm it is to compare scanned data in the query details between orders_even and orders_key on a date-filtered query rather than to trust the plan text.

Finally, try the default. Create a table with no DISTSTYLE and no SORTKEY, load it, and look at diststyle again after some queries have run. With AUTO, Redshift initially treats a small table as ALL, moves to EVEN as it grows, and can change to KEY if a distribution key would help, per the automatic table optimization documentation. For many workloads that is the right answer and the manual layouts above are the exception you reach for when a plan shows a broadcast on a join you care about.

The errors you will actually meet:

Typical causes are a header row you did not skip (add IGNOREHEADER 1), a delimiter mismatch, and a value that overflows its column.
- COPY cannot read the bucket. The default role is missing, not associated with the namespace, or the S3 policy does not cover the prefix. Check that the role ARN appears on the namespace and that the trust policy names redshift.amazonaws.com.
- The Data API call is rejected. Using IAM auth requires redshift-serverless:GetCredentials, and the database user becomes IAM:name. With the managed admin secret, the caller needs secretsmanager:GetSecretValue on that secret.
- Workgroup creation fails. Usually subnets or VPC. Also, if the account has VPC Block Public Access turned on, creating a publicly accessible workgroup is blocked, one more reason the tutorial uses --no-publicly-accessible.
- A table is rejected for too many columns. The 4-RPU base capacity allows up to 100 columns per table. Raise the base capacity.
- A join is slow and the plan shows DS_BCAST_INNER or DS_DIST_BOTH. That is the redistribution you came here to find. Set the DISTKEY of fact and dimension to the join column, or make the small dimension ALL.
- The bill is higher than the queries suggest. Remember the 60-second minimum per query and that capacity scaled up stays up. Set a maximum capacity and a usage limit.

Delete the workgroup first, then the namespace, then the supporting resources, and confirm both lists are empty:

Check the CLI reference for delete-namespace if you want to keep a final snapshot. Managed storage is billed at $0.024 per GB-month, so a few hundred MB of tutorial data costs cents if you forget it, but forgotten compute is what hurts.

Estimated cost of following along: with a 4-RPU base at $0.375 per RPU-hour, one hour of active compute is $1.50. A session of this size is a few dozen short queries, each billed at least 60 seconds, so plan for roughly one to two dollars of compute and cents of storage, and expect it to be covered by the new-user credit if you are eligible. I did not measure the run, so treat that as an estimate.

If your joins run all day and the data is bulk-loaded, Redshift rewards you for choosing a distribution key that matches the join column, and EXPLAIN tells you in one word, DS_DIST_NONE versus DS_BCAST_INNER, whether you did.