DynamoDB & Analytics

NoSQL Databases & DynamoDB

Amazon DynamoDB (DDB) 101

DDB Concepts

• DynamoDB is a NoSQL database offering at the table level (DB Table-as-a-Service)
  • Uses a wide-column data model, supporting key-value and semi-structured data (similar to document-style databases)
  • Fully serverless, with no need to manage servers or underlying infrastructure
    • Commonly used in serverless architectures and large-scale web applications within AWS
  • Exposes public endpoints for access
  • In comparison, RDS and Aurora are DB server–based services, operate privately, and use SQL-based engines
• DynamoDB capacity represents database performance, specifically operation throughput
  • Measured using Write Capacity Units (WCUs) and Read Capacity Units (RCUs)
  • Increasing capacity directly improves throughput and performance
• Scaling models:
  1. Provisioned capacity
     • Requires defining WCUs and RCUs
     • Can be manually configured for predictable workloads
     • Can also be automatically adjusted using scaling policies
  2. On-demand capacity
     • Fully managed performance model with automatic scaling
     • No need to specify capacity in advance
• Key features:
  • Built-in regional resilience with high availability across multiple AZs
    • Global replication can be optionally enabled
    • Data is automatically replicated across storage nodes without user configuration
  • Uses SSD-backed storage, providing very low latency (typically under 10 ms)
  • Accessible through the AWS console, CLI, and APIs
  • Supports backups and point-in-time recovery
  • Encryption at rest is enabled
  • Integrates with event-driven architectures by triggering actions on data changes
  • Does not support traditional SQL queries
    • Supports PartiQL, which offers SQL-like query capabilities
  • Pricing is usage-based:
    • Charges for read/write operations (based on RCUs and WCUs)
    • Storage usage
    • Optional features such as point-in-time recovery
    • Reserved capacity options are available for long-term cost savings
• DynamoDB considerations (exam and real-world):
  • Prefer DynamoDB for NoSQL use cases
  • Not suitable for relational data models
    • Lacks features typically found in relational databases
  • Ideal for key-value access patterns

DDB Tables

• A table represents a collection of items sharing a common primary key structure
  • It is the fundamental unit of configuration in DynamoDB
  • Tables can store an unlimited number of items
    • An item is equivalent to a row
• A primary key must be defined when creating a table, with two options:
  1. Simple primary key (Partition Key)
     • Each item must have a unique partition key value
  2. Composite primary key (Partition Key + Sort Key)
     • Each item must have a unique combination of partition and sort key values
• Attributes have no fixed schema
  • Items can contain different attributes or none at all
• Maximum item size is 400 KB
  • Includes keys, attribute names, and attribute values
• Table capacity determines performance and is measured in RCUs and WCUs
  • 1 WCU supports 1 KB of write throughput per second
  • 1 RCU supports 4 KB of read throughput per second
  • Most operations consume a minimum amount of capacity
    • For example, reading 100 bytes still consumes 1 RCU

DDB Backups

On-Demand Backups

• Manual full-table backups that persist until explicitly deleted
  • Comparable to manual snapshots in RDS
• Restore options:
  • Can restore within the same region or to a different region
  • Can include or exclude indexes
  • Encryption settings can be modified during restore

Point-in-Time Recovery (PITR)

• Provides continuous backups by recording all changes made to a table for up to 35 days
  • Disabled by default
• Enables restoration of a table to any specific second within the retention window

DynamoDB – Operations, Consistency & Performance

DDB Capacity and R/W Operations

• DynamoDB offers two capacity modes:
  1. Provisioned capacity → RCUs and WCUs are explicitly configured per table
  2. On-demand capacity → suited for unpredictable workloads or when minimizing administrative effort
     • No need to define capacity in advance; DynamoDB manages it automatically
     • Pricing is based on the number of read/write units consumed (per million requests)
     • Can be significantly more expensive than provisioned capacity in some cases
• It is possible to switch between provisioned and on-demand modes, although certain limitations apply
• Key trade-off: operational control (provisioned) versus simplicity and potentially higher cost (on-demand)
• Every table operation consumes at least 1 RCU or 1 WCU
  • Exception: eventually consistent reads may consume less than 1 RCU
  • 1 RCU supports one read request of up to 4 KB per second
    • Even if the data size is smaller, it still consumes 1 RCU
    • Larger items consume multiple RCUs (e.g., 400 KB = 100 RCUs)
    • Capacity is replenished every second
  • 1 WCU supports one write request of up to 1 KB per second
