Amazon Redshift Data Warehouse: Architecture and Costs

At three in the morning on a Sunday, a typical Amazon Redshift data warehouse is running at single digit CPU utilization and billing at exactly the same rate it does at ten on a Tuesday morning. That sentence is the whole cost story of provisioned Redshift compressed into one line: you rent nodes by the hour, and the hours pass whether or not anyone runs a query.

Everything else in this guide follows from that fact and from its counterpart, which is that Redshift Serverless changes the unit of billing but does not change the physics underneath. If you understand what the leader node does, how data lands on slices, why an unsorted table gets slow, and where the meter actually spins, you can size a cluster in an afternoon instead of arguing about it for a quarter.

What an Amazon Redshift data warehouse is made of: leader nodes, compute nodes, and slices

Redshift architecture is a shared-nothing MPP design wearing a Postgres-compatible front end. Three pieces matter.

The leader node is the only thing your client talks to. It parses SQL, builds and optimizes the plan, compiles the plan into C++ code segments, ships those segments to the compute nodes, and assembles the final result. It also handles all catalog and system table queries by itself. Two practical consequences: first, on any cluster with more than one node you are not billed separately for the leader, it comes with the cluster. Second, work that only the leader can do does not parallelize, so a query returning a million rows to a BI tool bottlenecks in a place that adding nodes will never fix.

Compute nodes hold the data and do the scanning, filtering, joining and aggregating. Each one is divided into slices, and the slice is the real unit of parallelism. Every slice gets a share of the node's memory and disk and processes its own portion of every table. Slice count is fixed by node type, from two on the smaller RA3 sizes up to sixteen on the largest. A cluster of four nodes with four slices each executes across sixteen parallel workers, which is why the number of files you split a load into, and the way rows spread across slices, matter more than most teams expect.

Managed storage is the newer half of the story. On the RA3 family, storage is decoupled from compute: hot blocks are cached on local SSD, everything else lives in an S3-backed layer, and you are billed for what you store per GB-month independently of how many nodes you run. On the older DC2 family, storage is local and finite, so you add nodes to add disk even when you do not need the CPU. If you are still on DC2 and your reason is "that is what we picked in 2019," the migration to RA3 usually pays for itself on the storage line alone.

Around those pieces sit the features people actually buy Redshift for: Spectrum, which queries external tables in S3 and bills per terabyte scanned; data sharing, which exposes live data to another cluster or account with no copies; and zero-ETL integrations that replicate from Aurora, RDS and DynamoDB without you owning a pipeline. Check that last group before you build ingestion by hand, and see What Is a Data Pipeline? Stages, Tools, and Failure Modes for the general shape of the decision.

Redshift distribution keys and sort keys: the two decisions that outrank hardware

Nearly every "Redshift is slow" ticket resolves to physical layout, not to node count. Two schema choices dominate.

Distribution decides which slice each row lands on. The styles:

The point of redshift distribution keys is to avoid moving data at query time. Run EXPLAIN and read the redistribution labels: DS_DIST_NONE and DS_DIST_ALL_NONE mean the join happened in place, DS_DIST_INNER and DS_DIST_BOTH mean rows crossed the network, and DS_BCAST_INNER means an entire table was broadcast to every node. A single DS_BCAST_INNER on a large table inside a nightly job is worth more attention than a node upgrade.

Sort keys decide the order of rows inside each slice, and they exist to feed zone maps. Redshift stores min and max values for every one megabyte block in memory, so a query with a predicate on a sorted column skips blocks without reading them. That is the entire mechanism, and it explains the rules: put the column you filter on most often first, usually a date or timestamp for time-series facts, and remember that a compound sort key only helps when your predicate uses the leading column. An interleaved sort key gives equal weight to several columns and is occasionally right for tables filtered many different ways, but it carries real maintenance cost and needs VACUUM REINDEX. Most teams that choose interleaved would have been better served by compound plus a narrower table.

