Implementing Cross-Warehouse Federation and Data Sharing with Snowflake Secure Data Sharing, BigQuery Analytics Hub, and External Tables

Introduction

Your company just acquired a competitor. Their data lives in BigQuery, yours lives in Snowflake, and the board wants a unified analytics layer by Q1. Meanwhile, your partner ecosystem has grown to thirty vendors who each need a slice of your data — but not all of it, not with write access, and absolutely not through a pipeline that runs stale for six hours before anyone notices. On top of that, three external research institutions want real-time access to aggregated metrics, and your legal team is watching every move.

This is not a hypothetical. It's the operational reality for data teams at mid-to-large organizations in 2024, and the challenge is simultaneously architectural, political, and deeply technical. The old answer — copy data into a central warehouse, manage the ETL, eat the latency and the cost — no longer holds up when the data volumes are in the petabytes and the access patterns are unpredictable. The modern answer is federation and secure sharing: let the data live where it lives, expose only what should be exposed, and build query engines that traverse warehouse boundaries without moving bytes unnecessarily.

By the end of this lesson, you'll understand the internals of how Snowflake Secure Data Sharing and BigQuery Analytics Hub actually work at the storage layer, not just how to click through their UIs. You'll be able to design a multi-warehouse federation architecture, implement cross-cloud External Tables, avoid the performance and security traps that catch even experienced engineers, and reason clearly about when federation is the right choice versus when you should just move the data.

What you'll learn:

• How Snowflake's Data Clean Room and Secure Data Sharing work at the metadata and storage layer, and how to implement provider/consumer relationships with row- and column-level security
• How BigQuery Analytics Hub enables marketplace-style data publishing and how to query across projects and datasets with Authorized Views and VPC Service Controls
• How External Tables bridge warehouse boundaries using object storage as a common substrate, including partition pruning, schema evolution, and format choices that affect query performance by orders of magnitude
• How to architect a multi-cloud federation layer that minimizes egress, enforces governance, and maintains a coherent semantic layer across Snowflake and BigQuery
• The anti-patterns that turn elegant federation architectures into slow, expensive, ungovernable messes — and how to avoid them

---

Prerequisites

This lesson assumes you're comfortable with:

• SQL at an advanced level (window functions, CTEs, lateral joins)
• Snowflake fundamentals: virtual warehouses, stages, databases, schemas
• BigQuery fundamentals: datasets, projects, slots, partitioning
• Basic cloud storage concepts: object storage (GCS/S3/ADLS), IAM, and bucket policies
• Python or shell scripting for automation tasks
• A working understanding of columnar storage formats (Parquet, ORC, Avro)

If you need to shore up BigQuery or Snowflake fundamentals, work through the earlier lessons in this learning path first. Federation architecture built on shaky warehouse foundations will fail in production in ways that are extraordinarily difficult to debug.

---

Understanding the Fundamental Models: Copy vs. Query vs. Share

Before touching a line of SQL, you need to internalize the three distinct models for cross-system data access, because conflating them is the root cause of most failed federation projects.

Copy-based integration is what most teams start with. An ETL pipeline extracts data from System A, transforms it, and loads it into System B. It's the simplest mental model, the most operationally mature, and the most expensive in terms of latency, storage cost, and pipeline maintenance. When your business requires data to be "as of last night" and the datasets are stable and bounded, copy is often the right call. Don't dismiss it just because it feels old-fashioned.

Query federation means the query engine reaches across a network boundary to execute part of a query against a remote system, retrieves the results, and assembles them locally. BigQuery's federated queries to external data sources work this way. The data doesn't move to your warehouse until query time, which means you're always working with current data — but you pay a network and latency cost on every query, and the remote system must be reachable and performant. This is excellent for low-frequency, high-latency-tolerance analytical queries against systems you don't own.

Zero-copy sharing is the model that Snowflake pioneered and that's become the gold standard for same-platform sharing. No data is copied. Instead, the provider creates a metadata construct — in Snowflake's case, a Share object — that points the consumer's query engine directly at the provider's underlying micropartitions. The consumer sees the data as if it were their own table, but they're reading the provider's storage. The consumer pays for compute (their virtual warehouse), the provider pays for storage. This is transformative for same-cloud sharing but gets complicated when you cross cloud boundaries.

Understanding which model is in play at any given moment in your architecture is non-negotiable. A query that looks like a simple JOIN across two tables might be transparently federating to another cloud region, and if you don't know that, you'll never understand why it costs $400 and takes 40 minutes.

---

Snowflake Secure Data Sharing: Internals and Implementation

How Sharing Works at the Storage Layer

Snowflake's storage architecture separates compute from storage, and that separation is what makes zero-copy sharing possible. Your data in Snowflake lives as micropartitions — immutable, compressed, columnar files — in object storage (S3, GCS, or Azure Blob, depending on your cloud). Snowflake's metadata services track which micropartitions constitute a table's current state.

