编写雪花数仓文件导入流程
按时效要求选择批量或自动导入,编写存储集成、加载去重与监控脚本,并验证异常数据处理。
完整提示词
<context>
Snowflake loading looks like one `COPY INTO` statement but production loads need more. Access to the bucket should go through a storage integration (cloud IAM role or service principal), not keys pasted into a stage definition. A named file format makes parsing explicit: delimiter, header skip, quoting, null markers, empty-field handling, encoding, date and timestamp formats. COPY keeps 64 days of load metadata per table and skips files it has already loaded, which prevents duplicates on rerun, but `FORCE = TRUE` or re-uploaded files with new names bypass it, and files whose load status is unknown because they fall outside that window are skipped unless `LOAD_UNCERTAIN_FILES = TRUE`. `ON_ERROR` decides whether one bad row fails a file, skips the file, or continues, and `VALIDATION_MODE` or `VALIDATE()` shows rejected rows. Files of roughly 100 to 250 MB compressed load most efficiently; thousands of tiny files waste time and Snowpipe overhead. Snowpipe with event notifications gives near-real-time loading billed serverlessly; batch COPY on a schedule is simpler and often cheaper for hourly or daily files. Semi-structured data can land in a `VARIANT` column, or Parquet can be loaded with `MATCH_BY_COLUMN_NAME`. Adding load metadata columns (`METADATA$FILENAME`, `METADATA$FILE_ROW_NUMBER`, load time) makes auditing and deduplication possible.
</context>
<task>
Files: {{files}}
Requirements: {{requirements}}
1. Read the existing Snowflake objects and infrastructure code in the repo, and follow how the team manages objects. Inspect sample files or the given schema for format details.
2. Choose batch COPY or Snowpipe from the latency need and file pattern, and say why.
3. Write the objects: storage integration (with the cloud-side trust steps listed for the user), stage on the path, named file format with every parsing option explicit, and the target table with load metadata columns.
4. Write the load: `COPY INTO` with a transformation select that adds `METADATA$FILENAME`, file row number and load time, the chosen `ON_ERROR` policy and a pattern filter; or the pipe definition with auto-ingest and the notification setup steps.
5. Handle duplicates beyond load metadata: a downstream merge or dedupe on a business key for re-sent files.
6. Write monitoring and audit: queries on `COPY_HISTORY` or `LOAD_HISTORY` and `VALIDATE()` for rejected rows, Snowpipe status checks, and an alert for files that failed or did not arrive.
7. Test against a dev database and a sample file including a bad row, and report results.
</task>
<constraints>
- Never put cloud access keys or secrets in SQL, stage definitions or the repo; use a storage integration.
- Do not use `FORCE = TRUE` in routine loads; explain when it is acceptable.
- Do not create or alter objects in production yourself; give the scripts.
- Mark cloud-provider-specific steps clearly and do not invent IAM policy names or ARNs; use placeholders.
{{> guardrails/scope-discipline}}
{{> guardrails/verify-before-done}}
</constraints>
<output_format>
## Approach
Batch or Snowpipe, file sizing advice and why.
## Objects
SQL or infrastructure code blocks with file paths.
## Load and dedupe
COPY or pipe definition and the dedupe step.
## Monitoring and audit
Queries and alerts, plus test results.
## Open questions
Numbered, or "None".
</output_format>可替换内容
- {{files}}
- {{requirements}}
- {{> guardrails/scope-discipline}}
- {{> guardrails/verify-before-done}}
分类与标签
编程开发、雪花数仓、文件导入、数据去重、加载监控、数据管道