Load data into Snowflake, outlining loading considerations and the impact of related features.

Loading is the first place where an engineer decides whether a pipeline survives a real production workload. The decision is not "use COPY or use Snowpipe". The decision is a combination of file shape, latency target, ordering guarantee, source system protocol, and cost ceiling. Every other feature in this chapter inherits those decisions. A pipeline that uses 5 MB Parquet files through a Snowpipe REST notification will saturate compute on small files, while a pipeline that uses 5 GB CSV files through a serverless Snowpipe will starve on parallelism. The two failure modes look identical from the outside (slow ingest) and have opposite fixes.

The COPY INTO command is the foundation. It reads files from a stage, applies a file format definition, optionally transforms columns through a SELECT projection, and writes rows into a target table inside a single transaction that commits per command. The unit of parallelism is the file, with one file processed per thread. Snowflake provisions roughly eight threads per node of the warehouse, so an X-Small (one node) handles eight files concurrently and a Large (eight nodes) handles sixty-four. This is the single most important number to internalize for batch loading. If the source produces three large files per hour, scaling the warehouse beyond X-Small produces no speedup at all, because the file count caps the parallelism.

File sizing and split granularity

The file-size sweet spot for COPY INTO is 100 MB to 250 MB compressed. Smaller files force the loader to spend more time on per-file overhead (stage list, header parse, transaction bookkeeping) than on actual row insertion, and larger files cannot be split across threads. For Parquet specifically, the file should be at least 32 MB to avoid spending more time decompressing dictionary pages than reading data. For CSV and JSON, splitting files in source systems before staging produces materially better throughput than letting Snowflake handle large monoliths.

The ON_ERROR setting controls per-file fault tolerance. The values CONTINUE, SKIP_FILE, SKIP_FILE_n, SKIP_FILE_n%, and ABORT_STATEMENT correspond to increasing strictness. ABORT_STATEMENT is the default and is appropriate for transactional loads where partial commits would corrupt downstream contracts. For exploratory bronze-layer loads where one bad file should not block the others, SKIP_FILE quarantines the offender. The VALIDATION_MODE parameter (RETURN_n_ROWS, RETURN_ERRORS, RETURN_ALL_ERRORS) runs the load in dry-run mode and reports problems without writing any rows, which is the correct pre-flight check before promoting a new format.

COPY INTO RAW_DB.LANDING.ORDERS
FROM @RAW_DB.LANDING.ORDERS_STAGE
FILE_FORMAT = (FORMAT_NAME = RAW_DB.LANDING.PARQUET_FMT)
ON_ERROR = 'SKIP_FILE_5%'
PURGE = FALSE
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
FORCE = FALSE;

💡 Exam Trap: FORCE = TRUE re-loads files that have already been ingested. The metadata cache that prevents duplicate loads is keyed by file name and file checksum over a 64-day window, not by file content. Renaming a file in the stage with the same content will cause re-ingestion; a different file with the same name and matching checksum will be skipped.

📊 Limit: The COPY load history is retained for 64 days. Files ingested before that window are eligible for re-load by default if their names reappear in the stage, regardless of FORCE.

Choosing among COPY, Snowpipe, and Snowpipe Streaming

Three ingestion paths exist for entirely different latency and cost profiles. COPY INTO runs on user-managed virtual warehouses with full SQL transactional semantics. Snowpipe runs on Snowflake-managed serverless compute, triggered by cloud event notifications or REST insertFiles calls, and bills per-file with a small per-thousand-file overhead. Snowpipe Streaming uses a Java SDK (the Snowflake Ingest SDK) to write rows directly into a target table without staging files at all, achieving sub-ten-second latency at row-level granularity.

The cost difference matters. Snowpipe carries a per-file overhead of 0.06 credits per thousand files in addition to compute time. For workloads with many small files, that overhead dominates. Snowpipe Streaming has no per-file overhead because there are no files, only client throughput. For high-volume row streams, Streaming is meaningfully cheaper; for low-frequency batches of large files, COPY on a small warehouse is cheapest.

Install and configure connectors (Kafka connector, Spark connector, Python connector, Native connectors)