When you create a Share and add a table to it, Snowflake doesn't copy the micropartitions. It creates a metadata pointer that says: "the consumer's query engine is permitted to read these specific micropartitions, filtered by these access policies." The consumer mounts the Share as a database in their account, and their virtual warehouses can read those micropartitions directly — but only through Snowflake's access control layer, which enforces row-level security policies, column masking policies, and object-level grants.

This is why Secure Data Sharing has two hard constraints that often surprise people: both provider and consumer must be in the same Snowflake cloud region (same CSP and same region), and data consumers cannot write to shared objects. The region constraint exists because the consumer's compute nodes need low-latency access to the provider's storage buckets. You cannot share across AWS us-east-1 to GCP us-central1 without going through a replication step first.

Setting Up a Provider Account

Let's build a realistic example. Assume you're a financial data provider, and you want to share a curated view of daily equity prices with three consumer accounts: a hedge fund, a retail brokerage, and an academic research institution. Each gets a different cut of the data.

Start by setting up the data assets you'll share. Good sharing practice means you almost never share base tables directly — you share views or dynamic tables that enforce your data contract:

-- In the provider account
CREATE DATABASE data_marketplace;
CREATE SCHEMA data_marketplace.equities;

-- This is your internal, full-fidelity table
CREATE TABLE data_marketplace.equities.daily_prices_internal (
    trade_date        DATE        NOT NULL,
    ticker            VARCHAR(10) NOT NULL,
    open_price        DECIMAL(12,4),
    high_price        DECIMAL(12,4),
    low_price         DECIMAL(12,4),
    close_price       DECIMAL(12,4),
    adjusted_close    DECIMAL(12,4),
    volume            BIGINT,
    market_cap        DECIMAL(18,2),
    pe_ratio          DECIMAL(10,4),
    -- Sensitive: internal analyst ratings, not for sharing
    internal_rating   VARCHAR(5),
    analyst_notes     TEXT
);

-- Create a row access policy that enforces entitlements
CREATE OR REPLACE ROW ACCESS POLICY data_marketplace.equities.ticker_entitlement
    AS (ticker VARCHAR) RETURNS BOOLEAN ->
    EXISTS (
        SELECT 1
        FROM data_marketplace.equities.consumer_entitlements e
        WHERE e.snowflake_account = CURRENT_ACCOUNT()
          AND e.ticker = ticker
          AND e.entitlement_expires_at > CURRENT_TIMESTAMP()
    );

Now create the views that consumers will actually see. Notice we're doing column exclusion (dropping sensitive fields) and attaching the row access policy:

-- Public market data view: no sensitive columns
CREATE OR REPLACE SECURE VIEW data_marketplace.equities.daily_prices_public AS
    SELECT
        trade_date,
        ticker,
        open_price,
        high_price,
        low_price,
        close_price,
        volume
    FROM data_marketplace.equities.daily_prices_internal;

-- Attach the row access policy to enforce per-account ticker entitlements
ALTER VIEW data_marketplace.equities.daily_prices_public
    ADD ROW ACCESS POLICY data_marketplace.equities.ticker_entitlement
    ON (ticker);

-- Premium view: includes adjusted close and market cap, no analyst data
CREATE OR REPLACE SECURE VIEW data_marketplace.equities.daily_prices_premium AS
    SELECT
        trade_date,
        ticker,
        open_price,
        high_price,
        low_price,
        close_price,
        adjusted_close,
        volume,
        market_cap,
        pe_ratio
    FROM data_marketplace.equities.daily_prices_internal;

ALTER VIEW data_marketplace.equities.daily_prices_premium
    ADD ROW ACCESS POLICY data_marketplace.equities.ticker_entitlement
    ON (ticker);

Why SECURE VIEW matters: A regular view in Snowflake allows consumers to infer the underlying query structure through SHOW commands and error messages. A SECURE VIEW hides the view definition from non-owners. If your view is building on proprietary logic or hiding sensitive columns, always use SECURE VIEW when it's going into a share.

Building and Managing Shares

-- Create the share objects
CREATE SHARE sh_public_equities
    COMMENT = 'Public equity market data - OHLCV only';

CREATE SHARE sh_premium_equities
    COMMENT = 'Premium equity data - includes fundamentals';

-- Grant usage on the database and schema to the share
GRANT USAGE ON DATABASE data_marketplace TO SHARE sh_public_equities;
GRANT USAGE ON SCHEMA data_marketplace.equities TO SHARE sh_public_equities;
GRANT SELECT ON VIEW data_marketplace.equities.daily_prices_public
    TO SHARE sh_public_equities;

-- Also grant access to the entitlements table so the row access policy resolves
GRANT SELECT ON TABLE data_marketplace.equities.consumer_entitlements
    TO SHARE sh_public_equities;

-- Add the consumer accounts to the appropriate shares
-- Academic institution gets public data
ALTER SHARE sh_public_equities ADD ACCOUNTS = RESEARCH_ORG_ACCOUNT;

-- Hedge fund and brokerage get premium data
ALTER SHARE sh_premium_equities ADD ACCOUNTS = HEDGE_FUND_ACCOUNT, BROKERAGE_ACCOUNT;

-- Verify
SHOW GRANTS TO SHARE sh_premium_equities;

The consumer_entitlements table is crucial and often overlooked. When Snowflake evaluates the row access policy, it executes in the context of the provider account — it can reference the provider's tables, but the CURRENT_ACCOUNT() function returns the consumer's account identifier. This lets you build dynamic, per-consumer entitlements without creating separate shares for every consumer.

Consumer-Side Setup and Querying

In the consumer account, mounting a share is straightforward:

-- In the consumer (hedge fund) account
CREATE DATABASE equities_feed
    FROM SHARE HEDGE_FUND_ACCOUNT.sh_premium_equities;

-- Query immediately — no copy, no ETL, data is live
SELECT
    trade_date,
    ticker,
    close_price,
    volume,
    pe_ratio
FROM equities_feed.equities.daily_prices_premium
WHERE trade_date >= DATEADD('day', -30, CURRENT_DATE())
  AND ticker IN ('AAPL', 'MSFT', 'GOOGL')
ORDER BY trade_date DESC, ticker;

The consumer's virtual warehouse pays for the compute to run this query. The provider's storage serves the micropartitions. If the provider adds new data to daily_prices_internal, it's immediately visible in the consumer's query — there's no synchronization lag.

Cross-Region and Cross-Cloud Sharing with Replication

When you need to share across regions or clouds (and you will), the architecture changes. Snowflake requires you to replicate the data to the target region first, then share from there.

-- In the provider account (AWS us-east-1)
-- Create a replication group that includes our sharing database
CREATE REPLICATION GROUP rg_market_data
    OBJECT_TYPES = DATABASES
    ALLOWED_DATABASES = data_marketplace
    ALLOWED_ACCOUNTS = GCP_CONSUMER_ACCOUNT
    REPLICATION_SCHEDULE = '15 MINUTE';

-- Trigger an initial replication
ALTER REPLICATION GROUP rg_market_data REFRESH;

-- Monitor replication status
SELECT *
FROM TABLE(INFORMATION_SCHEMA.REPLICATION_GROUP_REFRESH_HISTORY(
    REPLICATION_GROUP_NAME => 'rg_market_data'
))
ORDER BY phase_started_time DESC
LIMIT 20;

Now in the secondary (GCP) account:

-- In the consumer account (GCP us-central1)
-- Create the secondary replication group (consumer side)
CREATE REPLICATION GROUP rg_market_data
    AS REPLICA OF AWS_PROVIDER_ACCOUNT.rg_market_data;

-- The replicated database is now available locally in GCP
-- You can now create a share from this secondary
CREATE SHARE sh_gcp_equities;
GRANT USAGE ON DATABASE data_marketplace TO SHARE sh_gcp_equities;
-- ... continue as before

---

BigQuery Analytics Hub: Data Exchange at Scale

Architecture: What Analytics Hub Actually Is

BigQuery Analytics Hub is Google's marketplace infrastructure for data sharing built on top of BigQuery's Authorized Views and Dataset-level access controls. It introduces two key abstractions: Data Exchanges (directories of available data products) and Listings (individual shareable datasets).

Unlike Snowflake's sharing model, Analytics Hub is explicitly designed around the concept of a data marketplace — providers publish listings, consumers subscribe to listings and get a linked dataset in their own project. Under the hood, this is still a zero-copy mechanism: the consumer's linked dataset is a metadata pointer to the provider's underlying BigQuery storage. Queries against linked datasets are executed against the provider's data with the consumer's billing project paying for query compute.

The critical nuance is that Analytics Hub supports two subscriber types: Private (you control who gets access, like Snowflake sharing) and Public (anyone can subscribe via the marketplace, like a commercial data product). This makes it genuinely useful for both internal governance scenarios and commercial data monetization.

Setting Up an Analytics Hub Exchange

Let's continue the financial data example but now from the perspective of the BigQuery side of a hybrid architecture. Assume you have retail transaction data in BigQuery that needs to be shared with risk analytics teams in partner organizations.

First, the dataset and table structure:

-- In BigQuery (provider project: my-fintech-prod)
-- Create the sharing dataset
CREATE SCHEMA `my-fintech-prod.retail_transactions_shared`
OPTIONS (
    location = 'US',
    description = 'Shared retail transaction analytics for partner organizations'
);

-- Base table with full data
CREATE TABLE `my-fintech-prod.retail_transactions_internal.transactions` (
    transaction_id      STRING NOT NULL,
    transaction_date    DATE NOT NULL,
    merchant_id         STRING NOT NULL,
    merchant_category   STRING,
    amount_usd          NUMERIC,
    card_bin            STRING,  -- Sensitive: first 6 digits of card
    customer_segment    STRING,
    region              STRING,
    is_fraud            BOOL
)
PARTITION BY transaction_date
CLUSTER BY merchant_category, region;

