Test History in BigQuery vs Snowflake

Most teams dump JUnit XML into an S3 bucket or a CI artifact store and call it observability. The moment you want to answer "which tests have regressed over the last 30 days across all branches?" or "what's the P95 runtime for our checkout suite this sprint vs last sprint?", a flat file store stops working. You need a query engine with history — and the two most common choices at scale are BigQuery and Snowflake.

The decision is not just about SQL dialect. It's about ingestion latency, clustering strategy, cost per query, and how your dashboards — Grafana, Looker, or a custom JUnit test dashboard — connect to the warehouse. Getting the schema wrong early means expensive full-table scans on every flaky-test triage query six months later.

By the end of this article you'll have a concrete schema, working SQL patterns for both platforms, and a clear read on where each warehouse wins and where it quietly costs you.

What You're Actually Storing: JUnit History as a Time-Series Fact Table

A JUnit XML result is a nested document — suites contain cases, cases carry runtime, status, and optional failure messages. Flattened into a warehouse, each test case execution becomes one row in a fact table: run_id, suite_name, test_name, status, duration_ms, branch, commit_sha, timestamp. That schema is simple, but the cardinality is brutal. A 500-test suite running on 20 branches 40 times a day produces 400,000 rows daily. At 90-day retention you're at 36 million rows before you add parallelism or matrix builds.

This is why choosing the right storage backend matters before you write your first dashboard query. BigQuery and Snowflake both handle this volume comfortably, but they partition and cluster data differently, and those differences compound when you're joining against a commit metadata table or running a 90-day window function for flakiness scoring.

Schema Design, Clustering, and SQL Patterns That Actually Perform

Start with a partitioned table. In BigQuery, partition on DATE(run_timestamp) and cluster on (suite_name, test_name). In Snowflake, use a micro-partition-friendly clustering key of (TO_DATE(run_timestamp), suite_name). Both choices mean your flakiness queries — which always filter by time range and test identity — hit only the relevant micro-partitions or partition pruning paths.

-- BigQuery: create the core fact table
CREATE TABLE test_analytics.test_runs (
  run_id        STRING       NOT NULL,
  suite_name    STRING       NOT NULL,
  test_name     STRING       NOT NULL,
  status        STRING       NOT NULL,  -- PASSED | FAILED | SKIPPED | FLAKY
  duration_ms   INT64,
  branch        STRING,
  commit_sha    STRING,
  run_timestamp TIMESTAMP    NOT NULL
)
PARTITION BY DATE(run_timestamp)
CLUSTER BY suite_name, test_name;

The Snowflake equivalent swaps INT64 for NUMBER and uses CLUSTER BY (TO_DATE(run_timestamp), suite_name, test_name) — Snowflake's automatic clustering will maintain this over time, but you pay for the compute. BigQuery's clustering is free at write time; you pay only at query time based on bytes scanned.

-- Flakiness rate: tests that fail on some runs but pass on others, last 30 days
-- Works on both platforms with minor dialect changes
SELECT
  suite_name,
  test_name,
  COUNT(*)                                              AS total_runs,
  COUNTIF(status = 'FAILED')                           AS failures,  -- BigQuery syntax
  -- Snowflake: SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END)
  ROUND(COUNTIF(status = 'FAILED') / COUNT(*) * 100, 2) AS failure_rate_pct,
  APPROX_QUANTILES(duration_ms, 100)[OFFSET(95)]       AS p95_duration_ms
  -- Snowflake: APPROX_PERCENTILE(duration_ms, 0.95)
FROM test_analytics.test_runs
WHERE run_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND branch = 'main'
GROUP BY 1, 2
HAVING failure_rate_pct BETWEEN 5 AND 95   -- true flakes, not consistently broken
ORDER BY failure_rate_pct DESC
LIMIT 50;

This query is the backbone of any JUnit test dashboard. Wire it to a Grafana panel via the BigQuery data source plugin (community) or Snowflake's JDBC connector, and you have a live flakiness leaderboard. One team reduced triage time from 22 minutes per failure to under 4 once this panel replaced manual log-diving in Jenkins. For deeper pattern work — clustering test failures by error message similarity — embedding-based pattern detection on top of this same table is the natural next step.

Cost and Latency Trade-offs

BigQuery charges per byte scanned (on-demand: ~$6.25/TB). A 30-day flakiness query on a well-clustered 36M-row table typically scans under 2 GB — under a cent. Snowflake charges per virtual warehouse second; a single XS warehouse running the same query costs roughly $0.0003 but you pay for idle time if the warehouse stays warm. BigQuery wins for bursty, ad-hoc queries. Snowflake wins when you have a steady stream of dashboard refreshes that justify a warm warehouse and benefit from result caching. Snowflake's query result cache (24-hour TTL) means repeated dashboard loads cost nothing; BigQuery has no equivalent unless you materialize results.

Where Engineers Burn Time and Money on Test Warehouse Queries

Not partitioning on timestamp from day one is the most expensive mistake. Teams often partition on branch or suite_name because those feel like natural filter dimensions. But branch cardinality is high and uneven — main has millions of rows, feature branches have dozens. Queries end up scanning every partition. Fix it: always use time as the primary partition key, then cluster or sort on the dimensions you filter by most. Migrating a 200M-row table in BigQuery after the fact means a full rewrite — expensive and disruptive.

Ingesting raw JUnit XML instead of a normalized schema is the second trap. Storing XML or JSON blobs in a STRING column and parsing at query time with JSON_EXTRACT or PARSE_XML is seductive early on. At 50M rows it becomes a query-time tax on every dashboard load. Parse at ingest — in your CI pipeline, in a Cloud Function, or via a dbt staging model — and write clean, typed columns. The ingestion complexity pays back within weeks. Teams comparing warehouse setups against Datadog CI Visibility often find the managed option attractive precisely because it sidesteps this schema debt.

Myths About Warehousing Test Results That Slow Teams Down

"Pass rate is the KPI." A suite that passes 98% of the time can still be hiding a critical path that fails every Tuesday during a downstream service deployment. Pass rate is a lagging, aggregate metric that masks timing patterns, environment-correlated failures, and duration regressions. Pass/fail metrics are misleading on their own — the warehouse is only valuable if you're querying for variance, trends, and co-occurrence, not just counting greens. Build your dashboards around failure rate by cohort (branch, author, service, time-of-day) and P95 runtime drift, not headline pass rate.

"Snowflake and BigQuery are interchangeable — just pick one." The SQL dialects diverge in ways that matter for test analytics: window functions, approximate aggregates (APPROX_QUANTILES vs APPROX_PERCENTILE), and semi-structured data handling (VARIANT in Snowflake vs JSON in BigQuery) all behave differently. More importantly, your team's existing data stack matters — if your dbt models, Looker semantic layer, and data engineering pipelines are already on Snowflake, adding BigQuery for test data creates a second governance surface. Conversely, if you're GCP-native with Pub/Sub feeding BigQuery for all other observability data, Snowflake adds unnecessary cross-cloud egress. Match the warehouse to the ecosystem, not the other way around.

The right warehouse query won't fix a broken suite, but it will tell you which five tests are responsible for 80% of your CI failures before your next sprint planning. Start with the partitioned schema above, get the 30-day flakiness query running against real data, and build your quarterly reliability scorecard from that foundation. Once the data is clean and queryable, the dashboard and alerting layers become straightforward — the hard work is always in the schema.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.