Study guide
Technical reference and lesson notes
Purpose of This Lesson
Amazon Redshift is a managed, SQL-based data warehouse for analytical workloads. The key architectural decision is knowing when to use Redshift instead of querying an operational database, a read replica, Amazon S3, or another analytics service.
For certification scenarios, focus on the difference between OLTP systems that process application transactions and OLAP systems that analyze large volumes of historical or consolidated data.
Key Concepts
Amazon Redshift is an OLAP data warehouse
Redshift is designed for:
- Complex analytical queries
- Aggregations across large data sets
- Reporting and business intelligence
- Combining data from multiple operational systems
- Structured and semi-structured data analysis
- SQL-based access through JDBC, ODBC, and BI tools
It is not normally the primary database for an application’s transaction path. Transactional applications are more commonly supported by services such as Amazon RDS, Aurora, or DynamoDB.
OLTP versus OLAP
| Characteristic | OLTP | OLAP |
|---|---|---|
| Primary purpose | Process application transactions | Analyze historical and consolidated data |
| Typical query pattern | Short, frequent reads and writes | Complex queries, joins, and aggregations |
| Data scope | Current operational records | Data collected from one or more sources |
| AWS examples | Amazon RDS, Aurora, DynamoDB | Amazon Redshift, Amazon EMR-based analytics |
| Performance priority | Low-latency transactions | High-throughput analytical queries |
Running intensive reports directly against an OLTP database can consume resources needed by the production application. The appropriate alternative depends on the reporting requirement.
Read replica versus Redshift
A read replica is often sufficient when:
- The source is a single RDS database.
- The reporting workload is relatively straightforward.
- Reports need near-current operational data.
- A separate copy is needed to protect the primary database from read load.
Redshift is more appropriate when:
- Data must be consolidated from multiple databases or systems.
- Queries are complex or scan substantial volumes of data.
- The workload is analytical rather than transactional.
- Users need organization-wide reporting or business intelligence.
- Data is transformed and modeled for repeated analytical use.
A read replica remains synchronized with its source, making it useful for reporting on current data. Redshift generally represents data loaded into a separate analytical store, so loading and transformation pipelines must be considered.
Data sources and loading options
Redshift can receive data from a broad range of sources, including:
- Amazon RDS
- Amazon DynamoDB
- Amazon EMR
- Amazon S3
- AWS Glue
- AWS Data Pipeline
- Amazon EC2-hosted systems
- On-premises servers
The source data may be extracted, transformed, and loaded into Redshift using an appropriate ingestion or orchestration process. The specific service selected depends on the source, transformation complexity, latency requirement, and operational model.
Redshift Spectrum
Redshift Spectrum allows SQL queries to access data stored in Amazon S3 without requiring all of that data to be loaded into Redshift tables first. This is useful when:
- Large data sets are stored in S3.
- Some data is queried less frequently.
- The data lake should remain the system of record for raw or historical objects.
- Queries need to combine Redshift data with external S3 data.
Spectrum complements Redshift; it does not turn S3 into a transactional database. Query performance depends on data layout, file formats, partitioning, and the amount of data scanned.
Managed warehouse operations and durability
Redshift is managed by AWS, which reduces the operational work involved in provisioning, configuration, patching, and backup management. Redshift also maintains redundant copies of data within the service and supports continuous, incremental backups to Amazon S3.
The exam-relevant point is that Redshift provides managed durability and recovery capabilities, but it is still a provisioned analytical system whose cost and performance depend on the selected capacity and configuration. Do not treat it as an unlimited, automatically free data store.
Exam-Relevant Takeaways
- Redshift is primarily an OLAP service, not an OLTP database.
- Use Redshift for complex analytical queries over large collections of structured or semi-structured data.
- Use an RDS read replica for simpler reporting against a single RDS source when near-current data is important.
- Use Redshift when reporting requires data consolidated from multiple operational databases or other systems.
- BI and SQL tools can connect to Redshift using standard JDBC and ODBC interfaces.
- Amazon S3 can be used as a data source for Redshift.
- Redshift Spectrum queries data in S3 using SQL without requiring all queried objects to be ingested into Redshift.
- Redshift supports managed provisioning, configuration, patching, redundancy, and backups.
- Analytical queries should generally be isolated from production transaction workloads.
- Capacity, query performance, data movement, and storage design remain architecture considerations even though Redshift is managed.
Architecture Decision Guide
| Requirement | Recommended approach | Reason |
|---|---|---|
| Application transactions with frequent, low-latency writes | RDS, Aurora, or DynamoDB | Designed for OLTP access patterns |
| Basic reports against one RDS database | RDS read replica | Offloads reads while retaining synchronized data |
| Complex reporting across multiple databases | Amazon Redshift | Centralizes data for analytical queries |
| SQL analysis of large objects remaining in S3 | Redshift Spectrum | Queries S3 data without loading everything into warehouse tables |
| Enterprise BI over curated analytical data | Amazon Redshift with a BI client | Provides SQL access to a centralized warehouse |
| Raw or historical data stored as objects | Amazon S3, optionally queried with Spectrum | Separates durable object storage from warehouse compute |
Common Exam Traps
- Confusing a read replica with a data warehouse: A read replica offloads reads from one database; it does not consolidate and model data from many independent systems like a warehouse.
- Using Redshift for transactional workloads: Redshift is optimized for analytics, not frequent row-level application transactions.
- Assuming Redshift Spectrum copies all S3 data into Redshift: Spectrum allows external S3 data to be queried without first loading the complete data set into warehouse storage.
- Treating S3 as a relational warehouse: S3 provides object storage. SQL access through Spectrum does not make it an OLTP relational database.
- Ignoring data freshness: A read replica can provide near-current data, while a Redshift pipeline may introduce ingestion and transformation delay.
- Assuming managed means unlimited: AWS manages much of the infrastructure, but node or compute selection, query design, data transfer, storage, and scan volume affect cost and performance.
- Running reports on the production primary: Heavy analytical queries can compete with application transactions for database resources.
- Choosing a warehouse solely because the data is large: The query pattern, latency requirement, number of sources, data structure, and freshness requirements must all be evaluated.
Real-World Engineer Notes
- Separate operational and analytical workloads early when reports begin affecting production latency.
- Establish a loading strategy before selecting Redshift: batch loads, recurring extracts, transformations, and failure recovery all affect the design.
- Keep raw source data in S3 when long-term retention, replayability, or independent data-lake access is important.
- Use Redshift for curated, frequently analyzed data and Spectrum for suitable data that can remain external in S3.
- Design reporting around data freshness. A synchronized read replica and a periodically loaded warehouse solve different problems.
- Monitor query behavior and scanned data rather than assuming that a managed warehouse automatically makes every query efficient.
- Protect analytical environments with appropriate IAM, network controls, encryption, auditing, and least-privilege access to source data and S3 objects.
Quick Reference Summary
- Redshift: Managed SQL data warehouse for OLAP.
- Best for: Complex queries, aggregations, BI, and consolidated organizational data.
- Typical inputs: RDS, DynamoDB, EMR, S3, Glue, Data Pipeline, EC2, and on-premises systems.
- Read replica: Best for read offload and simpler reporting from one RDS source.
- Spectrum: SQL access to data stored in S3 without loading the entire data set into Redshift.
- Core distinction: OLTP processes current transactions; OLAP analyzes large or consolidated data sets.
- Operational model: AWS manages much of the warehouse infrastructure, while capacity, query design, ingestion, and cost remain design responsibilities.
Flashcards
- Q: What type of workload is Amazon Redshift designed for?
A: Online analytical processing, or OLAP.
- Q: Is Redshift generally an OLTP database for application transactions?
A: No. It is designed for analytical queries rather than frequent transactional reads and writes.
- Q: When is an RDS read replica a practical reporting solution?
A: When reports primarily use data from one RDS database and need a synchronized, near-current read source.
- Q: When is Redshift preferable to an RDS read replica?
A: When data from multiple systems must be consolidated for complex analytical queries.
- Q: Which standard connectivity interfaces can BI tools use with Redshift?
A: JDBC and ODBC.
- Q: What is the purpose of Redshift Spectrum?
A: To run SQL queries against data stored in Amazon S3 without loading all of that data into Redshift tables.
- Q: Name several possible Redshift data sources.
A: RDS, DynamoDB, EMR, S3, Glue, Data Pipeline, EC2-hosted systems, and on-premises servers.
- Q: Why should analytical queries be separated from OLTP databases?
A: Complex reports can consume resources and reduce the performance of production transactions.
- Q: What kind of data is commonly analyzed in a warehouse?
A: Large collections of structured or semi-structured data, often consolidated from multiple sources.
- Q: Does Redshift Spectrum make S3 a transactional database?
A: No. It provides SQL-based analytical access to S3 objects.
Practice Questions
Question 1
An organization has six regional RDS databases. The analytics team needs to run complex SQL queries across all regions and produce enterprise-wide reports without affecting customer transactions. Which solution best fits this requirement?
A. Run the reports against one regional RDS read replica
B. Deploy Amazon Redshift and load data from the regional databases
C. Run the reports against the production RDS primary instances
D. Store database snapshots in S3 and query them with DynamoDB
Correct answer: B
Explanation: Redshift is intended for centralized OLAP workloads and can consolidate data from multiple transactional sources. A read replica is associated with a source database and does not by itself provide a multi-source analytical warehouse.
Question 2
A company has a single RDS database and needs a simple operational report showing recently created orders. The report must not consume resources on the production database. Which option is the simplest fit?
A. Query an RDS read replica
B. Create a Redshift cluster and reload the entire database hourly
C. Query the RDS automated backups directly
D. Use Redshift Spectrum against the RDS database
Correct answer: A
Explanation: A read replica offloads read traffic while maintaining replicated data from the source. Redshift would add unnecessary extraction and loading complexity for a simple, near-current report from one database.
Question 3
An analytics team stores years of event data as objects in Amazon S3. It wants to query selected data sets using SQL and combine the results with curated tables in Redshift, without importing every S3 object into Redshift. Which capability should be used?
A. RDS read replicas
B. Redshift Spectrum
C. DynamoDB Streams
D. Amazon RDS Multi-AZ
Correct answer: B
Explanation: Redshift Spectrum provides SQL access to external data in S3 and can be used alongside Redshift data. The other options do not provide this S3 analytical query capability.
Question 4
A workload consists of frequent, low-latency customer order writes, with occasional large aggregations for business reporting. Which architecture is most appropriate?
A. Use Redshift as the application’s primary order database
B. Use an OLTP database for orders and isolate reporting on a read replica or Redshift, depending on reporting complexity
C. Store each order as an object in S3 and update it with SQL transactions
D. Run all reports directly against the OLTP primary database
Correct answer: B
Explanation: Transaction processing and analytical processing have different access patterns. The order system should use an OLTP service, while reporting should be offloaded to a read replica for simpler needs or Redshift for complex and consolidated analytics.
Question 5
Which statement about Redshift is most accurate?
A. It is primarily optimized for OLTP workloads.
B. It eliminates the need to consider capacity and query design because it is managed.
C. It is a managed SQL data warehouse designed for complex analytical queries.
D. It can query S3 only after every object has been copied into Redshift tables.
Correct answer: C
Explanation: Redshift is a managed OLAP data warehouse. Managed operations reduce infrastructure work, but capacity, query patterns, ingestion, and cost still require engineering decisions. Redshift Spectrum can query suitable S3 data externally.