Skip to main content
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 write to a BigQuery table or view using the Table gem. To read from an existing BigQuery table or view instead, see Read from BigQuery tables.

​
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 write to a table in your pipeline:
1

Add a table gem to the pipeline

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

Choose or create a table

To write to an existing table, select it from the list.To write to a new table, click + New Table.
3

Select type & format

Choose Table or View.
4

Configure location

The Location tab defines where a table lives and how it is identified within your project.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.
5

Configure properties

Map each incoming column to a column on the target table. Prophecy suggests mappings automatically — review and adjust them as needed. Any target column left unmapped defaults to null.
This is schema mapping, not schema definition or inference. Prophecy already knows the target’s schema (from an existing table, or as you define one for a new table in the Location step); this step is about reconciling your pipeline’s output columns against it.
You can also set a description for the table and configure generic options, such as skipping execution when the input has zero rows.

​
Working with JSON columns

Prophecy supports BigQuery JSON columns, including schema inference, nested field exploration, and reading and writing JSON data.When writing to a BigQuery JSON column, Prophecy automatically converts string representations of JSON into native BigQuery JSON values — no manual SQL transformation is required.
BigQuery JSON columns support schema inference and nested field access similar to Snowflake VARIANT columns.
6

Configure write options

​
Map schema for existing target tables

When you select an existing table as a target, the incoming schema might not match the schema of the target table. Prophecy lets you reconcile these differences in the Map Schema section of the Properties tab.For each target column, select the corresponding source column. Prophecy can suggest mappings for unmapped columns, which you can review and select. Any required casts or other transformations are applied so that the incoming data conforms to the target schema.If you want the target table to use the incoming schema instead, click Overwrite Target Schema. This replaces the existing target schema with the source schema rather than mapping the incoming columns to it.
Schema mapping is available when writing to existing target tables in Snowflake, Databricks, and BigQuery.
Select how you want the data to be written each time you run the pipeline. Learn more in Write strategies.
This step applies to Table targets only. If you selected View in the type & format step, there’s no Write Options tab — the gem shows Preview instead, even when it’s positioned at the end of a pipeline. Views are always fully recomputed and overwritten on each run, so there’s no write mode to choose.
7

Data tests

Add data tests to validate the table after it’s written. See Table tests vs. project tests to decide which approach fits your validation needs.

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