Snowflake SQL and Reliable Incremental Pipelines
Get Snowflake SQL, pipelines, Snowpark, and Cortex code with replay checks, query-profile diagnostics, and cost controls.
Before & after
Same prompt, same agent — the only difference is the skill.
I can help you write SQL for Snowflake. Dynamic Tables are a good approach for aggregating metrics because they refresh automatically based on the underlying data. You'll want to think about your fact table structure, what dimensions you need to aggregate across, and how often you want the refresh to happen. Snowflake also has stream and task features if you want more control. What's the source of your hourly data—is it coming from an event stream or a batch load?
DYNAMIC TABLE SCRIPT TABLE: agg_sales_metrics_hourly (auto-refresh) SOURCE CONFIGURATION Base table: raw_sales_events Granularity: hourly (refresh lag: 5 minutes) Target warehouse: analytics_wh (auto-suspend after 2 min idle) SCRIPT STRUCTURE Step 1 → Parse event timestamp to hour bucket Step 2 → Group by: region, product_category, customer_segment, hour Step 3 → Calculate: transaction_count, revenue_sum, avg_order_value, unique_customers Step 4 → Apply data quality filters (remove negative revenue, null region) Step 5 → Sort by hour DESC, region ASC for query performance RESULTING COLUMNS • hour_utc (timestamp) • region (string) • product_category (string) • customer_segment (string) • transaction_count (number) • revenue_sum (decimal) • avg_order_value (decimal) • unique_customers (number) • refresh_time (timestamp) PERFORMANCE TUNING Cluster key: hour_utc, region (reduces scan time for hourly reports) Stream attached: captures incremental updates to source table MONITORING Check query history if refresh times exceed 10 minutes
About this skill
"My MERGE keeps duplicating rows," "our refresh is falling behind," and "this query suddenly costs more" need different fixes. This skill produces Snowflake-specific code and diagnoses the grain, change semantics, execution profile, and operating limits behind the problem.
What it does
- Chooses Dynamic Tables, Streams and Tasks, or a simpler scheduled transform based on refresh support, deletes, latency, and recovery needs.
- Writes deterministic QUALIFY, MERGE, and VARIANT queries with explicit keys, ordering, and duplicate handling.
- Designs stream consumers around transactional offset advancement, independent consumers, retries, and delete propagation.
- Diagnoses pruning failures, join expansion, spilling, and queueing before recommending warehouse changes.
- Builds Snowpark transformations that stay inside Snowflake and configures dbt incremental models around actual source change behavior.
- Scopes Cortex calls with availability checks, input limits, result validation, and controls against repeated inference charges.
What you get
A focused SQL or Python patch, or a complete pipeline design with code, assumptions, validation queries, and a recovery path. Recommendations distinguish what the supplied evidence proves from what still needs an account-side check.
Best for
Analytics engineers, data engineers, and developers building or troubleshooting Snowflake SQL, incremental transformations, Snowpark workloads, and Cortex-backed data processing.
SKILL.md preview
---
name: snowflake-sql-and-reliable-incremental-pipelines
description: "Write and troubleshoot Snowflake SQL, incremental pipelines, Snowpark Python, dbt models, and Cortex integrations by checking row grain, change semantics, execution profiles, and recovery behavior. Use when the user says \"write this Snowflake query\", \"my MERGE keeps duplicating rows\", \"Dynamic Tables or Streams and Tasks?\", \"our refresh keeps falling behind\", \"this Snowflake query is too expensive\", \"keep this Snowpark job inside Snowflake\", \"fix my dbt incremental model\", or \"add Cortex to this pipeline\"."
---
# Snowflake SQL and Reliable Incremental Pipelines
… (sign up to view the full skill)More development skills
View all Development skills →Pandas Data Wrangling Without Silent Errors
Correct, fast pandas code for merging, cleaning, groupby, reshaping, and time series, with checks that catch silent row loss.
Apache Spark Job Tuning and Debugging
Finds why a Spark job is slow or failing from the plan and UI metrics, then gives the code and config fix plus how to verify it.
Reliable Data Pipelines and Analytics Models
Build and repair ETL/ELT pipelines with replay-safe writes, explicit data contracts, and actionable quality checks.
Statistical Tests, Power & A/B Readouts
Get hypothesis tests, effect sizes, confidence intervals, and sample-size calculations with experiment validity checks.