15ETL

Extraction, Transformation, and Loading

Outline

Extraction, Transformation, and Loading
Conceptual ETL Design using BPMN
Conceptual Design of the Northwind ETL

ETL stands for Extraction, Transformation, and Loading. It is a crucial process in data warehousing and business intelligence that involves moving and processing data from various sources so it can be effectively analyzed.

Extraction

What it is: The process of reading and collecting data from diverse source systems.

Sources: These can be internal (like transactional databases, CRM systems) or external (like third-party data feeds, social media data).

Transformation

What it is: A set of rules and functions applied to the extracted data to prepare it for loading into the target data warehouse.

Purpose: To ensure the data is clean, consistent, compliant (e.g., with business rules), and structured for analysis.

Examples: Filtering, cleaning (handling nulls/errors), aggregating, calculating new values, translating codes, and joining data from different sources.

What it is: The process of writing the transformed data into the target data warehouse or database.

Types: Can be a full load (overwriting all existing data) or an incremental load (adding new or updated records).

Conceptual ETL Design and BPMN

The Challenge

No agreed way to specify ETL at a conceptual level: Historically, there hasn't been a standard, high-level language to conceptually describe what an ETL process should do, making design and communication difficult before implementation.

The Solution: Using BPMN

Conceptual Model based on BPMN: The proposed solution is to use the Business Process Modeling Notation (BPMN) to model ETL design.

BPMN is a standard for modeling business processes. It provides a conceptual and implementation-independent specification of processes.

Advantages of Using BPMN for ETL

Familiarity: Users already familiar with BPMN don't need to learn a new, specialized language for designing ETL processes.

Implementation-Independent: The design focuses on the logic and flow of data and tasks rather than the specific software or platform used to execute it.

Executable Translation: Processes expressed in BPMN can be translated into executable specifications for ETL tools (e.g., Microsoft’s Integration Services or similar platforms). This bridges the gap between high-level design and actual implementation.

Extraction, Transformation, and Loading (ETL)

Extract data from internal and external sources, transform data, and load data into a data warehouse (ETL)
No agreed way to specify ETL at a conceptual level
We study conceptual ETL design
Conceptual model based on the Business Process Modeling Notation (BPMN)
- Users already familiar with BPMN do not need to learn another language to design ETL
- BPMN provides a conceptual and implementation-independent specification of processes
- Processes expressed in BPMN can be translated into executable specifications(e.g., Microsoft’s Integration Services)

Extraction, Transformation, and Loading

Conceptual ETL Design using BPMN: Core Principles

The approach to using Business Process Modeling Notation (BPMN) for conceptual ETL design is a strategic solution to the lack of a standardized, high-level method for planning data integration processes.

1. The Foundational Assumption

The entire method rests on the basic assumption that an ETL process is fundamentally a type of business process. This allows the well-established, standardized language of BPMN to be applied to the domain of data management.

2. Addressing the Standardization Gap

The approach directly addresses a major challenge in ETL:

No Standard Model: There is no single, universally agreed-upon standard model for defining ETL processes conceptually.

Tool-Specific Detail: Existing models are usually provided by specific ETL tools and are too detailed (implementation-specific) to be useful for high-level, conceptual planning.

By using BPMN, designers define a BPMN notation for ETL by leveraging standard BPMN constructs to represent the most common ETL tasks, creating a high-level, implementation-independent standard.

3. The Dual Nature of ETL Processes

A conceptual ETL process is viewed as a combination of two distinct but integrated types of processes:

Process Type Role and Focus

Control Processes Manage the coarse-grained groups of tasks, defining the overall sequence, scheduling, and error handling of the job (the 'when' and 'if').

Data Processes Detail how input data are transformed and how output data are ultimately produced (the 'what' of the data manipulation).

4. Two Kinds of Tasks in Modeling

This dual nature is mapped onto two corresponding kinds of tasks in the BPMN model:

Task Kind BPMN Focus Representation

Control Tasks Highlight the control procedures and sequence logic of BPMN (e.g., branching, merging). Represents a workflow where arrows indicate precedence (i.e., one activity must finish before the next begins).

Data Tasks Refer to the activities that directly manipulate data (e.g., aggregation, filtering, cleansing). Represents a data flow where arrows indicate the data 'flowing' along them from one transformation step to the next.

Conceptual ETL Design using BPMN

Basic assumption for using BPMN as conceptual model: ETL process is a type of business process
There is no standard model for defining ETL processes
Each tool provides its own model, too detailed to be conceptual
Using BPMN constructs we define the most common ETL tasks and define a BPMN notation for ETL
ETL process: A combination of control and data processes
- Control processes manage the coarse-grained groups of tasks
- Data processes detail how input data are transformed and output data are produced
Two kinds of tasks in ETL conceptual modeling
- Control tasks highlight the control procedures provided by BPMN. Represent a workflow (arrows represent the precedence between activities)
- Data tasks refer to the tasks that directly manipulate data during an ETL process. Represent a data flow (arrows represent data ‘flowing’ along them)

Control Tasks are the elements in a conceptual ETL model that define the workflow sequence and orchestration of the entire ETL process. They focus on when and in what order activities occur, rather than on the data manipulation itself.

Aspect Description

Primary Role To represent the workflow sequence or orchestration of the ETL process.

Independence They function independently of the data flow, focusing purely on process control and timing.

Notation Control tasks are modeled using standard BPMN constructs, such as activities, events, and crucial routing elements like gateways.

The Role of Gateways

The most important BPMN constructs for defining Control Tasks are Gateways . Gateways are used to control the sequence of activities in the ETL process, managing where the workflow splits, merges, or makes decisions.

The most used types of gateways in an ETL context are:

Exclusive Gateway (XOR):

Function: Models a decision point where the workflow must choose one and only one path out of several alternatives.

ETL Use Case: Evaluating a condition, such as checking if a source file exists or if an error threshold has been exceeded.

Parallel Gateway (AND):

Function (Splitting): Models a point where the workflow forks to execute multiple paths simultaneously (in parallel).

Function (Joining): Models a point where the workflow must wait for all parallel paths to complete before proceeding to the next step.

ETL Use Case: Initiating multiple independent loads (e.g., loading three separate dimension tables) at the same time, and then ensuring all three are finished before triggering the final fact table load.

In summary, Control Tasks provide the structure and operational logic of the ETL job, acting as the director that manages the flow and timing of all the subsequent data transformation and loading activities.

Control Tasks

Represent the workflow sequence or orchestration of the ETL process independently of the data flow
Control tasks are represented by means of BPMN constructs
For example, gateways are used to control the sequence of activities in an ETL process
The most used types of gateways in an ETL context are exclusive and parallel

Control Tasks

Data Tasks are the core elements in a conceptual ETL design that describe the actual data manipulation, operating at a lower level of abstraction than Control Tasks.

Role and Abstraction Level

Show Data Manipulation: A Data Task's primary purpose is to show how data are manipulated within an activity. They specify the logical operation being performed on the data itself.

Lower Abstraction: They operate at a lower abstraction level than control tasks. While control tasks define the workflow sequence (the "when"), data tasks define the transformation logic (the "what" and "how") applied to the records.

Types of Data Tasks

Data tasks represent all the activities typically carried out to prepare data for the data warehouse. They are categorized based on the number of input data flows they receive:

1. Unary Data Tasks

Definition: These tasks receive one input flow of data.

Input/Output: Reading data from a source or writing data to a target.

Data Conversion: Changing the data type of an attribute (e.g., text to numeric).

Transformation: Adding a column, removing duplicates, filtering rows, etc.

2. N-ary Data Tasks

Definition: These tasks receive as input more than one flow of data (where 'n' is greater than one).

Join: Combining two or more data flows based on a common key.

Union: Appending rows from two or more data flows.

Difference/Set Operations: Finding records present in one flow but not another.

Row Operations

Many Data Tasks involve Row Operations, which are fundamental transformations applied to data on a row-by-row basis. These are the most granular types of transformations.

Updating the value of a column (e.g., calculating a commission by multiplying two existing columns in the same row).