-- Authorized view: strip PII, mask sensitive fields
CREATE VIEW `my-fintech-prod.retail_transactions_shared.transaction_summary`
OPTIONS (description = 'Aggregated transaction data for partners - no PII')
AS
SELECT
    transaction_date,
    merchant_category,
    region,
    customer_segment,
    COUNT(*) AS transaction_count,
    SUM(amount_usd) AS total_volume_usd,
    AVG(amount_usd) AS avg_transaction_usd,
    COUNTIF(is_fraud) AS fraud_count,
    SAFE_DIVIDE(COUNTIF(is_fraud), COUNT(*)) AS fraud_rate
FROM `my-fintech-prod.retail_transactions_internal.transactions`
GROUP BY 1, 2, 3, 4;

Authorized Views vs. Row-Level Security in BigQuery: BigQuery has two mechanisms for access-controlled sharing. Authorized Views let the view query tables the caller doesn't have direct access to — the view's project provides the data access credential, not the caller's. Row-Level Security (row access policies added via CREATE ROW ACCESS POLICY) enforces per-user filtering at the table level. For Analytics Hub sharing, Authorized Views are the standard pattern because the linked dataset model means all subscribers see the same view definition — row-level differentiation requires a different approach using SESSION_USER() or group membership.

Creating the Analytics Hub Listing

Using the bq command-line tool and the Analytics Hub API (the UI flows mirror these operations):

# Create the data exchange
gcloud data-catalog exchanges create fintech-data-exchange \
    --location=us \
    --display-name="FinTech Analytics Exchange" \
    --description="Curated financial data products for partner risk teams" \
    --project=my-fintech-prod

# Create a listing pointing to our shared dataset
gcloud data-catalog exchanges listings create retail-transaction-summary \
    --exchange=fintech-data-exchange \
    --location=us \
    --display-name="Retail Transaction Summary" \
    --description="Aggregated retail transaction metrics by category, region, and segment. Updated daily." \
    --source-dataset=projects/my-fintech-prod/datasets/retail_transactions_shared \
    --project=my-fintech-prod

For private sharing (specific partner organizations):

# Grant a specific subscriber access to the listing
gcloud data-catalog exchanges listings addIamPolicy retail-transaction-summary \
    --exchange=fintech-data-exchange \
    --location=us \
    --member="group:risk-analytics@partner-org.com" \
    --role="roles/analyticshub.subscriber" \
    --project=my-fintech-prod

Consumer-Side Subscription and Querying

In the subscriber's project, subscribing to a listing creates a linked dataset:

# Consumer subscribes to the listing
gcloud data-catalog exchanges listings subscribe retail-transaction-summary \
    --exchange=fintech-data-exchange \
    --location=us \
    --exchange-project=my-fintech-prod \
    --destination-dataset=partner-risk-project:fintech_shared_data

Now the consumer queries their linked dataset:

-- In partner-risk-project
SELECT
    transaction_date,
    merchant_category,
    region,
    SUM(transaction_count) AS total_transactions,
    SUM(total_volume_usd) AS total_volume,
    AVG(fraud_rate) AS avg_fraud_rate
FROM `partner-risk-project.fintech_shared_data.transaction_summary`
WHERE transaction_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE()
  AND region IN ('Northeast', 'Southeast', 'West')
GROUP BY 1, 2, 3
ORDER BY transaction_date DESC, total_volume DESC;

The consumer's project is billed for query compute. The provider's project is billed for storage. No data was moved.

VPC Service Controls: The Security Layer You Cannot Skip

Analytics Hub without VPC Service Controls is like a bank vault with a strong door but no walls. VPC Service Controls create a security perimeter around BigQuery API calls, preventing data exfiltration through API access outside the perimeter.

In production Analytics Hub deployments handling sensitive data, you need to configure Service Perimeters for both provider and consumer projects:

# Create a service perimeter for the provider
gcloud access-context-manager perimeters create fintech-provider-perimeter \
    --title="FinTech Provider Perimeter" \
    --resources=projects/my-fintech-prod \
    --restricted-services=bigquery.googleapis.com,analyticshub.googleapis.com \
    --access-policy=POLICY_ID

# For cross-project access (subscriber queries), add perimeter bridges
gcloud access-context-manager perimeters update fintech-provider-perimeter \
    --add-access-levels=partners/partner_access_level \
    --policy=POLICY_ID

---

External Tables: The Cross-Warehouse Bridge

What External Tables Are and When to Use Them

External Tables are the glue that makes true multi-warehouse federation possible. Both Snowflake and BigQuery can query data that lives in object storage (S3, GCS, ADLS) without ingesting it into native table format. This creates a path where data can be written once to object storage and queried from multiple warehouse systems simultaneously.

