15. Loading Data

Using COPY INTO

-- Create stage and file format
CREATE OR REPLACE STAGE my_stage;
CREATE OR REPLACE FILE FORMAT csv_ff
  TYPE = CSV
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"';

-- Stage a file
PUT file://data/sales.csv @my_stage;

-- Copy into table
COPY INTO sales_table
FROM @my_stage/sales.csv
FILE_FORMAT = csv_ff
ON_ERROR = 'CONTINUE';

Validation

-- Validate before loading
COPY INTO sales_table
FROM @my_stage/sales.csv
FILE_FORMAT = csv_ff
VALIDATION_MODE = RETURN_ERRORS;

-- View history
SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
  TABLE_NAME=>'sales_table',
  START_TIME=>DATEADD(hours, -24, CURRENT_TIMESTAMP())));

Auto-Ingest (Snowpipe)

Snowpipe automates loading when new files arrive in a stage:

CREATE PIPE sales_pipe AS
  COPY INTO sales_table FROM @my_stage FILE_FORMAT = csv_ff;
ALTER PIPE sales_pipe REFRESH;