Snowflake Explained Simply: From User Roles to Advanced Data Ingestion

Now that you have a solid grasp of the core architecture that makes Snowflake so powerful, it is time to dive into some major concepts. If you haven't already, head over to snowflake.com and set up a free trial account to follow along.

(Fast-forward 2 hours later...)

Before we run our very first query, there is a golden rule in Snowflake you must always follow: you must always tell your workspace exactly who you are, what database you are opening, and which folder you are working in. Setting this context ensures Snowflake knows exactly where to route your code and how to apply your security permissions. To keep things uniform, here is the exact setup we will be using throughout the rest of this guide:

// SET THE ROLES - ROLE, WAEHOUSE, DB, SCHEMA

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE COMPUTE_WH;
USE DATABASE KEFAA;
USE SCHEMA RAW;

Users And Roles In Snowflake

This is how Snowflake handles security and permissions, ensuring people only see the data they need for their specific job. Instead of giving permissions directly to individual Users which would become a nightmare to manage as a company grows, Snowflake grants permissions to Roles. Think of roles like security badges for different departments. For example, you can assign an Analyst role the specific keys to view sales performance charts, while blocking them from seeing confidential HR databases. Administrators simply hand these department badges to the users, making it incredibly easy to control who sees what across the entire company.

Creating Users and Roles

In practice, managing these permissions is incredibly flexible because you can do it visually through Snowflake's web interface or directly with code.

--Create the new user
CREATE USER alex PASSWORD = 'Password';

--Assign the analyst role to the user
GRANT ROLE analyst TO USER alex;

As shown in the image, an administrator can easily navigate to the Governance & security tab in the UI to click "Create user" or monitor active team accounts.

Databases and Schema

Logical Data Organization

This is how Snowflake structures and organizes information inside your account, acting like a filing cabinet system for your data. At the top level is your Account, which represents your entire company's environment. Inside the account sit Databases, which act like large drawers used to group high-level business categories (such as a sales database or a marketing database). Finally, each database is divided into smaller folders called Schemas, which are logical groupings used to hold the actual final data objects like your tables and views.

Snowflake Objects

This expands on the structure, showing how every resource lives inside a clear container hierarchy. At the high Account level, you manage global tools like users, roles, and virtual computers. When you open a specific Databaseand look inside a Schema, you find the actual working components of your data pipeline such as standard tables, views, loading steps (Stages and Pipes), and custom calculations (UDFs). Because every single one of these items is an independent object, administrators can use roles to grant highly specific privileges, like letting an analyst read data from a table while blocking them from changing its structural format.

Snowflake Table Types

Permanent Tables

This is the default option for your core company data. These tables stay in your account forever until you manually delete them, and they offer maximum protection with up to 90 days of Time Travel and full Fail-Safe backup recovery.

CREATE TABLE PERMANENT_TABLE
(
ID INT,
NAME VARCHAR(10)
);

Temporary Tables

These tables exist only for a single user's active session and vanish automatically the exact moment that user logs out. They are perfect for holding quick, middle-stage data during a cleanup process without cluttering your database.

CREATE TABLE TEMPORARY_TABLE
(
ID INT,
NAME VARCHAR(10)
);

Transient Tables

These tables stay in your system permanently like a regular table, but they do not include the expensive Fail-Safe backup protection layer. This makes them ideal for storing data that is useful to keep around but can easily be recreated if a major system crash happens, saving the company money on storage fees.

CREATE TABLE TRANSIENT_TABLE
(
ID INT,
NAME VARCHAR(10)
);

External Tables

