Amazon Redshift is a fully managed, petabyte-scale cloud data warehouse service that lets you run complex analytical queries on massive datasets in seconds. For the AWS Certified Data Engineer Associate (DEA-C01) exam, you need to understand how it differs from a standard database, how it stores data, and how to set it up for optimal performance and cost. Mastering Redshift is central to the 'set up and optimise data warehousing solutions' objective.
Jump to a section
A simple way to picture Amazon Redshift: Cloud Data Warehousing and Analytics
When a small town library gets a sudden flood of new books and thousands of tourists visit every day, their old card-catalogue system breaks down. This leads to visitors waiting hours to find a book, staff getting overwhelmed, and books getting lost or misfiled. The librarian realises they need a completely new system designed to handle huge volumes of information quickly and reliably.
Amazon Redshift is like building that new library with a super-efficient filing system. Instead of one librarian checking each book request on a single index card (like a traditional database), Redshift divides up the work. It groups all the books by category (a process called 'columnar storage') so if you ask for all the 'mystery' books, it only looks at the mystery section, not every single book. The library also hires multiple junior librarians to work in parallel — each one searches their own section at the same time, so you get your answer in seconds instead of hours. The head librarian (the 'leader node') takes your request, breaks it into smaller tasks, and sends each task to a different junior librarian (a 'compute node'). They all report back with their findings, and the head librarian combines the results into a single, neat answer. This whole system is specifically designed for asking big, complex questions across millions of books — not for just looking up one book at a time.
Amazon Redshift is a cloud-based data warehouse service from AWS. A data warehouse is a special type of database designed not for handling day-to-day transactions (like buying a coffee), but for analysing huge amounts of historical data to answer business questions. For example, a retail company might use a data warehouse to answer 'What was our total revenue in the UK last quarter, broken down by product category?'
Before cloud data warehouses like Redshift, companies had to build their own on-premises data warehouses. This meant buying expensive hardware, hiring specialists to maintain it, and waiting weeks to add more storage when data grew. Redshift eliminates this problem. You spin it up in the AWS cloud in minutes, and it automatically handles backups, patching, and replication.
How Redshift is different from a standard database like Amazon RDS
Standard databases (called Online Transaction Processing or OLTP systems) are optimised for writing and updating small amounts of data many times per second. They store data in rows on disk. Imagine you have a spreadsheet with a million rows, each row being a customer order. An OLTP database stores the entire row together. If you ask 'What is the total sales amount for product X?', the database has to read every row to find product X and then read the amount column.
Redshift (an Online Analytical Processing or OLAP system) stores data in columns instead of rows. Using the same spreadsheet analogy, Redshift stores all the 'product' values together, all the 'sales amount' values together, and so on. This is called 'columnar storage'. Why does this matter? If you only need the 'sales amount' for a query, Redshift only reads that one column from disk, ignoring all the other columns in the table. This dramatically reduces the amount of data read from disk, making queries up to 100 times faster for analytical workloads.
Massively Parallel Processing (MPP)
Redshift is built on a Massively Parallel Processing (MPP) architecture. MPP means that a complex query is broken into smaller pieces and executed simultaneously across many individual computers called 'compute nodes'. Each compute node has its own CPU, memory, and disk storage.
Here is how it works in practice:
The 'leader node' is the brain. It receives your SQL query, parses it, and creates an execution plan.
The leader node breaks that plan into smaller tasks and distributes them to the compute nodes.
Each compute node works on its own chunk of data in parallel.
The compute nodes send their partial results back to the leader node.
The leader node combines (aggregates) all the partial results and sends you the final answer.
This parallel processing is why Redshift can scan terabytes of data in seconds. Adding more compute nodes makes Redshift faster and lets it handle larger datasets. You can scale horizontally by adding more nodes, or vertically by upgrading to more powerful node types.
Key concepts for setting up Redshift
Node types: Redshift offers different node families, like DC2 (dense compute, fast CPUs, more RAM, less storage) and RA3 (managed storage, can scale storage independently from compute). For the DEA-C01 exam, know that RA3 nodes are designed for large datasets where you want to decouple storage and compute costs.
Distribution styles: When you create a table, you choose how data is distributed across the compute nodes. The options are KEY (rows with the same distribution key go to the same node), EVEN (data is round-robined across all nodes), and ALL (a full copy of the table goes to every node). The choice affects query performance. A common exam scenario involves choosing KEY distribution on a column that is used in JOIN operations to minimise data shuffling between nodes.
Sort keys: A sort key determines the physical order of rows on disk. If you sort a table by a frequently-filtered column (like a 'date' column), Redshift can skip entire blocks of data that do not match the filter, speeding up queries significantly.
Workload Management (WLM): WLM allows you to define query queues to prioritise different types of workloads. For instance, you might create a short queue for CEO dashboards and a long queue for overnight ETL jobs.
Why it replaces traditional on-premises warehouses
Redshift replaces the need to buy, configure, and maintain physical servers. You do not manage hardware. You do not worry about disk failure — Redshift automatically replicates data across nodes and stores backups in Amazon S3. You pay only for what you use (per hour per node). You can pause a cluster when it is not needed, saving money. It integrates natively with other AWS services like AWS Glue (for ETL), Amazon S3 (for staging data), and Amazon QuickSight (for visualisation).
Create a Redshift Cluster
You launch a Redshift cluster in the AWS Management Console. You choose a node type (RA3 or DC2), specify the number of nodes, set a master username and password, and configure networking (VPC, security groups). This is the infrastructure step — once it is launched, the cluster is a running set of compute nodes ready to accept data.
Design the Table Schema with Distribution and Sort Keys
You write CREATE TABLE statements that define how data will be spread across nodes (distribution style) and how it will be ordered on disk (sort key). This step is crucial because a poor design slows down queries. For example, choosing KEY distribution on a join column keeps related data on the same node, reducing data shuffling.
Load Data Using the COPY Command
You stage your data in Amazon S3 (often in CSV or Parquet format). Then you run the COPY command, which loads the data into the Redshift table in parallel from the S3 files. This is the most efficient loading method. You specify an IAM role that gives Redshift permission to read the S3 bucket.
Optimise with Compression Encoding and ANALYZE
After loading data, you run the ANALYZE command to update table statistics, which helps the query optimiser choose efficient execution plans. You can also run ANALYZE COMPRESSION to see the recommended compression encoding for each column — this reduces disk storage and speeds up scans.
Run Queries and Monitor Performance
Users (or BI tools) send SQL queries to the cluster. The leader node parses and distributes the query to compute nodes. You monitor performance using system tables like STL_QUERY, SVL_QUERY_REPORT, and Amazon CloudWatch metrics. This step identifies slow queries, which you can then tune by adjusting sort keys, distribution keys, or WLM queues.
Manage Workloads with WLM and Concurrency Scaling
You configure Workload Management (WLM) to create separate queues for different types of queries (e.g., short vs long). You enable Concurrency Scaling to automatically handle spikes in concurrent queries. This keeps everything running smoothly without manual intervention.
Maintain and Scale the Cluster
You take snapshots for backup (automated or manual). You can resize the cluster (Elastic Resize for fast changes, Classic Resize for more flexibility) as data volume grows. You can also pause the cluster when not in use to save costs. Regular maintenance includes vacuuming (to reclaim space after updates) and analysing statistics.
Consider Sarah, a data engineer at a large e-commerce company called 'ShopFast'. ShopFast generates millions of transactions every day: orders, returns, page views, and inventory changes. The company's executives need answers to questions like: 'Which products sold best in the last hour during our flash sale?' or 'What is the average delivery time by region for the last quarter?'
Previously, ShopFast stored all its data in an Amazon RDS transactional database. Queries like the regional delivery time one would take 45 minutes to run and sometimes crashed the database, because RDS was struggling to read all the rows for every column.
Step 1: Setting up the Redshift cluster Sarah logs into the AWS Management Console and clicks 'Create Cluster'. She selects a four-node RA3 cluster because her data is growing rapidly, and she wants to be able to scale storage independently. She gives the cluster a name ('shopfast-analytics'), configures the security group to allow access only from her company's BI tools, and launches it. The cluster is ready in about 10 minutes.
Step 2: Ingesting data Sarah needs to get her transactional data into Redshift. She writes a simple ETL job using AWS Glue. This job extracts data from the RDS database, transforms it (for example, it flattens nested JSON fields and adds a 'partition_date' column), and loads it into Redshift using the COPY command. The COPY command is heavily tested on the DEA-C01 exam — it is the most efficient way to load large amounts of data from Amazon S3 into Redshift. Sarah stages her transformed data as CSV files in an S3 bucket, then runs:
COPY orders_table FROM 's3://shopfast-data/orders/' IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyUnload' CSV IGNOREHEADER 1;
Step 3: Optimising the data To make queries fast, Sarah creates the table with a DISTSTYLE of KEY on the 'customer_id' column, because the table is frequently joined with a 'customers' table on that column. She sets the SORTKEY to the 'order_date' column, because almost every query filters by date range. She also chooses COMPRESSION ENCODING for each column — Redshift has automatic compression, but Sarah manually tunes it for columns with long text strings.
Step 4: Running queries and monitoring Now, the executive team runs their queries via Amazon QuickSight dashboards. A query that used to take 45 minutes now finishes in 8 seconds. Sarah monitors the cluster using Amazon CloudWatch metrics and the STL_QUERY system table. She notices that one query queue is getting backed up, so she tunes the WLM settings to give priority to the dashboard queries. During weekends, when usage is low, Sarah pauses the cluster to save 70% on costs.
What an IT professional actually does daily:
Designs table schemas with appropriate distribution and sort keys.
Configures security: IAM roles, bucket policies for COPY, VPC security groups.
Monitors performance using Amazon CloudWatch and Redshift system tables (STL_QUERY, SVV_TABLES).
Manages Concurrency Scaling for handling high numbers of concurrent users.
Automates cluster snapshots and recovery.
Tunes WLM queues to prioritise different workloads.
Resizes the cluster up or down based on demand using Elastic Resize (fast, no downtime) or Classic Resize (slow, needed for some node changes).
The DEA-C01 exam tests your understanding of the core architectural decisions that affect performance, cost, and security in Amazon Redshift. You are not expected to memorise every SQL command, but you must know when to use each feature and why.
Key exam topics and question patterns:
Distribution styles: You will see scenario-based questions where a table is being JOINed with another table on a specific column. The correct answer is almost always KEY distribution on that join column. If the scenario says the table is large and does not have a clear join column, choose EVEN distribution. If the scenario involves a small dimension table (like a 'states' table with only 50 rows), choose ALL distribution.
Sort keys: The exam loves questions where you must choose a sort key to optimise a query that filters by a column (like 'order_date' or 'region'). Compound sort keys (multiple columns) are tested: the order of columns in the key matters. A compound sort key gives the most benefit when the first column is the most frequently filtered column.
COPY command vs. INSERT: You will be asked which method is best for loading data. The answer is always COPY from S3 for bulk loads. INSERT is for single rows and is inefficient at scale. Traps: Do not use multi-row INSERT statements — they are still slower than COPY.
Data compression (encoding): Redshift automatically applies compression when you load data with the COPY command. The exam might ask what happens when you run ANALYZE COMPRESSION before loading — this helps you manually choose the best encoding for each column.
Workload Management (WLM): A question might describe a scenario where one type of query (e.g., a long-running report) is blocking shorter queries (e.g., dashboards). The correct answer is to create separate WLM queues with different concurrency levels and priorities, and then assign queries to queues using query groups or user groups.
Concurrency Scaling: If the scenario involves sudden spikes in concurrent read queries, the answer is to enable Concurrency Scaling. This automatically adds additional cluster capacity to handle the load without queuing.
Spectrum: If you see a scenario involving querying data that resides in S3 without loading it into Redshift, the answer is Redshift Spectrum. It allows you to run SQL queries directly on files in S3 (like CSV, Parquet, or Avro).
Common tricks and traps:
The exam might ask 'What is the most efficient way to load data into Redshift from S3?' A distractor answer will say 'Use the INSERT statement in a loop'. The correct answer is 'Use the COPY command with an IAM role'.
They might give you a scenario with a small dimension table (e.g., 'currency_codes') and ask how to distribute it. A wrong answer would be EVEN (which shuffles data) or KEY (which distributes it unevenly). The correct answer is ALL distribution.
Look out for questions that conflate 'distribution key' and 'sort key'. A distribution key controls which node gets each row. A sort key controls the order of rows on disk. They serve different purposes.
Memorise these definitions for the exam:
Leader node: Manages communication with client programs and coordinates query execution.
Compute node: Stores data and executes query plan segments.
Node slice: Each compute node is divided into slices; a slice is a unit of parallel processing.
Columnar storage: Data is stored column-wise, reducing I/O for analytical queries.
Data warehousing: A system designed for analysing large volumes of historical data.
Amazon Redshift is an OLAP data warehouse, not an OLTP transaction database; it uses columnar storage and MPP architecture for fast analytics on big data.
Choose distribution style (KEY, EVEN, or ALL) based on how tables are JOINed — KEY on the join column for large tables, ALL for small dimension tables, EVEN for tables with no clear join column.
Always use the COPY command from Amazon S3 for bulk data loading into Redshift; never use INSERT for more than a few rows.
A compound sort key is most effective when queries filter on the first column of the key; use interleaved sort keys if you filter on multiple columns interchangeably.
Redshift Spectrum allows you to query data directly in Amazon S3 without loading it into the cluster, which saves storage costs for infrequently accessed data.
Workload Management (WLM) queues let you prioritise different types of queries, so a long-running report does not block a dashboard query.
Concurrency Scaling automatically adds capacity to handle spikes in concurrent read queries without queuing or performance degradation.
RA3 node types decouple storage and compute — you can scale storage independently and pay only for the data you store, not for compute when it is paused.
These come up on the exam all the time. Here's how to tell them apart.
OLTP (e.g., Amazon RDS)
Optimised for many short, concurrent read/write transactions
Stores data in rows on disk
Used for everyday operations like order processing and login verification
OLAP (e.g., Amazon Redshift)
Optimised for complex analytical queries on large datasets
Stores data in columns on disk (columnar storage)
Used for business intelligence reporting and data analysis
COPY Command
Loads data from Amazon S3 in parallel across all nodes
Automatically applies compression encoding
Able to load terabytes of data quickly
INSERT Command
Inserts one row at a time
Does not use parallel loading
Extremely slow for bulk data (do not use for more than a few rows)
Compound Sort Key
Sorts data hierarchically based on the order of columns in the key
Most efficient for queries that filter on the first column
Less effective when queries filter on non-first columns
Interleaved Sort Key
Gives equal weight to all columns in the key
Useful when you query unpredictably on any combination of columns
Increases maintenance (VACUUM) overhead
RA3 Node
Uses managed storage in S3, independent of compute
Can scale storage without adding compute nodes
Higher cost per node but lower overall cost for large datasets
DC2 Node
Uses local SSD storage attached to the node
Storage and compute scale together (adding nodes adds both)
Better suited for high-performance workloads on limited data
Redshift (standard table)
Data is stored in the Redshift cluster's managed storage
Data is loaded via COPY and maintained in the cluster
Queries run only on cluster data
Redshift Spectrum (external table)
Data remains in Amazon S3, not loaded into the cluster
Uses an external table defined in the AWS Glue Data Catalog
Queries scan S3 data directly, suitable for infrequently used data
DISTSTYLE KEY
Rows with the same distribution key go to the same compute node
Used for large fact tables that are frequently joined on the key column
Reduces data shuffling during JOINs
DISTSTYLE ALL
A full copy of the table is stored on every compute node
Used for small dimension tables (e.g., under 1000 rows)
Increases storage overhead but eliminates data movement during JOINs
Mistake
Amazon Redshift is just a bigger, faster version of Amazon RDS.
Correct
Redshift is a fundamentally different type of database (OLAP) designed for analytical queries on large datasets, not for high-volume transactional processing (OLTP) like RDS. It uses columnar storage and MPP architecture, which are not available in RDS.
Both are 'databases' that run SQL, and RDS also offers MySQL, PostgreSQL, etc., so beginners assume Redshift is 'RDS but bigger'. They do not realise the internal architecture is completely different.
Mistake
Adding more nodes will always make queries faster, regardless of how the data is distributed.
Correct
Adding nodes improves query speed only if the data is distributed effectively across those nodes. If every query needs to shuffle large amounts of data between nodes (due to a poor distribution key), adding nodes can actually increase network overhead and slow things down.
It is intuitive that 'more computers = faster work'. But in an MPP system, the performance gain depends on minimising data movement between nodes. Beginners overlook the distribution key's role.
Mistake
You should use the INSERT command for bulk loading data into Redshift because it is straightforward and simple.
Correct
The COPY command from Amazon S3 is the most efficient way to bulk load data. It loads data in parallel from multiple files across nodes. INSERT is a single-row operation and is extremely slow for large volumes. Using INSERT for a million rows is a common mistake that causes performance problems.
In traditional databases, INSERT is the standard way to add rows. Beginners naturally reach for INSERT. The AWS documentation emphasises COPY, but many self-taught users miss this key optimisation.
Mistake
Amazon Redshift Spectrum and Amazon Redshift are the same service; Spectrum just runs on top of existing Redshift clusters.
Correct
Redshift Spectrum is a feature of Redshift that lets you query data directly from Amazon S3 without loading it into your Redshift cluster. You do not need a Redshift cluster to use Spectrum, but you need a cluster to 'anchor' the query from. Spectrum can work with external tables defined in the AWS Glue Data Catalog.
The name 'Redshift Spectrum' sounds like an add-on to Redshift. Beginners think it is the same engine, but it is actually a separate query execution layer that can scan S3 data directly.
Mistake
You should always use a compound sort key with all columns that appear in WHERE clauses.
Correct
A compound sort key gives the most benefit when you filter by the first column in the key. If you frequently filter by two different columns in different queries (e.g., sometimes by date, sometimes by region), you should use an interleaved sort key instead. A compound key with many columns becomes less efficient for queries that do not filter on the first column.
Beginners assume 'more sort key columns = better filtering'. They do not understand that a compound sort key is a hierarchical index (like a phonebook sorted by last name, then first name). To find 'John' without a last name, you have to scan the whole book.
Mistake
Redshift automatically scales storage and compute together when you add nodes.
Correct
With RA3 nodes, you can scale storage independently from compute. DC2 nodes do not support this feature. You can add more nodes to increase compute power, and the storage automatically grows independently with RA3 nodes, but you pay separately for the managed storage. You can also use Elastic Resize to change the number of nodes in minutes without downtime.
Beginners coming from serverless services (like Aurora Serverless) expect everything to auto-scale transparently. Redshift's scaling model requires understanding node families and resize operations.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Yes, Amazon Redshift is based on PostgreSQL and supports standard SQL, including JOINs, subqueries, and aggregations. It is a relational data warehouse, but it is not a transactional database — it is optimised for read-heavy analytical queries.
Redshift pricing is based on the node type and number of nodes you run per hour. You also pay for backups in S3 and for data scanned by Redshift Spectrum (if used). RA3 nodes charge separately for managed storage. You can pause a cluster to avoid compute charges when not in use.
Both query data directly in S3. Athena is a serverless query service that you pay per query based on data scanned; you do not need a cluster. Redshift Spectrum is a feature of Redshift that lets you query S3 data from your existing Redshift cluster, using the same SQL engine and combining S3 data with your Redshift tables.
Technically yes, but it may be overkill and more expensive than alternatives like Amazon RDS or Amazon Athena. Redshift is designed for datasets starting at several hundred gigabytes and scaling to petabytes. For smaller data, consider a standard database or Athena.
Redshift automatically detects node failures and replaces them. It stores redundant copies of your data across different nodes (with RA3, data is stored in S3). If a node fails, Redshift re-replicates the data to the new node. You should also configure automated snapshots to S3 for disaster recovery.
Yes, you need basic SQL knowledge to understand how Redshift works and to answer scenario-based questions. You will not be asked to write complex SQL in the exam, but you must understand concepts like JOINs, WHERE clauses, and the COPY command syntax.
Redshift is not designed for real-time streaming. It is a batch-oriented data warehouse for analytics on historical data. For streaming data, you would use Amazon Kinesis Data Streams or Amazon MSK, then load the data into Redshift in micro-batches using the COPY command or a streaming ingestion feature (Redshift Streaming Ingestion for Kinesis Data Streams).
DC2 nodes are dense compute nodes with local SSD storage and are good for high-performance workloads where the data fits on the local disks. RA3 nodes use managed storage in S3, allowing you to scale compute and storage independently, which is more cost-effective for large datasets.
You've finished Amazon Redshift: Cloud Data Warehousing and Analytics. Continue through the DEA-C01 study guide to build a complete picture of the exam.
Done with this chapter?