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

# Salesforce external table gem

> Read and write from Salesforce

export const execution_engine_0 = "Prophecy Automate"

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

## Overview

The Salesforce gem enables you to read data from Salesforce directly into your Prophecy pipelines. The gem supports both SOQL (Salesforce Object Query Language) for querying standard Salesforce objects and SAQL (Salesforce Analytics Query Language) for accessing analytical datasets.

## Create a Salesforce gem

To create a Salesforce Source or Target gem in your pipeline:

<Steps>
  <Step title="Add a Source or Target 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 **Source** or **Target** from the dropdown.
    4. Click on the gem to open the configuration.
  </Step>

  <Step title="Select Salesforce format">
    In the **Type** tab, select **Salesforce**. Then, click **Next**.
  </Step>

  <Step title="Set location details">
    In the **Location** tab, set your connection details and query configuration. To learn more, jump to [Source location](#source-location) and [Target location](#target-location).
  </Step>

  <Step title="Set table properties">
    In the **Properties** tab, set the table properties. To learn more, jump to [Source
    properties](#source-properties) and [Target properties](#target-properties).
  </Step>

  <Step title="Preview data (Source only)">
    In the **Preview** tab, load a sample of the data and verify that it looks correct.
  </Step>
</Steps>

## Source configuration

Use these settings to configure a Salesforce Source gem for reading data.

### Source location

| Parameter                   | Description                                                                                                                            |
| --------------------------- | -------------------------------------------------------------------------------------------------------------------------------------- |
| Format type                 | Table format for the source. For Salesforce tables, set to `salesforce`.                                                               |
| Select or create connection | Select or create a new [Salesforce connection](/data-analysis/environment/connections/salesforce) in the Prophecy fabric you will use. |
| Query Mode                  | Specify the table you would like to read using a query. Learn more in [Query modes](#query-modes).                                     |

### Query modes

You have three options for retrieving data from Salesforce.

#### SAQL

Use a [SAQL](https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_intro.htm) query to access CRM Analytics datasets (formerly known as Wave Analytics). For example:

```
q = load "Account"; q = foreach q generate 'Id' as 'Id', 'Name' as 'Name'; q = limit q 5;
```

#### SOQL

Use a [SOQL](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm) query to read structured data from Salesforce objects such as `Account` or `Contact` tables. For example:

```SQL theme={null}
SELECT Id, Name, Industry, CreatedDate FROM Account WHERE CreatedDate >= LAST_N_DAYS:30
```

<Info>
  When using the `FIELDS(ALL)` keyword, the response is [limited to 200 rows per
  call](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_fields.htm#limiting_result_rows).
</Info>

<Warning>
  Prophecy does not support SOQL parent-to-child relationship queries that use nested
  subqueries. Query parent and child objects separately, then join the datasets in
  Prophecy.

  For example, the following query is not supported:

  ```sql theme={null}
  SELECT Id,
         Name,
         (SELECT FirstName, LastName FROM Contacts)
  FROM Account
  ```
</Warning>

#### Salesforce Objects

Retrieve all records from a specific Salesforce object by name, without writing a query. Use this option when the object exceeds the default 200 row limit of `FIELDS(ALL)`.

### Source properties

The following properties are available for the Salesforce Source gem. These properties only apply to tables retrieved with the **SOQL** or **Salesforce Object** query mode.

| Property                              | Description                                                                                        |
| ------------------------------------- | -------------------------------------------------------------------------------------------------- |
| Enable bulk query                     | Enable to run the query as a batch job in the background for better performance on large datasets. |
| Retrieve deleted and archived records | Enable to include soft-deleted and archived records in the returned table.                         |

## Target configuration

Use these settings to configure a Salesforce Target gem for writing data.

<Info>
  The Salesforce Target gem only supports writing to Salesforce Objects. You cannot write to CRM
  Analytics datasets.
</Info>

### Target location

Use the following parameters to define the write location.

| Parameter                   | Description                                                                                                                            |
| --------------------------- | -------------------------------------------------------------------------------------------------------------------------------------- |
| Format type                 | Table format for the source. For Salesforce tables, set to `salesforce`.                                                               |
| Select or create connection | Select or create a new [Salesforce connection](/data-analysis/environment/connections/salesforce) in the Prophecy fabric you will use. |
| Object Name                 | Name of the Salesforce Object to write to. If the object doesn't exist, it will be created automatically.                              |

### Target properties

The following properties are available for the Salesforce Target gem.

| Property          | Description                                                                                                                                                                                                                                                                                                                                    | Default  |
| ----------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------- |
| Description       | Text description of the target table. Use this field to document the purpose or content of the data being written.                                                                                                                                                                                                                             | None     |
| Write Mode        | Defines how records are written to Salesforce.<ul><li>Upsert: Update existing records if a match is found; otherwise, insert new records.</li><li>Insert: Add new records without modifying existing ones.</li><li>Update: Update existing records if a match is found.</li><li>Delete: Remove existing records if a match is found.</li></ul> | Upsert   |
| External ID Field | Specifies the Salesforce field used as the unique key when performing Upsert or Update operations. The field must be defined as an External ID in Salesforce.                                                                                                                                                                                  | None     |
| Use Bulk API      | Enable to run write operations using the Salesforce [Bulk API](https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/bulk_api_2_0.htm).                                                                                                                                                                                 | Disabled |