Redshift Data Warehousing

Amazon Redshift is a fully managed, petabyte-scale data warehouse optimized for analytics using columnar storage and massively parallel processing (MPP). Understanding cluster architecture is essential for cost and performance optimization.

A Redshift cluster consists of a leader node and one or more compute nodes. The leader node receives queries, compiles execution plans, and distributes work to compute nodes. Compute nodes execute the plans and return results to the leader.

Node Types:

• RA3 nodes separate compute from storage. You choose compute capacity (ra3.xlplus, ra3.4xlarge, ra3.16xlarge) independently from managed storage. This is ideal when storage needs grow faster than compute needs.
• DC2 nodes (dense compute) combine compute and local SSD storage. Choose DC2 for datasets under 1 TB where maximum query performance matters.

For example, a company analyzing 500 GB of daily transaction data might start with a single dc2.large node. As data grows to 5 TB, they migrate to ra3.xlplus nodes with managed storage, separating compute scaling from storage scaling.

Reserved Nodes provide significant cost savings for predictable, long-term workloads. A 1-year reserved node saves approximately 32% compared to on-demand pricing, and a 3-year commitment saves up to 65%. Use reserved nodes for production clusters with stable capacity requirements.

Redshift Serverless eliminates cluster management entirely. It automatically scales compute based on query complexity and data volume. Serverless is ideal for sporadic analytics workloads or organizations that want to avoid capacity planning.

The COPY command is the most efficient way to load data into Redshift. It parallelizes loading across cluster nodes and supports multiple data sources.

Basic COPY syntax loads from S3:
COPY orders FROM 's3://bucket/orders/' IAM_ROLE 'arn:aws:iam::role/RedshiftRole' FORMAT AS PARQUET;

COPY supports CSV, JSON, Avro, Parquet, and ORC formats. Parquet and ORC are columnar formats that compress well with Redshift columnar storage, resulting in faster loads and queries. For example, loading 100 GB of Parquet data typically completes in minutes, while the same data in CSV might take 10-15 minutes.

Loading from DynamoDB uses the FROM DYNAMODB option with a MAP type to specify the table and key conditions. This is useful for data warehousing operational data from DynamoDB.

Manifest files list specific S3 objects to load, preventing duplicate loads and ensuring data consistency. A manifest file points to exactly which files should be loaded, useful when your S3 bucket contains historical data that should not be reloaded.

Error handling options include MAXERROR to tolerate a specified number of load errors, and IGNOREHEADER to skip header rows. The REMOVEQUOTES option strips quotation marks from CSV data.

A best practice is to load data in bulk during off-peak hours, use manifest files for incremental loads, and prefer Parquet/ORC for columnar efficiency. Always validate loaded data with sample queries before reporting.

Redshift Spectrum allows you to query data directly in S3 without loading it into Redshift. This extends your data warehouse to query exabytes of data in S3 alongside data loaded in Redshift tables.

To use Spectrum, create an external schema that references an AWS Glue Data Catalog database. The Glue Catalog stores table definitions and metadata for files in S3. For example, you might create an external schema s3_logs pointing to a Glue database that catalogs Parquet files in S3 containing web server logs.

Query external tables like regular tables:
SELECT status_code, COUNT(*) FROM s3_logs.web_logs WHERE date = '2024-01-15' GROUP BY status_code;

Spectrum pushes predicates and filters down to S3, scanning only relevant files. With partitioned data, Spectrum prunes partitions based on WHERE clauses, dramatically reducing data scanned. Partitioning by date is a common pattern for time-series data.

Use cases:

• Querying historical data archived to S3 that is too large or infrequently accessed to justify loading into Redshift
• Joining external S3 data with internal Redshift tables for ad-hoc analysis
• Exploring new datasets in S3 before deciding to load them into Redshift

A media company might keep 90 days of detailed logs in Redshift for fast queries and archive older logs to S3 as Parquet. Analysts can query the full year of data using Spectrum when needed, paying only for the data scanned.

Query performance in Redshift depends heavily on data distribution and sorting strategies.

Distribution Styles control how data is distributed across nodes:

• KEY distribution assigns rows to nodes based on the value of a specified column. Rows with the same key value reside on the same node. Ideal for joining large tables on the distribution key.
• EVEN distribution distributes rows round-robin across all nodes. Best for tables without clear join patterns.
• ALL distribution copies the entire table to every node. Use for small dimension tables that join with large fact tables.
• AUTO distribution lets Redshift choose based on table size. Good default for new tables.

Sort Keys determine the physical ordering of data on disk:

• Compound sort keys sort by the first column, then the second, and so on. Effective when queries consistently filter by the leading columns.
• Interleaved sort keys give equal weight to all columns in the sort key. Better when queries filter by different columns in different patterns.

For a sales fact table, use sale_date as the leading compound sort key since most queries filter by date range.

Workload Management (WLM) configures query queues and memory allocation. Create separate queues for different workload types: a reporting queue with high concurrency for dashboards, and an ETL queue with low concurrency for complex transformations.

Concurrency Scaling automatically adds cluster capacity when queue wait times exceed thresholds. This handles bursty reporting workloads without permanently increasing cluster size.