Cloud concept · Updated

Denormalization - Denormalization deliberately combines related data or stores redundant values to make common reads require less work.

In short

Denormalization shapes stored data around access patterns rather than minimizing duplication across separate entities. It can reduce joins, network requests, and repeated calculations, but increases storage, write amplification, and the effort required to keep duplicated values consistent.

Cloud Concepts Hub →

6 key aspects of Denormalization

Denormalization exchanges additional storage and maintenance work for simpler, faster reads tailored to known access patterns.

01
Access-pattern-driven modeling

Design stored representations around specific queries and their latency, throughput, and freshness requirements. Combine data that is commonly read together rather than flattening every relationship.

02
Selective redundancy and precomputation

Copy frequently needed attributes or persist calculated results to reduce joins, requests, or repeated aggregation. Keep the duplication purposeful and identify the source of truth.

03
Read and write cost trade-offs

Fewer reads can improve response time and reduce request costs, but larger records and additional updates can increase storage and write costs. Evaluate the complete workload, especially when copied attributes change frequently.

04
Explicit consistency semantics

Decide whether each stored value represents current state, an eventually synchronized copy, or a historical snapshot. Choose update mechanisms that meet those semantics and include recovery for partially completed updates.

05
Controlled schema evolution

Coordinate schema changes across source data, derived tables, catalog metadata, and consuming applications. Plan backfills and compatibility periods because existing denormalized records do not automatically acquire newly derived values.

06
Measured, selective adoption

Benchmark representative queries and writes before and after changing the model. Denormalization complements good keys, partitioning, distribution, and file layout; it does not replace them.

Cloud services for Denormalization

A practical architecture keeps authoritative source data and creates separate read-oriented representations where needed. DynamoDB can serve low-latency application queries using duplicated attributes, while Glue transforms source datasets into denormalized tables stored in S3. The Glue Data Catalog describes those tables, and Lake Formation governs supported access paths. Athena queries lake datasets directly, while Redshift supports warehouse models and materialized views for analytical workloads. These services do not automatically synchronize every copy: engineers must define refresh schedules, change propagation, schema compatibility, and reconciliation so each representation meets its consumers' freshness and correctness requirements.

Amazon DynamoDBAmazon DynamoDB

DynamoDB models commonly duplicate or embed selected attributes so applications can satisfy known access patterns without server-side relational joins. Account for item size, partition-key distribution, and the consistency of updates across duplicated items.

Amazon RedshiftAmazon Redshift

Redshift supports selectively flattened analytical tables and materialized views that reduce repeated joins or computation. Compare these designs with well-designed star schemas rather than assuming complete flattening is always faster.

AWS GlueAWS Glue

Glue transformation jobs can join, enrich, and aggregate source datasets into denormalized outputs. The AWS Glue Data Catalog records table schemas and locations, but catalog changes alone do not transform existing data.

Amazon S3Amazon S3

S3 can store normalized source datasets alongside denormalized analytical datasets, often in columnar formats such as Parquet. Partitioning, file sizes, and refresh strategies affect how efficiently those datasets can be queried.

AWS Lake FormationAWS Lake Formation

Lake Formation governs access to supported cataloged lake tables, including denormalized datasets. It does not perform denormalization; permissions and sensitive-data exposure must be reviewed when attributes are copied into additional tables.

Amazon AthenaAmazon Athena

Athena queries denormalized datasets in S3 using cataloged table definitions. Prejoined or preaggregated tables can reduce repeated query work, while actual performance and scanned-data costs still depend on the query and physical layout.

Denormalization exam questions

Real practice exam questions on Denormalization, with the correct answer and its explanation. Answer them with every option in the exam simulator.

DEA-C01

A collaboration platform is designing an Amazon DynamoDB table to store team memberships. The application must list user IDs and membership roles for a team, and list team IDs and membership roles for a user. Each page of either list must be retrieved with one Query request, without scans or additional item reads. After a membership creation or deletion completes successfully, subsequent reads in either direction must reflect the change. Teams can have hundreds of thousands of members, and users can belong to many teams.

Which data model best meets these requirements?

Show answerCORRECT ANSWER

Store reciprocal membership items: one with partition key TEAM#<teamId> and sort key USER#<userId>, and another with partition key USER#<userId> and sort key TEAM#<teamId>. Include the role in both items. Create or delete both items with TransactWriteItems, and use strongly consistent table queries for both access patterns.

Reciprocal items denormalize the relationship to support both access patterns directly through the table's primary key. Each strongly consistent Query can return the requested IDs and roles without additional reads. Transactional writes ensure that both representations are created or deleted atomically, and separate membership items avoid unbounded growth within a single item.

SAA-C03

An online training company stores certificates, courses, and instructor profiles as separate items in Amazon DynamoDB. To display a learner's certificate history, the application queries the learner's certificates and then retrieves the referenced course and instructor items. These additional reads cause high latency during peak traffic. Certificate-history reads greatly outnumber certificate writes.

Each history entry must show the course title and instructor display name as they were when the certificate was issued, even if those values change later. The company wants to retrieve a page of complete history entries with one Query request and no additional item lookups.

Which data-model change best meets these requirements?

Show answerCORRECT ANSWER

Store the course title and instructor display name in each certificate item when it is issued. Use the learner ID as the partition key and an issuance timestamp with a certificate ID as the sort key, and leave the copied values unchanged.

Storing the issuance-time values alongside each certificate allows a Query for the learner to return complete history entries without additional lookups. The copied attributes are intentional historical snapshots, so later profile changes must not update them. This trades modest storage and write overhead for faster, simpler reads.

Practice Denormalization in a full exam

Questions on Denormalization in each practice exam, with every answer option and an explanation for each:

Start a free practice exam

What is Denormalization

3 min read

Denormalization is a data-modeling technique that deliberately combines related information or stores redundant values to improve retrieval performance. A normalized relational model separates entities and relationships to reduce redundancy and update anomalies. A denormalized model may instead repeat selected attributes, embed related records, or persist calculated results. The objective is not duplication itself: it is reducing the work required by important queries.

Start with access patterns, expected traffic, and freshness requirements. Identify which attributes applications retrieve together, how frequently they change, and whether queries need current values or historical snapshots. Read-intensive workloads often benefit when frequently requested data can be fetched in one operation. Write-intensive workloads can suffer if every business change requires updating many copies. Measure latency, throughput, scanned data, and total cost rather than assuming fewer joins always means a better design.

For example, an order service might store the customer's display name and delivery address alongside each order. This avoids fetching a separate customer record when displaying order history. However, these attributes require different semantics: the delivery address at purchase time is usually an intentional historical snapshot, while a displayed current customer name may need synchronization. Define the authoritative source and update policy for every copied attribute.

In Amazon DynamoDB, applications commonly denormalize because the service does not provide server-side relational joins. An order-summary item can contain the attributes needed for an order-history screen, while detailed line items remain separate. Partition and sort keys must support the required queries. Denormalization does not necessarily mean putting everything into one item or using one table; item-size limits, growing collections, hot partitions, and write volume still constrain the design.

In Amazon Redshift, selective denormalization can reduce repeated joins or computation in analytical workloads. A sales reporting table might include product category and customer region, or a materialized view might persist a frequently used aggregate. Nevertheless, star schemas with fact and dimension tables are often effective. Redshift supports joins, and appropriate distribution, sort keys, and query design can make them efficient. A single wide table is not automatically faster, especially when it duplicates frequently changing attributes or increases processing and storage costs.

In a data lake, transformation jobs can join source datasets and publish denormalized Parquet tables in Amazon S3. AWS Glue can perform these transformations and maintain table metadata in the AWS Glue Data Catalog. AWS Lake Formation governs supported access to the cataloged data; it does not itself denormalize records or replace the transformation engine. When schemas evolve, introduce compatible columns where possible, coordinate producer and consumer changes, and backfill existing data when required. Updating catalog metadata alone does not rewrite stored files.

The main operational trade-off is maintaining correctness across copies. Synchronous updates or transactions can provide stronger coordination where supported, while event-driven propagation can reduce coupling but introduces a period of stale data. Asynchronous pipelines need idempotent processing, retries, monitoring, and reconciliation. Denormalization is not equivalent to replication, indexing, or caching, although those techniques can complement it. Retain normalized authoritative data when useful, and add only the read-oriented representations whose measured benefits justify their maintenance cost.

Learn Denormalization in lessons

4 short lessons on Denormalization in 2 certification courses.

Open the lessons