How We Use AI to Lower Data Warehouse Query Costs

Warehouse cost optimization is often described as a collection of SQL tricks. The bigger opportunity is to make efficiency part of the test suite.

Polyform builds and maintains data pipelines for our customers. That means every transformation we write has two jobs: produce the correct business result and do it without wasting the customer's warehouse.

We already begin from an efficient modeling structure. Polyform's Activity Schema stores canonical, timestamped business events at a stable grain. Those fact-like events can be incrementally processed and reused across datasets, reports, journeys, workflows, metrics, and dimensions. We do not need to rebuild the same giant custom join for every new output.

That baseline matters. But incremental modeling does not rescue a bad query inside the incremental window. A pipeline that runs every 30 minutes can still scan too much data, deduplicate too many rows, use the wrong pruning column, or ignore the warehouse's physical layout.

So we added an AI optimization test to the transformation validation flow.

The optimization loop

Give the AI evidence before asking it to rewrite anything

A language model can suggest style changes from SQL text alone. That is not the same as optimizing a production transformation.

The optimization test receives the saved transformation SQL, the results of the required validation tests, source-table metadata, row counts, columns, and the current partition or storage key when one exists. It can inspect warehouse metadata through read-only tools. The prompt explicitly asks for material performance improvements and rejects formatting-only rewrites.

Validation evidence is especially important for deduplication. If the test suite shows that duplicates exist and that their timestamps arrive within a bounded delay, the optimizer can use that evidence to propose a widened safety window. Without that evidence, moving a filter can silently change which row wins.

The current implementation proposes one complete candidate per optimization pass, then compares that candidate with the current SQL. We can rerun validation after applying a recommendation and continue improving, but we do not present a hidden tournament of several mysterious rewrites as certainty. A small number of understandable, reviewable steps is more useful than a pile of candidates.

The expensive ROW_NUMBER pattern

One of the highest-value cases is a window function running before an incremental filter can prune the input.

Imagine a source table that receives repeated versions of the same event. The transformation needs the latest row for each event_id. A natural query is:

SELECT
  event_id AS activity_id,
  created_at AS ts,
  customer_id AS customer
FROM northlane_raw.track_events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY event_id
  ORDER BY loaded_at DESC
) = 1

The logic is clear, but the window function may need to rank the entire source before the incremental cutoff outside the transformation can help. Window functions, distinct operations, and some nested query shapes can act as optimizer barriers. When that happens, “incremental pipeline” does not necessarily mean “small scan.”

For the synthetic Northlane example, the validation fixture says source duplicates arrive within a 10-hour safety window. That allows the candidate to push a bare loaded_at predicate into the windowed input:

SELECT activity_id, ts, customer
FROM (
  SELECT
    event_id AS activity_id,
    created_at AS ts,
    customer_id AS customer,
    ROW_NUMBER() OVER (
      PARTITION BY event_id
      ORDER BY loaded_at DESC
    ) AS row_number
  FROM northlane_raw.track_events
  WHERE loaded_at >= TIMESTAMP_ADD(
    SAFE_CAST('{from_sync_time}' AS TIMESTAMP),
    INTERVAL -10 HOUR
  )
) AS deduped
WHERE row_number = 1

The 10 hours are not a universal tuning constant. They are part of this synthetic example's evidence. Another source may need two days, may need a different timestamp, or may not be safe to prune before deduplication at all.

The optimizer prompt requires the physical pruning column to remain bare on the left side of the comparison. Wrapping the column in a cast or function can prevent the warehouse from using partition or micro-partition metadata effectively. Cast the parameter when needed; do not casually transform the storage key.

Storage recommendations must speak the warehouse's language

Query text is only half of warehouse performance. The same logical filter can be cheap or expensive depending on how the table is physically organized.

The optimization test can recommend a partition, clustering, sort, or indexing action based on the connected warehouse and the source metadata. Those actions remain separate from the SQL recommendation because they have different consequences and permissions.

Warehouse	Physical recommendation	What the optimizer is trying to improve
BigQuery	Partition and cluster columns	Reduce bytes read through partition pruning and clustered block pruning.
Snowflake	Clustering key where justified	Improve micro-partition pruning on large tables with useful filter patterns.
Redshift	Sort key and related layout advice	Let range predicates skip blocks and help the planner choose better operations.
Databricks	Liquid clustering columns	Organize data for skipping while letting layout evolve with query patterns.
SQL Server	Index recommendation	Reduce scans for repeated filters and joins when an index is truly warranted.

