ETL Processing with SQL - Pro SQL Server 2012 BI Solutions - page 211

Figure 6-1. The ETL process with SQL Server and SSIS
Most of these steps can be completed using either SQL programming statements or SSIS tasks, and we
examine both in this topic. You will likely understand the role of the SSIS tasks more thoroughly if we start by
examining the SQL statements that they represent. For that reason, let's examine the code necessary to complete
these steps using SQL programming statements.
Deciding on Full or Incremental Loading
Tables in the data warehouse can be either cleared out and refilled or loaded incrementally. Clearing out the
tables and then completely refilling them is known as a flush and fill technique. This technique is the simplest
way to implement an ETL process, but it does not work well with large tables. When dimension tables are small
and have only a few thousand rows, the flush and fill technique works quite well. Large tables, such as the fact
table, for example, may have millions of rows. Therefore, the time it takes to completely clear the table out and
then refill it with fresh data may be excessive. In those cases, filling up only data that has changed in the original
source is a much more efficient choice.
To use the flush and fill technique, clear the tables in the data warehouse of a SQL Server database using
either the DELETE command or the TRUNCATE command. When using the delete command, rows are deleted
from the table one by one. Accordingly, if there are 1,000 rows in a table, the delete will be processed 1,000
times. This happens very quickly, but it will still take more time than a simple truncation. The TRUNCATE
command de-allocates data pages that internally store the data in SQL Server. These invisible data pages are
then free to be reused for other objects in a SQL Server database. Truncation represents the quickest way to
clear out a SQL table, but you are not allowed to truncate a table if there are foreign key constraints associated
with that table.
Listing 6-1 is an example of what your SQL code looks like using the DELETE command.