> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Aggregate Tables

DirectQuery is using the external database to feed some aggregate providers and answer some queries.
This can result in some large queries with heavy load on the database, and if the same cube is started multiple times, the same query can be repeated.

Aggregate tables are a way to cache some aggregated data in the external database to make some queries faster and cheaper.

## How does it work

Aggregate Tables are tables added in the external database as a table fed from aggregated data. These tables typically denormalize fields from joined tables into group-by columns, but can also preserve joins to keep the aggregate table smaller while still accessing fields from other tables at query time.

```mermaid theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}

graph LR;

    subgraph Aggtable[AggregateTable]
    InnerAggtable[<font size=18> fa:fa-table]
    end;

    subgraph Schema
    direction LR
    T0(<font size=18> fa:fa-table)
    T1(<font size=18> fa:fa-table)
    T2(<font size=18> fa:fa-table)
    T3(<font size=18> fa:fa-table)
    T0 --> T1
    T1 --> T2
    T0 --> T3
    %% linkStyle 0 stroke:white,arrow-head-color:white
    end;

    subgraph Database[External Database]
    direction TB
    Schema --> |Create table and feed| Aggtable
    end;

    classDef noBorder stroke-width:0px
    classDef external fill:#B3D9FF,color:black
    classDef table stroke-width:0px,fill:none,font-size:22px
    classDef bordered stroke:black

    class T0,T1,T2,T3,InnerAggtable table
    class Aggtable noBorder
    class Database,Aggtable,Schema external
    class Database,Schema bordered
```

Whenever possible, DirectQuery will query the Aggregate Table instead of the base Schema for a more optimized query:

```mermaid theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}

graph LR;

    subgraph Atoti
    DirectQuery["DirectQuery
    (dispatches queries)"]
    end;

    subgraph Aggtable[AggregateTable]
    InnerAggtable[<font size=18> fa:fa-table]
    end;

    subgraph Schema
    direction LR
    T0(<font size=18> fa:fa-table)
    T1(<font size=18> fa:fa-table)
    T2(<font size=18> fa:fa-table)
    T3(<font size=18> fa:fa-table)
    T0 --> T1
    T1 --> T2
    T0 --> T3
    %% linkStyle 0 stroke:white,arrow-head-color:white
    end;

    subgraph Database[External Database]
    direction LR
    Schema
    Aggtable
    end;

    DirectQuery --> |normal query| Schema
    DirectQuery --> |fast query| Aggtable

    classDef atoti fill:#FF9999,color:black
    classDef noBorder stroke-width:0px
    classDef external fill:#B3D9FF,color:black
    classDef table stroke-width:0px,fill:none,font-size:22px
    classDef bordered stroke:black

    class T0,T1,T2,T3,InnerAggtable table
    class Aggtable noBorder
    class Atoti,DirectQuery atoti
    class Database,Aggtable,Schema external
    class Atoti,DirectQuery,Database,Schema bordered
```

For instance, consider a *Sales* table containing sales such as this one:

| Id | Date       | Product | Buyer    | Quantity | Price |
| -- | ---------- | ------- | -------- | -------- | ----- |
| 1  | 2023-01-01 | P1      | Buyer\_A | 100.0    | 15.5  |
| 2  | 2023-01-01 | P1      | Buyer\_B | 100.0    | 16.5  |
| 3  | 2023-01-01 | P2      | Buyer\_A | 100.0    | 30    |
| 4  | 2023-01-01 | P2      | Buyer\_B | 100.0    | 31    |
| 5  | 2023-01-02 | P1      | Buyer\_A | 100.0    | 15.5  |
| 6  | 2023-01-02 | P1      | Buyer\_B | 100.0    | 15.5  |
| 7  | 2023-01-02 | P1      | Buyer\_A | 100.0    | 18    |
| 8  | 2023-01-03 | P1      | Buyer\_B | 100.0    | 18    |
| 9  | 2023-01-03 | P2      | Buyer\_A | 100.0    | 35    |
| 10 | 2023-01-03 | P2      | Buyer\_B | 100.0    | 40    |

It is possible to aggregate the sum of the *Quantity* per *Date* and *Product* in the external database into an other table *agg\_sales*:

| date       | product | sum\_of\_quantity | count\_of\_rows |
| ---------- | ------- | ----------------- | --------------- |
| 2023-01-01 | P1      | 200.0             | 2               |
| 2023-01-01 | P2      | 200.0             | 2               |
| 2023-01-02 | P1      | 300.0             | 3               |
| 2023-01-03 | P1      | 100.0             | 1               |
| 2023-01-03 | P2      | 200.0             | 2               |

If this table is added as an Aggregate Table in DirectQuery, it will be used whenever possible instead of the original *Sales* table.

For instance when asking for the `SUM(Quantity)` per *Product* in the *Sales* table, DirectQuery will retrieve the `SUM(sum_of_quantity)` per *product* in *agg\_sales*.
As the *agg\_sales* is smaller and already aggregated the result will be faster to compute and will potentially avoid a huge scan of the database, saving both time and compute costs.

## Use cases

There are a few classic use cases for using an Aggregate Table:

* Feeding an aggregate provider faster by pre-aggregating over the provider fields
* Having an "external database aggregate provider", i.e. being able to answer some queries from the pre-aggregated data in the external database but without having to store additional data in-memory.
* Feeding some hierarchies faster

## Supported aggregations

The following aggregation function can be used in an Aggregate Table:

* Sum
* Min
* Max
* Average
* Sum product
* Count
* Long
* Short
* Gross
* Square sum

### Custom Aggregations

