A Post About Nothing

Actually not. But as a fan of Seinfeld, it’s cool to work in that quote.

Seriously, though, No Change Detection is a valid change detection use case. In SSIS it looks like this:

Whack ‘n’ Load

I (lovingly) refer to this SSIS Design Pattern as the Whack ‘n’ Load Pattern. Is it an Incremental Load design pattern? Maybe?? It depends on how you want to classify incremental loads. I will argue for “yes, it’s an incremental load pattern because it picks up new and changed rows since the last execution.” But even as I was just typing that, I could feel the definition is a stretch.

The SSIS package shown above loads data from a flat file into a SQL Server database table. The Execute SQL Task named “SQL Truncate Target” executes a statement to truncate the destination table similar to:

Truncate Table dbo.SomeTable

The Data Flow Task named “DFT Load Target” contains the components shown in the “breakout,” a Flat File Source Adapter named “FFSrc Flat File” and an OLE DB Destination Adapter named “OLEDBDest Target.” The OLE DB Destination Adapter is aimed at the same table truncated in the Execute SQL task named “SQL Truncate Target.”

When executed, the SSIS package appears as shown here:

The flat file contains only five rows at this time. Tomorrow it make contain 5, 6, or 6,000 rows. Sometimes, data scales.

The Whack ‘n’ Load pattern always clears the destination table and then loads the contents of the source into the destination table.

Advantages:

There are few moving parts so there’s less opportunity for the load process to fail. When a Whack ‘n’ Load package fails, it’s usually due to unanticipated data conditions such as a NULL where no NULLs are allowed, string truncation (trying to put ten pounds of string into a five-pound column, for example), or some catastrophic failure in either the SSIS execution engine, SQL Server, the Operating System (OS), or hardware.
It’s possible for change detection to “miss” a changed record in the source. There are various reasons this occurs and sundry ways in which it happens when it occurs. One reason changes are not detected is hash collisions. A hash collision occurs when different values (or combinations of values) produce the same hash value. All hash algorithms have a collision probability and Jeff Preshing does a great job explaining both the theory and how to calculate the hash collision probability in his post titled Hash Collision Probabilities. The Whack ‘n’ Load design pattern does not miss changes.

Disadvantages:

Whack ‘n’ Load is slow – especially for larger data sets.

When to Use the Whack ‘n’ Load Design Pattern

I often use the Whack ‘n’ Load design pattern when loading small data sets like reference lookup data.

I often build Whack ‘n’ Load SSIS packages to prototype staging databases or to quickly stand up a reporting instance. These days I use Business Intelligence Markup Language (Biml) to accomplish the initial portion of a data integration project because I can generate a metric tonne of SSIS packages in a few minutes. Later I identify poorly-performing packages and modify them to use another design pattern.

When Not to Use the Whack ‘n’ Load Design Pattern

I don’t use the Whack ‘n’ Load design pattern when loading larger data sets because of performance concerns.

I don’t use the Whack ‘n’ Load design pattern when loading data that requires more complex storage schemes, such as the change history requirement for loading a Type II Star Schema Dimension.