Hand-Coded ETL revisited

[Nigel Nichols]

Hi

I read Gary Nissen's article, 'Is Hand-Coded ETL the Way to Go?', with interest.

Given that this was written nearly six years ago, I wonder whether the position has changed i.e. whather ETL tools are now more strongly recommended ober hand-coding.

Nigel

[BoxesAndLines]

You would have to ask Gary that question. Personally, I don't agree with his position. All ETL at my current client must be in the ETL tool (Informatica). The value of a metadata driven tool far outweighs the occasional performance enhancement of custom coding.

[Todd McDermid]

What I read in that article was this:

1. Hand-coding is always an option
   
2. Hand-coding is usually used when:
   
   1. Project management is bad or non-existent
      
   2. Costs of ETL tools appear to outweigh all other factors
3. There is an "industry bias" against hand-coding due to:
   
   1. The perception that Hand-coded projects are poorly documented
      
   2. The perception that Hand-coded projects are difficult to maintain

After reading Gary's article I don't find that he's arguing "for" or "against" hand-coding. He's merely laying out the factors that lead people to choose one or the other. I don't think much has changed in the final analysis since then. Yes, tool costs have dropped dramatically - they're even "free" (bundled) in some cases - but there's still the training cost that gets weighed.

[tod mckenna]

Hand coding gives you much more control. This control comes at a cost you can typically measure in loss of productivity and lack of documentation. ETL tools will come packaged with all the basic functionality you need to perform most ETL operations. You have little coding to do (most of the time you simply drag and drop, set a few parameters, and away you go) and little testing to do (the vendor tests all components so you know that a sort is actually going to sort). In addition, these tools have a lot of documentation available either from the vendor or through the metadata generated by the tool itself.

When you hand code, you need to be very, very disciplined. For many years I have been using Visual FoxPro for this very task. And over the years I have built a lot of great functionality that could rival SSIS and other tools (in fact, if VFP wasn't KILLED by M$ I would have packaged my VFP ETL tool and brought it to market -- oh well). VFP worked for me because (a) I have a ton of VFP knowledge, (b) it is a data-centric and object oriented language, (c) it is super fast at handling strings, and (d) my source applications, with few exceptions, were also written in VFP. But I can tell you that if I had a good dedicated tool at my disposal I could have been more productive and I'm sure would have better documentation.

But the control is hard to pass up. Now that I've gone away from using VFP, I find myself frustrated that I can't get the tool to do what I want. I find that I write more VB scripts and TSQL stored procedures (I currently use SSIS and SQL Server) than I thought I would have. I've had to write several custom tasks and components in C# to get additional functionality. I think I write just as much code now as I did when I "hand-coded". Go figure!

[John Simon]

I've used both commercial ETL tools and hand-written code in T-SQL and PL/SQL.
I much prefer handcoding. I've got more control and better performance. I've also done it enough times so that I've got a system with logging and error checking that is fast and easy to implement. Quite often I'll use SQL Server Integration Services to load the data into staging, then call stored procedures with SSIS.

The only downside to handcoding is the documentation and data lineage. However I've been on sites where the client paid for a third-party ETL tool because of the metadata capabilities. Did anybody ever look at the metadata though? Nope. I've heard comments that an ETL tool is easier to understand, but I'd bet that you can find more people who know SQL than the particulars of an ETL tool.

I believe that using both hand-coding and an ETL tool gives me more control, the visual documentation and superior performance than using an ETL tool alone. Especially when working with large volumes and a tight processing window.

[c]

What would you do when a ETL tool is available for use but all ETL work are hand-coded instead of using the tool?

Would you, and how would you enable a smooth transition from hand-coding to using the ETL tool?

[ngalemmo]

I've used a bunch of ETL tools, PL/SQL, T_SQL, even COBOL... I prefer ETL tools.

I agree that SQL gives you a lot more control, but at a significant cost. I one time wrote a PL/SQL package that used dynamic SQL for processing hierarchies. You pass it the name of the source parent/child table and the names of the target historical recursive table and hiearchy bridge table and it would update both with with effective dates and a bunch of other bells and whistles. It was a thing of beauty. Looking back at the code, I have no clue how it works (I did include comments, by the way). Maybe I'm getting old, but I can't imagine someone who has never seen the code could figure it out any quicker.

ETL tools impose a structure on how one works. Yes, you can get 'tricky' in places, but such opportunities are limited. When needing to build and support a large EDW, such structure is a good thing. Plus there is a lot less hassle when dealing with the variety of data sources you may encounter.

[fmarais]

There are many different ways of implementing the same business logic in most ETL tools.
A developer can move from one site to another and have to learn a completely different way of using the tool
because of differences in the level of understanding/requirements/expertise of developers involved.

So unfortunately ETL tools impose a structure only in a basic way,
more to do with their generic, lowest common denominator design, than
with the requirements of the ETL developer. Of course, if standards
and common design were imposed at a site, that would make a big difference
in maintainability.

Guess what? Exactly the same applies when handcoding.

Decoupling extract from transform in ETL is generally better for restartability
and scheduling of system loads. The source system access problem is over-emphasized
by vendors.

[Colin Davies]

One of the main drivers behind using a mainstream ETL tool is the fact that outsourcers can provide an ETL developer in India at a fraction of the price of a developer in the US. It has nothing to do with anything technical.

[Sponsored content]