Data Models and Schema Design for Redshift and DynamoDB
Data Models and Schema Design for Redshift and DynamoDB
Understanding data models and schema design is a critical competency for the AWS Data Engineer Associate exam. This guide covers the foundational concepts, practical design considerations, and exam strategies for both Amazon Redshift and Amazon DynamoDB.
Why Is This Important?
Data models and schema design directly impact the performance, scalability, cost-efficiency, and maintainability of your data solutions. Poorly designed schemas can lead to slow queries, excessive costs, and operational headaches. As a data engineer, you must understand how to:
- Optimize storage and query performance in a columnar data warehouse like Redshift
- Design access-pattern-driven schemas for a NoSQL database like DynamoDB
- Choose the right distribution and sort strategies in Redshift
- Select appropriate partition keys and sort keys in DynamoDB
- Balance between normalization and denormalization depending on the use case
What Are Data Models and Schema Design?
A data model is an abstract representation of how data is organized, related, and constrained within a system. Schema design is the practical implementation of a data model within a specific database technology.
For Amazon Redshift (a columnar, relational data warehouse), schema design involves:
- Table structures (fact tables, dimension tables)
- Distribution styles (KEY, EVEN, ALL, AUTO)
- Sort keys (compound, interleaved)
- Compression encodings
- Star schema vs. snowflake schema modeling
For Amazon DynamoDB (a fully managed NoSQL key-value and document database), schema design involves:
- Primary key design (partition key, or partition key + sort key)
- Global Secondary Indexes (GSIs) and Local Secondary Indexes (LSIs)
- Single-table design patterns
- Denormalization and item collections
- Access pattern-driven modeling
How It Works: Amazon Redshift Schema Design
1. Star Schema and Snowflake Schema
Redshift is optimized for analytical workloads and typically uses a star schema or snowflake schema. In a star schema, a central fact table (containing measures and foreign keys) is surrounded by dimension tables (containing descriptive attributes). A snowflake schema further normalizes dimensions into sub-dimensions. Star schemas are generally preferred in Redshift for simplicity and query performance.
2. Distribution Styles
Redshift distributes table data across nodes. Choosing the right distribution style is essential:
- KEY distribution: Rows with the same value of a specified column are placed on the same node. Ideal for large fact tables that are frequently joined on the distribution key.
- ALL distribution: A full copy of the table is placed on every node. Best for small, slowly changing dimension tables that are frequently joined.
- EVEN distribution: Rows are distributed in a round-robin fashion. Used when no clear join key exists and the table is not joined frequently.
- AUTO distribution: Redshift automatically chooses between ALL and EVEN based on table size. This is the default.
Best Practice: Co-locate frequently joined tables by using KEY distribution on the same join column. Use ALL distribution for small dimension tables.
3. Sort Keys
Sort keys determine the physical order of data on disk, which dramatically affects query performance by enabling zone map efficiency and reducing I/O:
- Compound Sort Key: Data is sorted by the first column, then the second, and so on. Best when queries consistently filter or order by the leading columns of the sort key.
- Interleaved Sort Key: Gives equal weight to each column in the sort key. Best when queries filter on different columns with roughly equal frequency. However, interleaved sort keys have higher maintenance overhead (VACUUM operations are more expensive).
Best Practice: Use compound sort keys for most workloads. Place the most frequently filtered column first. Use timestamp or date columns as sort keys for time-series data.
4. Compression (Encoding)
Redshift uses columnar storage with column-level compression. Using the ANALYZE COMPRESSION command or enabling automatic compression allows Redshift to choose optimal encodings. This reduces storage and improves I/O performance.
5. Additional Considerations
- Use COPY command for bulk loading data efficiently.
- Avoid using large VARCHAR columns unnecessarily; size columns appropriately.
- Use Redshift Spectrum for querying data in S3 without loading it into Redshift, extending your schema into a lakehouse architecture.
- Materialized views can pre-compute and store results of complex queries for faster access.
- Late-binding views allow querying Redshift Spectrum external tables without strict schema binding.
How It Works: Amazon DynamoDB Schema Design
1. Primary Key Design
DynamoDB is schema-less at the attribute level but requires a well-defined primary key:
- Simple Primary Key (Partition Key only): A single attribute uniquely identifies each item. The partition key determines which physical partition stores the item. Must have high cardinality for even distribution.
- Composite Primary Key (Partition Key + Sort Key): Two attributes together uniquely identify each item. The partition key groups related items, and the sort key orders items within that partition. This enables powerful range queries within a partition.
Best Practice: Choose a partition key with high cardinality to avoid hot partitions. Use the sort key to enable range queries and hierarchical data access.
2. Access Pattern-Driven Design
Unlike relational databases where you model data first and then write queries, DynamoDB requires you to identify all access patterns first and then design your schema to support them. This is a fundamental paradigm shift.
Steps:
- List all read and write access patterns
- Determine the primary key structure to serve the most critical patterns
- Use GSIs and LSIs to serve additional access patterns
- Denormalize data aggressively to minimize the number of queries
3. Secondary Indexes
- Global Secondary Index (GSI): Has a different partition key and optional sort key from the base table. Can be created at any time. Has its own provisioned throughput. Eventually consistent reads only. You can have up to 20 GSIs per table.
- Local Secondary Index (LSI): Shares the same partition key as the base table but has a different sort key. Must be created at table creation time. Shares throughput with the base table. Supports strongly consistent reads. Limited to 10 GB per partition key value. You can have up to 5 LSIs per table.
Best Practice: Prefer GSIs for flexibility. Use LSIs only when you need strongly consistent reads with an alternative sort order and you can live with the 10 GB partition limit.
4. Single-Table Design
A powerful DynamoDB design pattern is the single-table design, where multiple entity types (e.g., customers, orders, products) are stored in a single table. This allows related data to be retrieved in a single query by using carefully designed partition and sort key structures with overloaded attributes (e.g., PK = "CUSTOMER#123", SK = "ORDER#456").
Benefits:
- Reduces the number of tables to manage
- Enables fetching related entities in a single query
- Minimizes read capacity usage
Trade-offs:
- More complex to design and maintain
- Requires thorough understanding of all access patterns upfront
5. Denormalization and Item Collections
In DynamoDB, denormalization is the norm. Unlike relational databases, you duplicate data across items to avoid expensive joins (which DynamoDB does not support). An item collection is all items in a table or index that share the same partition key value, and it is fundamental to efficient queries.
6. Capacity and Throughput Considerations
- On-Demand Mode: Pay per request, no capacity planning needed. Good for unpredictable workloads.
- Provisioned Mode: You specify Read Capacity Units (RCUs) and Write Capacity Units (WCUs). Good for predictable workloads. Can use Auto Scaling.
- Item size affects throughput: 1 RCU = one strongly consistent read per second for items up to 4 KB. 1 WCU = one write per second for items up to 1 KB.
- Large items consume more capacity units; keep items small when possible.
7. Additional DynamoDB Features for Schema Design
- TTL (Time to Live): Automatically delete expired items, useful for session data, logs, etc.
- DynamoDB Streams: Capture item-level changes for event-driven architectures, replication, or analytics pipelines.
- Transactions: Support ACID transactions across multiple items and tables (TransactWriteItems, TransactGetItems).
- Sparse Indexes: A GSI where only items with the indexed attribute appear, enabling efficient filtering.
Comparing Redshift and DynamoDB Schema Design
| Aspect | Redshift | DynamoDB |
|---|---|---|
| Model | Relational, columnar | NoSQL, key-value/document |
| Schema | Defined upfront (DDL) | Schema-less (except keys) |
| Design Approach | Model data, then query | Identify access patterns first |
| Normalization | Star/snowflake (some normalization) | Heavy denormalization |
| Joins | Supported (optimize with distribution) | Not supported (pre-join via denormalization) |
| Scaling | Resize cluster or use Serverless | Automatic horizontal scaling |
| Best For | Complex analytics, aggregations, BI | High-throughput, low-latency OLTP, real-time |
Exam Tips: Answering Questions on Data Models and Schema Design for Redshift and DynamoDB
General Strategies:
- Read the question carefully to determine whether it is about analytical workloads (likely Redshift) or operational/transactional workloads (likely DynamoDB).
- Pay attention to keywords: "low latency," "high throughput," "key-value" → DynamoDB. "Complex queries," "aggregations," "BI," "data warehouse" → Redshift.
Redshift-Specific Tips:
1. Distribution style questions: If the question mentions slow joins between a large fact table and dimension tables, the answer likely involves changing the distribution style to KEY (on the join column) or ALL (for small dimension tables).
2. Sort key questions: If a query filters on a specific column (especially a date/timestamp), using that column as a sort key will improve performance. Compound sort keys are the default recommendation unless queries filter on multiple columns equally.
3. When you see "data skew" or "uneven distribution": Think about changing the distribution key to a column with higher cardinality, or switching to EVEN distribution.
4. Compression questions: Remember that Redshift can automatically apply compression encoding. The ANALYZE COMPRESSION command helps identify optimal encodings. Do NOT apply compression to sort key columns in some cases (Redshift best practices have evolved — the exam may test that automatic encoding is now recommended).
5. Star schema is preferred over snowflake schema in Redshift for most analytical workloads because it requires fewer joins.
6. VACUUM and ANALYZE: After bulk deletes or updates, VACUUM reclaims space and re-sorts. ANALYZE updates table statistics for the query optimizer. These are maintenance operations, not schema design per se, but questions may link them to sort key choices.
DynamoDB-Specific Tips:
1. Partition key selection is critical. If a question describes a hot partition or throttling problem, the answer is almost always to choose a partition key with higher cardinality or to use a write-sharding strategy (e.g., adding a random suffix to the partition key).
2. Access patterns first: If a question asks about how to design a DynamoDB table, look for the access patterns described in the scenario. The correct answer will support those access patterns efficiently.
3. GSI vs. LSI: If the question requires a different partition key for queries, use a GSI. If the question requires a different sort key with the same partition key and needs strongly consistent reads, use an LSI. Remember: LSIs must be created at table creation time.
4. Single-table design: If the question involves querying multiple entity types in a single request or mentions reducing the number of API calls, single-table design with composite keys is the answer.
5. Denormalization: If a question mentions avoiding joins or reducing read latency in DynamoDB, the answer is denormalization — duplicating data across items.
6. Sparse indexes: If only a subset of items should be queryable by a certain attribute, a GSI on that attribute creates a sparse index (only items with that attribute are indexed).
7. TTL for cleanup: If the question involves automatically removing old or expired data, TTL is the answer.
8. Item size matters: If a question involves large objects (e.g., images, large documents), remember that DynamoDB has a 400 KB item size limit. Large binary data should be stored in S3 with a pointer in DynamoDB.
Common Exam Traps:
- Do not confuse Redshift distribution styles with DynamoDB partition strategies. They are conceptually related (distributing data) but work very differently.
- Do not assume normalization is always good. In DynamoDB, normalization leads to multiple queries and poor performance. In Redshift, moderate normalization (star schema) is appropriate, but excessive normalization (highly normalized snowflake) hurts query performance.
- Do not choose interleaved sort keys as the default. They have higher maintenance costs and are only beneficial for specific query patterns.
- Watch for questions about GSI costs. GSIs consume their own WCUs and RCUs. If a question mentions cost optimization, reducing unnecessary GSIs is a valid strategy.
- Remember that Redshift Serverless and provisioned clusters both use the same schema design principles (distribution styles, sort keys, etc.).
Key Takeaways for the Exam:
1. Redshift: Think columnar, star schema, distribution styles, sort keys, compression.
2. DynamoDB: Think access patterns first, partition key cardinality, denormalization, GSIs/LSIs, single-table design.
3. Always match the schema design recommendation to the specific workload and access patterns described in the question.
4. Performance problems in Redshift often relate to distribution and sort key choices. Performance problems in DynamoDB often relate to partition key design and hot partitions.
5. Cost optimization in Redshift involves compression and appropriate table design. Cost optimization in DynamoDB involves efficient key design, appropriate capacity mode, and minimizing unnecessary indexes.
