Workload

Performance tuning & optimization for Hadoop legacy cluster → BigQuery

Hadoop-era performance habits (partition discipline, file scans, and script-based optimizations) don’t translate directly. We tune BigQuery layout and SQL so pruning works, bytes scanned stays stable, and SLAs hold as volume and concurrency grow.

Book assessment Explore workloads

Quick answer

Hadoop-era performance habits (partition discipline, file scans, and script-based optimizations) don’t translate directly. We tune BigQuery layout and SQL so pruning works, bytes scanned stays stable, and SLAs hold as volume and concurrency grow.

Back to pair page

Context

Why this breaks

In Hadoop clusters, performance is often enforced by discipline: always filter partitions, avoid full scans, and rely on file/partition layout. In BigQuery, cost and runtime are driven by bytes scanned, slot time, and how well queries prune partitions. After cutover, teams frequently see scan blowups and slow refreshes because query shapes, partitioning, and semi-structured parsing boundaries weren’t redesigned for BigQuery. Common post-cutover symptoms:

  • Partition pruning fails (filters wrap partition columns or cast them), causing large scans - Wide joins and heavy aggregations reshuffle large datasets; BI queries slow down - Repeated parsing/extraction of JSON/string fields becomes expensive - Incremental refreshes touch too much history each run - Spend becomes unpredictable because there are no baselines, guardrails, or regression gates Optimization replaces “Hadoop partition discipline” with BigQuery-native pruning, layout, and governance—backed by measurable evidence.

Approach

How conversion works

  • Baseline the top workloads: identify the most expensive and most business-critical queries/pipelines (dashboards, marts, incremental refreshes). - Diagnose root causes: partition pruning, join patterns, repeated parsing/extraction, and refresh/apply scope. - Tune table layout: consolidate partitioning strategy and choose clustering aligned to real access paths (filters + join keys). - Rewrite for pruning and reuse: predicate pushdown-friendly filters, pre-aggregation/materializations, and centralized typed extraction for semi-structured fields. - Capacity & cost governance: reservations/on-demand posture, concurrency controls, and guardrails for expensive queries. - Regression gates: store baselines and enforce thresholds so improvements persist.

Coverage

Supported constructs

Representative tuning levers we apply for Hadoop legacy cluster → BigQuery workloads.

SourceTargetNotes
Hadoop partition discipline (year/month/day partitions)Consolidated DATE/TIMESTAMP partitioning + pruning-first SQLReduce filter complexity and prevent pruning defeat.
Wide joins and heavy aggregationsPruning-aware joins + pre-aggregation/materializationsStabilize BI refresh and reduce scan bytes.
String/JSON parsing in queriesTyped extraction tables + reuseExtract once, cast once, reuse everywhere.
Repeated full refreshesBounded refresh/apply windowsAvoid touching more history than necessary each run.
Ad-hoc expensive queriesGovernance guardrails + cost controlsPrevent scan blowups and surprise bills.
Cluster scaling for concurrencySlots/reservations + concurrency postureStabilize SLAs under peak usage.

Compare

How workload changes

TopicHadoop legacy clusterBigQueryNotes
Primary cost driverAvoid HDFS scans via partition predicatesBytes scanned + slot timePruning and query shape dominate spend.
Data layout impactFile/partition layout is the main leverPartitioning/clustering must match access pathsLayout becomes an explicit design decision in BigQuery.
Semi-structured handlingStrings + UDF parsing commonTyped extraction boundaries recommendedCentralize parsing to reduce repeated compute and drift.
Optimization styleOperational discipline + scriptsPruning-aware rewrites + layout + regression gatesTuning is continuous and measurable via baselines.

Examples

Examples

Illustrative BigQuery optimization patterns after Hadoop migration: enforce pruning, extract once into typed columns, pre-aggregate for BI, and store baselines for regression gates.

01_pruning_guard.sql
-- Pruning-first query shape (table partitioned by event_date)
SELECT
  COUNT(*) AS rows
FROM `proj.mart.events`
WHERE event_date BETWEEN @start_date AND @end_date;
02_extract_once_typed.sql
-- Extract semi-structured fields once into typed columns
CREATE OR REPLACE TABLE `proj.stg.events_typed`
PARTITION BY event_date
CLUSTER BY customer_id AS
SELECT
  event_date,
  CAST(JSON_VALUE(payload, '$.customer_id') AS STRING) AS customer_id,
  CAST(JSON_VALUE(payload, '$.status') AS STRING) AS status,
  CAST(JSON_VALUE(payload, '$.amount') AS NUMERIC) AS amount,
  event_ts
