Cloud Data Warehouses — Troubleshooting

A query cost far more than expected

Before assuming the query logic itself is inefficient, check whether it actually used partition pruning as intended — a query with a filter that looks like it should prune partitions, but applies that filter after a join, subquery, or view layer in a way that defeats pruning, will scan the full table despite appearing correctly filtered in the SQL. Checking the query's actual execution plan or dry-run bytes estimate (covered on the Intermediate tab) before running, not just after seeing the bill, is what catches this before it's expensive rather than after. A second common cause: a SELECT * against a wide table when only a few columns are actually needed — since columnar storage means unused columns aren't free to include, explicitly selecting only needed columns is a real, direct cost reduction, not just a style preference.

A partitioned/clustered table isn't actually getting query benefits

If a table is correctly partitioned and clustered but queries still scan far more data than expected, check the actual filter predicate against the partition/cluster column's exact type and format — a date column partition filtered with a string comparison, or a subtle type mismatch between the filter value and the column's declared type, can silently defeat partition pruning even though the query "looks" correctly filtered to a human reader. Also check whether the filter is applied directly against the base table or against a view/CTE layered on top — some warehouses don't push a filter down through every possible view/subquery boundary the way a human might assume, and confirming pruning is actually happening (via the execution plan, not assumption) is the only reliable way to know rather than guess from the SQL's apparent structure.

Dashboard queries are slow despite the underlying data being small

If dashboard query latency is high but the actual result set and underlying table are both genuinely small, the cause is more likely warehouse compute cold-start latency than data volume — a compute cluster that's been auto-suspended (idle, to save cost) takes real time to spin back up on the first query after a gap, which shows up as a slow first dashboard load, followed by fast subsequent loads while the compute stays warm. This is a real, deliberate cost-vs-latency tradeoff, not a bug — the fix is either accepting the cold-start latency for infrequently-used dashboards, or adjusting the AUTO_SUSPEND threshold longer for a compute pool serving latency-sensitive, frequently-accessed dashboards, trading some idle-time cost for consistently fast response.

Semi-structured (JSON) query performance is much worse than expected

Querying deeply nested JSON/VARIANT data directly, on every query, is inherently slower than querying an equivalent flattened, strongly-typed column — the engine has to parse the semi-structured value at query time rather than reading a pre-typed column directly. If a specific JSON field is being queried frequently and repeatedly (a dashboard filtering on it regularly, not a one-off exploratory query), the fix is extracting that specific field into a proper flattened, typed column during transformation (a dbt staging model, covered elsewhere in this academy) rather than continuing to parse it from raw JSON on every single query — reserving direct JSON querying for genuinely ad-hoc, infrequent exploration rather than a routinely-hit query path.

A scheduled ingestion/transformation job broke after an upstream schema change

If a pipeline that was working suddenly fails (or, worse, silently produces wrong data) after an upstream source added, removed, or renamed a field, this points to a schema evolution gap — semi-structured ingestion (landing raw JSON without a rigid upfront schema, covered on Intermediate) is specifically resilient to additive schema changes (a new field simply isn't queried until someone adds logic for it) but not to breaking changes (a renamed or removed field that existing transformation logic depends on). Building an explicit schema-drift detection step into the pipeline (comparing an incoming batch's actual field set against the expected set, and alerting on unexpected differences) catches this proactively — relying on a downstream transformation query silently returning NULL for a renamed field, discovered only when someone eventually notices a dashboard number looks wrong, is a much slower and more painful way to find the same problem.