> [!WARNING]
>
> **Preview**
>
>
> This feature is
>
> subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the
> [Service Specific
> Terms](https://docs.cloud.google.com/terms/service-terms#1).
>
> Pre-GA features are available "as is" and might have limited support.
>
> For more information, see the
> [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages).

This page introduces Lakehouse Federation in AlloyDB for PostgreSQL, a unified
data management solution that extends AlloyDB's query capabilities.
This integration lets you seamlessly query a range of BigQuery
resources, including the following:

- Native tables and materialized views.
- BigQuery views (logical views).
- [BigLake external tables](https://docs.cloud.google.com/bigquery/docs/biglake-intro), including those for [Apache Iceberg managed tables](https://docs.cloud.google.com/bigquery/docs/iceberg-tables).
- Standard external tables.

With Lakehouse Federation, you can use AlloyDB's query
engine to power your application's transactional and analytical workloads from
within the same interface. You can also materialize or import this data on
AlloyDB for faster access for use in your applications, which
lets you use
[AlloyDB AI](https://docs.cloud.google.com/alloydb/docs/ai)
and the
[columnar engine](https://docs.cloud.google.com/alloydb/docs/columnar-engine/about).

Lakehouse Federation for AlloyDB lets you load and transform data
from BigQuery or Iceberg into AlloyDB to support your
operational applications or to join historical data with real-time transactional
data. This use case supports unified analytics and a complete view of your
business in your application context.

You can use AlloyDB as a transactional database and also have
large amounts of data residing in BigQuery or
BigLake. Your applications usually integrate independently with
both of these systems to access data across these different Google Cloud
services. Lakehouse Federation lets you use AlloyDB's
federated query support implemented as a foreign data wrapper to access
BigQuery and AlloyDB data using a SQL interface in
AlloyDB.

## Pushdown

You can use filter and aggregate pushdown techniques, which speed up queries and
reduce costs by filtering or summarizing data in BigQuery before
it's moved or processed by AlloyDB. This approach minimizes network traffic and memory usage,
letting you analyze massive datasets quickly and efficiently without exceeding resource
limits.

### Filter pushdown

*Filter pushdown* , also known as predicate pushdown, is an optimization technique
that moves the filtering of data as close to the storage layer as possible.
Instead of reading a massive table into memory and then discarding the rows that
you don't need, the database "pushes" the filter (using the `WHERE` clause) down
into the initial data scan.

With filter pushdown, you can use SQL queries with a `WHERE` clause to access a subset
of data from the remote table. This data can also be materialized on a local table
or attached as a local partition to a PostgreSQL table.

#### Supported filters

The following comparison and arithmetic operations are pushed down to BigQuery
using the BigQuery API if they appear in expressions in the filtering (`WHERE`)
clause of the PostgreSQL query:

**Comparison operators**

`=`, `>`, `<`, `>=`, `<=`, `<>`, `~~`, `!~~`

**Arithmetic operators**

`+`, `-`, `*`, `/`

> [!NOTE]
> **Note:** Filtering is also supported by the BigQuery Storage Read API.

### Aggregate pushdown

*Aggregate pushdown* is an advanced database optimization that performs
calculations---for example, `SUM`, `COUNT`, `AVG`, or `GROUP BY` as close to
the storage layer as possible. While filter
pushdown removes unneeded rows, aggregate pushdown summarizes needed rows before
the database engine processes them further.

#### Supported aggregates

The following aggregation functions are pushed down to BigQuery using
the BigQuery API, when applicable to the supported data types:

- SUM
- AVG
- MIN
- MAX
- COUNT

## BigQuery cost and billing

The BigQuery foreign data wrapper depends on the following:

- BigQuery compute pricing
- BigQuery Storage API pricing

For information, see [BigQuery pricing](https://cloud.google.com/bigquery/pricing).

### Limitations

- AlloyDB and BigQuery may use different collations, which can result in different data ordering between the two systems. For any part of a query executed remotely on BigQuery, the collation follows BigQuery's settings.
- Queries that return a very large amount of data from BigQuery, after the [pushdown](https://docs.cloud.google.com/alloydb/docs/lakehouse-federation-overview#pushdown), aren't optimized.

### What's next

- [Access BigQuery data from AlloyDB](https://docs.cloud.google.com/alloydb/docs/access-bigquery-data-from-alloydb).
- [Sync BigQuery and Iceberg data to AlloyDB](https://docs.cloud.google.com/alloydb/docs/sync-bigquery-iceberg-data).
- Learn about [supported BigQuery data types and column mappings](https://docs.cloud.google.com/alloydb/docs/reference/supported-bigquery-data-types-column-mappings).