To use a custom aggregation in an aggregate table, you need to define the corresponding [user defined aggregation](./how_to/user_defined_aggregation).\
You can also register its corresponding [re-aggregation function](./how_to/user_defined_aggregation#re-aggregable-udaf).\
This re-aggregation function will be used to compute the aggregates at a coarser granularity than the one stored in the aggregate table.

## Examples

### Create a simple Aggregate Table to feed an aggregate provider

Let’s create a new table that will pre-aggregate the quantity (in the tutorial sales table) per Date and Product:

<Tabs>
  <Tab title="BigQuery">
    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    CREATE OR REPLACE TABLE `tutorial`.SALES_BY_PRODUCT (
      PRODUCT STRING NOT NULL,
      QUANTITY_SUM FLOAT64 NOT NULL,
      CONTRIBUTORS_COUNT INT64 NOT NULL
    );
    ```

    We feed it with the data from the other tables:

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    INSERT INTO `tutorial`.SALES_BY_PRODUCT
      SELECT
        T0.PRODUCT AS PRODUCT,
        sum(T0.QUANTITY) AS QUANTITY_SUM,
        COUNT(*) AS CONTRIBUTORS_COUNT
      FROM
        `tutorial`.SALES AS T0
      GROUP BY
        T0.PRODUCT;
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    create table
      TUTORIAL.sales_by_product
    (
      `product` String NOT NULL,
      `quantity_sum` Float64 NOT NULL,
      `contributors_count` UInt64 NOT NULL
    )
    ENGINE = MergeTree()
    ORDER BY (product);
    ```

    We feed it with the data from the other tables:

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    INSERT INTO TUTORIAL.sales_by_product
      SELECT
        T0.PRODUCT AS `product`,
        sum(T0.QUANTITY) AS `quantity_sum`,
        COUNT(*) AS `contributors_count`
      FROM
        TUTORIAL.SALES AS T0
      GROUP BY
        T0.PRODUCT;
    ```
  </Tab>

  <Tab title="Databricks">
    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    CREATE OR REPLACE TABLE
      tutorial.SALES_BY_PRODUCT
    (
      PRODUCT String NOT NULL,
      QUANTITY_SUM FLOAT NOT NULL,
      CONTRIBUTORS_COUNT INTEGER NOT NULL
    )
    ```

    We feed it with the data from the other tables:

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    INSERT INTO tutorial.SALES_BY_PRODUCT
      SELECT
        T0.PRODUCT AS PRODUCT,
        sum(T0.QUANTITY) AS QUANTITY_SUM,
        COUNT(*) AS CONTRIBUTORS_COUNT
      FROM
        tutorial.SALES AS T0
      GROUP BY
        T0.PRODUCT;
    ```
  </Tab>

  <Tab title="MsSql">
    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    if not exists (select * from sys.tables where name = 'SALES_BY_PRODUCT')
    CREATE TABLE TUTORIAL.SALES_BY_PRODUCT (
      [PRODUCT] VARCHAR(50) NOT NULL,
      [QUANTITY_SUM] FLOAT NOT NULL,
      [CONTRIBUTORS_COUNT] BIGINT NOT NULL
    );
    ```

    We feed it with the data from the other tables:

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    INSERT INTO TUTORIAL.SALES_BY_PRODUCT
      SELECT
        T0.PRODUCT AS PRODUCT,
        sum(T0.QUANTITY) AS QUANTITY_SUM,
        COUNT(*) AS CONTRIBUTORS_COUNT
      FROM
        TUTORIAL.SALES AS T0
      GROUP BY
        T0.PRODUCT;
    ```
  </Tab>

  <Tab title="Redshift">
    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    CREATE TABLE IF NOT EXISTS
      tutorial."SALES_BY_PRODUCT"
    (
      "PRODUCT" VARCHAR NOT NULL,
      "QUANTITY_SUM" FLOAT NOT NULL,
      "CONTRIBUTORS_COUNT" BIGINT NOT NULL
    );
    ```

    We feed it with the data from the other tables:

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    INSERT INTO tutorial."SALES_BY_PRODUCT"
      SELECT
        T0."PRODUCT" AS "PRODUCT",
        sum(T0."QUANTITY") AS "QUANTITY_SUM",
        COUNT(*) AS "CONTRIBUTORS_COUNT"
      FROM
        tutorial."SALES" AS T0
      GROUP BY
        T0."PRODUCT";
    ```
  </Tab>

  <Tab title="Snowflake">
    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    CREATE OR REPLACE TABLE
      TUTORIAL.SALES_BY_PRODUCT
    (
      PRODUCT String NOT NULL,
      QUANTITY_SUM FLOAT NOT NULL,
      CONTRIBUTORS_COUNT INTEGER NOT NULL
    )
    ```

    We feed it with the data from the other tables:

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    INSERT INTO TUTORIAL.SALES_BY_PRODUCT
      SELECT
        T0.PRODUCT AS PRODUCT,
        sum(T0.QUANTITY) AS QUANTITY_SUM,
        COUNT(*) AS CONTRIBUTORS_COUNT
      FROM
        TUTORIAL.SALES AS T0
      GROUP BY
        T0.PRODUCT;
    ```
  </Tab>

  <Tab title="Synapse">
    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    CREATE VIEW TUTORIAL.SALES_BY_PRODUCT_VIEW AS
      SELECT
        T0.PRODUCT AS PRODUCT,
        sum(T0.QUANTITY) AS QUANTITY_SUM,
        COUNT(*) AS CONTRIBUTORS_COUNT
      FROM
        TUTORIAL.SALES AS T0
      GROUP BY
        T0.PRODUCT;
    ```

    <Note>
      To simplify this example on Synapse we create a view containing the Aggregated data.
      However, views means that the result will be recomputed for each query so it is not a wise choice for an Aggregate Table.
      We recommend to use a materialized table.
    </Note>
  </Tab>
</Tabs>

The pre-aggregated data looks like this:

| PRODUCT | QUANTITY\_SUM | CONTRIBUTORS\_COUNT |
| ------- | ------------- | ------------------- |
| BED\_2  | 6             | 4                   |
| HOO\_5  | 3             | 3                   |
| TAB\_0  | 4             | 4                   |
| TAB\_1  | 4             | 3                   |
| TSH\_3  | 3             | 3                   |
| TSH\_4  | 5             | 3                   |

Let's first discover the SQL table corresponding to the Aggregate Table:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final TableDescription salesTable =
        discoverer.discoverTable(new SqlTableId(PROJECT_ID, "tutorial", "SALES"));
    final TableDescription table =
        discoverer.discoverTable(new SqlTableId(PROJECT_ID, "tutorial", "SALES_BY_PRODUCT"));
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final TableDescription salesTable =
        discoverer.discoverTable(new ClickhouseTableId("TUTORIAL", "SALES"));
    final TableDescription table =
        discoverer.discoverTable(new ClickhouseTableId("TUTORIAL", "sales_by_product"));
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final TableDescription salesTable =
        discoverer.discoverTable(new SqlTableId(CATALOG_NAME, TUTORIAL_SCHEMA_NAME, "SALES"));
    final TableDescription table =
        discoverer.discoverTable(
            new SqlTableId(CATALOG_NAME, TUTORIAL_SCHEMA_NAME, "SALES_BY_PRODUCT"));
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final TableDescription salesTable =
        discoverer.discoverTable(new SqlTableId(DATABASE_NAME, SCHEMA_NAME, "SALES"));
    final TableDescription table =
        discoverer.discoverTable(new SqlTableId(DATABASE_NAME, SCHEMA_NAME, "SALES_BY_PRODUCT"));
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final TableDescription salesTable =
        discoverer.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "SALES"));
    final TableDescription table =
        discoverer.discoverTable(
            new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "SALES_BY_PRODUCT"));
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final TableDescription salesTable =
        discoverer.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "SALES"));
    final TableDescription table =
        discoverer.discoverTable(
            new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "SALES_BY_PRODUCT"));
    ```
  </Tab>

  <Tab title="Synapse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final TableDescription salesTable =
        discoverer.discoverTable(new SqlTableId(TEST_DATABASE_NAME, "TUTORIAL", "SALES"));
    final TableDescription table =
        discoverer.discoverTable(
            new SqlTableId(TEST_DATABASE_NAME, "TUTORIAL", "SALES_BY_PRODUCT_VIEW"));
    ```
  </Tab>
