Mastering DynamoDB for .NET Architects: Single-Table Design, Access Patterns, and Real-World Pitfalls

1 The Relational Detox: Shifting Mindsets for .NET Architects

SQL-trained .NET developers often struggle when they first touch DynamoDB. The discomfort isn’t about syntax. It’s about architectural expectations that have been formed over years of building systems around relational guarantees, joins, foreign keys, and normalized schemas. DynamoDB asks you to approach data from the opposite direction. And unless you shift that mindset early, it becomes difficult to design tables that scale beyond trivial workloads.

This section frames that shift. It walks through why DynamoDB behaves the way it does, how access-pattern-driven modeling works, and how Single-Table Design fits into modern .NET architectures.

1.1 The Cost of “Join” in a Distributed System

In SQL Server or PostgreSQL, a join is a local operation. All the data lives on one machine (or at most a small cluster with shared memory), and the query planner can determine a fast execution path by inspecting indexes and statistics. The latency is predictable and the compute boundaries are tight.

DynamoDB lives in a different world. It is a massively distributed key–value store where data is sharded across partitions that may be physically separated. Any operation requiring data from more than one partition violates DynamoDB’s core constraint: predictable single-digit millisecond latency at any scale.

A join would force DynamoDB to perform at least one cross-partition read. That introduces network latency, retry logic, and reassembly. And the cost grows out of proportion as your data set grows. DynamoDB’s designers removed joins entirely because the system’s performance promise is more important than relational flexibility.

For .NET architects, this means you can’t rely on the ORM to fix your query shape. Entity Framework hides the cost of joins, but DynamoDB exposes it because each item read is a discrete network call. If you design a schema that forces your API to perform multiple reads to hydrate one response, the system slows down—not because of DynamoDB, but because the model is relational in disguise.

The right question isn’t “How do I recreate relational joins in DynamoDB?” It’s “How do I store data so that a single partition lookup returns everything my API needs in one call?”

That question leads directly to access-pattern-driven design.

1.2 Access Patterns > Normalized Data

Relational modeling starts with normalization: break data apart to reduce redundancy and enforce constraints. You then write queries that reassemble the data by joining tables based on relationships.

DynamoDB reverses that sequence. You define your access patterns first:

“Get a user and their last ten orders.”
“List all items in this category sorted by price.”
“Fetch this cart with its line items.”

Only after you define these patterns do you design the schema. This inversion of control exists because DynamoDB optimizes for predictable key lookups, not ad-hoc querying. You store the data in the shape your application needs to read it. If a single read must return a dozen related objects, you colocate them under the same partition key.

Normalization becomes a spectrum, not a rule. Some duplication is fine if it eliminates additional reads. The real metric is read efficiency, not write compactness.

In .NET terms:

Relational thinking You store entities separately and let LINQ navigate between them. You trust the database to combine data on demand.

DynamoDB thinking You store data based on retrieval patterns. You design queries before you design entities. And you denormalize when needed to guarantee O(1) or O(log n) access time.

This mindset change is typically the hardest part for SQL-native developers.

1.3 Single-Table Design (STD) Philosophy

Single-Table Design often feels counterintuitive at first. SQL encourages multiple tables organized by entity. DynamoDB encourages one table organized by access patterns. It’s not an arbitrary rule—it’s a performance strategy.

1.3.1 Why put everything in one table?

A distributed datastore has two expensive operations:

Additional network calls
Cross-partition reads

Every time your .NET API needs to fetch more than one item to build a response, you pay both. Even with asynchronous calls, the latency adds up.

Single-Table Design reduces these costs because you’re intentionally grouping related data (even different entity types) under the same partition key. When done well, this ensures:

One query retrieves all required items.
DynamoDB reads from one partition.
Your API avoids multiple round trips.

For example, a partition containing:

PK = USER#123
SK = PROFILE
PK = USER#123
SK = ORDER#2023-10-01
PK = USER#123
SK = ORDER#2023-09-15

enables a “get user + recent orders” access pattern with a single query:

var request = new QueryRequest
{
    TableName = "AppTable",
    KeyConditionExpression = "PK = :pk",
    ExpressionAttributeValues =
    {
        [":pk"] = new AttributeValue("USER#123")
    }
};

This model scales because the shape of your query is constant. There is no equivalent to a join that grows slower as data grows.

1.3.2 The concept of Item Collections (Data locality)

An Item Collection is the set of all items sharing the same partition key. They sit together on disk. DynamoDB guarantees strong locality of data within that partition, and the query performance reflects that.

Item collections are the fundamental building block of Single-Table Design because they give you:

Fast, predictable access to related data.
Natural boundaries for 1:N relationships.
Efficient sorting using the sort key.

Architecturally, an item collection is the DynamoDB equivalent of a pre-joined dataset. You decide at write time what belongs together so reads stay efficient.

1.4 The Role of DynamoDB in a Modern .NET Stack

