Temporary tables and persistent tables help you streamline
queries, making them easier to understand and maintain. By creating intermediate
results that you can reuse, while giving you the option to keep the temporary
data unaggregated, you can reduce the resources required to execute queries,
improving performance.

Created tables are subject to the same static
[privacy checks](https://developers.google.com/ads-data-hub/guides/privacy-checks),
[allowed functions](https://developers.google.com/ads-data-hub/reference/allowed_functions),
and
[field join limitations](https://developers.google.com/ads-data-hub/reference/table-schemas/field-joins)
enforced throughout Ads Data Hub. Standard privacy checks apply when the data
from the final `SELECT` statement is output to BigQuery.

To create tables, follow the BigQuery syntax:

    CREATE [OR REPLACE] [TEMP | TEMPORARY] TABLE TABLE_NAME
     [OPTIONS(privacy_checked_export=<true | false>)] AS query_statement;

These clauses are not supported:

- `IF NOT EXISTS`
- `PARTITION BY`
- `CLUSTER BY`

## Temporary tables

**Temporary** (or **temp** ) **tables** improve query readability, and allow you
to create intermediate results that may include unaggregated data.

Temp tables:

- Only exist at the session level, and expire after query execution
- Can only be used inside of the query in which it was created
- May store unaggregated data that has not yet applied privacy checks
- Are referenced by name without any required namespace

To create a temp table, use the `CREATE TEMP TABLE` statement. This example
creates a temp table to store the results of a query, then uses the temp table
in a subquery:

    -- Get a list of creative IDs and store in a temporary table called creative_list:
    CREATE TEMP TABLE creative_list AS (
      SELECT
        adgroup_id,
        advertiser_id,
        creative_id
      FROM
        adh.dv360_youtube_impressions
    );
    -- Return creatives with a count of impressions greater than 100
    SELECT
      creative_id,
      COUNT(*) AS imps
    FROM
      creative_list
    WHERE
      imps > 100
    GROUP BY
      creative_id;

## Persistent tables

If you need to create an intermediate table to use in other queries, you can
create a **persistent table** . The syntax is the same as for temp tables,
without the `TEMP` clause. These tables last for 72 hours. If a table with the
selected name already exists, it is overwritten.

Persistent tables may store unaggregated data, but the contents of the table are
not visible except through privacy-checked query results.

Persistent tables:

- Expire after 72 hours
- Can be used outside of the query that created it
- May store unaggregated data that has not yet applied privacy checks
- Are referenced through the `tmp` namespace, but can also be referenced by name in the same query

To create a persistent table, use the `CREATE TABLE` statement. This example
creates a persistent table to store the results of a query, then uses the
persistent table in a later query:

Query 1

    -- Get a list of creative IDs and store in a persistent table called creative_list:
    CREATE TABLE creative_list AS (
      SELECT
        adgroup_id,
        advertiser_id,
        creative_id
      FROM
        adh.dv360_youtube_impressions
    );

    -- Return the total count of impressions in the table
    SELECT
      COUNT(*) AS imps
    FROM
      tmp.creative_list -- Alternative: creative_list

Query 2

    -- Return creatives which had more than 100 impressions
    SELECT
      creative_id,
      COUNT(*) AS imps
    FROM
      tmp.creative_list
    WHERE
      imps > 100
    GROUP BY
      creative_id;

> [!NOTE]
> **Note:** When you [run a query](https://developers.google.com/ads-data-hub/marketers/guides/run-queries#run_a_query) that consists of only `CREATE TABLE` statements, you still need to enter a destination table. The destination table is ignored during execution.

## Apply privacy checks

Ads Data Hub also supports a SQL syntax to create intermediate result tables
that are subject to privacy checks.

To create a table that is subject to privacy checks, add the `OPTIONS` clause to
your query:

    OPTIONS(privacy_checked_export=true)

This example creates a session-level temp table and applies privacy checks:

    -- Get a list of creative IDs and store in a temporary table called creative_list:
    CREATE TEMP TABLE creative_list OPTIONS(privacy_checked_export=true) AS (
      SELECT
        adgroup_id,
        advertiser_id,
        creative_id
      FROM
        adh.dv360_youtube_impressions
    );

This example creates a persistent table, applies privacy checks, and exports it
to your Google Cloud project:

    -- Get a list of creative IDs and store in a persistent table called creative_list:
    CREATE TABLE project_name.dataset_name.creative_list` OPTIONS(privacy_checked_export=true) AS (
      SELECT
        adgroup_id,
        advertiser_id,
        creative_id
      FROM
        adh.dv360_youtube_impressions
    );

## Export multiple tables using a single query

Ads Data Hub supports a flexible SQL syntax to export viewable result
tables in cases where one table per query is not enough. These tables are
subject to the same [privacy checks](https://developers.google.com/ads-data-hub/marketers/guides/privacy-checks)
enforced throughout Ads Data Hub.

The syntax for exporting a table alongside the main SQL expression is:

    CREATE TABLE PROJECT_NAME.DATASET.TABLE_NAME
      OPTIONS(privacy_checked_export=true) AS query_statement;

> [!NOTE]
> **Note:** The `CREATE TABLE` syntax without `OPTIONS(privacy_checked_export=true)` creates a non-viewable [persistent table](https://developers.google.com/ads-data-hub/marketers/guides/create-tables#persistent_tables) in the `tmp.*` namespace which is deleted after 72 hours (this time is subject to change).

For example, to export a table to the BigQuery destination
`PROJECT_NAME.DATASET.TABLE_NAME` which contains a count of
rows for each campaign ID from the table `adh.google_ads_impressions`:

    CREATE TABLE PROJECT_NAME.DATASET.TABLE_NAME
      OPTIONS(privacy_checked_export=true) AS
      SELECT campaign_id, COUNT(*) AS ct
      FROM adh.google_ads_impressions
      GROUP BY campaign_id;

The following example expands on this idea and uses the same query to export two
tables to BigQuery:

    CREATE TABLE PROJECT_NAME.DATASET.TABLE_NAME
      OPTIONS(privacy_checked_export=true) AS
      SELECT campaign_id, COUNT(*) AS ct
      FROM adh.google_ads_impressions
      GROUP BY campaign_id;

    CREATE TABLE PROJECT_NAME.DATASET.TABLE_NAME_2
      OPTIONS(privacy_checked_export=true) AS
      SELECT advertiser_id, COUNT(*) AS ct
      FROM adh.google_ads_impressions
      GROUP BY advertiser_id;

Tables may also be referenced in the same query in which they were created.
For example, the following query outputs two tables:

    CREATE TABLE PROJECT_NAME.DATASET.TABLE_NAME
      OPTIONS(privacy_checked_export=true) AS
      SELECT campaign_id, COUNT(*) AS ct
      FROM adh.google_ads_impressions
      GROUP BY campaign_id;

    SELECT ct FROM PROJECT_NAME.DATASET.TABLE_NAME;

- The first table is created at `PROJECT_NAME.DATASET.TABLE_NAME`,with the columns `campaign_id` and `ct`.
- The second table is created at the location specified in the **[Destination
  table](https://developers.google.com/%20ads-data-hub/marketers/guides/run-queries)** field of the job arguments, with the column `ct`.

**Naming multiple tables at runtime**

When creating multiple tables in a query, there are two ways to name them:

- Explicitly name tables in the SQL using the following format: `PROJECT_NAME.DATASET.TABLE_NAME`.
- Use parameters as placeholders in the SQL, and then name the tables at runtime.

## Create and update BigQuery tables

Ads Data Hub supports direct usage of certain BigQuery statements in scripts,
making it possible to insert rows into an existing result table rather than
generate a new one for every job. These statements must reference a direct table
path in your BigQuery project, and cannot directly access Ads Data Hub tables or
options (such as `privacy_checked_export`).

Supported statements:

- [`CREATE TABLE`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_statement)
- [`MERGE`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)

Example query:

    -- Compute new data for the current job. This table can be used with MERGE.
    CREATE TEMP TABLE new_data OPTIONS(privacy_checked_export=TRUE) AS
    SELECT
      DATE(event_time, @time_zone) AS event_date,
      <more grouping keys>,
      COUNT(*) AS impressions,
      <more metrics>
    FROM ...
    GROUP BY ALL;

    -- Initialize a result table on the first run of the query, which may include
    -- date partitioning and/or clustering for efficiency.
    CREATE TABLE IF NOT EXISTS PROJECT_NAME.DATASET.TABLE_NAME (<columns>)
    PARTITION BY event_date CLUSTER BY ...
    OPTIONS(partition_expiration_days=365);

    -- Atomically insert new results or update existing ones.
    MERGE PROJECT_NAME.DATASET.TABLE_NAME a
    USING new_data b ON a.data_date = b.data_date AND <more grouping keys>
    WHEN MATCHED THEN
      UPDATE SET a.impressions = b.impressions, <more metrics>
    WHEN NOT MATCHED THEN
      INSERT (<all columns>) VALUES(<all columns>);

Note that `MERGE` statements don't support modifying table schema. If you need
to add or change columns in an existing report table, you can either update the
table schema in BigQuery before running a job in Ads Data Hub, or run an Ads
Data Hub job to copy the table with updated schema and options, for example:

    CREATE OR REPLACE TABLE PROJECT_NAME.DATASET.TABLE_NAME
    PARTITION BY event_date CLUSTER BY ... OPTIONS(...) AS
    SELECT
      <new columns>
    FROM PROJECT_NAME.DATASET.TABLE_NAME;