Amazon Redshift

Redshift is a fully managed, scalable data warehouse designed and optimized for running complex analytical queries on large structured data. It is not just storage, but also a powerful platform for data processing and querying, enabling complex analytics on vast amounts of data.

Key Points

  1. Redshift Stores structured data, optimized for fast retrieval and complex analytics. It uses columnar storage to handle large datasets efficiently.
  2. Redshift supports complex SQL queries, allowing for advanced analytics on petabytes of data. It is built to scale seamlessly, handling massive volumes of data with high performance.
  3. Redshift can integrate with the following AWS services for streamlined workflows, data loading, and visualization.
    1. Amazon S3(via Redshift Spectrum or COPY)
    2. Amazon DynamoDB
    3. Amazon QuickSight
    4. AWS Glue
    5. Kinesis
  4. Advanced Features:
    1. Machine Learning: Native ML integration for predictive analytics.
    2. Data Sharing: Enables cross-cluster analytics.
    3. Automatic Scaling: Adjusts to varying workloads, ensuring cost efficiency.
  5. Cost Consideration:
    1. Typically more expensive than Athena for intermittent or small workloads.
    2. You pay for provisioned clusters (compute + storage), even when idle — unless using Redshift Serverless, which charges per query/runtime.

1. Redshift COPY and Redshift Spectrum

  1. Redshift COPY Command
    • The COPY command is used to load data into a Redshift table from external sources like Amazon S3, DynamoDB, or local files.
    • Use when you want to bring external data into Redshift for structured analysis and long-term storage.
  2. Redshift Spectrum
    • Redshift Spectrum enables direct querying of data stored in Amazon S3 without the need to load it into Redshift tables.
    • Use when You want to analyze data in S3 without moving it into Redshift or You're performing ad hoc queries on data that doesn’t need to reside permanently in Redshift.

alt text

2. Data Lake vs. Data Warehouse

  1. Data Lake is used to store raw, unstructured, semi-structured, or structured data in its original format. It is ideal for big data, machine learning, and real-time analytics, and supports a variety of file types (JSON, CSV, video, images, etc.). Data Lake Examples:
    • Amazon S3
    • Azure Data Lake Storage
    • Google Cloud Storage
  2. Data warehouses is used to store structured, cleaned, and processed data, optimized for complex queries and analysis. It is ideal for business intelligence, operational reporting, and analytics. Data Warehouse Examples:
    • Amazon Redshift
    • Snowflake
    • Google BigQuery
    • Azure Synapse Analytics

Athena: Amazon Athena is neither a data warehouse nor a data lake itself — but it’s closely associated with data lakes. It is a serverless, interactive query service used to analyze data directly in Amazon S3 using standard SQL. It works on top of your data lake (usually stored in S3).