The canonical use case: your operational data platform writes Parquet files to S3 as part of its streaming pipeline. Your data science team uses Snowflake for SQL analysis, your ML platform uses BigQuery for feature engineering, and you want both to access the same underlying data without running two copies of the ingestion pipeline.

The performance characteristics of External Tables are very different from native tables, and understanding why is critical for using them correctly. Native tables (Snowflake micropartitions, BigQuery managed storage) have highly optimized metadata that allows the query engine to prune aggressively and read only the columns and row ranges needed. External Tables must work with whatever file format and organization you've given them, and the quality of that organization determines whether queries take 5 seconds or 5 hours.

Designing Your Object Storage Layer for Federation

Before creating a single External Table, you need to think carefully about how data is organized in object storage. This decision is largely irreversible without rewriting data.

For a time-series dataset (our transaction data example), the ideal layout:

s3://my-data-lake/
└── transactions/
    └── v1/
        ├── region=Northeast/
        │   ├── merchant_category=Electronics/
        │   │   ├── transaction_date=2024-01-01/
        │   │   │   ├── part-00000.parquet  (128MB target)
        │   │   │   └── part-00001.parquet
        │   │   └── transaction_date=2024-01-02/
        │   │       └── part-00000.parquet
        │   └── merchant_category=Grocery/
        │       └── ...
        └── region=Southeast/
            └── ...

This Hive-partitioned layout allows both Snowflake and BigQuery to prune at the directory level before reading any files. The version prefix (v1/) is a schema evolution strategy — when the schema changes incompatibly, you write to v2/ and update external table definitions to point to both (or just the new one), preserving backward compatibility for running pipelines.

File format choices matter enormously:

• Parquet is the gold standard for analytics workloads. Columnar, splittable, excellent compression, supported by everything. Use this unless you have a specific reason not to.
• ORC performs comparably to Parquet but has better ecosystem support in Hive-centric environments. If your data originates from a Hive-based system, ORC may already be your format.
• Avro is row-oriented and better for streaming ingestion patterns where you're writing many small messages. Poor for analytics queries that read a few columns from many rows. Don't use Avro for external tables you plan to query analytically.
• JSON/CSV should be treated as a last resort for external tables. They offer no column pruning, poor compression, and require full row deserialization even for single-column queries.

External Tables in Snowflake

-- First, create a storage integration (one-time setup by ACCOUNTADMIN)
CREATE STORAGE INTEGRATION s3_data_lake_integration
    TYPE = EXTERNAL_STAGE
    STORAGE_PROVIDER = 'S3'
    ENABLED = TRUE
    STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789:role/snowflake-data-lake-role'
    STORAGE_ALLOWED_LOCATIONS = ('s3://my-data-lake/transactions/');

-- Create a stage pointing to the data lake location
CREATE STAGE data_lake.raw.transactions_stage
    URL = 's3://my-data-lake/transactions/v1/'
    STORAGE_INTEGRATION = s3_data_lake_integration
    FILE_FORMAT = (
        TYPE = 'PARQUET'
        SNAPPY_COMPRESSION = TRUE
    );

-- Create the external table with explicit partition columns
CREATE OR REPLACE EXTERNAL TABLE data_lake.raw.transactions_ext (
    transaction_id      VARCHAR     AS (VALUE:transaction_id::VARCHAR),
    transaction_date    DATE        AS (TO_DATE(SPLIT_PART(METADATA$FILENAME, '/', 5)
                                        REGEXP_SUBSTR(
                                            METADATA$FILENAME,
                                            'transaction_date=([0-9-]+)',
                                            1, 1, 'e', 1
                                        ))),
    merchant_category   VARCHAR     AS (
                                        REGEXP_SUBSTR(
                                            METADATA$FILENAME,
                                            'merchant_category=([^/]+)',
                                            1, 1, 'e', 1
                                        )),
    region              VARCHAR     AS (
                                        REGEXP_SUBSTR(
                                            METADATA$FILENAME,
                                            'region=([^/]+)',
                                            1, 1, 'e', 1
                                        )),
    amount_usd          FLOAT       AS (VALUE:amount_usd::FLOAT),
    customer_segment    VARCHAR     AS (VALUE:customer_segment::VARCHAR),
    is_fraud            BOOLEAN     AS (VALUE:is_fraud::BOOLEAN)
)
PARTITION BY (transaction_date, region, merchant_category)
LOCATION = @data_lake.raw.transactions_stage
AUTO_REFRESH = TRUE
FILE_FORMAT = (TYPE = 'PARQUET');

The AUTO_REFRESH = TRUE setting configures Snowflake to listen to S3 event notifications via SQS for new files. Without this, you'd need to manually run ALTER EXTERNAL TABLE ... REFRESH to detect new files. Set up the SQS notification on the bucket before enabling auto-refresh.

Refreshing Metadata and Handling Schema Evolution

When new files land with slightly different column schemas (a common reality with streaming pipelines), you have options:

-- Check current partition metadata
SELECT *
FROM TABLE(
    INFORMATION_SCHEMA.EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY(
        TABLE_NAME => 'transactions_ext',
        TABLE_SCHEMA => 'raw'
    )
)
ORDER BY registered_on DESC
LIMIT 50;

-- Force a full refresh after schema changes
ALTER EXTERNAL TABLE data_lake.raw.transactions_ext REFRESH;

-- For schema evolution: add nullable columns that older files will return NULL for
-- Snowflake handles this gracefully for Parquet (missing columns = NULL)
-- For BigQuery-originated Parquet, watch for field ID vs. name-based resolution differences

External Tables in BigQuery

BigQuery's approach to external tables (called "External Data Sources" in the UI, but "External Tables" in SQL) is similar but has some important differences in behavior:

-- BigQuery external table definition
CREATE OR REPLACE EXTERNAL TABLE `my-analytics-project.data_lake.transactions_ext`
WITH PARTITION COLUMNS (
    region              STRING,
    merchant_category   STRING,
    transaction_date    DATE
)
OPTIONS (
    format = 'PARQUET',
    uris = ['gs://my-data-lake/transactions/v1/*'],
    hive_partition_uri_prefix = 'gs://my-data-lake/transactions/v1/',
    require_hive_partition_filter = FALSE
);

BigQuery's require_hive_partition_filter option is worth discussing. When set to TRUE, BigQuery will reject queries that don't include a filter on at least one partition column. This prevents expensive full-table scans on very large external datasets — a useful safety mechanism in production. Set it to FALSE during development, then flip it to TRUE once your team's query patterns are established.

-- Query with partition pruning (efficient)
SELECT
    merchant_category,
    SUM(amount_usd) AS total_volume,
    COUNTIF(is_fraud) AS fraud_events
FROM `my-analytics-project.data_lake.transactions_ext`
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND region = 'Northeast'
GROUP BY merchant_category
ORDER BY total_volume DESC;

-- Check bytes processed to validate pruning is working
-- In BigQuery: run the query with dry_run=True first

---

Architecting a Multi-Warehouse Federation Layer

The Semantic Layer Problem

Once you have data accessible from both Snowflake and BigQuery through a combination of native sharing and external tables, you face the semantic layer problem: the same business concept (say, "active customer" or "gross revenue") may have subtly different definitions expressed in both systems, and any inconsistency will erode trust in your data platform.

The standard solution is to implement a semantic layer tool — dbt, Cube, LookML — that generates the canonical metric definitions and can be deployed against either warehouse. But in a true federation scenario, you need to go further and ensure that the "active customer" definition in Snowflake's shared view and the "active customer" definition in BigQuery's Analytics Hub listing are generated from the same source definition.

Here's a practical pattern using dbt:

# dbt_project.yml
name: 'enterprise_metrics'
version: '1.0'

models:
  enterprise_metrics:
    active_customers:
      +tags: ['shared', 'governance-approved']
      +meta:
        sharing:
          snowflake:
            share_name: 'sh_analytics_metrics'
            schema: 'shared_metrics'
          bigquery:
            analytics_hub_dataset: 'enterprise_metrics_shared'

-- models/shared/active_customers.sql
-- This model deploys to both Snowflake and BigQuery
-- dbt handles the dialect differences via adapter macros

{{ config(
    materialized='view',
    tags=['shared', 'governance-approved']
) }}

WITH base AS (
    SELECT
        customer_id,
        first_purchase_date,
        last_purchase_date,
        total_orders,
        total_spend_usd
    FROM {{ ref('customer_lifetime_value') }}
),

active_definition AS (
    SELECT
        customer_id,
        first_purchase_date,
        last_purchase_date,
        total_orders,
        total_spend_usd,
        CASE
            WHEN last_purchase_date >= {{ dbt.dateadd('day', -90, 'current_date') }}
             AND total_orders >= 2
            THEN TRUE
            ELSE FALSE
        END AS is_active
    FROM base
)

SELECT * FROM active_definition
WHERE is_active = TRUE

The dbt CI/CD pipeline deploys this model to both warehouses. When the definition of "active customer" changes, a single PR updates both systems simultaneously.

Handling Cross-Warehouse JOINs

The most seductive and most dangerous capability in a federation architecture is the cross-warehouse JOIN: take a table from Snowflake and JOIN it to a table in BigQuery. Every federation vendor will sell you on this capability. What they don't emphasize: cross-warehouse JOINs almost always involve moving data, and moving data at query time is expensive, slow, and unpredictable.

The right mental model is to think of cross-warehouse JOINs as requiring a data movement strategy, not a query strategy:

Strategy 1: Publish to the common substrate (External Tables) If data from System A needs to be JOINed with data from System B frequently, write System A's data to object storage in Parquet format and create External Tables in System B. This is not a real-time JOIN — it's a near-real-time copy — but it's orders of magnitude faster and cheaper than ad-hoc cross-system query time data movement.