</Tabs>

We will map the `PRODUCT` field of the sales table to the `product` field of the Aggregate Table:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final Map<FieldPath, String> fieldMapping = Map.of(FieldPath.of("PRODUCT"), "PRODUCT");
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final Map<FieldPath, String> fieldMapping = Map.of(FieldPath.of("PRODUCT"), "product");
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final Map<FieldPath, String> fieldMapping = Map.of(FieldPath.of("PRODUCT"), "PRODUCT");
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final Map<FieldPath, String> fieldMapping = Map.of(FieldPath.of("PRODUCT"), "PRODUCT");
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final Map<FieldPath, String> fieldMapping = Map.of(FieldPath.of("PRODUCT"), "PRODUCT");
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final Map<FieldPath, String> fieldMapping = Map.of(FieldPath.of("PRODUCT"), "PRODUCT");
    ```
  </Tab>

  <Tab title="Synapse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final Map<FieldPath, String> fieldMapping = Map.of(FieldPath.of("PRODUCT"), "PRODUCT");
    ```
  </Tab>
</Tabs>

We will also map the sum of the sales `quantity` column into a column called `quantity_sum`:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final List<MeasureMapping> measureMapping =
        List.of(
            MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
            MeasureMapping.count("CONTRIBUTORS_COUNT"));
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final List<MeasureMapping> measureMapping =
        List.of(
            MeasureMapping.sum(FieldPath.of("QUANTITY"), "quantity_sum"),
            MeasureMapping.count("contributors_count"));
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final List<MeasureMapping> measureMapping =
        List.of(
            MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
            MeasureMapping.count("CONTRIBUTORS_COUNT"));
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final List<MeasureMapping> measureMapping =
        List.of(
            MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
            MeasureMapping.count("CONTRIBUTORS_COUNT"));
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final List<MeasureMapping> measureMapping =
        List.of(
            MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
            MeasureMapping.count("CONTRIBUTORS_COUNT"));
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final List<MeasureMapping> measureMapping =
        List.of(
            MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
            MeasureMapping.count("CONTRIBUTORS_COUNT"));
    ```
  </Tab>

  <Tab title="Synapse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final List<MeasureMapping> measureMapping =
        List.of(
            MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
            MeasureMapping.count("CONTRIBUTORS_COUNT"));
    ```
  </Tab>
</Tabs>

We can now build the `AggregateTable`:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(fieldMapping)
            .withMeasuresMappings(measureMapping)
            .build();
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(fieldMapping)
            .withMeasuresMappings(measureMapping)
            .build();
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(fieldMapping)
            .withMeasuresMappings(measureMapping)
            .build();
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(fieldMapping)
            .withMeasuresMappings(measureMapping)
            .build();
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(fieldMapping)
            .withMeasuresMappings(measureMapping)
            .build();
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .originBaseTableName(salesTable.getName())
            .underlyingTable(table)
            .withGroupByFieldsMapping(fieldMapping)
            .withMeasuresMappings(measureMapping)
            .build();
    ```
  </Tab>

  <Tab title="Synapse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(fieldMapping)
            .withMeasuresMappings(measureMapping)
            .build();
    ```
  </Tab>
</Tabs>

