snowflake（Snowflake 开发）

Snowflake skill (AVA style)

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.

Mandatory placeholders

AVA core schema placeholder

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.

Warehouse placeholder

Tasks MUST use:

• WAREHOUSE = ${avashort.warehouse}

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

• '${avashort.warehouse}'

Parallel shard processing contract (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

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.

Java UDTF rules

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

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

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

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