Insights

What is an ETL?

by Lance Abbrederis on Oct 28, 2022

The term ETL stands for Extract, Transform and Load, and is a three-step process used to consolidate data from multiple sources. At its core, ETL is a standard process where data is collected from various sources (extracted), converted into a desired format (transformed), then stored into its new destination (loaded).

ETL tools enable data integration strategies by allowing companies to gather data from multiple data sources and consolidate it into a single, centralized location. ETL tools also make it possible for different types of data to work together.

A typical ETL process collects and refines different types of data, then delivers the data to a data lake or data warehouse such as BigQuery.

The ETL Process Explained

Step 1: Extraction

In the first step, extracted data sets come from a source (e.g., Salesforce, Google AdWords, etc.). Sources can consist of any type of structured or unstructured data from REST APIs, SQL or NoSQL servers, Flat files, Spreadsheets and more.

Extraction can process anywhere from kilobytes to terabytes of data and can run in a frequency that varies between a couple of minutes to days or can be a real-time or event-driven process as with streaming ETLs.

Step 2: Transform

The second step of the ETL process is transformation. In this step, a set of rules or functions are applied on the extracted data to convert it into a single standard format.

The transformation rules and functions can involve the following tasks:

  • Filtering — loading only certain data elements into the data warehouse. For example, Launchpad allows you to select only the data elements you require from a pre-defined, structured schema.
  • Cleaning — fixing values, replacing strings, converting text values into boolean (true/false) or even replacing campaign names and so on. Launchpad employs a set of powerful transformative functions which allow you to do this with any or all data elements.
  • Joining — Replacing values or populating new elements with mapped lookup values. A good example would be using derived fields in conjunction with lookup tables in Launchpad.
  • Splitting — splitting single data elements into one or more additional elements. A good use-case for this might be splitting a value by commas into separate values. Launchpad allows you to do this via functions and derived fields.

Step 3: Load

This is the final step of the ETL process, which involves loading the transformed data to its final data destination. The destination can range from small-to-large data warehouses, conventional databases or cloud storage like Amazon's S3 or Google Cloud Storage buckets.

The frequency of the loading varies and depends on business requirements and can be incremental (i.e. daily data appended to a table) or full (i.e. starting a Google Analytics historic back-fill from the year 2020 through now)

Conclusion

ETL is the backbone for most modern data ingestion and integration pipelines that facilitate accurate and efficient analytics. The importance of ETL will only grow in the future with the unprecedented demand for data

Launchpad is our powerful and easy to use ETL. It's a one stop shop to transfer data from an array of domains to destinations of your choice. It helps customers easily access their data and transfer it to their preferred data warehouse where it can then be transformed, analyzed and visualized.

Find out more

Share this post:
  • Lance Abbrederis

    About the Author

    Lance is head of operations at Calibrate Analytics. His passion for operational excellence can be traced back to 8 years of military service and 24 years of strategic business leadership. He is a huge stickler for data driven decisions, a key ingredient for all businesses and what we help our customers achieve.