We use cookies and other similar technologies (Cookies) to enhance your experience and to provide you with relevant content and ads. By using our website, you are agreeing to the use of Cookies. You can change your settings at any time. Cookie Policy.
Start a Discussion
BI
user-pic

How Robust is Using SQL for ETL?

Vote 0 Votes
From this excellent discussion on LinkedIn, How sustainable is SQL for ETL? does it makes sense to continue using hand written SQL/PL-SQL as the ETL technology to grow a fast rising Data Mart? Is there a better alternative?

3 Replies

| Add a Reply
  • Wow, "hand written SQL"? Batch and pseduo-batch ETL to align operational data stores of course should use SQL. More real-time system alignment can use a "push" rather than "pull" paradigm better - tools like (dare I say) event processing mechanisms, rule-based systems, etc. [I've even seen an insurance company present on event-driven ETL at a DAMA conference, so its not exactly new!].

  • SQL scripting is fundamental to actually work on data, making selections, creating derived fields etc.
    To copy data somewhere else and to control the ETL flow, it is better to have a specific tool under the hood.

  • Reading a bit between the lines, an alternative way of asking this question can be - "With the advent of ELT (Extract, Transform & Load) paradigm, has SQL turned a full circle with respect to data transformation"? The reason for this slightly 'ungainly' sounding question is to put things in perspective. There are 3 ways of doing bulk data integration:

    1) Using SQL also called Hand coded data integration
    2) Using ETL (Extract, Transform & Load) tools
    3) Using ELT (Extract, Load & Transform) tools

    SQL is meant for set operations (the ubiquitous, much maligned cursors not withstanding) while ETL operations were designed for row by row operations. ELT on the other hand gives the pretext of doing row-by-row operations from a design standpoint but under the hood the operations are done in a SQL like set fashion so as to use the power of the database.

    Coming back to the question, my recommendation is to go for a tool (ETL or ELT) rather than hand-coded SQL simply because of the fact that SQL type of data integration relies too heavily on the expertise of the programmer. In my experience, I have seen wonderfully crafted data integration programs in SQL but have also encountered many sphagetti code which is a nightmare from the maintenance standpoint.

    On an average, tool based data integration tends to have better metadata management capabilities, relatively more control and ease of maintenance as compared to hand-coded SQL.

Add a Reply

Recently Commented On

Monthly Archives

Blogs

ADVERTISEMENT