Day 3 - DynamoDB (Analytics Perspective): Key Design and Stream-Based Pipelines

📌 Key Takeaways

• DynamoDB is a key-value store that splits data by the hash of the partition key. A lookup by key takes milliseconds, but a Scan, which sweeps without a key, reads the whole table and consumes capacity accordingly.
• The throughput of a single partition has an upper limit. When traffic piles onto one key, it becomes a hot partition and is throttled, so use an evenly distributed key and, when needed, spread writes with write sharding.
• A GSI re-indexes the whole table under a different key (eventually consistent, can be added after creation). An LSI is a different sort key on the same partition key (defined only at creation, strongly consistent reads possible).
• Do not run bulk analytics on the table; send the data out: DynamoDB Streams or Kinesis Data Streams for real-time changes, S3 export for a full snapshot, and zero-ETL for warehouse replication with no operational burden.
• Streams offers 24-hour retention, no duplicates, and ordering guaranteed per item. Kinesis Data Streams offers up to 1 year of retention and more consumers, but duplicates and out-of-order records are possible.

DynamoDB Basic Model

Amazon DynamoDB is a fully managed, serverless NoSQL key-value and document database. It is fundamentally built for OLTP, but data engineers need to know it for two reasons: how to pull data out as a source of operational data, and how to design it as a state store and lookup store for pipelines.

Data is organized as table → item (row) → attribute (column). An item is identified by its primary key.

• Partition key (PK): It is hashed and determines the physical partition where the item is stored.
• Sort key (SK): It orders the items within the same partition key. The PK + SK combination is a composite primary key.

PK = USER#1001     SK = ORDER#2026-06-26#A   amount=120
PK = USER#1001     SK = ORDER#2026-06-26#B   amount=80
PK = USER#1001     SK = PROFILE              name="Kim"

• Attributes other than the keys have no schema. Each item may have different attributes. The size of a single item has an upper limit (400 KB).
• A group of items that share the same PK is called an item collection. In the example above, the orders and the profile of USER#1001 form one collection, fetched at once with a single PK.

Three Ways to Read

• The FilterExpression of a Scan is a filter that discards after reading. Even if the result is 10 items, the read capacity for the entire table is consumed. This is the answer to "why is it expensive even though I added a filter?"
• Query and Scan return at most 1 MB at a time, and beyond that you continue by fetching pages.

💡 Related Theory: DynamoDB designs its keys after deciding the access patterns first (access-pattern-first). A relational database normalizes the schema and writes queries later, but in DynamoDB the list of "what will be retrieved by which key" is the schema. Questions that were not on the list (arbitrary column aggregation, joins) can only be answered with a Scan, and that is why analytics is sent outside.

Capacity Modes and Billing Units

• One read unit = 1 strongly consistent read of an item up to 4 KB, or 2 eventually consistent reads. One write unit = 1 write of an item up to 1 KB. If an item is larger, it is rounded up in units of 4 KB and 1 KB and costs more.
• So a single strongly consistent read of a 20 KB item is 5 units. If you Scan the whole table for analytics, "table size ÷ 4 KB" read units are consumed.
• A new on-demand table accepts up to 4,000 writes and 12,000 reads per second. If you exceed 2x the previous peak within 30 minutes, throttling can occur.
• Switching from provisioned to on-demand is allowed up to 4 times in 24 hours.

Key Design and Hot Partitions

A single partition of a table is designed to deliver up to 3,000 read units and 1,000 write units per second. No matter how large the total table capacity is, traffic piled onto one partition key runs into this limit.

• Even distribution: For the PK, choose an attribute with many distinct values whose traffic spreads evenly (userId, deviceId). Attributes with few or skewed values, such as date, status, or country, make bad PKs.
• Write sharding: For a key that is unavoidably skewed, add a suffix to divide it across several partitions. When reading, you must query all the suffixes and combine them.
• Single-table design: Put several entities in one table and distinguish them with PK/SK prefixes. There are no joins, but related data is fetched with a single Query.
• Adaptive capacity returns spare capacity to a skewed partition, but it cannot lift the per-partition limit itself.

-- Anti-pattern: using only the date as the PK for a time series concentrates that day's traffic on one partition
PK = 2026-06-26
 
-- Write sharding: spread across N partitions with a suffix (when reading, query all of 0 to N-1)
PK = 2026-06-26#03
 
-- A more common solution: device ID as the PK, timestamp as the SK
PK = DEVICE#8821    SK = 2026-06-26T10:15:00Z

GSI and LSI

To query by an attribute that is not the primary key, you need a secondary index.

-- GSI example: look up orders by status
GSI: PK = status (SHIPPED/PENDING), SK = order_date
→ Supports a new access pattern such as "PENDING orders in date order"

