Why access patterns drive design in DynamoDB - Visual Breakdown

Concept Flow - Why access patterns drive design

Identify Access Patterns

↓

Design Table Keys

↓

Optimize Queries

↓

Efficient Data Retrieval

↓

Good Performance & Cost

↓

Repeat if needed

Start by knowing how you will access data, then design keys to match, so queries are fast and cost-effective.

Execution Sample

DynamoDB

Table: Orders
PK: CustomerID
SK: OrderDate
Query: Get all orders for a customer

Shows how designing keys based on access pattern (get orders by customer) helps efficient queries.

Execution Table

Step	Action	Input	Result	Notes
1	Identify access pattern	Get orders by customer	Access pattern defined	Focus on customer orders
2	Design primary key	PK=CustomerID, SK=OrderDate	Table keys set	Keys support query by customer
3	Query table	CustomerID=123	Returns all orders for customer 123	Efficient query using PK
4	Query with date range	CustomerID=123, OrderDate>2023-01-01	Returns recent orders	SK supports sorting/filtering
5	Try query by OrderDate only	OrderDate=2023-01-01	Fails or inefficient	No key supports this access pattern
6	Conclusion	Access patterns drive key design	Good performance only if keys match queries	Design for your queries

💡 Stop because queries not matching keys are inefficient or impossible

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
AccessPattern	undefined	Get orders by customer	Get orders by customer	Get orders by customer with date filter	Get orders by customer with date filter
PrimaryKey	undefined	PK=CustomerID, SK=OrderDate	PK=CustomerID, SK=OrderDate	PK=CustomerID, SK=OrderDate	PK=CustomerID, SK=OrderDate
QueryResult	none	none	All orders for customer 123	Filtered orders for customer 123	Filtered orders for customer 123

Key Moments - 2 Insights

Why can't we efficiently query orders by OrderDate alone?

Because the table's primary key is designed with CustomerID as the partition key, queries must include CustomerID to be efficient. Step 5 in the execution_table shows this query fails or is inefficient.

Why is the sort key OrderDate useful?

It allows sorting and filtering orders by date within a customer's orders, as shown in Step 4 where a date range query returns recent orders efficiently.

Visual Quiz - 3 Questions

Look at the execution_table, at which step do we set the table keys?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Check the 'Action' column for when keys are designed.

According to variable_tracker, what is the value of QueryResult after Step 3?

A All orders for customer 123

B none

C Filtered orders for customer 123

D undefined

💡 Hint

Look at the 'QueryResult' row under 'After Step 3' column.

If we wanted to query orders by OrderDate only, what would happen according to the execution_table?

A Query succeeds efficiently

B Query returns all orders

C Query fails or is inefficient

D Query returns no results

💡 Hint

See Step 5 in the execution_table for query by OrderDate only.

Concept Snapshot

Access patterns mean how you want to get data.
Design your table keys (partition and sort keys) to match these patterns.
This makes queries fast and cheap.
If keys don't match queries, queries fail or are slow.
Always start design by listing your access patterns.

Full Transcript

This visual execution shows why access patterns drive design in DynamoDB. First, you identify how you want to access your data, such as getting all orders for a customer. Then, you design your table keys to support that, for example using CustomerID as the partition key and OrderDate as the sort key. When you query by CustomerID, you get efficient results. You can also filter by OrderDate because it is the sort key. However, if you try to query by OrderDate alone, it fails or is inefficient because the keys don't support that pattern. This shows the importance of designing your table keys based on your access patterns to get good performance and cost.

Practice

1. Why is it important to design your DynamoDB table based on access patterns?

easy

A. Because it helps organize data for fast and efficient queries

B. Because it reduces the cost of storage space

C. Because it allows unlimited data size without limits

D. Because it automatically creates indexes for all queries

Solution

1. Step 1: Understand what access patterns mean

   Access patterns describe how your application will read and write data.

2. Step 2: Connect access patterns to table design

   Designing tables based on access patterns ensures queries are fast and efficient without scanning.

3. Final Answer:

   Because it helps organize data for fast and efficient queries -> Option A

4. Quick Check:

   Access patterns drive design = fast queries [OK]

Hint: Design tables by queries you need, not by data alone [OK]

Common Mistakes:

• Thinking storage size is the main factor
• Assuming DynamoDB auto-creates all needed indexes
• Ignoring how the app will query data

2. Which of the following is the correct way to define a primary key in DynamoDB to support a specific access pattern?

easy

A. PrimaryKey: { SortKey: 'UserId' }

B. PrimaryKey: { PartitionKey: 'OrderId', SortKey: 'UserName' }

C. PrimaryKey: { PartitionKey: 'Date' }

D. PrimaryKey: { PartitionKey: 'UserId', SortKey: 'OrderId' }

Solution