• In provisioned mode, each table includes burst capacity pools for reads and writes
  • Burst capacity is calculated as provisioned capacity multiplied by 300 seconds
  • Provisioned values represent sustained throughput, while burst capacity allows temporary spikes
  • If burst capacity is exhausted, a ProvisionedThroughputExceeded exception occurs
    • Requests are throttled and must be retried or capacity increased
  • Other internal operations may also consume burst capacity, so it should not be heavily relied upon
• Capacity efficiency considerations:
  • Smaller items are generally more efficient than larger ones for read/write operations
  • Retrieving data in fewer requests is more efficient than making multiple smaller requests

DDB Common Read Operations

DDB Query Operation

• The Query operation requires a single partition key value and can optionally include a sort key or a range of sort key values
  • Returns all matching items
  • Capacity consumption is based on the total size of the items returned
  • Filtering returned attributes does not reduce consumed capacity, as the full item size is still read
• A Query can only retrieve items associated with a single partition key
  • It cannot scan across the entire table
• Example:
  • A table stores weather data where SensorID is the partition key and day of the week is the sort key
  • Querying with PK=1 returns all items for that sensor
    • If total size is 4 KB, this consumes 1 RCU
  • Querying with PK=1 and SK=MON returns one item
    • If size is 2.5 KB, it still consumes 1 RCU
  • Performing one query for all items under a partition key is more efficient than multiple queries for individual sort key values

DDB Scan Operation

• The Scan operation evaluates every item in a table and returns those matching specified criteria
  • It is the most flexible but also the least efficient operation
  • Consumes capacity for all items scanned, regardless of whether they are returned
• Example:
  • Scanning a table to retrieve all items with a specific attribute or within a value range
  • Even if only a subset of items is returned, the operation consumes capacity based on the total size of all scanned items
  • For instance, scanning 14 KB of data consumes 4 RCUs, even if only part of that data is returned

DDB Consistency Model for Read Operations

• A database is considered consistent when read operations always return the most recent data
  • Eventually consistent systems allow slight delays in propagation but are easier to scale
  • Strong consistency is required for use cases where stale data is unacceptable
• DynamoDB replicates data across multiple storage nodes in different Availability Zones
  • A leader node handles write operations and propagates updates to other nodes
  • If the leader fails, a new leader is elected
• Two read consistency options are available:
  1. Strongly consistent reads
     • Always read from the leader node
     • Guarantees the latest data is returned
  2. Eventually consistent reads
     • Can read from any replica node
     • May return slightly outdated data
     • Costs 50% less than strongly consistent reads
     • Allows reading twice as much data for the same RCU allocation
• Example:
  • If replication is not yet complete across all nodes, an eventually consistent read may return outdated data depending on which node is accessed
  • Strongly consistent reads always return the most recent data

DDB Performance Calculations (Provisioned Capacity)

• Steps to calculate required capacity:
  1. Identify whether operations are reads or writes
     • For reads, determine if strong consistency is required
  2. Determine the average item size
  3. Calculate request frequency (operations per second)
  4. Compute RCUs or WCUs required per item
  5. Multiply by the number of operations per second to get total capacity
• Example 1:
  • 10 devices write one item per second, each item is 2.5 KB
  • Write operation → 2.5 KB rounds up to 3 WCUs per item
  • Total required capacity: 3 × 10 = 30 WCUs
• Example 2:
  • 10 items read per second, each item is 2.5 KB, eventual consistency is acceptable
  • Read operation → 2.5 KB rounds up to 1 RCU per item
  • Total: 1 × 10 = 10 RCUs
  • With eventual consistency, required capacity is reduced by half → 5 RCUs
• Auto scaling can be used to dynamically adjust RCUs and WCUs based on traffic patterns

DynamoDB Indexes (LSIs and GSIs)

DynamoDB Indexes

