Data Warehouse Patterns - SQL Server Integration Services Design Patterns - page 316

tination table differs from the source data. In this case, we're checking
ten different fields, and we'll process an update to the destination if any
of those fields is different between the source and destination. Also note
the liberal use of the ISNULL() function against the destination table.
This is recommended to ensure that rows in the destination table con-
taining NULL values are not inadvertently skipped during the MERGE .
• In the code block immediately following, we update rows in the target
table that have a valid match against the source but have one or more
values that differ between the two.
• In the code block beginning with WHEN NOT MATCHED BY tar-
get... , any source rows not matched to the specified key column(s)
of an existing dimension record are written as new rows to that dimen-
sion table.
• Finally, we use the OUTPUT clause to select the action description and
insert data. We can use the output of this to write to our auditing table
(more on that momentarily).
You'll notice that we're handling this dimension processing as a Type 1 dimension,
in which we intentionally overwrite the previous values and do not maintain a historic-
al record of past values. It is possible to use the MERGE command to process Type 2 di-
mensions that retain historical values, or even those with a mixture of Type 1 and Type
2 attributes. In the interest of brevity, I won't try to cover the various other uses of
MERGE as it applies to slowly changing dimensions, but I think you'll find that it's
flexible enough to handle most Type 1 and Type 2 dimensions.
It is also worth noting that you can also use the MERGE statement to delete data in
addition to performing inserts and updates. It's not as common to delete data in data
warehouse environments as it is in other settings, but you may occasionally find it ne-
cessary to delete data from the target table.
Auditing with MERGE
As with other data warehouse operations, it's considered a best practice to audit, at a
minimum, the row counts of dimensional data that is added, deleted, or changed. This
is especially true for MERGE operations. Because multiple operations can occur in the
same statement, it's important to be able to track those operations to assist with
troubleshooting, even if comprehensive auditing is not used in a given operation.