Data Engineering Bootcamp: Master Python, SQL & Spar

Mastering Data Engineering with Databricks: A Comprehensive Bootcamp

Join our comprehensive course to master Data Engineering with Databricks. Learn Python, SQL, and Spark to design, build, and manage data pipelines efficiently. Perfect for aspiring data engineers looking to leverage Databricks in real-world scenarios

Enroll Now

Course curriculum

1. Course Outline
1. Overview of SQL Revision for Data Engineering
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. Tips for Technical Questions
3. How much do you rate your self in SQL?
4. What all you have done using SQL?
5. What is the difference between Truncate and Delete?
6. What are the different types of constraints you have used?
7. What is the difference between Primary Key and Unique Constraint?
8. What is the difference between Primary Key and Foreign Key Constraint?
9. Can a table have more than one Unique Constraint?
10. What happens to the data in the child table's foreign key column when data in the parent table is deleted?
11. Can Foreign Key Constraint Columns have null values?
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?
18. Have you worked on PL/SQL or T-SQL?
19. Can you explain couple of performance tuning scenarios you have worked on?
20. How do you typically troubleshoot the errors you encounter while performing database operations such as running queries, creating tables, etc?
21. What is a COMMIT or ROLLBACK?
22. What all DML Operations you have performed?
23. What is merge or upsert?
24. How do you load the data from files into tables?
1. Overview of Python Revision for Data Engineering
2. 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
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. 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. Modularize File Format Converter for Dataset
10. Wrapper to Process all Data Sets
11. Setup Project for File Format Converter using Python
12. Install Dependencies for the Python Project using pip
13. Add Core Logic to Python Application
14. Overview of Run-time Arguments and Environment Variables
15. Using Run Time Arguments in Python Applications
16. Overview of Environment Variables
17. Setting Environment Variables on Windows or Mac or Linux
18. Use Environment Variables in Python Applications
19. Use Environment Variables in File Format Converter
20. Pass JSON Array as argument to Python Applications
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. How much do you rate your self in Python?
3. Can you elaborate your experience in Python?
4. What all Python Libraries or modules you have used?
5. Which library do you use for the data processing?
6. If you have to read the data from REST API, which library do you use?
7. Can you explain life cycle of data processing using Python where CSV data have to processed and stored into the Database?
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. Can you elaborate your Pandas Experience?
13. How do you filter the data in Pandas?
14. How do you read the data from CSV file using Pandas? How will you apply schema if the file does not contain header?
15. What is Python Virtual Environment and what are the advantages of using Python Virtual Environment?
16. What is pip? How do you organize and install the required dependent libraries to the current project?
17. Do you have experience in database programming using Python?
18. How to read data from Database table using Pandas?
19. Data is exposed using REST API, how do you use Python to get the data from REST API?
20. How do you check if file exists in a given path (Hint: using os module)?
21. How do you deal with files in Python (using os module)?
22. How can you check the data type of a Python Variable?
1. Live Session - SQL and Python Checkpoint
1. Cloud and Data Warehousing Essentials using Databricks
1. Introduction to Databricks Lab
2. Accessing the Databricks Environment
3. Navigating the Databricks Workspace
4. Databricks Notebooks and Collaborative Features - Part 1
5. Databricks Notebooks and Collaborative Features - Part 2
6. Running Sample Data Engineering Tasks - Part 1
7. Running Sample Data Engineering Tasks - Part 2
8. Best Practices and Tips
9. Q&A and Wrap-Up
10. Additional Resources
1. Overview of Data Analysis or Data Processing using Spark SQL
1. Process Data in DBFS using Databricks Spark SQL
2. Getting Started with Spark SQL Example using Databricks
3. Create Temporary Views using Spark SQL
4. Exercise to create temporary views using Spark SQL
5. Spark SQL Query to compute Daily Product Revenue
6. Save Query Result to DBFS using Spark SQL
1. Ranking using Spark SQL Windowing Functions
2. Create Temporary View for ranking using Spark SQL Windowing Functions
3. Compute Global Rank using Spark SQL Windowing Functions
4. Compute Ranks Per Key using Spark SQL Windowing Functions
5. Difference Between rank and dense_rank
6. Filter on Ranks using Spark SQL Windowing Functions
1. Spark SQL - Frequently Asked Interview Questions
2. Can you elaborate about your experience using Spark SQL?
3. What is Spark Metastore? How it is used?
4. What is the difference between Managed Table and External Table?
5. Providers or File Formats used for Spark Metastore Tables
6. What is Delta Format and What are the Advantages of using Delta Format?
7. Can you create index on Spark Metastore Tables? If not, Why?
8. How do you get the data from JSON or CSV Formatted files into Parquet or Delta Formatted Tables?
9. Columnar File Formats
10. What all different types of joins you have used?
11. What is the difference between inner join and outer join?
1. Overview of Data Processing using PySpark Data Frame APIs
1. Overview of Pyspark Examples on Databricks
2. Process Schema Details in JSON using Pyspark
3. Create Dataframe with Schema from JSON File using Pyspark
4. Transform Data using Spark APIs
5. Get Schema Details for all Data Sets using Pyspark
6. Convert CSV to Parquet with Schema using Pyspark
1. Overview of Databricks Jobs and Workflows
1. Overview of Databricks Workflows
2. Pass Arguments to Databricks Python Notebooks
3. Pass Arguments to Databricks SQL Notebooks
4. Create and Run First Databricks Job
5. Run Databricks Jobs and Tasks with Parameters
6. Create and Run Orchestrated Pipeline using Databricks Job
7. Import ELT Data Pipeline Applications into Databricks Environment
8. Spark SQL Application to Cleanup Database and Datasets
9. Review File Format Converter Pyspark Code
10. Review Databricks SQL Notebooks for Tables and Final Results
11. Validate Applications for ELT Pipeline using Databricks
12. Build ELT Pipeline using Databricks Job in Workflows
13. Run and Review Execution details of ELT Data Pipeline using Databricks Job

What's Included

329 lessons
22.5 hours of video content
Capstone Projects
Expert Support Sessions

Mastering Data Engineering with Databricks: A Comprehensive Bootcamp

Course curriculum

Welcome to the course!

Module 1 - SQL Revision for Data Engineering

Getting Started with SQL Revision 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

Module 2 - Python Revision for Data Engineering

Getting Started with Python

Python Collections for Data Engineering

Data Processing using Pandas Data frame APIs

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

Live Session - SQL and Python Checkpoint

Module 3 - Getting Started with the Databricks Lab Environment

Getting Started with the Databricks Lab Environment

Module 4 - Data Analysis or Data Processing using Spark SQL

Basic Transformations using Spark SQL

Ranking using Spark SQL Windowing Functions

Spark SQL - Interview Questions

Module 5 - Data Processing using PySpark Data Frame APIs

Getting Started with PySpark Data Frame APIs

Module 6 - Databricks Jobs and Workflows

ELT Data Pipelines using Databricks

What's Included