Table of Contents
When your friend mentioned her new project on ETL processes yesterday, you probably just smiled and nodded. Most people haven‘t heard of ETL or question what exactly it involves. Well, I used to be one of them too!
After years of working with data warehousing and analytics, I can clarify that ETL stands for Extract, Transform, Load. This process takes data from multiple sources, prepares it for analytical use, and loads it into a destination database like a data warehouse.
ETL serves as the backbone for reporting and analytics. According to Gartner, organizations spend upwards of 70% of their time and resources in preparing data for analysis.
So let me walk you through what really happens behind the scenes in ETL land. Grab your favorite brew and let‘s get started!
Extracting Data from a Myriad of Sources
The first step in ETL involves extracting data from the various systems that capture and manage it. This could include:
- Transactional Apps – such as order, shipping, ecommerce platforms
- Legacy Systems – such as mainframes, older DBs
- Files – such as Excel, delimited text files
- SaaS Apps – such as Marketo, Salesforce, Jira
For example, an online retailer may source order data from a shopping cart app, inventory data from an ERP system, and customer contact data from a marketing automation platform.
As you can imagine, these source systems can be quite diverse in terms of technology, architecture and data structure. They may involve relational databases, NoSQL systems, flat files, APIs, cloud-based apps and more.
According to IBM, large enterprises have more than 100 different types of source systems on average. The volume of data they store is massive as well.
Data volume growth – Source: Microfocus 2021 State of ETL Report
As you see above, 63% of organizations deal with over 1 terabyte of data. All this data needs to make it into the warehouse for those lovely Tableau dashboards we create!
Retrieving Relevant Data
To handle such a deluge of data, ETL tools connect to sources via pre-built connectors and extraction methods. Commonly used methods include:
Full Loads – Extract entire tables or files. Useful for initial data population, but inefficient ongoing.
Incremental Loads – Extract only records added/changed since a certain date based on time stamp or log files. Much more efficient for daily loads.
Change Data Capture – Track and extract data changes from database transaction logs and replicate them. Minimizes overhead on sources.
Determining relevant datasets from various systems and keeping them synchronized adds to the complexity. For instance, our retail customer entity may change her phone number. This update needs to flow from the CRM app where she updated it to the data warehouse for accurate analytics.
Dealing with Dirty Data
With data sourced from so many systems, inaccurate, incomplete or duplicated records are common. For example, product names like "T-Shirt" and "T Shirt" may not be standardized. Units may vary – ounces vs grams. Descriptions have abbreviations like S, M, L vs Small, Medium, Large.
Such data issues seriously impact reporting accuracy and have to be fixed. Data profiling tools assess and report various metrics regarding data health. Based on these metrics, standards and rules are created to clean and transform source data.
Transforming Data for Analysis
The Transform step applies various rules and logic to convert sourced data into analysis-ready form. This could involve:
- Standardization – Consistent formats for names, addresses, units etc
- De-duplication – Identify and remove duplicate entries
- Verification – Check for and fix invalid values
- Enrichment – Augment with calculated metrics and lookups
- Filtering – Remove unused columns not needed for analytics
For example, full customer shipping addresses extracted from an order system may need splitting into distinct address line 1, city name, state, country and pin code columns in the data warehouse.
Product attributes may undergo unit conversions from grams to pounds. Descriptive reasons for order cancellations and returns can get coded into buckets for easy reporting.
Tools like Informatica, Talend help define such transformation rules without extensive coding. This metadata helps improve visibility into the end-to-end data flow.
According to talend, data teams spend upwards of 70% of their time on such data prep tasks – hence the term “data janitor”! But transformed data delivers huge analytical value for organizations.
Achieving Resilient, Scalable Data Loads
Once extracted data gets transformed to desired formats, the third ETL step involves loading it into the target data warehouse database for analysis and reporting.
This data load process needs robust handling so that incomplete loads do not create reporting issues later. Common loading methods include:
- Full Refresh – Remove all existing data first, then load latest snapshot
- Incremental Loads – Use SQL INSERT, UPDATE, MERGE statements to apply new changes without compromising existing data
Data loads need to account for downstream dependencies. For example, if an updated product name flows into the warehouse, all references to the old name across reports and dependent datasets also need updating.
Increasing data volumes create scaling challenges during loads. Partitioning schemes, parallel load utilities and cloud infrastructure help handle billions of records for 24/7 warehouse availability.
According to a Datameer survey, 69% of companies use cloud platforms like AWS Redshift for DW/ETL needs due to their scalability.
Boosting ETL Processing Performance
Lengthy ETL runs risk delaying critical workflows like production reports. Tuning and optimizing ETL improves overall data analytics velocity.
Here are some key ETL optimization techniques:
- Partitioning – Break up data into smaller logical chunks for quicker processing
- Parallelism – Use multi-core CPUs and workflows for simultaneous processing
- Caching – Save commonly accessed data in memory to avoid repeated retrievals
- Compression – Compact data during motion to reduce network loads
- Scaling – Provision higher capacity servers/cloud infrastructure
- Monitor resource usage trends to identify bottlenecks
"We accelerated our ETL processes by 4X through a combination of partitioning, workflow parallelism and cloud infrastructure" – Mate, ETL Architect, MediaCorp
Well-designed ETL solutions also implement resilience capabilities such as failure notifications, restart ability and metadata-driven recovery to handle unexpected issues. Make sure to assess your environment thoroughly rather than simply adding more components.
Validating ETL Processing Integrity
With so many data modification steps, how can one validate ETL code and the final outputs?
ETL testing plays a key role through steps like:
- Test with sample datasets to validate expected vs actual data changes at each transform stage
- Perform mock runs and compare outputs vs known expected results
- Check counts of extracted, transformed and loaded records across systems
- Query randomly sampled data from warehouse to confirm integrity
- Automate testing suites to run daily after ETL jobs finish
Such testing improves data accuracy in reports, thereby increasing business user trust. It also minimizes firefighting production issues!
According to research by Celonis, 73% of data and IT leaders prioritize improving data quality, accuracy and process efficiency to drive business value. Proactive testing alignment helps achieve this goal across the data pipeline.
So there you have it my friend – a comprehensive inside look into ETL processes. Let‘s now get you hands-on by modeling some common scenarios. Ping me if you have any other questions!
Key Takeways on the ETL Process
- ETL pulls together data from numerous source systems
- Extracting only relevant deltas and keeping synchronised adds complexity
- Much effort goes into transforming dirty data into analysis-ready state
- Loading necessitates resilient, high-volume methods
- Optimization and testing are vital for data pipeline health
Happy data wrangling!
Prashanth
Data Analytics Architect