Home > ETL Best Practices > ETL #70 – The worst possible way to truncate your target table

ETL #70 – The worst possible way to truncate your target table

In my previous post, Why truncating a target table is a bad practice in ETL?, I strongly suggest that we do not truncate our target tables.

In this blog, I’ll tell you the worst possible way I’ve seen to truncate a target table.

Before I get into that, I’ll share with you the experience a business user has shared with me:

….Sherry, can you check the report again? It has failed with the new data load today again.

….What caused it to do that? Has been happening sporadically lately where the whole report just goes down.

….I thought the issue has been resolved since last change.

Before the “last change”, the report has no staging data. It’s pulling large amount of data directly from our data source via OPENQUERY (you can read my other posts to know my opinions about  OPENQUERY). If you are lucky, the report will send back data within 5 minutes.

So the last change was made is to pull data every morning and store it in a table.

Here is how this table (in my opinion, this is your target table, not a staging table) is populated every morning:

image

It is truncated first, then with an attempt to populate it. Unfortunately, almost 5 out of 10 days, the OPENQUERY failed, leaving the target table empty 5 days out of 10. In those 5 lucky days, the target table will remain empty for about 10 minutes, which is how long it takes for the table to be fully populated.

That explains the business users’ experience with the report.

Do you still need more convincing not to truncate your target tables?

  1. June 26, 2011 at 12:07 pm

    If it were me, I may design the process as below.
    1. truncate stage_targettable
    2.fill the stage_targettable if openquery succeeds
    3.truncate targettable and fill the target table by stage_targettable

    or
    1. truncate target table
    2. openquery
    3. check counts in target table, if exception, retry step 2

    Like

  1. November 27, 2011 at 1:18 am

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: