page206
292
Create records for a
DELETE
operation. Note that the important attributes are
order_key
and
operation
. The remainder will not be used for this example. However,
if logically deleting from a bitemporal model, then
stg_timestamp
would be used to set
the record
valid_to
date.
CREATE OR REPLACE TABLE base_customer_order_delete
(
customer_key NUMBER(38,0),
customer_name VARCHAR(25),
order_key NUMBER(38,0),
order_date DATE,
nation_name VARCHAR(25),
region_name VARCHAR(25),
operation VARCHAR(1),
stg_timestamp TIMESTAMP_NTZ
)
AS
SELECT customer_key,
customer_name,
order_key,
DATE_TRUNC ( 'DAY', current_date()),
nation_name,
region_name,
'D',
current_timestamp()
FROM base_customer_order
WHERE order_key BETWEEN 1000000000 AND 5000000000
LIMIT 100000;
Using our base data, now populate the raw or staging table,
stg_customer
_
order
.
INSERT OVERWRITE INTO stg_customer_order
SELECT *
FROM base_customer_order_insert
UNION ALL
SELECT *
FROM base_customer_order_update
Chapter 8
parallelization