Amazon Redshift: Analytical Data Warehousing

Redshift is an analytical warehouse, not an operational row store

Amazon Redshift is a managed data-warehouse service built for analytical SQL over large datasets. Warehouse workloads typically scan, filter, join, and aggregate many rows to answer questions across historical data. Redshift therefore uses columnar storage and distributed query execution so analytical operations can process data in parallel and read the columns relevant to a query.

This workload is different from high-frequency transactional access where applications repeatedly read or update individual records. A design should not select Redshift simply because SQL is required. The decisive question is whether the workload is analytical warehousing and BI, with large scans and aggregations, rather than OLTP request processing.

Distribution and sorting shape distributed query work

Redshift distributes table rows across compute slices. A distribution strategy influences where rows live and therefore whether join processing can occur locally or requires data redistribution across the network. Distribution skew is undesirable because some slices then hold or process disproportionate amounts of data. AUTO distribution can let Redshift choose and adapt a distribution style rather than requiring every table to be manually tuned.

Sort keys influence how rows are organized into storage blocks and allow the query processor to skip blocks using stored metadata such as minimum and maximum values. A date-oriented sort can therefore accelerate range-restricted scans when queries frequently constrain that date. Automatic table optimization can manage supported sort and distribution choices, but understanding the mechanics remains useful when diagnosing skew or large scans.

• Distribution aims to balance work and colocate data useful for joins.
• Sort keys help block pruning for predicates aligned with stored order.
• Columnar encoding reduces analytical I/O by organizing and compressing column values.
• Automatic optimization reduces the amount of manual physical-table tuning required.

Spectrum and Serverless change the warehouse boundary

Redshift Spectrum lets Redshift query structured and semi-structured data stored in Amazon S3 without loading all of it into local Redshift tables. External schemas and tables describe that data, commonly through catalog metadata. This allows a warehouse query to join curated warehouse data with data-lake files while S3 remains the storage location for the external dataset.

Amazon Redshift Serverless provides a serverless warehouse execution model with namespaces for data/database resources and workgroups for compute/network configuration. It removes cluster sizing from the user's normal workflow, but SQL design, table organization, workload isolation, and source-data quality still matter. The decision between provisioned and serverless Redshift is primarily an operational/capacity-model decision, not a change in Redshift's analytical purpose.