Data Engineering Essentials using SQL, Python, and PySpark from Udemy

What's inside

Learning objectives

Setup environment to learn sql and python essentials for data engineering
Database essentials for data engineering using postgres such as creating tables, indexes, running sql queries, using important pre-defined functions, etc.
Data engineering programming essentials using python such as basic programming constructs, collections, pandas, database programming, etc.
Data engineering using spark dataframe apis (pyspark) using databricks. learn all important spark data frame apis such as select, filter, groupby, orderby, etc.
Data engineering using spark sql (pyspark and spark sql). learn how to write high quality spark sql queries using select, where, group by, order by, etc.

Relevance of spark metastore and integration of dataframes and spark sql
Ability to build data engineering pipelines using spark leveraging python as programming language
Use of different file formats such as parquet, json, csv etc in building data engineering pipelines
Setup hadoop and spark cluster on gcp using dataproc
Understanding complete spark application development life cycle to build spark applications using pyspark. review the applications using spark ui.

Setup environment to learn sql and python essentials for data engineering
Database essentials for data engineering using postgres such as creating tables, indexes, running sql queries, using important pre-defined functions, etc.
Data engineering programming essentials using python such as basic programming constructs, collections, pandas, database programming, etc.
Data engineering using spark dataframe apis (pyspark) using databricks. learn all important spark data frame apis such as select, filter, groupby, orderby, etc.
Data engineering using spark sql (pyspark and spark sql). learn how to write high quality spark sql queries using select, where, group by, order by, etc.
Relevance of spark metastore and integration of dataframes and spark sql
Ability to build data engineering pipelines using spark leveraging python as programming language
Use of different file formats such as parquet, json, csv etc in building data engineering pipelines
Setup hadoop and spark cluster on gcp using dataproc
Understanding complete spark application development life cycle to build spark applications using pyspark. review the applications using spark ui.

Syllabus

Detailed overview of the topics related to SQL, Python, Hadoop, Spark, etc covered as part of this course.

Introduction to Data Engineering Essentials Course

Overview of our support to Data Engineering Essentials course

Overview of SQL topics covered in the course

Overview of Python topics covered in the course

Overview of Getting Started with GCP related to the course

Overview of Spark and Databricks Environment related topics

Detailed outline of Spark SQL Topics in the course

Detailed outline of Pyspark Topics in the course

Detailed outline of ELT Data Pipelines on Databricks

Overview of Performance Tuning of Spark covered in the course

Understand the relevance of SQL for Data Engineering

Introduction to SQL for Data Engineering

Overview of Application Architecture and RDBMS

Overview of Database Technologies and relevance of SQL

Overview of Purpose Built Databases

Overview of Data Warehouse and Data Lake

Usage of RDBMS and Data Warehouse technologies

Differences and Similarities between RDBMS and Data Warehouse Technologies

Setup required tools to build applications using Python, SQL, etc

Introduction to Setting up Tools for Data Engineering Essentials

Setup VS Code on Windows

Setup Python 3.9 on Windows

Configure Environment Variable PATH for Python on Windows

Overview of learning Python using Python CLI

Integrate VSCode with Python on Windows

Install Postgres 14 on Windows 11

Getting Started with pgAdmin on Windows

Getting Started with pgAdmin on Mac

Conclusion of Setting up Tools for Data Engineering Essentials

Setup required tables and data in Postgres Database to write SQL Queries

Overview of Postgres Database Server and pgAdmin

Overview of Database Connection Details

Overview of Connecting to External Databases using pgAdmin

Create Application Database and User in Postgres Database Server

Clone Data Sets from Git Repository for Database Scripts

Setup Application Tables and Data in Postgres Database

Overview of pgAdmin to write SQL Queries

Writing Basic SQL Queries

Review Data Model Diagram

Define Problem Statement for SQL Queries

Filtering Data using SQL Queries

Total Aggregations using SQL Queries

Group By Aggregations using SQL Queries

Order of Execution of SQL Queries

