Data Store Management: free practice questions

Options A and C are correct. Applying KEY distribution on the large `orders` fact table using the customer_id join column co-locates matching rows from both tables on the same slice, eliminating network shuffling during joins. Applying ALL distribution on the small `customers` dimension table (500,000 rows) broadcasts a full copy to every slice, ensuring that the join can always be resolved locally. This KEY+ALL pattern is the standard Redshift best practice for large fact-to-small-dimension joins. Option B is wrong because applying ALL distribution to a 2-billion-row fact table would be extremely slow to maintain and consume enormous storage on every node. Option D is wrong because EVEN distribution spreads rows randomly, which does not co-locate join keys and does not eliminate shuffling. Option E is wrong because distributing `customers` on order_id makes no logical sense—customers do not have order_ids as a natural key—and would not co-locate the join.

Option B is correct. Lake Formation data filters (row-level and cell-level security) allow administrators to define a filter expression (e.g., `region = 'US'`) and associate it with a permission grant for a specific principal—the data science team will only ever see US rows when querying through Lake Formation-integrated engines. AWS CloudTrail automatically captures Lake Formation API calls and, for governed tables, records data access events, providing the required audit trail. This combination is the native, purpose-built Lake Formation solution. Option A is partially correct in concept but inaccurate: CloudTrail data events on S3 capture low-level S3 API calls (GetObject), not the logical SQL query context; Lake Formation's own audit logging via CloudTrail for governed tables is more meaningful for compliance. Option C is wrong because duplicating data creates consistency risks, S3 bucket policies do not enforce row-level security (a malicious user with S3 access could read all rows), and S3 server access logging captures HTTP requests, not SQL-level audit trails. Option D is wrong because Amazon Macie is a data security and privacy service that classifies sensitive data—it does not filter query results in real time—and CloudWatch Logs does not automatically capture Lake Formation data access events.

Option A is correct. Using `device_id` as the partition key distributes write traffic evenly across 100,000 devices (avoiding hot partitions), and using `timestamp` as the sort key allows efficient, index-native time-range queries (BETWEEN, >, <) within a device's partition using the Query API—no full table scans needed. This is the canonical DynamoDB design pattern for time-series device data. Option B is wrong because partitioning by `timestamp` would cause hot partition issues: all 100,000 devices writing every 10 seconds would concentrate writes into a very small set of partition key values (recent timestamps), creating severe throttling. Option C is wrong because using only `device_id` without a sort key stores only the latest item per device (DynamoDB requires a unique primary key), making it impossible to store multiple readings per device—or if using a workaround, a Scan with FilterExpression is an O(n) full-table operation that is extremely inefficient for hundreds of billions of rows. Option D is wrong because using a UUID as the partition key distributes items randomly, making it impossible to efficiently retrieve readings for a specific device without a GSI; the GSI on `device_id` would work, but GSIs do not support sort-key range queries on `timestamp` unless `timestamp` is the GSI sort key, making Option A a more direct and efficient design.

Option C is correct. Short-Query Acceleration (SQA) is a Redshift WLM feature specifically designed for this scenario. When enabled, Redshift uses machine learning to predict query execution time and automatically routes short-running queries (below the configured maximum SQA run time) to a dedicated SQA queue that runs ahead of user-defined WLM queues. Setting the maximum SQA run time to 5 seconds ensures that dashboard queries meeting the SLA threshold are promoted ahead of long ETL jobs without requiring manual queue assignment or user group configuration, making it the most targeted and operationally simple solution. Option A is wrong because Concurrency Scaling adds transient cluster capacity for read queries experiencing high concurrency, but it does not prioritize short queries over long ones — dashboard queries would still queue behind ETL jobs within the main cluster before being routed to a scaling cluster, and the delay may persist. Option B is wrong in comparison to C because it requires manual operational overhead (user group or query group assignments, ongoing tuning of concurrency limits) and introduces risk of misconfiguration; SQA achieves the same outcome automatically and is the AWS-recommended approach for mixed short/long workloads. Option D is wrong because scaling out the cluster adds capacity but does not change the scheduling priority; short queries will still queue behind ETL jobs in the same WLM queue, failing the dashboard SLA. Option E is wrong because increasing concurrency to 50 in a single queue degrades per-query performance due to resource contention (Redshift recommends keeping total WLM concurrency low), and a query timeout only terminates queries rather than prioritizing short ones.

Automatic WLM with priority settings is the recommended modern approach in Redshift. By assigning higher priority to ETL queries and lower priority to dashboard queries, Redshift automatically manages concurrency and memory allocation so ETL queries are no longer starved behind long-running dashboard queries. Option B (adding more nodes) increases overall throughput but does not address queue prioritization; ETL queries will still queue behind dashboard queries. Option C (manual WLM) was the legacy approach and can work, but it requires careful, manual slot and memory tuning. More importantly, the question asks for the 'most appropriate' solution, and AWS guidance recommends automatic WLM over manual WLM for new configurations. Option D (Redshift Serverless) is a valid architectural pattern but involves migrating workloads to a separate environment, which is a significant operational change and not the intended WLM solution.