Skip to content

0007-external-stage-copy-into.md

Latest commit

 

History

History
55 lines (37 loc) · 5.94 KB

0007-external-stage-copy-into.md

File metadata and controls

55 lines (37 loc) · 5.94 KB
Raw

ADR-0007: External stage + COPY INTO — S3 to Snowflake load via pull model

Context

Snowflake needs to load Bronze Parquet that an upstream Sedona-on-Glue job writes to S3 (see ADR-0008). The S3 bucket and the Snowflake account belong to the same cloud platform but are separate trust boundaries, so the load pattern has to solve both how data moves and how Snowflake authenticates to S3.

Three load patterns are in scope:

  • Snowpipe REST API (AWS pushes). A Lambda or other AWS-side actor wraps INSERT calls into Snowpipe. Push semantics, near-real-time, but heavier wiring on the AWS side (Lambda + Snowpipe credentials + retry/dedup logic) and a per-row cost model that does not match this project's batch shape.
  • Snowpipe Auto-Ingest. S3 event notifications drive Snowpipe via SQS. Best fit for streaming or near-real-time freshness. The notification and queue plumbing is justified once an event-driven cadence is real; for a manual-or-daily refresh it is over-engineered.
  • External Stage + COPY INTO (Snowflake pulls). Snowflake-managed cross-account IAM via a Storage Integration (STS AssumeRole + External-ID). COPY INTO <table> FROM @<stage> runs on demand. Snowflake pulls; AWS does not push.

The refresh cadence for this pipeline is "manual or daily", not streaming. Authentication needs to be reproducible end-to-end via Terraform — one apply should leave a working trust path, with no manual ARN copy-paste step between AWS and Snowflake.

Decision

Use an External Stage on S3, fronted by a Snowflake Storage Integration that delegates to an AWS IAM role via STS AssumeRole with an External-ID. Loads run on demand via COPY INTO <table> FROM @<stage>. Snowflake pulls from S3; AWS does not push to Snowflake. Snowpipe (REST or Auto-Ingest) is deferred until a streaming or near-real-time use-case appears.

The Storage Integration's iam_user_arn and storage_aws_external_id are wired into the AWS IAM role's trust policy via direct Terraform resource references, so one apply establishes the full handshake without a manual DESC INTEGRATION step.

Consequences

Positive

  • Cross-account IAM via STS AssumeRole is the industry-standard pattern — no shared access keys handed to Snowflake. Snowflake assumes the IAM role on each load; rotation is handled by AWS, not by managing secrets in Snowflake.

  • Terraform resolves the chicken-and-egg between AWS and Snowflake in one apply. The AWS IAM role's trust policy reads iam_user_arn and the External-ID directly off the snowflake_storage_integration resource; the Snowflake side references the resulting IAM role ARN. No human-in-the-loop copy-paste loop between the two providers.

  • COPY INTO is symmetric. The same keyword loads and unloads, with direction inferred from argument types:

    COPY INTO raw_osm_power FROM @raw_osm_power_stage;   -- load
COPY INTO @export_stage FROM raw_osm_power;          -- unload

    PostgreSQL splits this into COPY FROM and COPY TO; Snowflake disambiguates on the operand. Useful for occasional exports back to S3 — for example Gold-layer extracts handed off to downstream consumers — without introducing a second tool.

  • No AWS-side actor for the load. Snowflake Tasks can drive the schedule from inside the warehouse if a cadence is needed; until then, COPY INTO is invoked from whatever orchestrator runs the upstream Glue job.

Negative / Trade-offs

  • Pull model, not event-driven. A file landing in S3 is not the same as data being in Snowflake. The freshness floor is whatever schedule (manual, Snowflake Task, external orchestrator) drives the COPY INTO. For a streaming use-case this would be the wrong choice.
  • Storage Integration was a preview feature in earlier Snowflake Terraform provider versions. Provider 2.15 required an explicit preview_features_enabled = ["snowflake_storage_integration_resource", ...] block, alongside file_format, table, and stage. The feature is moving toward GA; the preview flag should be revisited on each provider bump.
  • Storage-integration resource is split per cloud; two External-ID access paths exist. The previous-generation snowflake_storage_integration (now provider-flagged deprecated: true, slated for removal; replaced by the per-cloud _aws / _azure / _gcs variants) has a documentation quirk — the documented top-level storage_aws_external_id is empty in practice; the real value sits at describe_output[0].storage_aws_external_id[0].value. The newer split-per-cloud snowflake_storage_integration_aws (used in this project, behind the snowflake_storage_integration_aws_resource preview flag in provider 2.15) exposes the cleaner describe_output[0].external_id / iam_user_arn paths directly.
  • PAT auth requires a Network Policy. A Snowflake Programmatic Access Token without a linked Network Policy fails on the very first plan with Snowflake error 390432. The Terraform side has to set the Network Policy and link it to the PAT before the provider can authenticate.

Follow-ups / Out of scope

  • Snowpipe Auto-Ingest (S3 event → SQS → Snowpipe) is the natural step when streaming or near-real-time freshness becomes a requirement. The Storage Integration carries over; only the trigger path changes.
  • Snowflake Tasks for periodic COPY INTO — currently manual or orchestrator-driven; trivial to add when refresh cadence firms up.
  • Unload path (COPY INTO @stage FROM table) is wired conceptually but not exercised yet. First real use-case will be Gold-layer SLA extracts back to S3 for downstream consumers.