Assuming a Data Engineer has all appropriate privileges and context, which statements would be used to assess whether the User-Defined Function (UDF), MYDATABASE.SALES.REVENUE_BY_REGION, exists and…

Question

Options

• ASHOW USER FUNCTIONS LIKE 'REVENUE_BY_REGION' IN SCHEMA SALES;
• BSELECT IS_SECURE FROM SNOWFLAKE.INFORMATION_SCHEMA.FUNCTIONS WHERE
• CSELECT IS_SECURE FROM INFORMATION_SCHEMA.FUNCTIONS WHERE
• DSHOW EXTERNAL FUNCTIONS LIKE 'REVENUE_BY_REGION' IN SCHEMA SALES;
• ESHOW SECURE FUNCTIONS LIKE 'REVENUE_BY_REGION' IN SCHEMA SALES;

• A
  69% (43)
• B
  5% (3)
• D
  19% (12)
• E
  6% (4)

Explanation

A (SHOW USER FUNCTIONS) is the correct DDL command for listing UDFs in Snowflake - its output includes an is_secure column, so one command confirms both existence and security status. C (SELECT IS_SECURE FROM INFORMATION_SCHEMA.FUNCTIONS WHERE ...) is also correct because INFORMATION_SCHEMA.FUNCTIONS is the proper view for querying UDF metadata within the current database context.

B is wrong because SNOWFLAKE.INFORMATION_SCHEMA is the account-level schema for Snowflake-internal system objects, not for user-defined objects in a specific database. The correct path for a UDF in MYDATABASE is MYDATABASE.INFORMATION_SCHEMA.FUNCTIONS (or just INFORMATION_SCHEMA.FUNCTIONS when the database context is already set).

D fails because SHOW EXTERNAL FUNCTIONS targets functions that call remote external APIs - a completely different object type from a standard UDF. E uses invalid Snowflake syntax; SHOW SECURE FUNCTIONS is not a valid command variant.

Memory tip: Associate two patterns - "SHOW USER FUNCTIONS for DDL inspection" and "INFORMATION_SCHEMA.FUNCTIONS for SQL queries" - and remember that the SNOWFLAKE. prefix always points to system-level metadata, never to objects you created yourself.

No community discussion yet for this question.