Use Snowflake with R2 to extend your global data lake

Joint customers can now use Snowflake with Cloudflare R2 to extend your global data lake without having to worry about egress fee

PA

Phillip, AbhiCloudflare

·May 16, 2023

Use Snowflake with R2 to extend your global data lake

R2 and Snowflake: Keeping data lakes open and affordable

Cloudflare R2 is built for data lake workloads: it scales without upper limits, is designed for eleven 9's of annual durability, and — critically — charges no egress fees. That last property removes the usual cost penalty for moving data across clouds or regions, which also means no vendor lock-in when you want to analyze that data elsewhere.

Snowflake's Data Cloud is one such destination. The two platforms now work together directly: you can query data sitting in an R2 bucket from Snowflake, or load that data into Snowflake tables for heavier analytic work. For teams that have already consolidated data in R2, this removes the transfer-cost barrier that typically comes with loading data into Snowflake from a separate cloud or region.

Making the connection requires three pieces: an R2 bucket with credentials, a Snowflake external stage pointing at that bucket, and the SQL commands to move or query the data. R2's S3-compatible API means Snowflake treats it like any other object store.

Creating R2 credentials

Start in the Cloudflare dashboard with an existing R2 bucket, then generate an API token for Snowflake to use:

Select R2 in the dashboard.
Choose Manage R2 API Tokens on the right side of the page.
Select Create API token.

Optionally rename the token by selecting the pencil icon or the token name text.

Under Permissions, select Edit.
Select Create API Token.

Snowflake needs the generated Access Key ID and Secret Access Key to authenticate against R2.

Setting up an external stage

Snowflake uses stages to reference file locations in object storage. Creating an external stage for R2 requires the bucket name, the R2 credentials, and your Cloudflare account ID, which is visible in the Cloudflare dashboard.

CREATE STAGE my_r2_stage
  URL = 's3compat://my_bucket/files/'
  ENDPOINT = 'cloudflare_account_id.r2.cloudflarestorage.com'
  CREDENTIALS = (AWS_KEY_ID = '1a2b3c...' AWS_SECRET_KEY = '4x5y6z...')

Note that you may need to ask your Snowflake account team to enable S3-compatible endpoints before external stages can point at R2. See the Snowflake documentation for details.

Loading and unloading data

With an external stage in place, load data from R2 into Snowflake using the COPY INTO command. Swapping the table and external stage parameters reverses the operation, unloading data from Snowflake back into R2.

COPY INTO t1
  FROM @my_r2_stage/load/;

Querying R2 data in place

To query data that remains in R2 rather than loading it into Snowflake tables, first create an external table in Snowflake. Once the external table exists, ordinary Snowflake queries will work directly against the R2 bucket.