Data Store Management practice questions

Amazon Redshift RA3 node types use Redshift Managed Storage (RMS), which separates compute from storage. Data is durably stored in S3-backed managed storage and cached on local SSDs, so you size the cluster for compute and pay for storage based on usage.

The team's problem is a compute-storage imbalance: full disks with idle CPU. On DC2 nodes, storage is bound to node-local SSDs, so the only way to grow storage is to add nodes and their compute cost. RA3 with Redshift Managed Storage removes that coupling — managed storage grows automatically, hot blocks stay in the local SSD cache, and colder blocks live in S3-backed storage without any manual data movement. This directly satisfies both requirements: independent storage growth and automatic tiering.

1. Recognize the symptom: storage-bound cluster with low CPU means compute and storage needs have diverged.
2. Recall that DC2 nodes couple storage to node count while RA3 nodes use Redshift Managed Storage backed by S3.
3. Migrate to RA3 (via elastic resize, snapshot restore, or classic resize) so storage scales independently and tiering is automatic.

Exam tip: RA3 nodes with Redshift Managed Storage decouple compute from storage and tier data to S3 automatically.

Amazon Redshift Serverless removes cluster management: it measures capacity in Redshift Processing Units (RPUs), scales automatically with demand, and charges only while workloads run, making it suited to intermittent or unpredictable analytics.

The requirements are (1) no cluster sizing or management and (2) pay only for compute consumed. Redshift Serverless meets both: there is no cluster to provision, capacity scales up when queries arrive and down to zero cost when idle. Reserved nodes and concurrency scaling both presuppose a provisioned, always-billing base cluster, and a long-running EMR cluster adds even more operational burden. Serverless is the only choice where an idle workload costs nothing for compute.

1. Identify the workload pattern: idle most of the time with unpredictable bursts.
2. Map the billing requirement: pay-per-use compute means no always-on provisioned cluster.
3. Choose Redshift Serverless, which auto-scales RPU capacity and bills only for compute used during query execution.

Exam tip: For intermittent, unpredictable analytics with no cluster management, choose Amazon Redshift Serverless.

Redshift distribution styles determine which compute node slice stores each row. Joins are fastest when matching rows are already on the same slice: KEY distribution co-locates rows sharing a join column, and ALL distribution replicates small tables to every node.

The bottleneck is runtime redistribution (broadcast or shuffle) during the fact-dimension join. Distributing the large fact table by the join column customer_id means all rows for a given customer live on one slice. Replicating the small 5 GB dimension with ALL distribution guarantees every node already holds the dimension rows it needs. Together these make the join fully local. Reversing the styles would replicate 2 TB everywhere, EVEN ignores the join key entirely, and sort keys address scan pruning rather than row placement.

1. Diagnose the slowdown: data redistribution during joins means joined rows are not co-located.
2. Distribute the large fact table with DISTSTYLE KEY on the join column customer_id.
3. Set the small dimension table to DISTSTYLE ALL so every node holds a full copy and the join runs locally.

Exam tip: Co-locate joins in Redshift: KEY-distribute the big table on the join column and ALL-distribute small dimensions.

Redshift sort keys physically order rows on disk. Each block stores min/max metadata (zone maps), so range predicates on the sort key let the engine skip blocks whose ranges cannot match, dramatically reducing scanned data.

The dominant query pattern is a range filter on event date. Sorting the table by that column clusters each date's rows into contiguous blocks; a query for the last 7 days then reads only the blocks whose zone-map ranges overlap those dates instead of scanning the whole table. Distribution styles govern which node holds a row, not the order within it, so neither ALL nor KEY distribution prunes scans — and Redshift has no user-defined B-tree secondary indexes at all.

1. Identify the dominant predicate: range filters on the event date column.
2. Recall that sort keys plus zone maps allow Redshift to skip blocks outside the filtered range.
3. Define the date column as the sort key (a compound sort key with date first) so recent-date queries scan only relevant blocks.

Exam tip: Sort keys plus zone maps are how Redshift prunes range scans — sort on the column you filter by most.

Amazon Redshift Spectrum extends Redshift SQL to data stored in Amazon S3. You define an external schema pointing at the AWS Glue Data Catalog, and Spectrum's fleet scans the S3 files, so data is queried in place and joined with local warehouse tables.

The requirements are: keep the 500 TB in S3, query it from Redshift occasionally, and avoid load cost. Spectrum matches each requirement — the Parquet data stays in S3, external tables come straight from the existing Glue Catalog entries, and you pay for the data scanned rather than for permanently expanded cluster storage. Federated queries target operational databases, not S3; COPY performs the very load being avoided; and materialized views summarize data Redshift can already reach rather than providing S3 access themselves.

1. Confirm the data should stay in S3 and is already cataloged in the Glue Data Catalog.
2. Create an external schema in Redshift that references the Glue database, exposing the S3 tables as external tables.
3. Write standard SQL that joins the external (Spectrum) tables with local Redshift tables; Spectrum scans only the S3 data each query needs.

Exam tip: Redshift Spectrum queries S3 data in place via Glue Catalog external schemas; federated queries are for RDS and Aurora.

Amazon Redshift federated queries let the warehouse issue queries against live operational data in Amazon RDS for PostgreSQL/MySQL and Aurora PostgreSQL/MySQL by defining an external schema over a secured connection, pushing eligible predicates down to the source database.

The requirement is real-time access to operational Aurora data from Redshift SQL without ETL. Federated queries do precisely this: create an external schema referencing the Aurora database (credentials in AWS Secrets Manager), then join its live tables with warehouse tables in one query. Spectrum's scope is S3 files, UNLOAD moves data in the wrong direction, and snapshot-based batch loading reintroduces both ETL and staleness.

1. Store the Aurora credentials in AWS Secrets Manager and ensure network connectivity from the Redshift cluster to the Aurora instance.
2. Create an external schema in Redshift using CREATE EXTERNAL SCHEMA ... FROM POSTGRES pointing at the Aurora database.
3. Query and join the external operational tables with local warehouse tables in standard Redshift SQL; predicates are pushed down to Aurora where possible.

Exam tip: Use federated queries for live RDS/Aurora data from Redshift; use Spectrum for data in S3.