And add it to the schema:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final SchemaDescription schema =
        SchemaDescription.builder()
            .externalTables(List.of(salesTable))
            .externalAggregateTableDescriptions(List.of(aggregateTable))
            .build();
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final SchemaDescription schema =
        SchemaDescription.builder()
            .externalTables(List.of(salesTable))
            .externalAggregateTableDescriptions(List.of(aggregateTable))
            .build();
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final SchemaDescription schema =
        SchemaDescription.builder()
            .externalTables(List.of(salesTable))
            .externalJoins(List.of())
            .externalAggregateTableDescriptions(List.of(aggregateTable))
            .build();
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final SchemaDescription schema =
        SchemaDescription.builder()
            .externalTables(List.of(salesTable))
            .externalAggregateTableDescriptions(List.of(aggregateTable))
            .build();
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final SchemaDescription schema =
        SchemaDescription.builder()
            .externalTables(List.of(salesTable))
            .externalAggregateTableDescriptions(List.of(aggregateTable))
            .build();
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final SchemaDescription schema =
        SchemaDescription.builder()
            .externalTables(List.of(salesTable))
            .externalAggregateTableDescriptions(List.of(aggregateTable))
            .build();
    ```
  </Tab>

  <Tab title="Synapse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final SchemaDescription schema =
        SchemaDescription.builder()
            .externalTables(List.of(salesTable))
            .externalAggregateTableDescriptions(List.of(aggregateTable))
            .build();
    ```
  </Tab>
</Tabs>

Let's now define a bitmap aggregate provider on the sum of quantity per `Product`, the feeding of the in-memory provider will be done directly from the Aggregate Table:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IActivePivotInstanceDescription cubeDescription =
        StartBuilding.cube("MyCube")
            .withMeasures(
                superMeasureBuilder ->
                    superMeasureBuilder.withCalculations(
                        c -> Copper.sum("QUANTITY").as("Quantity.SUM").publish(c)))
            .withDimensions(
                builder -> builder.withSingleLevelDimensions(List.of("DATE", "PRODUCT", "SHOP")))
            .withAggregateProvider()
            .withPartialProvider()
            .withName("MyProvider")
            .bitmap()
            .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
            .includingOnlyMeasures("Quantity.SUM")
            .build();
    final ISelectionDescription selection =
        StartBuilding.selection(schema).fromBaseStore("SALES").withAllReachableFields().build();
    final IActivePivotManagerDescription managerDescription =
        StartBuilding.managerDescription()
            .withName("MyManager")
            .withSchema()
            .withSelection(selection)
            .withCube(cubeDescription)
            .build();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .databaseSettings(BigqueryDatabaseSettings.defaults())
            .build();
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IActivePivotInstanceDescription cubeDescription =
        StartBuilding.cube("MyCube")
            .withMeasures(
                superMeasureBuilder ->
                    superMeasureBuilder.withCalculations(
                        c -> Copper.sum("QUANTITY").as("Quantity.SUM").publish(c)))
            .withDimensions(
                builder -> builder.withSingleLevelDimensions(List.of("DATE", "PRODUCT", "SHOP")))
            .withAggregateProvider()
            .withPartialProvider()
            .withName("MyProvider")
            .bitmap()
            .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
            .includingOnlyMeasures("Quantity.SUM")
            .build();
    final ISelectionDescription selection =
        StartBuilding.selection(schema).fromBaseStore("SALES").withAllReachableFields().build();
    final IActivePivotManagerDescription managerDescription =
        StartBuilding.managerDescription()
            .withName("MyManager")
            .withSchema()
            .withSelection(selection)
            .withCube(cubeDescription)
            .build();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .build();
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IActivePivotInstanceDescription cubeDescription =
        StartBuilding.cube("MyCube")
            .withMeasures(
                superMeasureBuilder ->
                    superMeasureBuilder.withCalculations(
                        c -> Copper.sum("QUANTITY").as("Quantity.SUM").publish(c)))
            .withDimensions(
                builder -> builder.withSingleLevelDimensions(List.of("DATE", "PRODUCT", "SHOP")))
            .withAggregateProvider()
            .withPartialProvider()
            .withName("MyProvider")
            .bitmap()
            .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
            .includingOnlyMeasures("Quantity.SUM")
            .build();
    final ISelectionDescription selection =
        StartBuilding.selection(schema).fromBaseStore("SALES").withAllReachableFields().build();
    final IActivePivotManagerDescription managerDescription =
        StartBuilding.managerDescription()
            .withName("MyManager")
            .withSchema()
            .withSelection(selection)
            .withCube(cubeDescription)
            .build();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .build();
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IActivePivotInstanceDescription cubeDescription =
        StartBuilding.cube("MyCube")
            .withMeasures(
                superMeasureBuilder ->
                    superMeasureBuilder.withCalculations(
                        c -> Copper.sum("QUANTITY").as("Quantity.SUM").publish(c)))
            .withDimensions(
                builder -> builder.withSingleLevelDimensions(List.of("DATE", "PRODUCT", "SHOP")))
            .withAggregateProvider()
            .withPartialProvider()
            .withName("MyProvider")
            .bitmap()
            .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
            .includingOnlyMeasures("Quantity.SUM")
            .build();
    final ISelectionDescription selection =
        StartBuilding.selection(schema).fromBaseStore("SALES").withAllReachableFields().build();
    final IActivePivotManagerDescription managerDescription =
        StartBuilding.managerDescription()
            .withName("MyManager")
            .withSchema()
            .withSelection(selection)
            .withCube(cubeDescription)
            .build();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .build();
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IActivePivotInstanceDescription cubeDescription =
        StartBuilding.cube("MyCube")
            .withMeasures(
                superMeasureBuilder ->
                    superMeasureBuilder.withCalculations(
                        c -> Copper.sum("QUANTITY").as("Quantity.SUM").publish(c)))
            .withDimensions(
                builder -> builder.withSingleLevelDimensions(List.of("DATE", "PRODUCT", "SHOP")))
            .withAggregateProvider()
            .withPartialProvider()
            .withName("MyProvider")
            .bitmap()
            .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
            .includingOnlyMeasures("Quantity.SUM")
            .build();
    final ISelectionDescription selection =
        StartBuilding.selection(schema).fromBaseStore("SALES").withAllReachableFields().build();
    final IActivePivotManagerDescription managerDescription =
        StartBuilding.managerDescription()
            .withName("MyManager")
            .withSchema()
            .withSelection(selection)
            .withCube(cubeDescription)
            .build();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .build();
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IActivePivotInstanceDescription cubeDescription =
        StartBuilding.cube("MyCube")
            .withMeasures(
                superMeasureBuilder ->
                    superMeasureBuilder.withCalculations(
                        c -> Copper.sum("QUANTITY").as("Quantity.SUM").publish(c)))
            .withDimensions(
                builder -> builder.withSingleLevelDimensions(List.of("DATE", "PRODUCT", "SHOP")))
            .withAggregateProvider()
            .withPartialProvider()
            .withName("MyProvider")
            .bitmap()
            .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
            .includingOnlyMeasures("Quantity.SUM")
            .build();
    final ISelectionDescription selection =
        StartBuilding.selection(schema).fromBaseStore("SALES").withAllReachableFields().build();
    final IActivePivotManagerDescription managerDescription =
        StartBuilding.managerDescription()
            .withName("MyManager")
            .withSchema()
            .withSelection(selection)
            .withCube(cubeDescription)
            .build();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .build();
    ```
  </Tab>

  <Tab title="Synapse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IActivePivotInstanceDescription cubeDescription =
        StartBuilding.cube("MyCube")
            .withMeasures(
                superMeasureBuilder ->
                    superMeasureBuilder.withCalculations(
                        c -> Copper.sum("QUANTITY").as("Quantity.SUM").publish(c)))
            .withDimensions(
                builder -> builder.withSingleLevelDimensions(List.of("DATE", "PRODUCT", "SHOP")))
            .withAggregateProvider()
            .withPartialProvider()
            .withName("MyProvider")
            .bitmap()
            .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
            .includingOnlyMeasures("Quantity.SUM")
            .build();
    final ISelectionDescription selection =
        StartBuilding.selection(schema).fromBaseStore("SALES").withAllReachableFields().build();
    final IActivePivotManagerDescription managerDescription =
        StartBuilding.managerDescription()
            .withName("MyManager")
            .withSchema()
            .withSelection(selection)
            .withCube(cubeDescription)
            .build();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .build();
    ```
  </Tab>