Conceptually: These represent the basic functions that transform individual records without necessarily relying on an entire dataset (like aggregation would).

Data Tasks

Show how data are manipulated within an activity
At lower abstraction level than control tasks
Represent activities typically carried out to manipulate data: input and output data, data conversion and transformation, for instance, change the data type of an attribute, add acolumn, remove duplicates, and so on
We denote these tasks unary data tasks since they receive one input flow
n-ary data tasks receive as input more than one flow (e.g., this is the case of union, join, dfference,...)
Row operations are the transformations applied to the source or target data on a row-by-row basis, e.g., updating the value of a column

Data Tasks

Rowset Data Tasks are a type of data manipulation activity in ETL (Extraction, Transformation, and Loading) design that operate on an entire set of rows (a dataset or rowset) rather than on individual rows in isolation.

Key Characteristics of Rowset Data Tasks

Operation on Sets: Unlike basic row operations (which affect one row at a time, like updating a column value), Rowset Data Tasks require processing the entire collection of input data to produce the output.

Context Dependency: The result for any single output row depends on the values of all the input rows in the set.

Example: Aggregation

The most common example is aggregation. For instance, calculating the total sales or the average price for a product category requires summing up or averaging the values across every row belonging to that group in the input set. You cannot calculate the final total by only looking at a single row.

Essentially, a Rowset Data Task represents any complex transformation that is performed at the group or table level, summarizing or deriving new information from a volume of data.

Rowset Data Tasks

Rowset operations deal with a set of rows, e.g., aggregation is a rowset operation

Rowset Data

Lookup Data Tasks are a specific type of ETL task used to verify the existence of a particular data value against a reference source, often represented conceptually as a quick check followed by a decision point.

The core function of a Lookup Data Task is to check if some value is present in a file (or a database table, dimension table, etc.).

In conceptual ETL design, a Lookup Task and the immediate decision that follows are typically modeled as a concise two-part process:

The Check (Lookup Task): The task attempts to find a match for a key value (e.g., a customer ID or product code) in a reference data set.

The Decision (Exclusive Gateway): The process flow immediately splits based on the result:

One path for "Match Found" (the value is present).

One path for "No Match Found" (the value is missing or new).

BPMN Shorthand (Conditional Flows)

To simplify the conceptual design diagram, the standard BPMN sequence of a Lookup Task followed by an Exclusive Gateway is often replaced by a shorthand notation:

The lookup activity is shown, but instead of connecting to a diamond-shaped gateway, its output is represented by 2 conditional flows.

These flows are simply arrows branching out from the task, each labeled with the specific branching condition (e.g., "Found" and "Not Found").

This shorthand makes the diagram cleaner and easier to read, focusing on the result of the lookup without the visual overhead of the standard gateway symbol.

Lookup Data Tasks

Lookup Data Tasks check if some value is present in a file. Immediately followed by an exclusive gateway with a branching condition. We use a shorthand replacing these two tasks by 2 conditional flows.

Rowset Data

Extraction, Transformation, and Loading

Outline

Extraction, Transformation, and Loading
Conceptual ETL Design using BPMN
Conceptual Design of the Northwind ETL

The Northwind Operational Database is a classic sample database originally developed by Microsoft to demonstrate relational database concepts, SQL, and various features of database management systems like Access and SQL Server. It models the operations of a fictional specialty food import/export company called "Northwind Traders."

The schema is an Entity-Relationship Diagram (ERD) built around the core business processes of sales, purchasing, and inventory.

The Northwind database typically consists of around 8 to 14 tables, with the central tables being:

1. Sales and Ordering

Customers: Stores information about Northwind Traders' clients.

Relationship: One-to-many with Orders (one customer can place many orders).

Orders: Contains high-level information about each sales transaction.

Relationship: One-to-many with Order Details (one order has many line items).

Relationship: Many-to-one with Employees (one employee handles many orders).

Relationship: Many-to-one with Shippers (one shipper can handle many orders).

Order Details: (Also sometimes called Order_Items) Stores the specific products, quantities, and prices for each order. This table forms the many-to-many relationship between Orders and Products.