• When you write to the table, the GSI follows asynchronously. It is usually within 1 second, but a value you just wrote may not show up in a GSI query.
• If the GSI's write capacity is insufficient, writes to the base table are throttled too. In provisioned mode, set the GSI write capacity at or above the table's. This is a common cause of "the table has spare capacity but writes are throttled."
• Projection: You choose which attributes to copy into the index. KEYS_ONLY (keys only), INCLUDE (adds the attributes you specify), ALL (everything). A GSI query cannot fetch attributes that are not projected, and ALL increases storage and write cost.
• Sparse index: Items that lack the index key attribute are not placed in the index. It is a technique for cheaply querying a subset such as "only those awaiting processing."
• If you use status as the PK of a GSI as in the example above, the GSI itself becomes a hot partition. An index follows the same distribution rules as the table.

💡 Related Theory: A GSI is a tool for "creating one more lookup axis that you know in advance," not an analytics tool. If you add a GSI every time an aggregation axis is added, each write incurs a cost per index, and you still cannot do SUM or GROUP BY. Questions whose lookup axes keep changing are the job of a columnar analytics store.

Change Capture: Streams and Kinesis Data Streams

DynamoDB emits item-level changes (INSERT, MODIFY, REMOVE) as a stream. There are two options, and you can turn on both for one table.

What the stream holds is decided by the view type.

• The view type cannot be changed after the stream is created. To change it, turn the stream off and create a new one.
• A stream is written asynchronously, so turning it on does not affect table performance.
• Lambda receives records in batches, in order, for each shard. If the function fails, it cannot move on to the next record of that shard, so retry limits, batch splitting, and failure destination settings are needed exactly as with Kinesis.
• Deletions of items expired by TTL also appear in the stream (marked as service deletions rather than user deletions). A TTL deletion does not happen the instant an item expires but in the background, and it does not consume write capacity. "Archive expired items to S3" is the classic use of Streams + Lambda.

DynamoDB table
   ├─ DynamoDB Streams ──► Lambda ──► transform ──► S3 / OpenSearch / another table
   └─ Kinesis Data Streams ──► Data Firehose ──► S3 (Parquet) ──► Athena / Redshift Spectrum
                           └─► Managed Service for Apache Flink (real-time aggregation)

Exporting the Whole Table: S3 Export and Other Methods

• S3 export requires PITR (point-in-time recovery) to be turned on. Because it reads from the backup, it does not use read capacity and does not affect table performance. The output format is DynamoDB JSON or Amazon Ion, and it can export to a bucket in another account or another Region.
• The export result is not Parquet. If you will query it repeatedly with Athena, convert it once to Parquet with Glue or CTAS.
• An export is an asynchronous job with no guaranteed completion time. Do not build a pipeline on the assumption that "it finishes within a few minutes."
• Zero-ETL requires PITR on the source table, and in Redshift the items arrive in a SUPER type column. The minimum latency is 15 minutes, so if you need second-level freshness, a stream is the answer.
• Reading directly with Scan competes with production traffic for read capacity. If the question says "without affecting the production table," it is export.

# Export the table at a point in time from the PITR backup to S3
aws dynamodb export-table-to-point-in-time \
  --table-arn arn:aws:dynamodb:ap-northeast-2:111122223333:table/Orders \
  --s3-bucket acme-datalake-raw-prod \
  --s3-prefix dynamodb/orders/ \
  --export-format DYNAMODB_JSON

When DynamoDB Fits and When It Does Not

Operational (OLTP): DynamoDB  →  change capture (Streams/Kinesis) or snapshot (Export)  →  analytics (OLAP): S3 + Athena / Redshift

This is the data engineering version of CQRS (Command Query Responsibility Segregation). Writes and key lookups go to DynamoDB, and analytics and aggregation go to a columnar store, so each does what it is good at. For the link between the two, use a stream if it must be real time, an export if it is periodic, and zero-ETL if you want to hand it over to a managed service.

Symptom → Cause → Action

Question Clue → Answer Lookup Table

Tomorrow we cover RDS/Aurora, relational OLTP stores, along with zero-ETL and the criteria for choosing a store by looking at the workload.

📖 Glossary

• Partition key / sort key: The key that decides the partition where an item is placed, and the key that decides the order within it.
• Item collection: A group of items that share the same partition key.
• Hot partition: A phenomenon in which traffic piles onto one partition key and only that partition is throttled.
• Write sharding: A technique that adds a suffix to a skewed key to divide it across several partitions.
• RCU / WCU: Read and write capacity units. Reads are calculated in 4 KB units and writes in 1 KB units.
• GSI / LSI: A global secondary index that indexes the whole table by a different key, and a local secondary index that gives a different sort key on the same partition key.
• Projection (index): The range of attributes copied from the table into the index.
• DynamoDB Streams: A change log that keeps item-level changes in time order for 24 hours.
• PITR: Point-in-time recovery. It maintains continuous backups so that you can restore or export from any past point in time.
• TTL: A feature that lets you specify an expiration time on an item so that it is deleted automatically.