何謂 ETL（擷取、轉換、載入）？

ETL stands for Extract, Transform, and Load. ETL describes the basic data integration process used to collect and then synthesize data from multiple sources that do not typically share data types. Synthesized data is then placed in a repository, like a Data Warehouse, Data Mart, or Data Hub, for use by end data users.

An ETL process, or similar process, is essential for ensuring data consistency and integrity before it is loaded into a storage system. Normally, data pulled from sources like data lakes are unstructured, and then are combined with other data sources that are structured to form insights across disparate data domains.

(Unstructured data is data like social media, images and photos, and audio—formats that do not readily conform to a data model. Structured data has been standardized, such as product information which clearly separates product name, description, dimensions, etc., and can be placed alongside similar entries in a structured database, like an RDBMS.)

It is the need to combine structured and unstructured data that has led to applications and suites of tools that help achieve ETL processes more efficiently and effectively. While ETL can be performed by multiple extraction, transformation, and loading tools, ETL suites save time and offer exceptional features that have reduced the complexity of ETL, and assists in several ways:

• Provides deep historical business context
• Enables Business Intelligence improving decision making
• Combines data domains in ways previously unimagined
• Enhances a common central data repository
• Improves productivity, enhances features, reduces learning curve

Organizations and businesses use ETL to access multiple data sources and ultimately gain a better hold of operational insights.

• Provides deep historical business context
• Enables Business Intelligence improving decision making
• Combines data domains in ways previously unimagined
• Enhances a common central data repository
• Improves productivity, enhances features, reduces learning curve

Data integration is a broad term that encompasses the combination of multiple disparate data sources into a unified view. Within this broad data integration definition fits ETL. ETL is an approach to data integration that relies on a set of business rules to define the data required, and then uses the Extract, Transform, and Load process to acquire and analyze that data from various business systems and data repositories. Informing ETL, data integration would cover master references, data quality parameters, and importantly organization wide data definitions.

ETL, which stands for Extract, Transform, and Load, describes a basic process of data aggregation from multiple data sources. The process succinctly is: data is extracted, or “pulled”, from an external data source, it is then transformed, in staging area, to conform to the destination format (data format and storage format), and then it is loaded into a data warehouse.

While this is a traditionally popular data integration process, teams are not limited to ETL. ELT, or Extract, then Load, then Transform is another process. In the ELT process, data is loaded into the Data Warehouse and then transformed. The main advantage is that raw data is stored indefinitely and can undergo multiple transformations within the Data Warehouse to meet multiple demands. This load before transforming data storage, which was once a constraint, has been enabled by the cloud and its “infinite” scalability.

In short:

• ETL transforms data in a staging area before the DW, while ELT transforms data within the DW
• ETL does not transfer raw data into the DW, while ELT loads raw data directly into the DW