ETL Tool or SQL

In an earlier article I wrote about the advantage of handling changes in a dimension with the Kimball Slowly Changing Dimension Type 2 principle: creating a new row in a dimension. It explained the advantages of this method and how it works. Below I describe the advantages of T-SQL scripts compared to an ETL tool for updating a dimension.

What do you check?
Updating a dimension is boring. Nine times out of ten you do the same trick again. You take a data stream from a source system and a data stream from a dimension. You compare them, and then you always check three things:

• Have there been changes;
• Are there deletes;
• Are there logical deletes.

A change is a modification of a field in a source system that affects an attribute in a dimension, for example: the last name of a customer changed because he got married. A delete is a record that is physically no longer present in a source system, and a logical delete is a record that is flagged as deleted.

Tool or SQL?
There are several methods to check these three properties. You can use an ETL tool such as SQL Server Integration Services, or you can build / generate a script yourself that does this check for you. I have always been in favour of tools. Tools are self-documenting and take a lot of work off your hands. Now that I am working on a project where SQL handles many actions without tools, I see more and more advantages of leaving tools aside.

Advantages:

• Avoids problems with version upgrades
• Can be generated
• Always works
• Manageability

By the first one I mean the problems that come up when the vendor of an ETL tool decides to release a new version that is just a bit better than the previous one, and after a while decides to stop supporting the old version. With SQL you do not have this. The basics of SQL always stay the same, even after a version upgrade. By “can be generated” I mean that you can generate objects with dynamic SQL that do the work for you. Think of a stored procedure. When I decide to use SSIS (Integration Services), I must create an SSIS package for every dimension table (from a template), and I always have to change things by hand. You can NEVER generate an SSIS package! On top of that, I must change all those hundreds of SSIS packages when something changes (manageability). Finally, SQL is reliable and always works; for SSIS that remains to be seen. Several times I have seen odd bugs and messages that suddenly appeared and suddenly disappeared!

Disadvantages:

• More complex and less clear
• More initial development work

ETL tools are self-documenting. When you use tools such as a Dataflowtask or a Derived Column, you see what happens. Tools are also quicker to understand than pages of code. Finally, building your own ETL is of course a lot more work. Still, I think you reap the rewards of it in the longer term. Once your solution is in place, the data warehouse is more scalable and easier to manage. So much for my opinion on using SQL vs TOOLS.