Databases
Warehouse UDF Cleanup: Remove JavaScript Helpers After Models Move
Warehouse UDF cleanup starts when JavaScript helpers, SQL wrappers, or temporary functions remain after dbt models, semantic layers, or BI calculations move. The risk is not just storage: a stale UDF can keep broad execute grants, hide business logic from lineage tools, or run code that nobody reviews.
For stale warehouse user-defined functions and JavaScript helpers, cleanup should start with query history, function dependencies, grants, model lineage, and recreate SQL. The useful output is a UDF retirement record with function signature, callers, grant scope, replacement model, recreate statement, and rollback owner: Revoke or narrow execute grants before dropping a widely shared helper, protect scheduled consumers, and make the restore or recreate path visible before final removal.
Key takeaways
- Review stale warehouse user-defined functions and JavaScript helpers through Query callers, Execute grants, Model lineage, not age alone.
- Use one reporting cycle plus ad hoc analyst and finance-close windows before deciding that quiet means unused.
- Start with the reversible move: revoke or narrow execute grants before dropping a widely shared helper.
- Slow down when breaking reports or preserving unreviewed code execution after models move is still plausible.
- Prevent repeat cleanup by making teams register shared UDFs with owner, input contract, granted roles, and review date.
Identify the Data Contract
Start with one slice of analytics warehouses, dbt projects, BI tools, scheduled queries, permission grants, and metric ownership records where the cleanup candidates are visible to both the owner and the person paying the operational cost. The best cleanup scope is small enough that owners can answer quickly but wide enough to include the attachments that make removal risky.
| Field | Why it matters |
|---|---|
| Owner | Cleanup needs a person or team that can accept the decision |
| Current purpose | A short reason to keep the item, written in present tense |
| Last meaningful use | query history, scheduled report runs, BI usage, and model builds |
| Dependency evidence | information schema references, dbt lineage, grants, notebooks, and owner review |
| Risk if wrong | The outage, data loss, access failure, or rollback gap the review must avoid |
| Next action | Keep, reduce, archive, disable, remove, or investigate |
Do not make the inventory larger than the decision. A short list with owners and evidence beats a perfect spreadsheet that nobody is willing to act on.
Database Evidence to Collect
The useful question is not “how old is it?” It is “what would break, become harder to recover, or lose accountability if this disappeared?” For warehouse UDF cleanup, collect enough evidence to answer that without relying on naming conventions.
| Check | What to look for | Cleanup signal |
|---|---|---|
| Query callers | Query history, scheduled jobs, notebooks, and BI extracts that call the function | No supported query path uses the helper |
| Execute grants | Roles, service accounts, analysts, and inherited privileges with execute access | The grant is broader than the remaining need |
| Model lineage | dbt models, semantic metrics, views, and downstream dashboards | The calculation moved to a maintained model |
| Recreate path | Function DDL, tests, replacement query, and rollback owner | The team can restore the helper if a hidden caller appears |
Use several signals together. Activity can miss monthly jobs and incident-only paths. Ownership can be stale. Cost can distract from security or recovery risk. The strongest case combines runtime data, dependency checks, owner review, and a rollback plan.
If the evidence conflicts, label the item “investigate” with a named owner and review date. That is still progress because the next review starts with a narrower question.
Example UDF Review
Use read-only metadata to find the function definition and grants before changing warehouse behavior.
SELECT routine_schema, routine_name, routine_type, data_type
FROM information_schema.routines
WHERE routine_name ILIKE '%legacy_margin%';
SELECT grantee, privilege_type
FROM information_schema.routine_privileges
WHERE routine_name = 'legacy_margin_adjustment';These checks show that the helper exists and who can execute it. They do not prove removal safety until query history and downstream reports are reviewed.
Archive Before Removal
Use the least permanent move that proves the decision. In warehouse UDF cleanup, removal is only one possible outcome; reducing size, narrowing permission, shortening retention, archiving, or disabling a trigger may produce the same benefit with less risk.
- Revoke or narrow execute grants before dropping a widely shared helper.
- Move active calculations into dbt models, semantic-layer definitions, or versioned SQL before deleting the UDF.
- Preserve function DDL and a test query beside the migration that removes the routine.
Track the cleanup candidate with a simple priority score:
| Score | Good sign | Bad sign |
|---|---|---|
| Impact | Meaningful spend, risk, toil, noise, or confusion disappears | The item is cheap and low-risk but politically distracting |
| Confidence | Owner, purpose, and dependency path are understood | The team is guessing from age or name |
| Reversibility | Restore, recreate, re-enable, or rollback path exists | Deletion would be the first real test |
| Prevention | A rule can stop recurrence | The same pattern will return next month |
Start with high-impact, high-confidence, reversible candidates. Defer confusing items only if they get an owner and a date; otherwise “defer” becomes another word for keeping waste permanently.
Data You Should Not Rush
Some cleanup candidates are supposed to look quiet. Do not rush these cases:
- Finance close, revenue recognition, audit extracts, and customer-specific reports.
- JavaScript UDFs that implement parsing, encryption, or compatibility behavior outside normal lineage.
- Shared utility functions used by analysts through notebooks rather than production dashboards.
For these cases, use a longer observation window, explicit owner approval, and a staged reduction. The point is not to avoid cleanup; it is to avoid making the first proof of dependency an outage.
Run the Data Review
Run warehouse UDF cleanup as a decision review, not an open-ended hygiene project.
- Pick the narrow scope and export the candidate list.
- Add owner, current purpose, last-use evidence, dependency checks, and risk if wrong.
- Remove obvious false positives, then ask owners to choose keep, reduce, archive, disable, remove, or investigate.
- Apply the least permanent useful change first.
- Watch the signals that would reveal a bad decision.
- Complete the final removal only after the review window closes.
- Save a UDF retirement record with function signature, callers, grant scope, replacement model, recreate statement, and rollback owner.
For broader cleanup planning, use the cleanup library to pair this guide with related notes.
Keep Retention Explicit
Prevention should change the creation path, not just the cleanup path. For warehouse UDF cleanup, the useful prevention fields are data owner, retention policy, recreate path, and review date. Make those fields part of normal creation and review.
- Register shared UDFs with owner, input contract, granted roles, and review date.
- Prefer versioned models or views for reusable business logic that dashboards depend on.
- Review UDF grants when semantic models, dbt packages, or warehouse roles change.
The recurring review should be short: sort by impact, pick the unclear items, assign owners, and close the loop on anything nobody claims. If the review keeps producing the same class of candidate, fix the creation path instead of celebrating repeated cleanup.
Example Decision Record
Use a compact record so the cleanup can be reviewed later without reconstructing the whole investigation.
| Field | Example entry for this cleanup |
|---|---|
| Candidate | Stale warehouse user-defined functions and JavaScript helpers in analytics warehouses, dbt projects, BI tools, scheduled queries, permission grants, and metric ownership records |
| Why it looked stale | Low recent activity, unclear owner, or no current consumer after the first review |
| Evidence checked | Invocation pattern, Trigger map, and owner confirmation |
| First reversible move | Disable triggers before deleting code so hidden consumers surface safely |
| Watch signal | The metric, alert, job, route, query, or owner complaint that would show the cleanup was wrong |
| Final action | Keep, reduce, archive, disable, or remove after a window long enough to include the function’s expected schedule, including monthly or event-driven work |
| Prevention rule | Require owner, trigger purpose, and expected frequency for each function |
This record is intentionally small. If the decision needs a long narrative, the candidate is probably not ready for removal yet. Keep investigating until the owner, evidence, reversible move, and prevention rule are clear.
FAQ
How often should teams do warehouse UDF cleanup?
Use a window long enough to include the function’s expected schedule, including monthly or event-driven work for the first decision, then set a recurring cadence based on change rate. Fast-moving non-production systems may need monthly review; slower systems can be quarterly if every unclear item has an owner and a review date.
What is the safest first action?
The safest first action is usually ownership repair plus evidence collection. After that, disable triggers before deleting code so hidden consumers surface safely. That creates a visible test before permanent deletion.
What should not be removed quickly?
Do not rush anything connected to monthly schedules, dead-letter replay handlers, and incident-only repair jobs. Also slow down when the cleanup affects recovery, compliance, customer-specific behavior, rare schedules, or security response.
How do you make the decision useful later?
Write the decision as a small operational record: candidate, owner, evidence, chosen action, watch signals, rollback path, final date, and prevention rule. That format helps future engineers, search engines, and AI assistants understand the cleanup without guessing.