• Reminder: Query and Scan have limitations
  • Query is the most efficient DynamoDB operation, but it can only target a single partition key (PK) value at a time.
    • Optionally, you can also filter by a single sort key (SK) value or a range of SK values.
  • Scan is more flexible but much less efficient because it reads the entire table.
• DynamoDB indexes provide alternative ways to access table data
  • Help improve the speed of data retrieval.
  • Types of indexes:
    1. Local Secondary Index (LSI) → different SK
       • Choose when strong, immediate consistency is needed.
    2. Global Secondary Index (GSI) → different PK and SK
       • Typically used as the default option.
• Index design tip: When designing a base table, choose PK and SK based on the primary access pattern. Indexes are meant to provide alternative ways to query data, not replace the main access path.
• Indexes are sparse
  • Indexes only include items that match their criteria.
    • Example: if a table has 10 items but only 5 have attribute X, an index on X will only track those 5 items.
  • Scans using indexes only process items included in the index, which makes them more efficient than scanning the whole table.
• Index projections:
  • ALL → include all attributes from the base table in the index
  • INCLUDE → choose specific attributes to include
  • KEYS_ONLY → include only the key attributes (not the full item values)
  • Projection choices affect query performance.
    • Projecting attributes increases capacity usage.
    • Queries that request attributes not projected require extra backend retrieval, which is slower and less efficient.

Local Secondary Index (LSI)

• Provides an alternative sort key (SK) for a table
  • Partition key remains the same
  • Maximum of 5 LSIs per base table
• Must be created at the same time as the table; cannot be added later
• Shares the base table’s capacity (RCUs and WCUs) in provisioned mode
• Supports strong, immediate consistency
• Example: For a weather station table, create an LSI for sunny days
  • Querying the base table by PK alone cannot filter by the sunny day attribute.
  • Using the LSI, you can efficiently query items with PK=1 and SK=Sunny Day, returning all sunny days for station 1.
  • Scanning this LSI only reads items with the sunny day attribute, reducing overall capacity usage.

Global Secondary Index (GSI)

• Provides alternative partition key (PK) and sort key (SK)
  • Default limit: 20 GSIs per table (can request more from AWS support)
• Can be created at any time, offering more flexibility than LSIs
• Has its own capacity allocation in provisioned mode (RCUs and WCUs)
• Only supports eventual consistency, as data is replicated asynchronously from the base table
  • If immediate consistency is required, use an LSI instead
• Example: For the weather station table, create a GSI with PK=Alarm and SK=Station ID
  • Allows efficient queries for items in an alarm state
  • Optionally filter by a specific station or range of stations
  • Scanning this GSI only processes items in the alarm state, improving efficiency

DynamoDB Streams & Triggers

DynamoDB Streams

• DynamoDB Stream = chronological sequence of changes to items in a table
  • Maintains a 24-hour rolling history
  • Internally uses a Kinesis Data Stream
  • Enables event-driven architectures (EDA) through DynamoDB triggers
• Enabled per table
• Tracks all types of item modifications: inserts, updates, and deletes
• Example: removing an orange attribute from an item in a table Diagram:

• Stream view types: determine what data is included in the stream
  1. KEYS_ONLY → captures just the PK (and optionally SK) of the changed item
     • Does not show the specific changes, but sufficient to query the table for the item
  2. NEW_IMAGE → captures the complete item after changes
     • Can use the item’s new state directly without querying the table
  3. OLD_IMAGE → captures the complete item before changes
     • Allows inspection of the previous state, and comparing with the new state to detect changes
  4. NEW_AND_OLD_IMAGES → captures both pre-change and post-change versions
     • Provides full visibility of exactly what changed without extra queries
  • Note: if an item is created, the pre-change record is empty; if an item is deleted, the post-change record is empty

DynamoDB Triggers

• Database triggers = automated events triggered by table changes
  • Each event contains data about the change, which can be used to perform actions automatically
  • Traditional DBs, like Oracle, have long supported triggers
  • AWS implementation: DynamoDB Streams + Lambda functions
    • Fully serverless, no infrastructure management required
• Efficient architecture:
  • Unlike polling, which consumes resources continuously, triggers only use compute when relevant changes occur
• Common use cases:
  • Reporting & analytics – e.g., generate a report when inventory levels change
  • Data aggregation – e.g., count votes in a voting application
  • Messaging & notifications – e.g., send alerts when a user posts a new chat message

