Workload

UDFs & procedural utilities for Spark SQL → BigQuery

Re-home reusable logic—from Spark SQL UDFs and notebook “macro” utilities to BigQuery UDFs and stored procedures—so behavior stays stable under reruns, backfills, and changing upstream schemas.

Book assessment Explore workloads

Quick answer

Re-home reusable logic—from Spark SQL UDFs and notebook “macro” utilities to BigQuery UDFs and stored procedures—so behavior stays stable under reruns, backfills, and changing upstream schemas.

Back to pair page

Context

Why this breaks

Spark estates frequently hide business rules in “reusable logic”: SQL UDFs, notebook helper functions, and script-driven utilities that generate SQL and apply control-table semantics. During migration, teams translate SQL statements but miss these utilities—then outputs drift because typing, NULL behavior, regex semantics, and timezone handling differ in BigQuery. Common symptoms after migration:

  • UDF outputs drift due to type coercion and NULL handling differences - Notebook macros that relied on Spark session settings or temporary views stop matching - Error handling changes (fail-fast vs continue), breaking orchestration expectations - Dynamic SQL and identifier resolution behaves differently in BigQuery - Side effects (audit/control tables) aren’t modeled, so retries/backfills double-apply or skip A successful migration consolidates scattered utilities into explicit BigQuery routines with a behavior contract and a replayable harness that proves parity.

Approach

How conversion works

  • Inventory & classify reusable logic: Spark SQL UDFs, notebook utilities/macros, and call sites across pipelines and BI queries. - Extract the behavior contract: inputs/outputs, typing, NULL rules, side effects, error semantics, and performance constraints. - Choose BigQuery target form per asset:
  • SQL UDF for pure expressions - JavaScript UDF for complex logic (string/regex/object handling) - Stored procedure (SQL scripting) for multi-statement control flow and dynamic SQL - Set-based refactor where loop/cursor patterns exist in notebooks/scripts - Translate and normalize: explicit casts, null-safe comparisons, timezone intent, and deterministic ordering where logic depends on ranking/dedup. - Validate with a harness: golden inputs/outputs, branch coverage, failure-mode tests, and side-effect assertions—then integrate into representative pipelines.

Coverage

Supported constructs

Representative Spark procedural/macro constructs we commonly migrate to BigQuery routines (exact coverage depends on your estate).

SourceTargetNotes
Spark SQL UDFsBigQuery SQL UDFsPure expressions mapped with explicit casts and NULL behavior.
Notebook macro utilitiesBigQuery procedures + views/UDFsHidden dependencies made explicit; interfaces stabilized.
Regex-heavy transformsBigQuery REGEXP_* functions or JS UDFsRegex edge cases validated with golden cohorts.
Dynamic SQL generationEXECUTE IMMEDIATE with safe parameterizationNormalize identifier resolution; reduce drift and injection risk.
Control-table driven rerunsIdempotency markers + applied-window trackingRetries/backfills become safe and auditable.
Timestamp/timezone helpersExplicit timezone conversions and intentPrevents boundary-day drift in reporting.

Compare

How workload changes

TopicSpark SQLBigQueryNotes
Where logic livesNotebooks, jobs, Spark UDFs, scattered utility codeCentralized routines (UDFs/procedures) with explicit contractsMigration consolidates and stabilizes reusable logic.
Typing and coercionSpark implicit casts often toleratedExplicit casts recommended for stable outputsValidation focuses on mixed-type branches and join keys.
Operational behaviorReruns/retries often encoded in job structureIdempotency and side effects must be explicitHarness proves behavior under retries/backfills.

Examples

Examples

Illustrative patterns for moving Spark-era UDF and macro utilities into BigQuery routines. Adjust datasets, types, and identifiers to match your environment.

01_sql_udf_bigquery.sql
-- BigQuery SQL UDF example
CREATE OR REPLACE FUNCTION `proj.util.safe_div`(n NUMERIC, d NUMERIC) AS (
  IF(d IS NULL OR d = 0, NULL, n / d)
);
02_js_udf_bigquery.sql
-- BigQuery JavaScript UDF example (when logic is complex)
CREATE OR REPLACE FUNCTION `proj.util.normalize_code`(x STRING)
RETURNS STRING
LANGUAGE js AS """
  if (x === null) return null;
  return x.trim().toUpperCase();
""";
03_proc_dynamic_sql_bigquery.sql
-- BigQuery stored procedure with dynamic SQL + parameter binding
CREATE OR REPLACE PROCEDURE `proj.util.refresh_window`(start_d DATE, end_d DATE)
BEGIN
  DECLARE sql STRING;
  SET sql = '''
    DELETE FROM `proj.mart.fact_orders`
    WHERE event_date BETWEEN @s AND @e;

    INSERT INTO `proj.mart.fact_orders`
    SELECT * FROM `proj.stg.fact_orders`
    WHERE event_date BETWEEN @s AND @e;
  ''';

  EXECUTE IMMEDIATE sql USING start_d AS s, end_d AS e;
END;
04_harness_example.sql
-- Minimal harness pattern: compare routine outputs to golden expectations
CREATE TEMP TABLE golden AS
SELECT 10 AS n, 2 AS d, 5 AS expected UNION ALL
SELECT 10, 0, NULL;

SELECT
  n, d,
  `proj.util.safe_div`(n, d) AS got,
  expected,
  IF(`proj.util.safe_div`(n, d) IS NOT DISTINCT FROM expected, 'PASS', 'FAIL') AS verdict
FROM golden;

Workload Assessment

Migrate reusable logic with a test harness

We inventory your UDFs and macros, migrate a representative subset into BigQuery routines, and deliver a harness that proves parity—including side effects and failure behavior.

Book assessment

Avoid

Common pitfalls

  • Hidden dependencies: notebook macros rely on implicit session configs, temp views, or catalogs.
  • Mixed-type branches: CASE/IF returns different types; BigQuery needs explicit casts.
  • NULL semantics drift: null-safe equality and string functions behave differently unless explicit.
  • Dynamic SQL risks: string concatenation without bindings/escaping causes drift and security risk.
  • Side effects ignored: control/audit writes not recreated; reruns become unsafe.
  • Row-by-row patterns: loops in notebooks are costly in BigQuery; refactor to set-based SQL.
  • No harness: without replayable tests, parity becomes a debate at cutover.

Proof

Validation approach

  • Compile + interface checks: each routine deploys; argument/return types match the contract.
  • Golden tests: curated input sets validate outputs, including NULL-heavy and boundary values.
  • Branch + failure-mode coverage: exercise key branches and expected failures.
  • Side-effect verification: assert expected writes to control/log/audit tables and idempotency under retries/backfills.
  • Integration replay: run routines inside representative pipelines and compare downstream KPIs/aggregates.
  • Performance gate: confirm no hidden row-by-row scans; refactors validated with bytes scanned/runtime baselines.

Execution

Migration steps

A sequence that keeps behavior explicit, testable, and safe to cut over.

  1. 01

    Inventory reusable logic and call sites

    Collect Spark SQL UDFs, notebook utilities/macros, and call sites across pipelines and BI queries. Identify dependencies and side effects (control/audit tables).

  2. 02

    Define the behavior contract

    Specify inputs/outputs, typing, NULL rules, regex expectations, error semantics, side effects, and performance expectations. Decide target form (SQL UDF, JS UDF, procedure, or refactor).

  3. 03

    Convert with safety patterns

    Make casts explicit, normalize timezone intent, implement null-safe comparisons, and migrate dynamic SQL using EXECUTE IMMEDIATE with bindings and explicit identifier rules.

  4. 04

    Build a replayable harness

    Create golden input sets, boundary cases, and expected failures. Validate outputs and side effects deterministically so parity isn’t debated at cutover.

  5. 05

    Integrate and cut over behind gates

    Run routines in representative pipelines, compare downstream KPIs, validate retries/backfills, and cut over with rollback-ready criteria.

FAQ

Frequently asked questions

What are “macros” in a Spark SQL context? +

They’re usually notebook utilities and shared SQL snippets that behave like reusable functions—generating SQL, applying control-table logic, or standardizing transforms. We migrate them into BigQuery-native assets (UDFs, views, procedures) with explicit contracts.

Do we have to move everything into stored procedures? +

No. Many utilities belong as SQL UDFs or views, and some procedural logic is best refactored into set-based SQL. We choose the target form per asset to minimize risk and cost.

How do you prove parity for UDFs and procedures? +

We build a replayable harness with golden inputs/outputs, branch and failure-mode coverage, and side-effect assertions. Integration replay validates downstream KPIs before cutover.

What’s the biggest risk area? +

Implicit typing/NULL behavior differences and hidden side effects used for restartability. We make these explicit and validate under retries/backfills so behavior remains stable.

Migration Acceleration

Cut over routines with proof-backed sign-off

Get a conversion plan, review markers for ambiguous intent, and validation artifacts so procedural logic cutover is gated by evidence and rollback criteria.

Book assessment
Book assessment