page199
285
Curation Factors
You might ask yourself why you cannot separate the three
INSERT
,
UPDATE
, and
DELETE
parts of the
MERGE
statement and run these in parallel. Answering this question involves
understanding how Snowflake maintains micro-partitions and implements table
locking; I discussed these subjects within Chapter 4.
Figure 8-11 shows the impact of attempting to run three concurrent processes for
INSERT
,
UPDATE
, and
DELETE
in parallel against a single-core table. You will experience
blocking as the first process locks the core table and completes a
DELETE
operation; then
the next queued process will lock the core table and complete before the final process
locks the core table and completes.
Figure 8-11.
Serialized DML operations
The “Law of Unintended Consequences” (
https://en.wikipedia.org/wiki/
Unintended_consequences
) serializes the parallel processes. The observant might note
this is exactly the same effect described for logging information in Chapter 6.
If you are determined to serialize the DML operations, the optimal order of
application is as follows:
•
DELETE
: Reduces the data volume for later DML activity
•
UPDATE
: Operates against the minimal data volume in the object
•
INSERT
: Adds new records to increase object data volume
Merging data may also include enriching with reference data and maintenance of
bitemporal attributes mentioned for completeness only.
Chapter 8
parallelization