For every ordinary relational table in a database holds that, the data is stored in the underlying database. Snowflake recently added the possibility to create an external table, but what does this mean, what is the use of this and what does this enable?
To start with a simple definition of what this means; The data is not stored in Snowflake, but in files in an external staging environment. This external staging environment is a simple storage in the form of AWS S3, Google Cloud Storage or Azure Storage. For external tables, the metadata about the data files is stored, such as the file name, version ID and associated properties. On the basis of this metadata you can query data that is in the files in the external staging environment. For the end user it looks like the stored data is in the Snowflake Data Warehouse, but the data is actually retrieved from a separate storage account.
Because Snowflake now offers the possibility to use external tables in Snowflake, you can now make it available as a table for users without having to physically load a dataset. External tables are read-only, therefore data manipulation operations cannot be performed on them, but external tables can be used for query and join operations. This allows you to create views that consult the external tables.
All types of data supported by the COPY_INTO command are available to create an external table, so this offers you the option of making JSON data available in an external stage via Snowflake with a SQL query.
If you know the schema of the data in the storage well, you can define Virtual Columns, this helps to validate data types and the schema because the data must match exactly with the specified Virtual Column.
The AUTO_REFRESH function allows you to have the metadata of the external table automatically refresh when new data or an update is added to the stage environment.
All in all, the ability to create external tables in Snowflake is a valuable option that allows you to better integrate Snowflake into your existing infrastructure, thereby ensuring that you can make even more different data sources centrally available through the SQL data warehouse developed for the cloud. Do you want to know how you can integrate Snowflake into your current architecture? Do not hesitate to contact us.
