Programmatic Loading in Snowflake
Overview
Programmatic loading means using application code, such as Python or REST APIs, to control a Snowflake data load.
Snowflake still performs the actual loading through native capabilities such as:
PUTCOPY INTO- stages
- file formats
- Snowpipe
Programmatic loading adds the control layer around those commands.
Snowflake loads the data. Programmatic loading controls how, when, and why the load runs.
Why Programmatic Loading Is Needed
Manual COPY INTO is enough for simple practice loads. Production pipelines usually need more control.
Common reasons to use programmatic loading:
- load multiple files or tables in a defined order
- run validation before loading
- decide whether to continue, stop, or retry
- capture success and failure details in audit tables
- integrate with schedulers such as Airflow, cron, Prefect, or Dagster
- build metadata-driven ingestion frameworks
- send alerts when something fails
Where It Fits
Source file / API / application
|
v
Python / Snowpark / SQL API
|
v
PUT / COPY INTO / Snowpipe
|
v
Snowflake table
The code controls the workflow. Snowflake handles the scalable ingestion.
Programmatic Loading Options
Snowflake supports three common programmatic approaches:
| Approach | Best For | Main Idea |
|---|---|---|
| Python Connector | Batch orchestration and controlled loads | Python executes Snowflake SQL commands |
| Snowpark Python | Transformations inside Snowflake | Python DataFrame code runs on Snowflake compute |
| Snowflake SQL API | REST-based integrations | Applications execute SQL through HTTPS endpoints |
1. Programmatic Loading Using Python Connector
What It Is
The Snowflake Python Connector lets Python programs connect to Snowflake and execute SQL commands such as PUT, COPY INTO, SELECT, INSERT, and CREATE TABLE.
This is one of the most common approaches for production batch ingestion because it gives developers full control over the load flow.
Typical Use Cases
- Excel or CSV file loading
- batch ETL pipelines
- scheduler-driven ingestion
- metadata-driven
COPY INTOgeneration - row count validation
- audit logging
- retry and exception handling
Supporting File
Setup: Create Database, Schema, and Table
-- Step 1: Create database
CREATE OR REPLACE DATABASE PRD_COBRA_PLANNING_DB;
-- Step 2: Create schema
CREATE OR REPLACE SCHEMA PRD_COBRA_PLANNING_DB.PRD_COBRA_PLANNING_DB_SCH;
-- Step 3: Use database and schema
USE DATABASE PRD_COBRA_PLANNING_DB;
USE SCHEMA PRD_COBRA_PLANNING_DB_SCH;
-- Step 4: Create target table
CREATE OR REPLACE TABLE COBRA_EMPLOYEES (
GID STRING,
NAME STRING,
TYPE STRING,
MANAGER STRING,
JOINING_DATE DATE,
STAKEHOLDER STRING,
DEPARTMENT STRING,
TEAM STRING,
COBRA_ROLES STRING,
ML_PPM_ROLE STRING,
CAPACITY STRING,
LEAVING_DATE DATE,
RI_CATALOG_ROLE STRING,
COMPETENCY_LEVEL STRING
);
Table Stage Reminder
Snowflake automatically creates an internal table stage for every table.
For the COBRA_EMPLOYEES table, the table stage is:
@%COBRA_EMPLOYEES
This stage can be used by PUT and COPY INTO.
| Stage Reference | Meaning |
|---|---|
@~ | user stage |
@%TABLE_NAME | table stage |
@NAMED_STAGE | named internal or external stage |
Simple Example: Excel to Snowflake Table
This example performs four actions:
- read the Excel file
- convert it to CSV
- upload the CSV to the table stage
- load the staged file into
COBRA_EMPLOYEES
Install the required Python packages:
pip install snowflake-connector-python pandas openpyxl
import pandas as pd
import snowflake.connector
# If running from this module folder, keep the file in "supporting docs".
# Otherwise, replace this with the folder where you downloaded the Excel file.
base_dir = "/your/file/location"
excel_file = base_dir + "/cobra_employees.xlsx"
csv_file = base_dir + "/cobra_employees.csv"
# Step 1: Read Excel file
df = pd.read_excel(excel_file)
# Step 2: Convert Excel to CSV
df.to_csv(csv_file, index=False)
# Step 3: Connect to Snowflake
conn = snowflake.connector.connect(
user='YOUR_SNOWFLAKE_USER',
password='YOUR_SNOWFLAKE_PASSWORD',
account='YOUR_SNOWFLAKE_ACCOUNT',
warehouse='COMPUTE_WH',
database='PRD_COBRA_PLANNING_DB',
schema='PRD_COBRA_PLANNING_DB_SCH'
)
cursor = conn.cursor()
# Step 4: Upload CSV file to Snowflake table stage
put_sql = f"""
PUT 'file://{csv_file}'
@%COBRA_EMPLOYEES
AUTO_COMPRESS=TRUE
OVERWRITE=TRUE
"""
cursor.execute(put_sql)
# Step 5: Load staged CSV file into Snowflake table
copy_sql = """
COPY INTO COBRA_EMPLOYEES
FROM @%COBRA_EMPLOYEES
FILE_FORMAT = (
TYPE = CSV
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
)
ON_ERROR = 'CONTINUE'
"""
cursor.execute(copy_sql)
# Step 6: Close Snowflake connection
cursor.close()
conn.close()
What the Code Does
| Step | Code Area | Purpose |
|---|---|---|
| Read Excel | pd.read_excel() | reads the source workbook |
| Create CSV | df.to_csv() | creates a Snowflake-loadable file |
| Connect | snowflake.connector.connect() | opens a Snowflake session |
| Stage file | PUT | uploads the local CSV to the table stage |
| Load table | COPY INTO | loads staged rows into the target table |
Controlled Production Pattern
Supporting Validation Script
The simple script is useful for learning, but production ingestion normally needs additional controls.
A controlled load usually includes:
- source file validation
- required column checks
- controlled
PUT COPY INTOvalidation- transaction handling
- row count checks
- audit logging
- file archival
- clear exception handling
Production Flow
Read source file
|
v
Validate file and required columns
|
v
Convert Excel to CSV
|
v
Upload file to Snowflake stage
|
v
Validate COPY INTO
|
v
Load target table
|
v
Compare source and target counts
|
v
Insert audit record
|
v
Archive processed file
Audit Table Example
CREATE TABLE IF NOT EXISTS LOAD_AUDIT (
FILE_NAME STRING,
LOAD_TIME TIMESTAMP,
STATUS STRING,
SOURCE_ROW_COUNT NUMBER,
TARGET_ROW_COUNT NUMBER,
ERROR_MESSAGE STRING
);
Validation Before Loading
Use VALIDATION_MODE = RETURN_ERRORS to check whether Snowflake can load the file before inserting rows.
COPY INTO COBRA_EMPLOYEES
FROM @%COBRA_EMPLOYEES
FILE_FORMAT = (
TYPE = CSV
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
)
VALIDATION_MODE = RETURN_ERRORS;
If this returns errors, fix the file or table definition before running the final COPY INTO.
2. Programmatic Loading Using Snowpark Python
What Snowpark Is
Snowpark lets you write Python DataFrame code that executes inside Snowflake using Snowflake compute.
Use Snowpark when your pipeline needs transformation logic close to the data, not only orchestration from an external Python process.
When Snowpark Is the Right Choice
- transformation-heavy ELT pipelines
- replacing Spark or Glue jobs
- keeping processing inside Snowflake
- DataFrame-style transformations
- stored procedure-based pipelines
Snowpark Anti-Pattern: Only Submitting SQL
This works, but it is not using Snowpark's main advantage.
from snowflake.snowpark import Session
def main(session: Session):
session.sql("""
COPY INTO COBRA_EMPLOYEES
FROM @%COBRA_EMPLOYEES/cobra_employees.csv.gz
FILE_FORMAT = (
TYPE = CSV
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
)
ON_ERROR = 'CONTINUE'
""").collect()
return "Load completed"
Why this is not a true Snowpark pattern:
- the logic is still pure SQL
- Snowpark is only submitting the statement
- the same result could be achieved with the Python Connector
Snowpark DataFrame Pattern
This pattern reads staged data as a DataFrame and writes it to a table.
from snowflake.snowpark import Session
def main(session: Session):
df = session.read.options({
"FIELD_DELIMITER": ",",
"SKIP_HEADER": 1,
"FIELD_OPTIONALLY_ENCLOSED_BY": '"',
}).csv("@%COBRA_EMPLOYEES/cobra_employees.csv.gz")
df.write.mode("append").save_as_table("COBRA_EMPLOYEES")
return f"Rows loaded: {df.count()}"
Snowpark Stored Procedure Example
CREATE OR REPLACE PROCEDURE LOAD_COBRA_EMPLOYEES_SNOWPARK()
RETURNS VARCHAR
LANGUAGE PYTHON
RUNTIME_VERSION = '3.10'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'run'
EXECUTE AS OWNER
AS
$$
from snowflake.snowpark import Session
def run(session: Session):
df = session.read.options({
"FIELD_DELIMITER": ",",
"SKIP_HEADER": 1,
"FIELD_OPTIONALLY_ENCLOSED_BY": '"',
}).csv("@%COBRA_EMPLOYEES/cobra_employees.csv.gz")
df.write.mode("append").save_as_table("COBRA_EMPLOYEES")
return f"Rows loaded: {df.count()}"
$$;
Call the procedure:
CALL LOAD_COBRA_EMPLOYEES_SNOWPARK();
3. Programmatic Loading Using Snowflake SQL API
What the SQL API Is
The Snowflake SQL API allows applications to execute SQL through REST endpoints.
It is useful when the application cannot use the Python Connector or Snowpark runtime.
When SQL API Is Used
- web applications
- serverless functions
- lightweight integrations
- REST-based platforms
- applications where installing a Snowflake driver is not practical
SQL API Characteristics
| Characteristic | Detail |
|---|---|
| Access method | HTTPS REST API |
| Work style | Submit SQL statements and fetch results |
| Good fit | Lightweight execution from applications |
| Not ideal for | Complex local file handling or heavy orchestration |
The SQL API can run COPY INTO, but local file upload with PUT is usually better handled by a Snowflake driver such as the Python Connector.
Approach Comparison
| Approach | Control | Runs Where | Best For |
|---|---|---|---|
| Python Connector | High | External Python runtime | batch ingestion, orchestration, validation |
| Snowpark Python | High | Snowflake compute | in-Snowflake transformations and procedures |
| SQL API | Medium | REST client plus Snowflake | web apps and serverless integrations |
What Is Not Programmatic Loading
These are loading methods, but they are not usually called programmatic loading:
- manually running
COPY INTOin a worksheet - using the Snowsight file upload wizard
- fully automatic Snowpipe with no custom control code
Interview-Ready Definition
Programmatic loading in Snowflake is the use of application code to orchestrate, validate, and manage ingestion workflows while relying on Snowflake's native loading commands for the actual data load.
Key Takeaways
- Snowflake handles scalable data loading.
- Programmatic loading adds validation, control, logging, and orchestration.
- Use the Python Connector for controlled external batch pipelines.
- Use Snowpark when transformation logic should run inside Snowflake.
- Use the SQL API for REST-based applications and serverless integrations.
One-Line Summary
Programmatic loading makes Snowflake ingestion production-ready.