ETL is dead, long live AP2 ?

re: ETL is dead, long live AP2 ?

Chris Nelson — Fri, 15 Feb 2013 19:09:29 GMT

Jamie,

Pardon me if I'm laughing at Cloud Data APIs saving the world (aka ETL 2.0 with magic bullets!!!) We get thousands of different files from hundreds of different clients. These in most case are people that can barely use Excel and barely understand delimiters. So whom is going to generate all these APIs???

Chris

re: ETL is dead, long live AP2 ?

jamiet — Fri, 15 Feb 2013 20:51:03 GMT

Hello Chris,

Thanks for the comment.

I don't think I've insinuated anywhere that the scenarios I'm outlining here are going to solve any problems that we have with ETL solutions today, on the contrary I believe that new technologies inevitably bring with them a whole raft of new problems that need solving. The problems I experience on a day to day basis can nearly always be out in the "data quality issue" bucket and I don't see that changing any time soon.

I made a similar, tho more light-hearted, response to your comment on my previous post at http://sqlblog.com/blogs/jamie_thomson/archive/2013/02/12/what-would-a-cloud-based-etl-tool-look-like.aspx. I assure you I'm not advocating any "silver bullets" as you put it, merely stating my belief that the job of a data integrator may well be changing significantly.

Again, thanks for the comment. Debating this issue is good.

Regards

Jamie

re: ETL is dead, long live AP2 ?

Frank Szendzielarz — Sat, 16 Feb 2013 13:48:37 GMT

Interesting. We have been tackling some obliquely related philosophical issues at the financial instituion where I work.

We are slowly re-architecting things. Some of the components that are being decommissioned are inhouse ETL types of software. Bit by bit these are becoming redundant because we are moving to more of an event-driven 'flow'.

As new data comes in (for example stock price updates), this can now be fed to other systems on an item by item basis using pub-sub messaging infrastructure.

What about joins and transformation? In at least one system, the ETL batch processing equivalent has become obsolete because we are using message based correlation in combination with out-of-order queued messages (natively supported by BufferedReceive in WF4 with net.msmq binding) in WF4 statemachines. The statemachine guarantees that messages are dequeued in the correct order, and instances of the statemachine correlate on specific keys.

In the cloud, Azure Service Bus has a pub-sub messaging system. Flows of data can be orchestrated using WF. Pub-sub messaging can allow the information to be published to subscribers. This way, information flow and transformation can be managed using dynamically generated or declaratively generated WF activities..

As for "AP2" on bulk data - I would call that "Reporting" if happens at intervals, or perhaps a continuously updated Report.

re: ETL is dead, long live AP2 ?

jamiet — Sat, 16 Feb 2013 13:56:32 GMT

Thanks for the comment Frank, very interesting indeed, especially to hear what you are using in favour of an ETL process.

re: ETL is dead, long live AP2 ?

Chris Nelson — Mon, 18 Feb 2013 13:23:26 GMT

Jamie,

I'm in a different situation than Frank. The data I deal with is at most a day to a month old, so it works well for ETL processing. I'm also dealing with multiple time perdiods and long term analysis.

re: ETL is dead, long live AP2 ?

Sarath — Wed, 20 Feb 2013 23:07:00 GMT

Hello Chris,

So do you mean that emerging non-ETL kind of methods like AP2 are not suitable for data that is historic in nature?

Thanks,

Sarath

re: ETL is dead, long live AP2 ?

pakki — Tue, 26 Feb 2013 13:10:01 GMT

Pakki's nunga current ETL velai parrunga

Data Explorer hits full preview

SSIS Junkie — Wed, 27 Feb 2013 17:47:59 GMT

A short recap At the PASS Summit 2011 a project that existed as part of the now-defunct SQL Azure Labs