Day 5 - Week 7 Comprehensive Review: Analytics Stores Recap

📌 Key Takeaways

• The first question in choosing a store is the read pattern. If you read and write single records, it is RDS/Aurora or DynamoDB; if you aggregate a few columns across many rows, it is Redshift or Athena.
• The billing unit sets the direction of optimization. Athena and Spectrum bill by scan volume, so reading less is what counts; Redshift bills by compute time, so key design and concurrency management matter; DynamoDB bills by request units, so eliminating Scans is the cost saving.
• "Piling up on one side" has a different name in each service: distribution key skew in Redshift, hot partitions in DynamoDB, and the hot shard in Kinesis that we saw in Week 2. The prescription is always an evenly distributed key.
• Choose the path from operations to analytics by freshness and operational burden: Read Replica (immediate, same engine), zero-ETL (near real time, managed), DMS (wide combinations, CDC), streams (per event), export (snapshot), federation (no data movement).
• The tuning knobs are distribution and sort keys plus WLM for Redshift, partitions, formats, and workgroups for Athena, keys and indexes for DynamoDB, and readers and zero-ETL for Aurora.

Analytics Stores on One Table

This week we covered the analytics data stores (Redshift, Athena, DynamoDB, RDS/Aurora) and the choice of store by workload. Putting the four days of content on the same axes makes the differences clear at a glance.

💡 Related Theory: Columnar storage (Redshift, Parquet) is strong at "aggregating a few columns across many rows," and row-based storage (RDS, Aurora) is strong at "reading and updating all the columns of one row." DynamoDB is neither; it is strong at "straight to the answer if you know the key." Look at the verb in the question. Summing, trends, and reports point to columnar; orders, payments, and updates point to row-based; lookups, sessions, and state point to key-value.

The Same Problem Under Different Names: Skew

Distributed systems split data up and work on it in parallel. When the criterion for splitting is lopsided, parallelism breaks down. The skews we saw this week and in earlier weeks all have the same structure.

• Common principle: choose a key with many distinct values whose traffic and row counts spread evenly. A key with few values or one dominant value, such as status, country, or date, is bad everywhere.
• Difference: Redshift must use the same key as its join partner to eliminate redistribution, so it compromises between "even distribution" and "matching join keys." DynamoDB has no joins, so you only need to look at the access patterns.

The Same Problem Under Different Tools: Skipping

The way to make an analytics query fast and cheap is, in the end, not to read.

• All three stores have the trap of "reading everything even though you added a condition." In Redshift it is a filter on a column that is not the sort key, in Athena a filter on a column that is not the partition key, and in DynamoDB a filter on an attribute that is not a key (Scan).

The Billing Unit Sets the Direction of Optimization

• If the question says "minimize cost," first look at how often it is used. If occasionally, scan or request billing (Athena, on-demand, Serverless) is cheaper; if constantly, capacity billing (provisioned Redshift, DynamoDB) is cheaper.

From Operations to Analytics: Choosing the Path

For the requirement "I want to analyze OLTP data," there are several paths. Choose by freshness and operational burden.

"Must not put load on the operational DB"     → export, zero-ETL, read replica (direct Scan or federation causes load)
"Do not want to manage a pipeline"            → zero-ETL
"The engine is different / it is on-premises" → DMS (+ schema conversion)
"Complex transformation needed during replication" → Glue ETL
"Must react the moment a change occurs"       → stream + Lambda
"Look occasionally without moving the data"   → federated query

💡 Related Theory: "Keep operations (OLTP) and analytics (OLAP) on different engines" is the cardinal principle of data engineering. Only the means of connecting the two has changed. Going from hand-built ETL to DMS's CDC and then to managed zero-ETL reduced the operational burden. Even so, at the moment a transformation is needed, you still need Glue or SQL inside the warehouse. What zero-ETL removed is the management of E and L, not the need for T.

Telling Look-Alikes Apart at Once

Week 7 Numbers and Defaults on One Page

Integrated Scenario: Data Flow of a Commerce Service

Let us follow one company's store layout and attach the reason for each choice.

[Mobile app]
   ├─ Cart · sessions ───► DynamoDB (on-demand) ──Kinesis Data Streams──► Firehose ──► S3 (behavior log lake)
   └─ Orders · payments ─► Aurora (writer + reader)
                              ├─ reader ────────► customer service lookup screen
                              └─ zero-ETL ──────► Redshift ──► executive dashboard
                                                    ▲
S3 lake (3 years of logs, Parquet · date partitions) ──Spectrum──┘        Athena ──► data scientists' ad hoc analysis

• If the dashboard is hit at 9 AM, turn on concurrency scaling in Redshift. A short once-a-day peak often ends within the free credit.
• The order fact table in Redshift is frequently joined with the customer dimension, so customer_id is the DISTKEY, and because date range queries are common, the order date is the first sort key column. The small region dimension is ALL.
• On the data scientists' Athena workgroup, set a per-query scan limit to block a query that accidentally reads all 3 years.

Collection of Anti-Patterns

• Using DynamoDB as a reporting DB: Aggregating with Scan and filters burns read capacity. Export it with an export or a stream.
• Adding a GSI for every aggregation axis: Only the write cost goes up, and you still cannot aggregate.
• Running month-end settlement queries on the Aurora writer: Transactions slow down. Move them to a reader or Redshift.
• DISTSTYLE ALL on every table: Storage and load time are multiplied by the node count.
• A DISTKEY unrelated to the join key, or a skewed column: Redistribution or skew.
• Repeating single-row INSERTs into Redshift: Batch them and load with COPY.
• SELECT * on raw CSV in Athena: The most expensive combination.
• Keeping a provisioned cluster running constantly for occasional analytics: Athena or Redshift Serverless is the right fit.
• Hitting the operational DB with large hourly federated queries: Operational load accumulates. Switch to replication.

Scenario → Answer Lookup Table

Next week we move to the side that watches over pipelines: monitoring with CloudWatch metrics, logs, and alarms.

📖 Glossary

• Read pattern: The way a workload reads data. Whether it is per-record lookup, range aggregation, or search divides the stores.
• Billing unit: The basis on which a service sets its charges. Examples are scan volume, compute time, and request units.
• Skew (skew, hot partition): A phenomenon in which the values of the key used for distribution are lopsided, so load piles onto only some nodes or partitions.
• Data skipping: An optimization that uses statistics or path structure to avoid reading data that does not match the condition at all.
• Lakehouse: A configuration that queries warehouse tables and S3 lake data together.
• CQRS: A separation pattern that hands writes and per-record lookups, and analytics and aggregation, to different stores.
• Freshness: The time it takes for a value changed at the source to become visible on the analytics side.
• Fact table / dimension table: A large table where measurements accumulate, and the small reference tables that describe them.