Snowflake - Data Loading and Transforming while Loading

You can get all snowflake Videos, PPTs, Queries, Interview questions and Practice files in my Udemy course for very less price.. I will be updating this content and will be uploading all new videos in this course.

My Snowflake Udemy Course:
https://www.udemy.com/course/snowflake-complete-course-for-clearing-interviews

I can be reachable on [email protected].
------------------------------------------------------------

//Create database
CREATE DATABASE IF NOT EXISTS MYDB;
USE DATABASE MYDB;

//Creating the table

CREATE OR REPLACE TABLE MYDB.PUBLIC.LOAN_PAYMENT (
"Loan_ID" STRING,
"loan_status" STRING,
"Principal" STRING,
"terms" STRING,
"effective_date" STRING,
"due_date" STRING,
"paid_off_time" STRING,
"past_due_days" STRING,
"age" STRING,
"education" STRING,
"Gender" STRING
);

SELECT * FROM PUBLIC.LOAN_PAYMENT; -- 0

//Loading the data from S3 bucket
COPY INTO PUBLIC.LOAN_PAYMENT
FROM s3://bucketsnowflakes3/Loan_payments_data.csv
file_format = (type = csv , field_delimiter = ',' , skip_header=1);

//Validate the data
SELECT * FROM PUBLIC.LOAN_PAYMENT;

//Check the count
SELECT COUNT(*) FROM PUBLIC.LOAN_PAYMENT; -- 500

============
Transforming Data while Loading
===============
// Create a Schema for External Stages

CREATE OR REPLACE SCHEMA MYDB.external_stages;

// Publicly accessible staging area
CREATE OR REPLACE STAGE MYDB.external_stages.aws_ext_stage
url='s3://bucketsnowflakes3';

// listing the files in external stage
list @MYDB.external_stages.aws_ext_stage;

//Case 1: Just Viewing Data from ext stage
select $1, $2, $3, $4, $5, $6 from @MYDB.external_stages.aws_ext_stage/OrderDetails.csv;

//Giving Alias Names to fields
select $1 as OID, $2 as AMT, $3 as PFT, $4 as QNT, $5 as CAT, $6 as SUBCAT
from @MYDB.external_stages.aws_ext_stage/OrderDetails.csv;

select $1 as OID, $4 as QNT, $2 as AMT from @MYDB.external_stages.aws_ext_stage/OrderDetails.csv;


// Transforming Data while loading

// Case 2: load only required fields

CREATE OR REPLACE TABLE MYDB.PUBLIC.ORDERS_EX (
ORDER_ID VARCHAR(30),
AMOUNT INT
);

COPY INTO MYDB.PUBLIC.ORDERS_EX
FROM (select s.$1, s.$2 from @MYDB.external_stages.aws_ext_stage s)
file_format= (type = csv field_delimiter=',' skip_header=1)
files=('OrderDetails.csv');

SELECT * FROM MYDB.PUBLIC.ORDERS_EX;


// Case3: applying basic transformation by using functions

CREATE OR REPLACE TABLE MYDB.PUBLIC.ORDERS_EX (
ORDER_ID VARCHAR(30),
PROFIT INT,
AMOUNT INT,
CAT_SUBSTR VARCHAR(5),
CAT_CONCAT VARCHAR(60),
PFT_OR_LOSS VARCHAR(10)
);

//Copy Command using a SQL function
COPY INTO MYDB.PUBLIC.ORDERS_EX
FROM (select
s.$1,
s.$3,
s.$2,
substring(s.$5,1,5),
concat($5,$6), -- or simply $5||$6
CASE WHEN s.$3 less_than= 0 THEN 'LOSS' ELSE 'PROFIT' END
FROM @MYDB.external_stages.aws_ext_stage s)
file_format= (type = csv field_delimiter=',' skip_header=1)
FILES=('OrderDetails.csv');

SELECT * FROM MYDB.PUBLIC.ORDERS_EX;

// Case 4: Loading sequence numbers in columns

// Create a sequence
create sequence seq1;

CREATE OR REPLACE TABLE MYDB.PUBLIC.LOAN_PAYMENT (
"SEQ_ID" number default seq1.nextval,
"Loan_ID" STRING,
"loan_status" STRING,
"Principal" STRING,
"terms" STRING,
"effective_date" STRING,
"due_date" STRING,
"paid_off_time" STRING,
"past_due_days" STRING,
"age" STRING,
"education" STRING,
"Gender" STRING
);

//Loading the data from S3 bucket
COPY INTO PUBLIC.LOAN_PAYMENT("Loan_ID", "loan_status", "Principal", "terms", "effective_date", "due_date",
"paid_off_time", "past_due_days", "age", "education", "Gender")
FROM s3://bucketsnowflakes3/Loan_payments_data.csv
file_format = (type = csv field_delimiter = ',' skip_header=1);

//Validate the data
SELECT * FROM PUBLIC.LOAN_PAYMENT;


// Case 5: Using auto increment

CREATE OR REPLACE TABLE MYDB.PUBLIC.LOAN_PAYMENT2 (
"LOAN_SEQ_ID" number autoincrement start 1001 increment 1,
"Loan_ID" STRING,
"loan_status" STRING,
"Principal" STRING,
"terms" STRING,
"effective_date" STRING,
"due_date" STRING,
"paid_off_time" STRING,
"past_due_days" STRING,
"age" STRING,
"education" STRING,
"Gender" STRING
);

//Loading the data from S3 bucket
COPY INTO PUBLIC.LOAN_PAYMENT2("Loan_ID", "loan_status", "Principal", "terms", "effective_date", "due_date",
"paid_off_time", "past_due_days", "age", "education", "Gender")
FROM s3://bucketsnowflakes3/Loan_payments_data.csv
file_format = (type = csv field_delimiter = ',' skip_header=1);

//Validate the data
SELECT * FROM PUBLIC.LOAN_PAYMENT2;