Rules and Restrictions to Group and Filter Data in SQL queries

Filter Data based on Aggregated Results using Group By and Having

Inner Joins using SQL Queries

Outer Joins using SQL Queries

Filter and Aggregate on Join Results using SQL

Overview of Database Views

Overview of Common Table Expressions or CTEs

Outer Join with Additional Conditions in SQL Queries

Explanation about Fix of SQL Queries with Filtering on Outer Join Results

Advanced SQL Queries with examples such as Cumulative Aggregations and Ranking

Introduction to Cumulative Aggregations and Ranking in SQL Queries

Overview of CTAS to create tables based on Query Results

Create Tables for Cumulative Aggregations and Ranking

Overview of OVER and PARTITION BY Clause in SQL Queries

Compute Total Aggregation using OVER and PARTITION BY in SQL Queries

Overview of Ranking in SQL

Compute Global Ranks using SQL

Compute Ranks based on key using SQL

Rules and Restrictions to Filter Data based on Ranks in SQL

Filtering based on Global Ranks using Nested Queries and CTEs in SQL

Filtering based on Ranks per Partition using Nested Queries and CTEs in SQL

Create Students table with Data for ranking using SQL

Difference between rank and dense rank using SQL

Troubleshooting and Debugging Database Connectivity Issues

Introduction to SQL Troubleshooting and Debugging Guide

Overview of Database Connectivity Issues

Validate and Setup Telnet on Mac or PC

Validate Connectivity to Database Server using telnet

Troubleshoot Database Connectivity Issue with Correct Host Details

Current Databases and Users in Postgres Database Server

Troubleshoot Database Credentials and Permissions Issues

Overview of Compilation of SQL Queries

Troubleshooting Syntax Errors in SQL Queries

Troubleshooting Semantec Errors in SQL Queries

Overview of Bugs in SQL Queries

Development Best Practices with tips to troubleshoot SQL bugs

Develop Initial Solution based on the requirement

Identify and Troubleshoot Bugs in SQL Queries

Develop Solution using Development Best Practices

Read Explain Plans, identify performance bottlenecks and solve performance tuning problems

Introduction to Performance Tuning of SQL Queries

Overview of SQL Compilation Process and Explain Plans

Generate Explain Plans for SQL Queries

Review Tables used for Performance Tuning of SQL Queries

Review Data Storage Internals for Tables and Indexes

Review key terms used in Explain Plans for SQL Queries

Interpret Explain Plans for Basic SQL Queries

Review the Common Application Scenarios for Performance Tuning

Write SQL Queries for Customer Orders

Performance Testing of SQL Queries using Stored Procedure

Add Required Indexes to tune performance of SQL Queries

Guidelines on adding Indexes on Tables for SQL Queries

Interpreting the explain plan for SQL Queries using Indexes

Conclusion of Performance Tuning of SQL Queries

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Teachers how to process and pipe data, which are essential to handling the scale of today's production problems

Covers SQL and Python, two of the most important tools for modern operations

Develops skills for building production-grade data pipelines that can be used in enterprise settings

Includes building blocks for both batch and streaming pipelines using PySpark

Teaches how to tune the performance of PySpark pipelines for production environments

Requires a basic understanding of Python, SQL, and data engineering principles

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Data Engineering Essentials using SQL, Python, and PySpark with these activities:

Review SQL Fundamentals

Show steps

Refresh your memory on basic SQL concepts to strengthen your foundation. This will make it easier to grasp the more advanced topics covered in the course.

Browse courses on Database Concepts

Show steps

Review online tutorials or documentation on SQL basics.
Go through your notes or textbooks from previous SQL courses.
Practice writing simple SQL queries using an online SQL editor or a local database.

Participate in Online Discussion Forums

Show steps

Engage with your peers in online discussion forums to share knowledge, ask questions, and get feedback. This will expose you to diverse perspectives and enhance your understanding of the course material.

Show steps

