# Snowflake

Supported since 5.0.0.

Through the Snowflake (opens new window) connector, Ontop is able to construct VKGs on cloud-based Snowflake databases.

# Limitations & Exceptions

WARNING

Integrity constraints are often missing in Snowflake. Make sure to specify the missing ones in order to avoid very inefficient queries. We recommend using lenses for this purpose.

  • Due to an issue in the JDBC, quotation marks cannot always be used when defining an alias. Because of this, it is recommended to choose column names without special characters where possible.
  • Accessing struct fields with the "colon operator" is not supported (see below).

# Database Connection

The following shows the content of a sample .properties file that can be used to connect Ontop to Snowflake:

jdbc.url = jdbc:snowflake://dummyuser.snowflakecomputing.com/?warehouse=dummywarehouse&db=dummydb&schema=dummyschema
jdbc.user = user
jdbc.password = password
jdbc.driver = net.snowflake.client.jdbc.SnowflakeDriver

# Nested Type Support

Ontop implements explicit compatibility with the ARRAY type, which can be used with the flatten lens. However, it is not able to automatically infer the type of the resulting column.

WARNING

Due to an issue mentioned above, using special characters inside the output and position fields of the flatten lens for Snowflake will fail. Furthermore, these fields will always be case-insensitive.

# Struct Access

Individual object fields can be accessed by SQL expressions in the dialect's default way:

SELECT my_object['my_field'] FROM ...

WARNING

Accessing object fields using the "colon operator" is not supported. Please use the [] operator instead.

Last Updated: 2/15/2024, 10:16:53 AM