The Joy of Legacy Data

Sometimes you are in a position to develop your data schema from scratch when you are developing a new system using object-oriented technologies. If so, consider yourself amongst the lucky few because the vast majority of developers are often forced to tolerate one or more existing legacy data designs. Worse yet, it is often presumed that these data sources cannot be improved because of the corresponding changes that would be required to the legacy applications that currently access them. The problems presented by legacy data sources are often too difficult to fix immediately, therefore you have to learn to work around them.

The goal of this article is to introduce both application developers and Agile DBAs to the realities of working with legacy data. For our purposes any computer artifact, including but not limited to data and software, is considered to be a legacy asset once it is deployed and in production. For example, the C# application and its XML database that you deployed last week are now considered to be legacy assets even though they are the built from the most modern technologies within your organization. A legacy data source is any file, database, or software asset (such as a web service or business application) that supplies or produces data and that has already been deployed. For the sake of brevity we will only focus on the data aspects of legacy software assets.

The need to work with legacy data constrains a development team. It reduces their flexibility because they cannot easily manipulate the source data schema to reflect the needs of their object schema (see Mapping Objects to RDBs). Legacy data often doesn't provide the full range of information required by the team because the data does not reflect their new requirements. Legacy data is often constrained itself by the other applications that work with it, constraints that are then put on your team. Legacy data is often difficult to work with because of a combination of quality, design, architecture, or political issues.

Sources of Legacy Data
Common Problems With Legacy Data
Strategies for Working With Legacy Data
Data Integration Technologies
How Common is This?
What You Have Learned

1. Sources of Legacy Data

Where does legacy data come from? Virtually everywhere. Figure 1 indicates that there are many sources from which you may obtain legacy data. This includes existing databases, often relational, although non-RDBs such as hierarchical, network, object, XML, object/relational databases, and NoSQL databases. Files, such as XML documents or "flat files" such as configuration files and comma-delimited text files, are also common sources of legacy data. Software, including legacy applications that have been wrapped (perhaps via CORBA) and legacy services such as web services or CICS transactions, can also provide access to existing information. The point to be made is that there is often far more to gaining access to legacy data than simply writing an SQL query against an existing relational database.

Figure 1. Legacy data sources.

2. Common Problems with Legacy Data Sources

What type of problems are you likely to experience with legacy data? There are three technical issues, all of which contribute to your organization's technical debt (arguably technical debt associated with legacy data sources should be referred to as data debt), and one non-technical issue to be concerned with:

Data quality challenges
Database design problems
Data architecture problems
Process-related challenges

2.1 Data Quality Challenges

Table 1 lists the most common problems that you may encounter, indicating potential database refactorings (see the database refactoring catalog) that you could apply to resolve the problem. It is important to understand that any given data source may suffer from several of these problems, and sometimes a single data column/field may even experience several problems.

Agile DBAs will work with application programmers to identify their data needs, to then identify potential sources for that data, and in the case of legacy data to help them to access that data. Part of the job of accessing the data is to help application developers to transform and cleanse the data to make it usable. Agile DBAs will be aware of the potential problems summarized in Table 1 and will work closely with the application programmers to overcome the challenges.

Table 1. Typical Legacy Data Problems.

Problem	Potential Database Refactorings
A single column is used for several purposes	Split Column (to Notes)
The purpose of a column is determined by the value of one or more other columns	Remove Unused Column (to remove DateType) Split Column (to PersonDate)
Inconsistent data values	Introduce Trigger(s) for Calculated Column (between BirthDate and AgeInYears) Remove Redundant Column (to AgeInYears)
Inconsistent/incorrect data formatting	Introduce Common Format
Missing data	N/A
Missing columns	N/A
Additional columns	Introduce Default Value to a Column Remove Redundant Column
Multiple sources for the same data	N/A
Important entities, attributes, and relationships are hidden and floating in text fields	Replace Blob With Table Split Column
Data values that stray from their field descriptions and business rules	Split Column
Various key strategies for the same type of entity	Consolidate Key Strategy For Entity
Unrealized relationships between data records	Introduce Explicit Relationship
One attribute is stored in several fields	Combine Columns Representing a Single Concept
Inconsistent use of special characters	Introduce Common Format
Different data types for similar columns	Apply Standard Types to Similar Data
Different levels of detail	Introduce Calculated Column Replace Column
Different modes of operation	Separate Read-Only Data
Varying timeliness of data	Separate Data Based on Timeliness
Varying default values	Introduce Default Value to a Column
Various representations	Apply Standard Codes Apply Standard Types to Similar Data

2.2 Common Database Design Problems

The second problem with legacy data sources that Agile DBAs need to be aware of are fundamental design problems. Existing data designs, or even new data designs, are rarely perfect and often suffer from significant challenges. Common data design problems you will likely discover:

Database encapsulation scheme exists, but it's difficult to use
Ineffective (or no) naming conventions
Inadequate documentation
Original design goals at odds with current project needs
Inconsistent key strategy

These design problems may be the result of poor database design in the first place, perhaps the designers did not have a very good understanding of data modeling. Sometimes the initial design of a data source was very good but over time the quality degraded as ill-advised schema changes were made, something referred to as schema entropy. Once again, the Agile DBA will need to work closely with application programmers to overcome these problems. Their past experience dealing with similar design problems, as well as their personal relationship with the owners of the legacy data source(s), will prove to be a valuable asset to the project team.

2.3 Common Data Architecture Problems

Agile DBAs need to be aware of the problems with the data architecture within your enterprise, information that they will often gain through discussions with enterprise architects. These problems typically result from project teams not conforming to an enterprise architectural vision (such a vision seldom exists) or because the project team simply wasn't aware of data architectural issues. Some of the potential data architecture problems that you may discover include:

Applications responsible for data cleansing
Different database paradigms
Different hardware platforms
Different storage devices
Fragmented data sources
Inaccessible data
Inconsistent semantics
Inflexible architecture
Lack of event notification
Redundant data sources
No or inefficient security
Varying timeliness of data sources

A common implication of these architecture problems is that you need to put an effective data access approach in place such as introducing a staging database or a robust data encapsulation strategy. Staging databases are discussed below and encapsulation strategies are covered in another chapter.

2.4 Common Software Process Problems When Working With Legacy Data

The technical challenges associated with legacy data are bad enough, although unfortunately non-technical ones often overshadow them. The most difficult aspect of software development is to get people to work together effectively, and dealing with legacy data is no exception. Organizations will often hobble development teams because they are unable, or unwilling, to define and then work towards an effective vision. When it comes to working with legacy data there are several common process-oriented mistakes that I have seen organizations make:

Working with legacy data when you don't need to.
Data design drives your object model.
Legacy data issues overshadow everything else.
Application developers ignore legacy data issues.
You choose to not refactor the legacy data source.
Politics.
You don't see the software forest for the legacy data trees.
You don't put contract models in place.

3. Strategies for Working With Legacy Data

My assumption in this section is that your project needs to access one or more sources of legacy data but that it is not responsible for an organization-wide data conversion effort, e.g. you are not working on an Enterprise Application Integration (EAI) project, although you may be working on a data warehouse (DW)/Business Intelligence (BI) project. That isn't to say that the advice presented below couldn't be modified for such a situation. However, because the focus of this method is on ways of thinking (WoT) and ways of working (WoW) that Agile DBAs and application developers can apply when developing business applications this section will remain consistent with that vision.

The fundamental strategies that you should consider for working with legacy data for use with your application are:

Try to Avoid Legacy Data
Develop a Data Error Handling Strategy
Work Iteratively and Incrementally
Prefer Read-Only Legacy Data Access
Encapsulate Legacy Data Access
Introduce Data Adapters For Simple Data Access
Introduce a Staging Database For Complex Data Access
Adopt Existing Tools

