Amazon Redshift: Data Warehousing

• Amazon Redshift is a fully managed data warehouse service by AWS for large-scale data storage and analytics.

Key Points

• Data warehouse service designed to handle and analyze petabytes of data
• Columnar storage leads to faster queries, especially for analytical workloads.
• Scale compute and storage resources independently.
• Uses PostgreSQL-compatible SQL for querying.
• AWS handles infrastructure management, backups, patching, etc.

Features

• Uses data compression, parallel processing, and query optimization
• Pay-as-you-go pricing and the ability to scale resources
• Integrates with AWS services like Amazon S3, AWS Glue, and AWS Lambda
• Supports VPC, encryption (in-transit and at-rest), and IAM roles for access control.
• Automated backups and point-in-time recovery.

Advantages

• Optimized for analytical workloads, delivering fast results for large datasets.
• Adjust compute and storage resources as needs grow
• AWS manages maintenance, patches, and backups
• Seamlessly integrates with other AWS services.
• Robust security features like encryption, IAM roles, and VPC support.

Disadvantages

• Complex pricing, with additional costs for storage, backups, and data transfer.
• Performance may drop with complex queries or very large tables unless optimized.
• Optimizing queries and schema design for best performance adds complexity.
• Designed for OLAP (analytical) workloads, not transactional ones.

Redshift Cluster

• Consists of a collection of nodes and storage layers, spanning multiple availability zones for HA
• Specifies configurations like node type, the number of nodes, and the storage capacity required.
• Leader node manages query coordination and distributes queries to compute nodes, without processing data itself.
• Compute nodes perform the data processing and store the data, with tasks distributed by the leader node.

Nodes in Redshift

Types of Nodes:

• Each node is a virtual machine that performs data processing and stores data

Leader Node:

• Coordinates query execution and manages communication between compute nodes.
• Receives, parses, compiles, and distributes queries to the compute nodes, then aggregates results.
• Stores metadata and coordination data, not actual data.

Compute Nodes:

• Perform data processing tasks and store data.
• Contains a subset of the total data, processed in parallel for faster query performance (MPP architecture).
• Data is distributed using key, even, or all distribution styles.

Node Types:

• Node types relate to hardware configuration

Dense Compute (DC) Nodes:

• High-performance, memory-optimized nodes for workloads requiring high computational power.
• Suited for analytical queries.

Dense Storage (DS) Nodes:

• Cost-effective, storage-optimized nodes for data storage with lower computational power.
• Suitable for large datasets that do not require high processing power.

RA3 Nodes:

• Separate compute and storage scaling, allowing compute to scale independently from storage.
• More cost-effective for large, variable workloads.

Node Architecture and Distribution

Data Distribution:

• Redshift uses a distributed storage architecture to divide data across compute nodes, called slices.
• Each slice is responsible for a subset of the data, processed in parallel for faster processing.

Distribution Styles:

Key Distribution:

• Data distributed based on the value of a specified column (distribution key).
• Useful for tables frequently joined on that column.

Even Distribution:

• Data distributed evenly across all nodes.
• Useful when there is no obvious column for distribution.

All Distribution:

• A copy of the entire table is stored on each node.
• Best for small lookup tables that don’t change frequently.

Slices:

• Each node is divided into smaller units called slices.
• The number of slices depends on the node type and size (e.g., DC2.large has 2 slices per node, DC2.8xlarge has 32 slices per node).
• Each slice holds part of the data and performs computations in parallel.

Query Execution Flow

• The leader node receives the query and breaks it into smaller tasks.
• The leader node sends tasks to the relevant compute nodes.
• Compute nodes process the data stored on them, using parallel processing across slices.
• The leader node collects the results from the compute nodes, aggregates them, and returns the final result to the client.

Scaling

• Elastic Resize: Add/remove compute nodes or switch node types.
• Storage Scaling: RA3 nodes allow independent scaling of storage.

Data Redundancy & Fault Tolerance

• Replication: Data is replicated across nodes.
• Backups: Automated snapshots.

Redshift Cluster and Nodes Summary

• Leader Node: Coordinates query execution and doesn't store data.
• Compute Nodes: Perform data processing and store data.
• Distributed Data Storage: Data is stored into slices.
• Node Types: RA3 (separate compute and storage), DC (dense compute or storage), and DS for different use cases.
• Scalability: Clusters can be resized, and storage can scale independently.
• Clusters and nodes use a distributed, massively parallel architecture for fast query performance on large datasets.

