Snowflake via Snowpipe

use role accountadmin;

create storage integration kadoa_s3_integration
  type = external_stage
  storage_provider = 's3'
  enabled = true
  storage_aws_role_arn = '<ROLE_ARN_FROM_KADOA>'
  storage_allowed_locations = ('s3://<BUCKET_NAME_FROM_KADOA>/');

grant usage on integration kadoa_s3_integration to role sysadmin;

desc integration kadoa_s3_integration;

use role sysadmin;

create or replace stage kadoa_s3_stage
  storage_integration = kadoa_s3_integration
  url = 's3://<BUCKET_NAME_FROM_KADOA>/'
  directory = (enable = true);

create or replace file format parquet_format type = 'parquet';

create or replace table my_data
  using template (
    select array_agg(object_construct(*))
      within group (order by order_id)
    from table(
      infer_schema(
        location => '@kadoa_s3_stage/your-workflow-path/',
        file_format => 'parquet_format'
      )
    )
  );

create or replace pipe my_data_pipe
  auto_ingest = true
  as copy into my_data
  from @kadoa_s3_stage/your-workflow-path/
  file_format = (format_name = 'parquet_format')
  match_by_column_name = case_insensitive;

desc pipe my_data_pipe;

alter pipe my_data_pipe refresh;