I am currently designing tables in Google Big Query and I will need to move the designs over to Snowflake in AWS within the next year. GBQ has a STRUCT datatype that allows for nested data within a column (Specifying nested and repeated columns). Does Snowflake have a similar data type/functionality? According to this article from Snowflake, the platform supports SQL queries that access semi-structured data. The sample data from both articles look the same and the verbiage is similar, however I am not sure if the two are the same types. Would I be able to translate a design that utilizes GBQ structs over to Snowflake without fully refactoring it?
1 Answers
3
votes
I'm not a BQ expert, but I believe the key difference here is that BQ requires the definition of the STRUCT schema upfront. The equivalent type in Snowflake is the VARIANT type, which will store semi-structured data but doesn't require the Schema upfront. As such, you shouldn't need to re-factor anything as long as you can export the Struct column to JSON or Parquet or similar.