DynamoDB excels when you need predictable performance, simple operations, and rapid scaling with minimal overhead. .NET teams use it frequently in:

Good fit

High-traffic REST APIs
Multi-tenant SaaS platforms
Event-driven microservices
User-profile stores
Shopping carts, session data, stateful caches
Systems with large fan-out write loads

Poor fit

Ad-hoc reporting
OLAP workloads
Analytical queries requiring aggregations
Systems where business logic depends heavily on joins

DynamoDB is not a replacement for SQL Server. It’s a complement. In many architectures you pair DynamoDB with a data warehouse (e.g., Redshift or Synapse) for analytics and build your operational workloads on DynamoDB.

When .NET architects treat DynamoDB as a document store instead of an access-pattern engine, they struggle. But when you align your architecture with its constraints, it enables levels of horizontal scale that relational databases typically can’t handle without significant operational overhead.

2 Anatomy of the Schema: Keys, Indexes, and Attributes

This section moves from mindset to mechanics. DynamoDB’s schema is driven by keys, not rigid column definitions. The way you design those keys determines performance, cost, and scalability.

2.1 Primary Key Design Strategies

The primary key is the defining component of your table. DynamoDB supports two formats:

Partition Key only (simple key)
Partition Key + Sort Key (composite key)

Composite keys enable Single-Table Design because they allow you to group related data together and retrieve them efficiently.

2.1.1 Partition Key (PK) vs. Sort Key (SK)

The Partition Key (PK) determines which physical partition your data lives on. It must be designed with high cardinality and even distribution. If one key is accessed more frequently than others, you get a hot partition, which throttles the entire table.

The Sort Key (SK) organizes data within the partition. You can use it to:

Represent hierarchical relationships
Control sorting
Support range queries
Store multiple entities under one partition

Example of a composite-key structure:

PK	SK	EntityType
USER#123	PROFILE	User
USER#123	ORDER#2023-10-01	Order
USER#123	ORDER#2023-09-15	Order
ORDER#2023-10-01	ITEM#1	OrderItem

A typical .NET access pattern becomes a single efficient DynamoDB query:

var request = new QueryRequest
{
    TableName = "AppTable",
    KeyConditionExpression = "PK = :pk AND begins_with(SK, :skPrefix)",
    ExpressionAttributeValues =
    {
        [":pk"] = new AttributeValue("ORDER#2023-10-01"),
        [":skPrefix"] = new AttributeValue("ITEM#")
    }
};

This retrieves all items for an order, sorted by SK.

2.1.2 Cardinality and avoiding Hot Partitions

High cardinality means the number of distinct PK values must be large. DynamoDB distributes partitions based on these keys, so you want your workload spread evenly.

Avoid partition-key designs like:

TENANT#A for all users in one tenant
STATUS#PENDING for all pending orders
TYPE#PRODUCT for all products

These create hotspots under load.

Preferred designs use:

Natural identifiers (UserId, ProductId)
Composite synthetic keys (TENANT#123#USER#999)
Write-sharding patterns when necessary (e.g., adding random suffixes)

Always evaluate cardinality early. Fixing this later requires a data migration, not a simple index modification like in SQL.

2.2 Generic Key Naming

A common mistake is naming keys after entity types:

UserId
OrderId
ProductId

This approach makes Single-Table Design almost impossible because each entity now “owns” its own table-like identity. DynamoDB schemas become inflexible and expensive to evolve.

Instead, use:

PK
SK
GSI1PK
GSI1SK

This removes semantic coupling and lets you store any entity in any combination without renaming columns. The item itself carries its meaning through attribute values, not the key names.

Example:

PK	SK	EntityType	…
USER#123	PROFILE	User
USER#123	ORDER#555	Order

The .NET code accessing those values stays generic and stable:

var pk = $"USER#{userId}";

Generic key naming is one of the most important long-term design decisions because it avoids schema rigidity.

2.3 Composite Keys and Key Overloading

Key overloading is the practice of storing multiple entity types using similar PK/SK patterns. This helps you build rich item collections.

Examples:

User partition

PK = USER#123
SK = PROFILE
SK = ORDER#2023-01-01
SK = ORDER#2023-02-15

Order partition

PK = ORDER#2023-02-15
SK = HEADER
SK = ITEM#1
SK = ITEM#2

This allows:

Fetching all user-related items in a single query
Fetching order details and line items together
Navigating from one entity to another via indexes

In .NET code, constructing these keys is straightforward:

public static class Keys
{
    public static string UserPk(Guid userId) => $"USER#{userId}";
    public static string UserProfileSk() => "PROFILE";
    public static string UserOrderSk(DateTime dt) => $"ORDER#{dt:yyyy-MM-dd}";
    public static string OrderPk(Guid orderId) => $"ORDER#{orderId}";
}

You encapsulate key conventions instead of scattering strings throughout your codebase.

2.4 Global Secondary Indexes (GSI)

GSIs allow queries based on alternate keys. They are essential for many-to-many relationships and reverse lookup patterns.

2.4.1 Inverted Indexes vs. Adjacency Lists

Inverted Index You flip PK and SK to look up items in reverse order.

Example use case: Fetch all orders by status.

Adjacency List Store additional items that represent relationships.

Example use case: A product appears in many categories.

Choosing between them depends on access patterns:

Use inverted indexes when you need an alternate natural hierarchy.
Use adjacency lists when no natural hierarchy exists.

2.4.2 GSI Overloading

Just like PK and SK, avoid naming a GSI’s keys after entity types. Use:

GSI1PK
GSI1SK

This lets you store multiple lookup strategies in the same index, reducing the number of indexes and lowering cost.

Example:

PK	SK	GSI1PK	GSI1SK
ORDER#1	HEADER	STATUS#PENDING	DATE#2024-01-01
ORDER#2	HEADER	STATUS#SHIPPED	DATE#2024-01-02
ORDER#3	HEADER	STATUS#PENDING	DATE#2024-02-01

Query all pending orders with:

var request = new QueryRequest
{
    TableName = "AppTable",
    IndexName = "GSI1",
    KeyConditionExpression = "GSI1PK = :status",
    ExpressionAttributeValues =
    {
        [":status"] = new AttributeValue("STATUS#PENDING")
    }
};

One index, many use cases.

2.4.3 Sparse Indexes

A sparse index contains only items that define the indexed attributes. This is an advantage, not a limitation.

Use sparseness intentionally for:

“Only active items” queries
“Only orders with a due date” queries
“Only items with a secondary relationship” queries

Sparse indexes are often the cleanest way to express filtering in DynamoDB because they don’t consume RCUs for items your query doesn’t care about.

2.5 Managing Large Attributes

DynamoDB can store up to 400 KB per item. Large attributes create problems:

They increase RCU/WCU costs
They slow down network transfer to your .NET service
They reduce cache efficiency in DAX or Redis
They increase storage cost

Strategies

1. Store large JSON blobs only when they are always needed If a blob is accessed frequently together with the item, it belongs in the same item.

2. Offload infrequently accessed data to S3 Store only metadata in DynamoDB and keep the large content in S3.

3. Use compression in .NET You can compress large JSON attributes before writing:

using var output = new MemoryStream();
using (var gzip = new GZipStream(output, CompressionLevel.Optimal))
{
    await JsonSerializer.SerializeAsync(gzip, inputObject);
}
var compressed = Convert.ToBase64String(output.ToArray());

4. Break the item using an ITEM#CHUNK pattern For extremely large records, store multiple chunk items under the same PK.

3 Practical Modeling: The SaaS E-Commerce Scenario

This section applies the earlier principles to a concrete example: a multi-tenant SaaS order management system. The goal is to model realistic access patterns, map traditional entities into a DynamoDB-compatible structure, and show how a single-table schema supports common workflows without introducing new joins or extra reads. Throughout these examples, the table name remains generic, and key naming stays consistent with earlier conventions.

3.1 Defining the Access Patterns

Real systems rarely revolve around entities; they revolve around what the application needs to do. The most effective DynamoDB models start with explicit access patterns that define how the API retrieves data. For this SaaS application, four patterns cover most of the operational behavior.

Get User Profile

Each tenant has many users. The system needs a fast lookup for the user’s profile, preferences, or roles. This pattern maps cleanly to querying a single partition keyed by a combination of tenant and user IDs.

Example read:

var request = new GetItemRequest
{
    TableName = "AppTable",
    Key = new Dictionary<string, AttributeValue>
    {
        ["PK"] = new AttributeValue($"TENANT#{tenantId}#USER#{userId}"),
        ["SK"] = new AttributeValue("PROFILE")
    }
};
var response = await client.GetItemAsync(request);

This lookup returns exactly one item. No secondary index is required, and the cost is minimal.

Get Recent Orders for a User

Users expect up-to-date order history. Storing order headers under the same PK as the user allows a range query over the SK, sorted by the order date embedded in the key.

A pattern might use SK values shaped like:

ORDER#2024-01-21T09:33:12Z
ORDER#2024-01-19T17:11:45Z

Get Order Details (including Line Items)

Orders often require more than one object. Headers, items, pricing adjustments, or shipment data all belong in one item collection. A single query using PK = ORDER#xyz retrieves everything without multiple round trips.

Get Orders by Status (for Admin)

Admins need a cross-user lookup. This cannot live under a user’s partition because it groups by status. A GSI supports this pattern by placing STATUS#{value} in GSI1PK.

Example admin query:

var request = new QueryRequest
{
    TableName = "AppTable",
    IndexName = "GSI1",
    KeyConditionExpression = "GSI1PK = :status",
    ExpressionAttributeValues =
    {
        [":status"] = new AttributeValue("STATUS#PENDING")
    }
};

This index contains only items with that status, making it sparse and cost-efficient.

3.2 Entity Relationship Diagram (ERD) to Access Pattern Map

Relational diagrams usually show connections: users have orders, orders have items, products belong to categories, and so on. With DynamoDB, these relationships matter only when tied to a read pattern.

A relational ERD might show:

Tenant → Users (1:N)
User → Orders (1:N)
Order → Items (1:N)
Product ↔ OrderItem (M:N)
Product → Inventory (1:1 per location)

When mapping to DynamoDB:

The User → Orders relationship drives placing orders under the user’s partition.
Order → Items drives putting line items under the order’s partition.
Products referenced across orders are handled through GSIs or adjacency items.
Multi-tenant boundaries shape the PK prefixes to ensure logical separation.

The ERD becomes a guide to determine which relationships belong in the same item collection and which require indexing instead of joins.

3.3 Designing the Partition Strategy

Multi-tenancy changes the shape of the keys. You need strong isolation to prevent cross-tenant queries but also need even distribution to avoid concentrating load on large tenants.

A common pattern prefixes every PK with the tenant ID.

Examples:

User profile item PK = TENANT#A1#USER#581, SK = PROFILE
User’s order PK = TENANT#A1#USER#581, SK = ORDER#2024-01-22T11:30:00Z
Order details PK = TENANT#A1#ORDER#3392, SK = HEADER
Order item PK = TENANT#A1#ORDER#3392, SK = ITEM#1

This design offers predictable locality and isolates tenant workloads. If a single tenant becomes extremely large, write sharding can be introduced without rewriting existing keys. But for most SaaS platforms, a tenant-aware PK prefix does the job.

3.4 Modeling One-to-Many (1:N)

Orders and line items illustrate 1:N relationships well. Instead of separate tables, all items for a single order live under a shared partition key.

Example structure:

PK	SK	EntityType	Data
TENANT#A1#ORDER#3392	HEADER	Order	{ … }
TENANT#A1#ORDER#3392	ITEM#1	OrderItem	{ … }
TENANT#A1#ORDER#3392	ITEM#2	OrderItem	{ … }

To fetch the order:

var request = new QueryRequest
{
    TableName = "AppTable",
    KeyConditionExpression = "PK = :pk",
    ExpressionAttributeValues =
    {
        [":pk"] = new AttributeValue($"TENANT#{tenantId}#ORDER#{orderId}")
    }
};
var response = await client.QueryAsync(request);

The result returns the header and all items sorted by SK. The application reassembles them, but the database work is trivial.

3.5 Modeling Many-to-Many (M:N)

Products can appear in many orders, and orders contain multiple products. Under SQL, this is a join table. Under DynamoDB, this becomes a combination of adjacency lists and GSIs depending on the direction of lookup.

Order → Products

Already handled through line item items.

Product → Orders

This requires a reverse lookup. For example:

PK	SK	GSI1PK	GSI1SK
TENANT#A1#ORDER#3392	ITEM#1	PRODUCT#P100	ORDER#3392
TENANT#A1#ORDER#4921	ITEM#3	PRODUCT#P100	ORDER#4921

Query all orders containing a specific product:

var request = new QueryRequest
{
    TableName = "AppTable",
    IndexName = "GSI1",
    KeyConditionExpression = "GSI1PK = :prod",
    ExpressionAttributeValues =
    {
        [":prod"] = new AttributeValue("PRODUCT#P100")
    }
};

This produces a list of orders without constructing expensive join tables or introducing extra read operations.

4 The .NET Tooling Landscape: SDKs and Libraries

.NET developers generally expect rich ORMs. DynamoDB supports some high-level abstractions, but they behave differently from Entity Framework. Choosing the right tooling determines whether your code remains predictable or works against the database model.

4.1 AWSSDK.DynamoDBv2 (Low-Level)

The low-level API exposes DynamoDB operations in their raw form. You pass dictionaries of AttributeValue objects and issue commands like PutItem, GetItem, or Query. Although verbose, this interface is the most predictable because it contains no reflection and no hidden serialization behavior.

Example write:

var item = new Dictionary<string, AttributeValue>
{
    ["PK"] = new AttributeValue($"TENANT#{tenantId}#USER#{userId}"),
    ["SK"] = new AttributeValue("PROFILE"),
    ["Name"] = new AttributeValue(user.Name),
    ["Email"] = new AttributeValue(user.Email)
};

var request = new PutItemRequest
{
    TableName = "AppTable",
    Item = item
};

await client.PutItemAsync(request);

You gain complete control over the attribute shapes, which is essential in Single-Table Design.

4.2 The DataModel (High-Level Object Persistence Model)

The OPM maps C# classes to DynamoDB items using attributes. It feels more like EF Core but comes with trade-offs that matter under high load.

4.2.1 Mapping C# Classes with Attributes

A simple class mapping example:

[DynamoDBTable("AppTable")]
public class UserProfile
{
    [DynamoDBHashKey("PK")]
    public string Pk { get; set; }

    [DynamoDBRangeKey("SK")]
    public string Sk { get; set; }

    public string Name { get; set; }
    public string Email { get; set; }
}

Saving the object:

var context = new DynamoDBContext(client);
await context.SaveAsync(userProfile);

This approach is straightforward but tightly couples your object model to the data model. In a single-table environment, this coupling often becomes restrictive when items share attributes but differ in structure.

4.2.2 The Performance Cost of Reflection in the OPM

Under load, reflection-based serialization introduces overhead. For systems expecting thousands of requests per second, the runtime cost becomes noticeable. The low-level API avoids this entirely, and libraries like EfficientDynamoDb improve performance by using compile-time generation.

For small or medium-scale systems, the high-level model may be acceptable. But once Single-Table Design introduces heterogeneous items, the benefits decline.

4.3 Handling Serialization

DynamoDB JSON differs from typical JSON because it requires type wrappers. C# serializers need to map values correctly without corrupting data or misinterpreting numeric formats.

4.3.1 `System.Text.Json` vs. `Newtonsoft.Json`

System.Text.Json is faster and more memory-efficient, but lacks built-in support for DynamoDB JSON structures. Newtonsoft.Json is more flexible and historically used with DynamoDB models.

A custom converter example:

public class DynamoDecimalConverter : JsonConverter<decimal>
{
    public override decimal Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
        => reader.GetDecimal();

    public override void Write(Utf8JsonWriter writer, decimal value, JsonSerializerOptions options)
    {
        writer.WriteNumberValue(value);
    }
}

This prevents decimals from being serialized incorrectly as doubles.

4.3.2 Handling Enums and DateTime in .NET

DynamoDB stores strings or numbers, not .NET enums. Always serialize enums explicitly:

[DynamoDBProperty]
public string Status { get; set; } // Use enum.ToString()

For dates, always store ISO 8601 text:

var dt = DateTime.UtcNow.ToString("o");

This allows lexicographic sorting and correct comparisons within SK or GSI keys.

4.4 Recommended Libraries

Several .NET libraries simplify DynamoDB work, each suited for different performance and development goals.

AWS SDK for .NET (Official)

It’s stable, well-documented, and fully supported. Most enterprise teams rely on it for production workloads, especially for low-level API access where control is needed.

EfficientDynamoDb

A community library that uses source generation to avoid allocations and reflection. Benchmarks show improvements in both throughput and latency compared to the DataModel. It also supports fluent query builders that reduce boilerplate code.

Example using EfficientDynamoDb:

var result = await client.Query<UserProfile>()
    .WithPk($"TENANT#{tenantId}#USER#{userId}")
    .ExecuteAsync();

This combines low overhead with a clean API surface.

5 Advanced Data Patterns and Writes

Write operations determine how reliable your system becomes under contention. DynamoDB provides several advanced features for conditional operations, atomic increments, and multi-item transactions. These features help maintain correctness without sacrificing performance.

5.1 Conditional Writes

Conditional writes enforce invariants, ensuring that updates occur only when expected state conditions hold true. They help prevent race conditions that create inconsistent data.

Example: decrement inventory only if stock is above zero.

var request = new UpdateItemRequest
{
    TableName = "AppTable",
    Key = new Dictionary<string, AttributeValue>
    {
        ["PK"] = new AttributeValue(productPk),
        ["SK"] = new AttributeValue("INVENTORY")
    },
    UpdateExpression = "SET Stock = Stock - :one",
    ConditionExpression = "Stock > :zero",
    ExpressionAttributeValues =
    {
        [":one"] = new AttributeValue { N = "1" },
        [":zero"] = new AttributeValue { N = "0" }
    }
};

await client.UpdateItemAsync(request);

If two requests race, only one succeeds, guaranteeing consistency.

5.2 Atomic Counters

Atomic counters allow increments without reading the item. This is useful for tracking metrics, sequence numbers, or request counts.

var request = new UpdateItemRequest
{
    TableName = "AppTable",
    Key = new Dictionary<string, AttributeValue>
    {
        ["PK"] = new AttributeValue(metricPk),
        ["SK"] = new AttributeValue("COUNTER")
    },
    UpdateExpression = "ADD Count :inc",
    ExpressionAttributeValues =
    {
        [":inc"] = new AttributeValue { N = "1" }
    }
};

await client.UpdateItemAsync(request);

This avoids conditional checks and minimizes latency.

5.3 Transactions (`TransactWriteItems`)

Transactions guarantee that multiple writes succeed or fail together. They help enforce integrity across multiple items, even when those items belong to different partitions.

5.3.1 ACID in DynamoDB

Example: create an order and decrement inventory atomically.

var transactRequest = new TransactWriteItemsRequest
{
    TransactItems = new List<TransactWriteItem>
    {
        new TransactWriteItem
        {
            Put = new Put
            {
                TableName = "AppTable",
                Item = orderHeader
            }
        },
        new TransactWriteItem
        {
            Update = new Update
            {
                TableName = "AppTable",
                Key = inventoryKey,
                UpdateExpression = "SET Stock = Stock - :one",
                ConditionExpression = "Stock >= :one",
                ExpressionAttributeValues =
                {
                    [":one"] = new AttributeValue { N = "1" }
                }
            }
        }
    }
};

await client.TransactWriteItemsAsync(transactRequest);

If inventory is insufficient, the entire transaction fails, preventing phantom orders.

5.3.2 Transaction Cost Considerations

Transactions consume twice the RCU/WCU of the underlying operations. They also introduce latency because DynamoDB must coordinate writes across partitions. Use them sparingly, ideally for workflows that truly require atomicity. For less rigid workflows, conditional writes often offer a lower-cost alternative.

5.4 Time To Live (TTL)

TTL automatically removes items after a specified epoch timestamp. This is useful for expiring login tokens, carts, or logs without writing cleanup tasks.

Example item attribute:

TTL = 1700000000

Setting TTL in .NET:

item["TTL"] = new AttributeValue { N = expiryEpoch.ToString() };

DynamoDB deletes the item asynchronously, meaning you should not rely on exact expiration timing. TTL helps reduce storage cost and simplifies operational maintenance for data that doesn’t need long-term retention.

6 Querying, Pagination, and Streams in C#

Reading data efficiently determines how well your .NET services scale under load. DynamoDB offers fast lookups when you design the query paths intentionally. This section focuses on the practices that keep performance predictable while handling real workloads such as large lists, search-like operations, and event propagation through streams.

6.1 Query vs. Scan

Query uses the table’s key structure to retrieve only the relevant items. Scan inspects every item in the table or index and discards most of them. The difference is not subtle. A Query call touches a small, targeted section of a partition, while a Scan call pulls on every partition, consuming capacity and increasing latency.

In production, any Scan that returns more than a handful of items usually indicates that the access pattern is missing an index. A common mistake is performing a Scan with a FilterExpression just to find a specific type of item. That pattern loads entire segments from disk before applying the filter. In contrast, a Query uses key conditions to limit reads to the items that are already located in the same partition.

A typical example retrieves recent orders for a user:

var request = new QueryRequest
{
    TableName = "AppTable",
    KeyConditionExpression = "PK = :pk AND begins_with(SK, :skPrefix)",
    ExpressionAttributeValues =
    {
        [":pk"] = new AttributeValue(userPk),
        [":skPrefix"] = new AttributeValue("ORDER#")
    }
};

var result = await client.QueryAsync(request);

No filtering is needed, and the cost is stable regardless of table size. This is the predictable performance model DynamoDB is designed for.

6.2 Effective Pagination with `LastEvaluatedKey`

Pagination in DynamoDB requires working with continuation tokens returned from the service. The LastEvaluatedKey represents the last item read and acts as the cursor for the next page. Offset-based pagination does not work because DynamoDB cannot “skip” items. It must read them, which introduces unnecessary load.

The key idea is to return both results and a cursor to the client. The client sends that cursor back when requesting the next page. You control the item count by setting Limit in the query request.

6.2.1 Implementing Cursor-Based Pagination in ASP.NET Core

A typical API might expose a query parameter such as nextToken. When submitting a request, the client includes this token if it wants the next page.

Example controller method:

[HttpGet("orders")]
public async Task<IActionResult> GetOrders(
    [FromQuery] string? nextToken,
    [FromServices] IAmazonDynamoDB db,
    [FromQuery] int pageSize = 20)
{
    var request = new QueryRequest
    {
        TableName = "AppTable",
        KeyConditionExpression = "PK = :pk AND begins_with(SK, :order)",
        ExpressionAttributeValues =
        {
            [":pk"] = new AttributeValue(currentUserPk),
            [":order"] = new AttributeValue("ORDER#")
        },
        Limit = pageSize
    };

    if (!string.IsNullOrEmpty(nextToken))
        request.ExclusiveStartKey =
            JsonSerializer.Deserialize<Dictionary<string, AttributeValue>>(nextToken);

    var response = await db.QueryAsync(request);

    var token = response.LastEvaluatedKey?.Count > 0
        ? JsonSerializer.Serialize(response.LastEvaluatedKey)
        : null;

    return Ok(new { Items = response.Items, NextToken = token });
}

The API stays stateless. The database does not maintain session cursors, and the client controls pagination through the opaque token.

6.2.2 Avoiding the “Offset/Limit” Anti-Pattern

Offset-based pagination forces the database to read and discard items. DynamoDB has no concept of skip. If you issue “start at item 900,” the service must read 900 items before producing results. In high-traffic systems, this pattern becomes one of the most expensive operations you can perform.

Cursor-based pagination avoids this by reading only the items required for each page. The performance remains stable as your dataset grows.

6.3 Handling Result Sets with `IAsyncEnumerable<T>`

Streaming results from DynamoDB through the API improves memory efficiency and reduces latency. Instead of waiting for the full query response, you can emit each batch as it arrives.

When using the low-level client, you manually implement a wrapper that yields items as pages are fetched:

public async IAsyncEnumerable<Dictionary<string, AttributeValue>> StreamQueryAsync(
    QueryRequest request,
    [EnumeratorCancellation] CancellationToken cancellationToken = default)
{
    Dictionary<string, AttributeValue>? lastKey = null;
    do
    {
        request.ExclusiveStartKey = lastKey;
        var response = await _client.QueryAsync(request, cancellationToken);

        foreach (var item in response.Items)
            yield return item;

        lastKey = response.LastEvaluatedKey;
    }
    while (lastKey != null && lastKey.Count > 0);
}

This approach pairs naturally with ASP.NET Core’s chunked responses or gRPC streams. The service can begin returning data to the client without waiting for the complete dataset.

6.4 DynamoDB Streams & Lambda

Streams provide a near real-time feed of changes to a table. Each modification (insert, update, delete) produces a record. Lambda functions subscribed to the stream can react asynchronously to model changes.

For example, updating a tenant’s “Total Sales” counter does not belong in the main transaction. Instead, you append a record to the order item, and a Lambda function processes the stream to update aggregate values. This offloads expensive writes and keeps order creation latency low.

A typical Lambda handler in .NET:

public async Task FunctionHandler(DynamoDBEvent ev, ILambdaContext context)
{
    foreach (var record in ev.Records)
    {
        if (record.EventName == "INSERT" &&
            record.Dynamodb.NewImage.TryGetValue("EntityType", out var type) &&
            type.S == "OrderHeader")
        {
            var amount = decimal.Parse(record.Dynamodb.NewImage["Total"].N);

            await UpdateSalesAggregateAsync(
                record.Dynamodb.Keys["PK"].S,
                amount);
        }
    }
}

The handler avoids scanning or querying for related records. It uses data already included in the stream event. This pattern keeps aggregation tasks cheap and isolated from operational workloads.

7 Architecting the Data Access Layer (Repository Pattern)

The data layer in DynamoDB applications must align with the access patterns of the table. Trying to build a generic repository around CRUD operations creates mismatches because writes and reads require key knowledge that varies across entity types. The repository should express business intent, not table mechanics.

7.1 The Fallacy of `IRepository<T>`

Generic CRUD repositories assume that all entities behave consistently. They hide details about keys and relationships, which makes sense in relational ORMs where identity and queries follow a stable pattern. DynamoDB schemas depend on specific key shapes and access-pattern awareness. A generic interface discards those distinctions.

For example, a GetByIdAsync<T> method cannot know whether the item is a header, a line item, or part of a multi-entity partition. You would end up leaking PK/SK details through parameters, defeating the purpose of abstraction. It also misleads developers into believing DynamoDB can perform arbitrary lookups.

Instead of forcing the relational model onto DynamoDB, the repository should reflect the access patterns defined earlier.

7.2 The “Access Pattern Repository”

An access-pattern repository defines methods that match the data operations your API actually requires. This ensures each method uses the appropriate key structure, and the calling code doesn’t need to understand DynamoDB internals.

Example interface:

public interface IOrderRepository
{
    Task<OrderHeader?> GetOrderAsync(Guid tenantId, Guid orderId);
    Task<IReadOnlyList<OrderHeader>> GetRecentOrdersAsync(Guid tenantId, Guid userId, int limit);
    Task<IReadOnlyList<OrderItem>> GetOrderItemsAsync(Guid tenantId, Guid orderId);
}

Implementations issue queries that match the single-table structure:

public async Task<OrderHeader?> GetOrderAsync(Guid tenantId, Guid orderId)
{
    var pk = $"TENANT#{tenantId}#ORDER#{orderId}";

    var request = new QueryRequest
    {
        TableName = _tableName,
        KeyConditionExpression = "PK = :pk AND SK = :header",
        ExpressionAttributeValues =
        {
            [":pk"] = new AttributeValue(pk),
            [":header"] = new AttributeValue("HEADER")
        }
    };

    var result = await _db.QueryAsync(request);
    return result.Items.Count == 0
        ? null
        : _mapper.Map<OrderHeader>(result.Items[0]);
}

Each method aligns with a specific access pattern, keeping data logic predictable.

7.3 Dependency Injection & Configuration

A properly configured data layer prevents unnecessary clients or expensive object allocations. DynamoDB clients are thread-safe, and most applications use one instance for the entire lifetime of the service.

7.3.1 Registering `IAmazonDynamoDB` as a Singleton

In ASP.NET Core:

services.AddSingleton<IAmazonDynamoDB>(sp =>
{
    var config = new AmazonDynamoDBConfig
    {
        RegionEndpoint = Amazon.RegionEndpoint.USEast1
    };
    return new AmazonDynamoDBClient(config);
});

Because the client is lightweight and thread-safe, this configuration minimizes connection overhead.

7.3.2 Managing `DynamoDBContext` Lifecycles

If using the DataModel, the context is safe as a scoped service:

services.AddScoped<IDynamoDBContext, DynamoDBContext>();

This avoids repeated reflection-based metadata generation while staying aligned with request lifetimes.

7.4 Decorator Pattern for Caching

Caching improves performance for read-heavy patterns such as user profile lookups. The repository can be wrapped with a decorator that uses IMemoryCache or Redis. Because DynamoDB guarantees single-digit millisecond latency, caching is not required for every access pattern, but it helps stabilize hotspots.

Example decorator structure:

public class CachedOrderRepository : IOrderRepository
{
    private readonly IOrderRepository _inner;
    private readonly IMemoryCache _cache;

    public CachedOrderRepository(IOrderRepository inner, IMemoryCache cache)
    {
        _inner = inner;
        _cache = cache;
    }

    public async Task<OrderHeader?> GetOrderAsync(Guid tenantId, Guid orderId)
    {
        var key = $"order:{tenantId}:{orderId}";
        if (_cache.TryGetValue(key, out OrderHeader? cached))
            return cached;

        var order = await _inner.GetOrderAsync(tenantId, orderId);
        if (order != null)
            _cache.Set(key, order, TimeSpan.FromMinutes(5));

        return order;
    }

    // Other methods follow same pattern
}

The decorator keeps caching logic isolated without modifying repository internals.

8 Pitfalls, Anti-Patterns, and Operational Costs

DynamoDB provides strong performance guarantees, but misaligned patterns quickly erode those benefits. This section summarizes common issues encountered in real systems and how to address them before they impact production.

8.1 The “Hot Partition” Problem

A hot partition occurs when a small number of partition keys receive disproportionately high traffic. DynamoDB distributes data based on the partition key, so uneven key usage leads to throttling even if the table as a whole has available capacity.

In a multi-tenant application, using the tenant ID alone as the PK concentrates all user activity under one partition. The fix is embedding more specific identifiers in the PK or employing write sharding for time-series workloads. Controlled distribution ensures workloads spread across multiple physical partitions.

8.2 GSI Throttling

GSIs share write capacity with the base table. If an index has low cardinality or receives many writes, it can throttle the entire table. This often happens when teams overload a GSI with attributes written by many entities that share the same GSI key.

Monitoring GSI capacity usage is essential. If a GSI becomes a bottleneck, you can redesign the index key to increase cardinality or separate frequent-write attributes into a new index. Sparse indexes also help reduce load by storing only the items relevant to specific queries.

8.3 Filtering Post-Query

A FilterExpression removes items only after DynamoDB has read them from disk. When the goal is to reduce the number of items returned, filters appear convenient but may hide significant read costs.

For instance, a query that retrieves order headers for a user and filters by status still reads all orders before discarding most of them. Instead, design the key or index so that status becomes part of the key condition. This avoids unnecessary reads and stabilizes query performance.

8.4 Capacity Planning

Choosing between Provisioned and On-Demand modes depends on workload characteristics. Provisioned capacity is cheaper when workloads are predictable and steady, but you must allocate enough read/write units to handle peak load. On-Demand is beneficial when traffic is spiky or unpredictable, trading a higher per-request cost for elasticity.

A common approach for SaaS systems is starting with On-Demand during early development and switching selective tables or indexes to Provisioned once traffic patterns become consistent. DynamoDB supports auto-scaling of provisioned capacity, but it still reacts to usage rather than predicting it, so well-understood workloads benefit most.

8.5 Unit Testing

Testing DynamoDB integrations requires realistic behavior that mocks cannot provide. Mocked clients return expected responses without exposing concurrency or conditional-write behavior, which hides most of the complexity your application must handle.

8.5.1 Why You Shouldn’t Mock the Client

Mocking IAmazonDynamoDB simulates DynamoDB incorrectly. It cannot reproduce throughput limits, conditional expressions, or partition-key behavior. Tests that rely on these mocks often pass while the production system fails under real conditions.

8.5.2 Using DynamoDB Local

DynamoDB Local runs the database engine in a container and supports most operational features. You can start it in CI pipelines during integration tests:

docker run -d -p 8000:8000 amazon/dynamodb-local

Configure the client in .NET:

services.AddSingleton<IAmazonDynamoDB>(sp =>
{
    var config = new AmazonDynamoDBConfig
    {
        ServiceURL = "http://localhost:8000"
    };
    return new AmazonDynamoDBClient(config);
});

Tests issue real queries, perform conditional writes, and validate key constraints. This setup catches schema mistakes early and builds confidence that your Single-Table Design behaves correctly under real conditions.

Mastering DynamoDB for .NET Architects: Single-Table Design, Patterns, and Pitfalls