CREATE OR REPLACE STORAGE integration s3_int
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = S3
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN ='YOUR ROLE'
STORAGE_ALLOWED_LOCATIONS =('YOUR S3 BUCKET PATH');  

DESC INTEGRATION s3_int;

CREATE OR REPLACE STAGE BANK
URL ='YOUR S3 BUCKET PATH'  -- EXAMPLE: s3://your s3 bucket name/
file_format = BANK_CSV -- FILE FORMAT
storage_integration = s3_int;

LIST @BANK;

SHOW STAGES;

--CREATE SNOWPIPE THAT RECOGNISES CSV THAT ARE INGESTED FROM EXTERNAL STAGE AND COPIES THE DATA INTO EXISTING TABLE

--The AUTO_INGEST=true parameter specifies to read 
--- event notifications sent from an S3 bucket to an SQS queue when new data is ready to load.

-- CREATING PIPELINE FOR DISTRICT TABLE
CREATE OR REPLACE PIPE BANK_SNOWPIPE_DISTRICT AUTO_INGEST = TRUE AS
COPY INTO "BANK"."BANKING"."DISTRICT" --yourdatabase -- your schema ---your table
FROM '@bank/District/' --your database name s3 bucket subfolde4r name
FILE_FORMAT = BANK_CSV;

-- CREATING PIPELINE FOR ACCOUNT TABLE

CREATE OR REPLACE PIPE BANK_SNOWPIPE_ACCOUNT AUTO_INGEST = TRUE AS
COPY INTO "BANK"."BANKING"."ACCOUNT" --yourdatabase -- your schema ---your table
FROM '@bank/Account/' --your database name s3 bucket subfolde4r name
FILE_FORMAT = BANK_CSV;

-- CREATING PIPELINE FOR CARD TABLE

CREATE OR REPLACE PIPE BANK_SNOWPIPE_CARD AUTO_INGEST = TRUE AS
COPY INTO "BANK"."BANKING"."CARD" --yourdatabase -- your schema ---your table
FROM '@bank/Card/' --your database name s3 bucket subfolde4r name
FILE_FORMAT = BANK_CSV;

-- CREATING PIPELINE FOR CLIENT TABLE

CREATE OR REPLACE PIPE BANK_SNOWPIPE_CLIENT AUTO_INGEST = TRUE AS
COPY INTO "BANK"."BANKING"."CLIENT" --yourdatabase -- your schema ---your table
FROM '@bank/Client/' --your database name s3 bucket subfolde4r name
FILE_FORMAT = BANK_CSV;

-- CREATING PIPELINE FOR DISPOSITION TABLE

CREATE OR REPLACE PIPE BANK_SNOWPIPE_DISPOSITION AUTO_INGEST = TRUE AS
COPY INTO "BANK"."BANKING"."DISPOSITION" --yourdatabase -- your schema ---your table
FROM '@bank/disp/' --your database name s3 bucket subfolde4r name
FILE_FORMAT = BANK_CSV;

-- CREATING PIPELINE FOR LOAN TABLE

CREATE OR REPLACE PIPE BANK_SNOWPIPE_LOAN AUTO_INGEST = TRUE AS
COPY INTO "BANK"."BANKING"."LOAN" --yourdatabase -- your schema ---your table
FROM '@bank/Loan/' --your database name s3 bucket subfolde4r name
FILE_FORMAT = BANK_CSV;

-- CREATING PIPELINE FOR ORDER_LIST TABLE

CREATE OR REPLACE PIPE BANK_SNOWPIPE_ORDER_LIST AUTO_INGEST = TRUE AS
COPY INTO "BANK"."BANKING"."ORDER_LIST" --yourdatabase -- your schema ---your table
FROM '@bank/Order/' --your database name s3 bucket subfolde4r name
FILE_FORMAT = BANK_CSV;

-- CREATING PIPELINE FOR TRANSACTIONS TABLE

CREATE OR REPLACE PIPE BANK_SNOWPIPE_TRANSACTIONS AUTO_INGEST = TRUE AS
COPY INTO "BANK"."BANKING"."TRANSACTIONS" --yourdatabase -- your schema ---your table
FROM '@bank/Trnx/' --your database name s3 bucket subfolde4r name
FILE_FORMAT = BANK_CSV;

SHOW PIPES;

ALTER PIPE BANK_SNOWPIPE_DISTRICT REFRESH;

ALTER PIPE BANK_SNOWPIPE_DISPOSITION REFRESH;

ALTER PIPE BANK_SNOWPIPE_ACCOUNT REFRESH;

ALTER PIPE BANK_SNOWPIPE_CARD REFRESH;

ALTER PIPE BANK_SNOWPIPE_ORDER_LIST REFRESH;

ALTER PIPE BANK_SNOWPIPE_CLIENT REFRESH;

ALTER PIPE BANK_SNOWPIPE_LOAN REFRESH;

ALTER PIPE BANK_SNOWPIPE_TRANSACTIONS REFRESH;