Skip to main content

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:

Request path
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.

Snowflake: link the catalog for outside engines
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;
Google Cloud: create the federated catalog
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
Snowflake: let BigQuery sign in
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;
BigQuery: query the table
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, not gcloud components update.

Steps​

  1. 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_URL at the prefix that holds the catalog's tables and set STORAGE_AWS_ROLE_ARN to a role with read and write access to that prefix.

    Create and check the external volume
    CREATE 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_1 row of the DESC output, take STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID, and set them as the trusted principal and sts:ExternalId condition in the IAM role's trust policy. Then confirm that Snowflake can assume the role:

    Verify the external volume
    SELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('gravitino_ev');
  2. 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, and ENABLE_HORIZON_IRC_UNMANAGED_CREDENTIAL_VENDING lets Horizon vend its credentials. A catalog-linked database in VENDED_CREDENTIALS mode works inside Snowflake, but outside engines cannot read its data. Check the link:

    Check the linked database
    SHOW ICEBERG TABLES IN DATABASE SALES_LAKE_EV;
  3. Enable the APIs and create the federated catalog. Enable the APIs, then run the gcloud block of the Quick start.

    Enable the APIs
    gcloud services enable biglake.googleapis.com bigquery.googleapis.com --project={project_id}

    Despite its name, --snowflake-warehouse takes the Snowflake database to expose, not a compute warehouse. --snowflake-account-identifier takes {snowflake_org}-{snowflake_account}, which Snowflake returns from SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME();. Then read the catalog's service account ID:

    Read the BigLake service account ID
    gcloud alpha biglake iceberg catalogs describe gravitino-via-snowflake --project={project_id}

    The output's biglake-service-account-id is the identity BigQuery signs in to Snowflake with.

  4. Create the Snowflake service user and grants. Run the third block of the Quick start with SUBJECT set to that ID. BigQuery signs in with workload identity federation, so no password or token is stored anywhere. Grant USAGE and SELECT on each schema BigQuery reads. ON ALL ICEBERG TABLES covers the tables that exist when it runs, so run it again when new tables appear.

  5. 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 line
    gcloud 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 --location with the catalog's region; without it, bq looks in US and 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:

LayerGrant toControls
GravitinoThe Snowflake service accountEverything the chain can reach in the catalog, for all users together
SnowflakeBQ_FEDERATION_ROLEWhich linked schemas and tables BigQuery can reach, for all users together
BigQueryIndividual Google users and groupsWhich 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:

Let one user query one namespace
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.