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

snowflake