Data Engineering on AWS Vol 1 - OLAP & Data Warehouse

Explore essential AWS data engineering services, focusing on EC2 and EBS, and learn how cloud, regions, and availability zones form the pay-as-you-go infrastructure behind scalable data workloads.

Explore ec2 components—ami, instance type, and ebs root and data volumes—and follow a hands-on to launch an Amazon Linux 2 instance, configure security group, key pair, and Elastic IP.

Explore elastic block store basics for EC2, including block storage, IOPS, and GP2/GP3 volumes. Learn about EBS snapshots, multi-attach, and S3-backed backups.

Hands-on with AWS EBS: create, attach, detach, and format volumes on EC2; resize, snapshot, and restore to new volumes; attach to multiple instances; create an AMI; review monitoring options.

Explore how AWS VPCs create isolated networks across availability zones, using internet gateways, subnets, route tables, security groups, and concepts like NAT gateways, VPC endpoints, and VPC peering.

Set up a vpc with an internet gateway, two public and two private subnets across azs, and public/private route tables; launch four ec2 instances and test ssh on public subnets.

Demonstrates using a bastion host to access a private-subnet EC2 instance from a public subnet by transferring the PEM key to the public instance and SSH-ing via private IP.

Create and configure security groups in the VPC console; define inbound and outbound rules, ports, and sources, and apply them to EC2 instances across public and private subnets.

Learn how NAT gateway enables EC2 in private subnets to access the internet for software installation, and how VPC gateway endpoints provide private access to S3 and DynamoDB.

Discover how VPC peering enables direct, AWS-internal communication between multiple VPCs across regions or within the same region. Connect EC2, RDS, and Redshift resources without internet access for data engineering.

Explore AWS IAM authentication and authorization, including users, policies, groups, roles, and ARN usage for EC2, VPC, S3, and Redshift, with a hands-on admin user setup.

Explore how IAM groups simplify permissions by attaching policies to groups rather than individuals, and create service roles for EC2, Redshift, and Glue to read from S3 using trust policies.

Learn SQL fundamentals: databases, schemas, tables, CRUD operations, and joins and views—along with analytics SQL concepts and ANSI standards on OLTP and data warehousing with PostgreSQL.

Launch an Amazon Linux 2 ec2 instance, install the psql client, and deploy an Aurora PostgreSQL database in a chosen region; configure ssh access and an iam s3 role.

Explore how databases group objects into databases and schemas, define tables and views with DDL, and manipulate data with DML, while understanding primary keys, foreign keys, and referential integrity.

Create customers, sellers, and orders tables in a Postgres Aurora cluster, load datasets from S3, and apply referential integrity and basic schema changes.

Learn to perform CRUD with SQL, including read, update, and delete, plus inserts and load commands; master selecting columns, filtering with where and like, and grouping for analytics.

Learn how to use select operators like count, and, or, not, in, distinct, and aggregate functions with group by, plus aliasing techniques for tables and columns.

Explore SQL math and case and coalesce functions, including price per item, original price with discount, absolute value, square root, and power, with null handling examples.

Explore date functions for data engineering, including extract for year, day, weekday, and epoch, plus current date and interval arithmetic to compute past dates.

Demonstrate string and data type transformations with concat, cast, and substring, and teach create table as and insert into select for invoice-focused reporting.

Learn how to perform update, delete, and truncate operations, apply conditional updates, and use functions like length, lower, upper, substring, cast, and coalesce to transform data.

Learn how the having clause filters aggregated results after group by when using sum(price) as card_wise_spend, clarifying why where cannot reference aggregates.

Explains how inner join, left join, right join, and full outer join link two tables on a common key to combine related data, with practical examples using orders and customers.

Explore union, intersect, and except to combine results across tables with matching data types and column counts, and create views to simplify complex queries and secure underlying data.

Store the results of complex SQL statements in materialized views to speed up analytics, data engineering, and BI reporting, and refresh them to stay up to date.

Explains how common table expressions, using the with clause, break down complex SQL into runtime temporary tables, compute lead time and discounted price per customer, and average results by state.

Learn window functions in SQL, using partition by, over, and order by to compute top orders, state sales, and top sellers with dense_rank and other aggregates.

Learn how the merge statement uses a source (staging) table to conditionally update, insert, or delete a target table based on matching conditions; explore practical staging-to-production workflows.

Explore Python for data engineering by installing Python, setting up PyCharm, creating virtual environments, and using key libraries like numpy, pandas, matplotlib, PySpark, and Jupyter.

Master Python basics, from interpreted vs compiled language to classes, objects, data types, and type conversions, then perform a PyCharm and CLI walkthrough.

Learn how Python operates as an interpreted or compiled language, using PyCharm or the command line, and how pre-compiling to .pyc in pycache speeds execution.

Explore why in Python everything is an object, compare class templates to objects, and see how data types, variables, and type checks reveal memory as objects.

Explore the string data type and perform operations such as creating variables, splitting text, validating numeric content, changing case, trimming spaces, and concatenating values.