FROM `proj.raw.events_json`
WHERE event_date BETWEEN @start_date AND @end_date;
03_preaggregate_for_bi.sql
-- Pre-aggregate for repeated BI scans
CREATE OR REPLACE TABLE `proj.mart.events_daily`
PARTITION BY d
CLUSTER BY country AS
SELECT
  event_date AS d,
  country,
  COUNT(*) AS events,
  SUM(amount) AS amount
FROM `proj.stg.events_typed`
WHERE event_date BETWEEN @start_date AND @end_date
GROUP BY 1,2;
04_perf_baselines.sql
-- Store performance baselines for regression gates (simplified)
CREATE TABLE IF NOT EXISTS `proj.control.perf_baselines` (
  artifact_id STRING,
  captured_at TIMESTAMP,
  runtime_ms INT64,
  bytes_scanned INT64,
  slot_ms INT64
);

Workload Assessment

Replace Hadoop-era tuning with BigQuery-native pruning

We identify your highest-cost migrated workloads, consolidate partitioning, tune pruning and table layout, and deliver before/after evidence with regression thresholds—so performance improves and stays stable.

Book assessment

Avoid

Common pitfalls

  • Defeating partition pruning: wrapping partition columns in functions/casts in WHERE clauses.
  • Fragmented partitioning: migrating year/month/day partitions without consolidating into a single DATE partition key.
  • Clustering by folklore: clustering keys chosen without evidence from predicates and join keys.
  • Repeated parsing: extracting fields from JSON/strings repeatedly instead of extracting once into typed columns.
  • Over-materialization: too many intermediates without controlling refresh cost.
  • Ignoring concurrency: BI refresh spikes overwhelm slots/reservations and create tail latency.
  • No regression gates: the next model change brings scan bytes back up.

Proof

Validation approach

  • Baseline capture: runtime, bytes scanned, slot time, and output row counts for top workloads.
  • Pruning checks: confirm partition pruning and predicate pushdown on representative parameters and boundary windows.
  • Before/after evidence: demonstrate improvements in runtime and scan bytes; document tradeoffs.
  • Correctness guardrails: golden queries and KPI aggregates ensure tuning doesn’t change semantics.
  • Regression thresholds: define alerts (e.g., +25% bytes scanned or +30% runtime) and enforce via CI or scheduled checks.
  • Operational monitors: dashboards for scan bytes, slot utilization, failures, and refresh SLA adherence.

Execution

Migration steps

A sequence that improves performance while protecting semantics.

  1. 01

    Identify top cost and SLA drivers

    Rank queries and pipelines by bytes scanned, slot time, and business criticality. Select a tuning backlog with clear owners.

  2. 02

    Create baselines and targets

    Capture current BigQuery job metrics (runtime, scan bytes, slot time) and define improvement targets. Freeze golden outputs so correctness doesn’t regress.

  3. 03

    Consolidate layout: partitioning and clustering

    Move from fragmented `year/month/day` patterns to DATE partitioning aligned to filters and refresh windows. Choose clustering keys based on observed predicates and join keys.

  4. 04

    Rewrite for pruning and reuse

    Apply pruning-aware rewrites, reduce reshuffles, centralize semi-structured parsing into typed extraction tables, and pre-aggregate for heavy BI patterns.

  5. 05

    Capacity posture and governance

    Set reservations/on-demand posture, tune concurrency for BI refresh peaks, and implement guardrails to prevent scan blowups from new queries.

  6. 06

    Add regression gates

    Codify performance thresholds and alerting so future changes don’t reintroduce scan blowups or missed SLAs. Monitor post-cutover metrics continuously.

FAQ

Frequently asked questions

Why do costs spike after migrating from Hadoop to BigQuery? +

Most often because pruning was lost: partition predicates don’t translate cleanly, or filters defeat partition elimination. Consolidating partitioning and rewriting queries for pruning-first filters usually yields the biggest gains.

How do you keep optimization from changing results? +

We gate tuning with correctness checks: golden queries and KPI aggregates. Optimizations only ship when outputs remain within agreed tolerances.

How do you handle semi-structured parsing costs? +

We centralize extraction into typed tables so parsing happens once. This reduces both drift (type intent is explicit) and cost (no repeated JSON/string extraction).

Do you cover reservations and concurrency planning? +

Yes. We recommend a capacity posture (on-demand vs reservations), concurrency controls for BI refresh spikes, and monitoring/guardrails so performance stays stable as usage grows.

Optimization Program

Prevent scan blowups with regression gates

Get an optimization backlog, tuned partitioning/clustering, and performance gates (runtime/bytes/slot thresholds) so future releases don’t reintroduce slow dashboards or high spend.

Book assessment
Book assessment