Amazon Redshift for Data Warehousing

Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud. It is designed for online analytical processing (OLAP) workloads—queries that scan millions or billions of rows, aggregate data, and generate business intelligence reports. Unlike traditional relational databases optimized for transactional (OLTP) workloads (e.g., Amazon RDS), Redshift excels at complex queries over large datasets. The SAA-C03 exam tests your ability to choose Redshift when the workload is analytical, requires high performance on large datasets, and involves data integration from multiple sources.

Redshift uses a massively parallel processing (MPP) architecture. A cluster consists of a leader node and one or more compute nodes. The leader node receives queries, parses them, optimizes the execution plan, and distributes the work to compute nodes. Compute nodes execute the plan in parallel and return intermediate results to the leader, which aggregates and sends the final result to the client.

Each compute node is divided into slices—virtual processors. The number of slices per node depends on the node type and size. For example, a dc2.large node has 2 slices, while a dc2.8xlarge has 32 slices. Data is distributed across slices based on the distribution style you choose.

- Leader Node: Manages communication with clients, parses queries, and distributes work. There is exactly one leader node per cluster. It is not used for data storage or query execution. - Compute Nodes: Store data and execute queries. You can have 1 to 128 compute nodes (depending on node type). - Slices: Each compute node's CPU and memory are partitioned into slices. The number of slices is fixed per node type. - Distribution Styles: - KEY: Rows are distributed based on the values in one column (the distribution key). Rows with the same key value go to the same slice. This is optimal for joining large tables on that key. - EVEN: Rows are distributed round-robin across slices. This is the default and works well when keys are not used in joins. - ALL: A copy of the entire table is distributed to every slice. Use for small dimension tables that are frequently joined. - Sort Keys: Define the order in which rows are stored in each slice. Compound sort keys (default) define a multi-column order; interleaved sort keys give equal weight to each column. - Compression: Redshift automatically applies column compression when you load data using COPY. You can also manually define compression encodings (e.g., AZ64, ZSTD, LZO). - Data Blocks: Data is stored in 1 MB blocks. Each block contains data from one column of one slice. - Node Types: - Dense Compute (dc2): SSD-based, high compute power, up to 326 TB per cluster. - Dense Storage (ds2): HDD-based, up to 2 PB per cluster. - RA3: Managed storage, separates compute and storage, allows scaling compute independently.

To create a Redshift cluster via AWS CLI:

aws redshift create-cluster \
    --cluster-identifier mycluster \
    --node-type dc2.large \
    --master-username admin \
    --master-user-password MyP@ssw0rd! \
    --number-of-nodes 2

To verify cluster status:

aws redshift describe-clusters --cluster-identifier mycluster

To connect and run queries, use a SQL client (e.g., psql, SQL Workbench) with the endpoint provided by the cluster. Example:

psql -h mycluster.xxxxxx.redshift.amazonaws.com -p 5439 -U admin -d dev

Redshift allows you to define multiple queues with different concurrency levels and memory allocation. By default, there is one queue with a concurrency level of 5 (i.e., up to 5 queries can run concurrently). You can create additional queues for different user groups or query types. Short Query Acceleration (SQA) prioritizes short-running queries over long-running ones.

COPY mytable FROM 's3://mybucket/data/' 
IAM_ROLE 'arn:aws:iam::123456789012:role/MyRedshiftRole' 
FORMAT AS CSV DELIMITER ',' 
REGION 'us-east-1';

aws redshift resize-cluster --cluster-identifier mycluster --number-of-nodes 4

aws redshift resize-cluster --cluster-identifier mycluster --node-type dc2.8xlarge --number-of-nodes 2

aws redshift copy-cluster-snapshot --source-snapshot-identifier my-snapshot --target-snapshot-identifier my-snapshot-copy --source-region us-east-1 --target-region us-west-2

Redshift now includes automatic table optimization (ATO) that recommends distribution and sort keys based on query patterns. You can accept or reject the recommendations.

Scenario 1: E-Commerce Analytics A large e-commerce company uses Redshift to analyze customer behavior, sales trends, and inventory levels. They ingest data from multiple sources (transactional databases, web logs, and third-party APIs) into S3, then use AWS Glue ETL jobs to transform and load the data into Redshift. The fact table (orders) has billions of rows and uses KEY distribution on customer_id to join with the customer dimension table (also distributed by customer_id). Sort keys are on order_date for time-range queries. They use Concurrency Scaling to handle spikes during holiday sales. Misconfiguration: Initially, they used EVEN distribution on the fact table, causing massive redistribution during joins, leading to slow queries. After switching to KEY distribution, query performance improved 10x.

Scenario 2: Financial Reporting A financial institution needs to generate daily risk reports from terabytes of transaction data. They use Redshift with RA3 nodes to separate compute and storage, allowing them to scale compute independently as reporting needs grow. They load data from on-premises databases using AWS DMS. They use Redshift Spectrum to query historical data stored in S3 without loading it, reducing storage costs. They enable encryption at rest using AWS KMS and use VPC isolation for security. A common issue: they forgot to run ANALYZE after loading, causing the optimizer to generate poor query plans. They now schedule ANALYZE after every load.

Scenario 3: IoT Data Analytics A manufacturing company collects sensor data from thousands of devices. They stream data via Kinesis Data Firehose directly to Redshift for near-real-time monitoring. They use a staging table with EVEN distribution for raw data, then a nightly ETL job transforms and inserts into a fact table with KEY distribution on device_id. They use interleaved sort keys on timestamp and device_type to support various ad-hoc queries. They encountered a problem with data skew: one device produced 50% of the data, causing one slice to be overloaded. They resolved it by using a composite distribution key (device_id, device_type) to spread the load.