UNIT TEST REPORT
BANK LOAN ANALYSIS
Prepared by: Guided by:
Dhriti Saluja Mr. Pritam Gorain
Atharva Raich
Archana Neelam
Akshata Garudkar
Charan Kumar Mekala
Chamanthula Nagaraju
Previewing script to split the files:
Describing integration object:
Verifying whether AWS_IAM_USER_ARN and External Id of integration object is
matching with our Role’s AWS_IAM_USER_ARN and External Id :
Previewing the code for Creation of external stage:
Checking data into External Stage:
Testing Auto ingestion of files into Snowflake using Snowpipe:
S1:- Reviewing all the data types of the table
S2 :- Reviewing the File Format
Name of file format: MY_CSV_FORMAT
S3 :- Reviewing the code for creating Snowpipe
For creating snowpipe we have used MY_S3_STAGE as our external stage and MY_CSV_FORMAT as our
file format.
S4 :- Checking Event notification
S5 :- To check that the data is loaded or not
Using this command, we can check the data that has been loaded by using snowpipe
Testing SCD Type 2:
Step 1. Reviewing all the data types used for creation of Raw table
Step 2. Reviewing all the data types used for creation of Landing table
Step 3. Reviewing all the data types used for creation of Staging table
Step 4. Stream to track changes in staging table and create delta
Task Flow
Flat Files
Raw_table
Landing_table
Landing_Stream
Staging_table
For First time, checking the data in raw_table
For First time, checking data in landing_table
For First time, checking data in stream (created on landing table)
For First time, checking data in staging_table
Scenario 2: New file loaded with 2 update records and 2 new records
Step 1. Reviewing the code for Truncating loan_raw table and reload with fresh data
Step 2. Populating data into Raw table with data_v2.csv file using my_loan_internalstage
Step 3. Now we have fresh data in Loan_raw. We will transfer the data from loan_raw to
Loan_landing table using below logic:
Step 4. Check stream
Step 5. Pushing the data from landing_stream to stagi
Column level security:
step1: Reviewing the code for creation of masking policy
Step 2: Masking policy on table
Step 3: Checking output for all roles other than sandbox
Step 4: Previewing the code for creation of role and grant permissions to the role
Step 5: Checking the output for the role sandbox
ROW LEVEL MASKING:
Step 1: Reviewing creation of table and loading the data into table
Step 2: Reviewing code for creation of secure view
Step 3: Reviewing code for creating roles and granting usages
Step 4: Checking output for the role male
Step 5: Checking output for the role female
Step 6: Checking row level masking for new user
Step 7: Checking Output for the new user
Schedular task:
We created a table called Bank_loan_analysis8 with structure with respective to the files structure
which are present in the s3 bucket in order to load files data into this created table.
We created a task called mytask_minute . This task will load all the files present in the s3 bucket to
table Bank_loan_analysis8 within a schedule time of 2 minutes.
This show tasks command will show all tasks present in entire account.
This alter task mytask_minute resume command will start or resume the task mytask_minute in
order to load files data to bank_loan_analysis8 table.
This alter task mytask_minute suspend command will suspend the task mytask_minute. This will
stop task .
After 2 minutes of execution of alter task mytask_minute resume command
we need to check data loaded into Bank_loan_analysis8 from s3 bucket or not, with this
select * from Bank_loan_analysis8 command.
It takes 2 minutes to load data because we mentioned schedule time equals to 2 minutes.
After getting data loaded into table, we need to suspend the task.
ERROR HANDLING
In this case we created a masking policy where we taken value datatype and return type as integer, but
actually we are applying on parameter which of varchar data type .Thats why we are
getting this error .
Rectification
That error can be resolved by making changes in masking policy by taking value datatype and return type as
varchar as that of parameter datatype on which we applying this masking policy.
