Data Loading & Stages

## What a stage is A stage in Snowflake is a named pointer to a file location plus, optionally, a default file format. Loading is always a two-step story: get the files to a stage, then run `COPY INTO <table> FROM @<stage>`. `COPY INTO` cannot read your local filesystem, and there is no server-side "upload" in a SQL worksheet — the stage is the mandatory hand-off point. ## The three internal stage flavours Internal stages sit in Snowflake-managed cloud storage. Files there are encrypted and counted against your storage bill until you remove them. - **User stage — `@~`.** One per user, created automatically, cannot be altered or dropped, and is visible only to that user. Handy for ad-hoc one-off loads that may target several tables. - **Table stage — `@%orders`.** One per table, created automatically with the table. Files staged here can only be loaded into that table, and it has no privileges of its own — access follows the table. You cannot attach a default file format to it. - **Named internal stage — `@raw_stage`.** A real schema object created with `CREATE STAGE raw_stage FILE_FORMAT = (TYPE = CSV)`. It can carry a default file format, be granted to roles, be organised into folder-like paths, and support a directory table. This is the one you use for anything recurring or shared across a team. ## Getting files into an internal stage ```sql -- run from SnowSQL, a driver, or the Snowsight load wizard PUT file:///data/orders_2026_08.csv @raw_stage/orders/ AUTO_COMPRESS = TRUE PARALLEL = 8; LIST @raw_stage/orders/; ``` `PUT` is a client-side command: SnowSQL, the Python/JDBC/ODBC drivers and the Snowsight load-data UI implement it, but you cannot type `PUT` into a plain SQL worksheet and have it reach your disk. `AUTO_COMPRESS = TRUE` gzips the file on the way up. `GET` pulls files back down, `LIST` (`LS`) shows what is staged, and `REMOVE` deletes staged files. Staged files linger and cost storage, so most pipelines either `REMOVE` them or set `PURGE = TRUE` on the `COPY`. ## External stages ```sql CREATE STAGE ext_orders URL = 's3://acme-lake/orders/' STORAGE_INTEGRATION = acme_s3_int FILE_FORMAT = (TYPE = PARQUET); ``` Here the files stay in your own bucket and Snowflake reads them in place — no upload step and no second copy of the data. A **storage integration** is the recommended way to authorise this: it is an account-level object bound to a cloud IAM role (an AWS role ARN, an Azure tenant/app, a GCS service account) plus a list of allowed locations, so no long-lived keys appear in DDL or in `SHOW STAGES` output. Inline `CREDENTIALS = (AWS_KEY_ID = ... )` also works and is what interviewers expect you to argue against. ## Which one to reach for Use an external stage when the files already live in your data lake, when another team or tool produces them, when you want cloud-native lifecycle rules to expire them, or when you want Snowpipe auto-ingest — event notifications only exist on your own bucket. Use an internal stage when Snowflake is the only consumer, when you are pushing files from an on-prem or CI process that has no bucket, or when you want the files encrypted and governed entirely inside Snowflake. ## The COPY that follows ```sql COPY INTO orders FROM @ext_orders/2026/08/ FILE_FORMAT = (FORMAT_NAME = my_csv) PATTERN = '.*[.]csv[.]gz' ON_ERROR = ABORT_STATEMENT; ``` A folder prefix after the stage name narrows what is read, and `PATTERN` filters by regex. A stage path plus a `PATTERN` is far cheaper than pointing at the bucket root and letting Snowflake list millions of objects. `FILE_FORMAT` can be inline or a named `CREATE FILE FORMAT` object; naming it keeps the delimiter, header and null-handling rules in one place across pipelines. ## What interviewers probe The common follow-ups are: which stage is automatic and which you create; why `PUT` fails in a worksheet; why a storage integration beats embedded keys; and whether you know that the stage is only the landing zone — nothing is queryable as table data until `COPY INTO` runs (or you define an external table over the stage).