Data store management

Choosing a data store

Access pattern first: point reads by key mean DynamoDB, joins and aggregates mean Redshift or Athena, transactions mean RDS or Aurora, text search means OpenSearch, graph traversal means Neptune.

Provisioned vs serverless: serverless (Athena, Redshift Serverless, EMR Serverless, Glue, DynamoDB on-demand, Aurora Serverless) pays per use and suits spiky or unpredictable work. Provisioned (Redshift RA3 clusters with reserved pricing, provisioned DynamoDB, EMR on EC2) is cheaper for steady, heavy, predictable load.

Storage formats and performance

• Use Parquet or ORC with Snappy/ZSTD for analytics, keep objects 128 MB to 1 GB, and partition on the columns queries filter by.
• Row formats (CSV, JSON, Avro) suit ingestion and exchange, not repeated scanning.
• Open table formats (Apache Iceberg, Delta Lake, Hudi) add ACID transactions, upserts and deletes, schema evolution and time travel on S3. Amazon S3 Tables store Iceberg tables with managed compaction. Athena, Glue, EMR and Redshift all read Iceberg.

Amazon S3 as the lake

• Storage classes: Standard, Intelligent-Tiering (moves objects between access tiers automatically, best for unknown or changing access), Standard-IA, One Zone-IA, Glacier Instant Retrieval (milliseconds), Glacier Flexible Retrieval (minutes to hours), Glacier Deep Archive (cheapest, 12+ hours).
• Minimum storage durations (30 days for IA classes, 90 for Glacier Flexible/Instant, 180 for Deep Archive) mean moving short-lived data to cold classes costs more.
• Zones: raw (landing, untouched), cleaned/curated (typed, deduplicated, Parquet), and consumption layers. Separate prefixes or buckets per zone so permissions and lifecycle rules differ.
• Consistency: S3 is strongly consistent for reads after writes and lists.
• Request rates scale per prefix (3,500 writes and 5,500 reads per second per prefix), so spreading across prefixes raises throughput.
• S3 Select / Glacier restore and S3 Access Points are covered under access and lifecycle sections.

Amazon Redshift

Architecture and sizing

• A leader node plans queries; compute nodes hold slices of columnar data. RA3 nodes use managed storage (S3-backed) so storage and compute scale separately. Redshift Serverless scales RPUs automatically and bills per use.
• Concurrency scaling adds transient clusters for bursts of queries. Workload management (automatic WLM or queues with rules) prioritises and limits queries. Short query acceleration fast-tracks small queries.
• Result caching returns repeat query results instantly.

Table design

• Primary and foreign keys are informational only (not enforced), but the planner uses them.
• VACUUM reclaims space and re-sorts; ANALYZE refreshes statistics. Redshift now does both automatically in the background, but a big delete or load may still need manual runs.
• Star schema (a fact table with dimension tables) is the standard warehouse model; denormalise for read speed.

Loading and unloading

• COPY loads from S3, DynamoDB, EMR or SSH hosts in parallel. Best practices: many files, roughly equal size, a multiple of the number of slices, compressed, plus a manifest file to list exact files. Use an IAM role attached to the cluster. Auto-copy (COPY JOB) continuously ingests new files from an S3 prefix.
• UNLOAD writes query results to S3 (Parquet is supported), in parallel, optionally encrypted and partitioned. It’s the way to move data to the lake.
• Don’t insert row-by-row; use COPY or the bulk APIs. A staging table plus MERGE (or delete-then-insert) does upserts.
• Locks: DDL and long transactions can block; check STV_LOCKS / SVV_TRANSACTIONS; TRUNCATE commits implicitly; keep transactions short.

Querying beyond the cluster

DynamoDB

• Keys: a partition key (and an optional sort key). Design for even key distribution. Hot partitions throttle.
• Capacity: on-demand (pay per request, no planning) or provisioned (RCUs/WCUs, with auto scaling and reserved capacity). One RCU is one strongly consistent read per second of up to 4 KB (two eventually consistent). One WCU is one write per second of up to 1 KB.
• Indexes: GSI (different keys, eventually consistent, own capacity, can be added any time), LSI (same partition key, alternate sort key, strongly consistent reads possible, created only at table creation).
• Single-table design puts several entity types in one table, using generic key names and index overloading, to serve known access patterns with one query.
• Features: Streams, TTL, PITR (35 days), on-demand backup, global tables (multi-Region, multi-active), DAX (microsecond cache), export to S3 (from PITR, no impact on capacity) and import from S3.
• Table classes: Standard and Standard-IA (cheaper storage for rarely read data).
• Use PartiQL or the SDK; large scans are costly, so avoid them.

RDS and Aurora

• Multi-AZ gives failover (standby cannot serve reads, except with Multi-AZ DB cluster). Read replicas scale reads and can feed analytics. Aurora stores six copies across three AZs and offers Serverless v2, Global Database and Aurora zero-ETL to Redshift.
• Backups: automated (point-in-time recovery within the retention window, up to 35 days) and manual snapshots, copyable cross-Region.
• Locks: row and table locks in the engine, long transactions and SELECT ... FOR UPDATE cause waits and deadlocks; keep transactions short and index lock paths.
• For analytics don’t run heavy queries on the primary. Use a replica, or export to S3 and query with Athena.

Migration and remote access

Data catalog

The AWS Glue Data Catalog is a central, Hive-metastore-compatible metadata store: databases, tables (schema, location, format, partitions), connections and schema registry entries. Athena, Redshift Spectrum, EMR, Glue and Lake Formation all read it. An Apache Hive metastore can be used instead (EMR can use the Glue Catalog as its metastore).

Metadata types: technical (schema, location, format, partitions), business (owner, description, classification) and operational (freshness, lineage, run stats).

Crawlers

• A crawler connects to a data store through a classifier (built-in or custom), infers schema and partitions and writes tables to the catalog. Sources: S3, JDBC databases, DynamoDB, Delta, Iceberg, Hudi, MongoDB and others.
• Run on demand, on a schedule, or on S3 events (incremental crawls, so only new folders get scanned).
• Schema change policy: update the table, or log only; deletion policy for removed objects.
• Create new connections (JDBC details, network placement, credentials in Secrets Manager) for databases in a VPC.
• Name collisions or “mixed” tables happen when files in one prefix have different schemas, and one crawler may create many tables; group into one table with configuration options.

Keeping partitions in sync

New partitions in S3 aren’t visible until they’re in the catalog. Options:

Classification

Classify data by sensitivity and business rules: crawler classifiers detect format, Macie and Glue sensitive data detection find PII, and tags (and Lake Formation LF-tags) record classification so policies can use it. AWS Glue Schema Registry enforces schema compatibility for streaming producers and consumers.

Data lifecycle

S3 Lifecycle

• Transition actions move objects to cheaper classes after N days (Standard to Standard-IA after 30, to Glacier after 90). Expiration deletes objects at an age, or on a date, and removes expired delete markers. Separate rules for noncurrent versions and for incomplete multipart uploads (a common cost leak).
• Filter by prefix, tag or size. Use Intelligent-Tiering when access is unpredictable (no retrieval fee, small monitoring fee).
• Hot data (recent, queried often) in Standard; warm in IA; cold in Glacier; delete when legal retention ends. Match retention to compliance with S3 Object Lock (WORM; governance or compliance mode, legal holds) and Glacier Vault Lock.
• Versioning keeps every version, protects against overwrite and delete (a delete adds a delete marker). Combine with lifecycle to expire old versions, and MFA delete for extra protection.
• Replication (CRR/SRR) copies objects between buckets for resilience or compliance (needs versioning).

Other lifecycle tools

• DynamoDB TTL: a numeric epoch attribute; items expire within roughly 48 hours (no write cost), deletions appear in Streams, so it is the cheap way to age out data.
• Redshift: UNLOAD old partitions to S3, then delete or move them to Spectrum-backed external tables. Time-series tables keep a rolling window.
• OpenSearch: Index State Management and UltraWarm/cold tiers.
• AWS Backup: centrally scheduled backups with retention across RDS, DynamoDB, EFS, S3, Redshift and more, with cross-account and cross-Region copy and vaults with Vault Lock.
• Resiliency: multi-AZ by default for managed services, Redshift snapshots with cross-Region copy, DynamoDB PITR and global tables, S3 replication.

Data modeling and schema evolution

• Relational/dimensional (star or snowflake) for the warehouse; normalised for OLTP; key-value/document for DynamoDB; wide-column for Keyspaces; graph for Neptune.
• Structured (tables), semi-structured (JSON, XML, logs: Redshift SUPER type with PartiQL, Athena struct/array types), unstructured (images, text, audio: store in S3, keep metadata in the catalog).
• Partitioning: on low-to-medium-cardinality columns commonly used in filters (date, region). Too many small partitions slow planning; hive-style year=2026/month=11/. Bucketing helps joins.
• Indexing: sort keys, DynamoDB GSIs/LSIs, OpenSearch indexes, RDS B-tree indexes.
• Compression and columnar formats cut I/O and cost.
• Schema evolution: adding optional columns is easy in Parquet/Avro/Iceberg (Iceberg also supports rename and drop safely by column ID). Use the Glue Schema Registry for compatibility modes (backward, forward, full), Athena tables over Parquet (match by name or index), and Glue ResolveChoice for type conflicts. Redshift ALTER TABLE ADD COLUMN.
• Schema conversion: use SCT for heterogeneous migrations (shows an assessment report of what can’t convert automatically). DMS Schema Conversion does it in the console.
• Data lineage (where data came from and how it changed): Glue (data lineage in the catalog and DataZone), Amazon SageMaker ML Lineage Tracking (tracks ML artifacts), and CloudTrail plus tags. Lineage builds trust and supports audits.
• Changing data characteristics: volume and velocity growth means re-partitioning, compaction, new instance types or on-demand modes; slowly changing dimensions (type 1 overwrite, type 2 history rows).