Strategy 2: Pre-JOIN with a scheduled materialization Use a orchestration tool (Airflow, Dagster, Prefect) to schedule a periodic extraction from one system, load it into the other, and materialize the JOIN result. You lose recency but gain predictability and cost control.

Strategy 3: Semantic layer aggregation For dashboard and reporting use cases, compute aggregated metrics in each system separately and join them at the semantic layer level, where you're joining small result sets (thousands of rows) rather than large fact tables.

What to avoid: Using Snowflake's external function capability or BigQuery's Remote Functions to call across systems at query time, row-by-row. This is the worst of all worlds — the overhead of a network call per row, unpredictable latency, and no partition pruning.

Governance and Lineage in a Federated World

Governance becomes exponentially harder when data flows through multiple systems. You need to answer: where did this shared metric come from, who has access to it, what's the freshness, and what changed last week?

A federated governance architecture needs at minimum:

• A metadata catalog (Datahub, Atlan, Collibra) that ingests metadata from both Snowflake and BigQuery, resolves entity equivalencies (the "transactions" table in Snowflake and the "transactions" external table in BigQuery are the same logical entity), and tracks lineage across warehouse boundaries
• Data contracts enforced at the sharing layer, not assumed at the consumption layer
• Centralized access audit logging: Snowflake's QUERY_HISTORY and BigQuery's Cloud Audit Logs both export to the same SIEM or data warehouse for unified access monitoring

-- Snowflake: query access audit for shared objects
SELECT
    q.query_id,
    q.user_name,
    q.role_name,
    q.query_text,
    q.database_name,
    q.schema_name,
    q.start_time,
    q.bytes_scanned,
    q.rows_produced
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.database_name = 'EQUITIES_FEED'  -- The shared database name in consumer account
  AND q.start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
ORDER BY q.bytes_scanned DESC
LIMIT 100;

---

Hands-On Exercise

In this exercise, you'll implement a simplified version of the architecture we've discussed. You'll need access to a Snowflake trial account and a Google Cloud project with BigQuery enabled.

Scenario: You're building a data sharing platform for a retail analytics consortium. Member retailers contribute their anonymized transaction data to a shared data lake (we'll simulate this with a synthetic dataset), and each member gets access to consortium-wide benchmarks they can use to compare their performance to industry averages.

Step 1: Generate the synthetic dataset

import pandas as pd
import numpy as np
from datetime import date, timedelta
import os

def generate_transactions(n_rows=500_000, output_path='./data/transactions'):
    os.makedirs(output_path, exist_ok=True)
    
    regions = ['Northeast', 'Southeast', 'Midwest', 'West', 'Southwest']
    categories = ['Grocery', 'Electronics', 'Apparel', 'Restaurants', 'Home & Garden']
    segments = ['Budget', 'Mid-Market', 'Premium', 'Luxury']
    
    np.random.seed(42)
    
    start_date = date(2024, 1, 1)
    dates = [start_date + timedelta(days=i) for i in range(90)]
    
    records = []
    for transaction_date in dates:
        for region in regions:
            for category in categories:
                n = np.random.randint(500, 2000)
                amounts = np.random.lognormal(mean=3.5, sigma=1.2, size=n)
                fraud_mask = np.random.random(n) < 0.012  # 1.2% fraud rate
                segment_choices = np.random.choice(segments, size=n)
                
                for i in range(n):
                    records.append({
                        'transaction_id': f"{transaction_date.strftime('%Y%m%d')}-{region[:3]}-{category[:3]}-{i:06d}",
                        'transaction_date': transaction_date.isoformat(),
                        'region': region,
                        'merchant_category': category,
                        'customer_segment': segment_choices[i],
                        'amount_usd': round(float(amounts[i]), 2),
                        'is_fraud': bool(fraud_mask[i])
                    })
    
    df = pd.DataFrame(records)
    
    # Write partitioned Parquet
    for (region, category, tx_date), group in df.groupby(
        ['region', 'merchant_category', 'transaction_date']
    ):
        partition_path = (
            f"{output_path}/region={region}/"
            f"merchant_category={category}/"
            f"transaction_date={tx_date}/"
        )
        os.makedirs(partition_path, exist_ok=True)
        group.drop(columns=['region', 'merchant_category', 'transaction_date'])\
            .to_parquet(
                f"{partition_path}/part-00000.parquet",
                index=False,
                compression='snappy'
            )
    
    print(f"Generated {len(df):,} records across {len(dates)} days")
    print(f"Parquet files written to {output_path}")
    return df

df = generate_transactions()

Step 2: Upload the generated Parquet files to an S3 bucket (or GCS bucket), then create External Tables in both Snowflake and BigQuery pointing to the same data.

Step 3: In Snowflake, create a Secure Share with a view that computes consortium benchmarks (average fraud rate, average transaction value) by category and region, and verify that the row access policy correctly restricts access based on the querying account.

