What is ETL?

What is ETL?

ETL stands for Extract, Transform, Load. It is the fundamental process by which organisations move data from operational systems into analytical systems. Every time you see a dashboard, a business report, or an AI model that uses data from multiple sources, there is an ETL process working behind the scenes to make it possible.

The three stages are:

• Extract: Pull data from source systems such as databases, APIs, flat files, SaaS applications, and spreadsheets.
• Transform: Clean, restructure, and enrich the data. This includes removing duplicates, fixing formatting errors, converting data types, aggregating records, and applying business logic.
• Load: Write the transformed data into a destination system, typically a data warehouse, data lake, or analytics database.

How ETL Works in Practice

Consider a practical example for a retail company operating in three Southeast Asian markets:

Extract: Every night at midnight, the ETL process pulls sales transactions from the Shopee seller portal (Indonesia), Lazada API (Thailand), and the company's own e-commerce platform (Singapore). It also extracts inventory data from the warehouse management system and customer data from the CRM.

Transform: The raw data is processed:

• Currency values are converted to a common base (e.g., USD or SGD)
• Product names and categories are standardised across platforms
• Duplicate customer records are merged
• Invalid or incomplete transactions are flagged and quarantined
• Sales metrics like gross margin, average order value, and customer lifetime value are calculated

Load: The clean, standardised data is loaded into a Google BigQuery data warehouse, where it powers a Looker dashboard that the management team reviews every morning.

ETL vs ELT

A modern variation called ELT (Extract, Load, Transform) has gained popularity with the rise of cloud data warehouses. The key difference:

• ETL: Data is transformed before loading. Transformation happens on a separate processing system.
• ELT: Data is loaded raw into the warehouse first, then transformed using the warehouse's own processing power.

ELT has become popular because cloud warehouses like BigQuery and Snowflake have enormous processing capacity, making it practical to transform data after loading. ELT also preserves the raw data, which is useful for future reprocessing or new analyses.

For most SMBs, the choice between ETL and ELT is less important than having a reliable, automated process in place. Many modern tools support both approaches.

Common ETL Tools

ETL in Southeast Asian Businesses

ETL processes face unique challenges in the ASEAN region:

• Multi-platform integration: Southeast Asian businesses commonly sell through multiple marketplaces (Shopee, Lazada, Tokopedia, Tiki) plus their own channels. Each platform has different data formats and APIs.
• Language and character sets: Data from different markets may include Thai, Vietnamese, Bahasa, Chinese, and English text, requiring careful handling of character encoding during transformation.
• Payment diversity: Integrating data from diverse payment methods, including credit cards, bank transfers, e-wallets (GrabPay, GoPay, Touch 'n Go), and cash on delivery, adds complexity to financial data transformation.
• Varying data quality: Data quality standards can differ significantly across markets and systems, making the Transform step particularly important.

Best Practices for ETL

• Automate everything: Manual data processing is the enemy of reliability. Even if the initial setup takes longer, automation pays for itself quickly.
• Build in data quality checks: Validate row counts, check for null values, verify data types, and compare totals against source systems.
• Use incremental loads: Rather than reprocessing all data every time, process only new or changed records. This reduces cost and processing time.
• Version your transformation logic: Treat ETL code like application code with version control, code review, and testing.
• Plan for schema changes: Source systems will change. Build ETL processes that can detect and handle schema changes gracefully.