Why I chose this topic: I spent three weeks last quarter cleaning up a half-million-dollar Snowflake bill caused by a "set and forget" pipeline that turned into a runaway process. I’m writing this because the marketing teams at both companies are selling "declarative" as a synonym for "no-ops," and that lie is currently costing my peers their Q3 bonuses.
It was 3:14 AM on a Tuesday when the PagerDuty alert hit my phone. The notification was blunt: Warehouse Consumption Threshold Exceeded. We weren't just over our daily quota; we were at 400% of the monthly budget allocation, and the night was still young.
I rolled out of bed, fired up the VPN, and saw the Snowflake WAREHOUSE_METERING_HISTORY view. A single Dynamic Table—a simple join between a 2TB fact table and a handful of slowly changing dimensions—was running continuous refresh cycles every 60 seconds. Because the DAG was improperly defined and the clustering was sub-optimal, Snowflake was scanning the entire 2TB dataset on every single iteration. The warehouse wasn't just humming; it was screaming, and my AWS credits were evaporating in real-time.
What we saw
The initial dashboard looked like a standard performance bottleneck. We saw massive spill-to-local-disk errors in the query profile. My first instinct was to blame a rogue dev who might have introduced a CROSS JOIN or a Cartesian product in one of the upstream views.
We spent the first hour hunting for a syntax error. We checked the QUERY_HISTORY for any unusual JOIN conditions or massive filters. Nothing. The query plan was logically sound. The pipeline was doing exactly what we told it to do: refresh, check for changes, and materialize.
The symptom was "slow refresh," but the false lead was the query itself. We spent time optimizing the SQL, adding WHERE clauses, and pruning partitions. We pushed the refresh time down by 20%, but the warehouse cost stayed flat. It turned out the problem wasn't the query execution time—it was the warehouse size and the refresh interval setting. Snowflake’s Dynamic Tables were aggressively scaling up the compute to meet the 60-second target, regardless of whether the actual data had changed enough to justify the compute cost.
Photo by Julia Joppien on Unsplash
Root cause
The culprit was the TARGET_LAG parameter. We had set it to 1 minute, assuming the optimizer was smart enough to skip the refresh if the underlying tables hadn't updated.
In Snowflake, TARGET_LAG = '1 minute' acts as a hard requirement. If the underlying data changes—even by a single row—the engine fires the full refresh logic. Because we were using a virtual warehouse with AUTO_SUSPEND set to 60 seconds, the warehouse was effectively never suspending. It was waking up, running the join, hitting the 60-second timer, and staying alive just long enough to bridge the gap to the next refresh cycle.
CREATE OR REPLACE DYNAMIC TABLE daily_sales_summary
TARGET_LAG = '1 minute'
WAREHOUSE = 'COMPUTE_WH'
AS SELECT ... FROM fact_sales;
It’s elegant on paper. It’s a financial disaster in practice. The engine doesn't account for the value of the data being refreshed; it only accounts for the timeliness of it. In a high-churn environment, this is a recipe for a million-dollar bill.
Photo by Lightsaber Collection on Unsplash
The fix
We needed to kill the "continuous" loop immediately. We switched from a 1-minute TARGET_LAG to a 60 minutes window. This forced the engine to batch the updates, which allowed the AUTO_SUSPEND to actually trigger.
We also moved away from the default warehouse. We spun up a dedicated X-Small warehouse specifically for these tables, ensuring that even if the refresh hung, it couldn't scale up to an X-Large and destroy our monthly budget.
Concretely, we modified the DDL to:
ALTER DYNAMIC TABLE daily_sales_summary
SET TARGET_LAG = '1 hour'
WAREHOUSE = 'SMALL_ETL_WH';
The data latency increased, but the warehouse consumption dropped by 92% overnight. The business didn't even notice the hour-long delay, proving that our "real-time" requirement was just vanity metrics disguised as business value.
What we changed so it never happens again
We stopped treating declarative pipelines as "black boxes." If you use Snowflake Dynamic Tables, you must implement a hard limit on the warehouse size via MAX_CONCURRENCY_LEVEL and STATEMENT_QUEUED_TIMEOUT_IN_SECONDS. If a pipeline is taking too long, let it fail; do not let it scale.
In contrast, when we look at Databricks Delta Live Tables (DLT), the failure mode is different but equally frustrating. DLT is built on Spark, which means it’s prone to "executor death." If your cluster configuration is off, you’ll get ExecutorLostFailure errors because the driver is overwhelmed by the metadata overhead of the pipeline.
The lesson is this: Snowflake’s Dynamic Tables are for when you want the platform to handle the orchestration at the cost of less control. Databricks DLT is for when you want to tune the underlying Spark engine at the cost of more configuration complexity.
If you are running in a constrained budget, never use TARGET_LAG values below 15 minutes unless you have a strict warehouse limit. And for the love of everything, monitor your SYSTEM$DYNAMIC_TABLE_REFRESH_HISTORY table daily. If you aren't watching your refresh cycles, the platform is definitely watching your wallet.
We now have a custom Terraform module that enforces TARGET_LAG values. If a developer tries to deploy a DYNAMIC TABLE with a lag under 15 minutes, the CI/CD pipeline throws an error. We treat "low latency" as a feature that requires a manual override and a cost-benefit analysis.
Declarative infrastructure isn't magic. It’s just code that you can’t easily debug. When you define a pipeline, you aren't just writing SQL; you are writing a contract with your cloud provider’s billing department. Make sure you know what you’re signing.
Cover photo by U. Storsberg on Unsplash.
Top comments (0)