Connect BigQuery through Snowflake
BigQuery cannot federate a generic Iceberg REST catalog, but it can federate Snowflake Horizon Catalog, and Snowflake can link a Gravitino catalog as a catalog-linked database (CLD). Chaining the two lets BigQuery query Iceberg tables managed by Datastrato Enterprise in place, with no data copied:
BigQuery → BigLake federated catalog → Snowflake Horizon → catalog-linked database → Gravitino Iceberg REST → object storage
Gravitino authorizes every catalog request Snowflake makes. BigQuery reads the data files itself with storage credentials that Snowflake vends from a Snowflake external volume.
Quick start
The example links the Gravitino catalog sales_lake into Snowflake as SALES_LAKE_EV, federates
that database into BigQuery as gravitino-via-snowflake, and queries q4.leads. It assumes the
Snowflake external volume gravitino_ev already exists, as in Steps, and that the Snowflake
service account has privileges in Gravitino, as in Connect Snowflake to Iceberg REST.
CREATE OR REPLACE CATALOG INTEGRATION gravitino_iceberg_ev
CATALOG_SOURCE = ICEBERG_REST
TABLE_FORMAT = ICEBERG
REST_CONFIG = (
CATALOG_URI = 'https://{iceberg_host}/iceberg'
CATALOG_NAME = 'sales_lake'
ACCESS_DELEGATION_MODE = EXTERNAL_VOLUME_CREDENTIALS
)
REST_AUTHENTICATION = (
TYPE = OAUTH
OAUTH_TOKEN_URI = '{token_endpoint_uri}'
OAUTH_CLIENT_ID = '{client_id}'
OAUTH_CLIENT_SECRET = '{client_secret}'
OAUTH_ALLOWED_SCOPES = ('{scope}')
)
ENABLED = TRUE;
CREATE DATABASE SALES_LAKE_EV
LINKED_CATALOG = ( CATALOG = 'gravitino_iceberg_ev' )
EXTERNAL_VOLUME = 'gravitino_ev';
ALTER DATABASE SALES_LAKE_EV SET ENABLE_HORIZON_IRC_UNMANAGED_CREDENTIAL_VENDING = TRUE;
gcloud alpha biglake iceberg catalogs create gravitino-via-snowflake \
--project={project_id} --primary-location={gcp_region} \
--catalog-type=federated --federated-catalog-type=snowflake \
--snowflake-account-identifier={snowflake_org}-{snowflake_account} \
--snowflake-warehouse=SALES_LAKE_EV \
--snowflake-role=BQ_FEDERATION_ROLE \
--refresh-interval=5m
CREATE ROLE IF NOT EXISTS BQ_FEDERATION_ROLE;
CREATE USER BQ_SERVICE_USER TYPE = SERVICE
WORKLOAD_IDENTITY = ( TYPE = GCP SUBJECT = '{biglake_service_account_id}' )
DEFAULT_ROLE = BQ_FEDERATION_ROLE;
GRANT ROLE BQ_FEDERATION_ROLE TO USER BQ_SERVICE_USER;
GRANT USAGE ON WAREHOUSE {warehouse} TO ROLE BQ_FEDERATION_ROLE;
GRANT USAGE ON DATABASE SALES_LAKE_EV TO ROLE BQ_FEDERATION_ROLE;
GRANT USAGE ON SCHEMA SALES_LAKE_EV."q4" TO ROLE BQ_FEDERATION_ROLE;
GRANT SELECT ON ALL ICEBERG TABLES IN SCHEMA SALES_LAKE_EV."q4" TO ROLE BQ_FEDERATION_ROLE;
GRANT USAGE ON EXTERNAL VOLUME gravitino_ev TO ROLE BQ_FEDERATION_ROLE;
SELECT * FROM `{project_id}.gravitino-via-snowflake.q4.leads` LIMIT 10;
Prerequisites
- Snowflake connected to the Iceberg REST service. The Snowflake service account needs privileges in Gravitino on the tables BigQuery will read, as described in Connect Snowflake to Iceberg REST.
- Table data in Amazon S3 or Google Cloud Storage, in the same cloud as the Snowflake account, and an IAM role in that account Snowflake can assume.
- A Google Cloud project with billing enabled and permission to enable APIs and create BigLake catalogs.
- A gcloud CLI recent enough to have the Snowflake flags. Check with
gcloud alpha biglake iceberg catalogs create --help | grep snowflake; older releases reject--federated-catalog-type=snowflake. Releases installed through a package manager are upgraded with that package manager, notgcloud components update.
Steps
-
Create a Snowflake external volume over the table data. Horizon passes storage credentials to outside engines only from an external volume, never credentials Gravitino vended. Point
STORAGE_BASE_URLat the prefix that holds the catalog's tables and setSTORAGE_AWS_ROLE_ARNto a role with read and write access to that prefix.Create and check the external volumeCREATE OR REPLACE EXTERNAL VOLUME gravitino_ev
STORAGE_LOCATIONS = ((
NAME = '{location_name}'
STORAGE_PROVIDER = 'S3'
STORAGE_BASE_URL = 's3://{bucket}/{prefix}/'
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::{aws_account_id}:role/{role_name}'
))
ALLOW_WRITES = TRUE;
DESC EXTERNAL VOLUME gravitino_ev;From the
STORAGE_LOCATION_1row of theDESCoutput, takeSTORAGE_AWS_IAM_USER_ARNandSTORAGE_AWS_EXTERNAL_ID, and set them as the trusted principal andsts:ExternalIdcondition in the IAM role's trust policy. Then confirm that Snowflake can assume the role:Verify the external volumeSELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('gravitino_ev'); -
Link the Gravitino catalog in external-volume mode. Run the first block of the Quick start. The integration uses
ACCESS_DELEGATION_MODE = EXTERNAL_VOLUME_CREDENTIALS, the database names the external volume, andENABLE_HORIZON_IRC_UNMANAGED_CREDENTIAL_VENDINGlets Horizon vend its credentials. A catalog-linked database inVENDED_CREDENTIALSmode works inside Snowflake, but outside engines cannot read its data. Check the link:Check the linked databaseSHOW ICEBERG TABLES IN DATABASE SALES_LAKE_EV; -
Enable the APIs and create the federated catalog. Enable the APIs, then run the gcloud block of the Quick start.
Enable the APIsgcloud services enable biglake.googleapis.com bigquery.googleapis.com --project={project_id}Despite its name,
--snowflake-warehousetakes the Snowflake database to expose, not a compute warehouse.--snowflake-account-identifiertakes{snowflake_org}-{snowflake_account}, which Snowflake returns fromSELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME();. Then read the catalog's service account ID:Read the BigLake service account IDgcloud alpha biglake iceberg catalogs describe gravitino-via-snowflake --project={project_id}The output's
biglake-service-account-idis the identity BigQuery signs in to Snowflake with. -
Create the Snowflake service user and grants. Run the third block of the Quick start with
SUBJECTset to that ID. BigQuery signs in with workload identity federation, so no password or token is stored anywhere. GrantUSAGEandSELECTon each schema BigQuery reads.ON ALL ICEBERG TABLEScovers the tables that exist when it runs, so run it again when new tables appear. -
Check the namespaces and query. BigQuery picks up the Snowflake schemas on its first refresh, within the catalog's refresh interval of creation.
List the federated namespaces and query from the command linegcloud alpha biglake iceberg namespaces list --catalog=gravitino-via-snowflake --project={project_id}
bq query --project_id={project_id} --location={gcp_region} --use_legacy_sql=false \
'SELECT * FROM `{project_id}.gravitino-via-snowflake.q4.leads` LIMIT 10'Pass
--locationwith the catalog's region; without it,bqlooks inUSand reports that the dataset was not found.
Access control
No layer of the chain passes individual identities to the next. Every BigQuery user reaches Snowflake as the one BigLake service account, and Snowflake reaches Gravitino as the one Snowflake service account, so each layer controls a different thing:
| Layer | Grant to | Controls |
|---|---|---|
| Gravitino | The Snowflake service account | Everything the chain can reach in the catalog, for all users together |
| Snowflake | BQ_FEDERATION_ROLE | Which linked schemas and tables BigQuery can reach, for all users together |
| BigQuery | Individual Google users and groups | Which BigQuery users can query which namespaces and tables |
Grant individual access in Google Cloud IAM. A BigQuery user needs roles/bigquery.jobUser on the
project to run queries, plus roles/biglake.viewer on the federated catalog, a namespace or a table:
gcloud alpha biglake iceberg namespaces get-iam-policy {namespace} \
--catalog=gravitino-via-snowflake --project={project_id} --format=json > policy.json
jq '.bindings = ((.bindings // []) + [{"role":"roles/biglake.viewer","members":["user:{user_email}"]}])' \
policy.json > policy-new.json
gcloud alpha biglake iceberg namespaces set-iam-policy {namespace} policy-new.json \
--catalog=gravitino-via-snowflake --project={project_id}
A new grant can take a couple of minutes to take effect; until then the query fails with
Access Denied: ... User does not have permission to query table. Granting roles/biglake.viewer on
the project gives the user every federated catalog in it. Gravitino's privileges for individual people
do not apply to BigQuery queries.
Freshness
A change committed to Gravitino reaches BigQuery after two polls: Snowflake picks up the new snapshot
on the integration's REFRESH_INTERVAL_SECONDS (30 seconds by default), and BigQuery picks it up from
Snowflake on the federated catalog's --refresh-interval. With a five-minute BigQuery interval, a
row inserted through Gravitino appeared in BigQuery 3 minutes 23 seconds later in testing; the delay
depends on where the change lands in BigQuery's refresh cycle.
Limitations
- The BigQuery side of the chain is in preview and configured with
gcloud alpha, so its commands and flags can change. - Read-only. Google treats federated catalogs as read-only views of the remote catalog, so BigQuery cannot write the tables; write through Snowflake or another engine instead.
- Iceberg views are not passed through. Snowflake does not sync Iceberg views from the catalog, so BigQuery sees tables only.
- Table data must be in Amazon S3 or Google Cloud Storage, in the same cloud as the Snowflake account. BigQuery reads the data files directly and does not use credentials vended by Gravitino.