JavaScript UDFs in dbt BigQuery: The Slot Cost You're Missing

JavaScript UDFs in dbt BigQuery Are Silently Draining Your Slot Budget

JavaScript UDFs in dbt BigQuery cost more than most teams realise. Because they require BigQuery to spin up a JavaScript subprocess for every row they process, they consume significantly more slot resources than equivalent native SQL logic — and they permanently disable the BigQuery query cache for any model that calls them. If you are running JavaScript UDFs across multiple dbt models on a shared slot reservation, you are paying for that overhead on every single run, silently, with no warning in your job metadata.

This is not a theoretical edge case. It is a pattern we see repeatedly when auditing the dbt projects of fast-growing companies on BigQuery — often at Series A or Series B, when the query volume has grown but the engineering team has not yet had time to revisit the business logic choices made twelve months earlier. A JavaScript UDF that looked harmless at 500k rows a day becomes a meaningful cost and latency problem at 50 million rows. By the time someone notices, it has already been embedded in half a dozen downstream models.

This post explains exactly what is happening, how to find the offenders in your project, and how to decide whether to replace them, restructure them, or govern them properly going forward.

Why Do JavaScript UDFs Cost More Than SQL UDFs in BigQuery?

To understand the cost, you need to understand what BigQuery actually does when it encounters a JavaScript UDF.

Native SQL UDFs are compiled into the same execution plan as the surrounding query. BigQuery's query engine can optimise them alongside the rest of the SQL, push predicates through them where possible, and execute them using the same columnar, vectorised processing it uses for everything else. They are fast, cache-friendly, and slot-efficient.

JavaScript UDFs are fundamentally different. When BigQuery encounters a JavaScript UDF in a query, it must instantiate a separate JavaScript subprocess to execute that logic. Each row is processed individually through that subprocess rather than in the batch, columnar fashion that makes BigQuery fast. The result is a query that, even if logically identical to a SQL UDF equivalent, can consume dramatically more slot-time — because it is doing the same work in a far less efficient execution mode.

Google's own documentation confirms the asymmetry plainly: JavaScript UDFs typically consume more slot resources compared to standard SQL queries, decreasing job performance. The official guidance is equally direct: if the function can be expressed in SQL, it is often more optimal to run it as a standard SQL query job. And critically, calling a JavaScript UDF requires the instantiation of a subprocess — spinning up that process and running the UDF directly impacts query performance.

There is a second problem that compounds the cost: query cache invalidation. BigQuery's query cache can eliminate the compute cost of re-running identical queries entirely — but any model containing a JavaScript UDF will never benefit from that cache, because JavaScript UDFs are treated as non-deterministic by default. We have written separately about how non-deterministic functions break cache hits in dbt BigQuery; JavaScript UDFs are one of the most common causes we encounter in practice.

For teams on on-demand pricing, this means you are paying for full query execution every single time a model with a JavaScript UDF runs, even if nothing has changed in the underlying data. For teams on slot reservations, it means those UDF-heavy models are holding slots for longer than equivalent SQL logic would — reducing the throughput available to everything else in your dbt run.

---

📺 Watch: Create a Streaming Data Pipeline with Google Cloud Dataflow : Pub/Sub to BigQuery

---

How to Find JavaScript UDFs in Your dbt BigQuery Project

The first step is knowing what you are actually running. There are two places JavaScript UDFs appear in a dbt BigQuery project, and both need to be audited.

1. Inline temporary UDFs inside model SQL files

The most common pattern we encounter is a JavaScript UDF defined inline at the top of a dbt model using CREATE TEMP FUNCTION. This looks like:

CREATE TEMP FUNCTION parse_country_code(raw_code STRING)
RETURNS STRING
LANGUAGE js AS """
  if (raw_code === null) return null;
  return raw_code.trim().toUpperCase().substring(0, 2);
""";

SELECT
  transaction_id,
  parse_country_code(raw_country) AS country_code,
  amount
FROM {{ ref('stg_transactions') }}

This kind of pattern almost always started as a one-off solution to a parsing or string-manipulation problem that felt easier to write in JavaScript than SQL. The issue is that it then gets copied into other models, refactored slightly, and eventually there are four slightly different versions of essentially the same function scattered across the project — none of them version-controlled as a reusable asset, all of them running the JavaScript subprocess on every dbt run.

To find every instance of this pattern in your project, run:

grep -r "LANGUAGE js" models/ --include="*.sql" -l

That will return every model file that contains a JavaScript UDF definition. Review each one. Ask for each: can this logic be expressed in native BigQuery SQL functions? For string parsing, date manipulation, conditional logic, and most numeric transformations, the answer is almost always yes.

2. Persistent UDFs registered in BigQuery outside of dbt

The second pattern is harder to track: JavaScript UDFs that were created manually in BigQuery (via the console or a one-off script) and are referenced inside dbt models using their fully-qualified path. Because these were created outside of dbt, they do not appear in the dbt DAG, are not versioned, and are not tested. They are invisible to dbt's dependency graph — which means if the dataset they live in is dropped, recreated, or the access permissions change, the downstream models silently break.

To audit persistent UDFs across your BigQuery project, query the INFORMATION_SCHEMA:

SELECT
  routine_catalog,
  routine_schema,
  routine_name,
  routine_definition,
  data_type
FROM `your_project`.INFORMATION_SCHEMA.ROUTINES
WHERE routine_type = 'FUNCTION'
  AND routine_definition LIKE '%LANGUAGE js%'

Any result here that is referenced in a dbt model but not defined inside dbt represents a governance gap — invisible lineage, no test coverage, no documentation, and full JavaScript UDF slot overhead on every run that touches it.

If you want to understand the actual slot impact of your JavaScript UDF-heavy models, cross-reference INFORMATION_SCHEMA.JOBS against your known model names and look at the total_slot_ms field. Compare that figure against structurally similar models that do not use JavaScript UDFs. The gap is usually immediately obvious.

If you want to go deeper on query cost attribution in dbt, our post on dbt Query Labels in BigQuery covers how to tag every dbt job with model-level metadata so you can accurately attribute cost at the model level — which makes this kind of UDF cost audit significantly more actionable.

When Should You Actually Use a JavaScript UDF in dbt BigQuery?

This is where most posts on this topic become unhelpfully binary — "never use JavaScript UDFs" is not the right answer. There are legitimate cases where JavaScript UDFs are the correct tool, and the goal is not to eliminate them but to use them deliberately rather than by default.

Legitimate use cases for JavaScript UDFs in BigQuery:

• Complex regex or parsing logic that exceeds BigQuery's native REGEXP capabilities — for example, validating and normalising IBAN strings or parsing non-standard date formats from legacy source systems where the transformation logic genuinely requires looping or conditional branching that SQL cannot express cleanly.
• Calling a JavaScript library stored in GCS — for example, a cryptographic hashing function or a custom encoding scheme that is standardised across the business and needs to be consistent across multiple environments. This is a valid architectural choice, but it should be version-controlled, documented, and governed.
• Logic that would otherwise produce deeply nested, unmaintainable SQL — there are cases where the JavaScript equivalent is genuinely clearer and the performance trade-off is acceptable because the model runs infrequently or on a small dataset.

Cases where JavaScript UDFs should be replaced:

• String trimming, case normalisation, substring extraction — these are all native BigQuery SQL functions and should never be in a JavaScript UDF
• Conditional logic (CASE WHEN) dressed up as a JavaScript function — we have seen this pattern more times than we care to admit
• Date parsing that can be handled with PARSE_DATE, PARSE_TIMESTAMP, or FORMAT_DATE
• Numeric calculations that are straightforward arithmetic

The decision framework is straightforward: if you can write it in SQL without producing logic that is significantly harder to read and maintain than the JavaScript equivalent, write it in SQL. The performance and cost advantages are not marginal — they are structural.

If you are unsure whether to keep a JavaScript UDF or replace it, benchmark both versions on a representative data sample using dry-run cost estimates. The difference in estimated bytes processed will not capture the slot overhead difference, but running both versions in INFORMATION_SCHEMA.JOBS and comparing total_slot_ms will give you a clear signal.

If you are looking to implement a structured audit and governance process for your dbt project, explore how Fintel Analytics approaches this — we work with growth-stage businesses globally to identify exactly these kinds of silent cost and performance problems and build the governance frameworks to prevent them recurring.

How to Replace a JavaScript UDF With Native SQL in dbt

Once you have identified the JavaScript UDFs that should be replaced, the migration path is usually straightforward. The key is to validate that the SQL replacement produces bit-for-bit identical results before removing the JavaScript version.

Here is a real example of the kind of replacement we typically make. A common pattern we have encountered is a JavaScript UDF used to clean and normalise a currency code field from a payments source:

-- Before: JavaScript UDF in dbt model
CREATE TEMP FUNCTION clean_currency(raw STRING)
RETURNS STRING
LANGUAGE js AS """
  if (!raw) return null;
  return raw.trim().toUpperCase();
""";

SELECT
  payment_id,
  clean_currency(raw_currency_code) AS currency_code
FROM {{ ref('stg_payments') }}

-- After: Native SQL replacement
SELECT
  payment_id,
  UPPER(TRIM(raw_currency_code)) AS currency_code
FROM {{ ref('stg_payments') }}

The SQL version is shorter, faster, cache-eligible, and uses zero additional slot overhead. There is no meaningful trade-off here — the JavaScript version was created because it felt more familiar to the person who wrote it, not because it was the right tool.

For more complex cases where you do need to retain business logic that currently lives in a JavaScript UDF, the right pattern is to bring it inside dbt as a versioned, documented resource and abstract the SQL equivalent into a dbt macro. This keeps the logic in one place, version-controlled, and visible in the DAG.

For cases where a JavaScript UDF genuinely cannot be replaced — typically the cryptographic or library-dependent cases — the correct governance approach is to register it as a persistent UDF in a dedicated, access-controlled BigQuery dataset, reference it consistently from dbt models, document it explicitly in your dbt project (using a custom schema.yml entry or a project-level README that is actually maintained), and monitor its slot consumption separately using query labels.

A pattern we see repeatedly in more mature dbt projects is the use of a shared utils dataset in BigQuery that houses persistent UDFs for legitimate cross-project use cases, governed by the same IAM principles as the rest of the data platform. If you are managing access controls across datasets in BigQuery, our post on dbt Authorized Views in BigQuery covers the access control patterns that apply equally well to UDF datasets.

What Does This Actually Look Like in Production?

In our work with early-stage fintech and payments companies, JavaScript UDFs appear most frequently in two contexts.

First: data ingestion from third-party APIs or legacy systems where the source data contains non-standard formats. The engineering team writes a quick JavaScript UDF to handle a one-off parsing problem, it gets committed, and then lives in the model indefinitely long after the source format was standardised. We have seen single models with three or four JavaScript UDFs stacked on top of each other — each one added by a different engineer, none of them documented, collectively adding significant slot overhead to a model that runs every hour.

Second: fintech companies that have migrated calculations from spreadsheets into BigQuery without a proper data engineering review. The spreadsheet formula gets transliterated into JavaScript because that syntax is more familiar to the analyst who did the migration, rather than being rewritten in idiomatic BigQuery SQL. This is a direct manifestation of the broader pattern we see across the industry: business-critical logic landing in the wrong layer, in the wrong language, with no governance and no testing.

In one engagement with a Series A payments company, we audited a dbt project that had accumulated eleven JavaScript UDFs across seven models over eighteen months. None of them were documented. Three of them contained logic that had been superseded by changes to the source data but had never been updated — meaning the models were running transformation logic that was actively wrong. Nine of the eleven were replaceable with native SQL functions in under an hour. Migrating those nine eliminated the JavaScript subprocess overhead entirely for those models, restored cache eligibility, and reduced average model run time measurably. The remaining two were legitimate use cases that we governed properly as versioned persistent UDFs.

That kind of audit — finding the accumulated debt, separating what is legitimate from what is lazy shorthand, and establishing the governance to prevent it accumulating again — is the actual delivery work that makes a difference to a growing company's data platform.

Frequently Asked Questions

Q: Do JavaScript UDFs in BigQuery always cost more than SQL UDFs?

A: In practice, yes — almost always. JavaScript UDFs require BigQuery to instantiate a separate JavaScript subprocess per row, which consumes significantly more slot resources than the vectorised, columnar execution used for native SQL. Google's own documentation confirms that JavaScript UDFs typically consume more slot resources and decrease job performance. The exception is logic so complex that the SQL equivalent would force BigQuery into a worse execution plan, but this is rare.

Q: Can JavaScript UDFs in dbt BigQuery benefit from the query cache?

A: No. JavaScript UDFs are treated as non-deterministic by default in BigQuery, which permanently disables query cache eligibility for any model that calls them. This means that even if the underlying data has not changed, BigQuery will re-execute the full query and charge the full compute cost on every run. For frequently-run dbt models, this can represent a significant and entirely avoidable cost.

Q: Should I use dbt macros instead of JavaScript UDFs in BigQuery?

A: For most business logic, yes — dbt macros generate native SQL at compile time and carry none of the slot overhead or cache-disabling behaviour of JavaScript UDFs. Macros are also version-controlled, testable, and visible in the dbt DAG. JavaScript UDFs should be reserved for logic that genuinely cannot be expressed in SQL, such as complex string manipulation requiring looping, or calls to external JavaScript libraries.

Q: How do I find JavaScript UDFs hidden in my BigQuery project outside of dbt?

A: Query the INFORMATION_SCHEMA.ROUTINES view in your BigQuery project and filter for rows where routine_definition contains 'LANGUAGE js'. This surfaces all persistent UDFs registered in BigQuery, including any that were created manually outside of dbt and are not visible in your dbt DAG. Cross-reference these against your dbt model SQL files to identify any untracked dependencies.

Q: What is the right way to manage a legitimate JavaScript UDF in a dbt BigQuery project?

A: Register it as a persistent UDF in a dedicated, access-controlled BigQuery dataset — not inline in individual model files. Reference it consistently from dbt models using its fully-qualified path. Document it explicitly in your dbt project. Monitor its slot consumption using dbt query labels and INFORMATION_SCHEMA.JOBS. Treat it as a first-class asset in your data platform governance, not as a one-off workaround.

---

JavaScript UDFs in dbt BigQuery are one of those problems that are easy to dismiss as minor — until you run the numbers on a project that has been accumulating them for eighteen months and realise the slot overhead is non-trivial, the cache is permanently cold on a dozen high-frequency models, and half the logic is wrong because nobody knew where it lived. At Fintel Analytics, we have helped fintech, payments, and e-commerce companies audit exactly this kind of accumulated platform debt — replacing what should never have been there, governing what legitimately should be, and building the standards that prevent it recurring. If your dbt project on BigQuery is showing symptoms — rising run times, unpredictable costs, or business logic that nobody fully trusts — that is a solvable problem, and the fix is usually faster than you expect.