DEMO: Error Handling while moving data from staging table to base table | Snowflake| VCKLY Tech

In this video, I am going to show you how to handle errors while moving/loading data from stage table to base table in snowflake cloud data warehouse using Multi Table Insert Command and Error handling conversion functions.
Steps:
1: Create a staging table.
2: Create a base table.
3: Create an error table.
4: Preparing/Inserting sample records into staging table.
5: Loading data from stage table to base table using multi-table insert command.
6: Verify Data
7: Cleanup Tables

--Step 1: Create a staging table
CREATE OR REPLACE TABLE STG_FIN_SALES
(
SALE_TIMESTAMP string, -- timestamp
ITEM_SKU string, -- number
PRICE string, -- number(10,2)
IS_TAXABLE string, -- boolean
COMMENTS string -- string
);
--Step 2: Create a base table
CREATE OR REPLACE TABLE FIN_SALES
(
SALE_TIMESTAMP timestamp NOT NULL,
ITEM_SKU number NOT NULL,
PRICE number(10,2) NOT NULL,
IS_TAXABLE boolean NOT NULL,
COMMENTS string NOT NULL
);
--Step 3: Create an error table
CREATE OR REPLACE TABLE FIN_SALES_ERR(
SALE_TIMESTAMP string,
ITEM_SKU string,
PRICE string,
IS_TAXABLE string,
ERROR_MSG string, -- extra column to capture error details
COMMENTS string
);
--Step: 4 Preparing/Inserting sample records into staging table
insert into stg_fin_sales
(SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, COMMENTS)
values
('2020-17-17 18:21:34', '2S3289', '$3.42' , 'Foo' , 'Bad row --all columns.'),
('2020-17-03 18:21:56', '9O832' , '1.41' , 'FALSE', 'Bad row: SALE_TIMESTAMP has the month and day transposed, ITEM_SKU has a capital "O" instead of a zero.'),
('2020-03-17 18:22:03', '7O242' , '2.99' , 'T' , 'Bad row: ITEM_SKU has a capital "O" instead of a zero.'),
('2020-03-17 18:22:10', '53921' , '$6.25', 'F' , 'Bad row: PRICE should not have a dollar sign.'),
('2020-03-17 18:22:17', '90210' , '2.49' , 'Foo' , 'Bad row: IS_TAXABLE cannot be converted to true or false'),
('2020-03-17 18:22:24', '80386' , '1.89' , '1' , 'Good row.');

--Step 5: Loading data from stage table to base table using multi-table inserts
--Error-handling Conversion Functions

INSERT FIRST
WHEN row_status = 'Bad Row'
THEN
into FIN_SALES_ERR
(SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, ERROR_MSG,COMMENTS)
values
(SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, NVL(SALE_TIMESTAMP_X_ERR,' ')||NVL(ITEM_SKU_X_err,' ')||NVL(PRICE_X_err,' ')||NVL(IS_TAXABLE_X_err,' '),COMMENTS)
ELSE
into FIN_SALES
(SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, COMMENTS)
values
(SALE_TIMESTAMP_X, ITEM_SKU_X, PRICE_X, IS_TAXABLE_X, COMMENTS)
select
case when SALE_TIMESTAMP_X is NULL THEN ' {SALE_TIMESTAMP: Invlaid date} 'else NULL end as SALE_TIMESTAMP_X_err,
case when ITEM_SKU_X is NULL THEN ' {ITEM_SKU: Invlaid Number} ' else NULL end as ITEM_SKU_X_err,
case when PRICE_X is NULL THEN ' {PRICE: Invlaid Price} ' else NULL end as PRICE_X_err,
case when IS_TAXABLE_X is NULL THEN ' {IS_TAXABLE: Invlaid Flag} ' else NULL end as IS_TAXABLE_X_err,
SALE_TIMESTAMP_X,
ITEM_SKU_X,
PRICE_X,IS_TAXABLE_X,
COMMENTS,
SALE_TIMESTAMP,
ITEM_SKU,
PRICE,
IS_TAXABLE,
CASE
WHEN (SALE_TIMESTAMP_X is null and SALE_TIMESTAMP is not null) or
(ITEM_SKU_X is null and SALE_TIMESTAMP is not null) or
(PRICE_X is null and PRICE is not null) or
(IS_TAXABLE_X is null and IS_TAXABLE is not null) THEN 'Bad Row'
ELSE 'Good Row'
END row_status
from ( select
try_to_timestamp (SALE_TIMESTAMP) as SALE_TIMESTAMP_X,
try_to_number (ITEM_SKU, 10, 0) as ITEM_SKU_X,
try_to_number (PRICE, 10, 2) as PRICE_X,
try_to_boolean (IS_TAXABLE) as IS_TAXABLE_X,
COMMENTS,
SALE_TIMESTAMP,
ITEM_SKU,
PRICE,
IS_TAXABLE
from STG_FIN_SALES
);

​
​-- Step 6: check bad/error records. Verify the comments columns
​
select * from fin_sales_err;
--check good records
select * from fin_sales;
select error_msg from fin_sales_err;

-- Step 7: Cleanup tables

drop table stg_fin_slaes;
drop table fin_base;
drop table fin_base_err;
-- For more details,
https://docs.snowflake.net/manuals/sql-reference/sql/insert-multi-table.html
https://docs.snowflake.com/en/sql-reference/functions-conversion.html#error-handling-conversion-functions


#snowflake#vcklytech#dwh#datacloud#sql

Видео DEMO: Error Handling while moving data from staging table to base table | Snowflake| VCKLY Tech канала VCKLY Tech