Videos, Data Warehousing, Blog, ETL/ELT

What is an ETL Tool?

Jared Hillam

Jared Hillam

November 22, 2011

 

Here's the Case Study: ERP Real-Time Integration Case Study


What is an ETL tool, why is it important to have and how do you decide which is best for your organization?

Text from the Video:

Today I want to offer up a challenge to all those watching this video.  Find something in the room you’re in that has never had any data associated to it.  Go ahead look around.  Unless you find yourself watching this in the middle of no man’s land, chances are, that a very high percentage of everything around you has been represented by data at some point in its lifecycle.  Amazing isn’t it…?  So in many ways you can imagine that our digital world of data is a representation of the physical world around us.  And indeed it would be nearly impossible for organizations to accurately describe themselves if they didn’t have access to this data today.

And, just as organizations need to manage their physical assets, they likewise need to manage their data assets, after all they are a representation of the physical world.  And this is the point where I introduce you to something called an ETL tool.  ETL stands for Extract Transform and Load.  Just as its name implies, ETL tools EXTRACT data from a source, Transforms the data while in transit, then it Loads the data into a target storage of choice.  These movements of data can be scheduled on a regular basis or triggered to occur.  The types of projects that ETL tools are used for vary greatly as these tools are built to be very flexible.  Some common projects would be:

·         Rolling up Transaction Data for Business People to work with, which we commonly call a Data Marts or Data Warehouses

·         Migrating application data from old systems to new ones

·         Integrating data from recent corporate mergers & acquisitions

·         Integrating data from external suppliers and partners

The list of possible uses for an ETL tool extends well beyond the scope of this presentation.  But hopefully you’ll walk away from this video with an appreciation for how critical these systems can become to the day to day operations of a business.  To highlight life without an ETL tool, imagine having to be an expert at every source of data your organization owns, and trying to hand code all the data movement requirements we discussed earlier.

Now here’s where I want to provide a warning.  When you use an ETL tool you will marry your organization to it.  This is because a large body of logic will be designed in the data movement processes and it will become part of the lifeblood for running your business.  So, the purchase and use of an ETL tool is a very strategic move, even if it is intended for a small tactical project to begin with.  So before your marry yourself to your ETL tool make sure you’re not painting yourself in a corner, as ETL tool migrations are no small effort.  Here are 5 areas that you should focus on in determining which ETL tool to choose:

1.       Data Connectivity: ETL tools should be able to communicate to any source of data, no matter where it comes from.  This is critical, you may not have mainframes in house today, but all it takes is an acquisition of another organization to all the sudden need to get access to those systems

2.       Performance: Moving and changing data can require some serious processing power, so it’s important that your ETL tool can scale with your future data growth

3.       Transformation Flexibility: Matching, merging, and changing data is critical.  ETL tools should provide these and many other packaged transformations that allow modifications to be made to the data with simple drag and drop movements.

4.       Data Quality: Your data isn’t clean.  I can guarantee it, you will need support for data profiling, and data quality, including the ability to validate addresses.  The only way you can leverage your data to deliver information is if you’re data is consistent and clean.

5.       Flexible Data Acquisition Options: Once you have your ETL environment developed you’ll want the flexibility to determine whether the data is acquired on a time interval, or based on a trigger.  This also means that you want the flexibility to determine what data gets pulled whether it’s just the new data or all the data at once, or even a specific subset of data.

6.       Committed Vendor to ETL:  Remember you’re about to marry yourself to a tool.  Be sure your spouse is committed to Data Management, and all the activities around it.

Intricity partners with its customers to evaluate which tools will best fit their organizations.  I recommend you reach out to Intricity and talk with one of our specialists.  We can guide your organization in formulating a selection criterion that can ensure a good fit for managing your data over the long run.

-Jared Hillam, EIM Practice Director

Related Post

Snowflake Data Breach... Now What?

Snowflake's data breach affected 165 customers through stolen credentials. Discover the security measures that could have prevented it and how to protect your data in the future.

Read More

Using AI for Code & Metadata Conversions on Data Systems

LLM-based code conversions can have challenges and successes. Explore real-world insights and best practices for navigating these projects.

Read More

Ness Digital Engineering Acquires Intricity

Ness Digital Engineering Acquires Intricity - a New York based company specializing in data strategy, governance, modernization, and monetization

Learn More