Data Engineering, Serverless ETL & BI on Amazon Cloud

Explore building serverless ETL pipelines on AWS using the Glue Python Shell and Redshift, with MySQL as the transactional database and S3 data lake, orchestrated by Step Functions.

Explore aws redshift, a fully managed olap data warehouse using columnar storage with scalable pay-as-you-go serverless options, and seamless aws native integrations like quicksight and glue, plus jdbc connectors.

Compare Redshift, BigQuery, and Snowflake architectures and pricing—from Redshift's provisioned model to BigQuery pay-as-you-go and Snowflake compute-storage separation.

Discover AWS Glue, a fully managed, serverless data integration service that moves data between stores, performs ETL with Python, Shell, or Spark, and uses crawlers and a data catalog.

Create a MySQL database on AWS via RDS using the free tier, selecting MySQL 8.0.33, naming it e-commerce-db, enabling public access on a default VPC with a t3.micro.

Connect to a MySQL RDS instance, configure inbound rules, install the MySQL client, create ecommerce_db with tables, and ingest data from CSV files into items, orders, order reviews, and products.

Deploy an AWS Glue Python shell ETL pipeline to extract data from a MySQL database and write incremental updates to S3, using date filters and table-specific queries.

Use AWS Secrets Manager to store and retrieve MySQL credentials for a Glue job, then run a dynamic, date-filtered extraction with table name and output results to S3.

Provision a single-node redshift cluster and enable public access. Connect from a local sql client using the endpoint and port 5439 to test the connection with DBeaver.

Learn to ingest data into a Redshift cluster using copy commands, creating a transactional layer schema with orders, order items, reviews, and products from CSV and Parquet files in S3.

Deploy a python shell job in aws glue to ingest s3 data into redshift with redshift connector, using table, bucket, and date parameters and copy commands for csv or parquet.

Explore AWS Step Functions as a serverless orchestration service that coordinates distributed applications through workflows, with native integration to Lambda, Glue, and DynamoDB, plus built-in error handling, retries, and timeouts.

Execute a step function workflow that runs two glue jobs—extract raw data from MySQL to parquet in S3 and ingest it into Redshift—demonstrating a dependent end-to-end serverless ETL pipeline.

Master incremental data loads into Redshift with a staging table, joining on the primary key to delete duplicates and then insert into the main table, using AWS Glue.

Enrich and centralize data by stitching transactional data with third-party user behavior data using AWS Glue crawlers, Athena, and PySpark on AWS. Centralize the data in Redshift for analytics.

Learn to configure an AWS glue crawler on an S3 csv file to populate the data catalog and enable Athena querying, and compare csv versus parquet for analytics.

Configure a Glue crawler and query a CSV-based table in Athena, adjusting headers and skip header rows to reveal data scans and CSV vs Parquet differences.

Develop a PySpark-based aws glue workflow locally: read csv, convert event timestamp, create date and month partitions, and save as snappy parquet, then deploy to aws glue.

Port the local pyspark etl to AWS Glue by adding the AWS Glue context and dynamic file arguments; read from S3 and write Parquet output, then run the job.

Explore how to crawl parquet data with AWS Glue PySpark, compare with CSV in Athena, and optimize queries using partitioning and snappy compression for columnar Parquet files.

Automate PySpark ETL by using a Lambda to trigger a Glue job on new S3 files, passing the file name as a parameter. Configure max concurrency to run multiple files.

Rerun the Glue crawler to populate year and month partitions in the parquet_output data catalog, enabling centralized analytics with Athena and Redshift Spectrum across external and transactional data.

Discover how Redshift Spectrum enables querying data in S3 without loading or transforming, and how it can join datasets from Redshift and other sources for data enrichment.

Create an external schema with redshift spectrum linked to the data catalog to query S3 data as if it resides in redshift, then create an external table from csv files.

Learn to perform cross database joins in Redshift Spectrum by joining parquet_output with an RDBMS table to fetch the English category names and group by year.

Explore Amazon QuickSight, a BI and reporting tool, through an introduction to analysis, dashboards, and datasets, including Redshift, Athena, and S3 integrations.

Learn to connect Redshift to Quicksight via a VPC security group, import the orders table as a spice dataset, and publish an analysis dashboard with view and admin access controls.

Create and query a Redshift view from a custom SQL, import it into QuickSight, join category translations, and build filtered dashboards while managing memory.

Analyze how Redshift sort keys, especially compound sort keys, organize data by column order to speed up queries. Learn how the first column affects data block access and overall performance.

Explore how Redshift interleaved sort keys assign equal weight to multiple columns, improving query performance on restrictive predicates, with examples on customer_id and country_id and guidance on choosing sort keys.

Learn how vacuum reclaims space from deletes and updates in Redshift, why it re-sorts data, and the main vacuum types: full, delete-only, and reindex for planning.

Learn to choose Redshift sort keys—compound vs interleave—recognize when to apply them, understand vacuuming impact, and plan experiments with cloned production tables for safe optimization.

Master how distribution keys shape data placement and query performance in Redshift by exploring distribution plans, patterns, and the three distribution types—even, key, and all—across leader and compute nodes.

Create and apply a new parameter group for the Redshift cluster, add the schema to its search path, and reboot to enable sort keys on the orders table.

Create a production table clone to safely test sort and distribution keys, then alter tables for compound sort keys and use vacuuming to reclaim space without disrupting ETL.

Lab demonstrates reading data from S3 with AWS Glue PySpark, applying transformations, converting to dynamic frames, and inserting into Redshift tables via a Glue context using resolve choice.

Learn to read data from MySQL RDS with AWS Glue and PySpark, using secrets, a Glue connection, and a MySQL jar, with year-month partitioned output.

Explore Docker, a software development platform that packages code into portable containers. Learn how containers run the same anywhere, isolate resources, and scale with registry-based image deployment.

Install the Docker desktop engine, sign up for a free account, and verify Docker desktop runs as a daemon to enable Docker commands.

Create a docker image from a dockerfile starting Ubuntu 20.04 and Python 3, then build and run it with docker build and docker run, and push to a cloud registry.

Create and run a Docker image to containerize a Python data transformation, using a base image, copying code, and mounting a volume to save output locally.

Build a docker image, push to AWS ECR, and deploy a lambda function to run the container; read a csv, apply pandas transformations, and write results back.

Attach IAM permissions to the lambda role to access S3 data, deploy and test the function. Verify results in the bucket and learn about docker images on WS Cloud.

Learn how to design a real-time, serverless e-commerce transaction processing solution on AWS using Lambda, DynamoDB, and API Gateway, with scalable, fault-tolerant, and cost-effective ingestion and retrieval via API endpoints.

Deploy and test AWS Lambda functions that write to and read from DynamoDB, using an IAM role and API Gateway, with curl validation.

Set up an api gateway rest api, create dynamo resources and post methods, enable lambda proxy integration, and deploy a production stage for transactional writes and reads to DynamoDB.

Learn to build serverless data processing workflows with AWS Step Functions, Lambda, and Glue that extract from a MySQL RDS, perform ETL with PySpark in Glue, and write to S3.

Run a data processing workflow where a step function triggers a Glue job, and outputs are written to an S3 bucket's output folder, with subsequent runs triggered by step functions.