Amazon Redshift – Comprehensive Lecture Review

0.0(0)
Studied by 1 person
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/46

flashcard set

Earn XP

Description and Tags

A set of question-and-answer flashcards covering key concepts, architecture, features, tuning, advantages, and comparisons related to Amazon Redshift as presented in the lecture.

47 Terms

1
New cards

What core type of system is Amazon Redshift classified as, OLTP or OLAP?

It is an OLAP (Online Analytical Processing) system.

2
New cards

Which two architectural pillars primarily drive Redshift’s analytical performance?

Massively Parallel Processing (MPP) and a columnar storage engine.

3
New cards

In what year did Amazon Redshift become generally available?

February 15, 2013.

4
New cards

Redshift’s original code base was derived from which open-source database version?

PostgreSQL 8.0.2.

5
New cards

In Redshift’s shared-nothing MPP design, what resources does each node have exclusively?

Its own CPU, memory, and attached storage.

6
New cards

What is the single entry point for client connections in a provisioned Redshift cluster?

The Leader Node.

7
New cards

Name the three main Redshift compute-node families offered by AWS.

RA3, DC2 (Dense Compute), and DS2 (Dense Storage).

8
New cards

Which node family separates compute from storage and uses Redshift Managed Storage (RMS)?

RA3 nodes.

9
New cards

Within a compute node, what smaller parallel units further subdivide resources?

Slices.

10
New cards

Why does columnar storage reduce I/O for analytical queries?

Because only the required columns’ blocks are read instead of entire rows.

11
New cards

How large, by default, is a disk block in Redshift’s columnar format?

1 MB.

12
New cards

What twofold benefit does data compression provide in Redshift?

Reduced storage cost and less data movement during query execution.

13
New cards

Which COPY-time feature lets Redshift choose optimal compression encodings automatically?

Automatic Compression.

14
New cards

Name Redshift’s purpose-built high-performance compression algorithm for numeric/date data.

AZ64.

15
New cards

What Redshift table property determines where rows are stored across node slices?

Distribution style (DISTSTYLE).

16
New cards

Which DISTSTYLE replicates an entire small dimension table on every node?

DISTSTYLE ALL.

17
New cards

Which DISTSTYLE is best for large fact tables joined on a high-cardinality key?

DISTSTYLE KEY (using a DISTKEY).

18
New cards

What construct allows Redshift to skip disk blocks quickly based on min/max values?

Zone maps created via sort keys.

19
New cards

Compare Compound vs. Interleaved sort keys in one sentence.

Compound favors a left-to-right column prefix pattern, whereas Interleaved gives equal weight to each listed column for unpredictable filters.

20
New cards

Which two SQL commands are essential for Redshift table maintenance?

VACUUM and ANALYZE.

21
New cards

What does the VACUUM command do (two key functions)?

Reclaims space from deleted rows and resort data according to the sort key.

22
New cards

What happens if you omit regular ANALYZE after large data loads?

The optimizer has stale statistics, leading to poor query plans and slow performance.

23
New cards

Name the Redshift feature that lets you query data in S3 without loading it first.

Redshift Spectrum.

24
New cards

In Spectrum, where are the heavy scan and filter operations executed?

On a separate fleet of Redshift Spectrum servers, not on the main cluster.

25
New cards

Which feature temporarily adds transient clusters to handle query surges?

Concurrency Scaling.

26
New cards

How are Concurrency Scaling charges offset for most customers?

Clusters earn free Concurrency Scaling credits (1 hour per day, up to 30 hours).

27
New cards

What capability lets one Redshift cluster share live data with another without copying?

Data Sharing (producer/consumer clusters).

28
New cards

Redshift Serverless bills compute usage in which unit?

Redshift Processing Units (RPUs) per second.

29
New cards

State one primary benefit of Redshift Serverless for teams with variable workloads.

Automatic scale up and scale down to zero, eliminating capacity planning and idle cost.

30
New cards

Which AWS service integrates with Redshift ML to train models from SQL?

Amazon SageMaker.

31
New cards

Give one example use case for Redshift powering BI dashboards.

Real-time gaming metrics analysis (e.g., Zynga monitoring player engagement).

32
New cards

Why is Redshift unsuitable as a primary OLTP database?

It is optimized for large, read-heavy analytical queries, not high-frequency row-level transactions.

33
New cards

Against which two cloud data warehouses is Redshift most often compared?

Snowflake and Google BigQuery.

34
New cards

What architectural shift did RA3 nodes introduce compared with older DS2/DC2 nodes?

Decoupling of compute and storage via Redshift Managed Storage in S3.

35
New cards

Name one advantage Snowflake offers that differs from classic provisioned Redshift.

Fully independent scaling of compute “virtual warehouses” without manual tuning.

36
New cards

What BigQuery pricing model charges per analyzed terabyte?

On-demand (per-TB processed) pricing.

37
New cards

List two major advantages frequently cited for Amazon Redshift.

High performance on large analytic workloads and tight integration with the AWS ecosystem.

38
New cards

List two common disadvantages or challenges of Amazon Redshift.

Requires expertise for distribution/sort key tuning and manual maintenance tasks like VACUUM.

39
New cards

What Redshift feature caches results to serve identical repeated queries instantly?

The Leader Node’s result cache.

40
New cards

Why can data skew hurt performance in Redshift?

Uneven data distribution causes some slices to work harder, reducing parallel efficiency.

41
New cards

Which Redshift command is primarily used for high-speed bulk data loading from S3?

COPY.

42
New cards

Does Redshift enforce primary-key or unique constraints at runtime?

No; they are informational only and must be enforced during ETL externally.

43
New cards

In a lake-house architecture, what role does Amazon S3 normally play alongside Redshift?

It acts as the central data lake storing raw or cold data queried via Spectrum.

44
New cards

When would DISTSTYLE EVEN generally be your safest choice?

For tables without obvious join keys or that seldom participate in large joins.

45
New cards

What maintenance drawback comes with Interleaved sort keys?

Higher overhead for data loading and VACUUM operations.

46
New cards

Why are DC2 nodes recommended for datasets under ~500 GB?

They offer fast local SSDs optimized for maximum query speed on smaller data volumes.

47
New cards

Which Redshift security feature allows encryption of data at rest?

AWS Key Management Service (KMS) integration for encrypted clusters.