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

# HubSpot to PostgreSQL

> Configure ten HubSpot streams in the tabbed pipeline workspace, publish transformations, and deliver them to PostgreSQL.

This example uses the current pipeline detail workflow. Start with one stream
while learning, then repeat the same destination-owned setup for the remaining
HubSpot streams.

## 1. Prepare connections

1. Create a HubSpot private app with the scopes listed in the
   [HubSpot source guide](/connections/sources/crm/hubspot).
2. Create, test, and save the HubSpot source connection.
3. Create, test, and save a PostgreSQL destination connection.

## 2. Configure the Source tab

Open the pipeline, select **Source**, and choose the saved HubSpot connection.
Click **Discover catalog**, then select:

`contacts`, `companies`, `deals`, `tickets`, `owners`, `deal_pipelines`,
`ticket_pipelines`, `products`, `line_items`, and `quotes`.

For each stream:

1. Choose **Incremental** for changing CRM objects or **Full table** for small
   reference data such as owners and pipeline definitions.
2. Select the discovered cursor when Incremental mode is available.
3. Review fields and use **Preview** to inspect source records.
4. Click **Save stream settings**.

## 3. Add the PostgreSQL destination

Open **Destinations**, click **Add destination**, choose the saved PostgreSQL
connection, and save it. The destination now appears in both **Destinations**
and the destination navigator in **Transformations**.

## 4. Create and publish transformations

Open **Transformations**, select the PostgreSQL destination, and create one
transformation for every selected stream. Use explicit columns for stable
tables:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT
  id,
  created_at,
  updated_at,
  archived,
  properties
FROM {{ source('raw', 'hubspot__contacts') }}
```

HubSpot accounts can expose sparse custom properties and property names longer
than PostgreSQL's 63-character identifier limit. For wide dynamic streams, this
projection keeps runtime columns with PostgreSQL-compatible names:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT COLUMNS('^.{1,63}$')
FROM {{ source('raw', 'hubspot__companies') }}
```

For every transformation, complete the release sequence:

1. **Save draft**.
2. **Validate draft**.
3. **Preview output**.
4. **Publish revision**.

Only published revisions are used by pipeline runs.

## 5. Configure published output targets

Open the PostgreSQL destination from **Destinations**. Under **Published output
targets**, assign a destination table and Upsert key to each published
transformation:

| Stream           | Example table                        | Upsert key                |
| ---------------- | ------------------------------------ | ------------------------- |
| Contacts         | `analytics.hubspot_contacts`         | `id`                      |
| Companies        | `analytics.hubspot_companies`        | `id`                      |
| Deals            | `analytics.hubspot_deals`            | `id`                      |
| Tickets          | `analytics.hubspot_tickets`          | `id`                      |
| Owners           | `analytics.hubspot_owners`           | `id`                      |
| Deal pipelines   | `analytics.hubspot_deal_pipelines`   | `pipeline_id`, `stage_id` |
| Ticket pipelines | `analytics.hubspot_ticket_pipelines` | `pipeline_id`, `stage_id` |
| Products         | `analytics.hubspot_products`         | `id`                      |
| Line items       | `analytics.hubspot_line_items`       | `id`                      |
| Quotes           | `analytics.hubspot_quotes`           | `id`                      |

Pipeline-definition streams do not expose a synthetic `id`; use the composite
pipeline/stage key. Use **Create or verify destination table**, then click
**Save destination** after all output targets are ready.

## 6. Validate, run, and monitor

1. Open **Overview** and click **Validate pipeline**.
2. Resolve any readiness issue for a stream, transformation, output target, or
   destination table.
3. Click **Run all** in the pipeline header.
4. Open **Runs** to review status, destination, row counts, duration, published
   revisions, and errors.

Empty `line_items`, `quotes`, or other valid streams can complete with zero
rows. Zero rows alone do not indicate a connector failure.

After the first run succeeds, keep stable Upsert keys and use the discovered
update cursor for incremental CRM objects. A lookback window can capture late
updates while Upsert prevents duplicates.