Column compression is the quiet third decision. AZ64 for numeric and date types and ZSTD for wide text are sensible defaults, ANALYZE COMPRESSION will tell you what a sample suggests, and COPY applies encodings automatically when it loads an empty table. Compression is not just a storage saving: fewer bytes on disk means fewer blocks scanned, which is a direct latency win.

Vacuum, analyze, and the maintenance debt nobody budgets

Redshift does not update rows in place. An UPDATE writes a new row and marks the old one deleted, and a DELETE only sets a flag. Those ghost rows keep occupying blocks and keep getting scanned. Meanwhile, newly inserted rows land in an unsorted region at the end of the table where zone maps do you no good.

VACUUM is what reclaims and reorders:

• VACUUM DELETE ONLY reclaims space from deleted rows without resorting.
• VACUUM SORT ONLY resorts without reclaiming.
• VACUUM FULL does both and is the default.
• VACUUM REINDEX rebuilds interleaved sort keys.

Modern Redshift runs automatic table sort, automatic vacuum delete and automatic analyze in the background during periods of low load, and for well-behaved workloads that is enough. It stops being enough in two situations that describe a lot of real warehouses: clusters that never have a quiet period for background work to use, and tables that get very large rewrites, where a deep copy (create a new table with the same DDL, insert-select into it, swap names) finishes far faster than a vacuum on the same table.

ANALYZE is the other half. The optimizer's plan quality depends on table statistics, and stale statistics produce the classic symptom of a query that was fine last month and is now choosing a nested loop. COPY updates statistics on load by default; large INSERT INTO ... SELECT operations do not always leave them fresh.

Your monitoring list is short. SVV_TABLE_INFO gives you unsorted percentage, statistics staleness and row skew per table, which is the single most useful diagnostic view in the product. STL_ALERT_EVENT_LOG names the specific problems the optimizer noticed, including missing statistics and broadcast joins. And the workload management view of queue wait time tells you whether your problem is query speed at all or simply queuing. Automatic WLM with query priorities and short query acceleration is the right starting configuration; hand-tuned manual queues are a thing you graduate to with evidence, not a thing you start with.

Amazon Redshift data warehouse costs: provisioned clusters versus Redshift Serverless

Here is the decision most teams actually have to make, stated plainly.

Provisioned bills per node-hour for as long as the cluster exists, plus managed storage per GB-month on RA3. You can pause a cluster and stop compute charges while storage keeps billing, you can buy reserved nodes on one or three year terms for a substantial discount over on-demand, and you keep every knob: node type, node count, manual WLM, maintenance windows.

Redshift Serverless bills per RPU-hour, metered by the second with a sixty second minimum charge, only while queries are running. You set a base capacity in RPUs and a maximum, and the service scales within that. Storage is billed the same managed-storage way. There is no cluster to pause because idle is free by construction.

The honest heuristic: serverless wins on cost when your cluster would be idle a lot, and provisioned wins when it would not. A warehouse serving a nightly load plus a few analysts during business hours is idle for most of the week and is a serverless workload. A warehouse under continuous dashboard traffic from hundreds of users, plus streaming ingestion, plus scheduled transformations, is close to fully utilized and will almost certainly be cheaper on reserved provisioned nodes.

The second consideration is not cost, it is variance. Provisioned gives you a number you can put in a budget in January and still be right about in June. Serverless gives you a number that tracks reality, which is more efficient and less predictable. Set a maximum RPU-hours limit on every serverless workgroup on day one. It is the equivalent of a resource monitor, it converts a runaway from an invoice into an alert, and skipping it is how teams get the "we thought serverless was the cheap option" conversation.

Concurrency deserves a note because it is priced separately on provisioned clusters. Concurrency scaling spins up transient capacity when queries queue, and AWS grants an allowance of credits that accrues with cluster uptime and covers many teams entirely. Beyond the allowance it bills per second. Serverless folds this into the same RPU meter.

Redshift vs Snowflake: compare the pricing model, not the feature list

Feature-by-feature comparisons of redshift vs snowflake age badly, because both vendors ship the other's headline feature within a year or two. The durable differences are structural, and pricing model is the clearest of them.

