Amazon Redshift

Amazon Redshift is a fully managed, petabyte-scale data warehouse service that makes it simple and cost-effective to analyze all your data using standard SQL and your existing business intelligence tools.

Key Concepts

Data Warehouse Architecture

• Columnar Storage: Optimized for analytical queries
• Massively Parallel Processing (MPP): Distributed query processing
• Node Types: Leader node and compute nodes
• Clusters: Collection of nodes working together

Cluster Types

• Single Node: Development and testing
• Multi-Node: Production workloads
• RA3 Instances: Managed storage with pay-per-use
• DC2 Instances: Dense compute with local storage

Data Distribution

• Distribution Keys: How data is distributed across nodes
• Sort Keys: How data is sorted within nodes
• Compression: Automatic data compression
• Zone Maps: Metadata for query optimization

Common Interview Questions

Basic Concepts

1. What is Amazon Redshift and how does it work?
2. Explain the difference between Redshift and RDS
3. What are the advantages of using Redshift?
4. How does Redshift handle data warehouse architecture?

Cluster Management

5. How to create and manage Redshift clusters?
6. What are the different Redshift node types?
7. Explain Redshift cluster scaling and resizing
8. How to handle Redshift cluster maintenance?

Data Modeling and Design

9. How to design effective Redshift table schemas?
10. Explain Redshift distribution keys and their importance
11. What are Redshift sort keys and when to use them?
12. How to implement Redshift data modeling best practices?

Performance Optimization

13. How to optimize Redshift query performance?
14. Explain Redshift query planning and execution
15. What are Redshift workload management (WLM) queues?
16. How to handle Redshift query monitoring and tuning?

Data Loading and ETL

17. How to load data into Redshift efficiently?
18. Explain Redshift COPY command and its options
19. How to implement Redshift data loading best practices?
20. What are Redshift data loading patterns and strategies?

Security and Compliance

21. How does Redshift handle encryption and security?
22. Explain Redshift IAM integration and permissions
23. How to implement Redshift VPC and network security?
24. What are Redshift audit logging and compliance features?

Integration and Tools

25. How to integrate Redshift with other AWS services?
26. Explain Redshift integration with BI tools
27. How to use Redshift with data lake architectures?
28. What are Redshift integration patterns with S3 and Glue?

Advanced Features

29. How to implement Redshift cross-region replication?
30. Explain Redshift concurrency scaling
31. How to handle Redshift backup and restore?
32. What are Redshift machine learning integration features?

Monitoring and Maintenance

33. How to monitor Redshift cluster performance?
34. Explain Redshift CloudWatch metrics and alarms
35. How to handle Redshift cluster maintenance windows?
36. What are Redshift troubleshooting and optimization techniques?

Interview angle

• “Redshift or Athena?” - Redshift when you have sustained analytical query volume and want provisioned, tuned compute; Athena when queries are ad hoc and infrequent, because you pay per scanned byte with no cluster to keep alive. Redshift Serverless blurs the line and is the safe default for spiky workloads.
• “Why is a columnar store faster for analytics?” - a query touching 3 of 200 columns reads only those 3, and column-homogeneous data compresses far better. Row stores read the whole row. The flip side is that single-row writes and updates are expensive, which is why this is not your OLTP database.
• “What actually makes a Redshift query slow?” - a bad distribution key causing data to shuffle between nodes, or a sort key that does not match the query’s filter. Both are physical-design choices, and both are the first thing to check before adding nodes.
• “How does data get in?” - COPY from S3 in bulk, never row-by-row INSERT. Zero-ETL integrations from Aurora and DynamoDB remove the pipeline entirely for those sources.