2. Products and Inventory

Products: Stores information about the items Northwind sells.

Relationship: Many-to-one with Categories (many products belong to one category).

Relationship: Many-to-one with Suppliers (many products are supplied by one supplier).

Relationship: One-to-many with Order Details (one product can be in many order details).

Categories: Groups products (e.g., Beverages, Condiments, Seafood).

Suppliers: Stores information about the vendors who supply products to Northwind.

3. Personnel and Geography

Employees: Stores information about Northwind Traders' staff.

Relationship: Self-referencing one-to-many via the ReportsTo column, which models the organizational hierarchy (one manager/boss reports to, one employee manages many employees).

Shippers: Stores details about the companies used to ship orders.

Region, Territories, Employee Territories: These tables provide geographical data and a many-to-many relationship between Employees and the Territories they cover.

Schema of the Northwind Operational Database

Schema

he schema of the Northwind Data Warehouse is designed for Online Analytical Processing (OLAP) and is almost universally modeled using a Snowflake Schema. This design is optimized for fast, aggregated data retrieval for business intelligence and reporting, contrasting sharply with the normalized structure of the Northwind operational database (OLTP).

The Snoflake Schema breaks the data into two main components: a central Fact Table and surrounding Dimension Tables.

Fact Table: FactSales (or FactOrders)

The Fact Table sits at the center of the star and contains the measurable, numeric data (facts or measures) of the business process, along with foreign keys linking it to the dimension tables.

Column Type Origin in OLTP Description

Order_Fact_PK Surrogate Key (PK) Generated A new, simple primary key for the row.

OrderDate_SK Foreign Key (FK) Orders.OrderDate Links to the Date Dimension (Order Date role).

ShipDate_SK Foreign Key (FK) Orders.ShippedDate Links to the Date Dimension (Ship Date role).

Customer_SK Foreign Key (FK) Customers.CustomerID Links to the Customer Dimension.

Product_SK Foreign Key (FK) Products.ProductID Links to the Product Dimension.

Employee_SK Foreign Key (FK) Employees.EmployeeID Links to the Employee Dimension.

Quantity Measure Order Details.Quantity The count of units sold in the line item.

UnitPrice Measure Order Details.UnitPrice The price per unit at the time of sale.

Discount Measure Order Details.Discount The discount applied to the line item.

SalesAmount Derived Measure Calculated Quantity * UnitPrice * (1 - Discount).

The Granularity (the level of detail) of this fact table is typically the Order Line Item, meaning every row corresponds to a single product on a single order.

Dimension Tables

Dimension tables provide the context (who, what, where, when) for the facts and contain highly denormalized descriptive attributes. In a true star schema, these tables are joined directly to the Fact Table.

Dimension Table OLTP Source Tables Key Attributes (Examples) Role in Analysis

DimCustomer Customers CustomerName, City, Region, Country Filter sales by location or customer.

DimProduct Products, Categories, Suppliers ProductName, CategoryName, SupplierCompanyName Group sales by product type or vendor (denormalized to flatten the schema).

DimEmployee Employees EmployeeFullName, Title, ManagerName Analyze employee performance.

DimShipper Shippers ShipperCompanyName, Phone Analyze shipping costs and efficiency.

DimDate None (Generated) Date, Year, Month, DayOfWeek, IsHoliday Analyze sales trends over time (a role-playing dimension for Order Date and Ship Date).

Schema of the Northwind Data Warehouse

Schema

This information outlines three crucial conceptual components of the Extract, Transform, Load (ETL) process for building the Northwind Data Warehouse from its operational source. These components ensure the resulting analytical database is robust, consistent, and optimized for reporting.

1. Time Dimension Source and Scope

Rule: "File Time.xls contains data for loading the Time dimension, spanning the dates in table Orders of the operational database."

Explanation: The Date/Time Dimension (DimDate) is critical for time-based analysis (e.g., year-over-year sales comparisons). The ETL process does not create this dimension just by extracting the date field from the Orders table. Instead, it uses an external spreadsheet (Time.xls) because:

It needs to include descriptive calendar attributes (e.g., DayOfWeek, MonthName, IsHoliday, FiscalQuarter), which are not present in the operational database.

The ETL must ensure the dimension covers a continuous date range from the earliest OrderDate to the latest OrderDate (and often beyond) to prevent lookup failures for any sales transaction.

2. Conformed Geographic Dimensions (Shared Hierarchy) 📍

Rule: "Dimensions Customer and Supplier share the geographic hierarchy starting at the City level."

Explanation: This design principle employs Conformed Dimensions, a best practice in data warehousing that ensures analytical consistency.

The ETL process builds the DimCustomer and DimSupplier tables to include identical, standardized geographical fields (like City, State, Country, Continent).

This conformity allows analysts to filter or group by geography (e.g., Country) once, and that filter will correctly apply to sales facts (Customer geography) and purchasing facts (Supplier geography).

3. External Source for Hierarchy Enrichment 🗺️

Rule: "Data for the hierarchy State → Country → Continent loaded from Territories.xml."

Explanation: The raw operational data (OLTP) often lacks higher-level, standardized geographic groupings.

The Territories.xml file acts as a lookup table during the DimCustomer and DimSupplier ETL steps.

The ETL process extracts the lower-level geography (like City and Country) from the operational tables, then joins or looks up this information against Territories.xml to populate the full, enriched hierarchy (State and Continent), ensuring every dimension record has the complete reporting context.

Conceptual Design of the Northwind ETL: Data Sources

File Time.xls contains data for loading the Time dimension, spanning the dates in table Orders of the operational database
Dimensions Customer and Supplier share the geographic hierarchy starting at the City level
Data for the hierarchy State → Country → Continent loaded from Territories.xml

Data Sources

The data source, Cities.txt, serves two primary functions:

1. Source for City-to-State Mapping 🗺️

Source: External text file named Cities.txt.

Format: Tab-separated file with (at least) three fields. The key relationship is the mapping of City to State/Province.

Null Handling: The second field (State/Province) is explicitly set to null for cities in countries that don't use states or provinces (e.g., Singapore).

ETL Role: This file is crucial for data enrichment and standardization when building the geographic dimensions (DimCustomer, DimSupplier). When the ETL extracts a city name from the operational database, it uses Cities.txt to look up and supply the standardized State or Province name.

2. Conforming the Territories Dimension Data

Rule: The file is also used to identify to which state corresponds the city in the attribute TerritoryDescription of table Territories.

Context: The operational Territories table often contains vague geographical text in the TerritoryDescription field (e.g., "Westboro," "Boston").

ETL Action: The ETL must parse the TerritoryDescription field from the operational Territories table to extract the City name. It then uses the Cities.txt file to look up that city and correctly assign it to a State/Province.

Conceptual Rationale: This step ensures that all geographic dimensions—not just those derived from Customers and Suppliers—are conformed and link accurately to the final geographic hierarchy (State→Country→Continent), ensuring consistent reporting across all subject areas.

Conceptual Design of the Northwind ETL: Data Sources

File called Cities.txt identifies to which state or province a city belongs
Contains three fields separated by tabs and begins as shown below
For cities located in countries that do not have states (e.g. Singapore), second field is set to null
The file is also used to identify to which state corresponds the city in the attribute TerritoryDescription of table Territories

Data Sources

Conceptual Design of the Northwind ETL: Overall View

Overall View

This explains the ETL process for the Category Dimension table (DimCategory) and draws a comparison to the loading of the Time Dimension table (DimTime), highlighting the specific transformation rules for each.

1. Load of the Category Dimension (DimCategory)

Loading the DimCategory table is a straightforward, direct extraction and loading process known as a "direct move" or "Type 0 Slowly Changing Dimension" (SCD).

ETL Step Task Description

Input (Extract/Transform) Loads table Categories from the operational database (OLTP). The process reads all records and attributes from the Categories table in the normalized source system.

Insert (Load) Loads the table Category in the data warehouse (DW). The records are written directly into the denormalized DimCategory table.

