Skip to main content

ETL in Python

Leverage your Python and SQL knowledge to create an ETL pipeline to ingest, transform, and load data into a database.

Start Course for Free
4 Hours16 Videos48 Exercises9,477 Learners3850 XP

Create Your Free Account

GoogleLinkedInFacebook

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.

Loved by learners at thousands of companies

Course Description

Build Your ETL Skills

Developing your ETL skills will improve your data engineering processes and means that you can work with data more efficiently. This course covers the foundations of creating pipelines to efficiently extract, transform, and load data into your company’s systems. You’ll get hands-on experience by helping a fictional private equity firm process sales data to make data-driven decisions when buying real estate.

Learn to Set up ETL Pipelines

The course opens with an explanation of the ETL process and a deep-dive into data extraction. You’ll then move on to reviewing the ETL pipeline and the tools and techniques you need to transform data. Once the data is in your desired format, you can move it to a clean table and eventually move on to the last stage of the pipeline; loading your data ready to be used.

You’ll finish the course by looking at how the ETL pipeline is used to build useful insight for the fictional company’s shareholders. You’ll look at more complex queries such as aggregation, averages, and max/min functions, before moving on to ways that you can translate raw SQL queries into readable Excel files.

Throughout this course, you’ll be introduced to ETL tools and techniques that will simplify your workflow and create better structures for your data. These tools include SQLAlchemy, which can help you to perform insert and delete statements on your data, as well as offering aggregation functionality.
  1. 1

    Explore the data and requirements

    Free

    In this first chapter, you’ll be introduced to your role as a data engineer in a private equity fund. You'll be exposed to the whole ETL pipeline before deep-diving into its first phase: the extraction process.

    Play Chapter Now
    Introduction to ETL in Python
    50 xp
    The ETL process
    100 xp
    Downloading a ZIP file
    100 xp
    Exploring a ZIP file
    100 xp
    Ask the right questions
    50 xp
    Reading from a CSV file
    100 xp
    Writing to CSV
    100 xp
    Extracting
    50 xp
    Downloading the new dataset file from web
    100 xp
    Project folder structure
    50 xp
    Extract 'em all!
    100 xp
  2. 2

    Create the ETL foundations

    In this chapter you're going to create some important foundations for our ETL pipeline. In particular, along with data transformation, you'll start setting up the components needed to communicate with the database.

    Play Chapter Now
    Let's talk with the database
    50 xp
    SQLAlchemy core components
    50 xp
    Engines and sessions
    100 xp
    Database tables
    50 xp
    Table class definition
    100 xp
    Columns definition
    100 xp
    Data cleaning
    50 xp
    Lower string and date format
    100 xp
    Price and description
    100 xp
    Put transform operations together
    50 xp
    Setup base script
    100 xp
    Create tables
    100 xp
    Transform 'em all!
    100 xp
  3. 3

    From raw to clean data

    This chapter is all about moving transformed data to a clean table, from which insights can be built. You'll explore how to create a unique key to perform insert and delete statements on SQLAlchemy. At the end of this chapter you'll complete the load process, the last step of the ETL pipeline.

    Play Chapter Now
    Unique key definition and clean table
    50 xp
    Date data type definition
    100 xp
    Unique key definition
    100 xp
    Insert and delete operations
    50 xp
    Querying
    100 xp
    Insert and delete
    100 xp
    Put load operations together
    50 xp
    Insert operation
    100 xp
    Delete operation
    100 xp
    Load 'em all!
    100 xp
  4. 4

    From clean data to meaningful insights

    This chapter will show you how the data the ETL pipeline processes every month is used to build insights, readable by the fund’s shareholders. You'll explore key SQL components to build more complex queries and create these insights. You'll also explore libraries that will translate raw SQL queries into more readable Excel files.

    Play Chapter Now
    Operators
    50 xp
    Sales for Dublin and Cork
    100 xp
    First month 2021 sales
    100 xp
    Sqlalchemy func
    50 xp
    Aggregate functions
    50 xp
    Average, max and min functions
    100 xp
    Create the insights
    50 xp
    Creating the insights view
    100 xp
    How many counties?
    50 xp
    Working with Excel files
    50 xp
    Create a simple Excel file
    100 xp
    Add a table into Excel file
    100 xp
    Export monthly insights
    100 xp
    Wrap-up
    50 xp

Datasets

Property price register 2021

Collaborators

Hadrien Lacroix

Prerequisites

Understanding Data EngineeringIntroduction to Data EngineeringPython Data Science Toolbox (Part 2)
Stefano Francavilla Headshot

Stefano Francavilla

Stefano is the CEO and co-founder of Geowox.

Stefano is the CEO and co-founder of Geowox, a company using AI and big data to value residential properties. In a previous life, he studied Computer Science at the polytechnic university of Milan while founding a software development company. He then worked as a product engineer at Intercom, advised portfolio startups at Growing Capital, a seed investment firm.
See More

What do other learners have to say?

    FAQs

    Join over 11 million learners and start ETL in Python today!

    Create Your Free Account

    GoogleLinkedInFacebook

    or

    By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.