Step 4: In BigQuery, create an Analytics Hub listing with an Authorized View that exposes the same benchmarks, and subscribe to it from a second GCP project to verify the linked dataset behavior.

Step 5: Validate consistency — run the benchmark query from both Snowflake's External Table and BigQuery's External Table against the same underlying Parquet files and verify that the results are identical (within floating-point tolerance).

---

Common Mistakes & Troubleshooting

Mistake 1: Sharing base tables instead of views Sharing raw tables means your entire schema is your API contract. When you add a column, rename a column, or change a data type internally, you break consumers. Always share views, and treat those views as versioned APIs. Use v1_transactions, v2_transactions naming if you need to introduce breaking changes while maintaining backward compatibility.

Mistake 2: Not accounting for time zones in cross-warehouse joins Snowflake stores TIMESTAMP_TZ with timezone offset. BigQuery stores TIMESTAMP as UTC with no timezone information in the type system. When JOINing temporal data across systems via an external table or otherwise, a naive comparison of timestamp values will produce wrong results if one system implicitly converted to local time. Always normalize to UTC before crossing warehouse boundaries.

Mistake 3: External table partition pruning not working The most common symptom: queries against an External Table are scanning all files despite a WHERE clause on the partition column. The usual cause is a type mismatch between the partition column definition in the external table and the WHERE clause predicate. In Snowflake, if you defined the partition column as DATE but your WHERE clause uses a string literal without an explicit cast, pruning may fail silently. Always verify with the query profile.

-- Debug: check what Snowflake thinks the partitions are
SELECT SYSTEM$EXTERNAL_TABLE_PIPE_STATUS('transactions_ext');

-- Force a metadata refresh if files aren't appearing
ALTER EXTERNAL TABLE data_lake.raw.transactions_ext REFRESH;

Mistake 4: Confusing Analytics Hub linked datasets with actual data copies New users of Analytics Hub often assume they can create indexes, cache data, or optimize the linked dataset. You can't — it's a metadata pointer. If you need better query performance on a subscribed dataset, materialize it into a native BigQuery table on a schedule. Recognize this trades freshness for performance and govern that trade-off explicitly.

Mistake 5: Snowflake share consumer accounts in wrong region Adding a consumer account that's in a different Snowflake region than the share will silently succeed in the UI but fail at query time with a cryptic error about "micropartition not accessible." Always confirm cloud/region alignment before establishing sharing relationships. Use SELECT CURRENT_REGION() in both accounts.

Mistake 6: Analytics Hub and column-level security BigQuery column-level security (Policy Tags) does not automatically propagate through Analytics Hub listings. If you've tagged a column as PII/sensitive in the internal dataset, that policy tag is NOT inherited by the Authorized View you publish. You must either exclude the column from the view entirely or explicitly apply Policy Tags to the view's output schema. Test this by subscribing as a low-privilege test user and verifying column access is rejected.

---

Summary & Next Steps

You've worked through the full stack of cross-warehouse federation: the storage-layer internals of Snowflake Secure Data Sharing, the marketplace model of BigQuery Analytics Hub, and the shared object storage substrate of External Tables that bridges both. More importantly, you've seen these not as isolated features but as components of a coherent architecture that addresses the real tensions in modern data sharing: recency vs. cost, openness vs. governance, flexibility vs. consistency.

The key principles to carry forward:

• Know which model you're operating in (copy, federated query, or zero-copy share) at every point in your architecture. The cost, latency, and governance implications are completely different.
• Govern at the sharing layer, not the trust layer. Row access policies, authorized views, and VPC Service Controls are not optional — they're the difference between a sharing architecture and a data breach.
• Your object storage layout is your External Table performance. Parquet + Hive partitioning + right-sized files is not configuration, it's architecture. Get it wrong and no amount of query optimization will save you.
• The semantic layer is not downstream of sharing — it's upstream. Define your canonical metrics once in a tool like dbt and deploy them to both warehouses. Inconsistent definitions across shared datasets erode trust faster than any technical failure.

Next steps to deepen this expertise:

1. Explore Snowflake Data Clean Rooms, which extend Secure Data Sharing with a privacy-preserving joint analysis capability — two parties can run queries across their combined data without either party seeing the other's raw data. The cryptographic and query restriction model is fascinating and increasingly relevant for regulated industries.

2. Implement data contracts using tools like soda-core or elementary that validate schema, freshness, and statistical properties of shared datasets on every pipeline run — before consumers are affected.

3. Study Apache Iceberg as an alternative to Hive-partitioned Parquet for your object storage layer. Iceberg's table format spec supports ACID transactions, time travel, and hidden partitioning, and both Snowflake and BigQuery now support querying Iceberg tables natively. This is the direction the industry is moving for multi-engine external table scenarios.

4. Build the governance layer: integrate Snowflake's ACCOUNT_USAGE views and BigQuery's information schema into a unified metadata catalog, and implement automated lineage tracking that follows data across warehouse boundaries.