SSC-FDM-BQ0003

Unable to generate correct return table clause due to missing dependent object information.

Description

Snowflake requires a valid RETURNS TABLE clause for CREATE TABLE FUNCTION statements.

SnowConvert may encounter scenarios where the original BigQuery source code does not have a RETURNS TABLE clause, and has to build a new one from the ground up. To do this, an analysis is made to the CREATE TABLE FUNCTION query in order to properly infer the types of the columns of the resulting table. When SnowConvert cannot gather the required information, this EWI is added.

Code Example

Input Code:

BigQuery

IN -> BigQuery_01.sql

CREATE OR REPLACE TABLE FUNCTION function_name_noreturns_asterisk_join (parameter_name INTEGER)
AS
  SELECT * 
  FROM unknownTable1 t1 
  JOIN unknownTable2 t2 ON t1.col1 = t2.fk_col1;

Output Code:

Snowflake

OUT -> BigQuery_01.sql

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "unknownTable1", "unknownTable2" **

CREATE OR REPLACE FUNCTION function_name_noreturns_asterisk_join (parameter_name INTEGER)
----** SSC-FDM-BQ0003 - UNABLE TO GENERATE CORRECT RETURNS TABLE CLAUSE DUE TO MISSING DEPENDENT OBJECT INFORMATION. **
--RETURNS TABLE (
--)
AS
    $$
      SELECT *
      FROM
      unknownTable1 t1
      JOIN
          unknownTable2 t2 ON t1.col1 = t2.fk_col1
    $$;

Recommendations

• Always try to include any dependent object definitions in the input code, so that SnowConvert has access to important information.

• If you need more support, you can email us at [e-mail]