Data Store Management

A data engineer stores clickstream data in S3 under the prefix s3://analytics-lake/events/year=2024/month=03/day=15/ using Hive-style partition folders. A Glue Data Catalog table was created by a crawler two months ago. New daily folders are being written continuously by a Firehose stream, but Athena queries that filter on recent dates return zero rows even though the files exist in S3. The engineer confirms the schema and file format are correct. What is the MOST cost-effective way to ensure Athena can query the newly added partitions going forward with minimal ongoing operational effort?

A retail company is building a product catalog service for its e-commerce platform. Each product record has a different set of attributes depending on category (electronics have specifications like resolution and ports, while apparel have size and material). Products are looked up by product ID at very high request rates with single-digit millisecond latency requirements, and the schema evolves frequently as new categories are added. Which data store best fits these requirements?

A gaming company runs a multiplayer platform that must store player session state, such as current score, position, and inventory. The application requires single-digit millisecond read and write latency at any scale, and access is always by a known player ID. Traffic is highly unpredictable, spiking during tournaments to millions of requests per second. The data engineering team wants a fully managed data store that automatically scales without capacity planning and minimizes operational overhead. Which AWS data store best meets these requirements?

An IoT company ingests billions of sensor readings per day. Each reading has a device ID, timestamp, and metric value. The primary access pattern is to retrieve the most recent readings for a specific device by device ID, and single-digit millisecond read/write latency is required at massive scale. The data volume grows continuously and the team wants a fully managed, serverless data store that scales automatically without capacity planning for servers. Which data store best fits this use case?

A retail company stores order records in a DynamoDB table using OrderId as the partition key. Business analysts increasingly need to query orders by CustomerId and by OrderStatus, but these attributes are not part of the table's primary key. The queries must return results with low latency and must not require scanning the entire table. What is the MOST appropriate way to enable these access patterns?

A gaming company stores player match records in an Amazon DynamoDB table. Business rules require that each match record be automatically deleted 90 days after it is created to comply with a data retention policy, and the deletion should incur no additional write capacity charges. The team wants the least operational overhead. Which approach best meets these requirements?

A data engineering team stores clickstream data in Amazon S3 using a Hive-style layout: s3://logs/year=2024/month=05/day=12/. New daily partitions are added continuously. Analysts query the data through Amazon Athena, but query planning has become slow because millions of partitions must be fetched from the AWS Glue Data Catalog, and running a crawler after every load adds operational overhead. The team wants to eliminate partition-management overhead while keeping fast query planning. What should they do?

A financial services company stores customer transaction data in a data lake on Amazon S3, cataloged in the AWS Glue Data Catalog. A team of data analysts needs to query the data through Amazon Athena, but they must NOT be able to see the columns containing customer Social Security numbers and account numbers. The security team wants centralized, fine-grained access control that does not require modifying the underlying S3 objects or maintaining separate copies of the data. What is the MOST appropriate way to meet these requirements?

A data engineering team manages a data lake with hundreds of tables across dozens of databases in the AWS Glue Data Catalog, governed by AWS Lake Formation. As the organization grows, they need to grant fine-grained permissions to many teams based on data classification (e.g., 'confidential', 'public') and department ('finance', 'marketing'). Managing individual named-resource grants for each table and principal has become unmanageable. Which approach best scales permission management in Lake Formation?

A data engineering team manages a 40-node Amazon Redshift cluster supporting a growing analytics platform. Query patterns are evolving as new dashboards are added, and the team lacks the capacity to continuously monitor and hand-tune distribution and sort keys on hundreds of tables. They want Redshift to observe workloads and adjust sort and distribution keys over time without dropping and recreating tables manually. Which approach best meets this requirement with the least ongoing operational effort?

A data engineering team runs an Amazon Redshift cluster storing a 4-billion-row fact table. Storage costs are rising and query scans are slow. The table was created without specifying column encodings, and most columns currently use RAW encoding. The team wants Redshift to determine the most effective compression for each column based on the actual data. Which approach should they use?

A retail analytics team runs an Amazon Redshift cluster that performs well most of the day. However, every morning between 8 AM and 10 AM, hundreds of business users run dashboard queries simultaneously, causing queries to queue and dashboards to load slowly. The rest of the day the cluster is lightly used, and the team does not want to permanently resize the cluster or pay for extra capacity outside the peak window. Which approach best resolves the morning slowdown while minimizing cost?

A data engineering team loads a large fact table into Amazon Redshift nightly from thousands of small gzip CSV files stored in an S3 prefix. The load currently uses one COPY command per file issued serially, and the nightly job is missing its SLA. The cluster has 8 slices. What is the MOST effective change to improve load throughput while keeping costs low?

A retail analytics team stores 8 years of immutable transaction history (about 60 TB) in Parquet files on Amazon S3, partitioned by year and month. Analysts query the most recent 3 months daily for dashboards, but the older data is queried only a few times per quarter for ad hoc audits. The team wants to minimize storage costs for the cold data while keeping fast, frequent queries on the recent data performant, all within a single query interface. Which approach best meets these requirements?

A retail analytics company runs a production Amazon Redshift RA3 cluster that holds curated sales data. A separate data science team has its own Redshift RA3 cluster and needs read-only access to the latest curated sales tables for machine learning feature engineering. The data must always reflect the producer cluster's current state without copying, and the producer team wants to avoid ETL jobs or scheduled unloads. Which approach best meets these requirements?

A financial services company runs a provisioned Amazon Redshift cluster that holds curated transaction data. A separate business unit within the same AWS account operates its own Redshift Serverless workgroup and needs read-only access to the latest version of several curated tables for ad hoc analytics. The data changes throughout the day, and the business unit must always see current data without any ETL copying or scheduled refresh jobs. Which approach meets these requirements with the least operational overhead?

A data engineering team runs Amazon Redshift for a retail analytics warehouse. The two largest tables are a 5 TB fact_sales table and a 4 TB fact_returns table, both joined frequently on the customer_id column in nightly reporting queries. Query monitoring shows heavy data redistribution (DS_BCAST_INNER and DS_DIST_BOTH steps) during these joins, causing long runtimes. Which schema design change will most effectively reduce the data movement during these joins?

A data engineering team runs an Amazon Redshift data warehouse holding several years of historical sales facts. A separate Amazon Aurora PostgreSQL database stores the live customer profile table, which is updated continuously by the transactional application. Analysts need to run daily reports that join the historical sales data in Redshift with the most current customer profile data, but the team wants to avoid building and scheduling an ETL pipeline that copies the profile table into Redshift. Which approach best meets these requirements?

A data engineering team runs a business intelligence dashboard backed by Amazon Redshift. The dashboard executes the same complex aggregation query (multiple joins across a large fact table and several dimension tables, plus GROUP BY rollups) every few minutes for hundreds of users. The underlying fact table receives incremental inserts throughout the day. Query latency has become unacceptable, and the team wants to reduce repeated computation while keeping results reasonably fresh with minimal maintenance overhead. Which approach best addresses this?

A retail analytics team runs an Amazon Redshift cluster on DC2 node types. Their query performance is acceptable, but data volume is growing rapidly and they are constantly forced to add nodes purely to gain more disk space, which also adds unneeded compute cost. They want to scale storage independently from compute while retaining fast local access to hot data. Which change best addresses this requirement?