Data Warehousing on AWS Training Data Warehousing on AWS: Trends, Network And Critical Thinking 3 — Questions and Answers
Question 1: A business analyst reports that their Redshift queries are running slower than expected. After investigation, you find that table statistics are outdated. Which command resolves this?
- VACUUM FULL
- ANALYZE (Correct answer)
- REINDEX
- REFRESH MATERIALIZED VIEW
Correct answer: ANALYZE
The ANALYZE command updates table statistics used by the query planner, enabling it to generate optimal execution plans.
Question 2: Which Redshift feature allows you to query data directly in Amazon S3 without loading it into the cluster, representing a key trend toward data lake integration?
- Redshift COPY command
- Redshift Spectrum (Correct answer)
- AWS Glue ETL
- Amazon Athena Federation
Correct answer: Redshift Spectrum
Redshift Spectrum extends SQL queries to exabytes of unstructured data in S3, integrating data warehouse and data lake queries in a single statement.
Question 3: A company's Redshift cluster experiences high concurrency from BI users. Queries from the executive team are being queued behind analyst queries. What should be configured?
- Workload Management (WLM) queue with priority lanes for executive users (Correct answer)
- Add more slices to the cluster nodes
- Enable Concurrency Scaling for the executive users only
- Create a separate Redshift cluster exclusively for the executive team
Correct answer: Workload Management (WLM) queue with priority lanes for executive users
WLM allows you to define separate queues with different priorities and resource allocations, ensuring high-priority queries are not starved by lower-priority workloads.
Question 4: Which critical thinking consideration explains why columnar storage is better than row storage for analytical workloads?
- Columnar storage updates individual records faster
- Column storage reduces I/O by reading only the columns needed for a query (Correct answer)
- Columnar storage eliminates the need for indexes entirely
- Columnar storage improves OLTP insert throughput
Correct answer: Column storage reduces I/O by reading only the columns needed for a query
Analytical queries typically aggregate a few columns across millions of rows; columnar storage reads only those columns from disk, drastically reducing I/O compared to reading full rows.
Question 5: A data architect is evaluating whether to use AWS Lake Formation or Amazon Redshift alone for a new analytics platform. Which scenario best justifies choosing a lakehouse architecture combining both?
- All data is structured and fits within 2TB of storage
- Data includes structured, semi-structured, and unstructured formats with varying query frequencies (Correct answer)
- The team has no experience with SQL
- All queries need sub-second response times from a single table
Correct answer: Data includes structured, semi-structured, and unstructured formats with varying query frequencies
A lakehouse combining Lake Formation and Redshift handles diverse data formats by storing infrequently accessed or unstructured data in S3 while keeping hot structured data in Redshift.
Question 6: An organization's Redshift cluster is in us-east-1 and they need a disaster recovery copy in us-west-2. Which feature automates this?
- Manual snapshots copied via AWS CLI on a schedule
- Cross-Region Snapshots with automated snapshot copy enabled (Correct answer)
- Redshift Multi-AZ deployment spanning both regions
- AWS DataSync replication between the two clusters
Correct answer: Cross-Region Snapshots with automated snapshot copy enabled
Enabling cross-region snapshot copy in Redshift automatically replicates automated snapshots to a specified destination region for disaster recovery.
Question 7: Which network connectivity option provides the most consistent, low-latency connection between an on-premises data center and Amazon Redshift?
- AWS Site-to-Site VPN over the public internet
- AWS Direct Connect with a dedicated private virtual interface (Correct answer)
- AWS Transit Gateway with internet-facing attachment
- Amazon CloudFront distribution to the Redshift endpoint
Correct answer: AWS Direct Connect with a dedicated private virtual interface
AWS Direct Connect provides a dedicated, private network connection with consistent throughput and lower latency than internet-based VPN connections.
Data Warehousing on AWS Training Data Warehousing on AWS: Trends, Network And Critical Thinking 3
A business analyst reports that their Redshift queries are running slower than expected.
After investigation, you find that table statistics are outdated.
Which command resolves this?