Live Online Amazon Redshift Course for Data Engineering

Live Course Module: Amazon Redshift Course for Data Engineering

Total Duration: 40 Hours (4 Weeks)

---

WEEK 1: Introduction to Redshift and Cloud Data Warehousing

Duration: 8 Hours (4 Sessions × 2 Hrs)**

Topics:

1. Introduction to AWS & Redshift (2 hrs)

   • Overview of AWS cloud ecosystem

   • What is Amazon Redshift and how it fits in data engineering

   • Redshift architecture: Leader node, compute nodes, clusters

   • Redshift vs BigQuery vs Snowflake

2. Setting Up Amazon Redshift (2 hrs)

   • Creating AWS account and IAM roles

   • Launching a Redshift cluster

   • Connecting via Query Editor, SQL Workbench/J, and psql

3. Loading Data into Redshift (2 hrs)

   • Loading data from Amazon S3 using COPY command

   • Working with CSV, JSON, and Parquet formats

   • Using AWS Glue Data Catalog

4. Hands-on Lab & Mini Project (2 hrs)

   • Load sample dataset into Redshift from S3

   • Run analytical queries

Learning Outcomes:

✅ Understand Redshift architecture and cluster setup
✅ Load and query data efficiently
✅ Connect Redshift with AWS ecosystem (S3, Glue)

---

WEEK 2: SQL, Schema Design, and Data Modeling in Redshift

Duration: 10 Hours (5 Sessions × 2 Hrs)**

Topics:

1. Amazon Redshift SQL Basics (2 hrs)

   • Writing SQL for Redshift

   • SELECT, WHERE, GROUP BY, and JOINS

   • Aggregations, subqueries, and CTEs

2. Advanced SQL Functions (2 hrs)

   • Window and analytic functions

   • User-defined functions (UDFs) in Redshift

   • Working with JSON and semi-structured data

3. Schema Design and Data Modeling (2 hrs)

   • Star and Snowflake schema design

   • Distribution keys and sort keys

   • Choosing the right data types

4. Performance Optimization & Query Tuning (2 hrs)

   • Analyze and vacuum commands

   • Query plans and EXPLAIN

   • Managing workload and concurrency scaling

5. Mini Project (2 hrs)

   • Design a warehouse schema for an e-commerce dataset

   • Run optimized analytical queries

Learning Outcomes:

✅ Master Redshift SQL for analytics
✅ Design efficient schemas for large-scale data
✅ Optimize query and data performance

---

WEEK 3: ETL/ELT Pipelines and AWS Integrations

Duration: 10 Hours (5 Sessions × 2 Hrs)**

Topics:

1. ETL/ELT Concepts in Redshift (2 hrs)

   • ETL vs ELT in cloud data warehousing

   • Transformations using SQL and Redshift Spectrum

2. Integration with AWS Services (2 hrs)

   • Redshift Spectrum for external tables

   • Data ingestion with AWS Glue, Kinesis, and Data Pipeline

3. Automating Workflows (2 hrs)

   • Using AWS Lambda and Step Functions

   • Scheduling with Amazon Managed Airflow (MWAA)

4. Connecting Redshift with BI Tools (2 hrs)

   • Visualization using Amazon QuickSight, Tableau, and Power BI

   • Creating dashboards with live Redshift data

5. Mini Project (2 hrs)

   • Build a pipeline: S3 → Glue → Redshift → QuickSight dashboard

Learning Outcomes:

✅ Build end-to-end ETL pipelines
✅ Integrate Redshift with AWS Glue, Airflow, and BI tools
✅ Automate and visualize data workflows

---

WEEK 4: Advanced Administration, Security & Capstone Project

Duration: 12 Hours (6 Sessions × 2 Hrs)**

Topics:

1. Cluster Management & Scaling (2 hrs)

   • Elastic resize and concurrency scaling

   • Monitoring performance with CloudWatch

   • Managing workloads using WLM (Workload Management)

2. Security and Compliance (2 hrs)

   • Encryption (KMS, SSL)

   • IAM roles, VPC, and network security

   • Row-level and column-level access control

3. Cost Optimization and Best Practices (2 hrs)

   • Pricing models and cost estimation

   • Compression encoding and storage optimization

   • Redshift Spectrum cost control

4. Redshift ML and Advanced Analytics (2 hrs)

   • Overview of Redshift ML

   • Building and deploying models directly in Redshift

5. Capstone Project Development (2 hrs)

   • Build a real-world data warehouse for analytics

   • Integrate ETL, transformation, and reporting layers

6. Capstone Review & Presentation (2 hrs)

   • Project demo and feedback

   • Industry insights and Redshift best practices

Learning Outcomes:

✅ Administer and secure Redshift clusters
✅ Optimize cost and performance
✅ Use Redshift ML for predictive analytics
✅ Build a production-grade data warehouse project

---

🧩 CAPSTONE PROJECT EXAMPLE

Project Title: Retail Sales Data Warehouse on Amazon Redshift
Objective:
Design and build a data warehouse that integrates retail sales data from multiple sources (CSV, API, and streaming).
Perform transformations and build dashboards for sales insights.

Tech Stack:
AWS Redshift, S3, Glue, Lambda, Airflow (MWAA), QuickSight

Deliverables:

• Automated ETL pipeline

• Optimized warehouse schema

• Dashboard with business insights

FINAL COURSE OUTCOMES

By the end of this 4-week (40-hour) program, learners will be able to:

✅ Set up and manage Amazon Redshift clusters
✅ Load, query, and optimize large datasets
✅ Design star/snowflake schemas and implement ETL pipelines
✅ Integrate Redshift with Glue, Airflow, and BI tools
✅ Implement governance, monitoring, and cost optimization
✅ Deploy a production-ready Data Warehouse on AWS