ETL: Extract, Transform, Load | Dagster Glossary

Back to Glossary Index

ETL: Extract, Transform, Load

Extract, transform, and load data between different systems.

ETL definition:

ELT stands for Extract, Load, Transform, and is a process used in modern data pipelines for integrating and transforming data from various sources into a centralized data store. The ELT process is similar to the more traditional ETL (Extract, Transform, Load) process, but with a key difference: data is extracted from source systems and loaded directly into a data store, where it can then be transformed.

Dagster provides many integrations with common ETL/ELT tools.

Data ETL example in Python:

Here's an example of an ELT process in Python using the Pandas library and SQLite3. Please note that you need to have the necessary Python libraries installed in your Python environment to run the code:

Given two input files of customers.csv and orders.csv as follows:

customer_id,name,total_orders
1,Mary,42
2,Brian,28
3,Mercedes,7
4,Rose, 20

and

customer_id,item,value
1,cat food,12
1,apples,6
1,books,24
2,bananas,12
2,candles,12
2,coffee,4
3,nails,7
4,paper,18
4,pencils,2

Extract: We start by extracting data from our source systems. For this example, let's assume we have a CSV file containing customer orders.

import pandas as pd

# Extract data from CSV file
orders_df = pd.read_csv('orders.csv')
customers_df = pd.read_csv('customers.csv')

Load: Next, we load the data into our data store. In this case, we'll use a SQLite database.

import sqlite3

# Connect to SQLite database
conn = sqlite3.connect('my_database.db')

# Load data into database
orders_df.to_sql('orders', conn, if_exists='replace', index=False)
customers_df.to_sql('customers', conn, if_exists='replace', index=False)

Transform: Finally, we transform the data as needed. For example, we might want to join the orders data with customer data to get more insights.

# Extract customer data from database
customers_df = pd.read_sql_query('SELECT * FROM customers', conn)

# Join orders and customers data
enriched_df = pd.merge(orders_df, customers_df, on='customer_id')

print(enriched_df)

In this example, we used Pandas to extract data from a CSV file, load it into a SQLite database, and then join it with customer data to enrich the dataset. This ELT process can be repeated for other sources of data, allowing us to integrate and transform multiple datasets into a centralized data store.

Our example would yield the following output:

   customer_id      item  value      name  total_orders
0            1  cat food     12      Mary            42
1            1    apples      6      Mary            42
2            1     books     24      Mary            42
3            2   bananas     12     Brian            28
4            2   candles     12     Brian            28
5            2    coffee      4     Brian            28
6            3     nails      7  Mercedes             7
7            4     paper     18      Rose            20
8            4   pencils      2      Rose            20

Other data engineering terms related to
Data Transformation:
Dagster Glossary code icon

Align

Aligning data can mean one of three things: aligning datasets, meeting business rules, or arranging data elements in memory.
An image representing the data engineering concept of 'Align'
Dagster Glossary code icon

Clean or Cleanse

Remove invalid or inconsistent data values, such as empty fields or outliers.
An image representing the data engineering concept of 'Clean or Cleanse'
Dagster Glossary code icon

Cluster

Group data points based on similarities or patterns to facilitate analysis and modeling.
An image representing the data engineering concept of 'Cluster'
Dagster Glossary code icon

Curate

Select, organize, and annotate data to make it more useful for analysis and modeling.
An image representing the data engineering concept of 'Curate'
Dagster Glossary code icon

Denoise

Remove noise or artifacts from data to improve its accuracy and quality.
An image representing the data engineering concept of 'Denoise'
Dagster Glossary code icon

Denormalize

Optimize data for faster read access by reducing the number of joins needed to retrieve related data.
An image representing the data engineering concept of 'Denormalize'
Dagster Glossary code icon

Derive

Extracting, transforming, and generating new data from existing datasets.
An image representing the data engineering concept of 'Derive'
Dagster Glossary code icon

Discretize

Transform continuous data into discrete categories or bins to simplify analysis.
An image representing the data engineering concept of 'Discretize'
Dagster Glossary code icon

Encode

Convert categorical variables into numerical representations for ML algorithms.
An image representing the data engineering concept of 'Encode'
Dagster Glossary code icon

Filter

Extract a subset of data based on specific criteria or conditions.
An image representing the data engineering concept of 'Filter'
Dagster Glossary code icon

Fragment

Break data down into smaller chunks for storage and management purposes.
An image representing the data engineering concept of 'Fragment'
Dagster Glossary code icon

Homogenize

Make data uniform, consistent, and comparable.
An image representing the data engineering concept of 'Homogenize'
Dagster Glossary code icon

Impute

Fill in missing data values with estimated or imputed values to facilitate analysis.
An image representing the data engineering concept of 'Impute'
Dagster Glossary code icon

Linearize

Transforming the relationship between variables to make datasets approximately linear.
An image representing the data engineering concept of 'Linearize'

Munge

See 'wrangle'.
An image representing the data engineering concept of 'Munge'
Dagster Glossary code icon

Normalize

Standardize data values to facilitate comparison and analysis. Organize data into a consistent format.
Dagster Glossary code icon

Reduce

Convert a large set of data into a smaller, more manageable form without significant loss of information.
An image representing the data engineering concept of 'Reduce'
Dagster Glossary code icon

Reshape

Change the structure of data to better fit specific analysis or modeling requirements.
An image representing the data engineering concept of 'Reshape'
Dagster Glossary code icon

Serialize

Convert data into a linear format for efficient storage and processing.
An image representing the data engineering concept of 'Serialize'
Dagster Glossary code icon

Shred

Break down large datasets into smaller, more manageable pieces for easier processing and analysis.
Dagster Glossary code icon

Skew

An imbalance in the distribution or representation of data.
Dagster Glossary code icon

Split

Divide a dataset into training, validation, and testing sets for machine learning model training.
Dagster Glossary code icon

Standardize

Transform data to a common unit or format to facilitate comparison and analysis.
Dagster Glossary code icon

Tokenize

Convert data into tokens or smaller units to simplify analysis or processing.
An image representing the data engineering concept of 'Tokenize'

Transform

Convert data from one format or structure to another.
An image representing the data engineering concept of 'Transform'
Dagster Glossary code icon

Wrangle

Convert unstructured data into a structured format.
An image representing the data engineering concept of 'Wrangle'