5 Amazon Redshift in Practice
Learn how Amazon Redshift supports large-scale analytics, how to load and query sales data, and how to choose between a warehouse and an operational database.
Redshift for analytics
is a relational designed for analytical queries over large datasets. Its , columnar storage, and compression help it scan and aggregate data efficiently for reporting and analysis.
Typical uses include sales dashboards, trend analysis, and combining historical data from multiple systems.
Warehouse and operational workloads
An supports the day-to-day activity of an application. Such workloads, often called , include looking up or changing an individual customer, processing an order, or recording a payment.
A supports questions across many records. These analytical workloads, often called , commonly read and aggregate large volumes of data. Examples include asking which products generated the most revenue each month or how sales changed year over year.
Redshift can perform SQL inserts and updates, but it is optimized for analytics and reporting—not as the primary database for frequent, low-latency application transactions. A transactional relational database such as Amazon RDS or Aurora, or a NoSQL database such as DynamoDB, may be a better fit when its capabilities match the application's operational access patterns.
Create a sales table
In a real warehouse, a records events such as sales, while dimension tables add descriptive context such as products, customers, or dates. This exercise uses a small sales with columns for an identifier, date, product, region, quantity, and unit price.
Create the table in a Redshift database:
Load data from Amazon S3
Prepare a CSV file with columns in this order: sale_id,sale_date,product,region,quantity,unit_price. Example rows are:
Place the file in an S3 bucket and give Redshift an that permits access to the bucket. Replace the example bucket path and role ARN with your own values:
The command is the preferred approach for bulk loading. Redshift can load data in parallel, and this approach is generally more efficient than inserting a large dataset row by row.
Query and summarize sales
Use SQL aggregation, grouping, and time-based queries to analyze the loaded data. To check how many records were loaded:
Revenue is quantity multiplied by unit price: . To calculate revenue by region and order regions by revenue from highest to lowest:
To compare monthly revenue by product:
These queries read and summarize multiple rows to answer business questions, which is characteristic of warehouse analysis.
Choose a database for the workload
Choose a database based on the application's access pattern and the question being answered:
A checkout service that must update an order and retrieve it immediately needs an suited to the application's transactions.
An analyst who needs quarterly revenue across years of orders can use Redshift for warehouse analytics.
An application retrieving a user's profile by a known key at scale can consider DynamoDB when its key-value access pattern fits.
A common architecture sends or copies operational data into a warehouse for analysis. The warehouse can run reporting queries without making the application's primary database responsible for every large analytical scan. An and a can therefore serve complementary roles.