These are read-only windows that let Snowflake look at files sitting completely outside of its system, such as inside a cloud storage bucket like Amazon S3. They allow teams to instantly query outside data files using standard SQL without wasting time or money moving those files into Snowflake storage.
(We'll look into this later)

In the Examples above, we named them by their formarts, eg 'TEMPORARY_TABLE', but in real-world where we have many tables, how do we know which type of table this is;
SHOW TABLES;

By running that command we can clearly see the exact kind of tables they are.

Views in Snowflake

We have 3 different types of views, and to see how they work/how to create them we first have to create a table we will use in defining these views.

//Create employee table
CREATE OR REPLACE TABLE employees(

id INTEGER, 
name VARCHAR(50),
department VARCHAR(50),
salary INTEGER

);

Insert some data

//Insert data into the table
INSERT INTO employees(id, name, department, salary)
VALUES  (1,'Rose','Hr',50000),
        (2,'Ian','IT',40000),
        (3,'Brian','Sales',35000),
        (4,'Lucy','Marketing',45000),
        (6,'Liam','Hr',50000),
        (5,'Des','IT',45000),
        (7,'Gideon','Sales',30000),
        (8,'Grace','Marketing',42000)
;

With that now we can begin looking at the different types of views.

Standard Views

• This is the default choice in Snowflake. It doesn't actually store any data; instead, it is just a saved SQL SELECT statement that runs fresh every time someone opens it. Anyone with access to the view can also look behind the scenes to see the exact code used to build it.

//CREATE A VIEW THAT AGGREGATES SALARY BY DEPARTMENT 
CREATE OR REPLACE VIEW employees_salary AS 
SELECT department,
    SUM(salary) as total_salary 
FROM employees
GROUP BY department;

Secure Views

• These are built specifically for privacy and data protection. They look and act like standard views, but they completely hide the underlying SQL code from unauthorized users so people cannot see how the data is being filtered. For added safety, Snowflake’s query optimizer handles them differently to prevent users from guessing hidden data through clever trial-and-error queries.

//Create a secue view called hr_employees to get only those from IT
CREATE OR REPLACE SECURE VIEW hr_employees AS
SELECT 
id, name, salary
FROM employees
WHERE department = 'IT';

Materialized Views

• Unlike the other types, this view behaves more like a physical table because it actually saves and stores the results of the query on a disk. It automatically updates itself whenever the raw data changes, making it perfect for speeding up heavy, repetitive reports that take too long to run from scratch.

//CREATE A MATERIALIZED VIEW THAT AGGREGATES SALARY BY DEPARTMENT 
CREATE OR REPLACE MATERIALIZED VIEW materialized_employees_salary AS 
SELECT department,
    SUM(salary) as total_salary 
FROM employees
GROUP BY department;

Stages in Snowflake

A stage acts like a digital loading dock where you temporarily store raw data files before moving them into your permanent Snowflake tables.

Think of it as a waiting room for files like csv or json, instead of trying to load raw files straight into a database table, you upload them to a Stage first, ensuring they are properly formatted and organized before the final data loading process begins.

To better understand the stages, lets first create a simple table then load data using a stage

// create a table first 
CREATE OR REPLACE TABLE customer(
id INT, 
name VARCHAR(50),
age INT, 
state VARCHAR(50)
);

Table Stage

• This is an internal storage area created automatically by Snowflake for every single table you make. It is wired exclusively to that specific table, meaning files uploaded here can only be loaded into that one target table and cannot be accessed by other parts of the database.

// aCCESS THE TABLE STAGE - it is automatically created
LIST @%customer;

User Stage

• This is a private, internal storage area created automatically for every individual user account. It acts like your personal digital folder where you can safely drop files to test out queries nobody else can access or see the files stored in your user stage.

// aCCESS THE USER STAGE - it is automatically created once you create a user
LIST @~;

Named Stage

This is a custom, highly flexible storage area that you create manually using SQL code. It can be built as an Internal stage (saved straight inside Snowflake) or an External stage (pointing to an outside cloud folder like Amazon S3), and because it is an independent object, it can be shared across multiple different teams and tables

Creating an internal named stage

//create an internal named stage 
CREATE OR REPLACE STAGE customer_stage;

After creating the stage, you can add your csv to the stage from eg local storage

The next step is loading the data you already saved in your stage into the table, this can be done manually or using an sql statement;

//LOad data from the stage into the table 
COPY INTO customer 
FROM @customer_stage 
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1);

File Format in Snowflake

This is an independent database object that acts like a translator, telling Snowflake exactly how to read and break down your incoming data files. Instead of manually typing out file rules (like how commas or headers are treated) every single time you load data, you save those settings once into a File Format object. As outlined on the slide, it natively handles a wide range of structures—including standard flat files like CSV, semi-structured formats like JSON and XML, and big-data columns like AVRO, ORC, and PARQUET. You can cleanly attach a file format directly to a specific Stage configuration or call it dynamically right inside your standard COPY INTO data pipelines.

Creating a csv File format

// CREATE A CSV FILE FORMAT.
CREATE FILE FORMAT csv_format 
TYPE = 'CSV'
FIELD_DELIMITER = ','
RECORD_DELIMITER = '\n'
SKIP_HEADER = 1;

Loading the customer data using this file format

//Load data from the stage into the table using file format
COPY INTO customer 
FROM @customer_stage 
FILE_FORMAT = (FORMAT_NAME = csv_format);

Notice that when loading, in contrast to the previous method we used where we had to define rules eg SKIP_HEADER, now we just mention the name of our file format since we already defined all the rules there.

Data Loading Approaches in Snowflake

Bulk Loading (using COPY)

• This is a manual, batch-based approach designed to handle massive piles of historical files all at once. Using the standard COPY INTO command, a running virtual warehouse manually pulls data files from your Internal Stages or External cloud folders (like AWS S3 or Azure Blob Storage) and drops them straight into your target table in large blocks.

Loading from a Local File System:

• This illustrates the exact path data files take when you move them manually from your own personal computer into a Snowflake database table. As mapped out in the diagram, this is always a two-step process:

• First, you must run a PUT command to upload the data files from your local storage system into an internal storage area, such as an Internal Named Stage, a User Stage, or a Table Stage.

• Second, once the files are sitting safely inside Snowflake's environment, you must run a COPY INTO statement to actually insert that staged data into your target table.

Continuous Loading (using Snowpipe)

• This is an automated, serverless approach designed for streaming real-time data feeds. Instead of waiting around to run batch commands, a Pipe object listens for new data arriving in a cloud folder and instantly pushes it into a fast streaming queue. Because Snowpipe runs on a specialized, background Ingest Service, it loads data incrementally the second it appears without needing a dedicated user-managed virtual warehouse.

What is Snowpipe?

• This is a specialized database object that houses the exact loading instructions needed to run automated, continuous data ingestion.

• As shown in the diagram, a Pipe essentially wraps a standard COPY INTO statement, acting as a link between a source stage and a target table.

• Whenever a new data file lands in a stage, it triggers an event that drops the file into Snowflake's secure Ingest Queue
  .

• The pipe then automatically processes the file and loads it into your table using Snowflake-managed computing power. Because it is an independent object, it can be easily paused or resumed at any time, and as a best practice, it runs most efficiently when processing compressed files sized between 10 MB and 100 MB.

Snowpipe REST Api

• This function allows external software applications to trigger continuous data loading into Snowflake without relying on automated cloud storage alerts.

• As illustrated in the diagram, when your custom application drops new files into your Cloud Provider Storage, it simultaneously makes a secure REST Call containing the exact file names directly to a specific Snowflake REST Endpoint. This alert instantly pings Snowflake's background, Server-less Loader, which picks up the specified data files and inserts them straight into your destination database table.

Snowpipe Auto-Ingest

• This function completely automates data loading by setting up an event-driven link between your cloud storage and your database. As shown in the diagram, streaming platforms like Apache Kafka continuously dump raw files into an External Cloud Storage bucket. The moment a new file lands, it triggers an automatic alert (like an S3 notification) that alerts Snowflake's background, Server-less Loader.

• To set this up in code, you simply create a pipe and include the configuration setting AUTO_INGEST = TRUE. This tells the system to automatically wake up, grab the incoming file data, and load it into your destination database table the second it arrives, requiring zero manual commands or user-managed warehouse power.

To fully understand batch loading using snowpipe, you should create an aws account and link with snowflake. For continous loading, check out this article on how to stream data from kafka into snowflake using snowpipe

[https://dev.to/kepha_m/from-kafka-to-clean-tables-building-a-confluent-snowflake-pipeline-with-streams-tasks-140d]