</Tabs>

To make sure that the feeding can be done from the Aggregate Table,
we can call a validator that will check that the provider is compatible with the Aggregate Table before starting the application:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    AggregateTableValidator.validateAggregateProvidersUseAggregateTable(
        aggregateTable, managerDescription, List.of(ProviderCoordinate.at("MyCube", "MyProvider")));
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    AggregateTableValidator.validateAggregateProvidersUseAggregateTable(
        aggregateTable, managerDescription, List.of(ProviderCoordinate.at("MyCube", "MyProvider")));
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    AggregateTableValidator.validateAggregateProvidersUseAggregateTable(
        aggregateTable, managerDescription, List.of(ProviderCoordinate.at("MyCube", "MyProvider")));
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    AggregateTableValidator.validateAggregateProvidersUseAggregateTable(
        aggregateTable, managerDescription, List.of(ProviderCoordinate.at("MyCube", "MyProvider")));
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    AggregateTableValidator.validateAggregateProvidersUseAggregateTable(
        aggregateTable, managerDescription, List.of(ProviderCoordinate.at("MyCube", "MyProvider")));
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    AggregateTableValidator.validateAggregateProvidersUseAggregateTable(
        aggregateTable, managerDescription, List.of(ProviderCoordinate.at("MyCube", "MyProvider")));
    ```
  </Tab>

  <Tab title="Synapse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    AggregateTableValidator.validateAggregateProvidersUseAggregateTable(
        aggregateTable, managerDescription, List.of(ProviderCoordinate.at("MyCube", "MyProvider")));
    ```
  </Tab>
</Tabs>

### More complex aggregations such as average

In the previous example, SUM was based on a single input and pre-aggregated into a single column, but there are some more complex cases.
For instance:

* `count` has no input and produces 1 pre-aggregated column
* `sumProduct` can takes N input columns and produces 1 pre-aggregated column
* `average` takes a single input column but requires 2 pre-aggregated column in order to be able to re-aggregate: one column for the sum and one for the count

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(Map.of(FieldPath.of("PRODUCT"), "PRODUCT"))
            .withMeasuresMappings(
                List.of(
                    MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
                    MeasureMapping.avg(
                        FieldPath.of("QUANTITY"), "QUANTITY_SUM", "CONTRIBUTORS_COUNT"),
                    MeasureMapping.count("CONTRIBUTORS_COUNT")))
            .build();
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(Map.of(FieldPath.of("PRODUCT"), "product"))
            .withMeasuresMappings(
                List.of(
                    MeasureMapping.sum(FieldPath.of("QUANTITY"), "quantity_sum"),
                    MeasureMapping.avg(
                        FieldPath.of("QUANTITY"), "quantity_sum", "contributors_count"),
                    MeasureMapping.count("contributors_count")))
            .build();
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(Map.of(FieldPath.of("PRODUCT"), "PRODUCT"))
            .withMeasuresMappings(
                List.of(
                    MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
                    MeasureMapping.avg(
                        FieldPath.of("QUANTITY"), "QUANTITY_SUM", "CONTRIBUTORS_COUNT"),
                    MeasureMapping.count("CONTRIBUTORS_COUNT")))
            .build();
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(Map.of(FieldPath.of("PRODUCT"), "PRODUCT"))
            .withMeasuresMappings(
                List.of(
                    MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
                    MeasureMapping.avg(
                        FieldPath.of("QUANTITY"), "QUANTITY_SUM", "CONTRIBUTORS_COUNT"),
                    MeasureMapping.count("CONTRIBUTORS_COUNT")))
            .build();
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(Map.of(FieldPath.of("PRODUCT"), "PRODUCT"))
            .withMeasuresMappings(
                List.of(
                    MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
                    MeasureMapping.avg(
                        FieldPath.of("QUANTITY"), "QUANTITY_SUM", "CONTRIBUTORS_COUNT"),
                    MeasureMapping.count("CONTRIBUTORS_COUNT")))
            .build();
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(Map.of(FieldPath.of("PRODUCT"), "PRODUCT"))
            .withMeasuresMappings(
                List.of(
                    MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
                    MeasureMapping.avg(
                        FieldPath.of("QUANTITY"), "QUANTITY_SUM", "CONTRIBUTORS_COUNT"),
                    MeasureMapping.count("CONTRIBUTORS_COUNT")))
            .build();
    ```
  </Tab>

  <Tab title="Synapse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable =
        AggregateTableDescription.builder()
            .underlyingTable(table)
            .originBaseTableName(salesTable.getName())
            .withGroupByFieldsMapping(Map.of(FieldPath.of("PRODUCT"), "PRODUCT"))
            .withMeasuresMappings(
                List.of(
                    MeasureMapping.sum(FieldPath.of("QUANTITY"), "QUANTITY_SUM"),
                    MeasureMapping.avg(
                        FieldPath.of("QUANTITY"), "QUANTITY_SUM", "CONTRIBUTORS_COUNT"),
                    MeasureMapping.count("CONTRIBUTORS_COUNT")))
            .build();
    ```
  </Tab>