Join online discussion forums related to the course topics.
Actively participate in discussions by posting thoughtful questions and responses.
Read and respond to the posts of other students.
Respect different viewpoints and engage in constructive discussions.

Create a Data Engineering Glossary

Show steps

Develop a glossary of important terms and concepts related to data engineering. This will help you build your vocabulary and deepen your understanding of the field.

Browse courses on Terminology

Show steps

Identify key terms and concepts from the course material.
Research and gather definitions and explanations for each term.
Organize the terms alphabetically or by category.
Create a document or spreadsheet to compile your glossary.

Four other activities

Expand to see all activities and additional details

Show all seven activities

Practice SQL Queries

Show steps

Frequent practice is the key to mastering SQL. Solve as many SQL queries as possible to refine your command over the language and improve your problem-solving ability.

Browse courses on SQL Queries

Show steps

Connect to a database management system (DBMS) using a SQL client tool of your choice.
Create a new database and tables to work with.
Write SQL queries to perform various operations like data retrieval, insertion, updation, and deletion.
Test your queries and troubleshoot any errors.
Repeat steps 3 and 4 until you are confident in your ability to write SQL queries.

Work on Python Projects

Show steps

Hands-on experience is invaluable for solidifying your understanding of Python. Work on projects that involve data manipulation, analysis, and visualization to enhance your practical skills.

Browse courses on Python Programming

Show steps

Choose a project idea that aligns with your interests and the course material.
Gather the required resources and set up a development environment.
Implement your project using Python and appropriate libraries.
Test and debug your code to ensure it meets the project requirements.
Document your project and share it with others for feedback and learning.

Explore Spark DataFrames and SQL

Show steps

Gain proficiency in working with Apache Spark DataFrames and SQL by practicing various operations and techniques. This will help you master data manipulation and analysis using Spark.

Browse courses on Apache Spark

Show steps

Set up a Spark environment using a cloud platform or a local cluster.
Load data into Spark DataFrames and explore the DataFrame API.
Perform data transformations and aggregations using DataFrames.
Write Spark SQL queries to perform complex data analysis.
Optimize your Spark code for performance and scalability.

Build a Data Engineering Pipeline

Show steps

Challenge yourself by building a complete data engineering pipeline that involves data ingestion, transformation, and analysis. This project will provide you with a comprehensive understanding of the entire data engineering process.

Browse courses on Data Processing

Show steps

Define the scope and requirements of your data engineering pipeline.
Choose appropriate tools and technologies for each stage of the pipeline.
Implement the data ingestion process to load data from various sources.
Develop data transformation and cleansing processes to prepare the data for analysis.
Build data analysis and visualization components to explore and present insights from the data.

Career center

Learners who complete Data Engineering Essentials using SQL, Python, and PySpark will develop knowledge and skills that may be useful to these careers:

Data Engineer

Data Engineers are responsible for building, testing, and deploying data pipelines. They work closely with data scientists and other stakeholders to ensure that data meets their needs. This course provides a comprehensive overview of the skills and knowledge needed to be a successful Data Engineer, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in data engineering and prepare you for a career in this in-demand field.

See salaries and explore the career path for Data Engineer

Data Scientist

Data Scientists use data to solve business problems. They work with data engineers to build data pipelines and with data analysts to analyze data and develop insights. This course provides a strong foundation in the skills and knowledge needed to be a successful Data Scientist, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in data science and prepare you for a career in this exciting field.

See salaries and explore the career path for Data Scientist

Data Analyst

Data Analysts use data to analyze trends and patterns. They work with data engineers and data scientists to build data pipelines and develop insights. This course provides a strong foundation in the skills and knowledge needed to be a successful Data Analyst, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in data analysis and prepare you for a career in this growing field.

See salaries and explore the career path for Data Analyst

Database Administrator

Database Administrators are responsible for managing and maintaining databases. They work with data engineers and data scientists to ensure that data is stored and accessed efficiently. This course provides a comprehensive overview of the skills and knowledge needed to be a successful Database Administrator, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in database administration and prepare you for a career in this essential field.

