Software // Information Management
05:55 PM

Kimball University: The Subsystems of ETL Revisited

These 34 subsystems cover the crucial extract, transform and load architecture components required in almost every dimensional data warehouse environment. Understanding the breadth of requirements is the first step to putting an effective architecture in place.


A data warehouse will not be a success until it can be relied upon as a dependable source for business decision making. To achieve this goal, the ETL system must constantly work toward fulfilling three criteria:

Reliability. The ETL processes must run consistently to provide data on a timely basis that is trustworthy at any level of detail.

Availability. The data warehouse must meet its service level agreements. The warehouse should be up and available as promised.

Manageability. A successful data warehouse is never done; it constantly grows and changes along with the business. Thus, ETL processes need to evolve gracefully as well.

The ETL management subsystems are the key architectural components that help achieve the goals of reliability, availability and manageability. Operating and maintaining a data warehouse in a professional manner is not much different than other systems operations: follow standard best practices, plan for disaster and practice (see related article, "Don't Forget the Owner's Manual"). Many of you will be very familiar with the following requisite management subsystems:

Job Scheduler (subsystem 22) — Reliably manages the ETL execution strategy, including the relationships and dependencies between ETL jobs.

Backup System (subsystem 23) — Backs up the ETL environment for recovery, restart and archival purposes.

Recovery and Restart (subsystem 24) — Processes for recovering the ETL environment or restarting a process in the event of failure.

Version Control (subsystem 25) — Takes snapshots for archiving and recovering all the logic and metadata of the ETL pipeline.

Version Migration (subsystem 26) — Migrates a complete version of the ETL pipeline from development into test and finally into production.

Workflow Monitor (subsystem 27) — Ensures that the ETL processes are operating efficiently and that the warehouse is being loaded on a consistently timely basis.

Sorting (subsystem 28) — Serves the fundamental, high-performance ETL processing role.

Lineage and Dependency (subsystem 29) — Identifies the source of a data element and all intermediate locations and transformations for that data element or, conversely, start with a specific data element in a source table and reveals all activities performed on that data element.

Problem Escalation (subsystem 30) — Support structure that elevates ETL problems to appropriate levels for resolution.

Paralleling and Pipelining (subsystem 31) — Enables the ETL system to automatically leverage multiple processors or grid computing resources to deliver within time constraints.

Security (subsystem 32) — Ensures authorized access to (and a historical record of access to) all ETL data and metadata by individual and role.

Compliance Manager (subsystem 33) — Supports the organization’s compliance requirements typically through maintaining the data’s chain of custody and tracking who had authorized access to the data.

Metadata Repository (subsystem 34) — Captures ETL metadata including the process metadata, technical metadata and business metadata, which make up much of the metadata of the total DW/BI environment.


As you may now better appreciate, building an ETL system is unusually challenging. The required ETL architecture requires a host of subsystems necessary to meet the demanding requirements placed on the data warehouse. To succeed, carefully consider each of these 34 subsystems. You must understand the breadth of requirements and then place an appropriate and effective architecture in place. ETL is more than just extract, transform and load; it’s a host of complex and important tasks.

Bob Becker is a member of Kimball Group. He has focused on dimensional data warehouse consulting and education since 1989. Contact him at

3 of 3
Comment  | 
Print  | 
More Insights
The Agile Archive
The Agile Archive
When it comes to managing data, donít look at backup and archiving systems as burdens and cost centers. A well-designed archive can enhance data protection and restores, ease search and e-discovery efforts, and save money by intelligently moving data from expensive primary storage systems.
Register for InformationWeek Newsletters
White Papers
Current Issue
InformationWeek Elite 100 Digital Issue, April 2015
The 27th annual ranking of the leading US users of business technology
Twitter Feed
InformationWeek Radio
Archived InformationWeek Radio
Join us for a roundup of the top stories on for the week of April 19, 2015.
Sponsored Live Streaming Video
Everything You've Been Told About Mobility Is Wrong
Attend this video symposium with Sean Wisdom, Global Director of Mobility Solutions, and learn about how you can harness powerful new products to mobilize your business potential.