Data Store Management
Free AWS Certified Data Engineer - Associate practice — 6 questions on Data Store Management, with explanations. No sign-up. Full 12-question mixed test →
A retail analytics team has a Redshift RA3 cluster with a 2 billion-row FACT_SALES table and a 200-row DIM_STORE table. Queries join FACT_SALES to DIM_STORE on store_id and also filter FACT_SALES by sale_date ranges. FACT_SALES is currently distributed EVEN and sorted by store_id, causing broadcast joins and full-column scans. Which redesign BEST optimizes both join performance and range-filter performance?
DIM_STORE is tiny, so DISTSTYLE ALL replicates it to every slice cheaply, eliminating network shuffle for the join. FACT_SALES should use DISTKEY on store_id (the join column) so matching rows are colocated with the replicated dimension, and the sort key should be sale_date since queries filter on date ranges, enabling zone-map pruning.
A DynamoDB table stores IoT sensor readings with partition key = device_id and sort key = timestamp. One device (device_id = 'HUB-001') generates 90% of all writes because it aggregates readings from thousands of child sensors, causing consistent ProvisionedThroughputExceededException errors on that single partition even though the table has adaptive capacity enabled. What is the MOST effective fix?
Adaptive capacity helps with moderately unbalanced access but cannot fully overcome a single logical partition key receiving a disproportionate share of traffic — the physical partition throughput ceiling still applies. Sharding the hot key with a random or calculated suffix distributes writes across multiple physical partitions, which is the standard DynamoDB pattern for hot-partition mitigation.
A company uses AWS Lake Formation to manage a data lake shared across two AWS accounts. Account A owns the Glue Data Catalog and S3 data. Account B's analysts need query access, but only to rows where region = 'EU' in a customer table, and must never see the ssn column. Which Lake Formation feature combination correctly enforces this cross-account requirement?
Lake Formation supports fine-grained row-level and column-level permissions through data filters, and these can be granted cross-account. Account B accesses the shared table via a resource link, and Lake Formation enforces the row predicate (region='EU') and column exclusion (ssn) at query time for any Lake Formation-integrated engine (Athena, Redshift Spectrum, EMR), without needing to duplicate data.
A media company stores 50 TB of video thumbnails in S3 Intelligent-Tiering. Some thumbnails are accessed unpredictably (bursty campaigns), some haven't been accessed in over 200 days, and a small subset must be retrievable within milliseconds even if unused for a year, while another subset can tolerate 12-hour retrieval delays to minimize cost further. Which configuration correctly achieves this using S3 Intelligent-Tiering's archive access tiers?
S3 Intelligent-Tiering supports optional Archive Instant Access (millisecond retrieval, similar cost profile to Glacier Instant Retrieval) and Deep Archive Access (up to 12-hour retrieval, lowest cost) tiers, which you opt into; objects automatically move into these tiers based on configurable no-access-day thresholds (minimum 90 and 180 days respectively), without manual intervention or data deletion.
A financial services firm runs an Aurora MySQL cluster as its primary transactional database in us-east-1, serving read-heavy reporting workloads from three read replicas in the same region. Leadership now requires disaster recovery with an RPO under 1 second and the ability to serve low-latency local reads for a new APAC customer base, without re-architecting the application's write path. Which solution meets both requirements?
Aurora Global Database is purpose-built for this exact scenario: it replicates data to secondary regions using dedicated storage-based replication with typical lag under 1 second (RPO < 1s), the secondary region's replicas serve low-latency local reads for APAC users, and it supports managed planned/unplanned failover — all without changing the application's single write endpoint in us-east-1.
A data engineering team queries a 40 TB dataset stored as Parquet files in S3 using Redshift Spectrum from an RA3 cluster. Query performance is poor because Spectrum scans far more data than expected for queries that filter on 'transaction_date' and 'country'. The Glue Data Catalog table for this data is currently defined as a single flat table with no partitioning. What change will MOST directly reduce the data scanned per query?
Redshift Spectrum leverages partition pruning based on the Glue Data Catalog's partition metadata — if the underlying S3 data is organized by partition keys matching common filter columns and those partitions are registered in the catalog, Spectrum skips reading files outside the matching partitions entirely, directly cutting the data scanned (and cost, since Spectrum charges per TB scanned).
Ready for the real thing?
The full course: two full-length practice tests, video lessons for every exam domain, hands-on labs and detailed explanations.
$109.99 $34.99 with code FREETEST33 — valid through September 14.
Get my $34.99 deal →