page198
284
Having set out contextual information, let’s aim to answer these questions:
•
DML volume for
INSERT
,
UPDATE
, and
DELETE
operations
•
Core table data volume and number of micro-partitions
•
Core table cluster key definition; you can assume a cluster key
is defined
•
Staged table data profile
•
Core table data profile
You assume the raw or staged data contains equal numbers of
INSERT
,
UPDATE
, and
DELETE
operations. From Chapter 4 you know that both
INSERT
and
DELETE
operations
complete faster than
UPDATE
operations.
Knowing the target core table data volume allows you to determine the percentage
change for
INSERT
,
UPDATE
, and
DELETE
operations. The number of target core table
micro-partitions is useful but not essential; of more significance is the number of micro-
partitions to be replicated.
The staged data profile is of particular interest, you can assume the sample data is
typical in profile; in other words, the data is not skewed nor misrepresenting the usual
data loaded.
For the example, you can assume the source staged table data has these attributes of
significance for merging data:
•
Unique identifier: The primary, unique, or hash key for the record
•
Record start date: The business date from which the record is valid
•
DML operation: Letter (I, U, D) indicating the type of operation to
perform on the target core table
The three attributes identified from the source staged table data enable us to
effectively implement a
MERGE
statement. But the three attributes are highly unlikely to
match the target table cluster key, which must be defined according to business needs,
not technical data maintenance needs.
You must understand how the staged data matches the target core table data.
Note that the cluster key will lead with the least selective attribute first and then the
next least selective attribute, and so on. This information is crucial for developing the
parallelization strategy.
Chapter 8
parallelization