Is there a better ETL approach? Data Warehouse is all about loading data from source to target. Traditionally, source system produces fil...

My answer to a  curious ETL question

Answer by William Sharp:

Connect directly and schedule the etl jobs to run at a rate that matches the update frequency of the source.  you can profile the source update dates to figure that out.

View Answer on Quora

Why should I use an existing ETL vs writing my own in Python for my data warehouse needs?

Answer by William Sharp:

Unless you want to enter the ETL software market, you will spend a lot of time writing software instead of migrating / managing data (which I am assuming is your real job). If this tool is going to be used by many developers in an enterprise setting, you will spend a lot of time writing code to manage the ETL code. Probably more than you think

This is classic buy over build ... buy because there are people dedicated to solving issues you haven't even thought of yet. Build if your needs are so unique that there isn't a solution out there (extremely rare)

View Answer on Quora

Why is ETL associated mostly with data warehousing? Isn't ETL a procedure that eventually may apply when loading raw data to a transactio...

Why is ETL associated mostly with Data Warehousing?
Isn't ETL a procedure that eventually even may apply when loading raw data to a transactional databse?

View Question on Quora

MySQL: loading data from an external file

Path of least resistance

When you need to get something done quickly, the community edition of MySQL is a great option.   It has just what you need in terms of a management studio and requires very little setup.

Yesterday I was in that exact circumstance.  I have some data files that I need to analyze and not much time to do it.  So I downloaded the community edition of MySQL (found here).

Data time!

As I said, I needed to get some analysis done quickly, so I created a few tables with your basic ANSI  create table statements and I was ready to load my data.

I was loading data from delimited sources not located in the default MySQL data directory so I had to include a few wrinkles in my load data statement. By the way, load data is the command for loading data from a file into a MySQL table.

The wrinkles

Since I was loading data from an external file I had to include the following statement in my load data statement:
 Load data LOCAL INFILE 'INSERT FILE PATH HERE'
I was using a comma delimited file so I had to include the following statement as well:
FIELDS TERMINATED BY ','
And finally, I had column headers in external file so the following statement was necessary:
IGNORE 1 LINES
The load script, when complete, looked like this:
Use SCHEMA NAME;
Load data LOCAL INFILE Load data LOCAL INFILE 'INSERT FILE PATH HERE'
into table TARGET TABLE HERE
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

Nothing too magical, but I wanted to pass it along and, hopefully, save some of you the time in looking up the statements.

More information on MySQL load data parameters can be found here

How to design a Source to Target Matrix

After working a Source to Target Matrix (STTM) for weeks I came to a rather disappointing conclusion ... we laid out the matrix by sources and a developer can't pick that up and start developing. To say the least I was upset at myself and frustrated that we needed to reorganize this document again.  So I decided to pass on this piece of advice to you so you don't have to go through this like did.

Organize your STTM by targets and not sources

The target is what the developer is using as a starting point.  As a result, the developer needs to have his work, the mappings, organized by targets.  I'm not suggesting deviating from the left to right convention of source to target, but rather building a STTM for each target.  In many cases this means having a Excel tab for each target.

Sounds simple enough, right?  In reality it is simple but the complexity comes in how the information is gathered or relayed from clients.  If you start conversations with the client in the context of a target the approach is straight forward.  But that's not reality.  Clients start by talking about sources because that is what they know.  At the end of that session you end up with one source and multiple targets and a document you need to reorganize.

Have migration conversations with clients in the context of the target and save everyone the time of reorganizing and confirming the requirements

This approach is rooted in the principle of beginning with the end in mind.  In my experience, when everyone understands the goal in detail it is much easier, efficient and accurate to hit the target (no pun intended! Well maybe a little).

Data Discovery: a path to better ETL development

In my last post I made the statement that one of the uses for data discovery was to produce better ETL design.  I wanted to backup that statement with a follow up post on why I feel this way, some supporting research and how to go about achieving this enhanced design.

Why data discovery leads to better ETL design

Let’s start with why I feel this way.  Before I’d even heard of data quality I was doing it on a daily basis.  You see I spent several years as an ETL developer on many data warehousing implementation projects.

Typically after a couple of briefing meetings, I’d start developing ETL mappings.  Like any development effort that was followed by some unit testing where I would discover that although my ETL was written to specifications, the load didn’t “look right”.

After some digging I usually found the culprit was the fact that the source data did not match the expectations I had going into the development effort and it was time to, at the very least, add some transforms to the mapping to accommodate for the discrepancies.  In effect, I was performing two critical functions left out of the original development plan, data profiling and enhancement.  I feel strongly that had these two processes not been left out, I would have had a more complete and accurate ETL development experience from the get-go.

Unfortunately this was not an isolated event and, in fact, happened on almost every ETL project.  First hand experience is why I feel so strongly that data discovery leads to a better development process and, ultimately, outcome.

Supporting Research for Data Discovery in ETL Design

In a fairly recent polling exercise the Passioned Group, an analyst and consultancy company, based in The Netherlands, specializing in Business Intelligence, Data Integration and ETL tools, conducted a polling of 2,000 participants where they ask what they thought were the most important requirements when choosing an ETL tool.  The results demonstrated just how important data discovery is to ETL developers.

As you can see aside from performance, data profiling was the most important feature.  My intuition tells me that the people who responded to the poll had similar experiences to mine when developing ETL solutions.

Way back in 2001 William Laurent of Information Management wrote a piece entitled, Best Practices for Data Warehouse Database Developers.  The number one best practice was make sure you are provided with a usable data dictionary before starting heavy-duty development.  Data discovery can help build that data dictionary without relying on assumptions and assertions made by business analysts and database administrators.

