> ## 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.

# Read from BigQuery tables

> Configure a BigQuery table, view, or seed as a read source

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.

This page explains how to read from a BigQuery table, view, or seed using the Table gem. To write to a BigQuery table or view instead, see [Write to BigQuery tables](/data-analysis/gems/source-target/table/bigquery-write).

## 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**  |

## Configure table

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

To read from 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="Select a table">
    Choose the table, view, or seed you want to read from the list.
  </Step>

  <Step title="Select type & format">
    Confirm the type: **Table**, **View**, or **Seed**.

    <Note>
      Seeds configure differently from Tables and Views — they skip the Location step entirely. See
      [Configure seeds](#configure-seeds) below.
    </Note>
  </Step>

  <Step title="Configure location">
    The Location tab defines where a table lives and how it is identified within your project.

    | Field       | Description                                                                                                                                                                                         |
    | ----------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
    | Table alias | A stable, logical identifier for the table that stays constant even if the underlying database, schema, or table name changes. Required when you create the table, and cannot be changed afterward. |
    | Database    | The database containing the table.                                                                                                                                                                  |
    | Schema      | The schema containing the table.                                                                                                                                                                    |
    | Table       | The table name.                                                                                                                                                                                     |

    A **parameter set** selector appears in the upper-right corner of the Location tab:

    * If the table gem is used inside a pipeline, the selector shows that pipeline's active parameter set automatically.
    * If you're working on the table outside of a pipeline (for example, from the Project browser), the selector shows **Select Pipeline and Parameter set** until you choose one. You only need to do this if one or more Location fields are set to Advanced mode (see below) and you need their values resolved outside pipeline context.

    At the bottom of the Location tab, Prophecy shows a live preview of the resolved `database.schema.table` location: the hardcoded values if all fields are in Simple mode, or the resolved parameter values if any field is in Advanced mode. If a value can't be resolved yet — for example, because no parameter set is selected — Prophecy displays the raw value instead.

    <Accordion title="Make a location field dynamic">
      #### Make a location field dynamic

      In the default **Simple** mode, each Location field (database, schema, table) takes a fixed value that you type directly.

      Switch a field to **Advanced** mode to bind it to a project or pipeline parameter instead of a fixed value.

      <Note>
        Only parameters of type `sql_expression` can be used in Advanced mode. Using a parameter of a different type will cause the table location to fail to resolve.
      </Note>

      <Warning>
        Switching a field from Advanced back to Simple mode clears its current value.
      </Warning>

      Once a field is in Advanced mode, its value depends on which parameter set is active (see the parameter set selector above). This makes it possible to define a table once and reuse it across multiple pipelines, each supplying different values for the parameterized fields via their own parameter sets. To reuse a table you've already created, select it from **Table > \[alias]** in the Project browser.

      <Warning>
        Prophecy does not validate that tables resolved from different parameter sets share the same schema. If your parameter sets point to tables with different schemas, downstream steps in your pipeline may fail or behave unexpectedly.
      </Warning>
    </Accordion>
  </Step>

  <Step title="Configure properties">
    Define or infer the schema. Add a description if needed.

    <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>

    #### Working with JSON columns

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

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

    ##### Infer schema

    For tables that contain `JSON` columns, 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')`                    |

    <Note>
      Writing to a JSON column has different behavior — Prophecy auto-converts string JSON to native
      BigQuery JSON values on write. See [Write to BigQuery tables](/data-analysis/gems/source-target/table/bigquery-write)
      for details.
    </Note>
  </Step>

  <Step title="Preview">
    Load a sample of the data before saving. For views, this loads data based on the view's underlying query.
  </Step>

  <Step title="Data tests">
    Add data tests to validate the data you're reading. See [Table tests vs. project tests](/data-analysis/development/tests/test-comparison) to decide which approach fits your validation needs.
  </Step>
</Steps>

## Configure seeds

Seeds are lightweight CSV datasets defined in your project. Seeds are source-only and don't support writing.

| 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.