SQL and Python Essentials for Data Engineers

Enrol Now

Next Batch Starts On March 16th, 2023

00 Days
00 Hours
00 Minutes
00 Seconds

Course curriculum

1. Course Introduction
2. Course Overview
3. Before we begin...
1. Introduction: Overview of SQL Essentials for Data Engineering
2. Week 1 and 2 - Learning Outcomes of SQL Revision for Data Engineering
3. Key Activities & Technical Support
1. Introduction to SQL Revision for Data Engineering
2. Overview of Application Architecture and RDBMS
3. Overview of Database Technologies and relevance of SQL
4. Overview of Purpose Built Databases
5. Overview of Data Warehouse and Data Lake
6. Usage of RDBMS and Data Warehouse technologies
7. Differences and Similarities between RDBMS and Data Warehouse Technologies
1. Introduction to Setting up Tools for Data Engineering Essentials
2. Setup Git on Windows for Code Versioning
3. Setup VS Code on Windows
4. Setup Python 3.9 on Windows
5. Configure Environment Variable PATH for Python on Windows
6. Overview of learning Python using Python CLI
7. Integrate VSCode with Python on Windows
8. Install Postgres 14 on Windows 11
9. Getting Started with pgAdmin on Windows
10. Getting Started with pgAdmin on Mac
11. Conclusion of Setting up Tools for Data Engineering Essentials
1. Overview of Postgres Database Server and pgAdmin
2. Overview of Database Connection Details
3. Overview of Connecting to External Databases using pgAdmin
4. Create Application Database and User in Postgres Database Server
5. Clone Data Sets from Git Repository for Database Scripts
6. Register Server in pgAdmin using Application Database and User
7. Setup Application Tables and Data in Postgres Database
8. Overview of pgAdmin to write SQL Queries
1. Review Data Model Diagram
2. Define Problem Statement for SQL Queries
3. Filtering Data using SQL Queries
4. Total Aggregations using SQL Queries
5. Group By Aggregations using SQL Queries
6. Order of Execution of SQL Queries
7. Rules and Restrictions to Group and Filter Data in SQL queries
8. Filter Data based on Aggregated Results using Group By and Having
9. Inner Joins using SQL Queries
10. Outer Joins using SQL Queries
11. Filter and Aggregate on Join Results using SQL
12. Overview of Database Views
13. Overview of Common Table Expressions or CTEs
14. Outer Join with Additional Conditions in SQL Queries
15. Explanation about Fix of SQL Queries with Filtering on Outer Join Results
1. Introduction to Cumulative Aggregations and Ranking in SQL Queries
2. Overview of CTAS to create tables based on Query Results
3. Create Tables for Cumulative Aggregations and Ranking
4. Overview of OVER and PARTITION BY Clause in SQL Queries
5. Compute Total Aggregation using OVER and PARTITION BY in SQL Queries
6. Overview of Ranking in SQL
7. Compute Global Ranks using SQL
8. Compute Ranks based on key using SQL
9. Rules and Restrictions to Filter Data based on Ranks in SQL
10. Filtering based on Global Ranks using Nested Queries and CTEs in SQL
11. Filtering based on Ranks per Partition using Nested Queries and CTEs in SQL
12. Create Students table with Data for ranking using SQL
13. Difference between rank and dense rank using SQL
1. Introduction to SQL Troubleshooting and Debugging Guide
2. Overview of Database Connectivity Issues
3. Validate and Setup Telnet on Mac or PC
4. Validate Connectivity to Database Server using telnet
5. Troubleshoot Database Connectivity Issue with Correct Host Details
6. Current Databases and Users in Postgres Database Server
7. Troubleshoot Database Credentials and Permissions Issues
8. Overview of Compilation of SQL Queries
9. Troubleshooting Syntax Errors in SQL Queries
10. Troubleshooting Semantec Errors in SQL Queries
11. Overview of Bugs in SQL Queries
12. Development Best Practices with tips to troubleshoot SQL bugs
13. Develop Initial Solution based on the requirement
14. Identify and Troubleshoot Bugs in SQL Queries
15. Develop Solution using Development Best Practices
1. Introduction to Performance Tuning of SQL Queries
2. Overview of SQL Compilation Process and Explain Plans
3. Generate Explain Plans for SQL Queries
4. Review Tables used for Performance Tuning of SQL Queries
5. Review Data Storage Internals for Tables and Indexes
6. Review key terms used in Explain Plans for SQL Queries
7. Interpret Explain Plans for Basic SQL Queries
8. Review the Common Application Scenarios for Performance Tuning
9. Write SQL Queries for Customer Orders
10. Performance Testing of SQL Queries using Stored Procedure
11. Add Required Indexes to tune performance of SQL Queries
12. Guidelines on adding Indexes on Tables for SQL Queries
13. Interpreting the explain plan for SQL Queries using Indexes
14. Conclusion of Performance Tuning of SQL Queries
1. Simple Exercises for Filtering and Aggregations
2. Exercises on Joins and Aggregations using SQL
1. Solutions for Filtering and Aggregations
2. Solutions for Filtering and Aggregations
3. Validate Data and Review Data Model Diagram
4. Solution for Exercise 1 to get Customer Order Count
5. Solution for Exercise 2 to get Dormant Customers using Outer Join
6. Solution for Exercise 3 to get Revenue Per Customer using Outer Join
7. Solution for Exercise 4 to get Revenue Per Category
8. Solution for Exercise 5 to get Product Count Per Department
1. SQL - Frequently Asked Interview Questions
2. SQL: Feedback survey
3. Tips for Technical Questions
4. How much do you rate your self in SQL?
5. What all you have done using SQL?
6. What is the difference between Truncate and Delete?
7. What are the different types of constraints you have used?
8. What is the difference between Primary Key and Unique Constraint?
9. What is the difference between Primary Key and Foreign Key Constraint?
10. Can a table have more than one Unique Constraint?
11. What happens to the data in the child table's foreign key column when data in the parent table is deleted?
12. What all different types of joins you have used?
13. What is the difference between inner join and outer join?
14. What is a full outer join?
15. What is the difference between WHERE and HAVING?
16. What is a view and how is it different from a table?
17. What is CTAS and how it can be used to create a table with structure but no data?
1. Introduction: Overview of Python Essential for Data Engineering
2. Week 3 and 4 - Learning Outcomes of Python Essentials for Data Engineers
3. Key Activities & Technical Support
4. Setup Material - Python Essentials for Data Engineering
1. Setup Visual Studio Workspace for Python Application Development
2. Setup Notebook Environment in VS Code Workspace
3. Overview of VS Code Notebook Environment
4. Overview of Cells in VS Code Notebook
5. Defining Functions in VS Code Notebooks
6. Run the Code in VS Code Notebook Cell by Line
7. Constants and Variables in Python
8. Overview of Python Data Types
9. Getting help on Python Variables and Functions
10. Pre-Defined String Manipulation Functions
11. Overview of Python Lists
12. Loops and Conditions in Python
13. User Defined Functions in Python
14. Quiz - Basic Programming Constructs
1. Overview of File IO using Python
2. Read Data from CSV File into Python List
3. Overview of Python Collections
4. Getting Started with Processing Python Lists
5. Overview of Lambda Functions in Python
6. Usage of Lambda Functions
7. Filter Data in Python Lists using filter and lambda
8. Get unique values from list using map and set
9. Sort Python lists using key
10. Overview of JSON Strings and Files
11. Read JSON Strings to Python dicts or lists
12. Read JSON Schemas from file to Python dicts
13. Overview of Processing JSON Data using Python
14. Extract Details from Complex JSON Arrays using Python
15. Sort Data in JSON Arrays using Python
16. Create Function to get Column Details from Schemas JSON File
1. Overview of Pandas for Data Processing
2. Overview of Reading CSV Data using Pandas
3. Read Data from CSV Files to Pandas Dataframes
4. Filter Data in Pandas Dataframe using query
5. Get Count by Status using Pandas Dataframe APIs
6. Get count by Month and Status using Pandas Dataframe APIs
7. Create Dataframes using dynamic column list on CSV Data
8. Performing Inner Join between Pandas Dataframes
9. Perform Aggregations on Join results
10. Sort Data in Pandas Dataframes
11. Overview of Writing Pandas Dataframes to Files
12. Write Pandas Dataframes to JSON Files
1. Introduction: Python Capstone Projects
2. Week 5 and 6 - Learning Outcomes of Python Capstone Projects
3. Key Activities & Technical Support
1. Project 1 Handout - File Format Converter
2. Get File Names to be processed using glob
3. Get Column Names using Schemas File
4. Get Data Set Names from File Names or Paths using regular expressions
5. Read CSV Data into Pandas Dataframe with Schema Dynamically
6. Generate File Paths for Target JSON Files Dynamically
7. Recap of Writing Pandas Dataframe to JSON File
8. Write Pandas Dataframe to JSON Files
9. Wrapper to Process all Data Sets
10. Setup Project for File Format Converter using Python
11. Install Dependencies for the Python Project using pip
12. Add Core Logic to Python Application
13. Overview of Run-time Arguments and Environment Variables
14. Using Run Time Arguments in Python Applications
15. Overview of Environment Variables
16. Setting Environment Variables on Windows or Mac or Linux
17. Use Environment Variables in Python Applications
18. Use Environment Variables in File Format Converter
19. Pass JSON Array as argument to Python Applications
20. Modularize File Format Converter for Dataset
21. Pass Data Sets as Run Time Arguments to File Format Converter
22. Exception Handling in Python Applications
23. Raising Exceptions in Python Applications
24. Exception Handling in File Format Converter Application
1. Project 2 Handout - Files To Database Loader
2. Install Python Dependencies for Pandas and Database Integration
3. Run Queries from Notebook using SQL Magic
4. Validate Pandas and SQL Integration
5. Write CSV Data from File to Database Table
6. Write CSV Data from Files to Database Tables in Chunks
7. Overview of Deploying File to DB Loader Project
1. Project 3 Handout - Rest Payload to the DB Loader Essentials
2. Processing JSON Data - Introduction
3. Overview of JSON
4. JSON Data Types
5. Create JSON String
6. Process JSON String
7. Single JSON Document in Files
8. Multiple JSON Documents in files
9. Process JSON using Pandas
10. Different JSON Formats supported by Pandas
11. Common Use Cases for JSON
12. Write to JSON files using json module
13. Write to JSON files using pandas
14. Overview of REST APIs
15. Using curl command
16. Overview of Postman
17. Getting Started with requests
18. Convert REST Payload to Python Objects
19. Process REST Payload using Collection Operations
20. Process REST Payload using Pandas
1. Introduction to Troubleshooting and Debugging Python issues
2. Guidelines for Troubleshooting and Debugging Python related Issues
3. Overview of Database Connectivity using Python Applications
4. Overview of Database Connectivity using Python
5. Troubleshoot Network Connectivity to the Database Server using telnet
6. Troubleshoot Module Related issues for Database Connectivity using Python
7. Troubleshoot Credentials Related issues for Database Connectivity using Python
8. Overview of Python process to run Python Applications
9. Troubleshooting Compilation Errors in Python
10. Troubleshooting Run Time Errors in Python
11. Overview of Software Development Life Cycle
12. Overview of Unit Testing or Validation of Applications
13. Overview of Debugging VS Code Notebooks using Debug Feature
14. Debug VS Code Notebooks using Debug Feature
15. Getting Started with Debugging of Python Programs using VS Code
16. Recap of running File Format Converter application
17. Debug Python Application using VS Code with breakpoints
18. Managing Breakpoints for Debugging in VS Code
19. Conclusion to Troubleshooting and Debugging Python Issues
1. Introduction to Performance of Python Applications
2. Setup Database Loader Python Application
3. Ensure Postgres Database is setup for file to db loader Python Application
4. Cleanup the tables to run file to db loader application
5. Run and Validate File to DB Loader Application
6. Fix the error message in file to db loader application
7. Overview of Execution of file to db loader application
8. Performance Tuning using Chunksize in Pandas
9. Review Pandas Data Frame API to load data into the target table
10. Overview of multi or batch insert into Database Tables
11. Develop application for multiprocessing
12. Getting Started with Multiprocessing using Python
13. Invoking User Defined Functions using multiprocessing in Python
14. Refactor File to Database Loader Application for Multiprocessing
15. Add Parallel Processing to file to db loader Python Application
16. Validate File to DB Loader Application with Multiprocessing
17. Understanding the concept of Multiprocessing in Python
18. Performance Tuning Scenarios of Python Applications
1. Python - Frequently Asked Interview Questions
2. Python: Feedback survey
3. How much do you rate your self in Python?
4. Can you elaborate your experience in Python?
5. What all Python Libraries or modules you have used?
6. Which library do you use for the data processing?
7. If you have to read the data from REST API, which library do you use?
8. What are the different Python collections or Data Structures?
9. What is the difference between list, set, dict and tuple?
10. How do you sort the data in a Python list? What is the purpose of keyword argument key?
11. What is the difference between sort and sorted?
12. What is Python Virtual Environment and what are the advantages of using Python Virtual Environment?
13. What is pip? How do you organize and install the required dependent libraries to the current project?
14. How do you check if file exists in a given path (Hint: using os module)?
15. How can you check the data type of a Python Variable?
1. Before you go...
2. Congratulations! What's Next?