DynamoDB Global Tables

DynamoDB Global Tables

• Global Tables = multi-master, cross-region replicated DynamoDB tables
  • There is no single master table; all tables act as replicas of the global table
  • Supports write replication across all tables
• Implementation steps:
  • Create DynamoDB tables in multiple regions
  • Select any table and configure it to link with others
    • Linked tables become replicas in the same global table configuration
• Conflict resolution:
  • Uses last-writer-wins
    • DynamoDB selects the most recent write and replicates it to all regions
    • Provides predictable results
• Multi-master capability:
  • Reads and writes can occur in any region
  • Typically achieves sub-second replication across regions
• Consistency considerations:
  • Strongly consistent reads are only guaranteed in the same region as the write
  • Cross-region replication is asynchronous, so global applications must be able to handle eventual consistency
• Common use cases:
  • Enhance global application performance
  • Provide global high availability
  • Enable disaster recovery and business continuity across regions

DynamoDB Accelerator (DAX)

Using a Traditional Cache with DynamoDB

DynamoDB Accelerator (DAX) – Overview

• DAX = fully managed, DynamoDB-integrated in-memory cache
  • Dramatically improves read performance without requiring the application to manage the cache
  • Reduces overall database operations and associated costs
• DAX SDK:
  • Installed in the application, removes cache management overhead
  • Application interacts with DAX as if it were DynamoDB; queries are automatically routed to DAX
• Cache hits respond in microseconds (µs)
• Cache misses are handled internally by DAX, which retrieves the data from DynamoDB and updates the cache

DAX Architecture

• Private service deployed in your VPC → DAX nodes form a DAX cluster
  • Primary node: read/write, replicates updates to other nodes
    • If the primary fails, a new primary is elected automatically
  • Replica nodes: read-only
  • Deploy nodes across multiple AZs for regional high availability
• Applications connect to the DAX cluster through a single endpoint, which load-balances requests across nodes
  • Cache hits provide responses significantly faster than querying DynamoDB directly (µs vs. ms)
• On a cache miss, DAX retrieves data from DynamoDB, updates the primary node, and replicates to replicas

DAX Features

• DAX maintains two types of caches:
  1. Item cache: stores results of GetItem and BatchGetItem operations
     • Must specify the item’s PK (and SK if used)
  2. Query cache: stores results of Query and Scan operations
     • Also caches the query parameters → identical subsequent queries return cached results
• Write-through caching supported: data is written to DAX and DynamoDB simultaneously
• Eventual consistency only: replication across DAX nodes is asynchronous
• Scalability: cluster can scale up (larger nodes) or out (more nodes)
• When to use DAX:
  • Read-heavy or bursty workloads where the same data is frequently accessed
    • Reduces read capacity units (RCUs) and improves cost efficiency
  • Applications needing minimal read latency
  • Reduce operational overhead from managing a custom in-memory cache

DynamoDB TTL

DynamoDB Time-to-Live (TTL)

• TTL = per-item timestamp that marks when an item should expire and be deleted automatically
  • No write capacity is consumed, and it does not incur extra costs, so database performance is unaffected
  • Expiration is handled by background system processes
• Enabled per table → specify which attribute holds the expiration timestamp
  • Timestamp is expressed as the number of seconds since Epoch (1 January 1970, 00:00:00)
  • To expire an item, set its timestamp attribute to the desired expiration time
• Once TTL is enabled:
  • TTL processors run on each partition (per PK value)
    • One process periodically scans items in the partition to check if they are expired
      • If the item’s timestamp is in the past, it is marked as expired
      • Marked items are still queryable until deletion
    • Another process scans for expired items to remove them from the table and indexes
      • A corresponding delete event is also recorded in DynamoDB Streams if configured
• Optional 24-hour TTL event stream
  • Records all deletions for auditing purposes
  • Note: this stream is separate from standard DynamoDB Streams that track item changes
• Use cases:
  • Automatically delete user or sensor data after a set period (e.g., one year)
  • Retain sensitive data temporarily to meet compliance requirements

Amazon Athena 101

Amazon Athena – Core Concepts