1. Step 1: Recall DynamoDB primary key structure

   Primary key can be simple (partition key only) or composite (partition + sort key).

2. Step 2: Check which option uses valid composite key

   PrimaryKey: { PartitionKey: 'UserId', SortKey: 'OrderId' } correctly defines partition key 'UserId' and sort key 'OrderId', matching common access patterns.

3. Final Answer:

   <code>PrimaryKey: { PartitionKey: 'UserId', SortKey: 'OrderId' }</code> -> Option D

4. Quick Check:

   Composite key needs PartitionKey + SortKey [OK]

Hint: Primary key needs partition key; add sort key if needed [OK]

Common Mistakes:

• Using sort key without partition key
• Confusing attribute names for keys
• Defining only sort key without partition key

3. Given a DynamoDB table with primary key { PartitionKey: 'CustomerId', SortKey: 'OrderDate' }, which query will efficiently retrieve all orders for customer '123' sorted by date?

medium

A. Query(TableName='Orders', KeyConditionExpression='OrderDate = :date', ExpressionAttributeValues={':date': '2023-01-01'})

B. Scan(TableName='Orders', FilterExpression='CustomerId = :cid', ExpressionAttributeValues={':cid': '123'})

C. Query(TableName='Orders', KeyConditionExpression='CustomerId = :cid', ExpressionAttributeValues={':cid': '123'})

D. Scan(TableName='Orders')

Solution

1. Step 1: Understand query vs scan in DynamoDB

   Query uses keys to efficiently find items; scan reads entire table and filters after.

2. Step 2: Match query to access pattern

   Query(TableName='Orders', KeyConditionExpression='CustomerId = :cid', ExpressionAttributeValues={':cid': '123'}) queries by partition key 'CustomerId' = '123', which is efficient and returns sorted by 'OrderDate'.

3. Final Answer:

   <code>Query(TableName='Orders', KeyConditionExpression='CustomerId = :cid', ExpressionAttributeValues={':cid': '123'})</code> -> Option C

4. Quick Check:

   Query by partition key = efficient retrieval [OK]

Hint: Use Query with partition key, not Scan, for fast results [OK]

Common Mistakes:

• Using Scan instead of Query for key-based retrieval
• Querying by sort key alone without partition key
• Not using KeyConditionExpression properly

4. You designed a DynamoDB table with primary key { PartitionKey: 'UserId' } but your app needs to query orders by OrderDate. What is the main problem and how to fix it?

medium

A. Problem: Table size is too big; Fix: Use smaller partition keys

B. Problem: Cannot query by OrderDate efficiently; Fix: Add OrderDate as sort key

C. Problem: UserId is missing; Fix: Add UserId as partition key

D. Problem: Query syntax error; Fix: Use Scan instead of Query

Solution

1. Step 1: Identify the limitation of current key design

   With only partition key UserId, you cannot efficiently query by OrderDate alone.

2. Step 2: Suggest design fix for access pattern

   Adding OrderDate as sort key allows queries by UserId and ordered by OrderDate efficiently.

3. Final Answer:

   Problem: Cannot query by OrderDate efficiently; Fix: Add OrderDate as sort key -> Option B

4. Quick Check:

   Sort key enables range queries by date [OK]

Hint: Add sort key for secondary query attribute [OK]

Common Mistakes:

• Trying to query by non-key attribute without index
• Using Scan instead of fixing key design
• Ignoring need for composite key for multiple access patterns

5. You have two access patterns: (1) Get all orders by CustomerId sorted by OrderDate, and (2) Get all orders by ProductId. How should you design your DynamoDB table to support both efficiently?

hard

A. Use primary key with PartitionKey=CustomerId and SortKey=OrderDate; create a Global Secondary Index with PartitionKey=ProductId

B. Use PartitionKey=ProductId and SortKey=OrderDate; create a Local Secondary Index with PartitionKey=CustomerId

C. Use PartitionKey=OrderDate only; scan table for CustomerId and ProductId queries

D. Use PartitionKey=CustomerId only; filter ProductId in application code

Solution

1. Step 1: Design primary key for main access pattern

   Primary key with CustomerId as partition key and OrderDate as sort key supports pattern (1) efficiently.

2. Step 2: Add Global Secondary Index (GSI) for second pattern

   GSI with ProductId as partition key supports pattern (2) without scanning the main table.

3. Final Answer:

   Use primary key with PartitionKey=CustomerId and SortKey=OrderDate; create a Global Secondary Index with PartitionKey=ProductId -> Option A

4. Quick Check:

   Primary key + GSI supports multiple access patterns [OK]

Hint: Use GSIs for additional access patterns beyond primary key [OK]

Common Mistakes:

• Using only one key and scanning for other queries
• Confusing Local Secondary Index with Global Secondary Index
• Not adding sort key for ordered queries