Home > ETL Best Practices > ETL Best Practices (1) – No one big package fits all

ETL Best Practices (1) – No one big package fits all

I’ve seen situations where very large and complex projects were built within a single dtsx file, even if the data volume is also very large.

As a best practice, we should break out the project into small and manageable pieces.

Generally we should have at least 4 packages per project, these are Data Extract from sources, Data Quality Checks and Cleansing, Conforming and Transformations, and the Final Insert or Update to the target. I’ll also include the Auditing report package. This will make at least 5 packages per project. This approach also means that we would use at least 3 staging points.

This is a good practice to follow.

1) It speeds development. I’ve seen situation where the developer had to wait 10+ hours for a load to complete. Development progress is extremely slow because of the waiting mode between testing. 

2) It allows multiple developers to work on pieces of the same project.

3) No doubt that it also speeds up troubleshooting, and testing as well.

Each one of these packages should also follow a naming convention (another best practice).

Here is what I use for all the packages I built:

etlProjectName_Step_StepDescStartingWithVerb

Examples:

  • etlEAMPC_EXT_ExtractRawData
  • etlEAMPC_STG_CleanSIMSData
  • etlEAMPC_STG_TransformSIMSData
  • etlEAMPC_TRG_DeliverPCHW
  • etlEAMPC_TRG_DeliverPCSW
Categories: ETL Best Practices
  1. Mark Wojciechowicz
    March 26, 2011 at 5:29 pm

    Hi Sherry,
    I am a BI architect working on the MS platform. I recently picked up Ralph Kimball’s ETL book and I found myself thrilled, as well with the methods that they had to offer. One thing I am struggling with though is putting he cleansing principles into practice in SSIS. I am wondering if you can offer any advice or direction about the practical application of creating cleansing “screens” as well as the audit dimension.
    Any reply would be appreciated,
    Mark

    Like

  1. No trackbacks yet.

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: