Skip to main content

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:

  • PUT
  • COPY 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:

ApproachBest ForMain Idea
Python ConnectorBatch orchestration and controlled loadsPython executes Snowflake SQL commands
Snowpark PythonTransformations inside SnowflakePython DataFrame code runs on Snowflake compute
Snowflake SQL APIREST-based integrationsApplications 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 INTO generation
  • row count validation
  • audit logging
  • retry and exception handling

Supporting File​

↓Download cobra_employees.xlsx
📘 Download and use this Excel file for the training exercise.

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 ReferenceMeaning
@~user stage
@%TABLE_NAMEtable stage
@NAMED_STAGEnamed internal or external stage

Simple Example: Excel to Snowflake Table

This example performs four actions:

  1. read the Excel file
  2. convert it to CSV
  3. upload the CSV to the table stage
  4. 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​

StepCode AreaPurpose
Read Excelpd.read_excel()reads the source workbook
Create CSVdf.to_csv()creates a Snowflake-loadable file
Connectsnowflake.connector.connect()opens a Snowflake session
Stage filePUTuploads the local CSV to the table stage
Load tableCOPY INTOloads staged rows into the target table

Controlled Production Pattern

Supporting Validation Script​

↓Download controled_prog_prod.py
🧪 Download this Python script to practice validation, audit logging, row count checks, and file archival.

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 INTO validation
  • 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​

CharacteristicDetail
Access methodHTTPS REST API
Work styleSubmit SQL statements and fetch results
Good fitLightweight execution from applications
Not ideal forComplex 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

ApproachControlRuns WhereBest For
Python ConnectorHighExternal Python runtimebatch ingestion, orchestration, validation
Snowpark PythonHighSnowflake computein-Snowflake transformations and procedures
SQL APIMediumREST client plus Snowflakeweb apps and serverless integrations

What Is Not Programmatic Loading

These are loading methods, but they are not usually called programmatic loading:

  • manually running COPY INTO in 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.