Resizing Methods

• The below methods allow the cluster's configuration to meet evolving workload requirements

Classic Resize (Traditional):

• Performs a simple resize by adding or removing nodes and redistributing the data.
• Useful for smaller to medium-scale changes requiring performance improvements.
• Simple and straightforward, allowing adjustment of node count or type.
• Slower compared to Elastic Resize, requires a full cluster reboot and data redistribution, with potential downtime.

Elastic Resize

• Quickly adds or removes nodes from the cluster without a full reboot.
• Faster and more flexible for temporarily adjusting compute capacity for variable workloads.
• Faster than Classic Resize, with no downtime for cluster operations.
• Limited to modifying the number of nodes and not changing node types.

Concurrency Scaling

• Adds extra compute capacity automatically for query execution spikes without resizing the main cluster.
• Useful for occasional workload spikes or heavy querying exceeding normal capacity.
• Scales automatically, eliminating manual resizing.
• Billed separately based on the number of clusters and usage.

Resize using Redshift Spectrum

• Scales storage independently by querying data in Amazon S3.
• Useful for offloading large datasets to S3 while keeping query processing in Redshift.
• Scales storage without resizing compute capacity.
• Slightly more complex setup.

Snapshot and Restore

• Resizes by taking a snapshot and restoring it to a new cluster configuration.
• Useful for making large changes, especially when changing node types and sizes.
• Allows changing both node types and sizes.
• Requires downtime and is time-consuming compared to other methods.

Conclusion of Resize Methods

• Elastic Resize is fast and ideal for quick compute adjustments.
• Classic Resize is better for significant, long-term scaling.
• Concurrency Scaling and Redshift Spectrum valueable for handling sudden spikes or scaling storage.
• Snapshot and Restore is complex but allows for substantial configuration changes.

Snapshots in Redshift:

• Used for recovery, data protection, and disaster recovery.

Types

• Automated Snapshots: Taken automatically based on a schedule.
• Manual Snapshots: User-initiated backups, retained until manually deleted.

Features:

• Incremental backups store only changes since the last snapshot.
• Cluster Restoration: Restore a cluster to a previous point-in-time state.
• Cross-region snapshots: Copy snapshots to different AWS regions for better data durability.

Costs:

• Storage used by the snapshots is charged.

Snapshot Sharing in Redshift

• Sharing snapshots between AWS accounts or clusters supports collaboration, migration, and data sharing.

How it Works

• Share a snapshot by granting access to a specific AWS account.
• The recipient can restore the snapshot into their own Redshift cluster.

Limitations:

• Manual snapshots can be shared.
• Sharing works only within the same region.
• Encryption: The recipient needs permission to decrypt the snapshot.

Costs:

• No cost for sharing, but the recipient may incur costs for storing the shared snapshot.

Redshift Distribution Keys and Styles

• They control how data is distributed across the nodes, optimizing query performance and minimizing data movement.

Distribution Keys (DISTKEY)

• Determines how data is distributed across compute nodes.
• Impacts query performance by minimizing data shuffling during joins.
• When a table is created with a distribution key, Redshift distributes rows based on the values of that column.
• Useful for tables frequently in joins.

Types:

• KEY: Distributes data based on a column's values.
• EVEN: Distributes rows evenly across all slices.
• ALL: Distributes a copy of the entire table to each slice.

Distribution Styles

KEY Distribution:

• Ensuring rows with the same key value are stored together
• Data is distributed based on the values of the distribution key column.

EVEN Distribution:

• Best for large, independent tables where there is no clear column for frequent joins.
• Ensures an even distribution of data across all nodes.
• Data is distributed evenly.

ALL Distribution:

• Best for small dimension tables joined with large fact tables.
• Copies the entire table to all slices.

Summary of Distribution Styles:

• KEY: Best for join optimization.
• EVEN: Best for large, independent tables.
• ALL: Best for small dimension tables.

Choosing Distribution Style:

• Use KEY when you have large tables that are joined frequently on a specific column.
• Use EVEN for large tables where no single column is frequently used for joins.
• Use ALL for small dimension tables to avoid shuffling during joins.
• Selecting keys and styles helps reduce query time and improves performance.

Amazon Redshift VACUUM

• Used to reclaim space, sort data, and improve query performance.
• Optimizes the storage and performance of a Redshift cluster.

Purpose:

• Reclaim space: The VACUUM operation reclaims unused space from deleted or updated data.
• Sort data: It restores the sort order of rows to match the sorting scheme of the table.

