2.4.1.4. Data Warehousing and Analytics Design (Redshift, Athena, Glue, EMR)

2.4.1.4. Data Warehousing and Analytics Design (Redshift, Athena, Glue, EMR)

💡 First Principle: Building a purpose-built data platform that can efficiently store, process, and analyze large volumes of structured and unstructured data is essential for enabling actionable business insights.

Scenario: A large e-commerce company needs to analyze years of sales data for business intelligence reporting, requiring complex joins and aggregations over petabytes of historical data. Additionally, they need to perform ad-hoc queries on raw clickstream data stored in "Amazon S3" without loading it into a database.

Beyond transactional databases, AWS offers specialized services for data warehousing and analytics, crucial for business intelligence and large-scale data processing.

• "Amazon Redshift": A fast, fully managed, petabyte-scale data warehouse.
  • Why: Optimized for complex analytical queries ("OLAP") over large datasets. Ideal for business intelligence dashboards, reporting, and "ETL" workloads. Integrates with data lakes ("S3").
• "Amazon Athena": An interactive query service that makes it easy to analyze data directly in "Amazon S3" using standard "SQL".
  • Why: Serverless, pay-per-query. Ideal for ad-hoc analysis, querying "S3"-based data lakes without loading data into a database.
• "AWS Glue": A serverless data integration service ("ETL").
  • Why: Discovers, transforms, and prepares data for analytics. Includes a "Data Catalog" (metadata repository), "ETL engine" ("Spark"/"Python"), and crawlers. Essential for building data pipelines.
• "Amazon EMR (Elastic MapReduce)": A managed Hadoop framework for processing vast amounts of data using big data frameworks like "Spark", "Hive", "Presto".
  • Why: Provides flexibility and control over big data clusters. Ideal for complex data transformations, machine learning, and custom big data applications.
• "Amazon Kinesis": Services for real-time data streaming ("Kinesis Data Streams", "Firehose", "Analytics").
  • Why: Ingests and processes real-time data for immediate analytics, dashboards, and stream processing.

Visual: Data Warehousing & Analytics Ecosystem

⚠️ Common Pitfall: Using a transactional database (like "RDS") for large-scale analytical queries. Relational databases are row-based and not optimized for the columnar-style access patterns of analytics, leading to extremely slow and expensive queries on large datasets.

Streaming and Data Lake Design the Exam Tests:

• Kinesis Data Streams (KDS): Durable real-time ingestion. Data is split across shards; each shard accepts up to 1 MB/s or 1,000 records/s of writes and serves 2 MB/s of reads. Records with the same partition key go to the same shard, preserving order per key. A throttled producer is fixed by adding shards (or using on-demand mode, which scales automatically), not by retries alone or a bigger EC2 instance. Records are retained (24 hours by default, extendable up to 365 days), so consumers can replay, and multiple independent consumers each read the full stream at their own pace (enhanced fan-out gives each consumer dedicated throughput). Delivery is at-least-once, so consumers should be idempotent; it is not "exactly-once" to every consumer.
• KDS vs. SQS: Choose Kinesis for high-throughput streams, ordering per key, replay and many consumers of the same data. An SQS queue deletes a message once consumed, so multiple independent consumers need separate queues (typically via SNS fan-out).
• Amazon Data Firehose (formerly Kinesis Data Firehose): Fully managed delivery into S3, Redshift, OpenSearch and others, with optional Lambda transformation and a buffer (size/interval), so delivery is near real time (seconds to minutes) rather than true per-record real time. Typical clickstream pattern: Kinesis Data Streams for ingest and streaming processing, Firehose to land data in S3, Athena/Redshift/OpenSearch for analysis. Nightly batch loads or hourly SQL on an OLTP database are the "hours-late" pipelines this pattern replaces.
• Cross-account writers: A Kinesis data stream can carry a resource-based policy that names the producer accounts or roles as principals and grants only kinesis:PutRecord/kinesis:PutRecords (wildcard * principals are not accepted), so producers write with their own roles, whose IAM policies must also allow the put, without assuming a role in the stream's account. Granting only the put actions gives write access without read access.
• Lake Formation for data lake governance: "AWS Lake Formation" sits on the "Glue Data Catalog" and gives one place to grant database, table, column, row and cell-level permissions on S3 data (using data filters and LF-tags), enforced for "Athena", "Redshift Spectrum" and "EMR", and shareable across accounts (using "AWS RAM"). Plain S3 bucket sharing, per-account IAM policies and S3 Access Points cannot express column- or row-level rules.
• Making Athena faster and cheaper (you pay per data scanned): Convert data to a columnar format (Parquet or ORC), compress it, partition on commonly filtered columns (without splitting the data so finely that files stay tiny), and compact many small files into larger ones (small files add per-file overhead). Lifecycle deletion and storage-class tiering lower storage cost, not the data an individual query scans.
• Athena vs. Redshift: When complex multi-table joins over petabytes need consistent performance, a provisioned or serverless Redshift warehouse (which can also query S3 through Redshift Spectrum) outperforms ad-hoc Athena; Redshift is the managed replacement for a slow on-premises warehouse, whereas DynamoDB and Aurora are operational stores.

Key Trade-Offs:

• Managed Warehouse ("Redshift") vs. Serverless Query ("Athena"): "Redshift" provides consistent, high performance for well-defined, frequent analytical workloads but requires provisioning and managing a cluster. "Athena" is serverless and ideal for ad-hoc, infrequent queries on "S3", but performance can be less predictable and depends on data format and partitioning.

Reflection Question: How would you design a data analytics solution combining "Amazon Redshift" and "Amazon Athena" to meet both the structured data warehousing needs for sales data and the flexible ad-hoc querying requirements for raw clickstream data stored in "Amazon S3" for a large e-commerce company?