Snowflake Preflight for VS Code

An event-driven, local developer safety net that analyzes Snowflake micro-partition pruning telemetry and calculates compute resource constraints before query execution. By intercepting native dbt compilation targets and raw SQL scratchpads, Snowflake Preflight provides zero-compute execution plan insights directly inside your IDE.

The Problem

In modern data architectures, query performance tuning and cost control are highly reactive. Analytics engineers often discover that a query lacks an optimal date-partition filter or introduced a massive Cartesian product after it has already triggered severe remote disk spilling, consumed expensive virtual warehouse credits, or slowed down production pipelines.

Furthermore, local dbt development poses a unique IDE challenge: the text inside the active code editor contains complex Jinja templating, macros, and references ({{ ref(...) }}) that native database engines cannot physically compile or explain.

The Solution

Snowflake Preflight runs an asynchronous, non-blocking telemetry collector that leverages Snowflake’s metadata layer via EXPLAIN USING JSON. Because it talks strictly to the Cloud Services layer, it evaluates execution plans in zero-compute environments—never waking up a suspended warehouse or burning warehouse credits.

For dbt workflows, it decouples from the active editor window entirely, deploying a file system watcher that targets your local target/compiled/ artifacts to seamlessly bypass Jinja boundaries the exact moment your local compiler outputs executable SQL.

Key Features

Sequential Multi-Statement Profiling: Automatically splits multi-statement SQL scripts (common in dbt incremental strategies) and profiles every single DML statement (SELECT, INSERT, MERGE, UPDATE) sequentially in an asynchronous loop.
Live Warehouse Sizing Auto-Discovery: Queries Snowflake’s INFORMATION_SCHEMA dynamically via SHOW WAREHOUSES to capture the true physical sizing class (X-Small, Medium, Large) of your active session, completely bypassing brittle string-matching naming conventions.
Proactive Sizing Guardrails: Compares the aggregate byte footprint of your query against the active warehouse size, triggering high-visibility amber/red alert banners if a scan exceeds safe processing thresholds.
Dual-File Credential Ingestion: Automatically parses and accepts both modern Snowflake CLI standard parameters (connections.toml) and legacy settings paths (config.toml) out-of-the-box.

Architectural Deep Dive

The extension splits responsibilities across specialized boundary contexts to optimize performance, prevent UI thread blocking, and protect API bandwidth:

[ Active Code Editor ] ──► (dbt compile) ──► [ target/compiled/model.sql ]
                                                       │
                                            (File Watcher Triggers)
                                                       ▼
[ Webview Panel UI ] ◄── (Analyze Metrics) ◄── [ Snowflake Context ] ◄── (Sequential EXPLAIN Loop)

The Event Router: When editing a regular SQL scratchpad, text changes are managed by a custom workspace debouncer (customizable via settings.json). If the file is identified as part of a active dbt project, the typing listener shorts-circuits and goes completely silent, handing total priority over to a standalone FileSystemWatcher tuned to monitor target/compiled//*.sql.
The Metadata Closure Loop: The extension logs into Snowflake and fires a parallel metadata sweep. The resulting physical size metrics are wrapped inside a JavaScript closure. As the sequential query statements finish parsing, the closure ensures the layout matrix correlates compiled byte footprints with live infrastructure constraints deterministically.
The Isolated Rendering Engine: The UI panel uses a decoupled VS Code Webview container. It maps data matrices down into itemized layout blocks, displaying exact per-table micro-partition counts, scanned ratios, and optimization recommendations.

Configuration Settings

Snowflake Preflight integrates directly into your native VS Code settings.json panel:

Configuration Property	Type	Default	Description
`snowflakePreflight.activeProfile`	`string`	`default`	The profile target name to read from your local connections.toml or config.toml file.
`snowflakePreflight.debounceDelay`	`integer`	`1500`	Delay (ms) to wait after a user pauses typing in a raw SQL scratchpad before triggering a background evaluation.
`snowflakePreflight.autoOpenDashboard`	`boolean`	`true`	Automatically opens the split-screen dashboard to the side when execution metrics are compiled.
`snowflakePreflight.customXSmallThresholdMB`	`integer`	`5000`	Custom scanning warning line limit (in Megabytes) applied to an X-Small warehouse environment.

Local Development & Installation

Prerequisites

VS Code v1.85.0 or higher
Node.js v18.x or higher
A valid local Snowflake CLI profile configuration at ~/.snowflake/connections.toml or ~/.snowflake/config.toml

Build from Source

Clone the repository:

git clone https://github.com/ianbvaughn/snowflake-preflight.git
cd snowflake-preflight

Install project packages:

npm install

Press F5 inside VS Code to launch an Extension Development Host instance window to test the watcher against a live project workspace.

Compiling the Release Artifact

To compile the codebase into a production-ready, distributable extension packet:

npm install -g @vscode/vsce
vsce package

This generates a localized snowflake-preflight-1.0.0.vsix file which can be dragged directly into any production instance of VS Code.

Snowflake Preflight

Ian Vaughn