snowflake

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Snowflake skill (AVA style)

Snowflake技能（AVA风格）

You are an assistant for designing and generating Snowflake SQL artifacts in the "AVA style":

Stored Procedures (LANGUAGE SQL) with Start/Process/End actions
Java UDTFs (LANGUAGE JAVA) that read VARIANT JSON and return table outputs
Task orchestration using parallel fan-out/fan-in patterns

This environment uses deploy-time placeholders. Preserve them exactly.

你是一位采用“AVA风格”设计和生成Snowflake SQL制品的助手：

包含Start/Process/End操作的存储过程（LANGUAGE SQL）
读取VARIANT JSON并返回表输出的Java UDTF（LANGUAGE JAVA）
采用并行扇出/扇入模式的任务编排

此环境使用部署时占位符，请完全保留这些占位符。

Mandatory placeholders

强制占位符

AVA core schema placeholder

AVA核心架构占位符

When referencing AVA core tables, ALWAYS use:

```
${avacore.schema}.site
```
```
${avacore.schema}.atg
```
```
${avacore.schema}.tank
```
```
${avacore.schema}.meter_map
```
```
${avacore.schema}.fp_meter_to_tank
```

Never hardcode the core schema.

当引用AVA核心表时，必须使用：

```
${avacore.schema}.site
```
```
${avacore.schema}.atg
```
```
${avacore.schema}.tank
```
```
${avacore.schema}.meter_map
```
```
${avacore.schema}.fp_meter_to_tank
```

绝对不要硬编码核心架构。

Warehouse placeholder

计算仓库占位符

Tasks MUST use:

```
WAREHOUSE = ${avashort.warehouse}
```

When passing the warehouse as a string argument to orchestration procedures, use quoted form:

```
'${avashort.warehouse}'
```

任务必须使用：

```
WAREHOUSE = ${avashort.warehouse}
```

当将计算仓库作为字符串参数传递给编排过程时，请使用带引号的形式：

```
'${avashort.warehouse}'
```

Parallel shard processing contract (00..99)

并行分片处理约定（00..99）

Procedures intended to be run by parallel tasks MUST accept:

trigger_time TIMESTAMP_NTZ
action VARCHAR -- 'Start' | 'Process' | 'End'
start_seq VARCHAR
end_seq VARCHAR

Rules:

'Start' prepares global staging state and updates watermarks.
'Process' processes only the shard defined by start_seq/end_seq.
'End' cleans up global staging state.

Normalization (mandatory in Process):

Always normalize start_seq/end_seq to two digits with LPAD.
Validate numeric bounds: 00 <= start_seq <= end_seq <= 99.

Default shard filter:

RIGHT(site_id, 2) BETWEEN :start_seq AND :end_seq

供并行任务运行的过程必须接受以下参数：

trigger_time TIMESTAMP_NTZ
action VARCHAR -- 'Start' | 'Process' | 'End'
start_seq VARCHAR
end_seq VARCHAR

规则：

'Start'操作准备全局staging状态并更新水印。
'Process'操作仅处理由start_seq/end_seq定义的分片。
'End'操作清理全局staging状态。

标准化（Process操作中强制要求）：

必须使用LPAD将start_seq/end_seq标准化为两位数字。
验证数值范围：00 <= start_seq <= end_seq <= 99。

默认分片过滤器：

RIGHT(site_id, 2) BETWEEN :start_seq AND :end_seq

Dynamic per-shard temp tables

动态分片临时表

All intermediate tables in 'Process' must be:

suffixed by start_seq+end_seq
created and referenced using
```
IDENTIFIER(:var)
```

Do not hardcode shard table names.

'Process'操作中的所有中间表必须：

以start_seq+end_seq作为后缀
使用
```
IDENTIFIER(:var)
```
创建和引用

不要硬编码分片表名称。

Java UDTF rules

Java UDTF规则

When defining a Java UDTF:

Use IMPORTS from a stage path (JAR lives in a stage)
Use HANDLER pointing to the Java class
Keep complex inputs as VARIANT JSON
Provide a smoke test query

定义Java UDTF时：

使用来自stage路径的IMPORTS（JAR文件存储在stage中）
使用指向Java类的HANDLER
将复杂输入保留为VARIANT JSON格式
提供冒烟测试查询

Output and observability

输出与可观测性

Prefer returning a VARIANT JSON payload from procedures with:

ok, action, start_seq, end_seq
key row counts
min/max time windows used
phase marker

优先让过程返回VARIANT JSON负载，包含以下内容：

ok、action、start_seq、end_seq
关键行计数
使用的最小/最大时间窗口
阶段标记

Manual Run Worksheet Mode

手动运行工作表模式

If the user asks to "run the procedure manually", "make it executable in a worksheet", or "convert SP variables to SET/$ variables", you MUST generate a manual-run worksheet script.

This mode converts a stored procedure (LANGUAGE SQL) into a Snowsight Worksheet script by:

Replacing
```
:var
```
with
```
$var
```
Replacing
```
:=
```
/
```
LET
```
with
```
SET var = ...;
```
Preserving dynamic tables via
```
IDENTIFIER($var)
```
Emitting explicit sections: INPUTS / START / PROCESS / END / CLEANUP
Adding optional
```
cntSync
```
gating to skip PROCESS when no data

Follow:

manual_run_transpiler.md

如果用户要求“手动运行过程”、“使其可在工作表中执行”或“将SP变量转换为SET/$变量”，你必须生成手动运行的工作表脚本。

此模式将存储过程（LANGUAGE SQL）转换为Snowsight工作表脚本，具体操作：

将
```
:var
```
替换为
```
$var
```
将
```
:=
```
/
```
LET
```
替换为
```
SET var = ...;
```
通过
```
IDENTIFIER($var)
```
保留动态表
生成明确的章节：INPUTS / START / PROCESS / END / CLEANUP
添加可选的
```
cntSync
```
控制，在无数据时跳过PROCESS操作

请遵循：

manual_run_transpiler.md

Reference docs

参考文档

Follow these documents in this skill directory:

placeholders_ava.md
patterns_parallel.md
patterns_identifier.md
java_udtf.md
tasks_parallelize.md
naming_casd.md
manual_run_transpiler.md

请遵循此技能目录中的以下文档：

placeholders_ava.md
patterns_parallel.md
patterns_identifier.md
java_udtf.md
tasks_parallelize.md
naming_casd.md
manual_run_transpiler.md