</Tabs>

### Create a simple Aggregate Table to feed a hierarchy

For an aggregate table to be used to feed a hierarchy, it needs to be defined with all the levels of the hierarchy
as `GroupByFieldsMapping` and the `Count` as `MeasuresMappings`.

In the external database, the table for a hierarchy with the levels (Country, City) would look like:

| Country | City   | Count |
| ------- | ------ | ----- |
| France  | Paris  | 812   |
| Germany | Berlin | 485   |
| ...     |        |       |

## Count

The count is measure automatically added to any query sent to the database.
When adding an aggregate table, a check is therefore performed to ensure it contains a column with a count.
(otherwise it would never be used).

## Slicing hierarchies

Slicing hierarchies are always expressed in the queries sent to the database.
When a field expressed in a query is not part of the Aggregate Table, the query will not use the Aggregate Table.
Therefore it is recommended to include the slicing hierarchies in Aggregate Table definition.

## Aggregate tables with vectors

Arrays can be aggregated in an aggregate table but they need to have a specific format depending on the type of original array being aggregated:

| Original Array     | Aggregate Table Array |
| ------------------ | --------------------- |
| Native             | Native                |
| Multi Column Array | Multi Column Array    |
| Multi Row Array    | **Native**            |

See [the DirectQuery vector documentation](../directquery-vectors) for more information about array types.

## Generate SQL for Aggregate Table matching an Aggregate Provider

Aggregate Tables are a good tool to feed Aggregate Providers quickly because it can use the aggregated data instead of running a new aggregation.
DirectQuery provides a bootstrapper to generate the SQL to create and feed an Aggregate Table matching an Aggregate Provider.

Let's imagine we have the following Aggregate Provider defined:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    .withPartialProvider()
    .withName("MyProvider")
    .bitmap()
    .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
    .includingOnlyMeasures("Quantity.SUM")
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    .withPartialProvider()
    .withName("MyProvider")
    .bitmap()
    .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
    .includingOnlyMeasures("Quantity.SUM")
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    .withPartialProvider()
    .withName("MyProvider")
    .bitmap()
    .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
    .includingOnlyMeasures("Quantity.SUM")
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    .withPartialProvider()
    .withName("MyProvider")
    .bitmap()
    .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
    .includingOnlyMeasures("Quantity.SUM")
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    .withPartialProvider()
    .withName("MyProvider")
    .bitmap()
    .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
    .includingOnlyMeasures("Quantity.SUM")
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    .withPartialProvider()
    .withName("MyProvider")
    .bitmap()
    .includingOnlyHierarchies(HierarchyIdentifier.simple("PRODUCT"))
    .includingOnlyMeasures("Quantity.SUM")
    ```
  </Tab>
</Tabs>

We can create a bootstrapper for it:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final ProviderCoordinate provider = ProviderCoordinate.at("MyCube", "MyProvider");
    final IAggregateTableBootstrapper bootstrapper =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schema)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .targetTableName(aggregateTableName)
            .targetSchemaName(AGGREGATE_TABLE_TEMP_DATASET)
            .build();
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final ProviderCoordinate provider = ProviderCoordinate.at("MyCube", "MyProvider");
    final IAggregateTableBootstrapper bootstrapper =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schema)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .build();
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final ProviderCoordinate provider = ProviderCoordinate.at("MyCube", "MyProvider");
    final IAggregateTableBootstrapper bootstrapper =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schema)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .targetTableName(aggregateTableName)
            .targetSchemaName(AGGREGATE_TABLE_TEMP_SCHEMA_NAME)
            .build();
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final ProviderCoordinate provider = ProviderCoordinate.at("MyCube", "MyProvider");
    final IAggregateTableBootstrapper bootstrapper =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schema)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .build();
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final ProviderCoordinate provider = ProviderCoordinate.at("MyCube", "MyProvider");
    final IAggregateTableBootstrapper bootstrapper =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schema)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .targetTableName(aggregateTableName)
            .targetSchemaName(AGGREGATE_TABLE_TEMP_SCHEMA_NAME)
            .build();
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final ProviderCoordinate provider = ProviderCoordinate.at("MyCube", "MyProvider");
    final IAggregateTableBootstrapper bootstrapper =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schema)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .targetTableName(aggregateTableName)
            .targetCatalogName(TEST_DATABASE_NAME)
            .targetSchemaName(AGGREGATE_TABLE_TEMP_SCHEMA_NAME)
            .build();
    ```
  </Tab>
</Tabs>

