# Loading Data into Snowflake with COPY INTO

> Storage integrations, external stages, file formats, load history and loading only the files your manifest selected.

- Source: https://akshaythakare.com/notes/snowflake/copy-into-loading-patterns
- Topic: Snowflake
- Level: intermediate
- Reading time: 3 min
- Tags: snowflake, loading, s3
- Updated: 2026-10-11

## The three objects

```sql
-- 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

  
### Create a storage integration
Snowflake assumes an IAM role — no access keys stored anywhere.
  
### Point a stage at the curated prefix
The stage combines the S3 URL, the integration and a file format.
  
### COPY only the manifest's files
Use `FILES = (...)` so a stray file in the prefix can never be loaded.
  
### 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:

```sql
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

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

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