CHAPTER 2
The back room area of the data warehouse has frequently been called the staging area. Staging in this context means writing to disk and, at a minimum, we recommend staging data at the four major checkpoints of the ETL data flow. The ETL team will need a number of different data structures to meet all the legitimate staging needs, and thus the purpose of this chapter is to describe all the types of data structures you are likely to need.
This chapter does not describe all the source data types you must extract from. We leave that to Chapter 3!
PROCESS CHECK Planning & Design:
Requirements/Realities → Architecture → Implementation → Test/Release
Data Flow: Extract → Clean → Conform → Deliver
We also try to step back from the details of the data structures to recommend general design principles for the staging area, including planning and design standards and an introduction to the metadata needed to support staging. Metadata is a big topic, and we gather a number of more specific metadata designs in Chapter 4 in the cleaning and conforming steps. Also, we tie all the metadata topics together toward the end of the book in Chapter 9.
To Stage or Not to Stage
The decision to store data in a physical staging area versus processing it in memory is ultimately the choice of the ETL architect. The ability to develop efficient ETL processes is partly dependent on being able to determine the right balance between physical input and output (I/O) and in-memory processing.
The challenge of achieving this delicate balance between writing data to staging tables and keeping it in memory during the ETL process is a task that must be reckoned with in order to create optimal processes. The issue with determining whether to stage your data or not depends on two conflicting objectives:
Getting the data from the originating source to the ultimate target as fast as possible
Having the ability to recover from failure without restarting from the beginning of the process
The decision to stage data varies depending on your environment and business requirements. If you plan to do all of your ETL data processing in memory, keep in mind that every data warehouse, regardless of its architecture or environment, includes a staging area in some form or another. Consider the following reasons for staging data before it is loaded into the data warehouse:
Recoverability. In most enterprise environments, it’s a good practice to stage the data as soon as it has been extracted from the source system and then again immediately after each of the major transformation steps, assuming that for a particular table the transformation steps are significant. These staging tables (in a database or file system) serve as recovery points. By implementing these tables, the process won’t have to intrude on the source system again if the transformations fail. Also, the process won’t have to transform the data again if the load process fails. When staging data purely for recovery purposes, the data should be stored in a sequential file on the file system rather than in a database. Staging for recoverability is especially important when extracting from operational systems that overwrite their own data.
Backup. Quite often, massive volume prevents the data warehouse from being reliably backed up at the database level. We’ve witnessed catastrophes that might have been avoided if only the load files were saved, compressed, and archived. If your staging tables are on the file system, they can easily be compressed into a very small footprint and saved on your network. Then if you ever need to reload the data warehouse, you can simply uncompress the load files and reload them.
Auditing. Many times the data lineage between the source and target is lost in the ETL code. When it comes time to audit the ETL process, having staged data makes auditing between different portions of the ETL processes much more straightforward because auditors (or programmers) can simply compare the original input file with the logical transformation rules against the output file. This staged data is especially useful when the source system overwrites its history. When questions about the integrity of the information in the data warehouse surface days or even weeks after an event has occurred, revealing the staged extract data from the period of time in question can restore the trustworthiness of the data warehouse.
Once you’ve decided to stage at least some of the data, you must settle on the appropriate architecture of your staging area. As is the case with any other database, if the data-staging area is not planned carefully, it will fail. Designing the data-staging area properly is more important than designing the usual applications because of the sheer volume the data-staging area accumulates (sometimes larger than the data warehouse itself). The next section discusses staging-area design considerations and options.
Designing the Staging Area
The staging area stores data on its way to the final presentation area of the data warehouse. Sometimes, data in the staging area is preserved to support functionality that requires history, while other times data is deleted with each process. When history is maintained in the staging area, it is often referred to as a persistent staging area. When data is deleted with each load, the area is considered transient. It’s perfectly valid for the data-staging area to be a hybrid, composed of a mixture of persistent and transient staging tables.
Make sure you give serious thought to the various roles that staging can play in your overall data warehouse operations. There is more to staging than just building temp files to support the execution of the next job. A given staging file can also be used for restarting the job flow if a serious problem develops downstream, and the staging file can be a form of audit or proof that the data had specific content when it was processed.
Regardless of the persistence of the data in the staging area, you must adhere to some basic rules when the staging area is designed and deployed. The following rules all have the same underlying premise: If you are not on the ETL team, keep out! You must establish and practice the following rules for your data warehouse project to be successful:
The data-staging area must be owned by the ETL team. The data-staging area, and all of the data within it, is off limits to anyone other than the ETL team. The data-staging area is not designed for presentation. There are no indexes or aggregations to support querying in the staging area. There are no service-level agreements for data access or consistency in the staging area. All of these data access requirements are handled in the presentation area.
Users are not allowed in the staging area for any reason. Data in the staging area must be considered a construction site area. Allowing unauthorized personnel into the area can cause injuries. Curious users allowed in the area often misuse the data and reduce the perceived integrity of the data warehouse.
Reports cannot access data from the staging area. The data-staging area is a work site. Tables are added, dropped, or modified by the ETL team without notifying the user community. This concept does not mean that the area is a free-for-all with tables being added, dropped, and modified by programmers at will. However, it does mean that the area is intended to be a workbench, not a display case. The area is a controlled environment, meaning that modifications to tables in the production-staging area must go through the same lifecycle of tables in the data warehouse presentation layer. However, unlike changes to production data warehouse tables, data-staging tables can be changed without notifying users, breaking reports that might be using the altered table. Accordingly, do not allow any reports to point to the staging area even when such pointing is temporary.
Only ETL processes can write to and read from the staging area. Every data warehouse requires data sets that don’t have a conventional outside source, such as a table of data-quality status types. When the data warehouse requires data that does not exist in any existing external database environment, nevertheless it must come into the data-staging area like other data. Keep in mind that the data-staging area is not a transaction environment and that you should not allow data to be manually entered into it. If manual tables must be maintained, an application should be developed outside of the data-staging area, and the resulting data should be provided to the ETL team and incorporated into the staging area via an ETL process.
The ETL group owns the data-staging area. That means that the ETL architect designs the tables within it and decides whether a table belongs in the database or, based on the requirements of its respective ETL processes, is best suited for the file system. When the staging area is initially set up, the ETL architect must supply the database administrator (DBA) team and OS administrators with an overall data storage measure of the staging area so they can estimate the space allocations and parameter settings for the staging database, file systems, and directory structures. Figure 2.1 shows a sample staging area volumetric worksheet, focusing on the final delivery
Figure 2.1 Staging tables volumetric worksheet.
tables at the end of the ETL data flow. A full volumetric tracking system would have similar worksheets for copies of source data and the staging files that followed the cleaning and conforming steps.
The volumetric worksheet lists each table in the staging area with the following information:
Table Name. The name of the table or file in the staging area. There is one row in the worksheet for each staging table.
Update Strategy. This field indicates how the table is maintained. If it is a persistent staging table, it will have data appended, updated, and perhaps deleted. Transient staging tables are truncated and reloaded with each process.
Load Frequency. Reveals how often the table is loaded or changed by the ETL process. Quite often, it is daily. It can be weekly, monthly, or any interval of time. In a real-time environment, tables in the staging area can be updated continuously.
ETL Job(s). Staging tables are populated or updated via ETL jobs. The ETL job is the job or program that affects the staging table or file. When many jobs affect a single staging table, list all of the jobs in this section of the worksheet.
Initial Row Count. The ETL team must estimate how many rows each table in the staging area initially contains. The initial row count is usually based on the rows in the source and/or target tables.
Average Row Length. For size-estimation purposes, you must supply the DBA with the average row length in each staging table. In an Oracle environment, we create the table in a development environment, run statistics, and gather this information from the ALL_TABLES table. For instance, in Oracle, the DBMS_STATS package can be used to populate the appropriate statistics columns.
Grows With. Even though tables are updated on a scheduled interval, they don’t necessarily grow each time they are touched. The Grows With field is based on business rules. You must define when each table in the staging area grows. For example, a status table grows each time a new status is added. Even though the table is touched daily to look for changes, the addition of new statuses is quite rare.
Expected Monthly Rows. This estimate is based on history and business rules. Anticipated growth is required for the DBA to allocate appropriate space to the table. The monthly row count is a building block to calculate how many bytes the table grows each month.
Expected Monthly Bytes. Expected Monthly Bytes is a calculation of Average Row Length times Expected Monthly Rows.
Initial Table Size. The initial table size is usually represented in bytes or megabytes. It is a calculation of Average Row Length times Initial Row Count.
Table Size 6 Months. An estimation of table sizes after six months of activity helps the DBA team to estimate how the staging database or file system grows. This measurement is usually represented in megabytes. It is a calculation of (Average Row Length times Initial Row Count) + (Average Row Length times Expected Monthly Rows times 6) / 1,048,576).
So far, the details discussed in this section are mostly applicable to tables within a database management system (DBMS). The staging area normally consists of both DBMS tables and flat text files on the file system. Flat files are especially important when using a dedicated ETL tool. Most of the tools utilize an area in the file system for placing data down to optimize its workflow. In many cases, you need to stage your data outside of a DBMS in flat files for fast sequential processing. You can also use the volumetric worksheet for file-system planning. A general practice is to set the files down in a development area and record the space they occupy to provide the statistics to the appropriate personnel for official space allocation.
The next sections of this chapter provide information to help you select the appropriate architecture for your staging tables.
The ETL architect needs to arrange for the allocation and configuration of data files that reside on the file system as part of the data-staging area to support the ETL process. ETL vendors whose tools use the file system should recommend appropriate space allocation and file-system configuration settings for optimal performance and scalability. For nonvendor data files explicitly created by the ETL process, use the standard volumetric worksheet.
Data Structures in the ETL System
In this section, we describe the important types of data structures you are likely to need in your ETL system.
Flat Files
In many cases, you won’t need to stage your data within the confines of a DBMS. If you are not using a dedicated ETL tool and are doing all of the ETL tasks with SQL in your database, you need to create DBMS table structures to store all of your staging data. However, if you have an ETL tool or are utilizing shell scripts or scripting programs such as Perl, VBScript, or JavaScript, which can manipulate text files, you can store staging data right in your file system as simple text files.
When data is stored in columns and rows within a file on your file system to emulate a database table, it is referred to as a flat or sequential file. If your operating system is any flavor of UNIX or Windows, data in your flat files is in standardized character code known as American Standard Code for Information Interchange (ASCII). ASCII flat files can be processed and manipulated by ETL tools or scripting languages just as if they were database tables—and in certain cases much faster!
A DBMS requires overhead to maintain metadata about the data being processed, which in simple cases is not really needed in the data-staging environment. It has been the conventional wisdom that sorting, merging, deleting, replacing, and many other data-migration functions are much faster when they are performed outside the DBMS. Many utility programs are dedicated to text-file manipulation. Keep in mind when performing flat-file manipulations with scripting languages that you may have an obligation to separately advise your metadata tracking tables of the transformations you are making. If the metadata tracking (say, for compliance purposes) is as important as the transformations themselves, you should think instead about handling these operations through a dedicated ETL tool that can automatically supply the metadata context.
Arguments in favor of relational tables.
It is always faster to WRITE to a flat file as long you are truncating or inserting. There is no real concept of UPDATING existing records of a flat file efficiently, which argues against using a flat file for a persistent staging table. Querying and other random access lookups are not supported well by operating system utilities or vendor tools. Finally, flat files cannot be indexed for fast lookups.
When you READ from a staging table in the ETL system, database storage is superior when filtering is anticipated and when joins between tables on the same system are performed. Although dedicated sort packages used to be the clear performance winners, relational databases have made significant progress recently in leveling the playing field. Being able to work in SQL and get automatic database parallelism for free is a very elegant approach.
The exact platform to store your data-staging tables depends on many variables, including corporate standards and practices. However, we’ve observed that it’s most practical to use flat files over database tables for portions of the ETL process when the fundamental purpose of the process is one of the following:
Staging source data for safekeeping and recovery. When you extract data from its originating data source, you must quickly enter the system, select exactly what you need, and exit. If the ETL process fails after the data is extracted, you must be able to start without penetrating the source system again. The best approach to restart a failed process without constantly penetrating the source system is to dump the extracted data into a flat file for safe keeping. If the process fails at any point after the data has been placed in flat file, the process can easily restart by picking up the already extracted data from the flat file in the staging area. Further details on utilizing flat files to recover from ETL process failures are discussed in Chapter 8.
Sorting data. Sorting is a prerequisite to virtually every data integration task. Whether you are aggregating, validating, or doing look-ups, presorting data optimizes performance. Sorting data in the file system may be more efficient than selecting data from a database table with an ORDER BY clause. Since the nature of ETL is to integrate disparate data, merging data efficiently is a top priority that requires intensive sorting. A huge fraction of your ETL system’s processor cycles will go to sorting. You should carefully simulate the biggest sorting tasks both in your DBMS and with a dedicated sort package, if that is possible, to decide whether to base your sorting on the DBMS or a dedicated package.
Filtering. Suppose you need to filter on an attribute that is not indexed on the source database. Instead of forcing the source system to create indexes that may hinder transaction processing, it might be faster to extract the whole data set into a flat file and grep (A UNIX command for filtering simple data files) only the rows that meet your requirements. Another benefit of having the ability to filter data without using a database is realized when data does not originate from a database but from data files. A common example of this is Web logs. While processing clickstream data, we use a grep statement with the -v switch to select all the rows that do not contain certain values such as.gif or.jpg so hits that are serving image files are excluded from the data warehouse. Filtering flat files is tremendously more efficient than inserting all data into a database table, indexing the table, and then applying a WHERE clause either to delete or select the data into another table.
Replacing/substituting text strings. The operating system can blaze through a text file, translating any string to any other string amazingly fast using the tr command. Doing substring searching and replacing data in a database can require nested scalar functions and update statements. This type of sequential file processing is much faster at the file-system level than it is with a database.
Aggregation. Aggregation, discussed in Chapter 6, must be supported in two ways: in the regular ETL data flow before loading into a database and in the database itself when aggregating data that can only be requested with database filtering. Outside the database, it makes most sense to use a dedicated sort package. Inside the database, it almost always makes most sense to stay with the database sort capability, although occasionally it can pay to dump large volumes of data out of the database and use the sort package.
Referencing source data. In normalized transaction systems, it is common to have a single reference table support many other tables. A generic Status table, for example, can support order statuses, shipping statuses, and payment statuses. Instead of querying the same table in the source system over and over, it’s more efficient to extract the reference table and stage it in the staging area once. From there, you can look up data with your ETL tool. Most ETL tools can read a look-up table into memory and store it there for the life of the process. Accessing reference tables stored in memory is blazingly fast. Moreover, utilizing staged reference data keeps the queries that hit the source system simpler and more efficient because many of the table joins can be omitted.
XML Data Sets
At this point, XML data sets are not generally used for persistent staging in ETL systems. Rather, they are a very common format for both input to and output from the ETL system. It is likely that XML data sets will indeed become a persistent data-storage format in the ETL system and in the data warehouse queryable tables, but the hierarchical capabilities of XML will have to be integrated more deeply with relational databases before that will be common.
XML is a language for data communication. Superficially, XML takes the form of plain text documents containing both data and metadata but no formatting information. XML is expressed with much the same notation as HTML but departs from the architecture of an HTML document. HTML, by contrast, contains data and formatting information but no metadata.
Differences between XML and HTML are crucial to understanding how XML affects data warehousing. XML metadata consists of tags unambiguously identifying each item in an XML document. For instance, an invoice coded in XML contains sequences such as:
Here Customer is an XML element whose definition has been established between the sending and receiving parties before the invoice is transmitted. The customer element has been defined to contain a number of XML attributes, including name, address, city, and possibly others.
XML has extensive capability for declaring hierarchical structures, such as complex forms with nested repeating subfields. These hierarchical structures do not directly map into standard two-dimensional relational tables with rows and columns. When the data warehouse receives an XML data set, there may be a complex extraction process to transfer the data permanently into a relational data warehouse. There has been some discussion of extending relational databases to provide native support for XML’s hierarchical structures, but this would amount to a substantial extension of the syntax and semantics of SQL based relational databases, which as of this writing has not happened.
Setting aside the issues of complex hierarchical structures, XML is today an extremely effective medium for moving data between otherwise incompatible systems, since XML (and the XML Schemas that follow) provide enough information to do a full CREATE TABLE statement in a relational database and then populate this table with data with the right column types. XML defines a universal language for data sharing. That is its strength. The downside of XML for large-volume data transfer is the overhead of the XML document structure itself. If you are transmitting millions of similar and predictable records, you should seek a more efficient file structure than XML for data transfer.
DTDs, XML Schemas, and XSLT
In XML, the standard way for two parties to define a set of possible tags is by exchanging a special document known as a Document Type Definition (DTD). The DTD declaration for our customer example could be cast as:
plus similar lines for Address, City, State, and Postalcode.
Here the question marks after City, State, and Postalcode indicate that these fields are optional. The #PCDATA declaration indicates that Name is an unformatted text string.
Notice that the DTD contains somewhat less information than an SQL CREATE TABLE statement. In particular, there is no field length.
DTDs have until now been the basis for setting up a metadata understanding between two parties exchanging XML. A number of industry groups have been defining standard DTDs for their subject areas. But as data warehousers, we don’t get enough from DTDs to build a relational table. To rectify this problem, the W3C standards organization has defined an industrial-strength successor to DTDs known as XML Schemas. XML Schemas contain much more database-oriented information about data types and how the XML elements relate to each other or, in other words, how tables can be joined.
When an XML Schema has been agreed upon and XML content has been received, the information content is rendered via another specification called Extensible Stylesheet Language Transformations (XSLT). Actually, XSLT is a general mechanism for translating one XML document into another XML document, but its most visible use is for turning XML into HTML for final on-screen presentation.
Relational Tables
Staging data can optionally be stored within the confines of a relational DBMS. Using database tables is most appropriate especially when you don’t have a dedicated ETL tool. Using a database to store staging tables has several advantages:
Apparent metadata. One of the main drawbacks of using flat files is that they lack apparent metadata. By storing data in a relational table, the DBMS maintains technical metadata automatically, and business metadata can easily be attached to the table within the DBMS. Information such as column names, data types and lengths, and cardinality is inherent to the database system. Table and column business descriptions are elements commonly added to DBMS data catalogs.
Relational abilities. Enforcing data or referential integrity among entities is easy to accomplish in a relational environment. If you are receiving data from nonrelational systems, it might make sense to stage data in a normalized model before transforming into a dimensional model.
Open repository. Once data is placed in a DBMS, the data can easily be accessed (assuming permission is granted) by any SQL-compliant tool. Access to the data is crucial during quality-assurance testing and auditing.
DBA support. In many corporate environments, the DBA group is responsible only for data inside the DBMS. Data outside of the database, in the file system, is usually not looked after. Space allocation, backup and recovery, archiving, and security are tasks that the ETL team must coordinate when the staging data is not in a DBMS.
SQL interface. You will encounter many times when you need to write SQL to manipulate data and get it in just the right format. Most know that SQL is the standard language to speak to data—it is easy to write and is powerful. SQL is probably the most widely known programming language in the IT environment. Most database systems come packed with robust SQL functions that save numerous hours of manual coding. Oracle, for example, has overloaded functions such as to_char() that can take virtually any data type and convert it into a character string in a variety of formats. Besides enforcing referential integrity, having the ability to use native SQL is the primary reason to store staging data in a database environment.
Independent DBMS Working Tables
If you decide to store your staging data in a DBMS, you have several architecture options when you are modeling the data-staging schema. Designing tables in the staging area can be even more challenging than designing transaction or dimensional models. Remember, transaction databases are designed to get data in; dimensional designs get data out. Staging-area designs must do both. Therefore, it’s not uncommon to see a mixture of data architectures in the staging area.
To justify the use of independent staging tables, we’ll use one of our favorite aphorisms: Keep it simple. Independent tables get their name because they don’t have dependencies on any other tables in the database. In the transactional environment, these tables are known as orphans because they don’t have relationships with other tables in the schema. Because independent tables don’t have relationships, they are prime candidates to store outside of a relational database.
Most of the time, the reason you create a staging table is to set the data down so you can again manipulate it using SQL or a scripting language. In many cases, especially smaller data warehouse projects, independent tables are all you need in the staging area.
Just because independent tables are not necessarily normalized, they must not be treated like dump files. Dump files are typically created arbitrarily without concern about disk space or query performance. Each field of an independent file or table must have a purpose and a logical definition. Superfluous columns are omitted from any independent table design. For database tables, a proper index plan must be established and implemented on all independent tables. Since all of the processes that hit each staging table are known because only ETL processes use these tables, there is less need for bitmapped indexes here than in the presentation area, which is dominated by end user tools and ad-hoc requests. You will find more for use for single column and compound column b-tree indexes in your ETL system.
Third Normal Form Entity/Relation Models
There are arguments that the data-staging area is perhaps the central repository of all the enterprise data that eventually gets loaded into the data warehouse. However, calling the data-staging area an enterprise-wide central repository is a misnomer that makes data architects believe that the area must be fully normalized. After all, what rightful data architect leaves any enterprise data vulnerable to the possibility of redundancy? To build on the analogy of the restaurant kitchen that we describe earlier in this chapter, imagine that certain foods—fish, for example—must be carefully selected, cleaned, portioned, filleted, and sautéed before they are finally served. Now suppose you also serve ice cream. You don’t process ice cream the same way you process the fish before serving it—ice cream is essentially scooped and served. Forcing all data from every source system to go through the same normalization process—fitting it into a third normal form-data model—is like preparing ice cream as you would fish.
We rarely model the staging area in third normal form. We have had cases where data elements of a hierarchy have come from disparate data sources at different levels of granularity, including some external data from nonrelational sources. In those cases, it makes sense to model the data in a way that removes redundancy and enforces integrity before it is loaded it into the dimensional data model. But this ideally takes the form of focusing on isolated problem dimensions that need to be turned inside out to make sure that initially dirty data has been cleaned correctly. Remember that the main result of normalization is to enforce specific many-to-1 relationships. The typical annotations on an entity-relation diagram are not enforced or interpreted, except by humans examining the graphical depiction of the schema. Consider modeling decisions on a case-by-case basis, normalizing entities only as needed.
Don’t assume that the data-staging area must be normalized. Remember two of the goals for designing your ETL processes we describe at the beginning of this chapter: Make them fast and make them recoverable. If you have stages in your process that are not physically manipulating data and do not enable speed or recoverability, they ought to be removed.
Nonrelational Data Sources
A common reason for creating a dedicated staging environment is to integrate nonrelational data. Your data-integration tasks seem much less challenging when all data is under one roof (DBMS). Integrating heterogeneous data sources is a challenge that ETL developers must constantly confront as the data warehouse expands its scope to include more and more subject areas.
In enterprise data warehouse projects, many of the data sources are from nonrelational data sources or relational data sources that are not necessarily related to each other. Nonrelational data sources can include COBOL copy books, VSAM files, flat files, spreadsheets, and so on.
Bringing all of the disparate data sources into a single DBMS is a common practice, but is it really necessary? The power of ETL tools in handling heterogeneous data minimizes the need to store all of the necessary data in a single database. Figure 2.2 illustrates how a platform-neutral ETL tool can integrate many heterogeneous data sources right from their native data stores, integrating the data on the fly to migrate it to the data warehouse. Notice that the ETL tool is linked to the physical staging database and external files and can optionally set the transient data down for interim manipulation if required.
Figure 2.2 ETL tools natively integrate heterogeneous sources optionally using the data-staging area.
Integrating nonrelational data sources often requires some integrity checking. Data-integrity assurance is not free. It often requires real estate in the data-staging area and customized ETL processing to enforce business rules that would otherwise be enforced by the nature of a source system relational database. Relational means that tables have relationships—usually parent-to-child—enforced by the database. For instance, if an orders table has a status column, a status cannot be entered into that table without it preexisting in a separate status table. In this scenario, the status table is the parent of its associated children in the orders table. A parent record cannot be deleted unless all of its children are deleted as well; otherwise, the children become orphans. An orphan is any child record without a parent. Equivalently, we say that the primary key for an order cannot be deleted if there are foreign keys in the status table referring to that order. Orphans are a sign of referential integrity failure.
Nonrelational data sources do not enforce referential integrity. Nonrelational systems are essentially a collection of independent tables. Most often in legacy transaction systems, parent-child relationships are enforced only through the front-end application. Unfortunately, after years of operation, any data integrity that is not enforced within the database is inevitably put to risk by scripts or any other data manipulation performed outside the front-end application. It is practically guaranteed that nonrelational data sources include some data-quality issues.
As is not the case with transaction systems, it’s a good practice to have integrity checks in the ETL process rather than in the database when designing your data staging area. The difference is due to the fact that transaction systems expect data to be entered correctly. Moreover, a human being who enters erroneous data can react to an error thrown by the database and reenter the data correctly. Conversely, the ETL process must know how to handle data anomalies in a more automatic way. The process cannot simply reject all data- integrity failures because there may be no one to reenter the data correctly in a timely manner. Instead, you need to establish business rules for different data-quality failure scenarios and implement them in the ETL process. When erroneous data is passed through the process, sometimes you want to transform the data on the fly; load the data as is; load the data with an associated code or description describing the impact or condition of the data; or if the data is unacceptable, reject the data completely and put it into a reject file for investigation.
Don’t overuse the reject file! Reject files are notorious for being dumping grounds for data we’ll deal with later. When records wind up in the reject file, unless they are processed before the next major load step is allowed to run to completion, the data warehouse and the production system are out of sync.
Basic database referential integrity enforcement is not sufficient to enforce each of these scenarios. Hand-coded logic in the ETL process is almost always required to successfully integrate nonrelational data sources.
Dimensional Data Models: The Handoff from the Back Room to the Front Room
Dimensional data structures are the target of the ETL processes, and these tables sit at the boundary between the back room and the front room. In many cases, the dimensional tables will be the final physical-staging step before transferring the tables to the end user environments.
Dimensional data models are by far the most popular data structures for end user querying and analysis. They are simple to create, they are extremely stable in the presence of changing data environments, they are intuitively understandable by end users, and they are the fastest data structure for general relational database querying. Dimensional models are also the foundation for constructing all forms of OLAP cubes, since an OLAP cube is really just a high-performance dimensional model implemented on special-purpose software.
This section is a brief introduction to the main table types in a dimension model.
The other books in the Toolkit series discuss dimensional models in great detail and provide guidance and motivation for building them in many different business environments. We assume in this book that you have studied the motivations for dimensional models (or that you don’t need motivation because you have been handed a specification for a particular dimensional design!). So in this section we introduce the basic physical structure of dimensional models without any of the usual business-content justification. In section 2 of this book we exhaustively illustrate all the known dimensional model variations and discuss the ETL system that feeds each of these structures.
Fact Tables
Dimensional models are built around measurement processes. A measurement is a real-world observation of a previously unknown value. Measurements are overwhelmingly numeric, and most measurements can be repeated over time, creating a time series.
A single measurement creates a single fact table record. Conversely, a single fact table record corresponds to a specific measurement event. Obviously, the observed measurement is stored in the fact table record. But we also store the context of the measurement in the same record. While we might be tempted to store this context in many verbose fields directly in the fact table record, we systematically normalize the contextual attributes out of the fact table by creating a number of dimension tables that can be viewed informally as clumps of context.
So, for example, if the measurement is the amount of an insurance premium booked by an insurance company on a particular policy, a particular customer, a particular agent, a particular coverage (such as collision damage), an insured item (perhaps an automobile), a specific transaction type (such as establish premium), on a certain effective date, typical dimensions attached to the fact record would be Policy, Customer, Agent, Coverage, Item, Transaction Type and Effective Date. See Figure 2.3, which illustrates this example.
Figure 2.3 A dimensional model for an insurance policy transaction.
The grain of a fact table is the definition of what constitutes a unique fact table record. In the dimensional-modeling world, the grain is always declared at the beginning of the design in business terms, not in database terms. The grain of our insurance example could be insurance policy transaction. Then, later in the design process, when the available dimensions are understood, the grain can be formally stated in terms of the key of the fact table. This key declaration will include some but usually not all of the foreign key references to the dimensions attached to the fact table. We assume that the key to our insurance fact table is Policy X Transaction Type X Time.
The structure and content of a dimensional model depend only on the physics of the measurement process itself.
Dimension Tables
The dimensional model does not anticipate or depend upon the intended query uses. It is a superbly flexible, symmetric framework suitable for all classes of queries. But there is still some room for designer discretion. Many of the dimensions for a particular fact table will be referenced directly in the original data source. But the data warehouse team can add dimensions that come from other sources, as long as they are single valued at the time of the measurement event. For instance, in our insurance example, a marketing-oriented promotion dimension might be added if that data were available. One of the great strengths of dimensional models is their ability to gracefully add dimensional context that is valid in the context of the measurement event.
Similarly, the best dimensions are verbose descriptions of the dimensional entities. So the Customer dimension in our example should have many descriptive fields. We call these descriptive fields dimensional attributes. Fortunately, all dimensional designs allow attributes to be added to dimensions incrementally over the life of the warehouse. The responsibility of the data warehouse architect is to identify these opportunities for additional attributes and to request that the ETL team add them to the physical schema.
Dimensional attributes are mostly textual or are numbers that take on discrete values. Dimension tables should always be built with a single primary key field that is a simple meaningless integer assigned by the ETL process. These keys are called surrogate keys. The many advantages of surrogate keys are described in the other Toolkit books. In this book, we describe how surrogate keys are created and administered in a number of important situations.
The primary surrogate keys in each dimension are paired with corresponding foreign keys in the fact table. When this primary-to-foreign key relationship is adhered to, we say that the tables obey referential integrity. Referential integrity is a constant requirement in all dimensional models. Failure to maintain referential integrity means that some fact table records are orphans that cannot be retrieved through constraints on the affected dimensions.
Atomic and Aggregate Fact Tables
You know that a dimensional data model is the best format for data to support user queries. You might at times need to utilize certain elements at an atomic level so the data can be presented at a higher level. However, you need to store the atomic-level facts to produce the precisely constrained requests required by the users. Often, business users don’t want to analyze transaction-level facts because the cardinality of each dimension is so extensive that any atomic-level report would be pages long—making it humanly impossible to examine. However, you need to store the atomic-level facts to produce the periodic snapshot facts required by the users. When the time comes that the users request atomic-level data, you can simply migrate it from the staging area to the presentation layer.
It’s good practice to partition fact tables stored in the staging area because its resulting aggregates will most likely be based on a specific period—perhaps monthly or quarterly. Creating partitions alleviates the database from scanning the entire table and enables it to go directly to the subsection that contains the data it needs for that period to create the aggregate. Partitioning also reduces the burden of pruning or archiving old data. Partitioned tables can simply drop the portion of a table that contains the old data.
Dimensionally designed tables in the staging area are in many cases required for populating on-line analytic processing (OLAP) cubes. Or you may implement a hybrid structure where you have the large atomic data layer in a dimensional RDBMS schema, with progressively more aggregated structures above the atomic layer in the form of OLAP cubes. Some of the OLAP systems can drill down through the OLAP cubes and access the lowest-level atomic data in a single application.
Surrogate Key Mapping Tables
Surrogate key mapping tables are designed to map natural keys from the disparate source systems to their master data warehouse surrogate key. Mapping tables are an efficient way to maintain surrogate keys in your data warehouse. These compact tables are designed for high-speed processing. Mapping tables contain only the most current value of a surrogate key—used to populate a dimension—and the natural key from the source system. Since the same dimension can have many sources, a mapping table contains a natural key column for each of its sources.
Mapping tables can be equally effective if they are stored in a database or on the file system. The advantage of using a database for mapping tables is that you can utilize the database sequence generator to create new surrogate keys. And also, when indexed properly, mapping tables in a database are very efficient during key value lookups.
Since key mapping tables serve no analytical value, they should never reside in the presentation layer of the data warehouse nor be exposed to end users.
CROSS-REFERENCE Details on how to utilize mapping tables to create surrogate keys for your dimensions are explained in Chapter 5, and utilizing them to populate fact tables is discussed in Chapter 6.
Planning and Design Standards
PROCESS CHECK Planning & Design:
Requirements/Realities → Architecture → Implementation → Test/Release
Data Flow: Extract → Clean → Conform → Deliver
The data-staging area must be managed and maintained as much, if not more, than any other database in your environment. Some staging areas are administered like a sandbox, where developers have a free for all—creating, dropping, and modifying tables at will. Naturally, because of the lack of control in these environments, troubleshooting and impact analysis take much longer than necessary, causing inflated costs to the project.
The data-staging area must be a controlled environment. Only the data architect should be able to design or modify a table in the staging area. All physical changes must be owned by the DBA responsible for the database. Also, if any developer needs a table, a strong chance exists that it can be used by another developer. As a rule of thumb: If you build a table, expect that it will be used by someone else for reasons other than you originally intended. People, especially developers, are very creative when it comes to reusing existing resources.
Impact Analysis
Impact analysis examines the metadata associated to an object (in this case a table or column) and determines what is affected by a change to its structure or content. Changing data-staging objects can break processes that are crucial to properly loading the data warehouse. Allowing ad-hoc changes to data-staging objects is detrimental to the success of your project.
Once a table is created in the staging area, you must perform impact analysis before any changes are made to it. Many ETL tool vendors provide impact analysis functionality, but this functionality is often overlooked during the ETL product proof-of-concept because it is a back-room function and not really important until the data warehouse is up and running and begins to evolve.
Impact analysis, an ETL function, is an onerous responsibility because changes to the source systems and the target data warehouse can be continuous and only the ETL process knows exactly which of these disparate elements are connected. Communication among the ETL project manager, source system DBA, and data warehouse modeling team is crucial to ensure that appropriate impact analysis is performed whenever changes to any of the systems to which the data warehouse is dependent occurs.
Metadata Capture
The topic of metadata is discussed in depth in Chapter 9, but it’s necessary for you to understand what types of metadata you’ll be responsible for while designing the data-staging area. Metadata has many different meanings depending on its context. Metadata, which describes or supports other data elements, is sprinkled throughout the components of the data warehouse. In the realm of the data warehouse, the data staging area is not the metadata repository. However, several metadata elements associated with the data-staging area are valuable to the data warehouse and must be presented to its end users.
Enable the presentation of metadata elements by designing the staging database with a data-modeling tool. Data-modeling tools store applicable metadata in their own repositories. Also, use an ETL tool that implicitly provides metadata about its processes and supports presentation of its transformations. Types of metadata derived by the staging area include the following:
Data Lineage. Perhaps the most interesting of all data warehouse metadata, the data lineage, also known as the logical data mapping, illustrates transformations applied to a data element between its original data source and its ultimate target in the data warehouse.
Business Definitions. Every table created in the data-staging area stems from a business definition. Business definitions can be captured in several different places including the data-modeling tool, the ETL tool, the database itself, or spreadsheets and Word documents. Use whatever standards are in place for capturing business definitions within the presentation layer of the data warehouse to maintain consistency.
Technical Definitions. In the data-staging area specifically, technical definitions are likely to be more prevalent than business definitions. Remember: If it is not documented, it does not exist. Without proper documentation of the technical definitions of your staging tables, the tables might be recreated over and over, causing duplication of efforts and data explosion in the data-staging area. Technical definitions describe the physical attributes of data elements, including the structure, format, and location. Properly document technical metadata for all of your data-staging tables to minimize ambiguity and ensure reusability.
Process Metadata. Processes that load data-staging tables must record their statistics along with the statistics of the data warehouse table loads. Although information regarding staging table loads need not be presented to end users, the ETL team must know exactly how many records were loaded into each staging table, with success and failure statistics for each process. A measure of data freshness is useful both for ETL administrators and end users.
All tables and files in the data-staging area must be designed by the ETL architect. Metadata must be well documented. Data-modeling tools offer metadata capture capabilities that would otherwise cause metadata documentation to be a laborious task. Use a data-modeling tool to capture appropriate metadata when designing staging tables, keeping in mind that the structure-oriented metadata from these tools is perhaps 25 percent of the total metadata picture. The next 25 percent of the metadata describes the results of data cleaning. To capture the additional process metadata, which is fully 50 percent of the metadata, make sure your ETL tool supplies the statistics required. At a minimum, the number of rows inserted, updated, deleted, and rejected should be available for each process. Also, the process start time, end time, and duration should be obtainable without any code enhancements.
Naming Conventions
The ETL team should not be in the business of developing naming stand-ards for the data warehouse team. Nevertheless, data-staging tables must adhere to a standardized set of naming standards defined by the data warehouse architect. It’s best practice to adopt the conventions in place in the rest of the data warehouse and apply those same rules to the staging area. On occasion, the data-staging area may contain tables or elements that are not in the data warehouse presentation layer and do not have established naming standards. Work with the data warehouse team and DBA group to embellish the existing naming standards to include special data-staging tables.
Many ETL tools and data-modeling tools insist on presenting long lists of table names alphabetically. You should pay careful attention to grouping your tables together in useful clumps by using their alphabetic sort!
Auditing Data Transformation Steps
The data transformations in a complex ETL system reflect intricate business rules. If you kept a formal audit trail of all data transformations used in your system, you would include at least the following list:
Replacing natural keys with surrogate keys
Combining and deduplicating entities
Conforming commonly used attributes in dimensions
Standardizing calculations, creating conformed key performance indicators (KPIs)
Correcting and coercing data in the data cleaning routines
In volatile environments, the source system data is constantly changing; it’s mandatory that the data warehouse have the ability to prove the data within it is accurate. But how is that accomplished when the source is constantly changing? The ETL process must maintain a snapshot of the before picture of the data before goes through the data-cleansing routines.
When data is significantly altered (cleansed) by the ETL process, an extract of the data before manipulation occurs must be retained for audit purposes. Furthermore, metadata for all data-cleansing logic must be available without sifting through code. The original source data, the data-cleansing metadata, and the final dimensional data must be displayed in a cohesive delivery mechanism to support questions arising from data-cleansing transformations.
Snapshots of extract data are stored in the data-staging area and made available for audit purposes. A before and after glimpse of the data, along with the metadata describing the data-cleansing logic, depicts exactly how the data within the data warehouse is derived and promotes confidence in the quality of its data.
Summary
In this chapter, we have reviewed the primary data structures you need in your ETL system. We started by making the case for staging data in many places, for transient and permanent needs. You need to be especially diligent in supporting legal and financial compliance requirements. In these cases, you need not only to store data but to document the rules by which the data was created; if that isn’t enough, you need to prove that the data hasn’t been tampered with.
A mature ETL environment will be a mixture of flat files, independent relational tables, full-blown normalized models, and maybe some other kinds of structured files, especially XML documents. But in all cases, we urge you to remember that the whole point of the ETL system is the final result: data structures purposely built for ease of use by end user tools. And of course, we recommend that these purposely built tables take the form of dimensional models consisting of fact tables and dimension tables. Their close cousins, OLAP cubes, are usually created from dimensional schemas.
Finally, we touched on some best-practice issues, including adopting a set of consistent design standards, performing systematic impact analyses on your table designs, and changing those table designs as you move forward, and making sure that you capture metadata at each point in the ETL system. The big categories of metadata include table structure metadata (25 percent), data-cleaning results (25 percent), and process results (50 percent). Metadata is developed in depth in Chapter 4 and Chapter 9.
Now that we have all our working data structures organized, in Chapter 3 we dive into the source data structures we must read to populate the data warehouse.