3.1 Try to Avoid Legacy Data

The simplest solution is to not work with legacy data at all. If you can avoid working with legacy data, and therefore avoid the constraints that it places on you, then do so. There are several strategies that your team may try to apply in order to avoid working with legacy data, or to at least avoid a complex conversion effort. The strategies are presented in the order of simplest to most complex:

Create your own, stand-alone database.
Reprioritize/drop functionality that requires legacy data access. Your stakeholders may decide to forgo some functionality that requires legacy data access when they realize the cost of doing so.
Accept legacy data as is. Your team chooses to directly access the data without a conversion effort.
Refactor the legacy data source. The legacy system owners improve the quality of the legacy data source, allowing your team to work with high-quality legacy data.

An interesting observation is that when you take a big design up front (BDUF) approach to development where your database schema is created early in the life of your project you are effectively inflicting a legacy schema on yourself. Don't do this.

3.2 Develop a Data Error Handling Strategy

It should be clear by now that you are very likely that you will discover quality problems with the source data. When this happens you will want to apply one or more of the following strategies for handling the error:

Convert the faulty data.
Drop the faulty data.
Log the error.
Fix the source data.

3.3 Work Iteratively and Incrementally

Agile software developers work in an iterative and incremental (evolutionary) manner. The really good ones work in a disciplined agile manner. It is possible for data professionals to also work in this manner but that they must choose to do so. Agile developers will not attempt to write the data access/conversion code in one fell swoop. Instead they will write only the data-oriented code that they require for the business requirements that they are currently working on. Therefore their data-oriented code will grow and evolve in an iterative and incremental fashion, just as the code for the rest of the application evolves.

Working with legacy data, and in particular converting it into a cleaner and more usable design, is often viewed by traditional developers as a large and onerous task. They're partially right, it is an onerous task but it doesn't have to be a large one. They're wrong about it being a large task, instead you can break the problem up into smaller portions and tackle each one at a time. It's like the old adage "How do you eat an elephant? One bite at a time". Database refactoring is a technique for improving the design of a database schema in such a manner. It is possible to work iteratively and incrementally when in comes to data-oriented efforts, but you have to choose to do so. Yes, many data professionals are more comfortable taking a serial approach to development but this is simply not an option for modern development efforts. Choose to try new ways to work.

3.4 Prefer Read-Only Legacy Data Access

It can be exceptionally difficult to address many of the data quality problems and the database design problems described earlier when you simply have to read the data. My experience is that it is often an order of magnitude harder to support both reading and writing to a legacy data source as compared to just reading from it. For example, say both legacy data value X and value Y both map to "fixed" value A. If your application needs to update the legacy value, what should A be written back as, X or Y? The fundamental issue is that to support both read and write data access you need to define conversion rules for each direction. Writing data to a legacy data source entails greater risk than simply reading it because when you write data you must preserve its semantics - semantics that you may not fully comprehend without extensive analysis of the other systems that also write to that database. The implication is that it is clearly to your advantage to avoid updating legacy data sources whenever possible.

3.5 Encapsulate Legacy Data Access

By encapsulating database access you reduce coupling with a database and thus increase its maintainability and flexibility: this is true for the database(s) you are responsible for and it is true of legacy data sources. You also reduce the burden to your application developers, they only need to know how to work with the encapsulation strategy and not with all of the individual data sources. Encapsulating access to a legacy data source is highly desirable because you do not want to couple your application code to data-oriented code that will need to evolve as the legacy data sources evolve. This can be particularly true when you need to support both read and write access to legacy data sources and/or when multiple data sources exist.

3.6 Introduce Data Adapters For Simple Data Access