The Kafka connector is an Apache Kafka Connect plugin (snowflake-kafka-connector) that reads from Kafka topics and writes to Snowflake tables. It runs in two modes: the original Snowpipe-based mode, which stages JSON or Avro records to an internal table stage and then triggers Snowpipe, and the Snowpipe Streaming mode (enabled by setting snowflake.ingestion.method=SNOWPIPE_STREAMING), which writes rows in-flight without file staging. The Streaming mode supports exactly-once semantics through an offset token persisted on the Snowflake side. The connector requires a key pair authentication: the connector machine holds the RSA private key, and the matching public key is registered on the Snowflake user account.

The Spark connector lets a Spark job read from and write to Snowflake using the net.snowflake.spark.snowflake DataSource. It uses internal stages and temporary directories on the cluster as a transient buffer, then issues COPY INTO commands on a designated warehouse. For writes, the option column_mapping (with values order or name) controls how DataFrame columns align to table columns. The query_pushdown setting (on by default) pushes SQL operations into Snowflake so that joins and aggregations execute server-side rather than dragging data across the wire.

The Python connector (snowflake-connector-python) is the lowest-level client library. It implements DB API 2.0 and adds Snowflake-specific helpers such as write_pandas (PUT a pandas DataFrame to a stage and COPY INTO a table in one call) and fetch_arrow_all (return result sets in Apache Arrow format for zero-copy handoff to Pandas or Polars). Connection objects support OAuth, key-pair, and external browser authentication; MFA is supported through DUO push or TOTP.

Native connectors (formally Snowflake Connectors) are installed from the Snowflake Marketplace and run as Snowflake Native Apps inside the customer account. They package the source system credentials, scheduling logic, and target schema definitions into a single installable artifact that runs on the customer's compute, with the source vendor controlling the application logic. The connector for ServiceNow, for example, is installed into a dedicated database, configured through Streamlit, and runs scheduled refresh tasks on a designated warehouse.

from snowflake.connector.pandas_tools import write_pandas
import snowflake.connector
import pandas as pd

conn = snowflake.connector.connect(
 account="acme_analytics",
 user="ingest_svc",
 private_key=PRIVATE_KEY_BYTES,
 warehouse="INGEST_WH",
 database="RAW_DB",
 schema="LANDING",
)

df = pd.DataFrame({"order_id": [101, 102], "amount": [49.95, 12.50]})
success, nchunks, nrows, _ = write_pandas(
 conn,
 df,
 table_name="ORDERS",
 quote_identifiers=False,
 chunk_size=16000,
 auto_create_table=False,
)

⚠️ Anti-Pattern: Running the Kafka connector in classic Snowpipe mode against a topic that produces thousands of small messages per second. Each flush becomes a file under one kilobyte, the per-file Snowpipe overhead dominates the credit bill, and the target table accumulates thousands of micro-partitions per minute. Switch to Snowpipe Streaming mode or batch records with buffer.flush.time and buffer.size.bytes to consolidate.

💡 Exam Trap: The Kafka connector in classic mode stores each topic-partition's records in a table-specific internal stage named with the pattern SNOWFLAKE_KAFKA_CONNECTOR_<connector_name>_STAGE. Operators sometimes try to drop this stage to "clean up" disk; doing so breaks the offset tracking and forces a full reseek from the beginning of the topic.

📊 Limit: A single Snowpipe Streaming channel sustains roughly 10 MB per second of throughput per channel. For higher throughput, partition the source by key and open one channel per partition.

🎯 Scenario: A retail platform produces five Parquet files of 300 MB each every fifteen minutes. The team configured Snowpipe with notification triggering and used a Medium warehouse as the auto-ingest target before realizing Snowpipe is serverless. They saw both the per-file Snowpipe charge and an idle Medium warehouse on the bill. The fix is to remove the warehouse from the pipe (Snowpipe ignores it) and either accept the small Snowpipe overhead or switch to a scheduled COPY INTO on an X-Small that runs every fifteen minutes, finishing in under a minute and paying only for the run time.

Decision Anchor

Choose COPY INTO on a user-managed warehouse when batches arrive on a predictable cadence with files at or above 100 MB and end-to-end latency in minutes is acceptable. Choose Snowpipe with auto-ingest notifications when files arrive irregularly, file count per batch is small to moderate, and latency in the order of one minute is acceptable. Choose Snowpipe Streaming when sources push individual rows or small batches at high frequency and latency below ten seconds is required.