ETL not working? Redesign the source database!

I was invited to attend a conference call to discuss two items attached to the meeting invitation. I had been involved in neither of them until that point. I decline, pointing this out. They insist. So I dial in, and wish I hadn’t… except that, if I hadn’t dialled in, I wouldn’t be able to bring you this little nugget of WTF-itude.

There’s a major ETL project. Y’know – Extract, Transform, Load. Except that they (again) seem to be ignoring the “T” part, or, indeed, the part about making sure they can accurately read the data from the source SQL Server and put it into the destination Oracle server. In the immortal words of the ancient philosopher Clarksonius, “How hard can it be?”

(from 5 seconds onwards)

The discussion got to the point where they asked us to re-design the database and rewrite the applications so that they could extract the data more easily.

No, you didn’t misread that.

Ain’t gonna happen. This is a system that has been running in production for several years, and has data feeds in both directions that will also be affected (and lacks the necessary documentation to ensure that we could hit them all, if we were inclined to follow this insanity). On the other hand, *they* are still building and testing their extraction routines…

This entry was posted in SQLServerPedia Syndication and tagged , . Bookmark the permalink.

3 Responses to ETL not working? Redesign the source database!

  1. David Scutt says:

    There is an argument, I think, when designing ETL, for using the source database (if you can) to transform the data as much as possible (i.e. joining tables through views), the idea being that an RDBMS can do that better than SSIS\Informatica ETL system can. From my high-level view it would make sense to expose your application database in a way that would help the ETL developers. Plus it helps you keep a rein on security.

    But… a re-design and rewrite? I hope that was followed up with a “What, are you all on crack?” style response… It sounds like they don’t know what they’re doing.

    • thomasrushton says:

      They were definitely talking about the base tables. Good point about using Views to help out with this sort of thing, and I should have remember that, but their request rather blew my mind…

  2. mahender says:

    Sometime technology unaware client talks all these S , redesigning Database just for extract make no sense, but it may be needed when extract routines are not capable enough to process data efficiently or there is a big performance bottleneck which can’t be addressed in ETL .anyways it’s an nice share
    ETL Labs

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s