drive-snowflake

Data flow & failure modes

How a dealer stock file or Redbook feed physically gets from S3 into a Snowflake raw table, and where it can go wrong along the way.

Stock ingestion path

Two Lambdas, two very different jobs — see AWS resources for why the router only makes a routing decision and the processor does the actual work.

The Redbook path is the same shape but CSV-based and hand-rolled per feed — 5 separate Snowpipes (vehicles, makes, families, legacy map, cipher key), fed from a redbook-bronze bucket that a copyRedBookToBronzeBucket Lambda populates weekly from the manifold-<env> bucket.

Where this path can lose or duplicate data

Every Snowpipe COPY statement sets ON_ERROR = 'SKIP_FILE'.

  • One malformed row or type mismatch skips the entire file, not just the bad row.
  • There's no Terraform-managed alert on pipe or COPY failures — the only Snowflake-side alerting watches TASK_HISTORY for downstream dbt tasks, not PIPE_USAGE_HISTORY/COPY_HISTORY.
  • A skipped file surfaces only if someone notices missing rows. It doesn't page anyone.

The reverse (export) path is provisioned but not consumed.

  • dbt writes gzip CSV exports to an S3 export bucket via a dedicated storage integration and stage (snowflake-export-s3 module).
  • The Lambda that was meant to consume those exports (stockOutboundProcess, S3 export bucket → MySQL upsert) is fully commented out in serverless.yml.
  • Nothing currently reads what dbt writes there.

The trust-policy patch between an IAM role and its Snowflake storage integration runs via local-exec + AWS CLI, not declaratively.

  • Snowflake generates the storage integration's storage_aws_iam_user_arn/external ID only after the integration exists, so the IAM role's trust policy can't be set at creation time through the provider alone.
  • Both the Snowpipe and export-S3 modules work around this with a null_resource that shells out to aws iam update-assume-role-policy using var.aws_profile.
  • This makes terraform apply non-idempotent in the strict sense: terraform plan can't show the real state of the trust policy, and CI needs working AWS CLI credentials for the exact profile string, not just the Terraform provider credentials.
  • If that CLI call fails mid-apply, the storage integration and IAM role exist but the trust relationship is stale — Snowpipe silently can't assume the role to read S3, and nothing detects that condition automatically.

Cross-IaC coupling on the same path

Terraform doesn't own the Lambda functions in this path — they're deployed separately by the Serverless Framework app (platform/serverless/serverless.yml). Terraform does a data "aws_lambda_function" lookup on the deployed function, then creates the aws_lambda_permission and aws_s3_bucket_notification that wire S3 events to it.

This means terraform apply for this bucket notification depends on the Lambda already being deployed — an implicit ordering requirement between two separate deploy pipelines, not something Terraform enforces or documents itself.

See also

Esc
Esc Close