Key Details of VACUUM in Redshift

• When updating/deleting rows the old versions are not removed leading to space that affects query performance.
• Tables in Redshift are stored in sorted order based on the sort keys, which may degrade over time.

What VACUUM Does:

• Reclaims space by removing dead rows.
• Restores sort order.
• Rebuilds the table's metadata.

VACUUM Process:

• Full Vacuum: Reclaims space and sorts the entire table.
• Sort Only Vacuum: Restores the sort order without reclaiming space.
• Delete Only Vacuum: Reclaims space without restoring the sort order.

VACUUM Types:

• Full VACUUM: Cleans up space and restores sort order for the entire table.
• Sort Only: Only restores the sorting of the table.
• Delete Only: Only reclaims space - deleting unused rows.

Performance Impact:

• VACUUM operations can be heavy on resources (CPU, disk I/O) and can impact query performance during execution.

How to Run VACUUM:

• Manually trigger a vacuum operation using SQL.
• Enable the automatic vacuum feature that runs in the background.

VACUUM Frequency:

• The more frequent data is updated or deleted, the more often you'll need to run VACUUM.
• Schedule vacuum operations during low peak times.

Considerations for VACUUM:

• Redshift has an automatic vacuum process, but manual operations are also supported.
• Tables that have sort keys benefit from vacuuming.

Conclusion:

• In summary, in Amazon Redshift VACUUM is a crucial maintenance operation.
• Reclaims space by removing deleted rows.
• Restores data to the correct sort order to improve query performance.
• To keep a Redshift cluster optimized, regular operations is essential.

Integrations in Amazon Redshift

• Designed to handle large-scale data storage and analytics.
• Ability to integrate with a wide range of AWS services, third-party tools, and other systems.

Data Integration and ETL

• AWS Glue, an ETL service automating the process of preparing data for analytics.
• Redshift Spectrum runs queries on data stored in S3 without loading it into the Redshift cluster.
• AWS Data Pipeline automates the movement and transformation of data between different AWS services.
• Third-Party ETL Tools like Apache NiFi, Talend, Informatica, and Matillion are used for advanced data transformations.

Data Loading and Migration

• Amazon S3 integrates with Redshift for fast bulk data loading using the COPY command.
• AWS DMS migrates data from various source databases into Redshift.
• Amazon Kinesis Data Firehose streams real-time data into Redshift.

Analytics and BI Tools Integration

• Amazon QuickSight helps visualize and analyze Redshift data.
• Third-Party BI Tools integrates via JDBC and ODBC connections.

Machine Learning Integration

• Amazon SageMaker integrates with SageMaker to bring machine learning models and predictions into Redshift.
• Amazon Redshift ML integrates directly with Amazon Redshift to create machine learning models using SQL.

Security and Compliance Integrations

• AWS Identity and Access Management (IAM) controls access to clusters, databases, and objects.
• AWS Key Management Service (KMS) integrates with AWS KMS to provide encryption at rest.
• Amazon CloudWatch integrates with CloudWatch for logging and monitoring.

Redshift Data Sharing

• Amazon Redshift enables data sharing across different Redshift clusters.

AWS Lambda Integration

• Integrates with Redshift to trigger specific actions based on events in your Redshift cluster.

AWS CloudTrail Integration

• Integrates with Redshift to track and log all user activities and API calls.

External Data Sources and Federated Querying

• Can query data from external sources without needing to load the data into Redshift.

Summary of Integrations in Redshift

• It integrates deeply with a wide array of AWS services, third-party tools, and other databases.

COPY and UNLOAD

• COPY is used to load data from S3 to Redshift, typically for bulk data ingestion.
• UNLOAD is used to export data from Redshift to S3, often for data archiving, reporting, or sharing with other systems.
• Proper configuration of these commands ensures smooth data integration.

ETL vs ELT

ETL

• Extract → Transform → Load.
• Transformation Location: outside the target system.
• Speed of Loading: Slower
• Target System Load: Low
• Complexity: More complex process.
• Data Storage: Data is stored in a cleaned and transformed state.
• Best For legacy systems, complex transformation logic.

ELT

• Extract → Load → Transform.
• Transformation Location: inside the target system.
• Speed of Loading: Faster.
• Target System Load: High.
• Complexity: Simpler, but may require more resources in the target system.
• Data Storage: Raw data is first.
• Best For modern cloud-based data warehouses and lakes.