Working of an ETL Tool

ETL stands for Extract, Transform and Load. ETL is one of the main components in a BI solution backed by a data warehouse. ETL is also used in other projects such as data migration and data integration projects. ETL flows/jobs can be built by scripting or by using ETL tools. Most of the companies currently use one of the existing ETL tools to build ETL flows/jobs.

In short, ETL tools abstract the technical complexity and thereby enables developers to focus on ”what needs to be done” than on “how it needs to be done”, For example a developer doesn't have to bother about developing a connector to a database, developers design the ETL flow/job using drag and drop, click and configure GUI of the ETL tool and also run, test, debug, schedule the ETL jobs using GUI of the ETL tool.

ETL tools provide a visual framework for ETL developers to design the ETL jobs. It provides a level of abstraction of the code/script. There are many features that ETL tool provides but I won’t go in those details here.

ETL tool allows the ETL developers to focus on the business logic/rules instead of the technical implementation. So, ETL developers focus on what to implement than on how to implement.

ETL tool is expected to create optimized code for an ETL flow and is expected to either bring the data to the ETL server or perform operations in the databases based on whichever is better for performance reasons. Ideally ETL developers shouldn't have to optimize the code but only configure the jobs/transformations/tool correctly such that optimized code is created for every ETL flow.

ETL tool has pre-created connectors/drivers to almost all type of data sources, For example to connect to a DB, all that you provide is the DB server details so you as a ETL developer don’t have to know how to code to connect to a data source.

ETL tool has mappings for each of the commonly used functions. If you see the metadata repository of an ETL tool you will see that there are predefined mapping for every common data transformation for every possible data source. I don’t remember exactly the mappings so I will give a simple example just to give you an idea how an ETL tool works. Lets say, business transformation requires to find out date from timestamp. In one DB it could be date(timestamp column), in other it could be dt (timestamp column) and in another one some other convention. When you do this in an ETL tool you just select the date from timestamp function from the ETL tool and then the ETL tool checks which type of data source it is and takes the corresponding mapping so that the underlying data source understands it. Now extrapolate this logic to every single transformation to every type of data source/target.

Like other tools ETL tools are also available in both open source and proprietary flavors.
  • SSIS is a proprietary ETL tool from Microsoft
  • Datastage - IBM
  • Ab initio - Ab initio
  • Informatica - Informatica
  • BODS - SAP
  • Popular open source ETL tools are Pentaho Data Integration and Talend

Comments

Post a Comment

Thanks for your comment. It will be posted after checks.

Popular posts from this blog

ETL developer vs Data engineer

Image
Rephrased question : ETL developer vs Data engineer Answer : Unfortunately there are no strict industry standards on these job titles. That is just one part of it. Before ETL tools such as DataStage, Informatica, Ab Initio, etc., became popular, developers were hand coding every ETL flow. These ETL tools shortened the ETL flow development time to a great extent and allowed ETL developers to focus on business rule/logic/requirement (what to implement) than how to code it or optimize the code. There are many other benefits of using a tool but I won’t go into that in this answer. So an ETL developer with experience in these tools without any programming (coding) experience was/is able to design and develop end to end data flows. Whenever new types of source/target data format comes up, these tools catch up but it takes time, i.e., the ETL tool provider (e.g. Microsoft, IBM, etc.,) adds new components/connectors within the ETL tool to be able to work with new data format. For example, let’
Read more

3 years of IBI

This week (on 19th Feb 2020) I completed 3 years of IBI. To celebrate it I have created a Dashboard in Google Data Studio. So from now on instead of using the charts in the Google Sheets I will use the Dashboard. Google Data Studio is more user friendly and mobile friendly too, also it comes with easy to use filters and drill down/up capabilities. PDF version of the first 2 pages of the shareable dashboard is attached below. I am happy to share the first 2 pages of the IBI Dashboard, reach out to me if you would like to get the Google Data Studio IBI dashboard. Once you get the dashboard (without my data of course) you can make a copy of it and point it to your IBI data (assuming you have started), add more charts, pages, etc., and use it for your benefit. Like with every year, this IBI year too I have learnt a lot about myself, made new mistakes, changed several things about me. The benefits of IBI is much more than we can think of. Use it to know it. And what are some of the
Read more