We come across a lot of routine data-quality tasks in our line of work.  Migrations, test data sets, mergers and acquisitions, its really quite an old subject by now. I thought we pretty much had it nailed, boring, tedious, and profitable!

But there’s always a surprise waiting for you just around the corner.

We’ve stumbled into an entirely new way of looking at much of this stuff over the last year or so, here’s how it goes: You get dragged in to a project that’s publishing monster volumes of data that until  recently was locked away deep in the bowels of a proprietary system (or even several of them).

But government mandates, or a drive for improving productivity, or a dozen other reasons, have driven the various data owners together to publish all this stuff so that it’s more accessible.

This invariably means some kind of internet portal, perhaps some BI tools, a fancy security system, maybe even public access. Then we get to the data, and it starts to get hairy.

As we pull stuff out we find all sorts of inconsistencies, undocumented gaps, lapses, ridiculous paradoxes and so on. Of course everyone blames us, since they see us as the new owners of the new “system”.

The DQ-monkey is firmly on our back. Probably for life!

So what to do? After all, our mandate is to publish the stuff, and we want to do it in a way that seems straightforward to both the data owners, and the folk that will be using it. Until recently we would end up with a set of rather elaborate procedures to get the data, ship if from one place to another, piles of validation, then reload into the “new system”. This is classic ETL (Extract-Transfer-Load) there are even a few good tools out there to help.

But no matter how you sweat it its usually slow, your published portal lags, by days, sometimes a week (or a Quarter!) behind the real data. And every time there’s an inconsistency we get dragged over the coals.

So last time round we took a different track. We didn’t really plan it though. We had so many different systems, with different owners, schedules, availability etc we just could not get it together, no one could agree on formats and some folk had their data locked up in Fort Knox. We had to find another way so that everyone could give us the data “their way”.

So we went out to each provider, we called them “data sources”, and convinced them to let us build a little web-service right in their environment. We got them up and running, one at a time, no need for co-ordination at all.  Everyone got fairly enthusiastic as soon as they realized they would no longer be responsible for providing those weekly or monthly feeds. Just turn it on and forget it!

Then we built our portal, with a query processor that could figure out where to go and get what it needed. Since we had written all the webservices we had a pretty consistent common-API. All done with SOAP web-service calls.  It was sluggish, but it worked rather well, and when one source went down the others all kept chugging away just fine.

Then a strange thing happened.

We ran into some data errors, but they weren’t ours anymore!  Since the data was “live” from the horse’s mouth, the provider was the one everyone asked for explanation.

The DQ-monkey has been chopped into little pieces and smeared all over the globe!

We now call this the “DQ Spotlight”, as soon as something pops up we shine the spotlight on it and hey-presto, the owner fixes it, or at least explains well enough so we can document it!

So next time you’re called on to pull a collection together think about how you can delegate! Take out your Spotlight and blind that Monkey!

Some thoughts on the Silver Lining in that cloud. We’ve been using the Amazon cloud for development for couple of years now, and on the whole are very pleased. Even have a small production app up there.

But the last week has been a wakeup call for where its real advantages lie.

Imagine this.  You’ve been working on a production rollout of a well-developed app. It’s all there, well tested, all we want is to go public, both on a private internal net and on the Internet.

So we order the hardware, and wait. Get the machine room ready, and order extra AC, and wait. Get the power in there (wait for the mega UPS). The sysadmins get it up, install the OS, apply the patches etc., another week passes.  Now the DB guys come in, put in the DB, while at the same time the network guys get the app server up and running, and start haggling about how it will work with the firewall(s).

And we haven’t even loaded the app yet!

All told its weeks, actually nearly 2 months, from the decision to go to the first successful test from outside that really runs the app!  And more than a dozen people are involved, hundreds of man hours!

Then it finally dawns on everyone that there is no DR in place at all. Sure, we have good backups, and practice restores. But if the site is lost then we are out of commission until we build a new one! So now we start the whole process over to plan a DR and bring it online.

So, here I am, with all this collected wisdom about what it takes when I get a request, for a totally different unrelated project.  It’s late Wednesday afternoon on the 14th. “Paul, the OL app has to go live on the first! Make it happen! Oh, and we have to have a DR in California! We expect 1,000 online users by Christmas. ”

Big gulp, “only way to do it is a set of cloud servers”

So we try….. Thursday we fire up the DB, the security guy already has a cloud-database security group figured out, bring up a standby in CA. Decide we don’t like that version of  Linux, so we do it all again with something that’s a better fit for what we want. Load 20G of data in the afternoon.  Friday we test failover, switchover, switchback, get it down to 5 minutes after a couple of runs!

Monday we bring up App-servers. Big issue, the CA App-server can’t talk to the east coast DB!   Frantic scrabble for a couple hours, and it works.  Tuesday morning, I’m all done! I spend the rest of Tuesday writing up a nice set of 3 install docs, and a failover plan.

Then we realize we are weeks early! So we simply stop them all. Amazon won’t bill us if it’s not up and running.

Sometime on Wednesday it suddenly strikes me.

We found the silver lining in this cloud!

We replaced 2 months work part time by a team of ten or twelve with about 4 man days, plus a half day from the security guy, with no capital budget at all.

And we are done! Industrial strength DR, failover in 5 minutes, growth capacity available at a days notice!

Now if we can get a rainbow maybe I can find the crock of gold?