SSC-EWI-RS0003

View with no schema binding can not be removed due to missing references.

Severity

Medium

Description

Redshift documentation for CREATE VIEW includes an optional clause that specifies that the particular view is not bound to the database objects such as tables or functions, nor to those objects that it is referencing. The documentation also clarifies that in such cases that this clause is used, the referenced objects must be qualified with a schema name. This clause allows to create a view and reference objects that might not exist yet. Their existence will be verified once the view is queried, but not at its definition.

However, there is no equivalent command nor obvious workaround to implement this functionality in Snowflake, furthermore, the Snowflake documentation suggests that the views are linked to a specific schema and so are the referenced objects in the view.

If the references linked to the View are present in the input code, the statement will be removed without issue. However, if the necessary references are missing, a warning message will be added to inform the user that the statement cannot be removed due to the missing references.

SnowConvert performs analysis solely on the input code and does not account for objects already deployed in Snowflake. Therefore the output may have some issues pointing to missing references, if the references are already present in the Snowflake database, the user can safely remove the statement without any issues.

Code Examples

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE VIEW myView AS SELECT col1 FROM public.missingTable
WITH NO SCHEMA BINDING;

Output Code:

Snowflake

OUT -> Redshift_01.sql

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "public.missingTable" **
CREATE VIEW myView
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
AS SELECT col1 FROM
public.missingTable
!!!RESOLVE EWI!!! /*** SSC-EWI-RS0003 - WITH NO SCHEMA BINDING STATEMENT CAN NOT BE REMOVED DUE TO MISSING REFERENCES. ***/!!!
WITH NO SCHEMA BINDING;

Recommendations

• To resolve this issue, it is suggested to add the missing references to the input code, if the object is already deployed in the Snowflake database, the statement can be remove without issue.

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