• Serverless interactive query service
  • Enables ad-hoc SQL-like queries on data stored in Amazon S3
    • Athena can also access other sources using federated queries, but S3 is the primary focus
  • Pay only for the data scanned during queries, plus the S3 storage costs; no additional fees
• Schema-on-read
  • The schema is applied when the query runs, transforming the raw data into a table-like structure
  • Original data in S3 is never modified
  • Think of it like a lens: the underlying data is unchanged, but the schema presents it in a structured format
  • Different from schema-on-write databases, which require data to conform to the schema before storing

Athena Architecture

• Supports multiple source formats: JSON, XML, AVRO, log files, etc.
• Schemas (with tables) define how to interpret raw data as queryable structures
  • Tables do not actually store data, they define how to project source data for queries
  • Queries stream the data through the schema at runtime
• Query results can be sent to other services, e.g., Amazon QuickSight for visualization
• No upfront costs or infrastructure management; you only pay for queries
• Federated queries allow accessing non-S3 sources via Lambda-based connectors

Athena Use Cases

• Queries where data transformation or loading is unnecessary
• Ad-hoc or occasional queries on S3 data without managing servers
• Serverless querying for cost-sensitive workloads
• Analyzing AWS logs, such as VPC Flow Logs, CloudTrail, ELB logs, and cost reports
• Querying Glue Data Catalog tables or web server logs
• Not suitable if a traditional DB (SQL/NoSQL) is required; Athena is designed for querying raw data without a database

Demo: Querying OpenStreetMap’s Planet OSM with Athena

Objective: Retrieve locations of all veterinary facilities in a specific geographic region.

1. Determine coordinates of the area using Google Earth
2. Source data in S3 bucket s3://osm-pds/planet/ contains Planet OSM data:
   • node – individual points with metadata
   • way – boundaries or areas
   • relationship – relationships between nodes/ways
3. Create an S3 bucket to store query results

Querying in Athena

• Even though databases and tables are created in Athena:
  • No actual data is stored inside Athena
  • Billing is only for queries executed against the schemas

1. Create a database:

CREATE DATABASE A4L;

2. Create a planet table:

CREATE EXTERNAL TABLE planet (
  id BIGINT,
  type STRING,
  tags MAP<STRING,STRING>,
  lat DECIMAL(9,7),
  lon DECIMAL(10,7),
  nds ARRAY<STRUCT<ref: BIGINT>>,
  members ARRAY<STRUCT<type: STRING, ref: BIGINT, role: STRING>>,
  changeset BIGINT,
  timestamp TIMESTAMP,
  uid BIGINT,
  user STRING,
  version BIGINT
)
STORED AS ORCFILE
LOCATION 's3://osm-pds/planet/';

3. Test query – retrieve 100 rows:

SELECT * FROM planet LIMIT 100;

4. Query all veterinary amenities in a region (e.g., Brisbane, AUS):

SELECT * FROM planet
WHERE type = 'node'
  AND tags['amenity'] IN ('veterinary')
  AND lat BETWEEN -27.8 AND -27.3
  AND lon BETWEEN 152.2 AND 153.5;

Screenshot of Demo inside Athena:

Amazon ElastiCache

ElastiCache Overview

• In-memory caching service for applications requiring high performance
  • Managed caching service supporting Valkey (Redis fork), Redis OSS, or Memcached
  • Caches provide much faster access than disk-based databases (e.g., RDS) but store temporary data only
• Common use cases and architectures:
  • Cache frequently-read data for read-heavy workloads with low latency requirements
    • Reduces load on primary databases and lowers costs
    • Relational databases struggle with high loads → performance may degrade or costs increase significantly
  • Store session data to enable stateless servers for high availability (HA) and fault-tolerant (FT) systems
• Best practice: define a cache invalidation strategy to ensure cached data remains current
• Requires application-level integration; the application must understand the caching logic

ElastiCache Architectures

Caching Architecture

• Application uses an in-memory cache alongside a database (e.g., Aurora)
  1. Read operation: app requests data from ElastiCache
  2. Cache hit: ElastiCache returns data quickly at low cost
  3. Cache miss or stale data: app queries Aurora for data
     • App writes data back to ElastiCache for subsequent requests
     • Subsequent reads of the same data are likely cache hits, reducing database load
