Yes, Snowflake supports CREATE OR REPLACE FUNCTION for both SQL and JavaScript UDFs (user-defined functions), and it atomically replaces an existing function with the same name and signature. The key detail is that the replacement is immediate and does not require dropping the old function first, but it only works if the new function’s argument types exactly match the existing one; otherwise you get an error.
What Exactly Does CREATE OR REPLACE Do in Snowflake?
In Snowflake, CREATE OR REPLACE FUNCTION is a DDL statement that creates a new UDF or replaces an existing one with the same name and input argument data types. The replacement is atomic—meaning the old function is swapped out for the new one in a single transaction, and there is no window where the function is missing. This is different from a plain CREATE FUNCTION, which fails if a function with the same signature already exists.
Important nuance: the “replace” only applies when the argument list (the data types of the parameters) is identical. If you try to replace a function that takes an INT with one that takes a VARCHAR, Snowflake treats it as a new function and creates it alongside the old one, because the signature is different. This is consistent with how Snowflake handles stored procedures and views.
How to Use CREATE OR REPLACE FUNCTION
The basic syntax is straightforward. Here is a minimal SQL UDF example:
CREATE OR REPLACE FUNCTION add_one(x INT)
RETURNS INT
AS 'x + 1';
If add_one(INT) already exists, this replaces it. If not, it creates it. You can also use it with JavaScript UDFs:
CREATE OR REPLACE FUNCTION multiply(a FLOAT, b FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS 'return a * b;';
For JavaScript, you must specify LANGUAGE JAVASCRIPT. For SQL UDFs, the language is implicit. You can also include COMMENT, IMMUTABLE or STABLE keywords, and specify a RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT behavior.
When to Use CREATE OR REPLACE vs DROP + CREATE
Use CREATE OR REPLACE when you want to update a function’s logic without breaking dependencies. Because the replacement is atomic, any queries that reference the function during the replacement will either see the old version or the new version, but never a missing function. This is ideal for rolling out changes in production.
However, there are cases where you should use DROP FUNCTION first:
- If you need to change the return type of an existing function.
CREATE OR REPLACEwill fail with an error like “SQL compilation error: Invalid function return type” because the return type is part of the signature. - If you need to change the number of arguments or their data types. As noted, that creates a new overload, not a replacement.
- If you want to remove the function entirely—there is no
DROP OR REPLACE; you must useDROP FUNCTION.
For changing argument types, you must drop the old function first, then create the new one. Be careful: dropping a function may cause dependent views or other functions to fail until you recreate it.
Common Pitfalls and Misconceptions
One common mistake is assuming that CREATE OR REPLACE will replace a function even if the argument types differ. It will not—it will create a new overload. This can lead to confusion when you have two functions with the same name but different argument types, and you think you replaced the old one.
Another pitfall is using CREATE OR REPLACE on a function that is referenced by a view or another function. Snowflake does not automatically invalidate or recompile dependent objects. The view will continue to work, but it may now call the new function logic. If the return type changes (which is not allowed via replace), you would need to recreate the view.
Also note that CREATE OR REPLACE does not work for external functions (those that call a remote service via HTTPS). For external functions, you must use CREATE OR ALTER FUNCTION or drop and recreate. As of this writing, CREATE OR REPLACE is only for SQL and JavaScript UDFs.
Comparing CREATE OR REPLACE with CREATE OR ALTER
Snowflake also supports CREATE OR ALTER FUNCTION (and for external functions, CREATE OR ALTER EXTERNAL FUNCTION). The difference is subtle:
CREATE OR REPLACEdrops and recreates the function object. This means any grants (privileges) on the function are lost. You must re-grant them.CREATE OR ALTERmodifies the function in place, preserving existing grants. It also allows changing the return type and argument types? Actually,CREATE OR ALTERfor functions is more flexible: it can change the return type and even the argument list, because it alters the existing object rather than replacing it.
If you have other roles that have been granted USAGE on the function, CREATE OR REPLACE will revoke those grants. In contrast, CREATE OR ALTER keeps them. For production environments, CREATE OR ALTER is often safer. However, CREATE OR ALTER may not be available for all function types—check your Snowflake version. As of Snowflake 8.x, it is supported for SQL and JavaScript UDFs.
Step-by-Step: Replacing a Function Safely
Here is a practical workflow to replace a UDF without losing privileges or breaking dependencies:
- Check the current function definition using
SHOW FUNCTIONS LIKE 'my_function'or queryINFORMATION_SCHEMA.FUNCTIONS. - If you need to change the return type or argument types, use
DROP FUNCTION my_function(INT)first. Note that you must specify the argument types to drop. - Create the new function with
CREATE OR ALTER FUNCTION(if available) to preserve grants, orCREATE OR REPLACE FUNCTIONif you are okay re-granting. - Re-grant any necessary privileges:
GRANT USAGE ON FUNCTION my_function(INT) TO ROLE analyst; - Test the function with a simple
SELECT my_function(5);to confirm it works.
If you are using CREATE OR REPLACE and you have dependent views, you do not need to recreate them, but you should test them to ensure the new logic produces expected results.
Frequently Asked Questions
Does CREATE OR REPLACE FUNCTION work for stored procedures?
No, stored procedures use CREATE OR REPLACE PROCEDURE with similar semantics, but the syntax is different (e.g., RETURNS is optional, and you specify LANGUAGE). The same atomic replacement behavior applies.
Can I replace a function that is used in a view?
Yes, you can replace it, and the view will automatically use the new version. However, if the return type changes (which requires drop/create), the view may become invalid until you recreate it.
What happens to grants when I use CREATE OR REPLACE?
Grants on the function are dropped. You must re-grant privileges after the replacement. Use CREATE OR ALTER to avoid this.
Why do I get an error when I try to replace a function with different argument types?
Because the signature includes argument types. A function with different argument types is a different function, so CREATE OR REPLACE tries to create a new one. If a function with that exact signature already exists, you get an error; otherwise, it creates a new overload.
Is there a way to replace an external function?
Yes, use CREATE OR ALTER EXTERNAL FUNCTION. The CREATE OR REPLACE syntax is not supported for external functions because they are managed differently (they point to a remote API integration).
Can I use CREATE OR REPLACE FUNCTION in a transaction?
Yes, DDL statements in Snowflake are transactional? Actually, Snowflake DDL is not fully transactional in the traditional sense; each DDL statement is auto-committed. However, the replacement itself is atomic—there is no partial state.
Related
- Oracle String to Value: Converting Text in SQL
- Snowflake String Contains: SQL Methods and Examples
- Print String and Int on Arduino
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Round Up in Excel: Use ROUNDUP for Any Number or DecimalTo round a value in Excel away from zero, use =ROUNDUP(A2,0) when the number is in cell A2…
- Excel IF Formula Examples: Tests, Logic, and ErrorsExcel’s IF formula checks whether a condition is true or false and returns one result for each outcome.…
- Excel Formula Not Calculating? Common Causes and FixesIf a formula will not calculate in Excel, check that the cell is not formatted as Text, that…
- Sort Excel Rows by Column Value: A Practical GuideTo sort rows in Excel by a column value, click a cell in the data, then choose Data…
- Excel LET Function: How to Name and Reuse CalculationsUse Excel’s LET function to give a name to a value or calculation, then use that name elsewhere…
- COUNTIF Excel Number Range: Count Values by CriteriaTo count numbers in an Excel range that meet a condition, use COUNTIF: for example, with values in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.