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

# Google BigQuery table gem

> Process BigQuery tables in the SQL warehouse

export const execution_engine_0 = "the SQL warehouse"

<Info>This gem runs in {execution_engine_0}.</Info>

## Overview

In Prophecy, datasets stored in the [SQL Warehouse Connection](/data-analysis/environment/fabrics/prophecy-fabrics) defined in your fabric are accessed using Table gems. Unlike Source and Target gems, Table gems run directly within the data warehouse, eliminating extra orchestration steps and improving performance.

Available configurations for Table gems vary based on your SQL warehouse provider. This page explains how to use the Table gem for a BigQuery SQL warehouse, including supported table types, configuration options, and guidance for managing BigQuery tables in your Prophecy pipelines.

## Table types

The following table types are supported for BigQuery connections.

| Name  | Description                                                                                                   | Type             |
| ----- | ------------------------------------------------------------------------------------------------------------- | ---------------- |
| Table | Persistent storage of structured data in your SQL warehouse. Faster for frequent queries (indexed).           | Source or Target |
| View  | A virtual table that derives data dynamically from a query. Slower for complex queries (computed at runtime). | Source or Target |
| Seed  | Small CSV-format files that you can write directly in Prophecy.                                               | **Source only**  |

## Create a new table

Once you create a Table gem, you can reuse the table throughout your project. All created tables appear in the [Project](/data-analysis/development/studio/studio) tab in the left sidebar.

To create a table in your pipeline:

<Steps>
  <Step title="Add a table gem to the pipeline">
    1. Open your pipeline in the [Studio](/data-analysis/development/studio/studio).
    2. Click on **Source/Target** in the canvas.
    3. Select **Table** from the dropdown.
    4. Click on the gem to open the configuration.
  </Step>

  <Step title="Create a new table">
    Click **+ New Table**. The gem configuration will depend on the table type:

    * [Tables](#tables)
    * [Views](#views)
    * [Seeds](#seeds)
  </Step>
</Steps>

## Gem configuration

### Tables

Tables are persistent, indexed storage objects optimized for frequent access.

#### Source parameters

| Parameter  | Description                                                    |
| ---------- | -------------------------------------------------------------- |
| Location   | Specify the table's location using database, schema, and name. |
| Properties | Define or infer schema. Add a description if needed.           |
| Preview    | Load a sample of the data before saving.                       |

<Info>
  If your BigQuery tables are partitioned or clustered, Prophecy automatically displays the
  partitioning and clustering information in the Properties tab. You'll be able to view information
  such as the partition granularity, partitioning data type, clustering columns, and more. You won't
  be able to edit these properties.
</Info>

#### JSON columns

Prophecy supports BigQuery `JSON` columns, including schema inference, nested field exploration, and reading and writing JSON data.

##### Infer schema

For tables that contain `JSON` columns, you can use **Infer Schema** to sample data and discover the nested structure stored within the column. The inferred schema is displayed as an expandable tree in the Properties tab, similar to Snowflake `VARIANT` columns.

For example, a JSON column containing:

```json theme={null}
{
  "amount": 100,
  "user": {
    "id": 123,
    "plan": "premium"
  }
}
```

is displayed as nested fields that can be expanded and referenced throughout your pipeline.

#### Access nested fields

After schema inference, you can reference nested JSON fields in gems such as Reformat, Filter, and Join using dot notation.

For example:

```text theme={null}
payload.user.plan
```

Prophecy automatically generates the appropriate BigQuery JSON functions:

| Field type        | Generated SQL                                      |
| ----------------- | -------------------------------------------------- |
| String values     | `JSON_VALUE(payload, '$.user.plan')`               |
| Numeric values    | `CAST(JSON_VALUE(payload, '$.amount') AS FLOAT64)` |
| Boolean values    | `CAST(JSON_VALUE(payload, '$.active') AS BOOL)`    |
| Objects or arrays | `JSON_QUERY(payload, '$.user')`                    |

#### Target parameters

| Parameter     | Description                                                                                                                                                                            |
| ------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Location      | Choose the location where the table will be stored. You can create a new table by writing a new table name.                                                                            |
| Properties    | Define certain properties of the table. The schema cannot be changed for targets.                                                                                                      |
| Write Options | Select how you want the data to be written each time you run the pipeline (Table only). Learn more in [Write strategies](/data-analysis/gems/source-target/table/write/write-options). |

#### Write JSON data

When writing to BigQuery `JSON` columns, Prophecy automatically converts string representations of JSON into native BigQuery JSON values. No manual SQL transformations are required.

<Info>
  BigQuery JSON columns support schema inference and nested field access similar to Snowflake
  `VARIANT` columns.
</Info>

### Views

Views are virtual tables recomputed at runtime from a query.

#### Source parameters

| Parameter  | Description                                          |
| ---------- | ---------------------------------------------------- |
| Location   | Enter the database, schema, and table (view) name.   |
| Properties | Define or infer schema. Add a description if needed. |
| Preview    | Load data based on the view's underlying query.      |

#### Target parameters

| Parameter  | Description                                                                       |
| ---------- | --------------------------------------------------------------------------------- |
| Location   | Define the name of the view to be created or replaced.                            |
| Properties | Define certain properties of the table. The schema cannot be changed for targets. |
| Preview    | Load a preview of the resulting view.                                             |

<Note>
  Every time the pipeline runs, the target is overwritten. This is because the view is recomputed
  from scratch based on the underlying logic, and any previously materialized results are discarded.
  No additional write modes are supported.
</Note>

### Seeds

Seeds are lightweight CSV datasets defined in your project. Seeds are source-only.

| Parameter  | Description                                                                                                            |
| ---------- | ---------------------------------------------------------------------------------------------------------------------- |
| Properties | Copy-paste your CSV data and define certain [properties](https://docs.getdbt.com/reference/seed-configs) of the table. |
| Preview    | Load a preview of your seed in table format.                                                                           |

<Note>
  Seeds are implemented as [dbt seeds](https://docs.getdbt.com/docs/build/seeds) under the hood. The
  CSV data you define is stored in your Prophecy project files and materialized as a table in your
  data warehouse. This table is created in the [default target
  dataset](/data-analysis/environment/connections/bigquery#connection-parameters) specified in your
  BigQuery connection.
</Note>

## Reusing and sharing tables

After you create a table in Prophecy, you can reuse its configuration across your entire project. All created tables appear in the [Project](/data-analysis/development/studio/studio) tab in the left sidebar. To make tables available to other teams, you can share your project as a package in the [Package Hub](/data-analysis/development/extensibility/package-hub/package-hub). Other users will be able to use the shared table configuration, provided they have the necessary permissions in BigQuery to access the underlying data.