About this course

270 lessons
19.5 hours of video content

What's included

Capstone Projects

Get hands-on experience with real-time projects
Interview Preparation Kit

Be prepared with frequently asked interview questions, mock interviews, and profile-building sessions
Career Guidance

Don't know what's next? We'll help you with defined learning paths after the sql and python essentials course

SQL and Python Essentials for Data Engineers

Next Batch Starts On March 16th, 2023

Course curriculum

Welcome to the Course!

Week 1 and 2: SQL Essentials for Data Engineers

Getting Started with SQL Essentials for Data Engineering

Setup Tools for Data Engineering Essentials

Setup Application Tables and Data in Postgres Database

Writing Basic SQL Queries

Cumulative Aggregations and Ranking in SQL Queries

SQL Troubleshooting and Debugging Guide

Performance Tuning of SQL Queries

Exercises for Basic SQL Queries

Solutions for Basic SQL Queries

SQL - Interview Questions

Week 3 and 4: Python Essentials for Data Engineers

Getting Started with Python

Python Collections for Data Engineering

Data Processing using Pandas Data frame APIs

Week 5 and 6: Python Capstone Projects

Project 1 - File Format Converter using Python

Project 2 - Files to Database Loader

Project 3 - Rest Payload to the DB Loader Essentials

Troubleshooting and Debugging Python Issues

Performance Tuning of Python Applications

Python - Interview Questions

Finishing Up

About this course

What's included

Capstone Projects

Interview Preparation Kit

Career Guidance