Explore the number data type with integers and decimals, using zip code, price, and quantity examples to show initialization, type checks, and basic operations like power, div mod, and rounding.

Learn how lists function as unordered, mixed-type collections that can include numbers, strings, lists, or dictionaries, and practice indexing, for loops, and operations like append, insert, remove, and sort constraints.

Explore the tuple data type, which you cannot modify inside, learn how to initialize and access its values, and count occurrences and offsets of elements.

Explore set and dictionary data types in Python, learning how to create, update, and compare sets. Perform union, intersection, and difference, and manipulate dictionaries with keys, values, and type conversion.

Set up a Python interpreter and virtual environment in PyCharm, create a new project, and cover print, input, if statements and loops, plus function definition, return, and scope basics.

Learn to use the print function to display variables and strings in the console for debugging in PyCharm, and handle input, type conversion, concatenation, and formatting.

Learn how to implement conditional logic with if statements, elif clauses, and else blocks to take actions based on order price thresholds, such as sending text or making a call.

Explore for and while loops, showing how to iterate over lists, sets, dictionaries, and tuples, with break conditions to exit loops and manage infinite loops.

Explore how functions, the basis of methods in Python, encapsulate discount calculations, enable reusable code and APIs, and illustrate def definitions, parameters, scoping, and function calls.

Explore how function scope follows the local, global, and built-in (LGB) rule to resolve variables inside and outside a function, with examples showing how x is affected by scope.

Learn how the return keyword transfers values from a function, assign results to variables, and handle single or multiple returns as tuples.

Learn to pass values as arguments to functions, replacing hard coded inputs with dynamic amount and discount so the final discount and final price are correctly computed.

Explore how functions modify various Python types—integers, strings, lists, sets, dictionaries, and tuples—using pass, in-place edits, and the LEGB rule to show mutable versus immutable behavior.

Explore how positional arguments map to function parameters and how keyword arguments allow explicit mapping, including type handling and common errors from mismatched names.

Learn how Python handles function arguments using *args and keyword arguments, capturing inputs as tuples and dictionaries, and how to combine positional and keyword parameters.

Explore advanced Python concepts for data engineering, including classes and objects, methods and attributes, __init__, self, object instances, and how modules, packages, and imports shape memory and namespaces.

define a customers class with class variables org and location; initialize instance attributes via __init__ using self, including id; distinguish class versus instance variables under the lgb rule.

Implement two Python classes for onboarding and salary management: generate full name, age from date of birth, random customer ID and email, and compute salary hikes from years of experience.

Create a hike generator class to compute salary hikes. Use a lookup table for years of experience to determine hikes; include an order app with discounts.

This lecture demonstrates inheritance in a Python-like class structure, where a Volkswagen subclass inherits from Vehicles, gaining access to vehicle type, vehicle maintenance, and the vehicle manufacturer variable.

Explore how Python memory management handles objects, classes, and functions by illustrating memory areas like program memory, application heap, and stack, plus the role of constructor init and garbage collection.

Learn how modules and packages organize Python code. Use import, dot notation, and __init__.py to create reusable, distributable components.

Implement a modular bank data system by creating modules and packages to add customers, accounts, and loans, then query customer, account, and loan details by IDs.

Learn how Python automatically compiles modules to bytecode on import, stores __pycache__ files, and re-compiles when source time stamps change, with an option to precompile.

Learn how to use the __name__ variable to run code only when a module is executed directly, while preserving importability, and understand Python namespaces and import behavior.

Learn to handle Python errors gracefully with try/except, except as, and raise; distinguish Python errors from business logic with value errors, and leverage modules and namespaces.

Explore Python file handling in data engineering, covering open and with file operations, reading and writing CSV and JSON, and hands-on examples with read, read lines, and write.

Learn to read csv files with csv.reader, map rows to dictionaries with csv.dict_reader, and write with csv.writer; then use the json module to read and deserialize json data from files.

Explore Python multithreading by contrasting processes and threads, and learn to implement parallel work with the threading module to run code concurrently.

Learn Python multi-threading by reading multiple split files in parallel with the threading library, and aggregate per-payment method totals across five concurrent threads.

Learn practical debugging and profiling in Python for data engineering tasks, using print statements, IDE breakpoints, PyCharm, and CPU and memory profiling tools.

Understand how data flows from mobile, web, and IoT sources to OLTP databases and OLAP data warehouses, and review redshift, athena, hive, and data lakehouse concepts.

Explore data mart and data mesh concepts within the data engineering pipeline, showing how department-specific data warehouses and decentralized pipelines enable targeted analytics and reporting.

Explore data lake, data lakehouse, and data warehouse concepts, including storing structured, semi-structured, and unstructured data at any scale with open formats like iceberg, hoodie, and delta lake.

Explore how AWS S3 fits as a data lake, lakehouse, and distributed storage in the data engineering pipeline, enabling raw and processed data storage, analytics, and archival.

Explore the S3 storage hierarchy with buckets and objects, including bucket properties like versioning and encryption, and URI vs URL access used by data engineering tools.

