Hand-Coded ETL revisited

View previous topic View next topic Go down

Hand-Coded ETL revisited

Post  Nigel Nichols on Fri Feb 13, 2009 11:41 am

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

Nigel Nichols

Posts : 1
Join date : 2009-02-13

View user profile

Back to top Go down

Re: Hand-Coded ETL revisited

Post  BoxesAndLines on Sun Feb 15, 2009 11:53 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Nothing's changed

Post  Todd McDermid on Mon Feb 16, 2009 3:21 pm

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.
avatar
Todd McDermid

Posts : 11
Join date : 2009-02-04
Location : Nanaimo, BC

View user profile http://toddmcdermid.blogspot.com

Back to top Go down

Re: Hand-Coded ETL revisited

Post  tod mckenna on Thu Feb 19, 2009 10:21 am

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!
avatar
tod mckenna

Posts : 9
Join date : 2009-02-03

View user profile http://blog.todmeansfox.com

Back to top Go down

Re: Hand-Coded ETL revisited

Post  John Simon on Sun Aug 09, 2009 10:04 pm

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Hand-Coded ETL revisited

Post  c on Fri Aug 21, 2009 1:56 am

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?

c

Posts : 3
Join date : 2009-08-20

View user profile

Back to top Go down

Re: Hand-Coded ETL revisited

Post  ngalemmo on Wed Aug 26, 2009 12:26 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Hand-Coded ETL revisited

Post  fmarais on Fri Nov 06, 2009 8:32 am

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.

fmarais

Posts : 1
Join date : 2009-11-06

View user profile

Back to top Go down

It's all about the salary...

Post  Colin Davies on Fri Nov 20, 2009 1:56 am

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.

Colin Davies

Posts : 8
Join date : 2009-05-20

View user profile

Back to top Go down

Re: Hand-Coded ETL revisited

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum