> ## Documentation Index
> Fetch the complete documentation index at: https://docs.monterey.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Warehouse

> Ingest targeted data from Snowflake, BigQuery and other data warehouses to Reforge Insights for consolidation and analysis.

# Supported Warehouses

<AccordionGroup>
  <Accordion title="Snowflake">
    ## Secure Data Sharing (Preferred)

    You can request our account details (identifier and region) and use one of the below options to give us the targeted access to the data you want to share.

    ### Direct Share

    If we are in the same region, you can configure a data share using Snowflake's instructions [here](https://docs.snowflake.com/en/user-guide/data-sharing-provider).

    ***Note:*** Creating a replication will not work since we are two separate orgs.

    ### Private Listing

    If we are in different regions, you can configure a private listing using Snowflake's instructions [here](https://docs.snowflake.com/en/collaboration/provider-listings-creating-publishing).

    ***Note:*** This route may incur additional egress costs outside the ones you would normally get:

    > Snowflake creates one secure share area for an organization to manage auto-fulfillment to a region and associates billing costs with that area. Because of that, the costs associated with auto-fulfillment are attributed differently when compared to manual database replication costs.

    Full explanation per Snowflake's docs [here](https://docs.snowflake.com/collaboration/provider-understand-cost-auto-fulfillment).

    ## Service User (Alternative)

    You can instead create a service user and grant it `READ` access on the tables you wish to connect. We will need the following information to finish configuring the integration:

    * host
    * username
    * private key (or password)
      * ***Note:*** Snowflake will disallow passwords for all service users by **October 2026** (full docs [here](https://docs.snowflake.com/en/user-guide/security-mfa-rollout)).
    * role
    * database
    * schema
    * warehouse (optional)

    We connect to Snowflake using the following IP addresses, which you may need to whitelist in your Snowflake account:

    ```
    34.212.54.206
    35.162.156.254
    44.241.212.223
    44.240.0.247
    ```
  </Accordion>

  <Accordion title="BigQuery">
    To connect with BigQuery:

    <Steps>
      <Step>
        Add the service account email address to your Google Cloud IAM policy and grant it the `bigquery.jobUser` *(BigQuery
        Job User)* role. This will allow us to run queries on your behalf.
      </Step>

      <Step>
        Share the dataset containing tables, like those described below, with our service account email address and grant it the `bigquery.dataViewer` *(BigQuery Data Viewer)* role. This will provide us with read-only access to the datasets you want to share.
      </Step>
    </Steps>
  </Accordion>

  <Accordion title="Amazon Redshift">
    To connect with Amazon Redshift using Datashare:

    <Warning>
      You will need to have the appropriate permissions to create a datashare in your Redshift cluster.
      Contact your Redshift administrator if you cannot create a datashare.
    </Warning>

    <Note>
      Please refer to the [Amazon Redshift Producer actions](https://docs.aws.amazon.com/redshift/latest/dg/writes-producer-new.html) for more information on creating and sharing datashares.
    </Note>

    <Steps>
      <Step>
        Ask your primary contact at Reforge for the `Reforge Namespace ARN` and `AWS account ID`. You will need these to authorize Reforge's AWS account to access your datashare.
      </Step>

      <Step>
        Create a datashare in your Redshift cluster:

        ```sql theme={null}
        CREATE DATASHARE reforge_<your company name>_datashare;
        ```
      </Step>

      <Step>
        Add objects to your datashare. You can add entire schemas or specific tables and views:

        ```sql theme={null}
        -- Add schema
        ALTER DATASHARE <datashare name> ADD SCHEMA <schema name>; -- you may need to create the schema first

        -- Pick specific items
        ALTER DATASHARE <datashare name> ADD TABLE <table name>;
        ALTER DATASHARE <datashare name> ADD VIEW <view name>;
        ```
      </Step>

      <Step>
        Make the datashare visible cross-account:

        ```sql theme={null}
        ALTER DATASHARE <datashare name> SET PUBLICACCESSIBLE TRUE;
        ```

        Note: This doesn't expose your data publicly. It only allows consumption by authorized accounts.
      </Step>

      <Step>
        Grant usage access to the datashare to the Reforge AWS account:

        ```sql theme={null}
        GRANT USAGE ON DATASHARE <datashare name> TO NAMESPACE <Reforge Namespace ID>; -- The Namespace ID is the last part of the Namespace ARN
        ```
      </Step>

      <Step>
        Authorize Reforge's AWS account to access your datashare:

        ```bash theme={null}
        aws redshift authorize-data-share \
          --data-share-arn <your datashare ARN> \
          --consumer-identifier <Reforge AWS account ID>
        ```

        Replace the ARN with your datashare ARN and the consumer-accounts value with the Reforge AWS account ID provided to you by your Reforge contact.

        You can also do this via the Redshift console under **Datashares → `<datashare name>` → Authorize**.
      </Step>

      <Step>
        Provide the following information to your primary contact at Reforge:

        | **Field**                                     | **Example**                                                                   |
        | --------------------------------------------- | ----------------------------------------------------------------------------- |
        | **Datashare ARN**                             | arn:aws:redshift:us-east-1:111122223333:datashare:reforge\_company\_datashare |
        | **Redshift namespace ARN**                    | arn:aws:redshift:us-east-1:111122223333:namespace:prod-ns-1                   |
        | **List of schemas / tables / views** included | public.conversations, public.latest\_tickets\_v                               |
      </Step>
    </Steps>
  </Accordion>
</AccordionGroup>

# Data Models

We support several schemas and relationships between tables. With each of the schemas defined below, you can also provide a list of additional column names that you would like to be included on the respective fields as **metadata**. A few examples of common additional fields are for feedback or conversation messages, a "version" number that the feedback refers to, or for Contacts, a "plan" or "subscription tier". When working with the team to connect to your data warehouse, simply share with us fields beyond those defined below that you would like to have included!

<Note>
  All fields with type DATE fields should be provided as ISO8601 formatted
  strings (e.g., `2024-01-15T10:30:00Z`).
</Note>

### Standalone Feedback

Standalone feedback are singular items of feedback, such as a review or a single response to a question.

| Field Name  | Required |  Type  |                               Description                               |
| :---------- | :------: | :----: | :---------------------------------------------------------------------: |
| id          |    Yes   | STRING |                Unique identifier for the feedback record                |
| description |    Yes   | STRING |                        The main feedback content                        |
| created\_at |    Yes   |  DATE  |                      When the feedback was created                      |
| updated\_at |    Yes   |  DATE  |                    When the feedback was last updated                   |
| source      |    No    | STRING |                The source of the feedback, e.g. "HUBSPOT"               |
| title       |    No    | STRING |                     Optional title for the feedback                     |
| contact\_id |    No    | STRING |                 Optional identifier linking to a contact                |
| rating      |    No    | NUMBER | Optional numeric rating associated with the feedback (e.g. NPS or CSAT) |

### Conversation

A conversation is a collection of conversational feedback messages. For example, a chat log between a support agent and a user.

| Field Name  | Required |  Type  | Description                                |
| :---------- | :------: | :----: | :----------------------------------------- |
| id          |    Yes   | STRING | Unique identifier for the conversation     |
| title       |    No    | STRING | Optional title for the conversation        |
| source      |    No    | STRING | The source of the feedback, e.g. "HUBSPOT" |
| created\_at |    Yes   |  DATE  | When the conversation was created          |
| updated\_at |    No    |  DATE  | When the conversation was last updated     |

### Conversation Message

These are the individual messages within a conversation, and should be linked to a conversation by the `conversation_id` field.

| Field Name       | Required |   Type  | Description                                      |
| :--------------- | :------: | :-----: | :----------------------------------------------- |
| id               |    Yes   |  STRING | Unique identifier for the message                |
| created\_at      |    Yes   |   DATE  | When the message was sent                        |
| updated\_at      |    No    |   DATE  | When the message was last updated                |
| conversation\_id |    Yes   |  STRING | ID of the conversation this message belongs to   |
| content          |    Yes   |  STRING | The message content                              |
| incoming         |    Yes   | BOOLEAN | Whether the message was sent by an external user |
| contact\_id      |    No    |  STRING | Optional identifier linking to a contact         |

### Contacts

Contacts are the users of your product.

| Field Name  | Required |  Type  | Description                        |
| :---------- | :------: | :----: | :--------------------------------- |
| id          |    Yes   | STRING | Unique identifier for the contact  |
| name        |    Yes   | STRING | The contact's full name            |
| email       |    Yes   | STRING | The contact's email address        |
| account\_id |    Yes   | STRING | The account the contact belongs to |
| created\_at |    Yes   |  DATE  | When the contact was created       |
| updated\_at |    Yes   |  DATE  | When the contact was last updated  |

### Accounts

Accounts are the companies that your contacts belong to.

| Field Name      | Required |  Type  | Description                       |
| :-------------- | :------: | :----: | :-------------------------------- |
| id              |    Yes   | STRING | Unique identifier for the account |
| name            |    Yes   | STRING | The account's name                |
| domain          |    Yes   | STRING | The account's domain              |
| contract\_value |    No    | NUMBER | The account's contract value      |
| created\_at     |    Yes   |  DATE  | When the account was created      |
| updated\_at     |    Yes   |  DATE  | When the account was last updated |

### Custom Tables

<Warning>
  Custom tables require additional engineering work on our end, which will
  extend your onboarding timeline as it requires planning and scheduling
  resources. We encourage you to use the expected formats above to ensure the
  fastest possible onboarding experience.
</Warning>

Sometimes, you may not be able to map your data to the existing schemas. In this case, you can share your own, existing tables. This will require custom engineering work on our end, so understand that time will be required to support this.

If you are providing custom tables, then what we will need to do is to effectively write custom queries and map the data to schemas above on our end. Given that, helping us understand how to do that mapping will expedite the process and lead to better, faster results for getting your data into Reforge Insights.

When sharing custom table information, please provide the following:

**1. Table Information** The table names, column names, column types, and a description of the data in each column.

For example:

**Customer Table: client\_organizations**

| Column Name     | Column Type | Description                                                          |
| :-------------- | :---------- | :------------------------------------------------------------------- |
| org\_id         | STRING      | The unique identifier for the organization                           |
| org\_name       | STRING      | The name of the organization (maps to account name)                  |
| org\_website    | STRING      | The organization's website domain (maps to account domain)           |
| annual\_revenue | NUMBER      | The organization's annual contract value                             |
| creation\_date  | DATE        | When the organization was created (maps to account created\_at)      |
| last\_modified  | DATE        | When the organization was last updated (maps to account updated\_at) |

**Customer Table: client\_users**

| Column Name      | Column Type | Description                                                          |
| :--------------- | :---------- | :------------------------------------------------------------------- |
| user\_id         | STRING      | The unique identifier for the user (maps to contact id)              |
| full\_name       | STRING      | The user's full name (maps to contact name)                          |
| user\_email      | STRING      | The user's email address (maps to contact email)                     |
| org\_id          | STRING      | Foreign key to client\_organizations (maps to account\_id)           |
| signup\_date     | DATE        | When the user was created (maps to contact created\_at)              |
| profile\_updated | DATE        | When the user profile was last updated (maps to contact updated\_at) |

**Customer Table: support\_tickets**

| Column Name | Column Type | Description                                                        |
| :---------- | :---------- | :----------------------------------------------------------------- |
| ticket\_id  | STRING      | Unique identifier for the support ticket (maps to conversation id) |
| opened\_at  | DATE        | When the ticket was created (maps to conversation created\_at)     |
| closed\_at  | DATE        | When the ticket was closed (maps to conversation updated\_at)      |
| user\_id    | STRING      | User who opened the ticket (maps to contact\_id)                   |
| org\_id     | STRING      | Organization the ticket belongs to                                 |
| status      | STRING      | Current status of the ticket                                       |
| priority    | STRING      | Priority level of the ticket                                       |

**Customer Table: ticket\_messages**

| Column Name    | Column Type | Description                                                  |
| :------------- | :---------- | :----------------------------------------------------------- |
| message\_id    | STRING      | Unique identifier for the message (maps to message id)       |
| ticket\_id     | STRING      | Foreign key to support\_tickets (maps to conversation\_id)   |
| message\_text  | STRING      | The content of the message (maps to message content)         |
| sent\_time     | DATE        | When the message was sent (maps to message created\_at)      |
| edited\_time   | DATE        | When the message was edited (maps to message updated\_at)    |
| from\_customer | BOOLEAN     | Whether the message was from the customer (maps to incoming) |
| user\_id       | STRING      | User who sent the message (maps to contact\_id)              |

**2. Relationship Information** If there are any relationships between the tables, please point out how the tables should be joined, as in which column on table A should be joined with which column on table B.

**3. Data Mapping** An explanation of how you would expect the data to map to the schemas defined above.

If you are interested in sharing custom tables, please reach out to your primary contact at Reforge.