Summary
Snowflake ETL Using Pipe, Stream & Task
Building a complete ETL (or ETL) Workflow,or we can say data pipeline, for Snowflake Data Warehouse using snowpipe, stream and task objects. There are many ETL or ELT tools available and many of the article talks on theoritical ground, but this blog and episode-19 will cover everything needed by a snowflake developer.
Use Case (domain) for ETL
We are going to use a simple and commonly used domain and we can call it customer sales. In this domain, we have customer data set and item data set. All the purchases and transaction made are recorded into a order data set.
This data set is captured in RDBMS system and it flows to Snowflake Data Warehouse system.
- Customer Data - Master Data
- Item Data - Master Data
- Order Data - Transactional or Fact Data
Watch E2E Snowflake ETL Demo
You can watch the complete hands on video tutorial
Create Database & Schemas
The first step is to create database and schemas where all our tables and other objects will reside.
create database ch19;
create or replace schema landing_zone;
create or replace schema curated_zone;
create or replace schema consumption_zone;
Create Order Table (Landing Zone)
Now all our schemas are create, we must change the context to ch19.landing_zone and create customer transient table.
create or replace transient table landing_item (
item_id varchar,
item_desc varchar,
start_date varchar,
end_date varchar,
price varchar,
item_class varchar,
item_CATEGORY varchar
) comment ='this is item table with in landing schema';
create or replace transient table landing_customer (
customer_id varchar,
salutation varchar,
first_name varchar,
last_name varchar,
birth_day varchar,
birth_month varchar,
birth_year varchar,
birth_country varchar,
email_address varchar
) comment ='this is customer table with in landing schema';
create or replace transient table landing_order (
order_date varchar,
order_time varchar,
item_id varchar,
item_desc varchar,
customer_id varchar,
salutation varchar,
first_name varchar,
last_name varchar,
store_id varchar,
store_name varchar,
order_quantity varchar,
sale_price varchar,
disount_amt varchar,
coupon_amt varchar,
net_paid varchar,
net_paid_tax varchar,
net_profit varchar
) comment ='this is order table with in landing schema';
create or replace file format my_csv_vi_webui
type = 'csv'
compression = 'auto'
field_delimiter = ','
record_delimiter = '\n'
skip_header = 1
field_optionally_enclosed_by = '\042'
null_if = ('\\N');
In part-01, we have created the 3 schemas & tables in landing schema. Now we will create tables for curated zone and also load data using standard SQL statements.
SQL Scripts - Part 01 to Part 07
- Part-01 covers curated Zone SQL Script & Data Loading
- Part-02 covers curated Zone SQL Script & Data Loading
- Part-03 covers consumption Zone SQL Script & Data Loading
- Part-04 covers stage and pipe creation script for landing layer
- Part-05 stream and task creation for curated layers
- Part-06 stream and task cration for consumption layer
- Part-07 Lets validate the merge statements (Insert/Update)
Sample Data Files
First Time Data Load
Customer History Data
CUSTOMER_ID,SALUTATION,FIRST_NAME,LAST_NAME,BIRTH_DAY,BIRTH_MONTH,BIRTH_YEAR,BIRTH_COUNTRY,EMAIL_ADDRESS
AAAAAAAAMKJPHPBA,Dr.,Christopher,Schroeder,10,2,1931,SLOVAKIA,Christopher.Schroeder@McglO.org
AAAAAAAAONMOGPBA,Miss,Rosalinda,Pratt,1,9,1988,MALTA,Rosalinda.Pratt@l47gdNxCbBJNb.com
AAAAAAAAGPMHJPBA,Miss,Bernice,Brooks,8,9,1932,SINGAPORE,Bernice.Brooks@qjT0Snj.com
AAAAAAAAFEKPGPBA,Dr.,Melodie,Roman,3,12,1938,FRENCH GUIANA,Melodie.Roman@m8UYsygr.com
AAAAAAAANIEOPIAA,Sir,James,Laplante,2,7,1925,HUNGARY,James.Laplante@C8t.org
AAAAAAAAAABCLABA,Mr.,Michael,Marks,24,12,1959,RUSSIAN FEDERATION,Michael.Marks@I8soiNKe.com
AAAAAAAAJDFPBLAA,Ms.,Lizzie,Medley,11,11,1972,JORDAN,Lizzie.Medley@1yX3At.org
AAAAAAAAJFBBDLAA,Mr.,Oren,Alonzo,19,1,1957,PANAMA,Oren.Alonzo@2dpfyhVYrClKUDXN.edu
AAAAAAAAJLDFHPBA,Dr.,Michael,Macon,18,11,1963,BRAZIL,Michael.Macon@8KTcgloAXomzmoLj.org
AAAAAAAACMHFIPBA,Mr.,Victor,Mendez,5,10,1968,ISRAEL,Victor.Mendez@LQHgstNsYSjC4A.edu
AAAAAAAAFOKMIKCA,Miss,Deborah,Willis,23,5,1963,GREECE,Deborah.Willis@C.edu
AAAAAAAANOKIDJBA,Ms.,Josephine,Blais,5,1,1967,LATVIA,Josephine.Blais@D8q5qg.org
AAAAAAAADDAEDJBA,Miss,Dorothy,Duke,18,10,1978,MOROCCO,Dorothy.Duke@T.org
AAAAAAAAILLOEJBA,Sir,Jose,Campbell,25,4,1960,ERITREA,Jose.Campbell@XSmvThcV8mI9GZK.edu
AAAAAAAALFGEFJBA,Dr.,Alfred,Hair,27,8,1987,SRI LANKA,Alfred.Hair@bPgu094OMYmtQ.org
AAAAAAAANPGJFJBA,Mr.,Walter,Milligan,1,8,1963,NEPAL,Walter.Milligan@XGgCXa.com
AAAAAAAAGLNDLABA,Mrs.,April,Russo,19,10,1947,ECUADOR,April.Russo@SQCflL3mETetLr.edu
AAAAAAAABAIMCLAA,,,,16,4,,COMOROS,
AAAAAAAAFCFKDLAA,Miss,Debra,Connors,26,4,1989,ZIMBABWE,Debra.Connors@G7JagshMAvaZ.edu
AAAAAAAAGCKCCLAA,Ms.,Debra,Williams,6,6,1930,QATAR,Debra.Williams@SNBiej2Nz2v.com
Item History Data
ITEM_ID,ITEM_DESC,START_DATE,END_DATE,PRICE,ITEM_CLASS,ITEM_CATEGORY
AAAAAAAAEAHIAAAA,Adults could exercise empty copies. Magazines u,1999-10-28,2001-10-26,3.76,womens watch,Jewelry
AAAAAAAAMCGKEAAA,Cheap parts can help totally professional devices. Available coins drop mainly. Con,1997-10-27,1999-10-27,1.52,pop,Music
AAAAAAAACAGKEAAA,Together open times see here feet. Never alive standards support cars; patients might,1997-10-27,1999-10-27,8.60,science,Books
AAAAAAAAGFGKEAAA,"Examples can take. Always cultural passengers could not go only particular international initiatives. Fixed, military times move. Absolute studies would not join vast, national",1997-10-27,1999-10-27,0.89,womens,Shoes
AAAAAAAACJGKEAAA,"Likely, corporate sons assume. Twin days ought to assume about a feet. Secure conditions want full, good rights. Individuals shall not prepare exactly however universal minutes. Nearly other le",1999-10-28,2001-10-26,3.25,musical,Electronics
AAAAAAAAABHKEAAA,Banks might cause ever for a notes. More likely orde,2001-10-27,,1.10,pants,Men
AAAAAAAABDHKEAAA,Else british streets may change to a demands. Conditions leave yet uncertain inte,1997-10-27,,1.64,flatware,Home
AAAAAAAAKKHKEAAA,Children die more yet only figures. Programmes could lear,1997-10-27,2000-10-26,4.89,automotive,Electronics
AAAAAAAACIIKEAAA,More impossible directors used to control together multiple notes. Trees stay though once very reforms. Only ears place actually. There warm elements settle rarely men. Ad,1997-10-27,2000-10-26,2.04,scanners,Electronics
AAAAAAAAEHGKEAAA,"Economic, strong results mind even much difficult bits. Successful purposes like so. Little persons used to find more so",1997-10-27,1999-10-27,1.20,school-uniforms,Children
AAAAAAAACIHKEAAA,Conceivably educational problems may sleep. Details shall,1999-10-28,2001-10-26,2.26,accent,Home
AAAAAAAAKOJHAAAA,"Entire, empty lights head sharply political initiatives. Current, fundamenta",1997-10-27,1999-10-27,6.68,sailing,Sports
AAAAAAAAECGKEAAA,Strange pupils might achieve inside a things; results escort especially in a questions; well swiss links might reconstruct afterwards on a tools. Activities score al,1997-10-27,2000-10-26,2.07,country,Music
AAAAAAAACFHKEAAA,"Beliefs face too police. Years might talk original, christian songs. Entirely old officers might avoid typically. More new problems get husbands. Measures should make today visitors. Then jun",2001-10-27,,16.90,mens,Shoes
AAAAAAAAMDGKEAAA,"Only, particular numbers could not allow now middle children. There principal costs cannot look days; common, standard obligations shall occu",1997-10-27,2000-10-26,3.50,dresses,Women
AAAAAAAAEMIKEAAA,Deputies might not differ so able ways. Simultane,1997-10-27,2000-10-26,3.68,sports,Books
AAAAAAAAKLOJAAAA,"Giant, native principles shall not lead no doubt",1997-10-27,2000-10-26,7.37,womens,Shoes
AAAAAAAAMLKBGAAA,"Patients shall not contribute attractive, new hills. More hot occupations pick as aggressive organisms. Very funny deaths should think even. Relevant things might stand",1997-10-27,1999-10-27,95.71,reference,Books
AAAAAAAAGFLBGAAA,"Then central cars cultivate once tomorrow widespread women. Long things used to balance major, slight rights. Numbers get brilliant forests. ",2000-10-27,,3.13,rock,Music
AAAAAAAAGKJHAAAA,Running hopes cannot ,2000-10-27,,6.83,sports-apparel,Men
AAAAAAAAAHEKEAAA,"High, previous managers tax only fortunes. Bad, square parts consider too critical, symp",2001-10-27,,81.69,personal,Electronics
Order History Data
ORDER_DATE,ORDER_TIME,ITEM_ID,ITEM_DESC,CUSTOMER_ID,SALUTATION,FIRST_NAME,LAST_NAME,STORE_ID,STORE_NAME,ORDER_QUANTITY,SALE_PRICE,DISOUNT_AMT,COUPON_AMT,NET_PAID,NET_PAID_TAX,NET_PROFIT
2000-10-27,19:47:34 PM,AAAAAAAAHOJBGAAA,There intellectual resources would feel at least despite a rules; however complex hands may stay. Able rates leave more fundamental issues. Nights shall point then. Instead young others leave,AAAAAAAAIFHBGCBA,Dr.,Thomas,Myers,AAAAAAAAGOCAAAAA,able,11,31.38,0.00,0.00,345.18,352.08,44.99
2000-10-27,8:35:14 AM,AAAAAAAAOOJBGAAA,"Mysterious, powerful studies might integrate thus close reasons; relatively new skills improve all right. Brig",AAAAAAAAOGOCENCA,Miss,Diamond,Dew,AAAAAAAAIKBAAAAA,ese,52,1.05,0.00,0.00,54.60,56.78,-4671.68
2000-10-27,14:34:17 PM,AAAAAAAAJMJBGAAA,Ago likel,AAAAAAAANEPMMDDA,Mrs.,Sherri,Farmer,AAAAAAAAMACAAAAA,ese,88,43.02,0.00,0.00,3785.76,3899.33,-2475.44
2000-10-27,10:37:1 AM,AAAAAAAAHOJBGAAA,There intellectual resources would feel at least despite a rules; however complex hands may stay. Able rates leave more fundamental issues. Nights shall point then. Instead young others leave,AAAAAAAADJPLPABA,Dr.,Matthew,Delatorre,AAAAAAAALBBAAAAA,pri,88,28.78,0.00,0.00,2532.64,2633.94,113.52
2000-10-27,13:31:39 PM,AAAAAAAAHOJBGAAA,There intellectual resources would feel at least despite a rules; however complex hands may stay. Able rates leave more fundamental issues. Nights shall point then. Instead young others leave,AAAAAAAADHJIDJAA,Ms.,Sarah,Kellum,AAAAAAAANDDAAAAA,n st,57,4.47,178.35,178.35,76.44,78.73,-2116.92
2000-10-27,19:24:56 PM,AAAAAAAAJMJBGAAA,Ago likel,AAAAAAAAHGAKILBA,Ms.,Liza,Walters,AAAAAAAAHOEAAAAA,anti,36,17.30,0.00,0.00,622.80,635.25,-716.76
2000-10-27,10:18:13 AM,AAAAAAAAPMJBGAAA,Tiny opportunities see later beautiful entr,AAAAAAAAKLPPPABA,Ms.,Ellen,Cole,AAAAAAAAHNFAAAAA,anti,75,82.84,0.00,0.00,6213.00,6337.26,800.25
2000-10-27,15:29:15 PM,AAAAAAAAPMJBGAAA,Tiny opportunities see later beautiful entr,AAAAAAAAPEPJPCBA,Sir,Hobert,Grant,AAAAAAAAADFAAAAA,eing,8,20.57,0.00,0.00,164.56,179.37,40.24
2000-10-27,13:20:37 PM,AAAAAAAAEPJBGAAA,Concepts will end really eyes; cars may sign too main areas. Improved bottles end onl,AAAAAAAADPHOACDA,Sir,Bryan,Wick,AAAAAAAAKLEAAAAA,bar,1,38.01,16.72,16.72,21.29,22.56,-74.50
2000-10-27,20:38:57 PM,AAAAAAAAONJBGAAA,Reasons get. Minu,AAAAAAAAGMMPFNAA,Dr.,Numbers,Heard,AAAAAAAAEDBAAAAA,eing,68,15.65,0.00,0.00,1064.20,1117.41,49.64
2000-10-27,17:21:43 PM,AAAAAAAAAMJBGAAA,"Surprised teams go. Mental, forward services would come eventually; in add",AAAAAAAAKDOGOJDA,Sir,Robert,Jones,AAAAAAAABJDAAAAA,pri,38,49.63,0.00,0.00,1885.94,2055.67,-1316.32
2000-10-27,16:3:14 PM,AAAAAAAANOJBGAAA,So sick men can prevail often contents; today ,AAAAAAAALONFHIDA,Dr.,John,Weaver,AAAAAAAAIJDAAAAA,bar,25,51.90,0.00,0.00,1297.50,1388.32,-633.50
2000-10-27,12:24:40 PM,AAAAAAAAONJBGAAA,Reasons get. Minu,AAAAAAAABFPIBDAA,Dr.,Michael,Green,AAAAAAAADNAAAAAA,ought,95,26.20,2439.22,2439.22,49.78,53.76,-7929.27
2000-10-27,10:1:41 AM,AAAAAAAALNJBGAAA,"Relations may not use neither. Low voices shall handle therefore for the institutions. Free teachers cannot state different shareholders. Giant, desirable standards hire small, various governments; l",AAAAAAAAGAHAILBA,Dr.,Alicia,Allen,AAAAAAAAHGDAAAAA,ought,73,42.14,0.00,0.00,3076.22,3137.74,-1438.10
2000-10-27,8:11:24 AM,AAAAAAAADPJBGAAA,A,AAAAAAAANOJHFEDA,Ms.,Helen,Nickerson,AAAAAAAAPLFAAAAA,ought,21,78.43,0.00,0.00,1647.03,1795.26,28.35
2000-10-27,17:29:13 PM,AAAAAAAACOJBGAAA,"Numbers feel practices. Only positive signs help about possible, open factors. Thank",AAAAAAAABDLMBKDA,Sir,Marshall,Juarez,AAAAAAAAAACAAAAA,able,6,68.64,0.00,0.00,411.84,411.84,-68.40
2000-10-27,19:47:15 PM,AAAAAAAADPJBGAAA,A,AAAAAAAAMOAHDIAA,Dr.,Albert,Woodard,AAAAAAAAECEAAAAA,bar,76,115.89,0.00,0.00,8807.64,9071.86,2565.76
2000-10-27,18:57:53 PM,AAAAAAAAGMJBGAAA,Primarily tough days explain more however com,AAAAAAAAINKHEOCA,Miss,Lyndsay,Johnson,AAAAAAAAAKBAAAAA,cally,4,4.45,0.00,0.00,17.80,18.69,-23.44
2000-10-27,14:32:56 PM,AAAAAAAALNJBGAAA,"Relations may not use neither. Low voices shall handle therefore for the institutions. Free teachers cannot state different shareholders. Giant, desirable standards hire small, various governments;
Delta Load Data Set
Customer Delta Data
Delta Data 01
CUSTOMER_ID,SALUTATION,FIRST_NAME,LAST_NAME,BIRTH_DAY,BIRTH_MONTH,BIRTH_YEAR,BIRTH_COUNTRY,EMAIL_ADDRESS
AAAAAAAAPOJJJDAA,Dr.,Neal,Moore,14,7,1977,TOGO,Neal.Moore@lMVxi20y.edu
Delta Data 02
CUSTOMER_ID,SALUTATION,FIRST_NAME,LAST_NAME,BIRTH_DAY,BIRTH_MONTH,BIRTH_YEAR,BIRTH_COUNTRY,EMAIL_ADDRESS
AAAAAAAALKBJCPAA,Miss,Antonio,Calvin,24,2,1930,NETHERLANDS ANTILLES,Antonio.Calvin@i4jK16aM7UIKKiZ.org
AAAAAAAAPOJJJDAA,Dr.,Neal,Moore,14,7,1978,TOGO,Neal.Moore@lMVxi20y.com
Item Delta Data
Delta Data 01
ITEM_ID,ITEM_DESC,START_DATE,END_DATE,PRICE,ITEM_CLASS,ITEM_CATEGORY
AAAAAAAACDLBXPPP,Increases back simply satisfactory telecommunications. Fre,1997-10-27,,0.81,loose stones,Jewelry
Delta Data 02
ITEM_ID,ITEM_DESC,START_DATE,END_DATE,PRICE,ITEM_CLASS,ITEM_CATEGORY
AAAAAAAAGCLBYPPP,Firmly far achievements could not prepare natural workers; names ought to live clearl,1997-10-27,,5.72,country,Music
AAAAAAAACDLBXPPP,Increases back simply satisfactory telecommunications. Fre,1997-10-27,,6.41,tennis,Sports
Order Delta Data
Delta Data 01
ORDER_DATE,ORDER_TIME,ITEM_ID,ITEM_DESC,CUSTOMER_ID,SALUTATION,FIRST_NAME,LAST_NAME,STORE_ID,STORE_NAME,ORDER_QUANTITY,SALE_PRICE,DISOUNT_AMT,COUPON_AMT,NET_PAID,NET_PAID_TAX,NET_PROFIT
2000-10-27,19:59:47 PM,AAAAAAAACDLBXPPP,Increases back simply satisfactory telecommunications. Fre,AAAAAAAAPOJJJDAA,Dr.,Neal,Moore,AAAAAAAAOGDAAAAA,eing,46,23.50,0.00,0.00,1081.00,1102.62,-3209.42
Delta Data 02
ORDER_DATE,ORDER_TIME,ITEM_ID,ITEM_DESC,CUSTOMER_ID,SALUTATION,FIRST_NAME,LAST_NAME,STORE_ID,STORE_NAME,ORDER_QUANTITY,SALE_PRICE,DISOUNT_AMT,COUPON_AMT,NET_PAID,NET_PAID_TAX,NET_PROFIT
2000-10-27,11:34:55 AM,AAAAAAAAGCLBYPPP,Firmly far achievements could not prepare natural workers; names ought to live clearl,AAAAAAAALKBJCPAA,Miss,Antonio,Calvin,AAAAAAAAAGFAAAAA,cally,73,104.37,0.00,0.00,7619.01,7923.77,1739.59
2000-10-27,19:59:47 PM,AAAAAAAACDLBXPPP,Increases back simply satisfactory telecommunications. Fre,AAAAAAAAPOJJJDAA,Dr.,Neal,Moore,AAAAAAAAOGDAAAAA,eing,46,300.50,0.00,0.00,1081.00,1102.62,-3209.42