Read from BigQuery tables

This gem runs in .

Overview

In Prophecy, datasets stored in the SQL Warehouse Connection 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.

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 tab in the left sidebar. To read from a table in your pipeline:

Add a table gem to the pipeline

Open your pipeline in the Studio.
Click on Source/Target in the canvas.
Select Table from the dropdown.
Click on the gem to open the configuration.

Select a table

Choose the table, view, or seed you want to read from the list.

Select type & format

Confirm the type: Table, View, or Seed.

Seeds configure differently from Tables and Views — they skip the Location step entirely. See Configure seeds below.

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.

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.

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.

Switching a field from Advanced back to Simple mode clears its current value.

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.

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.

Configure properties

Define or infer the schema. Add a description if needed.

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.

Working with JSON columns

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

BigQuery JSON columns support schema inference and nested field access similar to Snowflake VARIANT columns.

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:

{
  "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:

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')`

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

Preview

Load a sample of the data before saving. For views, this loads data based on the view’s underlying query.

Data tests

Add data tests to validate the data you’re reading. See Table tests vs. project tests to decide which approach fits your validation needs.

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 of the table.
Preview	Load a preview of your seed in table format.

Seeds are implemented as dbt 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 specified in your BigQuery connection.

After you create a table in Prophecy, you can reuse its configuration across your entire project. All created tables appear in the Project tab in the left sidebar. To make tables available to other teams, you can share your project as a package in the 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.

Overview

Sources and targets

Data processing

Reporting

Advanced

Overview

Table types

Configure table

Make a location field dynamic

Working with JSON columns

Infer schema

Access nested fields

Configure seeds

​Overview

​Table types

​Configure table

​Make a location field dynamic

​Working with JSON columns

Infer schema

Access nested fields

​Configure seeds

​Reusing and sharing tables

Overview

Table types

Configure table

Make a location field dynamic

Working with JSON columns

Configure seeds

Reusing and sharing tables