SQL for Data Engineering
Development Academy of the Philippines
Enrollment is Closed
Course Overview
This course deals with manipulating and transforming data with SQL with the objective of making data ready for analysis and reporting. Through videos and hands-on exercises, Participants will learn how to cleanse data and how to deal with missing data using SQL. Participants will also learn the differences between an operational and an analytical database schema, and how to move data from the former to the latter.
What You Will Learn
Upon completion of this course, the learners are expected to:
- apply different techniques of cleansing and manipulating data with SQL;
- move data from one schema model to another using SQL. Specifically, to transform data from an operational database to a reporting and analytical database; and
- perform batch process data movement within SQL.
Course Requirements
You will need a computer or laptop and the following items:
- PostgreSQL installation
- pgAdmin 4 installation
- basic SQL scripting skills
Note to SPARTA scholars: Upon enrollment, you will have 6 months to finish a SPARTA course. Failure to complete the course in 6 months and/or inactivity for 3 months will result in course access revocation.
Course Instructor
Aldrie Chester Lim
Subject Matter Expert
Course Content
Week 1: Introduction to Extract, Transform, and Load (ETL)
12 Videos | 2 Activities
12 Videos
- Welcome to the course!
- Where We Want to Go
- The Gap and the Bridge
- ETL: An Overview
- Extraction Process Considerations
- Types of Extraction
- Transformation Process Considerations
- Data Cleansing Problems
- Data Transformation
- Loading Types
- Load Verification
- ETL Tools
2 Activities
- Recall Activity
- Exit Assessment
Week 2: ELT as an Alternate Approach to ETL
3 Videos | 2 Activities
3 Videos
- ETL Recap
- What is ELT?
- Comparison Between the ETL and ELT
2 Activities
- Recall Activity
- Exit Assessment
Week 3: Bulk Loading to Temporary Staging Tables
5 Videos | 2 Activities
5 Videos
- Creating a Temporary Table
- Extracting Data from CSV File
- Foreign Data Wrappers in PostgreSQL
- Setting Up the Foreign Server
- Setting Up the Destination Server
2 Activities
- Recall Activity
- Exit Assessment
Week 4: Cleaning Data in SQL
17 Videos | 2 Activities
17 Videos
- COALESCE()
- Data Type Conversion
- Removing Unwanted Spaces
- Removing Unwanted Characters
- Addressing Capitalization Issues
- INSERT Statement
- UPDATE
- UPSERT Using INSERT ON CONFLICT Statement
- DELETE Statement
- Turning Strings to Dates
- Deconstructing Dates to Multiple Columns
- How to Find Duplicates
- How to Delete Duplicates
- How to Combine Duplicate Rows in One Single Row
- How to Delete Unwanted Duplicates
- Using IF Function
- Using CASE Conditional Statement
2 Activities
- Recall Activity
- Exit Assessment
Week 5: Performing Feature Engineering Using SQL
13 Videos | 2 Activities
13 Videos
- What are its features and why are they important?
- Variables
- What Is Categorical Variable Encoding and Why It’s Necessary
- Traditional Techniques: One Hot Encoding
- Traditional Techniques: Ordinal or Label Encoding
- Traditional Techniques: Count or Frequency Encoding
- Monotonic Relationships: Ordered Label Encoding
- Monotonic Relationships: Mean Encoding
- What is feature scaling?
- Mean Normalization
- Min-Max Scaling
- Standardization
- Key Takeaways
2 Activities
- Recall Activity
- Capstone