This bootstrapper can provide the SQL to create and feed the provider:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForCreation());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    CREATE TABLE `cubulus-tests`.`test_aggregate_table_temp`.`Aggregate_Table_MyProvider` (
      `PRODUCT` String NOT NULL,
      `Quantity__PERIOD__SUM` INT64,
      `contributors__PERIOD__COUNT` INT64
    )
    ```

    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForFeeding());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    INSERT INTO `cubulus-tests`.`test_aggregate_table_temp`.`Aggregate_Table_MyProvider` SELECT
      `T0`.`PRODUCT` AS `PRODUCT_0`,
      SUM(COALESCE(`T0`.`QUANTITY`, 0)) AS `Quantity__PERIOD__SUM_1`,
      COUNT(*) AS `contributors__PERIOD__COUNT_2`
    FROM
      `cubulus-tests`.`tutorial`.`SALES` AS `T0`
    GROUP BY
      `T0`.`PRODUCT`
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForCreation());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    CREATE TABLE `TUTORIAL`.`Aggregate_Table_MyProvider` (
      `PRODUCT` String,
      `Quantity.SUM` Nullable(Float64),
      `contributors.COUNT` Nullable(Int64)
    ) ENGINE = MergeTree()
    ORDER BY
      `PRODUCT`
    ```

    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForFeeding());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    INSERT INTO `TUTORIAL`.`Aggregate_Table_MyProvider` SELECT
      `T0`.`PRODUCT` AS `PRODUCT_0`,
      SUM(COALESCE(`T0`.`QUANTITY`, 0.0)) AS `Quantity.SUM_1`,
      COUNT(*) AS `contributors.COUNT_2`
    FROM
      `TUTORIAL`.`SALES` AS `T0`
    GROUP BY
      `T0`.`PRODUCT`
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForCreation());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    CREATE TABLE `unity_catalog_directquery_tests`.`aggregate_table_temp_schema`.`Aggregate_Table_MyProvider` (
      `PRODUCT` String NOT NULL,
      `Quantity.SUM` DOUBLE,
      `contributors.COUNT` BIGINT
    )
    ```

    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForFeeding());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    INSERT INTO `unity_catalog_directquery_tests`.`aggregate_table_temp_schema`.`Aggregate_Table_MyProvider` SELECT
      `T0`.`PRODUCT` AS `PRODUCT_0`,
      SUM(`T0`.`QUANTITY`) AS `Quantity.SUM_1`,
      COUNT(*) AS `contributors.COUNT_2`
    FROM
      `unity_catalog_directquery_tests`.`tutorial`.`SALES` AS `T0`
    GROUP BY
      `T0`.`PRODUCT`
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForCreation());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    CREATE TABLE "dev"."TUTORIAL"."Aggregate_Table_MyProvider" (
      "PRODUCT" nvarchar(4000) NOT NULL,
      "Quantity.SUM" BigInt,
      "contributors.COUNT" BigInt
    )
    ```

    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForFeeding());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    INSERT INTO "dev"."TUTORIAL"."Aggregate_Table_MyProvider" SELECT
      "T0"."PRODUCT" AS "PRODUCT_0",
      SUM("T0"."QUANTITY") AS "Quantity.SUM_1",
      COUNT(*) AS "contributors.COUNT_2"
    FROM
      "dev"."TUTORIAL"."SALES" AS "T0"
    GROUP BY
      "T0"."PRODUCT"
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForCreation());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    SET
      enable_case_sensitive_identifier TO true;
    CREATE TABLE "dev"."test_aggregate_table_temp"."Aggregate_Table_MyProvider" (
      "PRODUCT" VARCHAR NOT NULL,
      "Quantity.SUM" FLOAT8,
      "contributors.COUNT" BigInt
    )
    ```

    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForFeeding());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    SET enable_case_sensitive_identifier TO true;
    INSERT INTO "dev"."test_aggregate_table_temp"."Aggregate_Table_MyProvider" SELECT
      "T0"."PRODUCT" AS "PRODUCT_0",
      SUM(COALESCE("T0"."QUANTITY", 0.0)) AS "Quantity.SUM_1",
      COUNT(*) AS "contributors.COUNT_2"
    FROM
      "dev"."tutorial"."SALES" AS "T0"
    GROUP BY
      "T0"."PRODUCT"
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForCreation());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    CREATE TRANSIENT TABLE "TEST_RESOURCES"."TEST_AGGREGATE_TABLE_TEMP"."Aggregate_Table_MyProvider" (
      "PRODUCT" VARCHAR NOT NULL,
      "Quantity.SUM" DOUBLE,
      "contributors.COUNT" NUMBER
    )
    ```

    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    System.out.println(bootstrapper.getSqlForFeeding());
    ```

    ```sql theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    INSERT INTO "TEST_RESOURCES"."TEST_AGGREGATE_TABLE_TEMP"."Aggregate_Table_MyProvider" SELECT
      "T0"."PRODUCT" AS "PRODUCT_0",
      SUM(COALESCE("T0"."QUANTITY", 0.0)) AS "Quantity.SUM_1",
      COUNT(*) AS "contributors.COUNT_2"
    FROM
      "TEST_RESOURCES"."TUTORIAL"."SALES" AS "T0"
    GROUP BY
      "T0"."PRODUCT"
    ```
  </Tab>
</Tabs>

You will need to execute this SQL to create and feed the table.
Creating and feeding the SQL table only need to be done once: remember that the purpose of using an Aggregate Table is to avoid re-running the same queries at every start up.
A good way to do that is to run the queries manually.

When the table exists, it is possible to use the Aggregate Table in the application, the Aggregate Provider will use it for feeding:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable = bootstrapper.toAggregateTable();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .additionalAggregateTables(List.of(aggregateTable))
            .build();
    app.start();
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable = bootstrapper.toAggregateTable();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .additionalAggregateTables(List.of(aggregateTable))
            .build();
    app.start();
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable = bootstrapper.toAggregateTable();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .additionalAggregateTables(List.of(aggregateTable))
            .build();
    app.start();
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable = bootstrapper.toAggregateTable();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .additionalAggregateTables(List.of(aggregateTable))
            .build();
    app.start();
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable = bootstrapper.toAggregateTable();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .additionalAggregateTables(List.of(aggregateTable))
            .build();
    app.start();
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final AggregateTableDescription aggregateTable = bootstrapper.toAggregateTable();
    final Application app =
        Application.builder(connector)
            .schema(schema)
            .managerDescription(managerDescription)
            .additionalAggregateTables(List.of(aggregateTable))
            .build();
    app.start();
    ```
  </Tab>
