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

# Write to Databricks tables

> Configure a Databricks table or view as a write target

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 write to a Databricks table or view using the Table gem. To read from an existing Databricks table or view instead, see [Read from Databricks tables](/data-analysis/gems/source-target/table/write/write-options).

## 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 write to 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="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**.
  </Step>

  <Step title="Select type & format">
    Choose **Table** or **View**.

    <Note>
      Seeds are read-only and aren't available as a write target.
    </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">
    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.

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

    You can also set a description for the table and configure generic options, such as skipping execution when the input has zero rows.
  </Step>

  <Step title="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.

    <Info>
      Schema mapping is available when writing to existing target tables in Snowflake,  Databricks, and BigQuery.
    </Info>

    Select how you want the data to be written each time you run the pipeline. Learn more in [Write strategies](/data-analysis/gems/source-target/table/write/write-options).

    <Note>
      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.
    </Note>
  </Step>

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

## Cross-workspace access

If your fabric uses Databricks as the SQL warehouse, you can't select Databricks in an external Source or Target gem. Instead, you must use Table gems, which are limited to the Databricks warehouse defined in the SQL warehouse connection.

To work with tables from a different Databricks workspace, use [Delta Sharing](https://docs.databricks.com/aws/en/delta-sharing/). Delta Sharing lets you access data across workspaces without creating additional Databricks connections.

<Info>
  Prophecy implements this guardrail to avoid using external connections when the data can be made available in your warehouse. External connections introduce an extra data transfer step, which slows down pipeline execution and adds unnecessary complexity. For best performance, Prophecy always prefers reading and writing directly within the warehouse.
</Info>

## 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 Databricks to access the underlying data.