In simple situations - you have to work with one legacy data source, you only need a subset of the data, and the data is relatively clean - then your best option is to introduce a class that accesses the legacy data. For example, assume you need access to customer data stored in a legacy database. The data that you currently require is stored in two different tables, there are several minor problems with the quality of the data, and one relatively complicated data quality issue. You decide to create a class called CustomerDataAdapter that encapsulates all of the functionality to work with this legacy data. This class would include the code necessary to read the data, and write it as well if required. It would also implement the functionality required to convert the legacy data into something usable by your business classes, and back again if need be. When a customer object requires data it requests it via CustomerDataAdapter, obtaining the data it needs at the time. If another type of business class required legacy data, for example the Order class, then I would implement an OrderDataAdapter to do this - one data adapter class per business class.

3.7 Introduce a Staging Database For Complex Data Access

As your project progresses you may discover that the data adapter approach isn't sufficient. Perhaps your application requires better performance that can only be achieved through a batch approach to converting the legacy data. Perhaps there is another data conversion effort in progress within your organization that you want to take advantage of, one that is based on introducing a new database schema. Perhaps your legacy data needs are so complex it has become clear to you that a new approach is needed.

A staging database can be introduced for the sole purpose of providing easy access to legacy data. The idea is that data converters are written, perhaps by refactoring your data adapters, to access the data of a single legacy data source, the then cleanse the data, and finally write it into the staging database. If the legacy data needs to be updated then similar code needs to be written to support conversion in the opposite direction. The main advantage of this approach is that legacy data problems can be addressed without your application even being aware of them - from the point of view of your application it's working with nice, clean legacy data. The main disadvantage is the additional complexity inherent in the approach.

3.8 Adopt Existing Tools

Your organization may have existing tools and facilities in place that you can use to access existing legacy data. For example you may have a corporate license for one or more Extract-Transform-Load (ETL) tools that are typically used for large-scale data conversion projects. Perhaps other application teams have already written data adapters or data converters that your team can reuse. In short, reuse existing resources whenever possible.

4. Data Integration Technologies

There are several important technologies available to you for integrating legacy data sources. My goal here is to make you aware that each one exists, that you have choices available to you. These technologies include:

Service-based technology.
Consolidated database(s).
Messaging-based approaches.
Common Warehouse Metamodel (CWM).
Extensible Markup Language (XML).

When choosing data integration technologies for your project the most important thing that an Agile DBA can do is to work with your enterprise architects and administrators to ensure that your team's choices reflect the long term architectural vision for your organization. Ideally this vision is well known already, although when you are working with new technologies or when your organization is in the process of defining the vision you may discover that you need to work with enterprise personnel closely to get this right.

5. How Common is This?

The need to perform legacy analysis is very common on agile projects. The majority of agile teams work with legacy assets in some way. In fact, the 2013 Agile Project Initiation Survey found that 72% of agile teams worked with legacy assets in some manner. The 2012 Agility at Scale Survey , see Figure 2, found that agile teams were both succeeding and failing when faced with technical complexities such as dealing with legacy data. Perhaps the teams that ran into trouble didn't heed the advice in this article. ;-)
Figure 2. Agile experiences with technical complexity.

6. What You Have Learned

Working with legacy data is a common, and often very frustrating, reality of software development. There are often a wide variety of problems with the legacy data, including data quality, data design, data architecture, and political/process related issues. This article explored these problems in detail, giving you the background that you require to begin dealing with them effectively.

You were also introduced to a collection of strategies and technologies for working with legacy data. The first one is to avoid working with it if possible, why needlessly suffer these problems? You saw that working iteratively and incrementally is a viable approach for dealing with legacy data, the hardest part is to choose to work this way. Technical solutions were also identified, including the development of data adapters and staging databases.

Working with legacy data is a difficult task, one that I don't wish on anyone. Unfortunately we all have to do it, so it's better to accept this fact, gain the skills that we need to succeed, and then get on with the work. This article has laid the foundation from which to gain the skills that you require.

Table of Contents