</Tabs>

### Preserving joins with joinedTableNames

By default, the bootstrapper denormalizes all fields from joined tables into the aggregate table as group-by columns. However, when dealing with dimension tables with many columns, this denormalization can make the aggregate table unnecessarily large.

The `joinToKeepNormalized` option allows specifying tables whose fields should remain accessible through joins at query time instead of being denormalized. This keeps the aggregate table smaller while still allowing queries to reference fields from those tables.

When using `joinToKeepNormalized`:

* Fields accessible through the specified join are not included as group-by columns in the aggregate table
* Join key fields (source fields of the join leading to those tables) are automatically added as group-by columns
* At query time, the aggregate table can still answer queries involving fields from joined tables by performing joins
* This reduces the number of denormalized columns while maintaining query compatibility

To use this feature, call the `joinToKeepNormalized` method on the bootstrapper builder with the source table and the join name:

<Tabs>
  <Tab title="BigQuery">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IAggregateTableBootstrapper bootstrapperWithJoinToKeepNormalized =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schemaWithJoin)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .targetTableName("AGG_SALES")
            .joinToKeepNormalized("SALES", "SALES_TO_PRODUCTS")
            .build();
    ```
  </Tab>

  <Tab title="ClickHouse">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IAggregateTableBootstrapper bootstrapperWithJoinToKeepNormalized =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schemaWithJoin)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .targetTableName("AGG_SALES")
            .joinToKeepNormalized("SALES", "SALES_TO_PRODUCTS")
            .build();
    ```
  </Tab>

  <Tab title="Databricks">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IAggregateTableBootstrapper bootstrapperWithJoinToKeepNormalized =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schemaWithJoin)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .targetTableName("AGG_SALES")
            .joinToKeepNormalized("SALES", "SALES_TO_PRODUCTS")
            .build();
    ```
  </Tab>

  <Tab title="MsSql">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IAggregateTableBootstrapper bootstrapperWithJoinToKeepNormalized =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schemaWithJoin)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .targetTableName("AGG_SALES")
            .joinToKeepNormalized("SALES", "SALES_TO_PRODUCTS")
            .build();
    ```
  </Tab>

  <Tab title="Redshift">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IAggregateTableBootstrapper bootstrapperWithJoinToKeepNormalized =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schemaWithJoin)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .targetTableName("AGG_SALES")
            .joinToKeepNormalized("SALES", "SALES_TO_PRODUCTS")
            .build();
    ```
  </Tab>

  <Tab title="Snowflake">
    ```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
    final IAggregateTableBootstrapper bootstrapperWithJoinToKeepNormalized =
        IAggregateTableBootstrapper.builder()
            .connector(connector)
            .schema(schemaWithJoin)
            .providerCoordinate(provider)
            .managerDescription(managerDescription)
            .targetTableName("AGG_SALES")
            .joinToKeepNormalized("SALES", "SALES_TO_PRODUCTS")
            .build();
    ```
  </Tab>
</Tabs>

In this example, fields accessible through the `SALES_TO_PRODUCTS` join (starting from `SALES`) will not be denormalized into the aggregate table. Instead, queries referencing these fields will use joins to access them.

Note that preserving joins comes with a trade-off: queries referencing fields from joined tables will require the join to be performed at query time, which most of the time increases compute costs and result in slower responses.

<Info>
  ### Join key fields and join chains

  Consider the following schema:

  ```
  Sales (base table)
    └── [join on STORE_ID] ──→ Stores
                                  └── [join] ──→ Regions
  ```

  By including `STORE_ID` from `Sales` as a group-by field in the aggregate table, queries can access fields from `Stores` and, by transitivity, from `Regions` as well — without denormalizing those fields into the aggregate table.
</Info>

Use this feature when:

* Dimension tables have many columns that would bloat the aggregate table
* Reducing storage footprint and improving aggregate table creation time is a priority

### Database specific features

Creating a view for an Aggregate Table is not a solution as the view must be recomputed for each query.
Creating a table requires to update it when the data changes.
Some databases offers native implementations to automatically create and materialize the content of a view (so it is fast to access) while automatically updating it (so it is always up-to-date).

<Tabs>
  <Tab title="Snowflake">
    In Snowflake there is a powerful feature called [Dynamic tables](https://docs.snowflake.com/en/user-guide/dynamic-tables-about).
    As these Dynamic tables also support time travel it is the recommended way to implement an Aggregate Table in Snowflake.
  </Tab>
</Tabs>

## Is my query using the Aggregate Table?

The simplest solution to check whether the query is using an aggregate table is to look at the query in the external database. If that is the case, then the aggregate table name should appear in the SQL query as the base table (the first table in the FROM clause).
If it using an aggregate table, the aggregate table name should appears as the base table (the first table in FROM clause) in the SQL query.

Additionally, for databases supporting tags such as Snowflake, the tag `aggregate_table_name` will be added to the query with the name of the aggregate table used.

While building a project, to have more information about why a query is compatible or not with aggregate tables, it is possible to add additional logs by setting the logger `atoti.server.directquery.query_resolution.aggregate_table` (see [Logger naming](./monitoring)) to `FINE`.
The additional logs look like this:

```
FINE: Trying to match query with 1 aggregate tables. Query is [...]
Aug 21, 2023 4:46:37 PM atoti.server.directquery.query_resolution.aggregate_table isCompatibleAggregateTable
FINE: The query did not match the Aggregate Table AggregateTable-0. Compatible base table: true, compatible group by fields: true, compatible selection fields: true, compatible aggregations: true, compatible condition: false. Query was [...]
Aug 21, 2023 4:46:37 PM atoti.server.directquery.query_resolution.aggregate_table findMatchingAggregateTable
FINE: No matching Aggregate Table for query [...]
```