A timestamp used in every incremental run is often a strong physical key. It is not automatically the right key. The source size, cardinality, write pattern, existing layout, and common query filters all matter. Small tables may not benefit enough to justify maintenance. A recommendation should say why the change is material, not merely that the warehouse supports the feature.

Compare both queries over the same realistic window

“This SQL looks faster” is not evidence. The optimizer compiles both versions against the connected warehouse with the same timestamp cutoff.

Polyform currently uses a rolling 30-day window for this comparison. Both the current and recommended SQL receive the same from_sync_time, and both preserve the same outer incremental filter. That controls the comparison: the candidate cannot appear cheaper simply because it was tested on less business time.

The estimator is warehouse-specific:

• BigQuery can use a dry run and compare estimated bytes processed.
• Snowflake can expose plan information such as assigned partitions and bytes.
• Redshift provides relative plan cost through EXPLAIN.
• SQL Server exposes estimated subtree cost.
• Databricks can return plan statistics when the engine provides a comparable total; otherwise Polyform marks the comparison unavailable.

These are estimates, not a universal unit of money. Redshift's own documentation warns that plan cost is relative rather than a precise runtime or memory prediction. BigQuery dry-run bytes are more directly connected to on-demand scan cost, but still do not capture every operational effect. We normalize what can be compared inside one warehouse and refuse to invent a percentage when the plans do not provide comparable totals.

The plan estimators run in parallel so the test does not add unnecessary serial latency. The result can show improvement, no meaningful change, regression, or unavailable evidence. A recommendation that reads nicely but produces a worse plan should say so.

The optimizer is deliberately advisory

The AI optimization test runs after the required transformation tests and can still provide advice when another test fails. That does not mean the optimization cures the failure. It means performance findings can be useful while the underlying transformation remains unapproved.

The product therefore keeps the boundaries visible:

• The summary and findings explain what should change and why.
• Storage actions are explicit and individually applied.
• The Current and Recommended tabs let the reviewer inspect the full query.
• The efficiency label says “estimated” and names the matched 30-day cutoff.
• The update action changes the draft, then validation runs again.

That last point matters. SQL optimization is a constrained program rewrite. The new query must preserve the output schema, grain, deduplication semantics, incremental boundary, and business meaning. A cheaper wrong query is not an optimization.

How I would measure the savings in production

The plan comparison is a gate, not the entire measurement system. After a change is approved, monitor the real transformation runs.

1. Record the baseline. Keep bytes scanned, partitions read, plan cost, execution duration, credits or slots, and run frequency for the old version.
2. Ship one change at a time. Separate a SQL rewrite from a physical table change when possible so the effect is attributable.
3. Compare like with like. Use similar data windows, warehouse size, concurrency, cache state, and source volume.
4. Watch correctness first. Reconcile row counts, duplicate behavior, freshness, and downstream outputs before celebrating the cost graph.
5. Measure the recurring total. A modest reduction on a transformation that runs every 30 minutes can matter more than a spectacular one-time query.

Warehouse pricing also differs. BigQuery on-demand users may feel scanned bytes directly. Snowflake customers think in credits and warehouse time. Databricks adds compute and storage-layout considerations. Redshift cost can depend on provisioned or serverless capacity. Translate the technical improvement into the customer's actual billing and operating model.

Why this belongs inside the data product

A generic SQL assistant can rewrite one query. Polyform can connect the rewrite to the transformation's role, validation evidence, source metadata, incremental contract, physical tables, downstream uses, and accountable review.

That context is what turns “make this query faster” into a safer engineering loop.

The same principle runs through our managed data foundation. Good data architecture is not only correct today. It is understandable, reusable, repairable, and economical to run tomorrow.

AI is useful here because the search space is broad. It can recognize optimizer barriers, examine source shapes, reason about warehouse-specific layouts, and produce a complete candidate faster than a person starting from a blank page. The warehouse is useful because it can provide a plan. Validation is useful because it constrains the rewrite. The person is useful because estimates and tests still do not own the business consequence.

Put those pieces together and warehouse optimization stops being an occasional cleanup project. It becomes a normal quality check every time the data logic changes.

References and further reading

• Google Cloud: BigQuery query plan and timeline
• Google Cloud: BigQuery partitioned tables
• Google Cloud: BigQuery clustered tables
• Snowflake: EXPLAIN
• Snowflake: micro-partitions and data clustering
• Amazon Redshift: creating and interpreting a query plan
• Amazon Redshift: choosing sort keys
• Databricks: EXPLAIN
• Databricks: liquid clustering
• Microsoft Learn: SQL Server indexes with included columns
• Microsoft Learn: SQL Server query processing architecture