ETL is dead, long live AP2 ?

jamiet — Fri, 15 Feb 2013 15:45:33 GMT

Three days ago I posted What would a cloud-based ETL tool look like? where I wondered out loud about the sorts of tools data integration dudes like myself would be using in the future. I got some good feedback and already have a list of “stuff” to go and look at including:

Boomi – They claim 1million cloud integrations (whatever one of those is) per day
AWS Data Pipeline – A web service that incorporates a scheduler, a workflow engine and (as the name suggests) a data pipeline engine
Informatica Cloud – An extension to Informatica’s market-leading PowerCenter for SalesForce.

Most interesting to me though was a link that Joe Harris provided to a a blog post by Mike Reich entitled Rethinking ETL for the API age. Mike outlined a number of points that really struck a chord with me; the key one was his message that the Extract-Transform-Load (ETL) mantra that has been trumpeted for years should be replaced by something that is more pertinent for “the cloud” – Mike offers Acquiring, Processing and Publishing (AP2) as a new acronym (we all love acronyms, right?). The idea of publishing data rather than loading it really resonated with me as making data easily available in non-proprietary formats so that people can consume it in whatever manner they choose has long been an interest of mine.

Here are some other bulleted thoughts that came into my head as I read Mike’s blog post:

“Flows are fluid and flexible, unlike structured, point-to-point ‘pipelines’” – My interpretation of “fluid and flexible” is that these “flows” can be plugged together to create a greater whole. This gives rise to the notion of composability; imagine being able to leverage flows that other people have constructed in your own flows. Yahoo Pipes (which I first blogged about almost five years ago in Taking Yahoo Pipes for a test drive) was an early incarnation of this notion of composability and is a great demonstrator of what the future holds for us.
Composability further gives rise to the notion of a marketplace where one could sell “flows”. For example, one could build a flow that aggregated data for a given search term from both Google and Bing, deduplicated the results then made them available as a single feed; expose that feed via a marketplace and charge on a pay-per-use basis. Its a simplistic, contrived example but in my opinion aptly demonstrates the opportunity here. I think data marketplaces, perhaps more pertinently data integration marketplaces, are going to be huge, I really do. Given the technology agnostic nature that is being proposed here these marketplaces would be totally interoperable too, unlike the hateful app stores that today’s xkcd expertly satirises.
“by using APIs to move information around, we decouple the data from the underlying technology and vendor” Absolutely true. An API is essentially a well-understood interface/abstraction over a proprietary data store so really there’s nothing new here (isn’t this what SOA was all about?) but there’s no harm in reiterating the point.
“information is stored in multiple structures and formats. Any effort to manage information should focus on translating between structures rather than trying to develop a common schema” I worked on a project from 2005-2008 where we attempted to adhere to a supposed industry standard schema. Eventually we realised that those attempts were futile given that no business can be fitted neatly into an industry-standard-shaped-box and that dovetails nicely with Mike’s point here.
“There are four common processing tasks; combining multiple streams, translating data formats, QA information, integrate third party processing” – I wonder if there is a fifth that we might refer to as data caching; after all, if we’re pulling data out of multiple APIs we are at the mercy of the speed at which those APIs can provide the data – is a person going to be prepared to wait for the data or do we need regularly cache the transformed data for easy retrieval?
“Publishing should be application/technology agnostic” It would be hard for me to agree more with this point.

As you can tell this is an area that I’m particularly interested in and shall continue to keep a watching brief.

@Jamiet

What would a cloud-based ETL tool look like?

jamiet — Tue, 12 Feb 2013 14:15:17 GMT

Given that “moving data to the cloud” is, rightly or wrongly, currently in vogue in our industry I have to think that pretty soon there will be a glaring need for tools that help us to move data between these heterogeneous sources – a cloud-based ETL tool for cloud-based data if you will. Perhaps such a thing already exists -I’ve talked about Kapow in the past which may well be considered a form of cloud ETL tool given that it fetches data from the web– if you know of anything that might fit this very loose description feel free to let me know in the comments.

I started to ponder what capabilities a cloud ETL tool should have and here’s a quick brainstormed list:

Data transformation would be done “in the cloud” i.e. I wouldn’t need to own my own hardware in order to run it
Ability to consume data from/push data to* the following types of data protocols:

ATOM (application/atom+xm)
RSS (application/rss+xml)
OData
GData
JSON
ODBC
JDBC
RDF (application/rdf+xml)
FTP

Ability to consume data from/push data to the following MIME types:

text/html (gives rise to the idea of screenscraping as a source of data)
text/plain
text/xml

Adapters (possibly with a plug-in model) for the following cloud storage and API providers:

Job scheduler
An IDE (open to debate whether the IDE should be “in the cloud” as well)
Ability to carry out common transformations (join, aggregate, sort, projection) on those heterogeneous data sources
Ability to authenticate using different authentication mechanisms
Configurable logging
Ability to publish transformed data in a manner that makes it consumable rather than insert it into another data store

Any thoughts here? As I said this is a brainstormed list so I don’t mind being told that I am approaching this from the wrong angle or even that I’m completely wrong . Should I be concentrating on scenarios rather than technologies?. I’m only too aware that given my ETL heritage my brain is already wired to consider how traditional ETL tools could be supplanted into the cloud (my mention of a job scheduler sold me out there) – perhaps that is completely wrong too and that my heritage is actually a disadvantage here.

I’m interested to know what people think and hopefully trigger a conversation. I’m especially keen to hear about scenarios that you might have where you need to move and transform data that lives “in the cloud”.

@Jamiet

*where applicable

UPDATE. Within seconds of publishing this post I’d already been alerted to InformaticaCloud.com & AWS Data Pipeline. Checking those out now!

I’ve been recommended to check out the following articles:

Search results matching tag 'Data Integration'

ETL is dead, long live AP2 ?

What would a cloud-based ETL tool look like?