Read that table as a set of trade-offs rather than a scoreboard. Redshift's provisioned model rewards discipline and punishes nothing else: if your utilization is genuinely high, renting the machine on a three year term is the cheapest way to buy that compute, and no per-second billing model beats it. Snowflake's model rewards teams that will actually configure auto suspend and resist warehouse proliferation, and it makes per-team isolation the default rather than a design exercise. The specific way a Snowflake bill drifts is covered in Snowflake Data Warehouse: Architecture, Costs, and Limits, and the same reasoning applied to the category as a whole sits in Enterprise Data Warehouse: Concept, Examples, and Cost.

Two non-price factors legitimately decide this for many teams. If your data, your IAM, your S3 lake and your Glue catalog are all in AWS, Redshift's integration with them removes real work, and Spectrum plus zero-ETL means some data never needs a pipeline at all. If you are multi-cloud, or you expect to share governed data with external parties as a routine act, Snowflake's model was built for that from the start.

Redshift cluster sizing without guessing

Redshift cluster sizing goes wrong in a predictable way: someone estimates raw data volume, divides by node capacity, and orders that many nodes. On RA3 that calculation is nearly meaningless, because storage scales independently. What you are actually sizing is compute for your working set and your concurrency.

A method that works:

1. Measure the working set, not the archive. How much data do queries actually touch in a typical week? Compressed, in Redshift's columnar format, this is usually a fraction of what the raw extracts suggest.
2. Start smaller than feels safe. Two RA3 nodes, or serverless at a modest base capacity, is a legitimate starting point for a team of analysts.
3. Load a real workload and read three signals. Queries going disk-based (spilling because they did not get enough memory), queue wait time, and the alert log. Those three tell you whether you need more memory, more concurrency, or a schema fix.
4. Fix the schema before adding hardware. A broadcast join or a missing sort key will consume a node upgrade and ask for another.
5. Resize with the right tool. Elastic resize changes node count in minutes by redistributing slices. Classic resize copies to a new cluster over hours, and is what you use to change node type.

The step teams skip is the fourth one. Buying capacity is a purchase order; fixing distribution keys is an afternoon of work by someone who understands the model. Only one of them is repeatable.

Loading data well is part of sizing too. COPY from S3 in parallel, split into a number of compressed files that is a multiple of your slice count, is dramatically faster than serial loads, and single-row INSERT statements from an application are the classic way to make a healthy cluster look broken. If you are choosing how data gets there in the first place, Data Integration Tools: A Buyer's Guide for Lean Teams covers the build-versus-buy decision without assuming you have a data engineering team.

Where a warehouse is the right answer, and the smaller case where it is not

A Redshift data warehouse earns its keep when several of these are true: you have years of history that must survive changes to the source systems, you join across domains that no single application owns, you have tens or hundreds of people querying the same governed numbers, you need modeled tables that carry business definitions rather than raw exports, or you have compliance-driven retention that the source tools cannot satisfy. At that scale a warehouse is not overhead, it is the only structure that keeps the numbers consistent, and the cataloging question that comes next is worth reading about in Data Catalog Tools: Do You Need One at Your Data Size?.

There is a smaller case that gets a cluster it does not need. It looks like this: a team of ten to fifty, data that lives in Stripe, HubSpot, QuickBooks, Google Analytics, Gmail and Slack, and a set of questions that are operational rather than analytical. Why did revenue dip last week. Which accounts went quiet before renewal. Did that payout ever land in the ledger. Those questions are cross-tool, they are about the last ninety days, and the volume involved is small. Standing up a warehouse to answer them means building extraction, modeling, orchestration and a BI layer, then maintaining all four, so that a person can get an answer that the source systems already contain.

This is where Skopx fits, and it is worth being precise about what that means. Skopx is an AI workspace that connects nearly 1,000 tools a company already uses and answers questions in chat with data cited back to the source record, alongside a morning brief, an insights engine that surfaces risks and anomalies, and workflows you build by describing them. It is not a data warehouse, not an ETL tool, not a BI or dashboard-building product, and not a CRM. It does not store your history in a modeled schema, it does not give you SQL over a semantic layer, and it will not replace Redshift for anything the paragraph above describes as a genuine warehouse workload. It reads from the systems of record and reasons over what it finds, which is a different job.

Recurring finance question, answered from the source tools

Every weekday 07:00

Scheduled trigger on a workflow described in chat

Read Stripe activity

Charges, refunds and payout batches

Read QuickBooks entries

Deposits and matched invoices

Read CRM accounts

Owner and plan for each customer

Reconcile and flag gaps

Runs on your own model key, zero markup

Post the summary

Every figure cited to its source record

The reconciliation example is a real one: matching Stripe fees and payouts against the ledger is a monthly chore for most small finance teams, and it is walked through in Stripe QuickBooks Integration: Reconciling Fees and Payouts. The general question of when a dashboard tool is the right destination at all, versus a faster path to the same answer, is in Tableau Data Analysis: Strengths, Limits, and Faster Paths.

The test is honest and easy to apply. If the answer requires joining millions of rows across years of modeled history, build the warehouse. If it requires asking three connected systems the same question and comparing what they say, a cluster is a very expensive way to do that. Skopx costs $5 per month for Solo and $16 per seat per month for Team, with workflows included and your own AI key billed at zero markup, and the full breakdown is on the pricing page. That is a different order of magnitude from a warehouse plus a pipeline plus a BI seat count, which is exactly the point: the two are not substitutes, they are answers to questions of different size.

Frequently asked questions

Is Redshift Serverless always cheaper than a provisioned cluster?

No. Serverless is cheaper when the cluster would otherwise be idle a large share of the time, which describes most small and mid-size analytics workloads. It is more expensive when utilization is high and sustained, because provisioned reserved nodes on a one or three year term buy that same compute at a discount serverless does not offer. Estimate your active hours honestly, then compare, and set a maximum RPU-hours limit either way.

Do I still need to run VACUUM manually?

Usually not. Automatic table sort, automatic vacuum delete and automatic analyze handle ordinary workloads in the background. You still need manual intervention in two cases: clusters with no quiet period for background maintenance to use, and tables that have taken very large deletes or rewrites, where a deep copy into a fresh table often finishes faster than a vacuum. Watch the unsorted percentage in SVV_TABLE_INFO rather than scheduling vacuums out of habit.

What is the difference between a distribution key and a sort key?

A distribution key decides which node and slice a row is stored on, and its job is to keep joined rows together so the query does not shuffle data across the network. A sort key decides the order of rows within each slice, and its job is to let zone maps skip blocks that cannot match your filter. They solve different problems, and a table can and often should have both.

How do I decide between Redshift and Snowflake?

Compare the pricing models and the surrounding ecosystem, not the feature lists. If you are AWS-centric with data already in S3, IAM and Glue, Redshift removes integration work and Spectrum lets some data stay where it is. If you are multi-cloud, or you routinely share governed datasets with outside parties, Snowflake was designed around that. On cost, Redshift provisioned rewards high steady utilization and Snowflake rewards teams that will configure auto suspend properly.

Can Redshift query data in S3 without loading it?

Yes, through Redshift Spectrum, using an external schema backed by the Glue Data Catalog. Spectrum bills per terabyte scanned, so partitioning and columnar file formats matter a great deal to the cost. It is a good fit for cold history and raw event data you query occasionally, and a poor fit for tables that every dashboard touches.

How many nodes should a new cluster start with?

Fewer than you think. Two RA3 nodes, or serverless at a modest base capacity, handles a surprising amount of work when the schema is right. Load a real workload, then look at disk-based queries, queue wait and the alert log, and let those three signals tell you whether the constraint is memory, concurrency, or a layout problem that no amount of hardware will fix. Building the habit of measuring before buying is the same discipline described in AI Native Organization: Best Practices That Hold Up.