• Improves scalability: allows many users without proportionally increasing database load
  • Cache hits typically respond in <1ms latency

Session State Architecture

• ElastiCache stores user session information
• When a user connects to an application instance through an ALB, the instance writes and updates the session in ElastiCache
• If the session is interrupted, a new instance can retrieve the session from ElastiCache → seamless to the user
• Fault-tolerant: if the serving instance fails, the ALB reconnects the user to another instance, which loads the session from cache

ElastiCache Engines

• Supported engines:
  1. Valkey (Redis fork)
  2. Memcached
  3. Latest Redis OSS
• Both provisioned and serverless offerings exist
• Supports multiple programming languages and instance types/sizes
• Larger and faster memory configurations are recommended for high-performance workloads

Redis vs Memcached Comparison

Feature	Redis	Memcached
Data structures	Advanced (lists, sets, sorted sets, hashes, bit arrays)	Simple (strings only)
Multi-AZ / HA	Supports replication across AZs → regionally resilient	No replication; sharding possible but no regional resilience
Backups	Supported	Not supported
Multi-threading	Single-threaded	Multi-threaded → can leverage multi-core CPUs for higher performance
Transactions	Supported → multiple operations treated atomically	Not supported

• Pronunciation of Memcached: “mem-cash-dee” or “mem-cashed”
• Background: Redis licensing changes led to the Valkey fork. AWS now supports Valkey, Memcached, and the latest Redis OSS

Amazon Redshift 101

Redshift Overview

• Petabyte-scale data warehouse (DWH) service
  • Columnar, OLAP-optimized database → built for analytics workloads
    • Not a row-based OLTP database like RDS/Aurora
    • Review differences: Database Refresher lecture
  • Capable of ingesting large volumes of data from multiple operational sources and preparing it for analysis
  • Access via SQL interface: supports JDBC/ODBC
    • JDBC → platform-independent, works in Java
    • ODBC → driver-dependent, language-independent
• Private service → deployed in a single AZ within a VPC
  • Not serverless → no public endpoints by default
  • Single-AZ design provides high-performance networking
    • Only AZ-level resilience; no built-in cross-region HA
  • Security controls: VPC, IAM, KMS encryption, CloudWatch monitoring
  • Faster to provision than an on-prem DWH, but still requires setup time
    • For immediate, ad-hoc queries without provisioning, use Athena
• Primarily ETL-oriented, but supports:
  • Redshift Spectrum: query S3 data directly without loading it into Redshift
  • Federated queries: query external databases (AWS and non-AWS) directly
    • Both features require a Redshift cluster but skip the data-loading step, saving time
• Additional features:
  • Billing: pay-per-use
  • Integrates with AWS services: QuickSight, Lake Formation
  • By default, uses public routing for external service access
    • Enhanced VPC Routing can enforce routing via VPC network configuration
      • Custom DNS, security groups, NACLs, VPC gateways

Redshift Architecture

• Redshift cluster deployed in a single subnet (AZ)
  1. Leader Node: coordinates client requests and compute nodes
     • Handles query parsing, planning, and aggregation
     • Connect via JDBC/ODBC
  2. Compute Nodes: execute queries and store data
     • Divided into slices, each with dedicated memory and disk
     • Slices operate in parallel to process workloads efficiently
• Data ingestion sources:
  • Load from AWS services: S3, DDB, RDS
  • Migrate using DMS
  • Stream via Kinesis Data Firehose (S3 as intermediate)
• Data replication:
  • Writes replicated to additional nodes → AZ resilience
  • Supports S3 backups & restores → regional or cross-region storage

Redshift Resilience and Recovery

Snapshots can restore to any region → quick DR deployment if primary AZ/region fails

AZ-resilient by design

Writes replicated to a secondary node

Entire cluster fails if the AZ goes down

Recovery options:

Automatic backups to S3

Occur ~every 8 hours or every 5 GB written

Retention configurable: 0–35 days (default 1 day)

Manual snapshots

Customers manage retention; snapshots persist indefinitely if desired

Backup capacity equal to cluster size is included at no cost

Incremental backups only store changes since the previous backup

Backups benefit from S3 resilience and security

Regional durability by default; global durability via S3 replication