Loading Data into Snowflake with COPY INTO

The three objects

-- 1. Trust relationship with an IAM role (no keys in Snowflake)
CREATE STORAGE INTEGRATION s3_curated
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::<account>:role/snowflake-curated-read'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-bucket/curated/');
 
-- 2. File format
CREATE FILE FORMAT parquet_fmt TYPE = PARQUET;
 
-- 3. External stage
CREATE STAGE curated_stage
  URL = 's3://my-bucket/curated/'
  STORAGE_INTEGRATION = s3_curated
  FILE_FORMAT = parquet_fmt;

Run DESC INTEGRATION s3_curated to get the Snowflake IAM user ARN and external ID for the role's trust policy.

The load, step by step

1. 1

   Create a storage integration

   Snowflake assumes an IAM role — no access keys stored anywhere.
2. 2

   Point a stage at the curated prefix

   The stage combines the S3 URL, the integration and a file format.
3. 3

   COPY only the manifest's files

   Use FILES = (...) so a stray file in the prefix can never be loaded.
4. 4

   Reconcile counts

   Compare staged rows with the manifest before the publish transaction starts.

Load exactly what was selected

Don't point COPY at a whole prefix and hope. Load the files your manifest names:

COPY INTO stg_sales
FROM @curated_stage/batch_id=2026-10-10/attempt_id=7f1e.../sales/
FILES = ('part-00000.parquet', 'part-00001.parquet')
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
ON_ERROR = ABORT_STATEMENT;

• ON_ERROR = ABORT_STATEMENT fails the load on any bad record — right for curated data that should already be clean
• MATCH_BY_COLUMN_NAME maps Parquet columns by name instead of position

Load metadata and replays

Snowflake remembers which files it has loaded into a table (load metadata is kept for 64 days) and skips them by default. That protects against accidental double loads — but it also means a deliberate reload needs FORCE = TRUE or a fresh staging table. Pipelines that load into session-specific staging and then publish with their own ledger check get predictable replay behavior.

Then reconcile

SELECT COUNT(*) FROM stg_sales;  -- compare with the curated manifest's declared count

Only after counts and keys reconcile should the publish transaction begin.