In defining what ETL is the Passioned Group mentions data profiling by explaining how it can help build a system that

that is robust and has a clear structure.

The Data Warehouse Information organization , a site “Powered by "DWH Professionals", "DWH Enthusiasts" and People alike” graphically depicts data profiling  in their recommended ETL design process.



Here is an important statement they make about the benefits of data profiling during ETL design.

Data Profiling is a process that familiarizes you with the data you will be loading into the warehouse/mart

So how do I use data discovery to achieve a better ETL design?

As I mentioned in my previous post, I recommend starting with the following question:

What are the critical data domains we are looking to integrate into the target?

The reason I start with this seemingly basic question is so that you can build true discovery processes into the ETL design.  True discovery finds data unbeknownst to the data consumer that also needs to be included in the target.  To me, this is one of the most value added services that the ETL team can provide to the data consumers.  Here is an example, taken from my previous experience, that demonstrates what I mean.

I had a marketing client that was looking to build a repository from which they could perform campaign management and analytics.  They had done a fair mount of quality due diligence and identified what they felt were the required sources.

When I asked my generic question there was a fair amount of dissent in the room and some even pointed to the source to target matrix (STTM) as my source of information.  However, I pressed on and discovered that some of the more executive users of the analytics were interested in performing analysis on customers were were marketed to but the address of record, for which the source systems was included in the STTM, was not deliverable (or was returned by the USPS).

As it turns out, this information was not stored in a source system but rather kept in a spreadsheet (of course) by one of the marketing administrators.  Of course knowing this allowed me to incorporate  the spreadsheet in the ETL sources but it also help us build in another process which discovered and profiled address data in critical business applications which were then included in an enrichment process so that undeliverable addresses could be updated with the proper addresses (where applicable).

Data discovery is a simple process once you know where to point the discovery tool.  This focus is obtained by asking the general but effective question I mentioned above.  Data domains, like address, help you ask more intelligent and specific questions like …

what critical applications store, collect or consume address data?

Once this is uncovered, data discovery works much the same way that data profiling works.  You define the source, build a connection, define and execute the profile jobs and decipher the results.

Data Discovery for ETL Tips

Here are a few tricks I use when performing data discovery for an ETL design proof of concept.

1. Profile early and often


2. Translate data profiles into a metadata dictionary


3. Identify data anomalies


4. Never develop an ETL map from a specification, do it based on profile results


5. Communicate where metadata and data distributions do not match the businesses expectations and look for the root cause

I know this list seems basic, but you’d be surprised how often it does not happen and how much rework and cost is incurred as a result.

Your thoughts?

The many uses of data discovery

Bloor research defines data discovery as …

the discovery of relationships between data elements, regardless of where the data is stored.

If you expand your mind beyond the conventional relational database meaning of relationships, I agree with this definition.  Relationships in this context, or rather the context I chose to apply, means much more than a primary – foreign key relationship.

In this context relationships is defined as commonality.  This commonality can be of a data type, value pattern, or business use.  If you can profile data and understand the relationships you can set yourself up for more efficient data management practices in the areas of ETL, MDM, and application lifecycle (or application retirement).  Let’s take each of these and examine how a data discovery can increase the quality of the effort.

ETL and Data Discovery

Classical ETL takes data from a source and loads it to a target.  If you perform data discovery profiling on the sources before you build the ETL mapping you can achieve the following:

• a more accurate picture of the required data type of the attribute
  
  
  
  • By examining the profile you can determine if the assigned data type is most appropriate for the data element
  
  
  
  


• a more accurate specification for the type of transform required
  
  
  
  • If the data and metadata are not 100% coordinated you can build transforms to accommodate for this
  
  
  
  


• identification of data anomalies and outliers which require further investigation for possible remediation prior to the migration of data
  
  
  
  • this leads to a more robust error handling and exception handling process
  
  
  
  


• the identification of data, previously unknown, that meets the business requirements and needs to be migrated
  
  
  
  • discovery can lead to uncovering data that was previously undefined or unobtainable for data migrations
  
  
  
  

As discover tools mature, it may also be possible to generate ETL mappings directly from the tool.  If the target is more richly defined in the discovery tool and the sources are more accurately identified, it makes sense to me that a discovery tool can build a better ETL mapping.

This will require a tight coupling between the discovery and ETL tool, however, there are vendors in the market with this type of coupling available to them.

MDM and Data Discovery

In the same way that data discovery can aid ETL, so too can it aid the efforts of an MDM implementation.  Since MDM implementations are so dependent on ETL, the same leverage is available and can lead to a better MDM hub definition and ETL specification.

Here too can a feature to generate a data mapping be particularly useful.  With so much configuration required for match and merge rules, cutting some development form the scope of the effort would only add benefit.

Another particularly interesting feature would be the ability to generate candidate schemas for the MDM hub based on the data and metadata obtained in the profiles of the sources.

Application Lifecycle and Data Discovery

Finally, during a data discovery investigation it is possible to segment data by data ranges derived from last create / update dates.  This can be leveraged to perform application and/or data lifecycle management which would basically archive data past a certain date line or retire an application which has not be accessed in a predetermined, business driven date.

Here is another use for dynamically generated data mappings which would migrate the retired data to a target or archive destination.

Discovery is only the first step

As you can see from this quick summary, there are many uses for data discovery and as the tools mature there are many more things that can be done to leverage a discovery effort.

Your thoughts?