Snowflake

The most powerful role is : ACCOUNTADMIN role.

Never create objects using the ACCOUNTADMIN role, instead choose a role with fewer privileges that will allow you to perform the required tasks.

The snowflake web interface is called Snowsight

We can use the SYSADMIN role to create a database named ECOMMERCE_DB

use role SYSADMIN;
create database ECOMMERCE_DB;
create schema ORDERS;
create warehouse ECOMMERCE_WH with warehouse_size='XSMALL';

Using the snowsight user interface vs executing commands.

Staging a CSV File

To start building the data pipeline required by the ecommerce app, we must have a CSV file ready with data to ingest.

The ecommerce app collects daily orders and saves them to csv file named -

orders-2025-07-07.csv

To ingest it into the db, we need to upload the file to snowflake stage.

A stage is a snowflake object that points to the location of data files in cloud storage.

Snowflake supports 2 types of stages :

External Stage - AWS S3, google cloud storage, microsoft azure
Internal Stage - Stored in the cloud storage providder that hosts the snowflake account.

A snowflake named internal stage is a database object created in a schema.

use database ECOMMERCE_DB;
use schema orders;
create stage ORDERS_STAGE;

list @ORDERS_STAGE;

LOADING FILES INTO SNOWFLAKE STAGES VIA THE SNOWFLAKE CLI

LOADING DATA FROM A STAGED FILE INTO A TARGET TABLE.

SELECT  $1, $2, $3, $4, $5 FROM @ORDERS_STAGE;

Snowflake supports structured ( csv, tab-separated values ) and semi structured ( json, orc, parquet, xml ) file format.

Merging data from the staging table into the target table.

use database ecommerce_db;

use schema orders;

create table customer_orders( 
    customer varchar,
    order_date date,
    delivery_date,
    baked_good_type varchar,
    quality number,
    source_file_name varchar,
    load_ts timestamp
);

Merge command :

merge into CUSTOMER_ORDERS tgt
using ORDERS_STG as src 
on src.customer = tgt.customer 
  and src.delivery_date = tgt.delivery_date 
  and src.baked_good_type = tgt.baked_good_type
when matched then 
  update set tgt.quantity = src.quantity, 
    tgt.source_file_name = src.source_file_name, 
    tgt.load_ts = current_timestamp()
when not matched then
  insert (customer, order_date, delivery_date, baked_good_type, 
    quantity, source_file_name, load_ts)
  values(src.customer, src.order_date, src.delivery_date, 
    src.baked_good_type, src.quantity, src.source_file_name,
    current_timestamp());

NOTE Unlike in other popular relational databases, you don’t have to define a primary key when creating a table in Snowflake.

Transforming Data with SQL commands

The customer_orders table contains the periodically ingested data. The manager only requires the customer_orders summary.

use database ECOMMERCE_DB;
use schema ORDERS;
create table SUMMARY_ORDERS(
  delivery_date date,
  baked_good_type varchar,
  total_quantity number
);

Query to View Customer Orders.

select delivery_date, baked_good_type, sum(quantity) as total_quantity
  from CUSTOMER_ORDERS
  group by all;

Inserting summarized data into the table using query.

truncate table summary_orders;

insert into SUMMARY_ORDERS(delivery_date, baked_good_type, total_quantity)
  select delivery_date, baked_good_type, sum(quantity) as total_quantity
  from CUSTOMER_ORDERS
  group by all;

Automating the process with tasks

Truncate the staging table.
Load data from the internal stage into the staging table using the COPY command.
Merge data from the staging table into the target table.
Truncate the summary table.
Insert summarized data into the summary table.

Granting execute task role in sysadmin role

use role accountadmin;
grant execute task on account to role sysadmin;
use role sysadmin;

To execute the task :

execute task PROCESS_ORDERS;

To view the task execution history :

task_history()


select *
  from table(information_schema.task_history())
  order by scheduled_time desc;