page208
294
With the raw or staged data created and core table prepared, you are ready to
investigate how to modify the application schema.
Testing Core Table Load
With your newly create raw or staged data, let’s establish how long your data pipeline
takes to merge the content into the target core table.
Commence testing using an X-Small warehouse and increase the size to provide
indicative timings having reset the target core table and set the same cluster key for
each run.
SET tpc_warehouse_XS = 'tpc_wh_xsmall';
SET tpc_warehouse_S = 'tpc_wh_small';
SET tpc_warehouse_M = 'tpc_wh_medium';
SET tpc_warehouse_L = 'tpc_wh_large';
SET tpc_warehouse_XL = 'tpc_wh_xlarge';
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_XS );
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_S );
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_M );
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_L );
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_XL );
MERGE INTO core_customer_order c
USING stg_customer_order s
ON c.order_key = s.order_key
WHEN MATCHED AND operation = 'D' THEN DELETE
WHEN MATCHED AND operation = 'U' THEN
UPDATE SET customer_key = s.customer_key,
customer_name = s.customer_name,
order_date = s.order_date,
nation_name = s.nation_name,
region_name = s.region_name
WHEN NOT MATCHED AND operation = 'I' THEN
INSERT ( customer_key,
customer_name,
order_key,
Chapter 8
parallelization