Snowflake External Tables: Query Your Data Lake Without the Import

Snowflake has quickly become a powerhouse in cloud data warehousing. One of its key features, external tables, provides a seamless way to access data residing outside of Snowflake’s managed storage. Imagine being able to query your data sitting in cloud storage buckets as easily as if it were loaded directly into Snowflake. Let’s dive into what makes external tables so unique.

What are Snowflake External Tables?

• Definition: External tables in Snowflake are virtual representations of data files stored in an external cloud storage service like Amazon S3, Google Cloud Storage, or Microsoft Azure. You define its schema and file format, but the data stays put.
• Metadata: Instead of the data, external tables store metadata—information about the data files such as their location, format (CSV, Parquet, JSON, etc.), and structure.
• Read-Only: It’s important to note that external tables are primarily for querying your external data. You can’t directly update or delete data in them with traditional data manipulation commands.

Why Use External Tables?

1. Immediate Access to Large Datasets: If you’ve got massive amounts of data in your data lake, loading it all into Snowflake might be time-consuming and costly. External tables let you start querying that data instantly without any data movement.
2. Flexibility with Updates: Is new data constantly arriving in your external storage? With an external table, the latest information is always accessible within Snowflake as the files update—there is no need for ETL processes to bring in new data.
3. Integration with Your Data Lake: External tables are an excellent fit for organizations with an existing data lake architecture. You can continue storing files in your preferred location while tapping into Snowflake’s robust analytical capabilities.

Setting Up a Snowflake External Table

Here’s a quick outline of the steps involved:

1. Create an External Stage: This is a named Snowflake object that references the location of your data in your cloud storage (e.g., an S3 bucket).
2. Create the External Table: Define the schema, file format, location (the external stage you created), and other properties.
3. Query away! Now, you can use SQL on your external table as if the data were part of your Snowflake database.

Example

SQL

CREATE EXTERNAL TABLE my_external_data (

    id INT,

    name STRING,

    timestamp TIMESTAMP

)

WITH LOCATION = @my_s3_stage

FILE_FORMAT = (TYPE = CSV); 

Use code with caution.

content_copy

Remember: Snowflake will often need permissions to access your cloud storage bucket. This might involve setting up roles or policies.

Key Considerations

• Performance: Since data isn’t stored locally in Snowflake, consider factors like network speed and file formats optimized for remote data access (like Parquet).
• Security: Ensure encryption and access controls are in place for Snowflake and your cloud storage.
• Cost: Snowflake charges for queries on external tables based on data scanned, so be mindful of query efficiency.

That’s a wrap! Snowflake external tables are a powerful tool for bridging the gap between your data lake and your cloud data warehouse. This allows for a faster, more flexible, and cost-effective way of analyzing various data sources.