Introduction to ETL and ELT Pipelines

ETL and ELT in Python

Jake Roach

Data Engineer

Graphic of business intelligence, machine learning, and AI.

Data pipelines

... are responsible for moving data from a source to a destination, and transforming it somewhere along the way.

Sources and destinations of a data pipeline.

ETL

Extract, transform, load
Traditional data pipeline design pattern
Sources may be tabular or non-tabular
Leverage Python with pandas

ELT

Extract, load, transform
More recent pattern
Data warehouses
Typically tabular data

Extract, transform, load (ETL)

def load(data_frame, target_table):
    # Some custom-built Python logic to load data to SQL
    data_frame.to_sql(name=target_table, con=POSTGRES_CONNECTION)
    print(f"Loading data to the {target_table} table")

# Now, run the data pipeline
extracted_data = extract(file_name="raw_data.csv")
transformed_data = transform(data_frame=extracted_data)
load(data_frame=transformed_data, target_table="cleaned_data")

Extracting data from raw_data.csv
Transforming data to remove 'null' records
Loading data to the cleaned_data table

Extract, load, transform (ELT)

...
def transform(source_table, target_table):
    data_warehouse.run_sql("""
        CREATE TABLE {target_table} AS
          SELECT
              <field-name>, <field-name>, ...
          FROM {source_table};
    """)

# Similar to ETL pipelines, call the extract, load, and transform functions
extracted_data = extract(file_name="raw_data.csv")
load(data_frame=extracted_data, table_name="raw_data")
transform(source_table="raw_data", target_table="cleaned_data")

We'll also take a look at...

Graphic that shows topics to be discussed later in the course.

Let's practice!

ETL and ELT in Python