Perform hands-on S3 basics: create buckets and folders, upload objects via the AWS console and AWS CLI from EC2, and manage access, versioning, and object keys.

Discover how s3 versioning preserves multiple versions of the same object to prevent accidental overwrite, enabling bucket-level control and management via cli commands like list and delete object version.

Explore data encryption on s3, covering data encryption at rest and in transit via tls with sse-s3, sse-kms, sse-c, and dual-layer options, including dek, cec, and bucket key.

Explore S3 object-level storage class options. Compare standard, standard IA, express one zone, and one zone IA, plus glacier variants such as instant retrieval, flexible retrieval, and lifecycle policies.

Break large files over 100 MB into parts and upload them in parallel to S3 using multipart upload; S3 assembles the object, and you can retry only failed parts.

Learn how to use S3 lifecycle policies and rules to automatically move objects between storage classes and expire or delete older versions, reducing storage costs.

Enable cross region replication to duplicate objects across buckets in different regions, supporting governance, disaster recovery, and global deployment.

Learn how the S3 mount point mounts an S3 bucket as a local file system on EC2, translating Unix commands to S3 API calls with caching for read-heavy workloads.

Explore how identity-based policies and bucket policies govern S3 access, using IAM for authentication and authorization, and apply actions such as list, get, put, and delete objects.

Learn to use S3 bucket policies as resource-based policies to grant a specific user list buckets, list objects, put object, and delete object.

Extend bucket policies to control S3 access from specific VPC endpoints, VPCs, or IPs within private subnets, enabling data pipelines on EMR and Glue clusters.

Explore configuring multiple S3 access points per bucket, attach per-point IAM policies, and synchronize bucket and access point policies to control access for users and services across VPCs.

Explore AWS Lambda, a serverless compute service with triggers. Learn how Object Lambda endpoints enable data transformation and masking on S3 objects via Athena.

Use pre-signed URLs to grant temporary, object-level access for external users without IAM, via console or CLI, with expirations up to 12 hours, for download or upload.

Learn how S3 scales for concurrent reads and writes, with 3500 writes and 5500 reads per second per prefix, and how multiple folders, same-region deployment, and transfer acceleration boost performance.

Explore S3 pricing in us-east-1, detailing tiered storage classes (Standard IA, One Zone IA, Glacier, Glacier Deep Archive) and costs for uploads, copy, encryption, and data transfer.

Position S3 as the central data storage in a pipeline, enabling Athena, Redshift Spectrum, EMR, Glue, and streaming tools like Kinesis and Kafka to process data.

Explore data modeling concepts for both OLTP and OLAP systems, including schemas, entities and attributes, primary and foreign keys, and dimensional modeling techniques for data warehouses.

Explore normalization and data integrity through practical 1NF, 2NF, and 3NF techniques. Learn to decompose raw data into related tables with primary keys and foreign keys.

explore data relationships in depth: one-to-one, one-to-many, many-to-one, and many-to-many, with practical examples from orders, invoices, customers, and items, and introduce denormalization and er diagrams.

Explore dimensional modeling concepts, including facts and dimensions, star and snowflake schemas, and the grain of fact tables, to design OLAP data warehouses.

Identify grains for two OLAP use cases: the vehicle sale details per customer as the fact granularity, and the record of each employee on every opportunity for month-wise workforce analysis.

Learn dimensional modeling essentials, including date dimension and greens, and design a star schema with customers, orders, order items, products, and sellers for retail data warehouses.

Examine transactional, periodic snapshot, and accumulating snapshot fact tables, with their grains. Explore slowly changing dimensions, confirmed dimensions, and additive, semi-additive, and non-additive facts across data marts.

Explore Redshift infrastructure, including Ra3 and Dc2 node types, Redshift managed storage with local SSD and S3, and cluster topology with VPCs, subnets, and IAM roles.

Create a Redshift cluster in a VPC by configuring subnets, security groups, a subnet group, an IAM role, and review free tier considerations.

Understand redshift architecture with leader and compute nodes, including node slices and MPP parallelism on Ra3 and Dc2, plus columnar storage and zone maps for selective IO.

Learn how to resize Redshift clusters with elastic and classic resize, including in-place vs cross-type upgrades, node scaling, data slice redistribution, and snapshot-based migrations.

Resize an AWS Redshift cluster from a dc2 two-node setup to four, covering elastic and classic options, snapshot prerequisites, and storage growth from 320 GB to 640 GB.

Rename an AWS Redshift cluster, monitor status changes from modifying to unavailable to available. Pause the cluster by enabling automated snapshot to avoid compute charges.

Resume paused clusters to enable snapshots, create and differentiate manual and automated snapshots with retention settings, and delete snapshots while configuring cross-region snapshot copy in Redshift.

Conclude the Redshift infrastructure by detailing clusters with leader and compute nodes, ra3 and dc2 types, Redshift managed storage on ra3, node slices, columnar storage, and zone maps for queries.