Mapping CategoryID is mapped to CategoryKey attribute in the Category table. The original primary key (CategoryID) is renamed to become the dimension key (CategoryKey) in the data warehouse. Since Category is typically a static dimension, the operational key is often used as the dimensional key.

The key takeaway is that for Category, there's minimal transformation, primarily just a renaming of the primary key to follow dimensional modeling conventions (using Key or SK for the dimension's primary key

2. Comparison with Time Dimension Load (DimTime)

The explanation contrasts the simple DimCategory load with the loading of the DimTime dimension (which comes from the external Time.xls file). This comparison highlights two essential transformation steps required for the DimTime table that are not needed for DimCategory:

A. Data Type Conversion (Transformation)

The Time.xls file, being an external source, may contain dates or other temporal attributes stored as text or general Excel number formats.

Requirement: The ETL process must perform an explicit data type conversion (e.g., converting text representations of dates into a proper Date or DateTime data type) to ensure data quality and allow for proper date functions in the data warehouse.

B. Addition of TimeKey (Transformation/Loading)

The external Time.xls file likely contains the raw date and descriptive calendar columns.

Requirement: An additional column, TimeKey, is created and loaded. This is the Surrogate Key (SK) for the dimension. It is often created by transforming the date itself into an integer (e.g., the date 2026-09-26 becomes the integer 20260926) or by simply using a sequential integer.

Purpose: The TimeKey is the primary key of the DimTime table and the foreign key used in the FactSales table. Using an integer surrogate key is a standard practice for performance and for insulating the data warehouse from changes in the source system's date format.

Conceptual Design of the Northwind ETL

Load of the Category dimension table
- Input task loads table Categories from the operational database
- Insert task loads the table Category in the data warehouse, mapping CategoryID to CategoryKey attribute in the Category table
Loading the Time dimension table from an Excel file is similar, but includes a data type conversion, and an addition of the column TimeKey

Northwind ETL

This describes a crucial pre-loading step in the Northwind ETL process: establishing the high-level Geography Dimension Hierarchy before loading the dimensions that depend on it (like DimCustomer and DimSupplier).

The concept is that the data warehouse must enforce referential integrity from the bottom up—you can't load a city if its state hasn't been defined, and you can't define a state if its country hasn't been defined.

1. The Pre-requisite Control Task

Rule: "Loading the City level first requires loading the Geography hierarchy State → Country → Continent."

Conceptual Rationale (Dependency): The ETL process must define the higher-level geographic entities (State, Country, Continent) before it can process the low-level data (City).

When the ETL loads a customer's City, it will look up the corresponding State Key (Foreign Key).

The State table, in turn, needs the corresponding Country Key.

The Country table needs the corresponding Continent Key.

Therefore, the Continent and Country tables must be created and populated first to generate the necessary Surrogate Keys (SKs).

2. Load of the Continent Table

The Continent table is typically the first or one of the first dimension tables loaded in the geographic hierarchy due to this dependency.

ETL Step Task Description

Input (Extract) Data is extracted from Territories.xml. This file contains the highest level of the geographic structure. The ETL system extracts the list of unique continent names.

Transformation Generate Surrogate Key. A unique ContinentKey (Surrogate Key) is created for each distinct continent name (e.g., "North America" gets Key 1, "Europe" gets Key 2).

Load Insert into DimContinent (or Continent). The new records (ContinentKey and ContinentName) are inserted into the data warehouse table.

Control Function Control Task. This load is considered a control task because its successful completion (generation of $\text{ContinentKey}$s) is required to proceed with loading the Country table, which in turn is needed for the State table, and so on.

The keys generated by the Continent load are immediately used as foreign keys when loading the Country table, thus establishing the hierarchical link.

Conceptual Design of the Northwind ETL

Loading the City level first requires loading the Geography hierarchy State → Country → Continent
Associated control task

Northwind ETL

Load of the Continent table

Northwind ETL

References

A. VAISMAN, E. ZIMANYI, Data Warehouse Systems: Design and Implementation, Chapter 8 Extraction, Transformation, and Loading, Springer Verlag, 2014