See salaries and explore the career path for Database Administrator

Software Engineer

Software Engineers design, build, and maintain software applications. They work with data engineers and data scientists to build data pipelines and develop insights. This course provides a strong foundation in the skills and knowledge needed to be a successful Software Engineer, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in software engineering and prepare you for a career in this in-demand field.

See salaries and explore the career path for Software Engineer

Data Architect

Data Architects design and implement data architectures for organizations. They work with data engineers and data scientists to ensure that data is stored and accessed efficiently. This course provides a comprehensive overview of the skills and knowledge needed to be a successful Data Architect, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in data architecture and prepare you for a career in this essential field.

See salaries and explore the career path for Data Architect

Business Analyst

Business Analysts use data to solve business problems. They work with data engineers and data scientists to build data pipelines and develop insights. This course provides a strong foundation in the skills and knowledge needed to be a successful Business Analyst, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in business analysis and prepare you for a career in this growing field.

See salaries and explore the career path for Business Analyst

Project Manager

Project Managers plan, execute, and close projects. They work with data engineers and data scientists to ensure that data projects are delivered on time and within budget. This course provides a comprehensive overview of the skills and knowledge needed to be a successful Project Manager, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in project management and prepare you for a career in this essential field.

See salaries and explore the career path for Project Manager

Data Warehouse Analyst

Data Warehouse Analysts design and implement data warehouses for organizations. They work with data engineers and data scientists to ensure that data is stored and accessed efficiently. This course provides a comprehensive overview of the skills and knowledge needed to be a successful Data Warehouse Analyst, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in data warehousing and prepare you for a career in this essential field.

See salaries and explore the career path for Data Warehouse Analyst

Database Developer

Database Developers design and develop databases for organizations. They work with data engineers and data scientists to ensure that data is stored and accessed efficiently. This course provides a comprehensive overview of the skills and knowledge needed to be a successful Database Developer, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in database development and prepare you for a career in this essential field.

See salaries and explore the career path for Database Developer

Data Visualization Analyst

Data Visualization Analysts use data to create visualizations that communicate insights to stakeholders. They work with data engineers and data scientists to build data pipelines and develop insights. This course provides a strong foundation in the skills and knowledge needed to be a successful Data Visualization Analyst, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in data visualization and prepare you for a career in this growing field.

See salaries and explore the career path for Data Visualization Analyst

Data Governance Analyst

Data Governance Analysts develop and implement data governance policies and procedures for organizations. They work with data engineers and data scientists to ensure that data is used in a consistent and ethical manner. This course provides a comprehensive overview of the skills and knowledge needed to be a successful Data Governance Analyst, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in data governance and prepare you for a career in this essential field.

See salaries and explore the career path for Data Governance Analyst

Information Security Analyst

Information Security Analysts protect data from unauthorized access, use, disclosure, disruption, modification, or destruction. They work with data engineers and data scientists to ensure that data is stored and accessed securely. This course provides a comprehensive overview of the skills and knowledge needed to be a successful Information Security Analyst, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in information security and prepare you for a career in this essential field.

See salaries and explore the career path for Information Security Analyst

Data Privacy Analyst

Data Privacy Analysts develop and implement data privacy policies and procedures for organizations. They work with data engineers and data scientists to ensure that data is used in a compliant manner. This course provides a comprehensive overview of the skills and knowledge needed to be a successful Data Privacy Analyst, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in data privacy and prepare you for a career in this essential field.

See salaries and explore the career path for Data Privacy Analyst

Data Science Consultant

Data Science Consultants help organizations to use data to make better decisions. They work with data engineers and data scientists to build data pipelines and develop insights. This course provides a strong foundation in the skills and knowledge needed to be a successful Data Science Consultant, including SQL, Python, and PySpark. With hands-on exercises and real-world examples, this course will help you build a foundation in data science consulting and prepare you for a career in this growing field.

See salaries and explore the career path for Data Science Consultant