# ETL & Reverse ETL Documentation
Source: https://www.integrate.io/docs/etl
Build visual data pipelines with Integrate.io ETL. Connect 150+ sources and destinations, transform data with built-in components, and schedule automated jobs.
# ETL: Advanced Search
Source: https://www.integrate.io/docs/etl/advanced-search
Use advanced search modifiers in Integrate.io ETL to quickly find packages, connections, jobs, and other resources across your account workspace.
## Plain text search
On the Packages and Jobs screens, a single-word search performs a case-insensitive substring match on the resource name. For example, `proj` matches `XXBI_PROJECT` and `XXBI_BPM_PROJECT`, and `estimat` matches `XXBI_ESTIMATING_V`.
To require an exact whole-word match, wrap the term in double quotes. For example, `"two"` matches `two` but not `one.two three`.
Multi-word plain text searches and digit-only searches continue to use whole-token matching.
## Search modifiers preview
| **Search Modifier** | **Description** |
| :------------------ | :------------------------------------ |
| + | preview of search modifiers available |
| : | preview of search modifiers available |
## Package group search modifiers
| **Search Modifier** | **Description** |
| :------------------ | :--------------------------------------------------------- |
| name: | search for the package group by its name |
| user: | search for package groups owned by a user |
| created: | search for a package group created before or after a date |
| modified: | search for a package group modified before or after a date |
## Package search modifiers
| **Search Modifier** | **Description** |
| :------------------ | :--------------------------------------------- |
| id: | search for a package's id number |
| name: | search for a package by its name |
| user: | search for packages owned by a user |
| created: | search for jobs created before or after a date |
| modified: | search for jobs modified after a date |
## Jobs search modifiers
| **Search Modifier** | **Description** |
| :------------------ | :----------------------------------------------------------------------------------------- |
| id: | search for a package's id number |
| log: | search for jobs with this error log |
| user: | search for jobs run by a user |
| status: | search for jobs with the status (failed, pending, stopped, running, stopping or completed) |
| package: | search for jobs run with the package name |
| cluster: | search for jobs run on a cluster name |
| started: | search for jobs started before or after a date |
| finished: | search for jobs by finished time after a date |
| duration: | search for jobs with a duration less than or greater than a number of seconds |
| schedule: | search for jobs by the schedule name |
Here is an example of a cluster search
# ETL: Connecting to SFTPToGo
Source: https://www.integrate.io/docs/etl/allowing-access-to-my-file-data-on-sftptogo
Connect your SFTPToGo file storage to Integrate.io ETL. Set up SFTP credentials and access permissions for reading file data in pipelines.
## Allowing access to my file data on SFTPToGo
SFTPToGo is a quick and easy way to spin up an SFTP server. It allows you to store files and then ingest them on our platform for processing purposes. The SFTP server can also be used as a destination on our platform so you can send processed files to the server and access them from there. If you do not already have an SFTPToGo account, you can sign up for an account [here](https://sftptogo.com/). All accounts include a 7-day free trial. Once you have your SFTPToGo account you can then follow the steps below to connect your SFTP server to our platform in less than 3 minutes.
### Connecting to your SFTPToGo server
On your Integrate.io or Prepforce account, click **Connections** on the lefthand navbar, next click **New Connection** button, and then click **Secure File Transfer Protocol (SFTP)**.
In this step you will need to add your SFTP account credentials. Below are the instructions for where you can find each credential.
* **Name:** You can type in whatever you would like to name this connection. E.g. ACME SFTP Connector
* **Hostname:** In your SFTPToGo account, click the **Credentials** tab and copy the **Host** to this field
* **User name:** In your SFTPToGo account, click the **Credentials** tab and copy the **Username** to this field
* **Password:** In your SFTPToGo account, click the **Credentials** tab and copy the **Password** to this field
You can leave all other fields (Authentication method, Access type, Port) as their default values.
After adding the credentials for the 4 fields in the step above, you can now proceed to test the connection by clicking **Test Connection**. You should see a **Yay, test successful** notification like in the screenshot below.
If you do not see this notification, follow the steps outlined by the error notification to resolve any issues with your credentials until you get a successful test connection.
Once you have successfully tested your connection, you can click **Create connection** button and start using your SFTPToGo server.
### Using a file from SFTPToGo server in your package
Now that you have successfully created a connection to your SFTPToGo server, you can start ingesting files to use in your packages.
To ingest a file from your SFTPToGo connection, first load your file to your SFTPToGo server. You can do this by clicking on the **File Browser** tab on your SFTPToGo home screen and then uploading the file.
On the Integrate.io platform, click **Packages** on the left navbar and then click **New Package**. Name your package and then click **Create package**.
In the package designer, click **Add Component** and, under the Sources column on the left, select **File Storage**.
Click the **File Storage** component and then choose your input connection from the dropdown menu in Step 1. This will be the SFTP connection you just created. Then click **Next**.
In your SFTPToGo dashboard, under the **File Browser** tab, click the 3 dots **...** on the file you want to ingest and then click **Copy path to clipboard**.
Then on Step 2 of your connection page on Integrate.io, paste the copied path name to the **Source path** field.
You can leave the rest of the settings at default unless you have specific requirements for any of them. Click **Next**.
The third and final section of this source component is for selecting the fields you want to use in your package and load to Salesforce. You can filter the actual fields that get loaded to Salesforce in a later component step. This step shows you a Data Preview of the fields in your file being ingested.
Click **Save** and you have now completed the source component. You can now proceed to add data transformation components to your package. If you don't have a need for data transformation, you can proceed to add the Salesforce component from the Destination column and set that up.
# ETL: Connecting to Amazon RDS
Source: https://www.integrate.io/docs/etl/allowing-integrateio-direct-access-to-relational-databases-on-amazon-web-services-rds
Grant Integrate.io ETL direct access to your Amazon RDS databases. Configure security groups and network settings for database connectivity.
This article walks you through the process of allowing Integrate.io ETL direct access to your [databases on RDS](https://www.integrate.io/blog/amazon-rds-what-is-it-and-how-does-it-work/). If you'd like to use an SSH tunnel, click [here](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) to read more.
You can watch this short video that demonstrates setting up a connection to MySQL RDS in VPC or read below for textual instructions.
The security rules are setup with either a VPC or an EC2 instance to provide Integrate.io ETL access to the database IP address and port.
**To verify or modify the security rules of an instance on VPC:**
Open the instance in the RDS Dashboard in the **Connectivity & Security** section of the **DB Instance** pane.
Verify that **Publicly Accessible** is set to the default value **Yes**.
Click the Security Group listed.
In the Security Group screen, select the **Inbound** tab. Then click **Edit inbound rules**.
Click **Add rule** to add the IP addresses listed [here](/docs/etl/integrateio-etls-ip-list/). Create a rule for each of the IP addresses:
In the "Type" dropbox, choose Custom TCP rule.
In the port range, add the port that your database listens to.
In the source dropbox select Custom IP and enter one of the IP addresses.
The default ports for supported databases are:
MySQL - 3306
SQL Server - 1433
PostgreSQL - 5432.
Click **Save rules**.
To configure your database to only accept SSL encrypted connections see [https://aws.amazon.com/rds/faqs/#53](https://aws.amazon.com/rds/faqs/#53) for more information.
# ETL: Connecting via Universal OAuth
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-any-api-with-oauth
Set up a universal OAuth connection in Integrate.io ETL to access any REST API that supports OAuth 2.0 authentication for data extraction.
## Universal OAuth Connection Guide
Universal OAuth is a flexible authentication service that allows you to connect to any OAuth-based API. This guide explains the three authentication methods available and how to use them with REST API components.
## Overview
When working with modern APIs, most services require authentication to access their data. Universal OAuth supports three authentication methods:
* **OAuth 2.0** - The standard authorization flow where you log in through the provider's website
* **Client Credentials** - Server-to-server authentication using API keys
* **Custom Auth** - Flexible option for non-standard authentication endpoints
Once authenticated, your connection can be used in REST API Source and Destination components to read from or write to any API.
## OAuth 2.0 (Authorization Code Flow)
This is the most common authentication method used by services like Google, GitHub, Slack, Salesforce, and many others. It provides secure access without sharing your password with third-party applications.
### How It Works
Enter your Client ID, Client Secret, and the provider's OAuth URLs
A popup window opens showing the provider's login page
Sign in to your account and grant permission to access your data
The popup closes and your connection is ready to use
### Configuration Fields
| Field | Description | Example |
| -------------------------------------------------- | ---------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------- |
| **Name** | A friendly name for your connection | My Google Analytics |
| **Authorization URL** | The provider's authorization endpoint where users log in | [https://accounts.google.com/o/oauth2/v2/auth](https://accounts.google.com/o/oauth2/v2/auth) |
| **Token URL** | The endpoint that exchanges the authorization code for an access token | [https://oauth2.googleapis.com/token](https://oauth2.googleapis.com/token) |
| **Client ID** | Your application's unique identifier (from the provider's developer console) | 123456789.apps.googleusercontent.com |
| **Client Secret** | Your application's secret key (keep this confidential) | GOCSPX-xxxxxxxxxx |
| **OAuth Scope** (optional) | Permissions your app needs (space-separated list) | openid email profile |
| **Additional Authorization Parameters** (optional) | Extra parameters for the authorization request | access\_type=offline\&prompt=consent |
**Redirect URI:** When setting up your OAuth application with the provider, use this callback URL:
`https://app.xplenty.com/auth/universaloauth/callback`
### Security Features
Universal OAuth implements industry-standard security measures:
* **PKCE (Proof Key for Code Exchange)** - Prevents authorization code interception attacks
* **State Parameter** - Protects against cross-site request forgery (CSRF) attacks
* **Automatic Token Refresh** - Tokens are automatically refreshed before they expire (when refresh tokens are available)
## Client Credentials
Client Credentials authentication is designed for server-to-server communication where no user interaction is required. This is commonly used for backend services, automated processes, and APIs that don't access user-specific data.
### How It Works
Provide your Client ID and Client Secret
The system directly requests an access token from the provider
No popup or user login required
### Configuration Fields
| Field | Description | Required |
| ----------------- | --------------------------------------------------------------------------- | -------- |
| **Name** | A friendly name for your connection | Yes |
| **Token URL** | The endpoint to request access tokens | Yes |
| **Client ID** | Your application's identifier | Yes |
| **Client Secret** | Your application's secret key | Yes |
| **OAuth Scope** | Permissions to request | No |
| **Audience** | The intended recipient of the token (required by some providers like Auth0) | No |
### Advanced Options
* **Use HTTP Basic Authentication** - Send credentials in the Authorization header instead of the request body (required by some providers)
* **Use custom parameter key names** - Some APIs use non-standard parameter names for client credentials
### When to Use Client Credentials
* Accessing APIs that don't require user authorization
* Machine-to-machine communication
* Background jobs and automated processes
* APIs that use API keys or service accounts
## Custom Auth
Custom Auth provides maximum flexibility for APIs that don't follow standard OAuth patterns. You can configure any HTTP request to obtain an access token from any endpoint.
### How It Works
Specify the HTTP method, URL, headers, and body
Tell the system where to find the access token in the response
The system makes the configured request and extracts the token
### Configuration Fields
| Field | Description | Example |
| ---------------------------- | ---------------------------------------------------- | ------------------------------------------------------------------------ |
| **HTTP Method** | The request method (GET, POST, PUT, PATCH) | POST |
| **Authentication URL** | The endpoint that returns the access token | [https://api.example.com/auth/token](https://api.example.com/auth/token) |
| **Response Path (JSONPath)** | The path to extract the token from the JSON response | \$.access\_token |
| **Request Headers** | Custom headers to include in the request | Content-Type: application/json |
| **Request Body** | Key-value pairs to send in the request body | grant\_type: client\_credentials |
### Understanding Response Path (JSONPath)
The Response Path tells the system where to find the access token in the API's response. Use dot notation to navigate nested JSON structures:
**Example Response:**
```json theme={null}
{
"status": "success",
"data": {
"access_token": "eyJhbGciOiJIUzI1NiIs...",
"expires_in": 3600
}
}
```
**Response Path:** `$.data.access_token`
#### Path Examples
| Response Structure | Path |
| ---------------------------------------------- | --------------------------- |
| `{"access_token": "..."}` | `$.access_token` |
| `{"data": {"token": "..."}}` | `$.data.token` |
| `{"auth": {"credentials": {"bearer": "..."}}}` | `$.auth.credentials.bearer` |
### When to Use Custom Auth
* APIs with non-standard authentication endpoints
* Legacy APIs that don't follow OAuth 2.0
* APIs requiring custom headers or body parameters
* Token endpoints that return tokens in non-standard response formats
## Custom Authentication Header
By default, the access token obtained from any authentication flow (OAuth 2.0, Client Credentials, or Custom Auth) is sent as a **Bearer token** in the `Authorization` header:
Authorization: Bearer eyJhbGciOiJIUzI1NiIs...
Some APIs require the token to be sent using a different header name or format. For example, Shopify uses `X-Shopify-Access-Token` instead of `Authorization: Bearer`. The **"Use custom authentication header"** checkbox allows you to customize how the token is sent.
### How to Configure
Check the **"Use custom authentication header"** checkbox (available on all three OAuth types)
Enter the **Header Name** - the HTTP header to use for authentication
Optionally enter a **Header Prefix** - the text placed before the token value. Leave empty to send the token without any prefix.
### Configuration Fields
| Field | Description | Default |
| ---------------------------- | ------------------------------------------------------------------------------ | ------------- |
| **Header Name** | The HTTP header name used for authentication | Authorization |
| **Header Prefix** (optional) | Text placed before the token. Leave empty for no prefix (token sent directly). | Bearer |
### Examples
| Provider | Header Name | Header Prefix | Resulting Header |
| ---------------------------- | ---------------------- | ------------- | --------------------------------- |
| Default (most APIs) | *unchecked* | *unchecked* | `Authorization: Bearer {token}` |
| Shopify | X-Shopify-Access-Token | *(empty)* | `X-Shopify-Access-Token: {token}` |
| Custom API with token prefix | X-API-Key | Token | `X-API-Key: Token {token}` |
## Using Your Connection in REST API Components
Once you've created a Universal OAuth connection, you can use it in REST API Source and Destination components to authenticate your API requests.
### How Authentication Works
When you select a Universal OAuth connection in a REST API component:
1. The system retrieves the stored access token from your connection
2. The token is automatically added to your API requests using the configured authentication header (default: `Authorization: Bearer {token}`)
3. If a custom authentication header is configured, the token is sent using the specified header name and prefix instead
4. Your request is sent to the API with proper authentication
**Default Request Header:**
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
**Custom Header Example (Shopify):**
X-Shopify-Access-Token: shpua\_ff47f0800268bf212f8...
### Setting Up a REST API Source
Add a REST API Source component to your pipeline
In the **Choose input connection** section, select **Connection**
Choose your Universal OAuth connection from the dropdown
Configure your API endpoint URL and other settings
The authentication is handled automatically
### Token Refresh
For connections that support refresh tokens (OAuth 2.0 flow with providers that issue refresh tokens), the system automatically refreshes expired tokens before making API calls. This ensures uninterrupted access to your data.
**Note:**
Some providers (like GitHub OAuth Apps) don't provide refresh tokens. In these cases, you may need to re-authenticate when the token expires.
## Troubleshooting
### Common Issues
| Issue | Possible Cause | Solution |
| --------------------------- | ------------------------------------------------- | ------------------------------------------------------------------------------------------------------------ |
| Popup blocked | Browser blocking popup windows | Allow popups for app.xplenty.com in your browser settings |
| Invalid redirect URI | Callback URL not registered with provider | Add `https://app.xplenty.com/auth/universaloauth/callback` to your OAuth app's allowed redirect URIs |
| Token exchange failed | Invalid Client ID or Secret | Verify your credentials match those in the provider's developer console |
| Custom Auth: Path not found | Incorrect Response Path | Check the actual API response structure and update the path accordingly |
| Access denied | Insufficient scopes | Add the required scopes to your OAuth configuration |
| 401 with valid token | API requires a non-standard authentication header | Enable "Use custom authentication header" and configure the correct header name and prefix for your provider |
### Testing Your Connection
After creating a connection, you can test it by:
Creating a REST API Source component
Selecting your connection
Configuring a simple GET request to a known endpoint (e.g., user profile endpoint)
Checking if the response returns valid data
## Supported Providers
Universal OAuth works with any OAuth 2.0 compliant provider. Here are some commonly used services:
* Google (Analytics, Sheets, Drive)
* GitHub
* GitLab
* Slack
* Salesforce
* HubSpot
* Shopify
* Stripe
* Dropbox
* Box
* Microsoft (Azure, Office 365)
* Zoom
* Twitch
* Spotify
* QuickBooks
* Zendesk
* And many more...
For providers not listed, you can configure Universal OAuth manually using the provider's OAuth documentation.
## Security Best Practices
* **Keep secrets confidential** - Never share your Client Secret or access tokens
* **Use minimal scopes** - Only request the permissions your integration actually needs
* **Regularly review connections** - Remove unused connections to minimize security exposure
* **Monitor for unauthorized access** - Check your provider's security logs for suspicious activity
# ETL: Connecting to Amazon S3
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-data-on-amazon-s3
Connect Amazon S3 to Integrate.io ETL for reading and writing data. Configure IAM credentials, bucket policies, and S3 connection settings.
To enable Integrate.io ETL [access to your S3 data](/docs/etl/allowing-integrateio-etl-access-to-data-on-amazon-s3/), you need to perform the following steps:
In AWS's IAM (Identity and Access Management) module, create a user account for working with Integrate.io ETL.
In IAM, save the user account's security credentials for later use when defining a connection in Integrate.io ETL.
In IAM, attach a user policy (provided by Integrate.io ETL) to the newly-created user account.
In Integrate.io ETL, create a connection using the IAM security credentials you saved previously.
Each of these steps are detailed below. **Note:** To enable Integrate.io ETL to operate with S3:
* never use an underscore in an S3 bucket name.
* never end an S3 bucket name with a dash.
### To create a user account in IAM
Log into the AWS Management Console.
Click Identity & Access Management (also known as IAM).
On the left navigation bar, click **Users**.
Click **Add User**.
Enter a user name, such as Integrate.io ETL, and make sure the check box **Programmatic access** is selected. Click **Next: Permissions** to proceed.
Click **Create User**. A user account with security credentials is created.
### To save user security credentials
Click **Download .csv** and save the credentials.csv file for later when you will need to configure Integrate.io ETL to work with S3.
Click **Close** to close the window.
### Creating new security credentials for a user
**Note:**
Perform this step if in the procedure to save user security credentials above, any of the user account security credential keys contain a slash, or if you cannot find the credentials.csv file you saved.
In the list of users, click the user you created for working with Integrate.io ETL, then scroll down to **Security credentials**, and then click **Make inactive**.
Click **Create access key**.
Continue from Step 1 in to download and save user security credentials above.
### To attach an Integrate.io ETL user policy to the IAM user account
In the list of users, click the user you created for working with Integrate.io ETL, then scroll down to **Add permissions**.
Click **Attach existing policies directly**.
In the search box, search 's3 ...'.
For **read-only** buckets, check **AmazonS3ReadOnlyAccess**.
For **read-write** buckets, Click **create policy.** Choose **JSON**, then Copy the following policy and replace "your-bucket" with your bucket name:
```json theme={null}
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "ListObjectsInBucket",
"Effect": "Allow",
"Action": ["s3:ListBucket"],
"Resource": ["arn:aws:s3:::bucket-name"]
},
{
"Sid": "ReadWriteObjects",
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:GetObject",
"s3:DeleteObject"
],
"Resource": ["arn:aws:s3:::bucket-name/*"]
}
]
}
```
All three object actions are required for pipelines that write to S3. Integrate.io ETL writes each output file to a temporary object and then finalizes it, which on Amazon S3 is a copy-and-delete operation. This needs `s3:PutObject` to write the file, `s3:GetObject` to read the temporary object while finalizing it, and `s3:DeleteObject` to remove the temporary object afterward. Granting `s3:PutObject` alone causes writes to fail while the file is being finalized.
Then click **Review policy.** On the next page, click **Create policy.** For more information, refer to AWS IAM documentation on the web.
### To create an Amazon S3 connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection.**
Select **Amazon S3**.
Type a name for the connection, then from the credentials.csv file you previously saved in **To save user security credentials** above, copy the Access key id and Secret access key into the respective fields.
If the S3 buckets reside in a region that requires AWS Signature v4, fill in the region for the connection. See region list [here](https://docs.aws.amazon.com/general/latest/gr/rande.html#s3_region).
Click **Test connection**. If the credentials are correct, a message that the connection test was successful appears.
Click **Create amazon s3 connection**.
The connection is created and appears in the list of connections.
Now you can create a package and test it on your actual data stored in S3.
# ETL: Connecting to DB2
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-db2-databases
Connect DB2 databases to Integrate.io ETL for reading and writing data. Configure access credentials, firewall rules, and connection settings.
* Create a user and grant it minimum permissions required for Integrate.io ETL to read or write data from the database.
* Allow access from [Integrate.io ETL's IP addresses](/docs/etl/integrateio-etls-ip-list/) to your database port (Refer to [this](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) article if you'd prefer to create an SSH tunnel or reverse SSH tunnel.)
## What is DB2?
Db2 is a family of data management products by IBM, built to manage both structured and unstructured data. Db2 can help you manage data that is on-premises as well as in private and public cloud environments. DB2 brings predictability into customer behavior to help businesses reduce complexity and drive high impact data insights.
## Create a DB2 database connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select DB2 Database and name the connection.
Select your access type (direct connection if you've whitelisted Integrate.io ETL's IP addresses.)
Enter your database host name.
Enter the database name.
Enter the user name
Enter the user's password.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
With Integrate.io’s 120+ integration sources and powerful transformation layer, your team will be able to make Db2 your single source of truth.
# ETL: Connecting to Google Cloud SQL for MySQL
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-google-cloud-sql-for-mysql-databases
Connect Google Cloud SQL for MySQL to Integrate.io ETL. Configure database access, firewall rules, and connection settings for data pipelines.
* Create a user and grant it minimum permissions required for Integrate.io ETL to read or write data from the database.
* Allow access from [Integrate.io ETL's IP addresses](/docs/etl/integrateio-etls-ip-list/) to your database port (Refer to [this](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) article if you'd prefer to create an SSH tunnel or reverse SSH tunnel.)
## To create a Google Cloud SQL for MySQL database connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Google Cloud SQL for MySQL and name the connection.
Select your access type (direct connection if you've whitelisted Integrate.io ETL's IP addresses.)
Enter your database host name.
Enter the database name.
Enter the user name.
Enter the user's password.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
**Note:**
If you'd like to keep an internet connection secure and safeguard any sensitive data that is being sent between Google Cloud and Integrate.io ETL, we recommend setting up a Reverse SSH Tunnel.
# ETL: Connecting to Google Cloud SQL for PostgreSQL
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-google-cloud-sql-for-postgresql-databases
Connect Google Cloud SQL for PostgreSQL to Integrate.io ETL. Configure access, firewall rules, and connection settings for data pipelines.
* Create a user and grant it minimum permissions required for Integrate.io ETL to read or write data from the database.
* Allow access from [Integrate.io ETL's IP addresses](/docs/etl/integrateio-etls-ip-list/) to your database port (Refer to [this](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) article if you'd prefer to create an SSH tunnel or reverse SSH tunnel.)
## To create a Google Cloud SQL for PostgreSQL database connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Google Cloud SQL for PostgreSQL and name the connection.
Select your access type (direct connection if you've whitelisted Integrate.io ETL's IP addresses.)
Enter your database host name.
Enter the database name.
Enter the user name.
Enter the user's password.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
**Note:**
If you'd like to keep an internet connection secure and safeguard any sensitive data that is being sent between Google Cloud and Integrate.io ETL, we recommend setting up a Reverse SSH Tunnel.
# ETL: Connecting to Heroku Postgres
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-heroku-postgres-databases
Connect Heroku Postgres to Integrate.io ETL for reading and writing data. Configure database credentials, SSL, and connection settings.
* Create a user and grant it minimum permissions required for Integrate.io ETL to read or write data from the database.
* Allow access from [Integrate.io ETL's IP addresses](/docs/etl/integrateio-etls-ip-list/) to your Heroku Postgres database port (Refer to [this](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) article if you'd prefer to create an SSH tunnel or reverse SSH tunnel.)
## To create a Heroku Postgres database connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Heroku Postgres and name the connection.
Select your access type (direct connection if you've whitelisted Integrate.io ETL's IP addresses.)
Enter your Heroku Postgres database host name or IP address.
Enter the database name.
Enter the username.
Enter the user's password.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
# ETL: Connecting to HubSpot
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-hubspot
Integrate.io ETL can load data to your HubSpot accounts. This article details creating the HubSpot connection in Integrate.io ETL.
## To create a HubSpot connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select "Hubspot" under "Services" connection type.
In the new connection window, click 'Authenticate' in the bottom right and enter your HubSpot credentials when prompted.
When prompted, name your HubSpot connection and click 'Create connection'.
Having successfully followed these steps, you can now use for HubSpot connection within your packages.
# ETL: Connecting to HubSpot (Service Token)
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-hubspot-service-token
Connect Integrate.io ETL to HubSpot using a service token, configure API access, and load contacts, deals, and marketing data into your pipelines.
## How to Retrieve a HubSpot Private App Token for Integrate.io
You can use **HubSpot Private Apps** to connect your HubSpot account securely with **Integrate.io** using an **Access Token (Bearer Token)**. This guide walks you through creating the token from the HubSpot Dashboard.
## Step 1: Log In to HubSpot
Go to [https://app.hubspot.com](https://app.hubspot.com)
Log in with an account that has **Super Admin** or **App Management** permissions.
## Step 2: Create a Private App
In the top right, click the **Settings** icon.
In the left sidebar, scroll down and click **Integrations > Legacy Apps**
Click the **Create** button
Select **Private** (For one account)
## Step 3: Configure the App
**Give your app a name**, e.g. `Integrate.io Sync`
(Optional) Add a description, icon, or team owner for tracking
## Step 4: Define Access Scopes
Under **Scopes**, search and select the APIs your integration needs access to.
Some common scopes for data sync into Integrate.io:
| Scope Category | Scope Name | Description |
| -------------- | --------------------------- | ------------------------ |
| CRM | `crm.objects.contacts.read` | Read contacts |
| CRM | `crm.objects.deals.read` | Read deals |
| Marketing | `marketing.emails.read` | Read marketing emails |
| Analytics | `reports.read` | Access analytics reports |
| Custom Objects | `crm.objects.custom.read` | Read custom CRM objects |
For exporting (writing to HubSpot), select appropriate **write** scopes.
## Step 5: Create the App and Copy the Token
Click **Create App**
Once created, you'll see your **Access Token** in the **Auth** tab
* **Copy and store this token securely**
The access token is like a password. Treat it as sensitive.
## Step 6: Use Token in Integrate.io
In **Integrate.io**:
Go to **Connections > Create New Connection**
Select **HubSpot**
For **authentication**, select:
* **Access Token (Private App)**
Paste the token you copied from HubSpot
Integrate.io will now use this token for authenticated API calls.
## Token Expiration
HubSpot Private App tokens do **not expire** unless:
* You **regenerate** the token
* You **delete the private app**
* Your user permissions are removed
# ETL: Connecting to IBM i (AS/400)
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-ibm-i-as400
Connect IBM i (AS/400) databases to Integrate.io ETL for reading data. Configure JDBC access, firewall rules, and connection settings.
* Create a user and grant it minimum permissions required for Integrate.io ETL to read data from the database.
* Allow access from [Integrate.io ETL's IP addresses](/docs/etl/integrateio-etls-ip-list/) to your database port (Refer to [this](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) article if you'd prefer to create an SSH tunnel or reverse SSH tunnel.)
## Create an IBM i (AS400) database connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select IBM i (AS400) and name the connection.
Select your access type (direct connection if you've whitelisted Integrate.io ETL's IP addresses.)
Enter your database host name.
Enter the database name.
Enter the user name
Enter the user's password.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
With Integrate.io ETL’s 120+ integration sources and powerful transformation layer, your team will be able to make IBM i (AS400) your single source of truth.
# ETL: Connecting to Microsoft SQL Server
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-microsoft-sql-server-databases
Connect Microsoft SQL Server to Integrate.io ETL for reading and writing data. Configure firewall rules, credentials, and connection settings.
* Create a user and grant it minimum permissions required for Integrate.io ETL to read or write data from the database.
* Allow access from [Integrate.io ETL's IP addresses](/docs/etl/integrateio-etls-ip-list/) to your SQL Server database port (Refer to [this](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) article if you'd prefer to create an SSH tunnel or reverse SSH tunnel.) Note: We only support **"Default"** [connection policy](https://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-settings?view=azuresql\&tabs=azure-portal#change-the-connection-policy) on Azure SQL/Synapse Analytics.
## To create a Microsoft SQL Server database connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select SQL Server and name the connection.
Select your access type (direct connection if you've whitelisted Integrate.io ETL's IP addresses.)
Enter your SQL Server database server name as the hostname.
Enter the database name.
Enter the user name. Use the pattern **user\@server\_name** as the user name.
Enter the user's password.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
# ETL: Connecting to Amazon Athena
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-amazon-athena-instance
Connect Amazon Athena to Integrate.io ETL for querying data. Configure IAM access credentials, S3 output location, and connection settings.
## To allow Integrate.io ETL access to Amazon Athena
* [Create an IAM user in AWS](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_users_create.html#id_users_create_console).
* Attach the `AmazonAthenaFullAccess` policy to the user.
* Create security credentials for the user and save them for later use when defining the connection in Integrate.io ETL.
## To define a connection in Integrate.io ETL to Amazon Athena
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select [**Amazon Athena**](https://www.integrate.io/blog/amazon-athena/) and name the connection.
Select AWS region.
Type in the S3 staging directory to use with Athena in the form `s3://bucket/path`. Note that the bucket should be in the same region you selected above.
Enter the default database to use. If you leave it empty, the user's default database will be used.
Fill out the IAM access key and secret.
Click **Test connection**. If the credentials are correct, a message that the connection test was successful appears.
Click **Create connection**.
# ETL: Connecting to Dynamics 365
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-dynamics-365
Integrate.io ETL can access your Dynamics 365 data. This article details creating the Dynamics 365 connection in Integrate.io ETL.
## Tenant Id and Instance URL
Before you start, you need to get **Tenant Id** and **Instance URL** for your Dynamics 365 account. Here is how you can get them:
#### Step 1: Access Admin Center
Go to [https://admin.powerplatform.microsoft.com](https://admin.powerplatform.microsoft.com)
Sign in with your Dynamics 365 admin account
Click **Environments** in the left navigation (for new admin portal: **Manage** -> **Environments**)
Find your Dynamics 365 environment in the list
Click the environment name
In the environment details, look for **Environment URL**
Copy the URL (e.g., `https://yourorg.crm.dynamics.com`)
This is your **Instance URL**
#### Step 2: Get Tenant ID
**Open a new tab** and go to [https://portal.azure.com](https://portal.azure.com)
**Sign in** with your current account
**Search for "Tenant Properties"** in the top search bar
**Copy the "Tenant ID"** (it's a long GUID)
## To create a Dynamics 365 connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Dynamics 365.
Fill **Tenant ID** and **Instance URL** (without https\:// at the beginning).
Click **Authenticate** button.
In the new window, sign in with your Dynamics 365 account or click **Sign in with a different Microsoft account**.
In the next Microsoft account page click **Yes** to let Integrate.io ETL access your information.
In the new Dynamics 365 connection window, name the connection and click **Create connection.**
## To modify Dynamics 365 connections in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
Click a connection to open and modify it. Make any necessary changes, then click Reconnect, and Save changes. To exit the Dynamics 365 connection window without changes, click Back to connections (grey tab on the left side) on the Dynamics 365 connection window.
To delete a Dynamics 365 connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
# ETL: Connecting to FTPS
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-file-transfer-protocol-secure-ftps
Connect an FTPS server to Integrate.io ETL for reading file data. Configure host, port, TLS credentials, and whitelist required IP addresses.
### To define a connection in Integrate.io ETL to your FTPS server
Click the Connections icon (lightning bolt) on the top left menu.
Click **New connection** and choose **File Transfer Protocol Secure (FTPS)**.
In the new FTPS connection window, name the connection and enter the connection information.
* **Hostname** - the host name of the FTPS server.
* **User Name** - the user name to use when connecting to FTPS server.
* **Password** - the password to use when connecting to FTPS server using password authentication.
**Note:**
User name cannot contain the character \$ and passwords cannot contain the character @.
* **Port** - the TCP port of the FTPS server (Default is 21).
Click **Test connection**. If the credentials are correct, a message that the connection test was successful appears.
Click **Create FTPS connection.**
The connection is created and appears in the connections list.
Now you can create a package and test it on your actual data stored in an FTPS server.
# ETL: Connecting to Google Cloud Storage
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-google-cloud-storage-v2
Connect Google Cloud Storage to Integrate.io ETL using a service account key. Configure bucket access and GCS connection settings for pipelines.
### Service Account Creation & JSON Key Generation
Access your [Google Cloud Console](https://console.cloud.google.com/#/project) and click the relevant project.
On the sidebar, choose **IAM & Admin -> Service Accounts -> Create Service Account**
Fill up **Service account name** and then click **Done**.
Click the newly created service account and navigate to **Keys**.
Generate a JSON key via **Add Key -> Create New Key -> JSON -> Create**.
We will upload this JSON key later on Integrate.io ETL when creating the connection.
### Grant Access to Cloud Storage Bucket
Access your [Google Cloud Storage](https://console.cloud.google.com/storage) and click the relevant bucket.
Under **Permissions** section, click **Grant Access**.
Input the service account email we created from the previous section under **New Principals** field.
For Read-only, select the following roles: **Storage Legacy Bucket Reader** and **Storage Legacy Object Reader**.
For Write access, select the following roles: **Storage Legacy Bucket Writer** and **Storage Legacy Object Reader**.
Click **Save**.
### Create a Google Cloud Storage connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Google Cloud Storage.
Name the connection. Upload JSON key.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
# ETL: Connecting to Google Drive
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-google-drive
Integrate.io ETL can access your Google Drive data. This article details creating the Google Drive connection in Integrate.io ETL.
## To create a Google Drive connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Google Drive.
Click **Authenticate.** (If required, sign in to Google Drive)
Click **Connect** to authorize Integrate.io ETL access to your Google Drive account.
In the new Google Drive connection window, name the connection and click **Create Google Drive connection.**
## To modify Google Drive connections in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
Click a connection to re-connect if your password has changed or the token has expired.
To delete a Google Drive connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
## To build a data pipeline in Integrate.io ETL to read files (.txt or .csv) stored in Google Drive
## REST API Component: get\_file\_ids
* The first call to the Google Drive API will be to search for the files you want to extract and get their ids.
* **Authentication** - Click **Connection** and then select your Google Drive connection. If you haven't created your Google Drive connection yet, click **+ New** and follow the instructions found above.
* **URL** - Enter the URL for the Google Drive API: **[https://www.googleapis.com/drive/v3/files?q=\$query\\\_string](https://www.googleapis.com/drive/v3/files?q=\$query\\_string).** Make sure the method is set to **GET.** Replace the variable **\$query\_string** with your own URL encoded query string. The query string is how you pass your search parameters to the API in order to find the files you want to download. For other examples of query strings, see [this](https://developers.google.com/drive/api/guides/search-files#query_string_examples) doc.
* **Response** - Type \$.files\[\*] in the Base record JSONPath Expression field.
* **Input fields** - Click **+ icon** to the right of the 'id' field to move it over to the Selected fields. Then click **Save.**
## Select Component: get\_files
* The second call to the Google Drive API will be to pass those ids to the API and get the files that match them.
* Type this expression in the Expression Editor: **CCurl(CONCAT('[https://www.googleapis.com/drive/v3/files/](https://www.googleapis.com/drive/v3/files/)', id, '?alt=media'), 'GET', '', '', '`$google_drive_connection_id`')#'body'.** Replace the variable **`$google_drive_connection_id`** with your own unique Google Drive connection id string. This can be found in the Connections menu. Find your GoogleDrive connection and there will be a unique connection id string beneath the connection name. Click it to copy it. (It will look something like this: GOOGLEDRIVE\_CONNECTION\_10818)
## Select Component: flatten\_split\_records
* Pass the api\_response\_body field into the function STRSPLITTOBAG along with the newline character as the delimiter regex and the result of that into Flatten (see these docs for more information on the [STRSPLITTOBAG](/docs/etl/strsplittobag/) or [Flatten](/docs/etl/flatten/) functions) - **Flatten(STRSPLITTOBAG(api\_response\_body, '\n'))**. Click **Save.**
## Filter Component: remove\_header
* Select the **NOT** condition from the dropdown menu. Then select the line field from the dropdown menu on the Expression side, then select the **Text matches (regex)** in the Operator dropdown menu, and lastly type **.\*add\_time.\*** in the Value/Expression field. This will remove the record which contains the column headers from the data set.
## Select Component: split\_line
* Pass the line field into the CSVSPLIT function with the comma selected as the delimiter regex (for more information on the CSVSPLIT function see [this](/docs/etl/csvsplit/) article) - **CSVSPLIT(line, ',')**. Click **Save.**
## Select Component: extract\_fields
* After the previous component executes the line field is a tuple. You can parse the tuple into its individual items using the syntax field\_name.$0, field\_name.$1, field\_name.\$2, etc. You can see this in the examples of [this](/docs/etl/bagtotuple/) doc. When you parse the tuple, the items are returned as byte array datatypes. Explicitly cast them to the correct datatype. In the template, all fields have been cast to chararray (string). If you prefer a different datatype, change chararray to one of the datatypes that Integrate.io supports. See [this](/docs/etl/using-expressions-in-integrateio-etl/) doc for supported datatypes and methods of casting and parsing. Last, add fields up to the number of columns in your schema by clicking the + icon to the right of any of the fields and rename the aliases according to your column names. For instance, if field\_0 is the 'id' column, type 'id' in the alias for that field.
## Destination Component: drive\_data
* The template shows a Redshift destination component; however, if you'd prefer to use a different destination, delete the Redshift component and select a destination component of your choice.
* **Choose target connection** - Select your target connection. If you haven't created your connection yet, click **+ New.**
* **Destination properties** - Fill in the values for target schema and table, select an operation type, pre or post-action SQL, and advanced options.
* **Schema mapping** - Click **Auto-fill** to bring in all of the fields. If you've selected a Merge operation type, click the **Key** box next to the merge key field(s). Click **Save.**
# ETL: Connecting to HDFS
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-hadoop-distributed-file-system-hdfs
Integrate.io ETL can access data residing on any Hadoop distributed file system (HDFS). This article details creating an HDFS connection in Integrate.io ETL.
You must provide Integrate.io ETL access to the cluster's [HDFS](https://www.integrate.io/blog/storing-apache-hadoop-data-cloud-hdfs-vs-s3/). Please consult our support team if the HDFS is behind firewall.
## To create a Hadoop Distributed File System (HDFS) connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
Click **New connection**.
Select **Hadoop Distributed File System (HDFS)**.
In the new HDFS connection window, name the connection and enter the connection information.
* User Name - the user name to use when connecting to HDFS (Kerberos authorization is not currently supported).
* NameNode Hostname - the host name of the NameNode server or the logical name of the NameNode in a high availability configuration.
* NameNode Port - the TCP port of the name node. Leave empty if the NameNode is in a high availability configuration.
* HttpFS Hostname - the host name of the Hadoop HttpFS gateway node. This should be available to Integrate.io ETL's platform.
* HttpFS Port - the TCP port of the Hadoop HttpFS gateway node (Default is 14000).
Click **Test connection**. If the credentials are correct, a message that the connection test was successful appears.
Click **Create HDFS connection**.
The connection is created and appears in the list of file storage connections.
Now you can create a package and test it on your actual data stored in Hadoop Distributed File System (HDFS).
# ETL: Connecting to Instagram
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-instagram
Connect Instagram to Integrate.io ETL for extracting social media data. Set up the Facebook-Instagram OAuth connection for data pipelines.
## To create an Instagram connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Instagram.
(If required, sign in to Instagram)
Click **Connect** to authorize Integrate.io ETL access to your Instagram account.
Click **Save changes** after set the title of (connection) name.
In the new Instagram connection window, name the connection and click **Create Instagram connection.**
## To modify Instagram connections in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
Click a connection to open it and re-connect if your password has changed or the token has expired. Make any necessary changes, then click Reconnect, and Save changes. To exit the Instagram connection window without changes, click Back to connections (grey tab on the left side) on the Instagram connection window.
To delete an Instagram connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
# ETL: Connecting to Intercom
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-intercom
Connect Intercom to Integrate.io ETL for reading customer messaging data. Set up API credentials for both free and premium Intercom accounts.
Integrate.io ETL can access your Intercom data in both free and premium accounts. This article details creating the Intercom connection and the process of building a data pipeline to read Intercom data in Integrate.io ETL. There are also pre-built templates to read data from Intercom. Please see [Create a package from a template](/docs/etl/create-a-package-from-a-template/) for instructions on using a template.
## To create an Intercom connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Intercom.
Click **Authenticate.** (If required, sign in to Intercom)
Click **Connect** to authorize Integrate.io ETL access to your Intercom account.
Click **Save Changes** to create connector after setting connector name.
In the new Intercom connection window, name the connection and click **Create Intercom connection.**
## To modify Intercom connections in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
Click a connection to re-connect if your password has changed or the token has expired.
To delete an Intercom connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
## To build a data pipeline to read Intercom companies data in Integrate.io ETL
## REST API Component: IntercomCompanies
* **Authentication** - Click **Connection** and then select your Intercom connection. If you haven't created your Intercom connection yet, click **+ New** and follow the instructions found above.
* **URL** - Enter the URL for the Companies endpoint of the Intercom API: **[https://api.intercom.io/companies](https://api.intercom.io/companies)**. Make sure the method is set to **GET.**
* **Headers** - Click **Add** and create Content-type, Accept, and Intercom-version headers as shown here:
* **Response** - Type \$.companies\[\*] in the Base record JSONPath Expression field.
* **Input fields** - Click **Select all** to move all the Available fields over to the Selected fields or pick and choose which fields you would like by clicking on the **+** icon next to the field name in the Available Fields column. Then click **Save.**
## Select Component: Companies\_ConvertDates
* Click **Autofill** to bring in all the fields from the source component. If desired, convert the three Unix timestamp fields (created\_at, remote\_created\_at, and updated\_at) into Datetime data types. Multiply the three fields times 1000 and pass the result into the ToDate function (for more information on the ToDate function see [this](/docs/etl/todate/) article) - like this **ToDate(created\_at \* 1000)**. Click **Save.**
## Destination Component: companies\_output
* The template shows a Redshift destination component; however, if you'd prefer to use a different destination, delete the Redshift component and select a destination component of your choice.
* **Choose target connection** - Select your target connection. If you haven't created your connection yet, click **+ New.**
* **Destination properties** - Fill in the values for target schema and table, select an operation type, pre or post-action SQL, and advanced options.
* **Schema mapping** - Click **Auto-fill** to bring in all of the fields. If you've selected a Merge operation type, click the **Key** box next to the merge key field(s). Click **Save.**
# ETL: Connecting to LinkedIn
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-linkedin
Create a LinkedIn connection in Integrate.io ETL, authorize OAuth access, and build pipelines that read LinkedIn ads, pages, and profile data.
Integrate.io ETL can access your LinkedIn data. This article details creating the LinkedIn connection and the process of building a data pipeline to read LinkedIn data in Integrate.io ETL. There are also pre-built templates to read data from LinkedIn. Please see [Create a package from a template](/docs/etl/create-a-package-from-a-template) for instructions on using a template.
## To create a LinkedIn connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection.**
Select LinkedIn.
Click **Authenticate.** (If required, sign in to LinkedIn.)
Click **Authenticate** to authorize Integrate.io ETL access to your LinkedIn account.
In the new LinkedIn connection window, name the connection and click **Create LinkedIn connection.**
## To modify LinkedIn connections in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
Click a connection to open it and re-connect if your password has changed or the token has expired. Make any necessary changes, then click Reconnect, and Save changes. To exit the LinkedIn connection window without changes, click Back to connections (grey tab on the left side) on the LinkedIn connection window.
To delete a LinkedIn connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
## To build a data pipeline to read LinkedIn campaign data in Integrate.io ETL
## REST API Component: get\_campaigns
* **Authentication** - Click **Connection** and then select your LinkedIn connection. If you haven't created your LinkedIn connection yet, click **+ New** and follow the instructions found above.
* **URL** - Enter the URL for the Campaigns endpoint of the LinkedIn API: **[https://api.linkedin.com/v2/adCampaignsV2?q=search](https://api.linkedin.com/v2/adCampaignsV2?q=search)**. Make sure the method is set to **GET.**
* **Response** - Make sure the **JSON** response type. Edit the **Base record JSONPath Expression** field like this: **\$.elements\[\*].**
* **Input fields** - Click **Select all** to move all the Available fields over to the Selected fields or pick and choose which fields you would like by clicking on the + icon next to the field name in the Available Fields column. Then click **Save**.
## Select Component: extract\_campaign\_meta
* Click **Autofill** to bring in all the fields from the source component. Parse the nested fields inside the five Map fields (totalBudget, locale, runSchedule, changeAuditStamps, and unitCost) using this syntax: field\_name#'key' For example, **totalBudget#'amount'.** For more information on parsing JSON data see [this](/docs/etl/how-do-i-process-json-data/) article. Click **Save.**
## Destination Component: campaigns
* The template shows a Redshift destination component; however, if you'd prefer to use a different destination, delete the Redshift component and select a destination component of your choice.
* **Choose target connection** - Select your target connection. If you haven't created your connection yet, click **+ New.**
* **Destination properties** - Fill in the values for target schema and table, select an operation type, pre or post-action SQL, and advanced options.
* **Schema mapping** - Click **Auto-fill** to bring in all of the fields. If you've selected a Merge operation type, click the **Key** box next to the merge key field(s). Click **Save.**
# ETL: Connecting to SFTP
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-secure-file-transfer-protocol-sftp
Connect any SFTP server to Integrate.io ETL for reading file data. Configure host, port, credentials, and SSH key authentication settings.
Integrate.io ETL can access data in files residing on any Secure File Transfer Protocol (SFTP) server. This article details creating an SFTP connection in Integrate.io ETL. In order to allow Integrate.io ETL direct access to your server, create firewall rules to allow Integrate.io ETL access from the relevant IPs in this [list](/docs/etl/integrateio-etls-ip-list/). You can also follow the directions [here](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) to allow Integrate.io ETL access through an SSH tunnel or reverse SSH tunnel.
### To define a connection in Integrate.io ETL to your SFTP server
Click the Connections icon (lightning bolt) on the top left menu.
Click **New connection**.
Select **Secure File Transfer Protocol (SFTP)**.
In the new SFTP connection window, name the connection and enter the connection information.
* **Access type** - For a direct connection, create firewall rules to allow Integrate.io ETL access from the relevant IPs in this [list](/docs/etl/integrateio-etls-ip-list/). You can also follow the directions [here](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) to allow Integrate.io ETL access through an SSH tunnel or reverse SSH tunnel.
* **Hostname** - the host name of the SFTP server.
* **Authentication Method** - Select password or public key authentication. If key based authentication is selected, copy your account's public key from **Settings** -> **Account Settings** -> **Profile** and add it to the SFTP user's authorized keys.
* **User Name** - the user name to use when connecting to SFTP server.
* **Password** - the password to use when connecting to SFTP server using password authentication. **Password** cannot contain the character @.
* **Port** - the TCP port of the SFTP server (Default is 22).
Click **Test connection**. If the credentials are correct, a message that the connection test was successful appears.
Click **Create SFTP connection.**
The connection is created and appears in the connections list.
Now you can create a package and test it on your actual data stored in an SFTP server.
# ETL: Connecting to Shopify
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-shopify
Create a Shopify connection in Integrate.io ETL and build pipelines that read orders, products, customers, and inventory data into your warehouse.
Integrate.io ETL can read your Shopify data. This article details creating the Shopify connection and the process of building a data pipeline to read Shopify data in Integrate.io ETL. There are also pre-built templates to read data from Shopify. Please see [Create a package from a template](/docs/etl/create-a-package-from-a-template) for instructions on using a template.
## To create a Shopify connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Shopify.
Enter the Shopify store name you'd like to connect to and click **Authenticate**.
(If required, sign in to Shopify)
Click **Connect** to authorize Integrate.io ETL access to your Shopify account.
In the new Shopify connection window, name the connection and click **Create Shopify connection.**
## To modify Shopify connections in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
Click a connection to open it. Make any necessary changes, then click Reconnect, and Save changes. To exit the Shopify connection window without changes, click Back to connections (grey tab on the left side) on the Shopify connection window.
To delete a Shopify connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
## To build a data pipeline to read Shopify customer data in Integrate.io ETL
## REST API Component: shopify\_customers
* **Authentication** - Click **Connection** and then select your Shopify connection. If you haven't created your Shopify connection yet, click **+ New** and follow the instructions found above.
* **URL** - Enter the URL for the Customers endpoint of the Shopify API: **`https://$shop_name/admin/api/2020-04/customers.json?limit=250&updated_at_min=$last_updated`**. Replace the variables `$shop_name` and `$last_updated` with your shop name and a timestamp for the minimum age of the updated data. Make sure the method is set to **GET.**
* **Pagination** - Check the box **Use pagination** and select **Automatic** from the **Pagination scheme** dropdown menu.
* **Response** - Make sure the **JSON** response type is selected. Edit the **Base record JSONPath Expression** field like this: \$.customers\[\*].
* **Input fields** - Click **Select all** to move all the Available fields over to the Selected fields or pick and choose which fields you would like by clicking on the + icon next to the field name in the Available Fields column. Then click **Save**.
## Clone Component
* No setup required. The clone component allows you to perform multiple transformations on the same input data.
## Select Component: flatten\_customer\_addresses (left-hand pipeline)
* This section of the pipeline will pass through the customer id and addresses field in order to flatten the array of addresses and build out a customer address table. Click in the first text field in the **Expression** column. Select "id" from the list of fields (#1). Type "id" in the **Alias** column (#2) (or click the magic wand icon \[#3].) Then click the plus icon to the right of the Alias value to add another field (#4). In the **Expression** column for the second field select "addresses" (#5). Pass the addresses field into the Flatten function like this: **Flatten(addresses)** (#6) Type "address" in the **Alias** column (#7). For more information on the Flatten function see [this](/docs/etl/flatten/) article. Click **Save.**
## Select Component: map\_customer\_addresses (left-hand pipeline)
* Click Autofill to bring in all the fields from the previous component. Pass the address field into the JsonStringToMap function like this: **JsonStringToMap(address)** Type "address" in the **Alias** column. For more information on the JsonStringToMap function see [this](/docs/etl/jsonstringtomap/) article. Click **Save.**
## Select Component: parse\_customer\_address (left-hand pipeline)
* Click **Autofill** to bring in all the fields from the previous component. Parse the nested address fields using this syntax: field\_name#'key'. For example, **address#'first\_name'.** For any of the non-string fields, explicitly cast them to the appropriate data type. For example, **(long)address#'id'.** For more information on parsing JSON data see [this](/docs/etl/how-do-i-process-json-data/) article. Click **Save.**
## Destination Component: customers\_addresses\_destination
* The template shows a Redshift destination component; however, if you'd prefer to use a different destination, delete the Redshift component and select a destination component of your choice.
* **Choose target connection** - Select your target connection. If you haven't created your connection yet, click **+ New.**
* **Destination properties** - Fill in the values for target schema and table, select an operation type, pre or post-action SQL, and advanced options.
* **Schema mapping** - Click **Auto-fill** to bring in all of the fields. If you've selected a Merge operation type, click the **Key** box next to the merge key field(s). Click **Save.**
## Select Component: parse\_customer\_address (right-hand pipeline)
* This section of the pipeline will pass through all of the fields except for the addresses field and builds out a customers table. Add another Select component after the Clone component. Click **Autofill** to bring in all the fields from the previous component. Then click the x icon next to the addresses field to remove it. Parse the nested fields inside the default\_address field using this syntax: field\_name#'key'. For example, **default\_address#'first\_name'.** Setting up this component is very similar to setting up the parse\_customer\_address component on the left-hand pipeline. For more information on parsing JSON data see [this](/docs/etl/how-do-i-process-json-data/) article. Click **Save.**
## Destination Component: customers\_destination (right-hand pipeline)
* Follow the same steps to set up this component that you did to set up the customer\_address\_destination component on the left-hand pipeline.
# ETL: Connecting to Xero
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-xero
Connect Xero accounting data to Integrate.io ETL. Set up OAuth authorization and build a data pipeline for reading Xero financial records.
Integrate.io ETL can access your Xero data. This article details creating the Xero connection and the process of building a data pipeline to read Xero data in Integrate.io ETL. There are also pre-built templates to read data from Xero. Please see [Create a package from a template](/docs/etl/create-a-package-from-a-template) for instructions on using a template.
## To create a Xero connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Xero.
Click **Authenticate.** (If required, sign in to Xero)
Click **Connect** to authorize Integrate.io ETL access to your Xero account.
In the new Xero connection window, name the connection and click **Create Xero connection.**
## Xero data access scopes
The Xero connection requests OAuth scopes that grant read access to accounting transactions, reports, journals, settings, contacts, attachments, files, and budgets. Existing connections created before a scope is added do not retroactively gain access. To read data from an endpoint added to the scope list after your connection was created, open the connection and click **Authenticate** to re-consent.
If a Xero API call returns `401` with `insufficient_scope`, Xero issued the token before you granted the required scope. Re-authenticate the connection to fix it.
## To modify Xero connections in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
Click a connection to re-connect if your password has changed or the token has expired.
To delete a Xero connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
## To build a data pipeline to read Xero account data in Integrate.io ETL
## REST API Component: accounts
* **Authentication** - Click **Connection** and then select your Xero connection. If you haven't created your Xero connection yet, click **+ New** and follow the instructions found above.
* **URL** - Enter the URL for the Accounts endpoint of the Xero API: **`https://api.xero.com/api.xro/2.0/Accounts`**. Make sure the method is set to **GET.** Click **Add** beneath **Headers** twice to add the following two headers: Accept: application/json, If-Modified-Since: `$last_updated_at`. Replace the variable `$last_updated_at` with a timestamp for the minimum age of the updated data.
* **Response** - Make sure the **JSON** response type is selected. Edit the **Base record JSON Path Expression** field like this: `$.Accounts[*]`.
* **Input fields** - Click **Select all** to move all the Available fields over to the Selected fields or pick and choose which fields you would like by clicking on the + icon next to the field name in the Available Fields column. Then click **Save**.
## Select Component: parse\_account\_fields
* Click **Autofill** to bring in all the fields from the source component. Convert the UpdatedDateUTC field into a datetime data type using these functions: **ToDate((long)REPLACE(REPLACE(UpdatedDateUTC,'\\/Date\\(',''),'\\+.\*','')).** Click **Save.**
## REST API Component: budgets
Use this configuration to read budget data from the Xero [Budgets API](https://developer.xero.com/documentation/api/accounting/budgets). Your connection must include the `accounting.budgets.read` scope. If you created your Xero connection before Integrate.io added this scope, re-authenticate it first.
* **Authentication** - Click **Connection** and select your Xero connection.
* **URL** - Enter `https://api.xero.com/api.xro/2.0/Budgets` and set the method to **GET**. Add a header `Accept: application/json`.
* **Use pagination** - Leave this unchecked. The Xero Budgets endpoint does not support pagination, and enabling the page-parameter strategy returns the same records repeatedly.
* **Response** - Select **JSON** and set **Base record JSON Path Expression** to `$.Budgets[*]`.
* **Input fields** - Click **Select all** or pick individual fields, then click **Save**.
`GET /Budgets` returns budget summaries. To retrieve the budget lines for a specific budget, use `GET https://api.xero.com/api.xro/2.0/Budgets/{BudgetID}` in a second REST API source component.
Example URL for a single budget:
```text theme={null}
https://api.xero.com/api.xro/2.0/Budgets/00000000-0000-0000-0000-000000000000
```
## Destination Component: xero\_accounts\_destination
* The template shows a Redshift destination component; however, if you'd prefer to use a different destination, delete the Redshift component and select a destination component of your choice.
* **Choose target connection** - Select your target connection. If you haven't created your connection yet, click **+ New.**
* **Destination properties** - Fill in the values for target schema and table, select an operation type, pre or post-action SQL, and advanced options.
* **Schema mapping** - Click **Auto-fill** to bring in all of the fields. If you've selected a Merge operation type, click the **Key** box next to the merge key field(s). Click **Save.**
# ETL: Connecting to YouTube
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-data-on-youtube
Connect YouTube data to Integrate.io ETL. Set up Google OAuth authorization and configure a pipeline to read YouTube channel and video data.
Integrate.io ETL can access your YouTube data. This article details creating the YouTube connection and the process of building a data pipeline to read YouTube data in Integrate.io ETL. There are also pre-built templates to read data from YouTube. Please see [Create a package from a template](/docs/etl/create-a-package-from-a-template) for instructions on using a template.
## To create a YouTube connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select YouTube.
(If required, sign in to YouTube)
Click **Authenticate** to authorize Integrate.io ETL access to your YouTube account.
In the new YouTube connection window, name the connection and click **Create YouTube connection.**
## To modify YouTube connections in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
Click a connection to open it and re-connect if your password has changed or the token has expired. Make any necessary changes, then click Reconnect, and Save changes. To exit the connection window without changes, click Back to connections (grey tab on the left side) on the connection window.
To delete a YouTube connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
## To build a data pipeline to read YouTube channel list data in Integrate.io ETL
## REST API Component: Channels\_List
* **Authentication** - Click **Connection** and then select your YouTube connection. If you haven't created your YouTube connection yet, click **+ New** and follow the instructions found above.
* **URL** - Enter the URL for the Channel List endpoint of the YouTube API: **`https://www.googleapis.com/youtube/v3/channels?part=snippet%2CcontentDetails%2CbrandingSettings%2Cstatistics&id=$Channel_id`**. Replace the variable `$Channel_id` with your Channel ID. Make sure the method is set to **GET.**
* **Response** - Make sure the **JSON** response type is selected as well as the **Object** record type.
* **Input fields** - Click **Select all** to move all the Available fields over to the Selected fields or pick and choose which fields you would like by clicking on the **+** icon next to the field name in the Available Fields column. Then click **Save.**
## Select Component: Extract\_Channel\_Fields
* Click **Autofill** to bring in all the fields from the source component. Parse the nested fields inside the four Map fields (snippet, contentDetails, statistics, and brandingSettings) using this syntax: field\_name#'key' For example, **snippet#'title'**. For the nested field called relatedPlaylist inside the contentDetails field, use the JsonStringToMap function and then parse the keys within it (for more information on the JsonStringToMap function see [this](/docs/etl/jsonstringtomap/) article; for information on processing JSON data see [this](/docs/etl/how-do-i-process-json-data/) article) like this: **JsonStringToMap(contentDetails#'relatedPlaylists')#'likes'**. Click **Save.**
## Destination Component: Channels\_List\_Table
* The template shows a Redshift destination component; however, if you'd prefer to use a different destination, delete the Redshift component and select a destination component of your choice.
* **Choose target connection** - Select your target connection. If you haven't created your connection yet, click **+ New.**
* **Destination properties** - Fill in the values for target schema and table, select an operation type, pre or post-action SQL, and advanced options.
* **Schema mapping** - Click **Auto-fill** to bring in all of the fields. If you've selected a Merge operation type, click the **Key** box next to the merge key field(s). Click **Save.**
# ETL: Connecting to Marketing Cloud (REST)
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-marketing-cloud-rest
Connect Salesforce Marketing Cloud (REST API) to Integrate.io ETL. Configure API credentials and connection settings for data extraction.
## To define a connection in Integrate.io ETL to Marketing Cloud
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select **Marketing Cloud(REST)** and name the connection.
In the new Marketing Cloud connection window, name the connection and enter the connection information.
* **Name** - Name for the new connection
* **Client ID** - Your Marketing Cloud Client ID
* **Client Secret** - Your Marketing Cloud Client Secret
* **Account ID** - Your Marketing Cloud Account ID
Click **Create Connection**.
## To modify a Marketing Cloud connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
Click a connection to open and modify it. Make any necessary changes, then click Test connection, and Save changes. To exit the Marketing Cloud connection window without changes, click Back to connections (grey tab on the left side) on the Marketing Cloud connection window.
To delete a Marketing Cloud connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
# ETL: Connecting to Marketing Cloud (SOAP)
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-my-marketing-cloud-soap
Connect Salesforce Marketing Cloud (SOAP API) to Integrate.io ETL. Configure WSDL credentials and connection settings for data extraction.
## To create a Marketing Cloud (SOAP) connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Marketing Cloud (SOAP).
In the new Marketing Cloud (SOAP) connection window, name the connection and enter the connection information, then click **Create connection**.
* **Name** - Name of the new connection
* **Client ID** - Your Marketing Cloud Client ID
* **Client Secret** - Your Marketing Cloud Client Secret
* **Subdomain** - Your Marketing Cloud Subdomain
* **Account ID** - Your Marketing Cloud Account ID
## To modify a Marketing Cloud (SOAP) connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
Click a connection to open and modify it.
Make any necessary changes, then click Test connection, and Save changes. To exit the Marketing Cloud (SOAP) connection window without changes, click Back to connections (grey tab on the left side) on the Marketing Cloud (SOAP) connection window.
To delete a Marketing Cloud (SOAP) connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
# ETL: Connecting to MySQL
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-mysql-databases
Connect MySQL databases to Integrate.io ETL for reading and writing data. Configure user permissions, firewall rules, and connection settings.
* Create a user and grant it minimum permissions required for Integrate.io ETL to read or write data from the database.
* Allow access from [Integrate.io ETL's IP addresses](/docs/etl/integrateio-etls-ip-list/) to your MySQL database port (Refer to [this](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) article if you'd prefer to create an SSH tunnel or reverse SSH tunnel.)
## To create a MySQL database connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select MySQL and name the connection.
Select your access type (direct connection if you've whitelisted Integrate.io ETL's IP addresses.)
Enter your MySQL database host name or IP address.
Enter the database name.
Enter the username.
Enter the user's password.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
# ETL: Connecting to Oracle
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-oracle-databases
Connect Oracle databases to Integrate.io ETL for reading and writing data. Configure user permissions, firewall rules, and connection settings.
* Create a user and grant it minimum permissions required for Integrate.io ETL to read or write data from the database.
* Allow access from [Integrate.io ETL's IP addresses](/docs/etl/integrateio-etls-ip-list/) to your database port (Refer to [this](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) article if you'd prefer to create an SSH tunnel or reverse SSH tunnel.)
## To create an Oracle database connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Oracle Database and name the connection.
Select your access type (direct connection if you've whitelisted Integrate.io ETL's IP addresses.)
Enter your database host name.
Select Database Identifier and enter SID / Service Name by Database Identifier.
Enter the database name.
Enter the user name.
Enter the user's password.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
# ETL: Connecting to PostgreSQL
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-postgresql-databases
Connect PostgreSQL databases to Integrate.io ETL for reading and writing data. Configure permissions, firewall rules, and connection settings.
* Create a user and grant it minimum permissions required for Integrate.io ETL to read or write data from the database.
* Allow access from [Integrate.io ETL's IP addresses](/docs/etl/integrateio-etls-ip-list/) to your PostgreSQL database port (Refer to [this](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) article if you'd prefer to create an SSH tunnel or reverse SSH tunnel.)
## To create a PostgreSQL database connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select PostgreSQL and name the connection.
Select your access type (direct connection if you've whitelisted Integrate.io ETL's IP addresses.)
Enter your PostgreSQL database host name or IP address.
Enter the database name.
Enter the username.
Enter the user's password.
If you need to connect to PostgreSQL through **custom SSL certificate**, check on custom SSL certificate. Then upload files of Certificate file, Private key, Ca Cert.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
# ETL: Connecting to SAP HANA
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-sap-hana-databases
Connect SAP HANA databases to Integrate.io ETL for reading and writing data. Configure user permissions, firewall rules, and connection settings.
* Create a user and grant it minimum permissions required for Integrate.io ETL to read or write data from the database.
* Add rules in your [database firewall](https://docs.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql) to allow access for Integrate.io ETL's IP addresses. Add a rule for each IP relevant to your account's region using [this list](/docs/etl/integrateio-etls-ip-list/) (Refer to [this](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) article if you'd prefer to create an SSH tunnel or reverse SSH tunnel.)
## To create a SAP HANA database connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select SAP HANA and name the connection.
Select your access type (direct connection if you've whitelisted Integrate.io ETL's IP addresses.)
Enter your database host name.
Enter the database name.
Enter the user name.
Enter the user's password.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
# ETL: Connecting to TikTok Ads
Source: https://www.integrate.io/docs/etl/allowing-integrateio-etl-access-to-tiktok-ads-data
Connect TikTok Ads to Integrate.io ETL for syncing advertising data. Configure API credentials and set up the TikTok Ads data connection.
## To create a TiktokAds Insights connection in Integrate.io ETL:
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New** **connection**.
Select TiktokAds.
Click **Authenticate.** You will be redirected to Tiktok business account in order to grant Integrate.io ETL's Tiktok application access to your ad accounts on your behalf.
You may need to login in advance before Tiktok asks you to allow Integrate.io ETL access to your TiktokAds and related stats. Click **OK**.
In the new TiktokAds connection window, name the connection and click **Create TiktokAds connection.**
## To modify TiktokAds Insights connections in Integrate.io ETL:
Click the Connections icon (lightning bolt) on the top left menu.
Click a connection to open and modify it. Make any necessary changes, then click Reconnect, and Save changes. To exit the TiktokAds connection window without changes, click Back to connections (grey tab on the left side) on the TiktokAds connection window.
To delete a TiktokAds connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
**NOTE:**
You may need to reconnect to TiktokAds periodically.
# ETL: Getting Started Guides Overview
Source: https://www.integrate.io/docs/etl/category/getting-started
Browse Integrate.io ETL getting started guides covering connections, packages, clusters, jobs, package groups, terminology, and best practices.
Get up and running with Integrate.io ETL & Reverse ETL. These guides cover the foundational concepts you need to understand before building pipelines: how connections, packages, clusters, and jobs work together to move and transform your data.
# ETL: Packages & Workflows
Source: https://www.integrate.io/docs/etl/category/packages-and-workflows
Create and manage ETL packages and workflows in Integrate.io. Use the visual designer, templates, version control, and workflow components.
Packages are the building blocks of Integrate.io ETL pipelines. A package defines your data flow from source through transformations to destination. Workflows let you orchestrate multiple packages, run SQL, move files, and chain operations together. These guides cover creating, configuring, and managing both.
# ETL: Sources
Source: https://www.integrate.io/docs/etl/category/sources
Connect to data sources in Integrate.io ETL. Browse 150+ source connectors for databases, cloud storage, SaaS APIs, and file systems.
Source components define where your ETL pipeline reads data from. Integrate.io supports databases (PostgreSQL, MySQL, Redshift, BigQuery, Snowflake), SaaS platforms (Salesforce, HubSpot, Google Ads, Facebook Ads), file storage (S3, SFTP, Google Drive), and REST APIs. Each guide covers connection setup and component configuration.
## Snapshot CDC Sources
Read only changed records from a database using a Snapshot CDC source component. Each database has its own connector; see the [Snapshot CDC Source overview](/docs/etl/using-components-cdc-database-source) for the shared configuration, storage, and change detection reference.
## Tutorials & Reference
# ETL: Component Previewer
Source: https://www.integrate.io/docs/etl/component-previewer
Use Component Previewer in Integrate.io ETL to test and preview data output from individual pipeline components before running a full job.
## What is Component Previewer?
Component previewer is a new feature available in the Xplenty package designer, which allows you to preview your data at each transformation component as you build your package. Up until now, you could only preview data on some of our source components. Apart from confirming the output of your transformation components, running a component preview is also a great way to identify issues within your package and your data flow logic.
## Usage
The usual flow of creating packages and components does not change. Create and manage packages as you usually would, and if you feel the need to peek into your data pipeline, here are the steps:
Open a package.
Open or create a transformation component - it must receive data from other source or transformation components.
Optionally, set the **Limit** value next to the Preview button to control the number of records returned. The default is 50, and accepted values range from 1 to 1000. Leaving the field empty restores the default.
Click the yellow Preview button and wait for the preview to complete. Do not close the component window while the previewer is running.
The component displays the preview data once the previewer job completes. If errors occur, the component displays error messages instead.
## Error logs
The component previewer provides real-time progress of the data preview job. At the end of the preview, the result data appears in a table. If the preview contains an error, the log box shows you the error message. Below are a few examples of package errors during the component preview job.
### Invalid source component
If the source component is not set up correctly, the preview will fail validation. In this example, the source connection is present but source properties and the schema section are not complete.
Running the previewer on a Select component attached to this source component returns the following error:
To fix this error, revisit the "file\_storage\_5" source component and set it up completely. Then re-run the previewer.
### Transformation component produces no data
In this scenario, a transformation component in the preview path produces no data. For example, a Filter component might have filtered out all data. Below are the logs which would be shown in this case.
The final message suggests that you review the components attached to the transformation component being previewed to identify any logic that might have caused the empty output.
### Assertion error when using the Assert component
The [Assert component](/docs/etl/using-components-assert-transformation/) allows you to ensure that data from a source complies with certain conditions before loading it into another component. If the source data doesn't meet these conditions, the job fails with an assertion failure. Similarly, when you preview an Assert component, the preview job fails if the conditions are not met. The example below shows a preview failure when the source data does not meet a specified condition.
To see the assertion message specified in the Assert component, scroll to the right end of the log box.
## Limitations
### Administrative
* Accounts using the component previewer feature must have a sufficient subscription level to allow for the creation of at least 1 sandbox cluster.
### Practical
Component previewer is known to fail given the following conditions:
* Using BingAds or Google Adwords source connections
* BigQuery source data contains date
* BSON in source data (e.g. in MongoDB)
* RestAPI source component has package variable in request header field (e.g. Authorization \$access\_token)
As noted above, BingAds connections are currently not supported by component previewer. Also, component preview data may not be consistent with standard job output since component previewer uses only sample data from the source connection. If the source connection has a wide range of values in a column, only a subset of those values might appear in the component previewer output. An example using Aggregate component:
1. Source data has 100 rows with a date column
2. First 50 rows have date "1st July", next 50 rows have "3rd July"
3. Previewer uses the first 50 rows as sample data - only "1st July" dates
4. Component previewer on an Aggregate component which counts occurrence of each date will output\
date | count\
1st July | 50
5. In a standard job, the output would be\
date | count\
1st July | 50\
3rd July | 50
# ETL: Windows File Share Source
Source: https://www.integrate.io/docs/etl/connecting-to-windows-file-share-source
Read files from a Windows file share (SMB/CIFS) in Integrate.io ETL using SFTP over a reverse SSH tunnel, with no inbound firewall rules.
Open **Windows Powershell** via **Run as Administrator** and run the commands below
## 1. Install OpenSSH Server (localhost only)
```powershell theme={null}
Add-WindowsCapability -Online -Name OpenSSH.Server~~~~0.0.1.0
Start-Service sshd # creates the default sshd_config on first run
$cfgPath = "C:\ProgramData\ssh\sshd_config"
$cfg = Get-Content $cfgPath -Raw
# Ensure the SFTP subsystem uses internal-sftp (sftp-server.exe can't be reached from inside the chroot used in Step 2)
$cfg = $cfg -replace "(?m)^\s*#?\s*Subsystem\s+sftp\s+.*\r?\n", ""
$cfg = "Subsystem sftp internal-sftp`r`n" + $cfg
# Idempotent global directives — must be inserted BEFORE any Match block to stay in global scope
if ($cfg -notmatch "# BEGIN integrate-io-global") {
$globalBlock = @"
# BEGIN integrate-io-global
ListenAddress 127.0.0.1
PasswordAuthentication no
PubkeyAuthentication yes
PubkeyAcceptedAlgorithms +ssh-rsa
# END integrate-io-global
"@
if ($cfg -match "(?m)^Match\s+") {
$cfg = $cfg -replace "(?m)^(Match\s+)", "$globalBlock`$1"
} else {
$cfg = $cfg.TrimEnd() + $globalBlock
}
}
Set-Content -Path $cfgPath -Value $cfg -Encoding UTF8 -NoNewline
Restart-Service sshd
Set-Service -Name sshd -StartupType Automatic
```
## 2. Create the SFTP user and grant read access
A dedicated non-admin SFTP user reads files from a folder on the Windows host. You'll be prompted for a password — any value; key auth is enforced below. Pick one of the two options:
* **Option A** — create a new folder for Integrate.io to read from.
* **Option B** — point the SFTP user at an existing folder you already have.
### Option A: Create a new folder to read from
A dedicated non-admin user, chrooted into `C:\fileshare-test`. Drop the files you want Integrate.io to read into the `in/` subfolder; the SFTP user gets read-only access.
```powershell theme={null}
New-Item -ItemType Directory -Path "C:\fileshare-test\in" -Force | Out-Null
if (-not (Get-LocalUser -Name "xplenty-sftp" -ErrorAction SilentlyContinue)) {
$pw = Read-Host -AsSecureString "Password for xplenty-sftp (any value; key auth is enforced)"
New-LocalUser -Name "xplenty-sftp" -Password $pw -PasswordNeverExpires | Out-Null
}
icacls "C:\fileshare-test" /inheritance:r | Out-Null
# Admins/SYSTEM own the chroot; the SFTP user gets read-only (RX), which inherits into in/.
icacls "C:\fileshare-test" /grant "SYSTEM:(OI)(CI)F" "Administrators:(OI)(CI)F" "xplenty-sftp:(OI)(CI)RX" | Out-Null
$cfgPath = "C:\ProgramData\ssh\sshd_config"
if (-not (Select-String -Path $cfgPath -Pattern "# BEGIN integrate-io-match" -Quiet)) {
Add-Content -Path $cfgPath -Value @"
# BEGIN integrate-io-match
Match User xplenty-sftp
AuthorizedKeysFile __PROGRAMDATA__/ssh/authorized_keys_%u
ForceCommand internal-sftp
ChrootDirectory C:\fileshare-test
AllowTcpForwarding no
PermitTunnel no
X11Forwarding no
# END integrate-io-match
"@
}
Restart-Service sshd
```
### Option B: Use an existing folder (read-only)
Use this instead of Option A if you already have a folder to ingest from.
**Important**: Does any non-admin currently write to the folder? OpenSSH refuses the session unless the chroot root **and every parent up to the drive** are owned by Admins/SYSTEM and **not writable by any non-admin principal**. This is only recommended if there are no other non-admin users or any existing integrations writing to the folder, otherwise, create a new folder to ingest from.
```powershell theme={null}
## Point the SFTP user at the existing folder (read-only)
$Share = "C:\ExistingData" # <- the existing folder you want to ingest from
if (-not (Get-LocalUser -Name "xplenty-sftp" -ErrorAction SilentlyContinue)) {
$pw = Read-Host -AsSecureString "Password for xplenty-sftp (any value; key auth is enforced)"
New-LocalUser -Name "xplenty-sftp" -Password $pw -PasswordNeverExpires | Out-Null
}
# Additive read-only grant — does NOT touch existing ACEs.
# RX = list + read + traverse, no write/delete. (OI)(CI) propagates to files + subfolders.
icacls $Share /grant "xplenty-sftp:(OI)(CI)RX" | Out-Null
# --- ONLY if the chroot check fails with "bad ownership or modes" ---
# That means a non-admin principal has write at the chroot root. Clear it,
# then re-grant read to anyone who still needs it. Skip this block otherwise.
# icacls $Share /remove:g "Users" "Authenticated Users" "Everyone" | Out-Null
# icacls $Share /grant "Users:(OI)(CI)RX" | Out-Null # re-grant read if needed
# -------------------------------------------------------------------
$cfgPath = "C:\ProgramData\ssh\sshd_config"
if (-not (Select-String -Path $cfgPath -Pattern "# BEGIN integrate-io-match" -Quiet)) {
Add-Content -Path $cfgPath -Value @"
# BEGIN integrate-io-match
Match User xplenty-sftp
AuthorizedKeysFile __PROGRAMDATA__/ssh/authorized_keys_%u
ForceCommand internal-sftp
ChrootDirectory $Share
AllowTcpForwarding no
PermitTunnel no
X11Forwarding no
# END integrate-io-match
"@
}
Restart-Service sshd
```
## 3. Generate the tunnel keypair, upload to Integrate.io dashboard
Go to Integrate.io dashboard Settings > SSH Public Key and paste the public key
If you have already uploaded a public key from this same machine, skip this step.
```powershell theme={null}
$Dir = "C:\integrateio"
New-Item -ItemType Directory -Force -Path $Dir | Out-Null
ssh-keygen -t rsa -b 4096 -f "$Dir\tunnel_key" -C "integrate-io-tunnel-$env:COMPUTERNAME"
# Hand the key to the service account: SYSTEM and Administrators only
$key = "$Dir\tunnel_key"
takeown /F $key /A
icacls $key /inheritance:r /grant:r "NT AUTHORITY\SYSTEM:R" "BUILTIN\Administrators:R"
icacls $key /remove "$env:USERDOMAIN\$env:USERNAME"
# Copy the public key to the clipboard — paste it into the Integrate.io UI in Step 4
Get-Content "$Dir\tunnel_key.pub" | Set-Clipboard
Write-Host "Tunnel public key copied. Paste it into the Integrate.io SFTP connection's tunnel public-key field."
```
## 4. Create the SFTP connection in Integrate.io
In the Integrate.io UI, create a new **SFTP connection**:
* **Access type:** `reverse`
* **Authentication method**: `Public key authentication`
* **User:** `xplenty-sftp`
* Both **tunnel endpoint** and **public key** would be generated after saving the connection.
## 5. Install the Integrate.io SFTP public key on Windows
Run the script below and paste the SFTP public key generated from the connection on our previous step.
```powershell theme={null}
$sftpPubKey = Read-Host "Paste the SFTP public key from Integrate.io, then press Enter"
$keyFile = "C:\ProgramData\ssh\authorized_keys_xplenty-sftp"
New-Item -ItemType Directory -Force -Path "C:\ProgramData\ssh" | Out-Null
# If updating an existing key, restore write access first (the restrictive ACL below otherwise blocks rewrites)
if (Test-Path $keyFile) {
takeown /F $keyFile /A | Out-Null
icacls $keyFile /grant "Administrators:F" | Out-Null
}
Set-Content -Path $keyFile -Value $sftpPubKey -Encoding ASCII
icacls $keyFile /inheritance:r | Out-Null
icacls $keyFile /grant:r "NT AUTHORITY\SYSTEM:R" "xplenty-sftp:R" | Out-Null
```
## 6. Quick test before making it permanent
Run the tunnel once in the foreground to confirm it connects, then click **Test Connection** in the Integrate.io UI. Press **Ctrl + C** here to stop the tunnel once it passes.
Fill in the two values from Step 4 and paste:
```powershell theme={null}
# --- Replace with the values from the Integrate.io UI ---
$BASTION_HOST = "tunnel.xplenty.com" # replace if endpoint is different
$BASTION_FORWARD = "..." # forwarding port, e.g. 49667
# --- Nothing below needs editing ---
& "C:\Windows\System32\OpenSSH\ssh.exe" -vv -NR "${BASTION_FORWARD}:127.0.0.1:22" `
"sshtunnel@${BASTION_HOST}" `
-p 50683 `
-i "C:\integrateio\tunnel_key" `
-o "ExitOnForwardFailure yes" `
-o "ServerAliveInterval 10" `
-o "ServerAliveCountMax 1" `
-o "StrictHostKeyChecking accept-new" `
-o "UserKnownHostsFile C:\integrateio\known_hosts" `
-N
```
## 7. Run the tunnel as a persistent SYSTEM task
Fill in the **two values** from the Integrate.io UI in Step 4, then paste:
```powershell theme={null}
# --- Replace with the values from the Integrate.io UI ---
$BASTION_HOST = "tunnel.xplenty.com" # replace if endpoint is different
$BASTION_FORWARD = "..." # forwarding port, e.g. 49667
# --- Nothing below needs editing ---
$Dir = "C:\integrateio"
@{ BastionHost = $BASTION_HOST; BastionForward = $BASTION_FORWARD } |
ConvertTo-Json | Set-Content "$Dir\tunnel.config.json" -Encoding UTF8
@'
$cfg = Get-Content "C:\integrateio\tunnel.config.json" -Raw | ConvertFrom-Json
$ssh = "C:\Windows\System32\OpenSSH\ssh.exe"
while ($true) {
& $ssh -NR "$($cfg.BastionForward):127.0.0.1:22" `
"sshtunnel@$($cfg.BastionHost)" `
-p 50683 `
-i "C:\integrateio\tunnel_key" `
-o "ExitOnForwardFailure yes" `
-o "ServerAliveInterval 10" `
-o "ServerAliveCountMax 1" `
-o "StrictHostKeyChecking accept-new" `
-o "UserKnownHostsFile C:\integrateio\known_hosts" `
-N
Start-Sleep -Seconds 5
}
'@ | Set-Content "$Dir\tunnel.ps1" -Encoding UTF8
$action = New-ScheduledTaskAction -Execute "powershell.exe" `
-Argument "-NoProfile -ExecutionPolicy Bypass -WindowStyle Hidden -File `"$Dir\tunnel.ps1`""
$trigger = New-ScheduledTaskTrigger -AtStartup
$settings = New-ScheduledTaskSettingsSet -StartWhenAvailable `
-AllowStartIfOnBatteries -DontStopIfGoingOnBatteries `
-RestartCount 3 -RestartInterval (New-TimeSpan -Minutes 1) `
-ExecutionTimeLimit ([TimeSpan]::Zero)
$principal = New-ScheduledTaskPrincipal -UserId "SYSTEM" -LogonType ServiceAccount -RunLevel Highest
Register-ScheduledTask -TaskName "Integrate.io SFTP Reverse Tunnel" `
-Action $action -Trigger $trigger -Settings $settings -Principal $principal -Force
Start-ScheduledTask -TaskName "Integrate.io SFTP Reverse Tunnel"
```
## 8. Confirm and test
```powershell theme={null}
Get-Process ssh
Get-ScheduledTask -TaskName "Integrate.io SFTP Reverse Tunnel" | Select-Object TaskName, State
```
You should see one `ssh` process and `State: Running`. In the Integrate.io UI, click **Test Connection** — it should pass.
### Path mapping: Integrate.io path vs Windows path
The SFTP user is chrooted into the folder you set in Step 2, so that folder is the SFTP root. In Integrate.io, the **source path always starts at `/`**, which points at that folder on Windows. Do **not** put the Windows drive or the chroot prefix in the Integrate.io path.
| File on Windows (chroot = `C:\ExistingData`) | Source path in Integrate.io |
| -------------------------------------------- | --------------------------- |
| `C:\ExistingData\test.csv` | `/test.csv` |
| `C:\ExistingData\sales\jan.csv` | `/sales/jan.csv` |
| every `.csv` directly under the folder | `/*.csv` |
To verify an actual read, first confirm the file exists under the chroot folder on Windows, then run a one-row test package in Integrate.io with the matching source path:
```powershell theme={null}
Get-ChildItem C:\ExistingData\
```
## Removing everything later
This removes the tunnel, the SFTP user, and the OpenSSH server. A folder you created in Option A is deleted; an existing folder used in Option B is left in place (only the read grant is revoked).
```powershell theme={null}
Unregister-ScheduledTask -TaskName "Integrate.io SFTP Reverse Tunnel" -Confirm:$false
Get-Process ssh -ErrorAction SilentlyContinue | Stop-Process -Force
Stop-Service sshd
Set-Service sshd -StartupType Disabled
Remove-WindowsCapability -Online -Name OpenSSH.Server~~~~0.0.1.0
Remove-LocalUser -Name xplenty-sftp
Remove-Item -Recurse -Force C:\integrateio
# Option A only — WARNING: permanently deletes C:\fileshare-test and every file in it. Back up first.
Remove-Item -Recurse -Force C:\fileshare-test -ErrorAction SilentlyContinue
# Option B only — leave the existing folder, just revoke the read grant:
# icacls "C:\ExistingData" /remove "xplenty-sftp" | Out-Null
Remove-Item -Force C:\ProgramData\ssh\sshd_config -ErrorAction SilentlyContinue
Remove-Item -Force C:\ProgramData\ssh\authorized_keys_xplenty-sftp -ErrorAction SilentlyContinue
```
# ETL: Organize Connections with Connection Groups
Source: https://www.integrate.io/docs/etl/connection-groups
Organize Integrate.io ETL connections into named groups to scope browsing, separate environments, and manage connections by team or project.
Connection groups let you organize related connections under a single named group. Use them to separate connections by team, environment, project, or any other grouping that matches how you work.
A connection can belong to one group at a time. Connections that are not assigned to a group remain available from the main **Connections** list as before.
## When to use connection groups
* You manage many connections and want to scope what you see while browsing.
* Multiple teams or projects share an account and each owns a distinct set of connections.
* You want to separate connections by environment, such as production and staging.
## Create a connection group
Click the **Connections** icon on the main left menu, then open **Groups**.
Click **New connection group**.
Enter a name and an optional description for the group.
Add connections to the group using the connection picker. Connections already assigned to another group are not shown.
Click **Create connection group**.
## Edit or delete a group
From the **Groups** list, use the actions on a group to:
* **Edit** — change the group name, description, or membership.
* **Duplicate** — create a new group with the same settings.
* **Delete** — remove the group. The connections themselves are not deleted; they return to the ungrouped state.
## Move a connection between groups
Open a connection and update its group from the connection form, or edit the target group and add the connection from the picker. Removing a connection from a group does not delete the connection.
## Search and filter
The **Groups** list supports assisted search by group attributes. When you open a group, the connections view is scoped to that group and supports the same search and filter options as the main connections list.
## Restrict a Package Group to specific connection groups
Account admins can bind a [package group](/docs/etl/package-groups) to one or more connection groups so its packages may only reference connections from those groups. Once bound, the connection picker, save-time validation, and job run-time all enforce the restriction. See [Restricting a Package Group to specific Connection Groups](/docs/etl/package-groups#restricting-a-package-group-to-specific-connection-groups) for the API and the rules.
# ETL: Creating a Package from a Template
Source: https://www.integrate.io/docs/etl/create-a-package-from-a-template
Create a new Integrate.io ETL package from a pre-built template. Choose from available templates to quickly start common data pipeline patterns.
When working with one of our more common REST API services, you can utilize one of our available templates to speed up the process of getting your new service connected. The templates often output to a Redshift destination component, but you can easily swap that with a destination of your choice (such as a database, S3, etc.) The templates have the REST API curls already implemented so that you can simply input your credentials (such as username and password) and easily begin pulling your data. If you have trouble, you can always reach out to your designated product specialist for help using the live chat option in the bottom left corner of the dashboard.
**To create a package from a template:**
On the main menu, click **Packages**.
Click the **New package** button.
Click the dropdown menu next to **Template...**
Select the desired template (if necessary, filter the templates list first by typing relevant characters in the text box).
Click **Create package.**
# ETL: Package Creation Guide
Source: https://www.integrate.io/docs/etl/creating-a-new-package
Create a new ETL package using the classic package creation flow in Integrate.io ETL. Configure package settings and define your data pipeline.
**To create a package:**
On the main menu, click **Packages**.
Click **New** **package**.
Name your package. It's a good idea to give it a descriptive name so that you can identify it from a list of packages that you've created.
Optionally, you may add a **Description**. A description detailing what the package does is useful if you have other members of your team that may be accessing this package or if you come back to it a while later to make alterations.
Select **Dataflow** or **Workflow** (for more information on workflows, see [Creating a workflow](/docs/etl/creating-a-workflow/ "Link: /etl/creating-a-workflow/")).
Select a template from the dropdown menu or leave it blank if you want to build from scratch (for more information on utilizing a template, see [Create a package from a template](/docs/etl/create-a-package-from-a-template/ "Link: /etl/create-a-package-from-a-template/")).
Click **Create package.**
Click **Add component** (a package must always start with a source component).
Add additional components as required, connecting them by dragging the dotted-line connections from one component to the next (for more information on packages and components, see [Integrate.io ETL's packages](/docs/etl/integrateio-etls-packages/ "Link: /etl/integrateio-etls-packages/")).
Add a destination component (a dataflow must always end with a destination component).
# ETL: Creating a New Package
Source: https://www.integrate.io/docs/etl/creating-a-new-package-in-integrateio-etl
Create a new data pipeline package in the Integrate.io ETL visual designer. Configure package settings, add components, and build data flows.
## To create a package:
On the main menu, click **Packages** (1).
Click **New package**.
Optionally, set a name and\or description
Select the package **type**:
* **Dataflow** - describes the data to process (location, schema, fields), data manipulation to perform, and the output destinations (location, schema).
* **Workflow** - define dependencies between tasks. For example: after dataflow A completes successfully run dataflow B.
To create a package from a template, select the required template from **Templates** dropdown (if necessary, filter the templates list first by typing relevant characters in the **Filter Templates** text box).
Click **Create package** to create new package.
# ETL: Creating a Workflow
Source: https://www.integrate.io/docs/etl/creating-a-workflow
Create workflow packages in Integrate.io ETL to orchestrate multi-step tasks. Define sequential and conditional task execution with error handling.
## To create a workflow:
On the main menu, click **Packages**.
Click **New package**.
Optionally, set a name and/or description
Select **workflow** option from Type dropdown
Click **Create package** to create workflow package.
Click **+ Add Task** button.
Choose task.
Add additional tasks as required
Connect tasks to create sequence of task execution. Click the connect icon on the dotted line to set the execution condition:
* **On success (default)** - task will be executed once the preceding task was executed successfully
* **On failure** - task will be executed once the preceding task execution failed
* **On completion** - task will be executed once the preceding task completed, regardless to the completion status (failed/succeeded)
## Execute SQL task
Select your DB connection.
Write the SQL query that should be executed, and select the query result type from **Result type** dropdown. You can test the query by clicking **Test Query**.
You can assign the return value to a workflow variable.
If there are at least two preceding tasks, choose task execution condition:
* all preceding conditions evaluate to true (AND)
* one of the preceding conditions evaluate to true (OR)
**Note**:
BigQuery connections uses standard SQL within Execute SQL task
## Run package task
Select the package to run
Optionally, you may edit the dataflow variables if you want to override the values with workflow variables.
If there are at least two preceding tasks, choose task execution condition:
* all preceding conditions evaluate to true (AND)
* one of the preceding conditions evaluate to true (OR)
## File mover task
Select a file storage connection as the source of the file(s)
Input source bucket (if connection requires a bucket) as well as the source path
You can enable following options on the source connection:
* Delete files from source
* Fail if the source is empty
Select a file storage connection as the destination of the file(s)
Input destination bucket (if connection requires a bucket) as well as the destination path. Any directories/folders in the path must already exist. If you wish to change the name of the file, define the path all the way to the file name and extension - for example, directory\_name/new\_file\_name.csv
If there are at least two preceding tasks, choose task execution condition:
* all preceding conditions evaluate to true (AND)
* one of the preceding conditions evaluate to true (OR)
**Note**:
Package variable (\`\$\`) and Wildcard file pattern (\`\*\`) can be applied as bucket or path name.
## Using Variables in Workflows
User variables can be defined at the workflow package level and can be used for both the Execute SQL Task and the Run Package Task.
1. **Execute SQL Task**
* Variables can also be assigned values by the Execute SQL task. This is useful if you want to have dynamic values on your variable and use it later on.
* When using variables in a SQL query, enclose the variable within curly brackets (i.e: `'${var\_name}'`).
* Example of using a variable within the Execute SQL task query:\
DELETE FROM event\
WHERE `date>='${START\_DATE}'`\
AND `date<='${END\_DATE}'`
2. **Run Package Task**
* Workflow package level variables can be used to override dataflow level variables. Values you do not override track the child dataflow's current variables and secrets, so edits made on the child are reflected in the workflow without re-saving. Here's an example:
**Note:**
Take note that we address package variables regularly as **\$variable\_name** which is different from the way we used it on Execute SQL Task above.
* If both workflow and dataflow variables have the same name, you will still have to assign the workflow variable to the dataflow variable.
* If a task dataflow uses a variable that isn't defined at the dataflow level but is assigned a value at the workflow level, the dataflow task will use the workflow variable value.
# ETL: Defining Connections
Source: https://www.integrate.io/docs/etl/defining-connections
Create and manage data connections in Integrate.io ETL. Define access credentials for databases, cloud storage, APIs, and SaaS applications.
First, allow Integrate.io ETL access to the service or data repository. This may require setting up firewall rules, opening SSH tunnels or creating users with minimum required permissions. Read more about allowing Integrate.io ETL access to your data repositories [here](/docs/etl/category/connectivity-and-security/).
Once Integrate.io ETL is allowed access, you should set up a new connection in Integrate.io ETL:
1. Click \`Connections\` icon on the main left menu and then click **New connection**.
2. Click **new connection**.
3. Select your connection type
4. Fill in the required fields.
5. Test your connection and save it.
6. Now you can use your new connection in packages.
# ETL: Dynamic Connections
Source: https://www.integrate.io/docs/etl/dynamic-connections
Use dynamic connections in Integrate.io ETL to parameterize data source and destination settings at runtime with variables and expressions.
**WHAT IS THE DYNAMIC CONNECTION FEATURE?**
Dynamic connection is a new feature which allows you to replace database connections in source, destination components and execute SQL task component on workflow at package runtime. This is a convenient and scalable approach to running jobs across multiple database connections while using a single package! To use this feature, reach out to Integrate.io Support and request it to be enabled on your account!
Overview
1. Set variable for connection on package
2. Set variable for connection on workflow
3. Find connection IDs
4. Update variable value
**Set variable for connection on package**
Open any existing package or create a new one.
Add a database source or destination component
Open the component and specify any available connection
Check the checkbox for “Set variable for connection”
* It is not possible to change the variable name.
* The value of the variable defaults to the connection ID from the connection dropdown.
Configure “Source properties” and “Schema” sections, then “Save” the component.
Configure the rest of the package as needed, then "Save" the package.
**Set variable for connection on workflow**
Open any existing package or create a new one.
Add a SQL task component
Open the component and specify any available connection
Check the checkbox for “Set variable for connection”
* It is not possible to change the variable name.
* The value of the variable defaults to the connection ID from the connection dropdown.
Configure “Query” and “Execute task if” sections, then “Save” the component.
Configure the rest of the workflow as needed, then "Save" the workflow.
**Find connection IDs**
You are ready to override the dynamic connection variable with a valid database connection ID, but first you need to find suitable connection IDs on the Connections page.
Make sure that you only use IDs of database connections, and that the type of database is the same as the type defined in the source / destination component. I.e. for a MySQL database, only specify the ID of another MySQL database connection.
**Update connection variable value**
Replace the dynamic connection variable value with the ID(s) you identified from the connections page.
When running a job, “Run job” in the package designer
“Run job” on the Packages page
“Run job” on the Jobs page
When creating a schedule, “Add package” in the “New schedule” window
When creating a Workflow package, “Run package” task window
**Limitations**
* Dynamic connection is available only on database source, database destination components and execute SQL task component on workflow.
* The connection in the variable must be the same type as the original input connection\
For example, when selecting a MySQL connection from the dropdown in the source component, the dynamic connection variable specified at runtime must also be a MySQL connection
* The schema of the dynamic connection must be the same as the original input connection's (selected in the source / destination component)
# ETL: Getting Started
Source: https://www.integrate.io/docs/etl/getting-started
Get started with Integrate.io ETL for cloud data integration. Learn the platform basics, create your first pipeline, and process data at scale.
Integrate.io ETL’s platform allows organizations to integrate, process, and prepare data for analytics on the cloud. By providing a coding and jargon-free environment, Integrate.io ETL’s scalable platform ensures businesses can quickly and easily benefit from the opportunities offered by big data without having to invest in hardware, software, or related personnel. With Integrate.io ETL, every company can have immediate connectivity to a variety of data stores and a rich set of out-of-the-box data transformation components. This includes building reliable ETL pipelines for SaaS sources like Salesforce, including scheduled Salesforce data pipelines for analytics and reporting. The main terms you will encounter in the Integrate.io ETL documentation include:
## Connection
Connections define the data repositories or services your Integrate.io ETL account can read data from or write data to. The connections contain access information that is stored securely and can only be used by your account's members. For example, you might create a Salesforce connection for a Salesforce data pipeline source and a warehouse connection as your destination.
## Package
An Integrate.io ETL package is a data flow definition. Each package can be either dataflow or workflow.
* **Dataflow-** Integrate.io ETL Dataflow describes the data sources, transformations to perform, and the output destinations (location, schema).
* **Workflow-** Workflows are packages that allow defining dependencies between tasks, such as executing a SQL query or running a dataflow package. You can define the dependencies and the conditions for executing a task, for example--task can be executed only when the previous task was completed successfully. Workflows are commonly used for workflow orchestration, error handling, and operational control of scheduled Salesforce data pipelines.
Once you define a package, you can verify it, and, as in any development lifecycle, fix any errors and re-verify until the package is ready to run as a job on a cluster. This development cycle helps improve data pipeline reliability before you put scheduled pipelines into production. You can create your own package from scratch or use one of our pre-defined templates. To create a package from a template, create a new package and select the desired template from the 'Templates' dropdown.
## Cluster
An Integrate.io ETL cluster is a group of machines (nodes) that is allocated exclusively to your account's users. You can create one or more clusters, and you can run one or more jobs on each cluster. A cluster that you've created remains allocated to your account until you request to terminate the cluster.
Your account includes a free sandbox cluster for testing jobs on relatively small amounts data during the package development cycle. Many teams validate scheduled Salesforce data pipelines in a sandbox cluster before moving workloads to production.
## Job
An Integrate.io ETL job is a process that is responsible for running a specific package on a cluster. The job is a batch process that processes a finite amount of data and then terminates. Several jobs can run the same package simultaneously. When you run a new job, you select the name of the package whose workflow the job should perform, and the cluster on which to run. Jobs are also the unit you’ll use for job monitoring, reviewing run history, and investigating failures, key for monitoring, partial failures, and failed job recovery in scheduled pipelines.
## Getting Started
After signing in to your account, you will be taken to Integrate.io ETL’s dashboard, where you can access all components of Integrate.io ETL: connections, packages, jobs, clusters, schedules, and settings. Schedules and job monitoring are especially useful for operating scheduled Salesforce data pipelines reliably.
The first step would be creating a package, by clicking on the **New package** button (2) under **Packages** (1) and selecting **Dataflow** (3).
You can create your own package from scratch or use one of our pre-defined templates. Templates are packages that capture all of the important information from a particular source and push the data into the determined destination. If you’re building a Salesforce data pipeline, templates can speed up initial setup and standardize common objects and fields.
To create package from a template, create a new package and select the desired template from **Templates** dropdown. Follow the instructions specified on the package notes for changing variable values or adjusting the template to your needs.
In the dataflow UI, select **+Add Component** (4) and choose which data source (5) you would like to pull data from.
After choosing your source component, click in the middle of the striped rectangle and configure this component (6).
Click the dropdown menu and select the connection type. If you haven't created the connection yet, click **+New** to create it.
On the selected connection form, add the required details. Note that for part of the connection types, you should allow Integrate.io ETL access to the service or data repository before creating the connection. This may require setting up firewall rules, starting SSH tunnels or creating users with minimum required permissions. Read more about allowing Integrate.io ETL access to your data repositories [here](/docs/etl/category/connectivity-and-security/ "Link: /etl/category/connectivity_and_security/"). NOTE: If your source isn’t listed in the connections list, you can use the REST API component to connect to most SaaS applications and other data stores that support REST API, and you will likely find a template to help you speed up the process.
After selecting the desired connection, define your source properties and the schema. You will be able to view the detected schema and a preview of the data, which should assist in selecting the desired fields. Previewing data and schema early can reduce downstream partial failures caused by unexpected field types or missing columns.
Hover over the source component and click the **+** sign in the blue bar below (7). You can add transformation and destination components to the flow. Use transformations to manipulate, shape, standardize and enrich your data. Read more about using transformations [here](/docs/etl/category/creating-packages/)
For now, let’s choose the destination of interest (8).
Click the destination component and configure the endpoint. After selecting the target connection and defining the destination properties, you will be able to map the input schema to the target table columns. One important item to note is that clicking the **Auto-fill** button in the upper right corner on schema mapping step will auto-detect the schema of the data pipeline, saving you the time and effort to populate the schema manually. When completed, click **Save**.
In the upper right corner of the package editor, click the checkmark button to **Save and validate** (9) the package. The validation checks the package for design-time errors, and saves the changes made in the package. Read more about validating a package [here](/docs/etl/validating-a-package/ "Link: /etl/validating-a-package/"). After the package completes validation, you can click **Save & Run job** to (10) save the package and run it as a job on a suitable cluster.
In the **Run job** dialog, select the desired cluster. You can select a sandbox (which is free in all accounts and meant for development purposes) or a production cluster. If needed, you can create a new cluster. Read more about creating cluster [here](/docs/etl/creating-a-cluster/ "Link: /etl/creating-a-cluster/").
Then, select the package that should be executed (by default, the current package will be selected), and click **Run job.**
You can monitor the progress and details of each job, and once completed, a sample of the job's outputs. If the job failed, you will be able to see the error messages that caused it to fail.
**Congratulations, you have completed your first job!**
## Related
# ETL: Import or Export Package JSON
Source: https://www.integrate.io/docs/etl/how-do-i-import-or-export-a-packages-json-source-code
Import or export package JSON source code in Integrate.io ETL. Use JSON export for version control, backup, and cross-account pipeline migration.
**Import / Export package JSON**
Each package can be represented as a JSON file that holds components and their settings, package variables, and metadata (version, description). You can export that JSON data or import a JSON of some previous package version if you want to use a version control system like Git. Each package has to be saved and validated before exporting. To export package JSON first open a package, find the Import/Export dropdown and click the Export button. After that, the browser will start a download. The exported JSON file is formatted so it will be easy to track the difference between such files.
To import your own package JSON, open a package and find Import/Export dropdown and click the Import button. You will see the following form. Paste the JSON into the Package JSON input box, and click the "Import" button.
The package will be updated and you will see the changes immediately. It is not necessary for the JSON to be formatted but it should be valid JSON otherwise the JSON import will generate an error. Note that package variables are included in the package JSON, so you will need to manually remove all sensitive information & credentials as needed.
# ETL: Package Version Control
Source: https://www.integrate.io/docs/etl/how-do-i-use-package-version-control
Use package version control in Integrate.io ETL to track changes, compare versions, and revert to previous package configurations when needed.
## Introduction
Often in larger teams, it is very useful to version a package, keep track of a specific package version, schedule a certain package version, and rollback to a specific package version. Integrate.io ETL's package Version Control consists of the following core features:
* Ability to lock package version on schedule
* Ability to create a major version from the package designer
* View / Rollback to previous versions
Please note that only 100 of the last versions are made available, and discuss Version Control with your Integrate.io ETL Account Manager if you would like it enabled on your account. Here is a blog article on [Version Control of Packages](https://www.integrate.io/blog/package-version-control/).
Edits you make in the package designer are autosaved to a private draft and do not create a package version until you save. See [Package Drafts](/docs/etl/package-drafts).
## Create a Major Version from Package Designer
Integrate.io ETL introduces two ways to save a package, Quick Save and Create Major Version both can be found under the Save dropdown (in the Package Designer).
### Quick Save
A Quick Save is your typical save button, which produces a minor version that increases linearly like 2.1, 2.2, 2.3, and so on. This is useful while you're developing the package and doing some tests.
### Major Version
Once everything is tested, you can click Create Major Version, to create a checkpoint of your last working version. This asks for a Version Description - similar to a Git commit message. Each major version increments the number on the left and resets back the minor version to 0 like 2.0, 3.0, 4.0, etc.
After that you will see the following form:
Add a description text and click the "*Create*" button. After creating the version you will see the new version number and description on your package list:
The version number and description are also visible when selecting a package version on schedule form and when rolling back the package to a previous version.
## Preview / Rollback Version
You can preview and rollback any of your packages to an earlier version. Integrate.io ETL provides access to the latest 100 versions. Open the Rollback version form from the Packages page by clicking the version number under the package name:
Or from the Package Designer by clicking the version number under the package name:
After that you will see the following form:
By default, the selected version is the version previous to the current version. To see all the available versions click the dropdown:
Each row shows the version number, description, and author. Two inline tags help you find specific rows:
* **Latest version**: the last saved version of the package. This row appears in the list only when you have an unsaved [draft](/docs/etl/package-drafts) on the package, because the designer canvas is showing your draft instead of the saved version. Packages without a draft continue to hide the latest row.
* **Draft based on this version**: the version your current draft was started from. Opens read-only so you can compare your draft against its starting point.
After you select a version you can preview it or roll back to it.
To preview the version click **Open**. You are redirected to the package designer in read-only mode: you can inspect any component but all designer controls are disabled. Read-only version views never load your draft, so **Open** on the Latest version row always shows the clean saved version even when you have unsaved draft changes.
To roll back the package click **Rollback version**. A rollback produces a minor version with a predefined description:
### Rollback safeguards
Rollback checks the state of the package before it creates a new version and prompts you in the following cases.
**You have an unsaved draft.** Rolling back replaces the running version and clears your draft. The designer shows "Discard your draft and roll back?" before proceeding. Choose **Discard draft & Rollback** to continue, or **Cancel** to keep editing.
**A newer version was saved while the list was open.** If someone saved a new version after you opened the Rollback version form, the designer shows "A newer version exists" listing the new version number. Choose **Refresh list** to reload the versions in place, or **Rollback anyway** to continue with the version you selected.
**You selected the running version.** Rolling back to the version already running would create a byte-identical duplicate, so the rollback is skipped:
* If you have an unsaved draft, the designer shows "Version *X* is already the running version, so rolling back is not needed. Discard your draft to return to it?" Choose **Discard draft** to drop your draft and return to the running version, or **Cancel** to keep your draft.
* If you have no draft, the designer confirms "Version *X* is already the running version" without creating a new version.
Rollback to any older version still creates a new version so the history remains append-only.
## Best Practice: Lock a Package Version on a Schedule
Regardless of the current package version, you can lock your schedule to run any previous version. This enables your team to continue developing on production package, without having to duplicate the package and worry about currently scheduled job runs (being impacted by development changes). How to use the feature:
Go to the Schedules page
Click the New Schedule button
Go to the Packages step and click Add package
On package select form, select a required package
After the package is selected you will see the following screen:
By default, the latest package version is used, to use a specific version uncheck the "*Always run the latest version of the package*" checkbox and select the required version.
You can use the description as a guide for version selection. After you selected the required version and configured the schedule variables click Save. You will see the applied version number right after the package. See the screen below for an example
# ETL: Creating a Delivery
Source: https://www.integrate.io/docs/etl/how-to-create-a-delivery
Create a data delivery pipeline in Integrate.io ETL. Configure source-to-destination data movement with scheduling and transformation options.
## Getting Started with Data Delivery
This guide will help you get your Data Delivery up and running. In this guide, you’ll learn:
* What is data delivery and how does it work
* Data delivery basics, including key terms and concepts
* What to expect in each step of the data delivery process
## What is data delivery
**The Concept**
Integrate.io ETL’s Data Delivery allows you to connect the data source(s) of your choice and replicate that data to any destination. This point-and-click, two-step solution requires no code writing and no technical support - just fast, effective data delivery.
**Terms**
Before you get started, here are some definitions and terms that you should be familiar with:
* **Source**: the data origin - a CRM, database, SaaS application, etc. - that you want to pull data from. (Integrate.io ETL integrates with [over 100 sources](https://www.integrate.io/integrations/), which means that you’ll be able to connect your data no matter where you’re working from)
* **Destination**: a centralized database that houses the data pulled in from your source(s). When Integrate.io ETL delivers your data, it will go directly into the destination of your choosing.
* **Connection**: define the data repositories or services your Integrate.io ETL account can read data from or write data to. The connections contain access information that is stored securely and can only be used by your account’s members.
* **Schedule**: the frequency that you want Integrate.io ETL to replicate the data from the source. For example, if set to once a day, Integrate.io ETL will deliver any new data from the source to the destination every 24 hours. [Read More](/docs/etl/scheduling-package-execution/)
* **Cluster**: an Integrate.io ETL cluster is a group of machines (nodes) that is allocated exclusively to your account’s users. You can create one or more clusters, and you can run one or more jobs on each cluster. [Read more](/docs/etl/integrateio-etls-clusters/)
* **Package**: the pipeline from your source(s) to your destination. Here, you can plan how the data will look like, how move and where it is going.
## The Data Delivery Process
### How does it work
Choose the **source** to deliver the data from and the **destination** to deliver the data to.
**Note**:
If you haven’t created a connection yet, click the yellow **+New** button and follow the instructions for the specific source that you would like to connect to. Each source has different instructions, so make sure that you [follow the correct instructions for your connection](/docs/etl/defining-connections/).
### Frequency
By default, each delivery is scheduled to run once a day. You can always change the frequency by editing the delivery’s schedule directly. [For more information about editing a schedule, click here.](/docs/etl/scheduling-package-execution/)
### Delivery Type
The first execution of data delivery will read all of the data from the source i.e. the “full load”. After that, the delivery will be **incremental**, which means that the tool will only grab any new data that’s come in since your last delivery.
Of course, incremental delivery only works for entities that have a modification timestamp. For objects without a modification timestamp, we’ll run a full load delivery every time. This ensures that your data is always completely up-to-date and that you don’t have to worry about things like missing information or duplicate data.
To learn more about how the data delivery process will work for each of your sources, visit that source’s page [here](/docs/etl/list-of-salesforce-objects/).
### Monitoring the delivery process
Visit the **Jobs** page to track the delivery process, check the status of each entity and monitor which entities have succeeded and which have failed.
# ETL: Ask Helm
Source: https://www.integrate.io/docs/etl/integrateio-ai-assistant
Ask Helm is the in-app AI assistant for Integrate.io ETL, with modes for answering documentation questions and building packages for you.
Ask Helm is the single AI entry point across Integrate.io ETL & Reverse ETL. It answers questions from the Integrate.io documentation and edits the package open in the designer for you. One Ask Helm button in the top nav bar replaces the previous separate AI Assistant and docs Q\&A popups.
## Opening Helm
Open Helm from any page in the app:
* Click **Ask Helm** in the top navigation bar (available on account pages and inside the package designer).
* Press Cmd+K (macOS) or Ctrl+K (Windows and Linux) from anywhere in the app.
* In the [Expression Editor](/docs/etl/using-expressions-in-integrateio-etl), click **Ask Assistant** in the editor header.
Helm opens in Ask mode by default. When you open Helm without a package loaded, only Ask is available; Build appears once you open a package in the designer.
## Modes
The composer footer shows a mode dropdown with three options. Pick a mode from the dropdown or press Shift+Tab in the composer to cycle through them.
| Mode | What it does | When to use it |
| :---- | :--------------------------------------------------------------------------------------------- | :----------------------------------------------------------------------------------------------------------------- |
| Auto | Picks Ask or Build per message based on the wording of your prompt. | Mixed sessions where you switch between questions and edits. |
| Ask | Answers questions from the Integrate.io documentation. Read-only. Never modifies your package. | Learning how a component works, looking up an expression, understanding an error message. |
| Build | Acts on the package currently open in the designer. Adds, wires, and edits components. | "Add a filter after the source", "connect the Salesforce source to the destination", "rename this transformation". |
Auto routes to Ask for questions (prompts that start with words like "how", "what", "why", "can I") and to Build for imperative edits ("add a join", "remove the sort"). Each response shows a small stamp indicating which route ran the turn (Helm Ask or Helm Build).
### Ask mode
Ask searches the Integrate.io documentation and returns an answer with source links. Sources appear under the response so you can jump to the underlying doc page. Ask answers never change your package or account.
### Build mode
Build takes actions on the package open in the designer. Changes are written to your [package draft](/docs/etl/package-drafts), not to the running version, so jobs and schedules keep running the last saved version until you click **Save Version**.
Build requires a package to be open. If Helm is open on an account page (with no package loaded), the composer shows an "open a package to build" handoff instead of sending the message. Auto messages that resolve to Build in this state show the same handoff.
### Auto misroute recovery
When Auto sends a message to Ask that looked like an edit request, the answer includes a **Want me to do this? Run in Build** chip. Click it once to re-run the same prompt through Build without retyping. This only appears on Auto-routed answers; pinning Ask manually opts out of the offer.
## Working with your open package
When you open Helm inside the package designer, the empty state shows the current package name and the state of the canvas (for example, which components are selected). Build acts on that package: any components Helm adds, edits, or removes appear on the canvas with the [Draft Version](/docs/etl/package-drafts) badge and take effect in jobs only after you click **Save Version**.
Ask still works with a package open. Use it to look up documentation without leaving the designer.
## Suggested prompts
The empty state shows a short list of suggested prompts. On account pages the suggestions are documentation questions. In the designer the suggestions are edits tailored to the current package (for example, adding a component of a type you don't have yet). Click a suggestion to drop it into the composer.
## Conversation history
Helm keeps a history of past conversations per user and per account. Open the history panel from the header to browse prior threads, resume a conversation, or delete a thread. Closing the popup mid-draft clears the unsent draft; empty responses do not leave a bubble behind.
## Example prompts
Ask mode:
```text theme={null}
How do I schedule a package to run every hour?
What's the difference between a workflow and a package?
Which functions can I use to parse a JSON string in a Select transformation?
```
Build mode (with a package open):
```text theme={null}
Add a Filter component after the Salesforce source that keeps only rows where Status = 'Active'.
Connect the Select transformation to the Snowflake destination.
Rename the current package to "Daily Salesforce sync".
```
## Availability
Ask Helm is available to all Integrate.io ETL & Reverse ETL accounts. Ask mode works on every page; Build mode requires a package to be open in the designer. Build is enabled on accounts that have the package agent turned on; accounts without it see Ask only, and the mode dropdown is hidden.
Prefer to talk to a human? Use live chat in the platform or email [support@integrate.io](mailto:support@integrate.io).
## Related
# ETL: Best Practices Guide
Source: https://www.integrate.io/docs/etl/integrateio-best-practices-guide
Best practices for designing, scheduling, and operating Integrate.io ETL pipelines: package structure, performance tuning, error handling, and data quality.
Unlock the full potential of Integrate.io with best practices for **Speed & Performance, Security & Compliance, and Scheduling & Automation**.
Our intuitive drag-and-drop interface streamlines pipeline management, reducing manual effort so you can focus on insights and action. Use component previews to test segments in isolation and optimize efficiency.
Need support? Our 24/7 chat team is here, and you can schedule a Success Engineer review for personalized recommendations.
Let's get started. Explore the table of contents to optimize your workflows and elevate your data operations.
This guide covers three pillars: **Speed & Performance** (14 practices), **Security & Compliance** (6 practices), and **Scheduling & Automation** (5 practices). Use the table of contents on the right to jump to any section.
## Pillar I - Speed and performance
A common client request we hear at Integrate.io: "We need to make the pipeline run faster." Understandable - speed matters!
But performance isn't just about speed. Data quality is critical. In datasets with 100,000+ rows, a single error can have a huge impact. That's why testing and debugging best practices are essential, and they keep your pipelines running smoothly and error-free.
### Start with a small data set.
First, let's cover the language of Integrate.io. **Connections** are source and destination locations. This applies to databases, file storage, or Rest API's. **Packages** are data pipelines. A **dataflow** is one pipeline. A **workflow** is a series of tasks – dataflows and SQL queries can be tasks in a workflow.
Integrate.io provides a Component Previewer, which is a fast and easy way to test your data pipeline. Click the yellow "Preview" button and you'll see a small sample of your data. This is the simplest way to verify your data is structured according to any relevant rules or quality control standards. Note when using Component Previewer, it's best to have a Sandbox cluster running as this reduces the runtime of the Component Previewer.
We recommend starting with a small table that's designed for the specific purpose of testing. It's wise to confirm your transformations -- both at the field and table level -- are having the desired effect before applying them to your full data set.
For a deeper dive on this topic, note the documentation linked below:
* [Getting Started](/docs/etl/getting-started)
* [Integrate.io Terminology](/docs/etl/integrateio-etl-terminology)
### Don't forget to save and validate your work.
Have you ever spent hours designing a perfect PowerPoint presentation, forgot to save it, and then had to start from scratch? It happened to me pre-autosave and I still have nightmares.
If you're not careful, you can suffer the same fate with your data pipelines. It's super important to save often or you'll risk losing your hard work. This applies at both the micro and macro level.
**Components** are blocks of code that change or transform your data. Save each component when you're done editing. You can click "..." at the top right to save and validate your pipeline.
To learn more about the tests we perform to validate your work, read the doc below:
* [Validating a Package](/docs/etl/validating-a-package)
### Only pull the data you need (incremental load).
Incremental load is a life-saver when it comes to optimizing your data pipelines for speed.
You only need to do one full sync to pull in all of your existing data. Beyond that point, it's an expensive operation that provides no benefit.
What's the fix? Adjust your package to only pull in new or updated data. This can be achieved with a timestamp that updates anytime a field value changes.
The syntax is simple, especially if you're familiar with SQL and conditional logic. We will use a where clause like this on your database: **updated\_at > '\$last\_updated\_at'::timestamp**
To get more detailed instructions about how to set up an incremental load, read the docs below:
* [How Do I Read Data from File Storage Incrementally?](/docs/etl/how-do-i-read-data-from-file-storage-incrementally)
* [How Do I Move Data Incrementally Between Databases?](/docs/etl/how-do-i-move-data-incrementally-between-databases)
### When and how to use a sandbox cluster.
Integrate.io provides two types of clusters. The **production cluster** is meant to be used with data pipelines in production on a schedule. The **sandbox cluster** should be used during the development and testing process. In addition, the sandbox cluster is a great place to run a historical load if you need to periodically.
A cluster is a group of nodes assigned to your account. One or more jobs can be run on a single cluster. The main difference is scalability. Production clusters can be scaled. The sandbox can't.
If you're testing, stick with the sandbox. After the tests pass, switch to production. Simple.
For more details about ETL testing and both cluster types, read the content linked below:
* [Integrate.io's Clusters](/docs/etl/integrateio-etls-clusters)
* [Creating a Cluster](/docs/etl/creating-a-cluster)
* [ETL Testing Fundamentals](https://www.integrate.io/blog/etl-testing-fundamentals/)
### Offload work to the database or SFDC query (filter).
If you're working with a database or data warehouse and looking for faster runtimes, here's a tip. Many of Integrate.io's transformations can be done via SQL commands in a database. So instead of using a component to join/filter/aggregate data, you can write a SQL query in the database source component that performs those same actions. Then the work of performing those transformations is done by the database instead of our infrastructure. The database is faster, because it has the data right there. No middleman required. If you have an abundance of joins and transformations, put those in a SQL query instead. This will make a dent in your runtime. Bear in mind this strategy requires SQL smarts and the data must live in the same database.
### Labels for easy understanding (naming conventions).
If you don't name a component, Integrate.io will automatically name it for you. That said, you'd be better off with a customized name that fits the component's purpose.
Imagine you have a huge data pipeline with a long list of transformations. One of them involves parsing nested data in JSON. Which name will be more understandable:
**Select\_3**
**Parse\_JSON**
We encourage you to meet with your team and agree to a labeling or naming convention system that is easy to understand for all parties involved.
You can also use our Notes functionality in the package designer to leave details of certain components for you and your team.
### Optimize for large data sets (the power of parallelism).
Two of the most common situations where you might need to optimize for a large volume of data:
1. Calling an API
2. Database component
Let's say you want to get data from one endpoint and push that information to another endpoint. A common example would be grabbing an ID and then fetching information associated with it.
Without parallelism, Integrate.io will call that endpoint sequentially, passing each individual value in one at a time. To speed up this process you can induce parallelism that will allow Integrate.io to split that total number of API calls into five threads per node on the cluster.
A one-node cluster can support five threads. Two nodes takes the total to ten. In other words, parallelism will multiply the speed of your jobs by five. Nifty, right?
We can accomplish the same goal in the database source component by defining a key column and increasing the value of max parallel connections up to ten.
You could also use the sort trick to force parallelism with your Rest APIs. Put another way, add a Sort component to your pipeline (in front of the CURL). Sort uses HADOOP to map, reduce, and parallelize your dataflow. Then you can increase default parallelism\* by about 5-6 per node.
Always test with one node at first. If your data processing volume reaches a level where you need more nodes than allocated to your account, reach out to your AE or Success Engineer.
\* For more info, [consult this doc](/docs/etl/system-and-pre-defined-variables) (under the header: "System Variables").
To see more in-depth instructions on this topic, consult the following Knowledge Base articles:
* [Rest API Parallelism with Integrate.io](https://www.integrate.io/blog/rest-api-parallelism/)
* [Using Components: Database Source](/docs/etl/using-components-database-source)
### Become aware of the tools available (setting variables).
Programmer types are familiar with variables. These are a very useful and efficient feature, especially when you're working with a dynamic value. Integrate.io provides three types of variables:
* User variables (set by you)
* Pre-defined variables (set by Integrate.io)
* System variables (set by you or Integrate.io)
Variables classify as an expression, which means you can use functions or methods to assign a dynamic value, such as a timestamp or ID that changes based on the context.
Dive into the documentation below for a deeper understanding of variables in Integrate.io:
* [Using Expressions in Integrate.io](/docs/etl/using-expressions-in-integrateio-etl)
* [System and Pre-Defined Variables](/docs/etl/system-and-pre-defined-variables)
* [Using and Setting Variables in Your Packages](/docs/etl/using-and-setting-variables-in-your-packages)
### Smooth, simple, and systematic error handling (Workflows).
A workflow is a series of dataflows with conditional logic. Workflows are often used to log errors.
For example: if you're working with an API and the call is unsuccessful, you can push that info to an error logging table for future investigation. The same is true for failed jobs in general.
Workflows can also be used for complex processes. If you need to push data to one table before accessing that data in another package, you can connect them with a workflow.
There are three options for your conditional logic here:
1. On completion (if this job completes, then do this)
2. On success (if this job succeeds, then do this)
3. On failure (if this job fails, then do this)
Learn more about workflows and how they work at the links below:
* [Creating a Workflow](/docs/etl/creating-a-workflow)
* [Integrate.io Workflows: Managing Dependent ETL Tasks Easily and Efficiently](https://www.integrate.io/blog/xplenty-workflows/)
### Over-communicate so everyone's on the same page (Notes).
If you've ever written code, you understand the importance of comments. No matter how perfect your code might be, that doesn't mean it will make sense to the rest of your team.
The same is true for data pipelines. If there are essential details that will help your coworkers understand the package structure, click the yellow note button at the top of your dashboard.
Notes can also be used to plan ahead and troubleshoot issues. For example: "Hey team, I am running into an issue with parsing this nested data. Could you check my syntax for issues?"
Another one: "We need to connect the customer and orders table together here. Can we finalize the list of fields that should be pushed to our database? Thanks!"
For more tips to help you be more productive and efficient with Integrate.io, read the doc below:
* [Working in the Package Designer](/docs/etl/working-in-the-package-designer)
### Create separate environments for staging/production (SDLC).
The software development life cycle is designed to prevent errors and breaking changes.
Developers and engineers understand the importance of testing before deployment. If you push untested code to production, you could make a mistake that breaks the entire app or website.
The same practice should be applied to data pipelines, too. If you push an untested pipeline to production, you run the risk of corrupting your data destination with inaccurate information.
This is a recipe for disaster. It could take hours, days, or weeks to reverse these changes and get your data in good shape. To prevent this nightmare, you need a staging environment.
A staging environment is a great safety net. You could make a hundred mistakes without doing any damage to your production database. This feature is available on our Enterprise plan or can be added as a line item to one of our other plans.
Alternatively some clients use Package Groups to separate their packages into separate stages – Development, Staging, and Production.
We also provide version control, so you can keep track of changes and revert to a previous version as needed. If you want to learn more about testing and version control, note the content below:
* [Creating a Workflow](/docs/etl/creating-a-workflow)
* [Integrate.io and Package Version Control](https://www.integrate.io/blog/package-version-control/)
* [How Do I Use Package Version Control](/docs/etl/how-do-i-use-package-version-control)
* [Pipeline Development Lifecycle Best Practices](/docs/etl/migrate-from-development-to-production)
### One big package vs. several small packages (node load time).
Should you sacrifice speed for simplicity? It depends. If you're building and testing a package, we recommend focusing on one pipeline -- or transfer from source to destination -- at a time.
Otherwise, you'll have a tough time troubleshooting any errors. That said, we encourage you to combine multiple pipelines in a single dataflow package after development to cut job run time.
Set up and tear down of a package is expensive from a usage perspective. After you confirm each pipeline is in good shape, copy/paste the related ones into a package for time savings.
You can also simplify packages and reuse code blocks with workflows as we discussed above.
### Organize data pipelines by team/medium/purpose (Package Groups).
If you're managing a ton of data pipelines with different purposes, your dashboard will get busy. Scrolling through hundreds of packages is a slow process. Let's prevent the inconvenience.
Package Groups are a file and folder system. You can group packages together based on any criteria that matter to you. For example:
* Technology (Google, Facebook, Shopify)
* Team/department (support, sales, marketing)
* Client (ideal for agencies and similar business models)
No matter what your requirements are, you can use Package Groups to keep your team focused. Read more at the Knowledge Base article below:
* [Integrate.io's Package Groups](/docs/etl/package-groups)
### Test in isolation: component preview/expression editor/X-Console.
Testing should be done early and often. Imagine building a data pipeline that includes 10-100 components and not testing until the end. Good luck finding the cause of your error message.
Integrate.io makes it easy to avoid this frustration. We've already covered the component previewer, which shows you a preview of the data. It's an easy way to verify your data is in the right shape.
You can use our expression editor and X-Console for a similar purpose. If the expression editor doesn't like your logic, it won't let you save the result. Dig into our docs to diagnose what's up.
X-Console is similar to a terminal or console.log. Copy/paste your expression, press "Enter," and cross your fingers. If the output doesn't look right, you can experiment until it does.
Click the brackets shaped like HTML code (\<>) on the bottom of the page to open X-Console. This is also a great way to get familiar with the functions and expressions available.
For more information about testing and using expressions in Integrate.io, read the content below:
* [Integrate.io's Functions](/docs/etl/integrateio-etl-functions)
* [Component Previewer](/docs/etl/component-previewer)
* [X-Console: A How-To Guide](https://www.integrate.io/blog/xplenty-x-console/)
## Pillar II - Security and compliance
Being featured on the news is fun (unless it's due to a huge security breach at your company). Who wants to be the star of that headline? Nobody I know. Talk about a hit to your reputation.
All it takes is one compromised password to start a nightmare from which there's no escape. This is why we provide several simple ways to safeguard your most confidential information. Implement the following steps to minimize your risk and become known as a security guru.
### Establish a secure connection to Integrate.io.
We provide four ways to connect your sources and destinations to Integrate.io:
1. Direct (Secure)
2. SSH Tunnel (Super Secure)
3. Reverse SSH Tunnel (Super Duper Secure)
4. External SFTP (Send Your Data to a Cloud Server)
We never save or store your data. We're only involved with the transit of your data.
All four methods include encryption to protect your precious and confidential information.
We'll iron out this detail during onboarding. Ask your success/solutions engineer for more info. You're also welcome to read the in-depth documentation below:
* [Managing Your SSH Public Keys](/docs/etl/managing-your-ssh-keys)
* [Set Up a Reverse SSH Tunnel from Windows](/docs/etl/set-up-a-reverse-ssh-tunnel-from-windows)
Helpful hint: type your source/destination into the Knowledge Base search box to access a doc that walks you through the process of establishing that connection: ***allowing Integrate.io access to*…**
### Require your team to become security aware (2FA).
First: strong passwords are a must. Shoot for twenty characters and a complex structure. Here's a helpful hint -- full sentences are easy for you to remember and hard for hackers to guess.
Two factor authentication is one of the most effective ways to enhance your online security. Imagine one of your employees gets hacked, because they used duplicate passwords.
If you use 2FA, it's a moot point (unless the hacker stole your employee's device -- unlikely!). The log-in must be verified on a second device. Without that, the hack won't be successful.
It only takes a second to set-up and the benefit lasts forever. For more details, read this content:
* [2-Factor Authentication (Knowledge Base)](/docs/etl/2-factor-authentication)
### Enhance security and simplify access with SSO.
Everyone knows reusing passwords isn't ideal, but ensuring your team follows best practices can be a challenge.
While password managers help, Single Sign-On (SSO) offers an easier, more secure solution. Here are the top seven benefits of using SSO.
1. Eliminates password fatigue.
2. Enforces better password policies.
3. Centralized control over who can access the system.
4. Reduces the need for unsafe password management strategies.
5. Boosts overall productivity due to faster log-ins and fewer lost passwords
6. Lowers password-related calls to IT so they can work on more important tasks.
7. Lowers the threat of data breaches by moving ID/authentication data off-premises.
Check out the content below to learn more about SSO and how it benefits overall security:
* [Single Sign On (Knowledge Base)](/docs/etl/single-sign-on)
* [An Overview of Single Sign On Authentication](https://www.integrate.io/blog/benefits-single-sign-on-authentication/)
### Hash or encrypt data your customers want kept private (PII).
Personal identifiable information is a big deal. If you work with phone numbers and addresses, be aware: people expect you to keep that information private. Fail and you'll lose their trust fast.
We provide two ways to secure sensitive information. You could use a hash function to obscure that text and make it unreadable. And if it's unreadable, no one will be able to use that info.
Alternatively, you could encrypt the data while it's in transit and decrypt after it's safe to do so. We partner with the Key Management Service at Amazon Web Services to make this possible.
This is a complex topic, so we recommend reading the content below for a deeper dive:
* [Field Level Encryption](https://www.integrate.io/blog/field-level-encryption/)
* [Encrypt and Decrypt Sensitive Data](/docs/etl/how-do-i-encrypt-and-decrypt-sensitive-data)
* [Integrate.io PII and PHI Transformations](https://www.integrate.io/blog/xplenty-pii-phi-transformations/)
* [PII Pseudonymization Explained in Plain English](https://www.integrate.io/blog/pii-pseudonymization-explained-in-plain-english/)
### Know the privacy laws in your geography and/or industry (HIPAA/GDPR).
Those are scary looking acronyms, so let's break them down to ensure understanding.
The Healthcare Insurance Portability and Accountability Act (HIPAA) has been an important federal law in healthcare since 1996. The goal is to protect sensitive patient information.
Most patient health records are in digital form now. This convenience comes at a steep cost (potential for hacks). Good luck hacking into a filing cabinet. Fail to meet this law's standards and you could be fined up to \$50,000 per violation.
The General Data Protection Regulation (GDPR) is designed to protect the personal or private data of citizens in the European Union (EU). If you thought HIPAA was harsh, brace yourself. Fines for noncompliance run as high as €20 million or 4 percent of annual global revenue.
If either of these laws are relevant to your business model, do yourself a favor and read these:
* [The Ultimate Guide to HIPAA](https://www.integrate.io/blog/the-ultimate-guide-to-hipaa/)
* [The Complete Guide to GDPR](https://www.integrate.io/blog/the-complete-guide-to-gdpr-compliance)
* [Understanding Data Security Compliance Laws](https://www.integrate.io/the-complete-guide-to-data-security/chapter-2/)
* [Common Regulations That Data-Driven Entities Need to Know](https://www.integrate.io/blog/common-data-regulations/)
FYI we can also run your data pipelines in Europe for GDPR using the AWS Ireland region. Notify your success and solutions engineer to take advantage of this customization.
### Be careful about granting admin privileges (User Access Control).
Let's say you've hired a junior data engineer who is enthusiastic and ready-to-go. They have high confidence, but it's somewhat unearned due to their lack of experience.
You ask them to update one record of your production database. Somehow a mistake is made and the update affects every record in the database. As Scooby Doo would say: "Ruh-roh!"
First, this is a prime example of why you need a testing and staging environment. Don't force a junior employee to walk across a tightrope with no safety net. If you do, it's begging for trouble.
Second, you could prevent this situation by only allowing your junior staff to update in staging. Any errors can be rolled back without affecting the production environment. Now it's merely a learning opportunity (rather than a total disaster). You can always update their permissions later.
If we've convinced you of this idea's merit, consult the content below for more in-depth info:
* [Setting Account Member Roles](/docs/etl/setting-account-member-roles)
* [Managing Your Integrate.io Account Members](/docs/etl/managing-your-integrateio-etl-account-members)
* [Why User-Level Security Is Crucial for Business Intelligence](https://www.integrate.io/blog/why-user-level-security-is-crucial-for-bi/)
## Pillar III - Scheduling and automation
Your data pipelines are fast. Your work process is 100% optimized. Your security is tightened. Congrats! You've already made a ton of progress. But you're not a true power user yet…
Don't feel bad. There's one step left. And it's the easiest one. You already did the hardest part. All that's left? Remove some manual work. Let's simplify and automate as much as possible. Don't skip this necessary step, because it could save you hundreds of hours in the long-run.
### Clone a similar data pipeline to save time.
There's no good reason to reinvent the wheel. If you're using the same transformations in several packages, you might as well copy/paste those code blocks to save some time.
All you have to do is draw a selection marquee around the particular components you want to copy and use the keyboard shortcut to copy them (ctrl or cmd + c). And then use the keyboard shortcut to paste them into a similar package. Update the field names and ta-da, you're done.
If there's only one component to copy and you want to use it in the same package: hover over the component you want to copy and then click the overlapping squares icon that shows up.
If you run an agency style business and are building similar pipelines for several clients: click the three dots next to the package name in your dashboard and select, "Duplicate package." Want more information on the benefits of automation? Read the article linked below:
* [Why You Need a Fully Automated Data Pipeline](https://www.integrate.io/blog/why-a-fully-automated-data-pipeline/)
### Check our templates before building from scratch.
We're proud to provide a wealth of data pipeline templates that make our clients' lives easier. Click "New Package" in the package dashboard and then select a template for your dataflow.
Below is a list of the popular services (and their most common use cases) we have covered:
* Adroll
* Asana
* Close.io
* Freshdesk
* Gitlab
* Google Sheets
* Hubspot
* Intercom
* Jira
* LinkedIn Ads
* Mailchimp
* Marketo
* Mixpanel
* Outbrain
* Pardot
* Pipedrive
* Recurly
* Sendgrid
* ShipStation
* Shopify
* Sparkpost
* Stripe
* Taboola
* Trello
* Twilio
* Wrike
* Xero
* Yahoo
* YouTube
* Zendesk
If you know any other data types who work with these services and want to help them be a more productive and valued team member, feel free to send them our way.
If you'd like to see us produce a template that isn't listed here, contact your Success Engineer. They'd be happy to pass your feedback along to the dev team.
We also provide account reviews as a part of your subscription and this is a great opportunity to optimize for speed and security.
[Schedule your call here](https://calendly.com/d/gqw-rds-b3h/customer-success-call).
### Find the right flow or frequency for you (Schedules)
Do you want to run your jobs manually forever? No way. You have other data pipelines to build. Let's automate your jobs so you can focus on more important tasks.
Schedules may run at any frequency you desire. You can set them to run every x-amount of minutes, hours, days, weeks, or months. We also provide CRON expressions for specificity.
For example, you could use this expression to run a schedule every morning at 8 UTC: **0 8 \* \* \***
A schedule may contain one or several packages. It's totally up to you. You're also welcome to customize the amount of nodes used. We advise starting with one and moving up as required.
Learn more about scheduling and automating your most important dataflows at the links below:
* [Scheduling Package Execution](/docs/etl/scheduling-package-execution)
* [Scheduling with CRON Expressions in Integrate.io](https://www.integrate.io/blog/scheduling-with-cron-expressions/)
### Get notified ASAP if a job or package fails (Service Hooks).
If you click the gear shaped icon in your dashboard, you'll be taken to your account settings. This is where you can set up a service hook to notify you of job or cluster related events.
The most common use of a service hook is setting up a notification to alert you of failed jobs. This notification could be sent via Slack, email, Webhook, or PagerDuty.
With a service hook in place, you'll be empowered to diagnose bugs or issues as they arise.
For more details RE: how to monitor your work in Integrate.io, check out the documentation below:
* [Service Hooks](/docs/etl/service-hooks)
* [Monitoring Jobs](/docs/etl/monitoring-jobs)
* [Web and Email Notifications](/docs/etl/viewing-and-setting-web-and-email-notifications)
### Plan ahead for API keys or values that change (User Variables).
User variables are super helpful when you're working with usernames, passwords, API keys, and dynamic values (such as an ID that updates on subsequent runs of a job or package).
We recommend testing with a hard-coded value to confirm your data pipeline is operating right. Afterwards, you can replace that value with a variable to simplify and streamline your process. For more information about how to use and set user variables in Integrate.io, consult this doc:
* [Using and Setting Variables in Your Packages](/docs/etl/using-and-setting-variables-in-your-packages)
# ETL: ETL Terminology
Source: https://www.integrate.io/docs/etl/integrateio-etl-terminology
Learn the key terms and concepts used throughout Integrate.io ETL documentation, including packages, clusters, jobs, connections, and pipelines.
### Connection
A connection defines the endpoint and credentials needed to connect to a data repository.
### Package
An Integrate.io ETL package is a dataflow or a workflow definition. Dataflows describe the data to process (location, schema, fields), data manipulation to perform, and the output destinations (location, schema). Workflows define dependencies between tasks. For example: after dataflow **A** finishes successfully run dataflow **B**. Once the package is defined, it is run as a job on a cluster.
### Cluster
An Integrate.io ETL cluster is a group of machines (nodes) that is allocated exclusively for your account's users. You can create one or more clusters, and you can run one or more jobs on each cluster. A cluster that you've created remains allocated to your account until you request to terminate the cluster.
### Job
An Integrate.io ETL job is a process that is responsible for running a specific package on a cluster. The job is a batch process that processes a finite amount of data and then terminates. Several jobs can run the same package simultaneously. When you run a new job, you select the name of the package to execute and the cluster on which to execute it.
### Account and User
An Integrate.io ETL account represents a related group (usually a company) of Integrate.io ETL users connected to a specific provider/region. An account is created when a user signs up to the Integrate.io ETL service. An account is linked to a region, which is where clusters will be created and jobs will execute. The region should, therefore, be where your data is.
# ETL: Clusters Overview
Source: https://www.integrate.io/docs/etl/integrateio-etls-clusters
Learn about Integrate.io ETL clusters, the compute environments that run your data pipeline jobs. Understand cluster types, sizing, and lifecycle.
When you create a new cluster, you can choose from different cluster sizes (the number of nodes) according to the computing power you require for different types of jobs and packages (if you have a contract that allows you to do so). Generally speaking, the relationship between the number of nodes and computing power is linear: if a job takes four hours to run a four-node cluster, it will take one hour to run on a 16 node cluster. Integrate.io is built to handle large data volumes at scale. By distributing processing across multiple nodes in a cluster, even pipelines moving billions of rows can be completed efficiently without manual infrastructure management or custom code. All accounts include one free sandbox cluster for testing jobs on relatively small amounts of data during the package development cycle. Since the sandbox cluster is single-node and neither scalable nor highly available, it is not production-grade. Trial users who haven't signed a contract yet have unlimited access to a sandbox cluster and can run up to 4-node production clusters max of 200 prod hours (with more hours available upon request) during the trial period.
# ETL: Packages Overview
Source: https://www.integrate.io/docs/etl/integrateio-etls-packages
Learn about Integrate.io ETL packages, the core building blocks for data pipelines. Understand package types, components, and design principles.
Once you define a package, you can verify it, and, as in any development lifecycle, fix any errors and re-verify until the package is ready to run as a job on a cluster.
Click the following links for information on using packages:
* [Creating a new package](/docs/etl/creating-a-new-package/)
* [Creating a new package from a template](/docs/etl/create-a-package-from-a-template/ "Link: /etl/create-a-package-from-a-template/")
* [Working in the package designer](/docs/etl/working-in-the-package-designer/)
* [Using and setting variables in your packages](/docs/etl/using-and-setting-variables-in-your-packages/ "Link: /etl/using-and-setting-variables-in-your-packages/")
* [Validating a package](/docs/etl/validating-a-package/)
* [Using pattern matching in source component paths](/docs/etl/using-pattern-matching-in-source-component-paths/)
* [Using ISO 8601 date/time functions](/docs/etl/using-iso-8601-string-functions/)
* [Using functions in components](/docs/etl/integrateio-etl-functions/)
* Components:
| Component | Description |
| ----------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| [Amazon Redshift Source](/docs/etl/using-components-amazon-redshift-source/) | Read data stored in an Amazon Redshift table, view or using a query. |
| [Bing Ads Source](/docs/etl/using-components-bing-ads-source/) | Read Bing Ads report data. |
| [Database Source](/docs/etl/using-components-database-source/) | Read data stored in a database table, view or using a query. |
| [Facebook Ads Insights Source](/docs/etl/using-components-facebook-ads-insights-source/ "Link: /etl/using-components-facebook-ads-insights-source/") | Read Facebook Ads Insights reports data. |
| [File Storage Source](/docs/etl/using-components-file-storage-source/ "Link: /etl/using-components-file-storage-source/") | Read data stored in a file or multiple files in object stores such as Amazon S3, Google Cloud Storage or Azure Blob Storage or file servers such as SFTP. |
| [Google Ads source](/docs/etl/using-components-google-ads-source/ "Link: /etl/using-components-google-ads-source/") | Read Google Ads report data. |
| [Google Analytics Source](/docs/etl/using-components-google-analytics-source/ "Link: /etl/using-components-google-analytics-source/") | Read Google Analytics report data. |
| [Google Analytics (GA4) Source](/docs/etl/using-components-google-analytics-4-ga4-source/) | Read Google Analytics 4 (GA4) report data. |
| [Google BigQuery Source](/docs/etl/using-components-google-bigquery-source/ "Link: /etl/using-components-google-bigquery-source/") | Read data stored in a Google BigQuery table or using a query. |
| [Google Cloud Spanner Source](/docs/etl/using-components-google-cloud-spanner-source/ "Link: /etl/using-components-google-cloud-spanner-source/") | Read data stored in a Google Cloud Spanner table or using a query. |
| [MongoDB Source](/docs/etl/using-components-mongodb-source/ "Link: /etl/using-components-mongodb-source/") | Read data stored in a MongoDB collection. |
| [NetSuite Source](/docs/etl/using-components-netsuite-source/ "Link: /etl/using-components-netsuite-source/") | Read NetSuite standard and custom records (tables) using the NetSuite JDBC drivers (SuiteAnalytics Connect). |
| [Salesforce source](/docs/etl/using-components-salesforce-source/ "Link: /etl/using-components-salesforce-source/") | Read Salesforce sales cloud standard and custom objects using the Bulk API. |
| [Rest API Source](/docs/etl/using-components-rest-api-source/ "Link: /etl/using-components-rest-api-source/") | Read data from HTTP endpoints such as Rest Web Services. Use the Rest API source component to define the authentication method, request parameters and response fields to use in the package. |
| [Aggregate Transformation](/docs/etl/using-components-aggregate-transformation/ "Link: /etl/using-components-aggregate-transformation/") | Use the Aggregate transformation to group the input dataset by one or more fields and use aggregate functions such as Count, Average, Minimum, Maximum, etc. |
| [Assert Transformation](/docs/etl/using-components-assert-transformation/ "Link: /etl/using-components-assert-transformation/") | Use the Assert transformation to make sure that all data in the source complies with the conditions you specify in the component. If a record does not comply, the job fails and a message is added to the error log. |
| [Clone transformation](/docs/etl/using-components-clone-transformation/ "Link: /etl/using-components-clone-transformation/") | Use the Clone component to split a dataflow into two dataflows in order to apply multiple transformations to the same data. |
| [Cross Join Transformation](/docs/etl/using-components-cross-join-transformation/ "Link: /etl/using-components-cross-join-transformation/") | Use the Cross Join transformation to combine records from two different inputs. The cross join returns the Cartesian product of records from the two inputs. That is, it will produce records that combine each record from the left input with each record from the right input. |
| [Distinct Transformation](/docs/etl/using-components-distinct-transformation/ "Link: /etl/using-components-distinct-transformation/") | Use the Distinct transformation to filter out duplicate records that have the same values in all fields, leaving only unique records. For example, you might need to filter out users' double-clicks in events. |
| [Filter Transformation](/docs/etl/using-components-filter-transformation/) | Use the Filter transformation to filter input data by defining conditions that must be met by records in the input. |
| [Join Transformation](/docs/etl/using-components-cross-join-transformation/ "Link: /etl/using-components-cross-join-transformation/") | Use the Join transformation to combine records from two different inputs. The join component can be used to add information from one data source to another data source or to filter data that exists in both data sources or exists in only one of them. |
| [Limit Transformation](/docs/etl/using-components-limit-transformation/ "Link: /etl/using-components-limit-transformation/") | Use the Limit transformation to limit the number of records in the output for the entire dataset or per partition or group within the data set. |
| [Rank Transformation](/docs/etl/using-components-rank-transformation/) | Use the Rank component to sort input data by one or more fields, in an ascending or descending order and add a rank field that reflects the sort order. |
| [Select Transformation](/docs/etl/using-components-select-transformation/ "Link: /etl/using-components-select-transformation/") | Use the Select transformation to choose which fields from the input will be available in the next component and transform them using [expressions](/docs/etl/using-expressions-in-integrateio-etl/ "Link: /etl/using-expressions-in-integrateio-etl/") in order to parse input data, enrich it, extract information from it or manipulate it. |
| [Sort Transformation](/docs/etl/using-components-sort-transformation/) | Use the Sort component to sort input data by one or more fields, in an ascending or descending order. |
| [Union Transformation](/docs/etl/using-components-union-transformation/) | Use the Union transformation to combine records from two inputs with the same schema (same fields and data types). |
| [Window Transformation](/docs/etl/using-components-window-transformation/) | Use the Window component to apply window functions to incoming data, similar to window functions in SQL. These functions let you rank or distribute data, provide moving averages, running totals and other useful data. The output of the Window component contains all records and fields from the input data flow with the addition of the calculated window functions. |
| [Sample Transformation](/docs/etl/using-components-sample-transformation/ "Link: /etl/using-components-sample-transformation/") | Use the Sample component to return a percentage of random records from the input |
| [Cube transformation](/docs/etl/using-components-cube-transformation/ "Link: /etl/using-components-cube-transformation/") | Use the Cube and Rollup component to group the input dataset by combinations of fields and use aggregate functions such as Count, Average, Minimum, Maximum, etc. |
| [Amazon Redshift Destination](/docs/etl/using-components-amazon-redshift-destination/) | Use the Cube and Rollup component to group the input dataset by combinations of fields and use aggregate functions such as Count, Average, Minimum, Maximum, etc. |
| [Database Destination](/docs/etl/using-components-database-destination/) | Use the database destination component to store the output of a data flow in a relational database table. |
| [File Storage Destination](/docs/etl/using-components-file-storage-destination/) | Use the File storage destination component to store the output of a data flow into files in a designated directory on a file server (SFTP, HDFS) or object store (Amazon S3, Google Cloud Storage, Azure Blob Storage). |
| [Google BigQuery Destination](/docs/etl/using-components-google-bigquery-destination/) | Use the Google BigQuery destination component to store the output of a data flow in a BigQuery table. |
| [Google Spanner Destination](/docs/etl/using-components-google-spanner-destination/) | Use the Google Spanner destination component to store the output of a data flow in a Google Spanner table. |
| [MongoDB Destination](/docs/etl/using-components-mongodb-destination/) | Use the MongoDB destination component to store the output of a data flow in a MongoDB collection. |
| [Salesforce Destination](/docs/etl/using-components-salesforce-destination/) | Use the Salesforce destination component to store the output of a data flow in Salesforce Sales cloud object. |
| [Snowflake Destination](/docs/etl/using-components-snowflake-destination/) | Use the Snowflake destination component to store the output of a data flow in a Snowflake table. |
| [Salesforce SOAP Destination](/docs/etl/using-components-salesforce-destination/) | Use the Salesforce SOAP destination component to store the output of a data flow in Salesforce Sales cloud object using Salesforce SOAP connection. |
| [Netsuite SOAP Destination](/docs/etl/using-components-netsuite-soap-destination/) | Use the Netsuite SOAP destination component to store the output of a data flow in Netsuite cloud object using Netsuite SOAP connection. |
| [Facebook Ads Destination](/docs/etl/using-components-facebook-ads-destination/) | Use the Facebook ads destination component to store the output of a data flow in Facebook ads cloud object. |
| [Google Ads Destination](/docs/etl/using-components-google-ads-destination/) | Use the Google ads destination component to store the output of a data flow in Google ads cloud object. |
| [Tiktok Ads Destination](/docs/etl/using-components-tiktok-ads-destination/) | Use the Tiktok destination component to store the output of a data flow in Tiktok ads cloud object. |
| [HubSpot Destination](https://www.integrate.io/support/#documentationetl/using-components-hubspot-destination) | Use the HubSpot destination component to store the output of a data flow in HubSpot cloud object. |
# ETL: Package Drafts
Source: https://www.integrate.io/docs/etl/package-drafts
How package drafts autosave your unsaved changes in the package designer, per user, until you publish them with Save Version.
## Overview
A package draft is a private, automatically saved working copy of a package. As you edit a package in the designer, your changes are saved to your draft roughly every two seconds. The draft never affects the running version of the package: jobs, schedules, and teammates continue to use the last saved version until you click **Save Version**.
Each user has their own draft per package. Your draft is private: teammates editing the same package work in their own drafts, and a package is only flagged as having unsaved changes for the user who made them.
## How It Works
| Behavior | Details |
| :---------------- | :-------------------------------------------------------------------------------------------------------------------------- |
| Autosave timing | Changes are saved to your draft about two seconds after you stop editing. Leaving the designer saves the draft immediately. |
| What is saved | Components, connections, package variables, and the package name and description. |
| What is not saved | Secret variable values are not stored in drafts. They are committed only when you save a version. |
| Scope | One draft per user per package. Drafts are private to the user who created them. |
| Running version | Jobs and schedules always run the last saved version. Drafts never change a running or scheduled pipeline. |
| Version history | Draft autosaves do not create package versions. Version history records only explicit saves. |
Actions that update your draft include adding, editing, moving, or removing components, connecting or disconnecting components, renaming the package or editing its description, changing package variables, and undo or redo.
Because drafts are saved continuously, the designer no longer warns about unsaved changes when you navigate away. Your work is already saved in your draft and is restored the next time you open the package.
## Draft Indicators
When you have unsaved draft changes, Integrate.io ETL shows a **Draft Version** indicator in two places:
* **In the package designer**: a Draft Version badge appears next to the package name. Its tooltip reads "You have unsaved changes. Save Version to make it the running version."
* **In the package list**: the package shows a Draft Version tag with the version number your draft is based on, for example "Draft Version 5".
The version indicator in the designer shows the version your draft was created from, not the latest saved version.
## Resuming a Draft
When you open a package that has one of your drafts, the designer loads the last saved (running) version and shows a dialog titled **You have an unsaved draft**:
| Option | What it does |
| :---------------------- | :--------------------------------------------------------------------------------------------------------------------- |
| Resume draft | Loads your draft into the designer so you can continue editing. |
| Open last saved version | Keeps the running version loaded. Your draft is preserved and the prompt reappears the next time you open the package. |
The dialog does not dismiss on backdrop click or Esc: it closes only when you pick one of the two buttons, so a draft is never discarded by accident. Read-only version views never load drafts and never show the prompt.
## Saving a Version
Clicking **Save Version** publishes your draft as the new running version of the package:
Make your changes in the package designer. They are autosaved to your draft.
Click **Save Version** to save your draft as the new running version, or **Save & Run job** to save and immediately run the package.
The draft becomes the new package version, your draft is cleared, and the Draft Version badge disappears.
Package variables follow the same flow: variable changes are stored in your draft and applied when you save a version.
### Run, Save & Run, and Save Version
The three action buttons in the designer toolbar act on different versions of the package:
| Button | Acts on | When to use |
| :------------- | :---------------------- | :------------------------------------------------------------------------------------------------- |
| Run job | The last saved version. | You have no unsaved changes, or you want to run the running version without publishing your draft. |
| Save & Run job | Your draft. | You have unsaved changes you want to publish and run in a single step. |
| Save Version | Your draft. | You want to publish your draft as the new running version without running a job. |
When you click **Save & Run job** with unsaved changes, the designer asks you to confirm with the message "Your draft will be saved as a new version, then run." Confirming saves and runs; cancelling returns you to editing. **Run job** never shows a confirmation and never publishes your draft.
## Discarding a Draft
To throw away your unsaved changes and revert to the last saved version:
In the package designer toolbar, click **Discard Draft** next to the action buttons.
Confirm in the dialog. The designer reverts to the last saved version and shows "Draft discarded. Reverted to the last saved version."
The **Discard Draft** button is available to any account member with edit permission on the package, including accounts that do not have package version control enabled. Discarding a draft removes only your own draft. It does not affect the saved package or any teammate's draft.
## Working with Teammates
Because drafts are per user, several people can edit the same package at once without overwriting each other's work in progress. Conflicts are handled at save time:
### A teammate saves a version while you are editing
If a newer version of the package was saved after your draft was created, clicking **Save Version** shows a dialog: "A newer version was saved while you were editing." You can choose:
| Option | What it does |
| :---------------------- | :------------------------------------------------------------------------------------------------ |
| Continue & Save Version | Publishes your draft anyway, replacing the newer version as the latest. |
| View Latest Version | Opens the latest saved version read-only in a new browser tab so you can compare before deciding. |
| Copy to New Package | Saves your draft as a brand-new package, leaving the original package untouched. |
| Discard Draft | Deletes your draft and reverts to the latest saved version. |
### A teammate saves a version while your editor is clean
If you have no unsaved changes when a teammate publishes a new version, the designer shows a "Package updated" notice with two options: **Refresh** to load the new version, or **Keep editing** to stay on the version you have open.
### Copying a draft to a new package
**Copy to New Package** creates a new package from your draft content. The new package is named "Copy of" followed by the original package name, and the original package and your draft on it are left unchanged. This is useful when your draft has diverged too far from the latest saved version to merge.
## Duplicating a Package with a Draft
Duplicating a package always copies the last saved version, not your draft. If you duplicate a package while you have unsaved draft changes, a dialog warns: "Duplicate copies the last saved version. Your unsaved draft changes on this package won't be included."
## Drafts and Ask Helm
Edits made by [Ask Helm](/docs/etl/integrateio-ai-assistant) in Build mode and by the [Integrate.io MCP tools](/docs/etl/creating-packages-with-mcp-tools) are written to your draft, not to the running version. AI-generated changes appear on the canvas with the Draft Version badge, and take effect in jobs and schedules only after you click **Save Version**. Package validation also checks your draft when one exists, so you can validate AI or manual edits before publishing them.
## FAQ
**Q: Can my teammates see my draft?**
No. Drafts are private to each user. A package shows the Draft Version tag only to the user who has unsaved changes on it.
**Q: What happens to my draft if I close the browser without saving?**
Nothing is lost. Drafts are saved on the server, so your changes are restored automatically the next time you open the package.
**Q: Do drafts affect scheduled jobs?**
No. Schedules and manually run jobs always use the last saved version of the package. Draft changes take effect only after you save a version.
**Q: Does autosaving create package versions?**
No. Draft autosaves are not recorded in version history. A new version is created only when you click Save Version.
**Q: Are secret variables stored in my draft?**
No. Secret variable values are excluded from drafts and are committed only when you save a version.
**Q: What happens to drafts when a package or user is deleted?**
Deleting a package removes all drafts on it. Removing a user from the account removes that user's drafts.
## Related
# ETL: Package Groups
Source: https://www.integrate.io/docs/etl/package-groups
Learn about Integrate.io ETL package groups for organizing packages, connections, and team resources. Separate environments for your data projects.
## What is an Integrate.io ETL Package Group?
A Package Group provides a way to group and organize packages together. Think of Package Groups like folders. You can group pipelines by team if there are many users using the same Integrate.io ETL account, or group pipelines by customer, or if you simply want to have some template packages you’d like to go back to in the future. You could use an Integrate.io ETL Package Group to group packages together based on your requirements. You can have a look at our Package Groups feature by clicking this new icon we’ve introduced.
## Creating a Package Group
To create a Package Group, navigate to the Package Groups tab and click **New Package Group**. A modal will then pop up (see image below). Give the Package Group a name and add a description if so desired. You can also add packages to this Package Group if you already have some packages in mind, otherwise you can just click **Create Package Group** and add packages later.
## Adding Packages to a Package Group
After Package Group creation, you can add packages to your Package Group by clicking the triple dot icon on the Package Group and clicking **Edit Package Group.** You can then click the dropdown and add packages to the Package Group. Take note that only packages that do not already belong to a Package Group can be added - i.e. a given package can only belong to one Package Group. Click the triple dot icon, and click **Edit Package Group** and add packages accordingly.
## Moving a Package to Another Package Group
If you would like to move a single package to another Package Group, you can go to the **Packages** tab, click the triple dot icon, and click **Change Package Group**. Packages not belonging to any Package Group would have **Add to Package Group** instead.
For moving packages to another Package Group in bulk, you can remove the packages from a Package Group by clicking **Edit Package Group**, removing the relevant packages, and saving it. You can then edit another Package Group by using the same **Edit Package Group** button and add the packages.
## Restricting a Package Group to Specific Connection Groups
By default a package group can reference any connection in the account. Account admins can bind a package group to one or more [connection groups](/docs/etl/connection-groups) so packages in the group may only reference connections from those groups. Use this to keep production packages isolated from sandbox connections, separate one team's connections from another's, or enforce environment boundaries between package groups.
A package group with no binding is **unrestricted** and continues to see every connection in the account. Adding at least one binding switches the package group to **restricted** and applies these rules to every package in the group:
* The connection picker in the package designer lists only connections from the bound connection groups.
* Saving a package that references a connection outside the bound groups returns a 422 error naming the offending connection. This applies to sources, destinations, execute SQL, and Database Lookup components (including the bare `database_connection_id` and `cloud_storage_connection_id` fields on Database Lookup).
* Cloning a package into a restricted package group is blocked when the source references an out-of-bounds connection.
* Connections that do not belong to any connection group are treated as out of bounds in a restricted package group.
* Running an existing package that references an out-of-bounds connection fails at run time. Packages last saved before the binding was applied are grandfathered until they are next edited.
Clearing every binding returns the package group to unrestricted.
### Configure Connection Access on a Package Group
The **Connection access** picker is shown on the package group form when you have `manageConnectionAccess`.
Open the package group in **Package Groups**, or start a new one with **New Package Group**.
Scroll to **Connection access**. Use the picker to add one or more connection groups. Each added group is listed below the picker with a remove button.
Save the package group. The binding replaces the previous set; leaving the list empty saves the package group as unrestricted.
### Connection Access Audit Page
**Settings > Developer > Connection Access** is a read-only, account-wide overview of every package group with its binding state and a count of connections currently out of bounds. Use it to spot restricted package groups whose packages still reference connections outside the binding, then open the offending package group to fix the binding or the package.
Only bound (restricted) package groups are scanned. Unrestricted package groups show as **No restrictions**.
Access is gated on `manageConnectionAccess`. Members without the permission see a "not authorized" message.
### Permissions
Editing a package group's binding is an account-governance action gated on the dedicated `manageConnectionAccess` permission. Owners and Admins have it by default. On accounts using [custom roles](/docs/etl/custom-roles), grant `manageConnectionAccess` to any additional role that should manage bindings. `updateWorkspace` alone does **not** grant it: a package-group editor cannot silently change which connections the group's members can reach.
### Managing bindings from the API
The binding is exposed on the account API v2 under a workspace. All calls require an admin API key.
Fetch the current binding for a package group:
```bash theme={null}
curl -u API_KEY: \
-H "Accept: application/vnd.xplenty+json; version=2" \
-H "X-Account-Id: ACCOUNT_ID" \
https://api.integrate.io/api/v2/workspaces/WORKSPACE_ID/connection_group_bindings
```
The response returns `connection_group_ids` (the bound connection group IDs) and `unrestricted` (true when the array is empty).
Replace the binding set. This call uses full replace-set semantics: rows not present in `connection_group_ids` are removed, and new IDs are added. Submit an empty array to clear every binding.
```bash theme={null}
curl -u API_KEY: \
-H "Accept: application/vnd.xplenty+json; version=2" \
-H "Content-Type: application/json" \
-H "X-Account-Id: ACCOUNT_ID" \
-X PUT \
-d '{ "connection_group_ids": [12, 34] }' \
https://api.integrate.io/api/v2/workspaces/WORKSPACE_ID/connection_group_bindings
```
Cross-account connection group IDs are rejected with a 422 rather than silently dropped.
List the out-of-bounds connection references currently held by packages in a restricted package group:
```bash theme={null}
curl -u API_KEY: \
-H "Accept: application/vnd.xplenty+json; version=2" \
-H "X-Account-Id: ACCOUNT_ID" \
https://api.integrate.io/api/v2/workspaces/WORKSPACE_ID/connection_binding_violations
```
Each violation names the package, the referenced connection, and the connection group that connection belongs to. Use this report to find packages you need to re-point, or connection groups you need to add to the binding, before a scheduled run hits the run-time check.
For an account-wide overview of every package group with its binding state and violation count, call:
```bash theme={null}
curl -u API_KEY: \
-H "Accept: application/vnd.xplenty+json; version=2" \
-H "X-Account-Id: ACCOUNT_ID" \
https://api.integrate.io/api/v2/workspaces/connection_access_audit
```
## Deleting a Package Group
You can delete a Package Group by clicking the triple dot icon on the Package Group and clicking **Delete Package Group**. Deleting a Package Group will not delete the packages under the Package Group. These packages will no longer belong to a Package Group.
## Package Group Summary Bar
A summary bar on each Package Group is shown which indicates the following job-related information.
**Note**:
Archived packages are excluded from all counts in the summary bar.
* The leftmost number represents the total number of active (non-archived) packages under the Package Group
* ❌ Red cross indicates the number of packages failed on last run
* ⏸️ Gray Pause symbol indicates the number of packages stopped on last run
* ✅ Green tick indicates number of packages completed on last run.
This could be helpful if the packages underneath are being run regularly (via an Integrate.io ETL schedule for example) as it would quickly give insights on whether all the jobs succeeded during the last run, or if a package failed.
# ETL: AlloyDB Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-alloy-cdc-source
How to configure the AlloyDB Snapshot CDC source component to read changed records from AlloyDB in your Integrate.io ETL pipeline.
Use the Alloy (Snapshot CDC) source component to read only the records that have changed in your AlloyDB tables since the previous pipeline run, instead of reprocessing the entire table every time. The component produces two outputs: **Upserted records** (new and updated rows) and **Deleted records** (rows removed from the source).
## Connection Setup
Configure your AlloyDB connection in Integrate.io, then add the **Alloy (Snapshot CDC)** source component to your pipeline and select that connection. See the [Snapshot CDC Source reference](/docs/etl/using-components-cdc-database-source) for full configuration details.
## Snapshot Storage
AlloyDB Snapshot CDC uses **File Based** snapshot storage. The snapshot of your previous run is stored as a Parquet file on Integrate.io managed cloud storage, which means:
* Only read access to your AlloyDB database is required.
* No snapshot tables are created in your source database.
* On the first run, every current record is treated as upserted because there is no previous snapshot to compare against.
Database (table-based) snapshot storage is only available for SQL Server connections. For AlloyDB, File Based storage is the only option, and you do not need to configure an S3 connection of your own.
## Change Detection
Choose how the component decides whether a row has changed:
* **Primary Key**: matches rows by a unique identifier column and detects updates by comparing the remaining column values. Best when the table has a reliable key.
* **Composite Hash**: builds a hash from all or selected columns and compares hashes between runs. Best when the table has no reliable primary key.
With File Based storage you can also switch the **Source Mode** from **Table** to **Query** to track changes across a custom SQL query, including JOINs across multiple tables.
## Full Configuration Reference
Configuration steps, change detection methods, query mode, best practices, troubleshooting, and limitations that apply to every Snapshot CDC source.
# ETL: Amazon Redshift Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-amazon-redshift-cdc-source
How to configure the Amazon Redshift Snapshot CDC source component to read changed records from Redshift in your Integrate.io ETL pipeline.
Use the Amazon Redshift (Snapshot CDC) source component to read only the records that have changed in your Redshift tables since the previous pipeline run, instead of reprocessing the entire table every time. The component produces two outputs: **Upserted records** (new and updated rows) and **Deleted records** (rows removed from the source).
## Connection Setup
Configure your Amazon Redshift connection in Integrate.io, then add the **Amazon Redshift (Snapshot CDC)** source component to your pipeline and select that connection. See the [Snapshot CDC Source reference](/docs/etl/using-components-cdc-database-source) for full configuration details.
## Snapshot Storage
Amazon Redshift Snapshot CDC uses **File Based** snapshot storage. The snapshot of your previous run is stored as a Parquet file on Integrate.io managed cloud storage, which means:
* Only read access to your Redshift cluster is required.
* No snapshot tables are created in your source database.
* On the first run, every current record is treated as upserted because there is no previous snapshot to compare against.
Database (table-based) snapshot storage is only available for SQL Server connections. For Amazon Redshift, File Based storage is the only option, and you do not need to configure an S3 connection of your own.
## Change Detection
Choose how the component decides whether a row has changed:
* **Primary Key**: matches rows by a unique identifier column and detects updates by comparing the remaining column values. Best when the table has a reliable key.
* **Composite Hash**: builds a hash from all or selected columns and compares hashes between runs. Best when the table has no reliable primary key.
With File Based storage you can also switch the **Source Mode** from **Table** to **Query** to track changes across a custom SQL query, including JOINs across multiple tables.
## Full Configuration Reference
Configuration steps, change detection methods, query mode, best practices, troubleshooting, and limitations that apply to every Snapshot CDC source.
# ETL: Amazon Redshift Source
Source: https://www.integrate.io/docs/etl/using-components-amazon-redshift-source
Configure the Amazon Redshift source component to read data from Redshift tables and views in your Integrate.io ETL data pipeline.
## Connection Setup
Connect Integrate.io ETL to your [Redshift](https://www.integrate.io/blog/guide-to-amazon-redshift/) cluster in order to write data to it, or read data from it. Watch this short [video walkthrough](https://share.getcloudapp.com/p9uGqxDO) or read below for further instructions.
## In Redshift console
Start by navigating to the Redshift console --> **Clusters** screen and clicking on the **Properties** tab.
Scroll down to the Network and Security section. Make sure that the cluster is set with the value for **Publicly Accessible** to Yes. Then, click the **VPC Security Group** to verify and/or modify the security rules.
In the Security Group screen, select the **Inbound** tab.
There should be rules for the IP addresses listed [here](/docs/etl/integrateio-etls-ip-list/). If those rules need to be altered or don't exist, click Edit.
Edit any existing rules or click **Add Rule** to add a new rule. For each rule, select the type of database and enter the Redshift port. Then click **Save**.
**To configure your cluster to only accept SSL encrypted connections:**
First, access the parameter group and edit it to set require\_ssl to true.
Then, Navigate to Config --> Workload management. If you created the cluster with a default parameter group, create a new parameter group and modify the cluster to associate to that parameter group. Click **Edit** on the cluster homepage, then go to **Database Configurations** to associate the parameter group with the current cluster.
### To verify or modify the security rules of an instance on EC2-Classic (without VPC)
In your [Redshift Cluster](https://www.integrate.io/blog/data-warehousing-for-dummies-a-beginners-guide-to-setting-up-an-amazon-redshift-data-warehouse/) Security Group, modify a rule or add a new rule for each IP address listed [here](/docs/etl/integrateio-etls-ip-list/):
* In the Connection Type dropbox, choose CIDR/IP.
* In the CIDR/IP to Authorize field, enter the IP addresses from [this list](/docs/etl/integrateio-etls-ip-list/).
## Create a Redshift user
* [Create a Redshift user](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_USER.html).
* Grant it the following permissions:
* If you intend to only append data into a table, give the user minimal permissions required to execute the COPY command.
* If you intend to merge data into a table, give the user minimal permissions required to execute the COPY command, create a table, and insert and update to your target tables.
* Note that truncate requires Integrate.io ETL to either be the owner of the target table or have superuser access.
## To define a connection in Integrate.io ETL to Amazon Redshift
Click **Connections** (the lightning bolt) on the left side of the dashboard.
Click **New connection**.
Select **Amazon Redshift** from the Analytical Database category.
Type a name for the connection.
If you allow direct access from Integrate.io ETL's IP addresses, enter the hostname and port. If direct access is not allowed, read more about setting a tunnel connection [here](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/).
Enter the user name and password you created for Integrate.io ETL to use.
Enter the default database to use. If you leave it empty, the user's default database will be used.
Set the region to the AWS region in which the Redshift cluster was created. If the region requires AWS Signature v4 (see list [here](https://docs.aws.amazon.com/general/latest/gr/rande.html#s3_region)) you may need our support team's help with allowing Integrate.io ETL access to read from this Amazon Redshift connection.
Click **Test connection**. If the credentials are correct, a message that the cloud storage connection test was successful appears.
Click **Create connection** and the connection is created and appears in the list of connections.
***
Use the Amazon Redshift source component to read data stored in an Amazon Redshift table, view or using a query. The source component uses Amazon Redshift's UNLOAD statement to pull data into files in Amazon S3 and then reads the files.
## Connection
Select an existing Amazon Redshift connection or create a new one (for more information, see [Allowing Integrate.io ETL access to my Redshift cluster](/docs/etl/allowing-integrateio-etl-access-to-my-redshift-cluster/).)
## Source Properties
* **Access mode** - select table to extract an entire table/view or query to execute a query.
* **Source schema** - the source table's schema. If empty, the default schema is used.
* **Source table/view** - the table or view name from which the data will be imported.
* **where clause** - optional. You can add predicates clauses to the WHERE clause as part of the SQL query that is built in order to get the data from the database. Make sure to skip the keyword WHERE.
| Good | `prod_category = 1 AND prod_color = 'red'` |
| :--- | :----------------------------------------------- |
| Bad | `WHERE prod_category = 1 AND prod_color = 'red'` |
* **Query** - type in a SQL query. Make sure to name all columns uniquely.
* **Null string** - NULL values in string columns will be replaced with the string specified here. By default NULL values will appear like empty strings.
## Source Schema
After defining the source table/view/query select the fields to use in the source.
With table access mode, the fields you select are used to build the query that will be executed to read the data.
With query access mode, select all the fields that are defined in the query and make sure to use the same column names
Define the data type for the field. Use the following table when matching Redshift data types to Integrate.io ETL data types.
| **Amazon Redshift** | **Integrate.io ETL** |
| :---------------------- | :------------------- |
| varchar, nvarchar, text | String |
| smallint, int | Integer |
| bigint | Long |
| decimal, real | Float |
| double precision | Double |
| timestamp, date | DateTime |
# ETL: Bing Ads Source
Source: https://www.integrate.io/docs/etl/using-components-bing-ads-source
Configure the Bing Ads source component to read Microsoft Advertising report data in your Integrate.io ETL pipeline. Supports campaign metrics.
## Connection Setup
## To create a Bing Ads connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection.**
Select Bing Ads.
In the Bing network account window, sign in with your Bing account or click **Sign in with a different Microsoft account**.
In the next Microsoft account page click **Yes** to let Integrate.io ETL access your information.
In the new Bing Ads connection window, name the connection and click **Create bing ads connection.**
## To modify Bing Ads connections in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
Click a connection to open and modify it. Make any necessary changes, then click Reconnect, and Save changes. To exit the Bing Ads connection window without changes, click Back to connections (grey tab on the left side) on the Bing Ads connection window.
To delete a Bing Ads connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
***
## Connection
Select an existing Bing Ads connection or create a new one.
## Source Properties
* **API Version** - Integrate.io ETL will use the selected Adwords API version when running the job. Microsoft releases a new API version every few months and sunsets older versions of the API. You should check and update the API versions periodically.
* **Report type** - Select a report type from the dropdown list of reports.
* **Unit of time** - Select date/time granularity for the report. Date and time fields will be added to the source schema depending on the selection.
* **Date range** - Select a date range from the dropdown list of ranges. If you select `custom date range...`, you will be prompted to enter From date and To date. Click either inputs and select the dates from the calendar. You can also use variables as values for the custom date range in the format `yyyyMMdd`.
* **Accounts and campaigns** - Select accounts or specific campaigns to report on.
## Source Schema
After defining the Bing report, select the fields to use in the source.
**Note:**
Some fields are not compatible with other fields in the same report.
# ETL: Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-cdc-database-source
How Snapshot Change Data Capture source components work in Integrate.io ETL, with shared configuration, storage, and change detection reference for every supported database.
This page is the shared reference for all Snapshot CDC source components. Each database has its own dedicated connector in the pipeline designer; pick the one that matches your connection below, then use this page for the full configuration, storage, change detection, and troubleshooting detail that applies to all of them.
## Supported Databases
## Overview
Snapshot Change Data Capture (CDC) is a feature that allows you to track and process only the data that has changed between pipeline runs. Instead of processing your entire dataset every time, CDC identifies:
* **Upserted records**: New rows that were inserted OR existing rows that were updated
* **Deleted records**: Rows that existed in the previous run but no longer exist in the source table
This approach significantly reduces processing time and resource usage for large datasets where only a small percentage of data changes between runs.
## How It Works
Snapshot CDC works by maintaining a **snapshot** of your data from the previous pipeline run. On each subsequent run, the system compares the current data against this snapshot to identify changes.
The snapshot can be stored in one of two ways, depending on the **Snapshot Storage** method you select:
* **Database**: The snapshot is stored as a table in the same database as your source data. **This option is only available for SQL Server connections.**
* **File Based**: The snapshot is stored as a Parquet file on cloud storage managed by Integrate.io. **This option is available for all database connection types** (MySQL, PostgreSQL, SQL Server, Snowflake, and others).
### Pipeline Output
When you configure a CDC source component, it produces **two separate outputs**:
1. **Upserted records** - Contains all new and modified rows
2. **Deleted records** - Contains rows that were removed from the source table
You can route these outputs to different destinations or process them with different logic as needed. See [Building a mirror destination from CDC outputs](#building-a-mirror-destination-from-cdc-outputs) for the recommended pattern when you want a destination table to be a true mirror of the source (including deletes).
## Building a mirror destination from CDC outputs
The CDC source emits two streams. What those streams *mean* for your destination depends on what you want the destination to look like.
### Recommended pattern: a true mirror table
To make a destination table track every insert, update, and delete in the source, wire **three destination components** to the CDC source:
| Component | Reads from | Target | Operation | Key |
| :--------------- | :--------------- | :-------------------- | :--------- | :---------- |
| Mirror Upsert | Upserted records | `mirror_table` | **Merge** | Primary key |
| Mirror Delete | Deleted records | `mirror_table` | **Delete** | Primary key |
| Audit (optional) | Deleted records | `deleted_audit_table` | **Append** | (none) |
After every run, `mirror_table` is an exact replica of the source table. The optional `deleted_audit_table` keeps a tombstone log of every removed row.
The Mirror Delete component is only safe when the Deleted records output contains **only truly-deleted rows**. That is the case for Primary Key mode and for the hybrid Composite + Primary Key mode. **Do not** add this component when using Composite mode without a Primary Key, because the Deleted records output contains "ghost" rows for every UPDATE, and deleting them from the mirror would undo your updates. See the [Composite Hash Method](#2-composite-hash-method) section below.
### Common variations
* **Upsert-only sink (no deletes):** wire only the Mirror Upsert component. Deleted rows in the source will accumulate in the destination indefinitely.
* **Audit log only:** wire only the Audit component if you only care about "what disappeared".
* **Append-only event log:** wire both outputs to append-only tables (no Merge, no Delete). This gives you a full change history.
## Snapshot Storage
Snapshot CDC supports two storage methods for maintaining the snapshot between runs. The storage method is selected in the **Snapshot Storage** toggle in the component configuration.
### Database Storage (SQL Server only)
The snapshot is stored as a table directly in your source database. This option is **only available for SQL Server connections** and requires write access to the source database.
* A snapshot table is automatically created in the same schema as the source table
* Change detection queries (upsert and delete) are executed entirely within the database using SQL
* Requires `CREATE TABLE` and `INSERT`/`DELETE` permissions on the source database
### File Based Storage
The snapshot is stored as a Parquet file on cloud storage managed by Integrate.io. This option is useful when you do not have write access to the source database, or when you prefer not to create additional tables in your source system.
* No write access to the source database is required; only read access is needed
* The snapshot is stored as a Parquet file on Integrate.io’s managed S3 storage
* Change detection is performed by loading the current data and the previous snapshot, then comparing them in the pipeline
* On the first run, when no previous snapshot exists, all current records are treated as upserted (new)
* The snapshot file is automatically overwritten after each successful pipeline run
**When to use File Based storage:**
* You are using a non-SQL Server database (MySQL, PostgreSQL, Snowflake, etc.), File Based is the only snapshot storage option for these connection types
* Your database user has read-only access to the source database
* You do not want to create snapshot tables in the source database
* Corporate policy prohibits writing additional tables to the production database
### Availability by Connection Type
| Connection Type | Database Storage | File Based Storage |
| :-------------- | :--------------- | :----------------- |
| SQL Server | Yes | Yes |
| MySQL | No | Yes |
| PostgreSQL | No | Yes |
| Snowflake | No | Yes |
| Other databases | No | Yes |
## Change Detection Methods
Snapshot CDC offers two methods for detecting changes. Both methods work with either snapshot storage option (Database or File Based). Note that Database storage is only available for SQL Server connections; for all other database types, File Based storage is used.
### 1. Primary Key Method
The Primary Key method uses a unique identifier column to track changes.
**How it works:**
The system compares rows between the current table and the snapshot using the specified primary key
A row is considered **upserted** if:
* It exists in the current table but not in the snapshot (new record)
* It exists in both tables but any column value has changed (updated record)
A row is considered **deleted** if:
* It exists in the snapshot but not in the current table
**Best for:**
* Tables with a reliable unique identifier (e.g., `id`, `customer_id`, `order_number`)
* Standard database tables with primary key constraints
**Configuration:**
Select **Primary Key** as the Change Detection Method
Choose the primary key column from the dropdown (e.g., `id`)
**With File Based storage:** When using File Based snapshot storage with Primary Key method, the system generates an MD5 hash of all non-primary-key columns to efficiently detect which rows have changed. Rows are matched by primary key, and the hash is used to determine whether a row has been updated.
### 2. Composite Hash Method
The Composite Hash method creates a hash value from all (or selected) columns to detect changes. This is useful when your table doesn't have a unique identifier.
**How it works:**
The system generates a hash value (like a fingerprint) for each row based on column values
The hash is stored in the snapshot alongside the row data
On subsequent runs, hashes are compared to detect changes:
* A row is **upserted** if its hash doesn't exist in the snapshot
* A row is **deleted** if its hash exists in the snapshot but not in the current data
**Best for:**
* Tables without a primary key
* Tables where you want to detect changes based on specific columns only
* Scenarios where the primary key might change between runs
**Without a Primary Key, every UPDATE in your source appears in BOTH the upserted and deleted outputs.**
Composite Hash mode without a Primary Key has no way to recognise that an old hash and a new hash belong to the same row. From CDC's point of view, an UPDATE is a DELETE (old hash) followed by an INSERT (new hash).
If you intend to route the Deleted records output to a destination that performs `DELETE` operations (for example, a [Mirror Delete component](#building-a-mirror-destination-from-cdc-outputs)), you **must** specify a Primary Key (see step 4 below). Otherwise the Deleted records output will contain a copy of every updated row in addition to the truly-deleted ones, and using it to delete from a mirror table will revert your updates.
**Configuration options:**
Select **Composite** as the Change Detection Method
**Use custom columns for composite hash** (optional):
* If unchecked: All columns are used to generate the hash
* If checked: You can select specific columns for hash generation. This is useful for ignoring noise updates to bookkeeping columns (e.g. `last_updated_at`, `synced_at`) while still detecting business-meaningful changes
**Composite key column** (Database storage / SQL Server only): Select a column that will help identify whether a changed hash represents an update or a deletion. If not set, rows with changed hashes will appear in **both** upserted and deleted outputs. This option is not available with File Based snapshot storage.
**Primary key** (highly recommended when available):
* When set, CDC pairs old and new hashes by Primary Key, so updates appear **only** in the upserted output and the deleted output contains only truly-deleted rows. This is the **recommended hybrid configuration** for production
* When NOT set, every UPDATE produces a row in **both** the upserted output (new hash) AND the deleted output (old hash). See the warning above
**Composite + Primary Key with no custom hash columns is functionally identical to Primary Key mode.** The composite hash adds no value when it spans every column. To get value from Composite mode while keeping a Primary Key, also enable **Use custom columns for composite hash** and pick the business columns that should trigger updates.
**With File Based storage:** The Composite Hash method works the same way with File Based storage. The hash values and row data are stored in the Parquet snapshot file instead of a database table. All hash computation and comparison is performed in the pipeline. Note that the **Composite key column** option is not available with File Based storage; use the **Primary key** field instead to distinguish between updates and deletions.
## Choosing a Configuration
Use the table below to pick a configuration based on your source table and what you want from the deleted output:
| Your situation | Recommended mode | Why |
| :-------------------------------------------------------------------- | :-------------------------------------------------------------- | :---------------------------------------------------------------------------------------------------------------------- |
| Have a stable Primary Key and want every column change detected | **Primary Key** | Simplest config, clean upsert and delete streams |
| Have a stable Primary Key and want to ignore changes to some columns | **Composite + Primary Key + custom hash columns** (recommended) | Filters out noise (e.g. `last_synced_at`) while keeping the delete stream clean |
| No Primary Key exists | **Composite** (no Primary Key) | Works, but the deleted output will include "ghost" rows for every update. Do not wire it to a Delete-by-key destination |
| Have a Primary Key but selected Composite without custom hash columns | Equivalent to **Primary Key** mode | Composite + Primary Key + all-columns hash is functionally identical to Primary Key mode |
## Configuration Steps
**Create a Database (CDC) Source component** and connect it to your database
**Select CDC mode**: In the component configuration, CDC mode is automatically enabled for the Database CDC Source component type
**Choose your data source**:
* **Table mode** (default): Select the schema and table you want to track changes for. Optionally add a Where clause to filter rows.
* **Query mode** (File Based only): Switch the Source Mode to Query and write a custom SQL query. This allows JOINs across multiple tables, complex filtering, and column aliases. After writing the query, click **Refresh Fields** to load the available columns.
**Select a Change Detection Method**:
* **Primary Key**: For tables with unique identifiers
* **Composite**: For tables without primary keys or when you need hash-based detection
**Configure the detection parameters** based on your chosen method
**Select Snapshot Storage**:
* **Database**: Stores the snapshot as a table in your source database. Requires write access. **Only available for SQL Server connections.**
* **File Based**: Stores the snapshot as a Parquet file on Integrate.io managed storage. No write access to the source database is needed. Available for all database connection types.
**Select input columns** which will be processed by child components
**Connect outputs**: Route the "Upserted records" and "Deleted records" outputs to your desired destinations
## Snapshot Storage Details
### Database Snapshot Table (SQL Server only)
When using **Database** snapshot storage (available only for SQL Server connections), the system automatically creates and maintains a snapshot table in your database:
| Method | Snapshot Table Name |
| :------------- | :--------------------------------------------- |
| Primary Key | `{table_name}_integrate_io_snapshot` |
| Composite Hash | `{table_name}_integrate_io_snapshot_composite` |
**Important notes:**
* The snapshot table is created automatically on the first run
* The snapshot is updated after each successful pipeline run
* Do not modify or delete the snapshot table manually, as this will affect change detection accuracy
* The snapshot table uses the same schema as your source table
### File Based Snapshot
When using **File Based** snapshot storage, the snapshot is stored as a Parquet file on cloud storage managed by Integrate.io. The file is stored at a path unique to your account, package, and source table, so each CDC component maintains its own independent snapshot.
### File Based Snapshot: Query Mode
When using **File Based** snapshot storage, you can switch the **Source Mode** from **Table** to **Query**. This allows you to write a custom SQL query as the data source for CDC, instead of selecting a single table.
**Why use Query Mode:**
* You need to **JOIN multiple tables** and track changes across the combined result
* You need complex **WHERE clauses**, aggregations, or transformations applied before change detection
* You want to select only a subset of columns from one or more tables
* You need to rename columns using aliases (e.g., `c.name AS customer_name`)
**How it works:**
Set the **Source Mode** toggle to **Query**
Write your SQL query in the query editor
Click **Refresh Fields** to load the columns returned by your query
Select the **Primary Key** column from the returned fields (the key must uniquely identify each row in your query result)
**Important:**
The snapshot is identified by the component ID rather than a table name. This means each CDC component using Query Mode maintains its own independent snapshot, even if two components use the same query.
**Important notes:**
* No tables are created in your source database
* The snapshot file is created automatically on the first run
* The snapshot file is overwritten after each successful pipeline run with the latest data
* Snapshot files are managed automatically and do not require manual maintenance
* If you change the source table, schema, or column selection, the existing snapshot will be used for comparison against the new data, which may cause all rows to appear as upserted (and previous rows as deleted) on the first run after the change
## Example Use Cases
### Use Case 1: Order Processing (SQL Server)
Track new and updated orders to sync with a data warehouse:
* **Connection**: SQL Server
* **Source table**: `orders`
* **Method**: Primary Key
* **Primary key**: `order_id`
* **Snapshot Storage**: Database
* **Upserted records**: Send to data warehouse for processing
* **Deleted records**: Mark as cancelled in the warehouse
### Use Case 2: Product Catalog Sync (SQL Server)
Sync product changes to an e-commerce platform:
* **Connection**: SQL Server
* **Source table**: `products`
* **Method**: Composite Hash (no reliable primary key)
* **Custom columns**: `sku`, `name`, `price`, `description`
* **Snapshot Storage**: Database
* **Upserted records**: Update product listings
* **Deleted records**: Remove from catalog
### Use Case 3: Customer Data Updates (MySQL)
Track customer information changes for GDPR compliance:
* **Connection**: MySQL
* **Source table**: `customers`
* **Method**: Primary Key
* **Primary key**: `customer_id`
* **Snapshot Storage**: File Based
* **Upserted records**: Log changes for audit trail
* **Deleted records**: Process data deletion requests
### Use Case 4: Read-Only SQL Server CDC
Track changes on a SQL Server database where you only have read access:
* **Connection**: SQL Server
* **Source table**: `transactions`
* **Method**: Primary Key
* **Primary key**: `transaction_id`
* **Snapshot Storage**: File Based
* **Upserted records**: Load into analytics warehouse
* **Deleted records**: Flag as reversed in the warehouse
### Use Case 5: Multi-Table CDC with Query Mode
Track changes across a JOIN of orders, customers, and products:
* **Connection**: MySQL (or any supported database)
* **Source Mode**: Query
* **Query**:
```vbnet theme={null}
SELECT o.order_id, c.name AS customer_name, p.name AS product_name,
o.quantity, o.total_price, o.status
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = o.product_id
WHERE o.status IN ('pending', 'shipped')
```
* **Method**: Primary Key
* **Primary key**: `order_id`
* **Snapshot Storage**: File Based
* **Upserted records**: Send enriched order data to the warehouse
* **Deleted records**: Archive cancelled or completed orders
### Use Case 6: PostgreSQL CDC
Sync data from a PostgreSQL database (File Based storage is required since Database storage is not available for PostgreSQL):
* **Connection**: PostgreSQL
* **Source table**: `inventory`
* **Method**: Composite Hash
* **Custom columns**: `sku`, `quantity`, `warehouse_id`
* **Snapshot Storage**: File Based
* **Upserted records**: Update inventory management system
* **Deleted records**: Remove discontinued items
### Use Case 7: True mirror of a production table
Keep a destination table that exactly mirrors the source, ignoring noise updates to bookkeeping columns. This is the canonical production pattern, combining the hybrid Composite + Primary Key mode with the three-destination wiring described in [Building a mirror destination from CDC outputs](#building-a-mirror-destination-from-cdc-outputs):
* **Connection**: PostgreSQL (or any supported database)
* **Source table**: `orders`
* **Method**: Composite (hybrid)
* **Custom hash columns**: `status`, `total`, `customer_id`, `fulfilled_at` (business fields only)
* **Primary key**: `order_id`
* **Snapshot Storage**: File Based
* **Destinations**:
* **Mirror Upsert**: Database Destination on the Upserted records output. Target `analytics.orders_mirror`, operation **Merge**, key `order_id`
* **Mirror Delete**: Database Destination on the Deleted records output. Target `analytics.orders_mirror`, operation **Delete**, key `order_id`
* **Audit (optional)**: Database Destination on the Deleted records output. Target `analytics.orders_deleted_log`, operation **Append**
With this configuration, updates that only touch `last_synced_at` or other unhashed columns are correctly ignored, while inserts, updates to business columns, and deletes are all reflected in the mirror.
## Best Practices
1. **Choose the right detection method**:
* Use Primary Key when you have a reliable unique identifier
* Use Composite Hash when no primary key exists or when tracking changes to specific columns
2. **Consider column selection for Composite Hash**:
* Include only columns that matter for change detection
* Exclude frequently changing but unimportant columns (e.g., `last_modified_timestamp` if you only care about data changes)
3. **Choose the right snapshot storage**:
* Use **Database** storage when you are using a SQL Server connection, have write access, and want the most efficient change detection (queries run entirely in the database)
* Use **File Based** storage when you are using any non-SQL Server database, when you have read-only access, or when you do not want to create additional tables in the source database
4. **Monitor snapshot size**:
* For Database storage: The snapshot table grows with your source table. Consider periodic maintenance if storage becomes a concern.
* For File Based storage: The Parquet snapshot file is managed automatically by Integrate.io.
5. **Handle deleted records appropriately**:
* Decide whether to hard-delete or soft-delete in your destination
* Consider archiving deleted records for audit purposes
6. **Test with small datasets first**:
* Verify that change detection works as expected before running on production data
7. **Query Mode best practices**:
* Ensure your query result has a column that uniquely identifies each row, and use it as the Primary Key for accurate change detection
* Do not end your query with a semicolon. The system appends processing logic and a trailing semicolon may cause errors
* Use column aliases to avoid ambiguous names when joining multiple tables (e.g., `c.name AS customer_name`)
* Avoid non-deterministic functions (e.g., `NOW()`, `RAND()`) in your query. They produce different values on each run, causing all rows to appear as changed
8. **Avoid changing the Where clause or query between runs**:
* Changing the Where clause or rewriting the query alters which rows are included in the comparison. This may cause all rows to appear as upserted and previous rows as deleted on the first run after the change.
9. **Pair Composite mode with a Primary Key whenever one exists**:
* Composite-only mode (no Primary Key) populates the Deleted records output with both true deletes and "ghost" rows for every update. The hybrid Composite + Primary Key mode is the recommended production configuration. See [Choosing a Configuration](#choosing-a-configuration).
10. **To make a destination behave as a true mirror, wire two destinations to it**:
* One Merge-by-PK destination on Upserted records, and one Delete-by-PK destination on Deleted records, both targeting the same mirror table. See [Building a mirror destination from CDC outputs](#building-a-mirror-destination-from-cdc-outputs).
## Troubleshooting
This is expected behavior. On the first run, there's no snapshot to compare against, so all current records are treated as new (upserted). This applies to both Database and File Based snapshot storage.
You are using Composite Hash mode without a Primary Key. In that mode, every UPDATE in the source appears in both outputs (the new hash in upserted, the old hash in deleted). Specify a Primary Key in the Composite configuration to fix this. See the warning in the [Composite Hash Method](#2-composite-hash-method) section for the full explanation.
This applies to Database snapshot storage (SQL Server only). Ensure your database user has `CREATE TABLE` permissions on the target schema. Check the job logs for any error messages. If you do not have write access to the database, or you are using a non-SQL Server connection, use **File Based** snapshot storage instead.
Yes, add a separate CDC source component for each table you want to track.
Database snapshot storage is only available for SQL Server connections. If you are using MySQL, PostgreSQL, Snowflake, or another database type, only File Based snapshot storage is available.
Yes, but switching storage methods will effectively reset the snapshot. On the first run after switching, all current records will appear as upserted because the new storage location has no previous snapshot to compare against.
Changing the Where clause or the source query alters which rows are included in the comparison. Rows that were previously included in the snapshot but are now excluded will appear as deleted, and rows newly included will appear as upserted. The first run after the change will reflect the difference between the old and new result set.
After writing your SQL query, click the **Refresh Fields** button next to the Primary Key dropdown. This executes the query against the database and populates the available columns. You can then select the appropriate primary key.
No. Query Mode is only available with **File Based** snapshot storage. If you need Database snapshot storage (SQL Server), use Table mode with a Where clause for filtering.
If your query produces rows with duplicate primary key values, change detection will not work correctly. Ensure that the column selected as the primary key is unique across all rows returned by your query. When joining tables, use the primary key from the "main" table (e.g., `order_id` from an orders table in an orders-customers-products JOIN).
No. File Based snapshot storage uses Integrate.io's managed cloud storage. No additional connection configuration is required on your part.
## Limitations
* **Database storage**: Only available for SQL Server connections. Snapshot table must remain in the same database schema as the source table. Requires write access to the database.
* **File Based storage**: Available for all database connection types. No write access to the source database is required, but the pipeline performs the comparison (rather than the database), which may use more cluster resources for very large tables.
* Large initial snapshots may take time to process on the first run
* **Query Mode**: Only available with File Based snapshot storage. The query result must contain a column suitable for use as a primary key. Non-deterministic functions in the query will cause false positives in change detection.
* Changing the Where clause, source query, or schema columns between runs may cause unexpected results on the first run after the change
# ETL: Crisp Chat Source
Source: https://www.integrate.io/docs/etl/using-components-crisp-chat-source
Configure the Crisp Chat source component in Integrate.io ETL to extract conversations, contacts, and chat events into your data pipelines and warehouse.
Use the Crisp Chat source component to read conversations, people profiles, visitors, and operators data from your Crisp Chat account and ingest them into your [Integrate.io](http://integrate.io/) ETL pipeline.
## Connection
Select an existing Crisp Chat connection or create a new one. For setup instructions, see **Allowing Integrate.io ETL access to my Crisp Chat**.
## Source Properties
The source component is configured in Step 02 of the component editor.
### Source Table (Object)
Select the Crisp object to read data from:
| Object | Description | Paginated |
| ---------------- | -------------------------------------------------------------------------------------------------------------------------------- | --------- |
| conversations | All conversations (chat sessions) across the website. Includes metadata, messages preview, visitor info, and assignment details. | Yes |
| people\_profiles | Contact profiles (people) who have interacted with the website. Includes email, name, location, and custom data. | Yes |
| visitors | Currently active visitors on the website. Returns real-time visitor data (may be empty if no active visitors). | No |
| operators | Team members (operators) with access to the website. Includes name, email, role, and availability. | No |
Click the **Refresh** button next to the dropdown to reload the list of available objects.
### Load Type
Select how records are loaded on each pipeline run:
* **Full Load.** Fetches all records for the selected object on every run.
* **Incremental Load.** Fetches only records created or updated after a reference date. Useful for scheduled pipelines to avoid re-processing historical data.
### Incremental Load Settings
When **Incremental Load** is selected, the following options appear:
**Sync date field.** Select the date field used to filter records. Available fields include any field ending in `_at`:
* `created_at`: Timestamp when the record was created (Unix epoch, milliseconds)
* `updated_at`: Timestamp when the record was last updated
* `waiting_since`: Timestamp since the conversation has been waiting (conversations only)
**Load records.** Select the filter condition:
* `newer than ( > )`: Fetch records after the reference date.
* `older than ( < )`: Fetch records before the reference date.
**Reference date.** Choose the source of the date value:
* **Last successful run.** Track records since the last successful run of this pipeline. Selecting this option auto-fills `incremental_load_date` with the `$_PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP` system variable, so each scheduled run picks up only what changed since the previous run finished. Recommended for scheduled pipelines.
* **Fixed Date.** Select a specific calendar date using the date picker. Use this for a one-time historical backfill.
* **Variable.** Use a custom package variable. Select this when you need to drive the start date from a value other than the last successful run timestamp.
**Timestamp format**: Crisp uses Unix epoch timestamps in **milliseconds** (e.g. `1774494734867`), not ISO 8601 strings. The connector handles this conversion internally.
## Field Flattening
The Crisp API returns nested JSON objects for some fields. The connector automatically **flattens** these into individual columns using underscore-separated naming:
### Conversations: Flattened Fields
| Original API field | Flattened columns |
| -------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| unread (object) | unread\_visitor, unread\_operator |
| active (object) | active\_now |
| compose → visitor (nested) | compose\_visitor\_type, compose\_visitor\_excerpt, compose\_visitor\_timestamp |
| preview\_message (object) | preview\_message\_fingerprint, preview\_message\_from, preview\_message\_type, preview\_message\_excerpt |
| meta (deeply nested) | meta\_nickname, meta\_email, meta\_phone, meta\_ip, meta\_origin, meta\_avatar, meta\_segments, meta\_device\_timezone, meta\_device\_geolocation\_country, meta\_device\_geolocation\_city, meta\_device\_geolocation\_region, meta\_device\_geolocation\_coordinates\_latitude, meta\_device\_geolocation\_coordinates\_longitude, meta\_device\_system\_os\_name, meta\_device\_system\_browser\_name, etc. |
### People Profiles: Flattened Fields
| Original API field | Flattened columns |
| ----------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| active (object) | active\_now |
| person → geolocation (nested) | person\_geolocation\_country, person\_geolocation\_city, person\_geolocation\_region, person\_geolocation\_coordinates\_latitude, person\_geolocation\_coordinates\_longitude |
| company (object) | company\_emails, company\_phones, company\_tags |
Flattening is recursive. Deeply nested objects (like `meta.device.system.browser`) are fully expanded to leaf-level fields.
## Schema
After configuring the source properties, the **Schema** section (Step 03) displays all available fields with their detected data types.
Key data types:
* **string.** Text values and serialized JSON arrays
* **long.** Numeric values including Unix timestamps and integer IDs
* **boolean.** True/false values (e.g. `is_blocked`, `active_now`)
* **double.** Floating-point values (e.g. `meta_device_geolocation_coordinates_latitude`)
Use the field selector to choose which columns to include in your pipeline. You can rename fields using aliases and change data types as needed.
# ETL: Database Source
Source: https://www.integrate.io/docs/etl/using-components-database-source
Configure the Database source to read data from PostgreSQL, MySQL, SQL Server, Oracle, or DB2 tables and views in your Integrate.io ETL pipeline.
## Connection
Select an existing database connection or create a new one (for more information, see [Allow Integrate.io ETL access to my database server](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/)).
## Source Properties
* **Access mode** - select table to extract an entire table/view or query to execute a query.
* **Source schema** - the source table's schema. If empty, the default schema is used.
* **Source table/view** - the table or view name from which the data will be imported.
* **Where clause** - optional. You can add predicate clauses to the WHERE clause as part of the SQL query that is built in order to get the data from the database. Make sure to skip the keyword WHERE.
* **Query** - type in a SQL query. Make sure to name all columns uniquely.
* **Null string** - NULL values in string columns will be replaced with the string specified here. By default NULL values will appear like empty strings.
## Source Schema
After defining the source table/view/query, select the fields to use in the source.
With table access mode, the fields you select are used to build the query that will be executed to read the data.
With query access mode, select all the fields that are defined in the query and make sure to use the same column names.
Define the data type for each field. Data types are mapped according to the destination database platform.
## Related
# ETL: Dummy Source
Source: https://www.integrate.io/docs/etl/using-components-dummy-source
Use the Dummy Source component to start a package that has no source data of its own, such as webhook-triggered or schedule-driven packages.
The Dummy Source emits a single seed record so a package can run without reading from an external source. Use it when the package is triggered by a webhook or schedule, or when a downstream component (for example a Python or REST API step) fetches the data itself.
## When to use it
Add a Dummy Source when your package has no real source data to read, but still needs a source component to be valid. Common cases:
* Webhook-triggered packages where the payload is handled downstream.
* Schedule-driven packages that call out to an API or run a script.
* Packages where a Python script or REST API transformation pulls the data, and the source exists only to start the flow.
It replaces older workarounds that used a dummy file or dummy API source.
## Add the Dummy Source
1. In the package designer, open the **Sources** palette.
2. Drag **Dummy Source** onto the canvas.
3. Open the component. There is nothing to configure.
4. Connect it to the next component in your package.
## Output
The Dummy Source produces one record with a single field:
| Field | Type |
| ----- | ------ |
| seed | string |
The `seed` field is a placeholder. Downstream components are expected to generate or fetch the data your package actually processes.
## Limitations
* No connection or configuration options.
* Schema is fixed to one string field named `seed`. You can map or drop it in a downstream Select transformation.
* Data preview is not available on this component.
# ETL: Execute SQL
Source: https://www.integrate.io/docs/etl/using-components-execute-sql-workflow
The Execute SQL workflow component runs a SQL statement as a step in your workflow. Use it to perform database operations between package executions.
The Execute SQL workflow component runs a SQL statement as a step in your workflow. Use it to perform database operations between package executions.
## Overview
* Runs a single SQL statement against a database connection
* Commonly used for pre/post-processing: truncating staging tables, updating flags, running stored procedures
* Does not return data -- it executes the statement and reports success or failure
## Configuration
Select a database connection.Enter the SQL statement to execute.Optionally use variables in the SQL statement with the `${variable_name}` syntax.
## Use Cases
* Truncate a staging table before loading data
* Run a stored procedure after a dataflow completes
* Update a status flag to mark a batch as processed
* Create or drop temporary tables between pipeline steps
# ETL: Facebook Ads Insights Source
Source: https://www.integrate.io/docs/etl/using-components-facebook-ads-insights-source
Configure the Facebook Ads Insights source component to read ad performance and campaign report data in your Integrate.io ETL data pipeline.
## Connection Setup
Integrate.io ETL can read [Facebook Ads Insights](https://www.integrate.io/blog/improving-your-marketing-experience-with-facebook-ads-and-integrateio/) data from your Facebook ad accounts. This article details creating the Facebook Ads Insights connection in Integrate.io ETL (This method applies to Facebook Ads connection as well).
## To create a Facebook Ads Insights connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select Facebook Ads Insights.
Click **Authenticate.** You will be redirected to Facebook in order to grant Integrate.io ETL's Facebook application access to your ad accounts on your behalf.
You may need to log in to Facebook before Facebook asks you to allow Integrate.io ETL access to your Facebook ads and related stats. Click **OK**.
In the new Facebook Ads Insights connection window, Name the connection and click **Create facebook ads insights connection.**
## To modify Facebook Ads Insights connections in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
Click a connection to open and modify it. Make any necessary changes, then click Reconnect, and Save changes. To exit the Facebook Ads Insights connection window without changes, click Back to connections (grey tab on the left side) on the Facebook Ads Insights connection window.
To delete a Facebook Ads Insights connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
**Note:**
You may need to reconnect to Facebook periodically.
***
## Connection
Select an existing Facebook connection or create a new one.
## Source Properties
* **API version** - Integrate.io ETL will use the selected API version when running the job. Facebook releases a new Facebook Marketing API version every few months and sunsets older versions of the API. You should [check](https://developers.facebook.com/docs/graph-api/changelog "Link: https://developers.facebook.com/docs/graph-api/changelog") and update the API versions periodically.
* **Level** - Select the report level (`account` by default).
* **Date range** - Select a date range from the dropdown list of ranges. If you select `custom date range...`, you will be prompted to enter From date and To date. Click either inputs and select the dates from the calendar. You can also use variables as values for the custom date range in the format `yyyy-MM-dd`.
* If custom date range option was selected, `Run report day by day` checkbox will be displayed. When the checkbox is checked, the report will be executed for each day in the selected date range, in order to minimize data sampling. Make sure to include \`date\_start\` and \`date\_stop\` dimensions if checkbox is checked
* **Time breakdown** - use to break the results by period. The default, `All days` means that one result will be returned for the entire period. Add the fields `date_start` and `date_stop` to get the period per record (in Schema, below).
* **Filtering (optional)** - use to filter results based on the selected fields values. Filtering field should contain an array of filter objects. Each filter object has three fields: 'field', 'operator' and 'value'. Valid filter operators are: ` 'EQUAL', 'NOT_EQUAL', 'GREATER_THAN', 'GREATER_THAN_OR_EQUAL', 'LESS_THAN', 'LESS_THAN_OR_EQUAL', 'IN_RANGE', 'NOT_IN_RANGE', 'CONTAIN', 'NOT_CONTAIN', 'IN', 'NOT_IN', 'ANY', 'ALL', 'NONE' `.
Examples:
* `[{'field':'ad.effective_status','operator':'IN','value':['ARCHIVED']}]` - will return archived ads.
* `[{field:"ad.impressions",operator:"GREATER_THAN",value:0}]` - will retrieve insights only for ad objects with data (at least 1 impression).
* **Action report time** - determines the report time of action stats (by `impression` by default). See [Facebook Marketing APIs documentation](https://developers.facebook.com/docs/marketing-api/insights/parameters/ "Link: https://developers.facebook.com/docs/marketing-api/insights/parameters/") for more details.
* **Report on...** - by default, return the input for all ad accounts the connection has access to. You may also select specific accounts to report on.
* **Include custom conversions fields** - If checked, custom conversion fields will be fetched and shown on available fields list.
## Source Schema
Select the metrics, dimensions or breakdowns to use in the source. Action metrics include metrics for standard action and custom events with any of the 7 available attribution windows.
## Related
# ETL: File Mover
Source: https://www.integrate.io/docs/etl/using-components-file-mover-workflow
Configure the File Mover workflow component to move or rename files between storage locations as an automated step in your ETL workflow.
The File Mover workflow component moves or renames files between storage locations as a step in your workflow.
## Overview
* Moves files between cloud storage locations (S3, GCS, Azure Blob, SFTP, etc.)
* Can rename files during the move
* Useful for archiving processed files or staging data between pipeline steps
## Configuration
Select the source connection and path.Select the destination connection and path.Configure file pattern matching (optional -- filter which files to move).Choose whether to delete the source file after moving.
## Use Cases
* Archive raw files to a different S3 bucket after processing
* Move files from an SFTP landing zone to cloud storage
* Rename files with timestamps after a dataflow completes
# ETL: File Storage Source
Source: https://www.integrate.io/docs/etl/using-components-file-storage-source
Configure the File Storage source to read files from Amazon S3, Google Cloud Storage, Azure Blob Storage, or SFTP servers in your ETL pipeline.
## Connection
Select an existing file storage connection or create a new one. For more information, see [How to connect to your data sources.](/docs/etl/category/connectivity-and-security)
## Source Properties
### Source location
* **Source bucket** - The name of your cloud storage source bucket that contains the folders and objects defined in the path. Only relevant in the case of object stores such as Amazon S3 and Google Cloud Storage object stores.
* **Source path** - The path to your input folder or file. Examples:
* Folder: sales/2015/01/ (**Note**: This is NOT supported for **GCS**. Use pattern instead to specify multiple files.)
* File or object: sales/2015/01/log.csv
* Pattern: `sales/2015/{01,02}/`\
You can use wild card characters for [pattern globbing](/docs/etl/using-pattern-matching-in-source-component-paths/ "Link: /etl/using-pattern-matching-in-source-component-paths/").
**Note**
File and directory names that begin with an underscore (\_) or a dot (.) are ignored and data contained in them will not be read.
### Source file format
* **record delimiter** - defines what breaks the data into records.
* New line (\n,\n\r,\r) - each line in your files is a record.
* End of file - each file is treated as a record.
* **record type** - defines the format of the record.
* Delimited values - fields are delimited by a delimiter you define such as tab, comma or otherwise (see delimited values parameters below).
* Excel - a MS Excel file (.xlsx)
* Json object - each record is a Json object (enclosed in curly brackets).
* Raw - the record is read in its entirety into a single string/binary field.
* Parquet - process .parquet file(s). **Note:** we are unable to ingest column with DATETIME/INT96 datatype.
**Notes on limitation for file format**:
We currently do not support Avro file format.
[Read more](/docs/etl/how-do-i-select-my-file-input-format/) about selecting the right record delimiter and format.
**Note**:
The source data can be compressed (zip, gzip or bzip2) or uncompressed. If the zip file is password protected, enter the password in the zip password field; otherwise, leave it blank. The source data must be utf-8 encoded. If the data is encoded in another format, see [this](/docs/etl/how-do-i-process-a-different-encoding/) doc for instructions on parsing.
### Delimited values parameters
If you selected new line record delimiter and delimited values record type, define the delimiter character used to separate fields in your objects and whether the data is enclosed in quotes.
In the **field delimiter** dropdown list, select one of the characters (, tab). You can also type a single character or one of the following escape sequences:
* \b (backspace)
* \f (formfeed)
* \n (newline)
* \r (carriage return)
* \t (tab)
* \\' (single quote)
* \\" (double quote)
* \\\ (backslash)
If some or all of the fields are enclosed in single or double quotes, select ' or " in the **string qualifier** dropdown list. If the fields may also contain line breaks, select **" (newline inside)** or **' (newline inside)** according to the string qualifier used in the files. Use the "newline inside" option with caution as unbalanced double quotes may have undesired effects on job performance.
You can select an **escape character** based on your input files if a **string qualifier** is chosen on the previous step. This is so that the escape characters are omitted and records are ingested accordingly.
Check **First row contains column names** if there is a header row in each source file and you wish to skip it.
Optionally enable **Detect header by name** to locate the header row dynamically by matching your schema column names. Use this when the header isn't always on row 1 (for example, files with a leading title or blank rows). The full schema header must still appear in column order in each file.
### Excel parameters
* **Sheet Name** - Specify one sheet name from your Excel file. Leave blank to combine all sheets (all sheets must share the same schema).
* **Ingest large Excel file** - Enable streaming mode for large Excel files. Provide a **Sheet Name** to read just one sheet, or leave it blank to combine all sheets.
* Check **First row contains column names** if there is a header row in the sheet and you wish to skip it.
* Enable **Match columns by name** to map workbook columns to your schema by header name instead of by position. Use this when column order varies between files.
* Enable **Detect header by name** to locate the header row dynamically by matching your schema column names. The header can be on any row, and combined with **Match columns by name** the columns can be in any order. Glob patterns (for example, `2025_*.xlsx`) are supported when matching by name.
When streaming with a sheet name (**Ingest large Excel file** enabled and **Sheet Name** set), date cells come through as Excel serial numbers (e.g. `45306`) instead of formatted dates. Declare those columns as `int` or `decimal` in your schema, or transform them downstream.
### Json parameters
* **Base record JSONPath Expression** - You can use a custom JSONPath expressions to define the base record and extract nested objects and arrays. Select the object preset, to use the keys of the JSON object as the input fields. Select the array preset, to use the keys of the JSON objects within the array as the input fields. Type in a custom JSONPath expression to extract fields of nested objects/arrays (e.g. `$.data[*]/` would use the keys of the JSON objects with the array named "data" in the input JSON).
### Source action
* **Process all files directly from source** - by default, files are read and data is manipulated within the same process.
* **Process only new files (Incremental load)** - Define connection and path to store file manifest (see below) to let Integrate.io ETL read only new files in your source bucket/path. This is not supported for Excel files.
* **Manifest connection** - By default set to the source's connection. You may use another connection that has read/write access to store the manifest file.
* **Manifest path** - Fill in `bucket/directory/filename.gz`. This is where the manifest file will be stored when the job is executed. The manifest lists all files that have been processed. Each job execution compares the current file listing in the input path to the manifest and only processes new files. Backups of the previous manifests will be stored with each job execution for debugging/rollback purposes.
* **Copy, merge and process all files** - Use when the source path contains many small files and you'd like all of them. Integrate.io ETL will first read all files, merge them into larger files and then process them as large files which are faster to read and process.
**Note:** The process will fail if the input path is for a single file. You may get strange results if your delimited data doesn't end in a line break or if your files contain header lines.
* Character Encoding - You can specify the **character encoding** of the input file. Only available when **source action** is set to **copy, merge and process all files**.
* **Move processed files to another folder** - Use when you want to move source files to backup directory after processing of contents.
## Source Schema
After defining the source location and format, you select the fields to use in the source.
* With delimited values, fields are read by order so make sure you define all fields that exist in the source files by order.
* With JSON input, you may define only the fields that you wish to use in your package.
* With raw input, there is only a single field that contains the entire record data.
* Define how you will refer to these fields (alias) in the other components of your package. If you use illegal characters, we'll let you know before you close the dialog.
* You can also add `file_path` field (Under Meta Data) to get the path to the file as a field in your data.
For each field, define the alias to use for the field in following components and data type. For JSON input, define the key in the source JSON file for each field. Read more about processing JSON data [here](/docs/etl/how-do-i-process-json-data/).
## Related
# ETL: Google Ads Source
Source: https://www.integrate.io/docs/etl/using-components-google-ads-source
Configure the Google Ads source component to read advertising report data from your Google Ads account in Integrate.io ETL data pipelines.
## Connection Setup
## To create a Google Ads connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New** connection.
Select Google Ads.
Click **Authenticate**.
In the Google account window, choose an existing Google account or click **Add account** to create a new Google account.
In the next Google window click **Accept** to authorize Integrate.io ETL access.
In the new Google Ads connection window, name the connection and click **Create connection.**
## To modify a Google Ads connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
Click a connection to open and modify it. Make any necessary changes, then click Reconnect, and Save changes. To exit the Google Ads connection window without changes, click Back to connections (grey tab on the left side) on the Google Ads connection window.
To delete a Google Ads connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
***
**Note:**
Ad blockers may block html and javascript elements that mention Ads. We recommend that you disable your ad blocker or make an exception for Integrate.io ETL.
## Connection
Select an existing Google Ads connection or create a new one.
## Source Properties
* **API Version** - Integrate.io ETL will use the selected Ads API version when running the job. Google releases a new API version every few months and [sunsets older versions of the API](https://developers.google.com/google-ads/api/docs/sunset-dates?hl=en). You should check and update the API versions periodically.
* **Report type** - Select a report type from the dropdown list of reports. See [this article](https://developers.google.com/google-ads/api/docs/migration/mapping?hl=en) to map reports to Google AdWords UI. **Note: For structure reports, select "All time" in date range. We are using report types from the old version of Google Adwords.**
* **Date range** - Select a date range from the dropdown list of ranges. If you select `custom date range...`, you will be prompted to enter From date and To date. Click either inputs and select the dates from the calendar. You can also use variables as values for the custom date range in the format `yyyyMMdd`.
* **customer ids** - Leave empty to read data for all Ads customer account ids accessible to the connection or use a comma separated list of Ads customer account ids. The account ids appear in the top right corner of your Ads page or are managed by Ads Manager (MCC). If you provide an MCC account's customer id, the report will be executed for all the Ads accounts it has access to. Customer ids appear in the format nnn-nnn-nnnn (e.g. 123-456-7890)
## Source Schema
After defining the Ads report, select the fields to use in the source.
**Note:**
Some fields are not compatible with other fields in the same report. Refer [here](https://developers.google.com/google-ads/api/docs/migration/mapping?hl=en) for more information. Please contact us if there are missing fields on the Ads source components
## Related
# ETL: Google Analytics 4 (GA4) Source
Source: https://www.integrate.io/docs/etl/using-components-google-analytics-4-ga4-source
Configure the Google Analytics 4 source component to read analytics report data from your GA4 property in Integrate.io ETL data pipelines.
## Connection Setup
## Step 1: Create a Service Account within your GCP project
### Create a Service Account
Access your [Google Cloud Console](https://console.cloud.google.com/#/project).
On the sidebar, choose [IAM & Admin > Service Accounts.](https://console.cloud.google.com/iam-admin/serviceaccounts)
Click [+ CREATE SERVICE ACCOUNT.](https://console.cloud.google.com/iam-admin/serviceaccounts/create)
Fill up the Service account details and click "**Done**".
### Generate a private key for your service account
Navigate to [IAM & Admin > Service Accounts.](https://console.cloud.google.com/iam-admin/serviceaccounts)
Click the Service Account you created from the previous step.
Click the **"KEYS"** tab.
Click "**ADD KEY"** > "**Create new key**" > **"JSON"** and click "**CREATE**".
A private key (JSON) file will be automatically downloaded to your machine.
## Step 2: Enable the APIs
Enable [Google Analytics Admin API](https://console.cloud.google.com/marketplace/product/google/analyticsadmin.googleapis.com)
Enable [Google Analytics Data API](https://console.cloud.google.com/marketplace/product/google/analyticsdata.googleapis.com)
## Step 3: Grant Google Analytics 4 Property Access to Service Account
Navigate to [Google Analytics](https://analytics.google.com/) and click the "**Admin**" cog in the left sidebar.
Ensure that you are in the correct Account and Property, and then click "**Property Access Management**".
Click the blue **+** button to add access permissions to new users, and select "Add users".
Paste the Service Account email address you created earlier. Deselect "**Notify new users by email**" and select "**Viewer**" and click "**Create**".
## Step 4: Create GA4 Connection on Integrate.io ETL
Navigate to the **Connections** tab and click **Google Analytics - GA4.**
Fill up the Connection Name.
Upload the JSON key you generated from Step 1.
Click **Test Connection** and **Create Connection**.
***
## Connection
Select an existing Google Analytics 4 (GA4) connection or create a new one.
## Source Properties
* **Api Version -** Select api version Google Analytics 4 API. See list of available APIs from Google [here](https://developers.google.com/analytics/devguides/reporting/data/v1/rest)
* **Date range** - Select a date range from the dropdown list of ranges. If you select `custom date range...`, you will be prompted to enter From date and To date. Click either inputs and select the dates from the calendar. You can also use variables as values for the custom date range in the format `yyyy-MM-dd`.
* **Include custom dimensions and metrics -** Fetch list of custom dimensions and metrics from selected accounts and show it on tables of dimensions and metrics in schema section
* **Property IDs** - Leave empty to read data for all accessible Google Analytics 4 properties or type in the IDs of your Google Analytics properties (or tables) in the form of properties/xxx. You can enter multiple ids separated by comma or use a variable too. Source Schema
Select the metrics and dimensions to use in the source. The source also includes pre-defined fields for the Google Analytics account name, property name, property id and account id. Data can be also previewed by clicking "Refresh" button.
## Migrating from Universal Analytics to Google Analytics 4 (GA4)
For metrics and dimensions mapping, please visit GA4 migration guide [here](https://developers.google.com/analytics/devguides/migration/api/reporting-ua-to-ga4-dims-mets)
# ETL: Google Analytics Source
Source: https://www.integrate.io/docs/etl/using-components-google-analytics-source
Configure the Google Analytics source component to read web analytics report data from your GA account in Integrate.io ETL data pipelines.
## Connection Setup
Integrate.io ETL can access your Google Analytics data in both free and premium accounts. This article details creating the [Google Analytics connection](https://www.integrate.io/blog/multi-cloud-data-analytics/) in Integrate.io ETL.
## To create a Google Analytics connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New** connection.
Select Google Analytics.
Click **Authenticate**.
In the Google account window, choose an existing Google account or click **Add account** to create a new Google account.
In the next Google window click **Accept** to authorize Integrate.io ETL access.
In the new Google Analytics connection window, name the connection and click **Create connection.**
## To modify a Google Analytics connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
Click a connection to open and modify it. Make any necessary changes, then click Reconnect, and Save changes. To exit the Google Analytics connection window without changes, click Back to connections (grey tab on the left side) on the Google Analytics connection window.
To delete a Google Analytics connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
***
## Connection
Select an existing Google Analytics connection or create a new one.
## Source Properties
* **Date range** - Select a date range from the dropdown list of ranges. If you select `custom date range...`, you will be prompted to enter From date and To date. Click either inputs and select the dates from the calendar. You can also use variables as values for the custom date range in the format `yyyy-MM-dd`.
* If custom date range option was selected, `Run report day by day` checkbox will be displayed. When the checkbox is checked, the report will be executed for each day in the selected date range, in order to minimize data sampling. Make sure to include \`ga:date\` dimension if checkbox is checked
* **Segment** (optional) - Select a segment from the dropdown list of segments or write your own.
* **Filters** (optional) - Enter a filter to retrieve a subset of the data (e.g. `ga:browser=~%5EFirefox`).
* **Sort** (optional) - Enter the sort order and sort field (e.g. `ga:country,ga:browser`).
* **Sampling Level** - Set the sampling level for the query. See the Google Analytics [sampling section](https://developers.google.com/analytics/devguides/reporting/core/v3/reference#sampling) for more details.
* **Include empty rows** - if unchecked, records where all metric values are zero will be omitted.
* **Profile IDs** - Leave empty to read data for all accessible Google Analytics profiles or type in the IDs of your Google Analytics profiles (or tables) in the form of ga:xxx. You can enter multiple ids separated by comma or use a variable too. Use the [Account Explorer](https://ga-dev-tools.web.app/account-explorer/ "Link: https://ga-dev-tools.web.app/account-explorer/") tool to get a list of the profiles you have access to.
## Source Schema
Select the metrics (between 1 and 10) and dimensions (between 0 and 7) to use in the source. The source also includes pre-defined fields for the Google Analytics account name, property name, profile name and id.
# ETL: Google BigQuery Source
Source: https://www.integrate.io/docs/etl/using-components-google-bigquery-source
Configure the Google BigQuery source component to read data from BigQuery tables or run queries in your Integrate.io ETL data pipeline.
## Connection Setup
Integrate.io ETL uses highly secured certificate-based authentication for connecting to your Google [BigQuery Dataset](https://www.integrate.io/blog/how-to-offload-data-processing-from-google-bigquery/). You need to provide your own service-account's JSON-key and grant Integrate.io ETL access only to projects and associated datasets that you specifically select. This article discusses providing Integrate.io ETL access on your Google BigQuery dataset and then details creating the BigQuery connection in Integrate.io ETL.
### Project Access
Access your [Google Cloud Console](https://console.cloud.google.com/#/project).
Click the project name for which you want to grant Integrate.io ETL access.
On the sidebar, choose **IAM & Admin -> Service Accounts**
Click **"+ CREATE SERVICE ACCOUNT"**
Fill-out service account details and then grant following roles:
* **BigQuery Data Viewer**
* **BigQuery Job User**
### Dataset Access
Access your [BigQuery console](https://console.cloud.google.com/bigquery).
In the BigQuery sidebar, under **Resources**, select your project, then select your dataset, click **SHARE DATASET** and select **Permissions**
Click **"+ ADD PRINCIPAL"** and then assign following roles:
* **BigQuery Data Editor**
* **BigQuery Data Viewer**
### Create a service-account JSON-key
Access your [Google Cloud Console](https://console.cloud.google.com/#/project).
Click the project name for which you want to grant Integrate.io ETL access.
On the sidebar, choose **IAM & Admin -> Service Accounts**
Select & click the service account that has been created in previous step, click "**KEYS**" tab on the top and then click "**ADD KEY**" -> "**Create new key**" -> choose "**JSON**" -> click "**CREATE**"
A JSON-key file will be automatically downloaded to your machine.
### Create Google BigQuery connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select **Google BigQuery**.
Name the connection.
Fill in Dataset ID and select the region where your BigQuery is hosted.
Upload a JSON-key file that you have created on previous step. Service account email and Project ID will be filled in automatically.
Click **Test Connection** to make sure the connection details are correct.
Click **Create connection** to create the connection.
### Migrate from previous version of Google BigQuery connector
Customers who have created previous version of BigQuery connector and want to migrate to the latest version of BigQuery connector, please follow these steps:
Select package containing BigQuery connector from your dashboard.
On the package designer page, select BigQuery source and/or destination components that you want to modify.
Replace previous version of BigQuery with connection that has prefix ID **BIGQUERYV2\_CONNECTION\_**
Remove Integrate.io ETL's service account email (**[service-account-integrateio-for-xplenty@developer.gserviceaccount.com](mailto:service-account-integrateio-for-xplenty@developer.gserviceaccount.com)**) from your both project's and dataset's permission.
1. To remove from your project's permissions, access your [Google Cloud Console](https://console.cloud.google.com/#/project) and go to **IAM & Admin -> Service Accounts,** select checkbox on Integrate.io ETL's service account email and click **DELETE**
2. To remove from your dataset's permissions, access your [BigQuery console](https://console.cloud.google.com/bigquery). In the BigQuery sidebar, under **Resources**, select your project, then select your dataset, click **SHARE DATASET** and select **Permissions.** Remove Integrate.io ETL's service account email from each assigned role by clicking delete icon.
***
## Connection
Select an existing [Google BigQuery connection](https://www.integrate.io/blog/how-to-offload-data-processing-from-google-bigquery/) or create a new one. For more information, see [Allowing Integrate.io ETL access to my Google BigQuery dataset](/docs/etl/allowing-integrateio-etl-access-to-my-google-bigquery-dataset/ "Link: /etl/allowing-integrateio-etl-access-to-my-google-bigquery-dataset/").
## Source Properties
* **Access mode** - select table to extract an entire table or query to execute a query (access table views).
* **Source table** - the table name from which the data will be imported.
* **Query** - type in a SQL query. Make sure to name all columns uniquely.
## Source Schema
After defining the source table/query select the fields to use in the source.
With table access mode, the fields you select are used to build the query that will be executed to read the data.
With query access mode, select all the fields that are defined in the query and make sure to use the same column names
Define the data type for the field. Use the following table when matching BigQuery data types to Integrate.io ETL data types.
| Google BigQuery | Integrate.io ETL |
| :---------------- | :--------------- |
| String | String |
| Integer | Long |
| Float | Double |
| Timestamp | Datetime |
| Record | Map |
| Record (repeated) | Bag |
## Related
# ETL: Google Cloud Postgres Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-google-cloud-postgres-cdc-source
How to configure the Google Cloud Postgres Snapshot CDC source component to read changed records from Cloud SQL for PostgreSQL in your Integrate.io ETL pipeline.
Use the Google Cloud Postgres (Snapshot CDC) source component to read only the records that have changed in your Cloud SQL for PostgreSQL tables since the previous pipeline run, instead of reprocessing the entire table every time. The component produces two outputs: **Upserted records** (new and updated rows) and **Deleted records** (rows removed from the source).
## Connection Setup
Before reading changes, set up your Google Cloud Postgres connection. See [Connecting to Google Cloud SQL for PostgreSQL](/docs/etl/allowing-integrateio-etl-access-to-google-cloud-sql-for-postgresql-databases) for the credentials and network access required. Once the connection exists, add the **Google Cloud Postgres (Snapshot CDC)** source component to your pipeline and select it.
## Snapshot Storage
Google Cloud Postgres Snapshot CDC uses **File Based** snapshot storage. The snapshot of your previous run is stored as a Parquet file on Integrate.io managed cloud storage, which means:
* Only read access to your Cloud SQL for PostgreSQL database is required.
* No snapshot tables are created in your source database.
* On the first run, every current record is treated as upserted because there is no previous snapshot to compare against.
Database (table-based) snapshot storage is only available for SQL Server connections. For Google Cloud Postgres, File Based storage is the only option, and you do not need to configure an S3 connection of your own.
## Change Detection
Choose how the component decides whether a row has changed:
* **Primary Key**: matches rows by a unique identifier column and detects updates by comparing the remaining column values. Best when the table has a reliable key.
* **Composite Hash**: builds a hash from all or selected columns and compares hashes between runs. Best when the table has no reliable primary key.
With File Based storage you can also switch the **Source Mode** from **Table** to **Query** to track changes across a custom SQL query, including JOINs across multiple tables.
## Full Configuration Reference
Configuration steps, change detection methods, query mode, best practices, troubleshooting, and limitations that apply to every Snapshot CDC source.
# ETL: Google Cloud Spanner Source
Source: https://www.integrate.io/docs/etl/using-components-google-cloud-spanner-source
Configure the Google Cloud Spanner source component to read data from Spanner tables or queries in your Integrate.io ETL data pipeline.
## Connection Setup
Content for this article is coming soon. To create a Google Cloud Spanner connection in Integrate.io ETL, use the **Connections** menu and follow the in-app connection wizard.
***
## Connection
Select an existing Google Spanner connection or create a new one.
## Source Properties
* **Access mode** - select table to extract an entire table or query to execute a query.
* **Source table** - the table name from which the data will be imported.
* **where clause** - optional. You can add predicates clauses to the WHERE clause as part of the SQL query that is built in order to get the data from the database. Make sure to skip the keyword WHERE.
| Good | `prod_category = 1 AND prod_color = 'red'` |
| :--- | :----------------------------------------------- |
| Bad | `WHERE prod_category = 1 AND prod_color = 'red'` |
* **Query** - type in a SQL query. Make sure to name all columns uniquely.
## Source Schema
After defining the source table/query select the fields to use in the source.
With table access mode, the fields you select are used to build the query that will be executed to read the data.
With query access mode, select all the fields that are defined in the query and make sure to use the same column names
Define the data type for the field. Use the following table when matching database data types to Integrate.io ETL data types.
| **Cloud Spanner** | **Integrate.io ETL** |
| :---------------- | :------------------- |
| BOOL | Boolean |
| INT64 | Long |
| FLOAT64 | Double |
| STRING(n) | String |
| BYTES(n) | Binary |
| DATE | Datetime |
| TIMESTAMP | Datetime |
# ETL: Google Cloud SQL Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-google-cloud-sql-cdc-source
How to configure the Google Cloud SQL Snapshot CDC source component to read changed records from Cloud SQL in your Integrate.io ETL pipeline.
Use the Google Cloud SQL (Snapshot CDC) source component to read only the records that have changed in your Cloud SQL tables since the previous pipeline run, instead of reprocessing the entire table every time. The component produces two outputs: **Upserted records** (new and updated rows) and **Deleted records** (rows removed from the source).
## Connection Setup
Before reading changes, set up your Google Cloud SQL connection. See [Connecting to Google Cloud SQL for MySQL](/docs/etl/allowing-integrateio-etl-access-to-google-cloud-sql-for-mysql-databases) for the credentials and network access required. Once the connection exists, add the **Google Cloud SQL (Snapshot CDC)** source component to your pipeline and select it.
## Snapshot Storage
Google Cloud SQL Snapshot CDC uses **File Based** snapshot storage. The snapshot of your previous run is stored as a Parquet file on Integrate.io managed cloud storage, which means:
* Only read access to your Cloud SQL database is required.
* No snapshot tables are created in your source database.
* On the first run, every current record is treated as upserted because there is no previous snapshot to compare against.
Database (table-based) snapshot storage is only available for SQL Server connections. For Google Cloud SQL, File Based storage is the only option, and you do not need to configure an S3 connection of your own.
## Change Detection
Choose how the component decides whether a row has changed:
* **Primary Key**: matches rows by a unique identifier column and detects updates by comparing the remaining column values. Best when the table has a reliable key.
* **Composite Hash**: builds a hash from all or selected columns and compares hashes between runs. Best when the table has no reliable primary key.
With File Based storage you can also switch the **Source Mode** from **Table** to **Query** to track changes across a custom SQL query, including JOINs across multiple tables.
## Full Configuration Reference
Configuration steps, change detection methods, query mode, best practices, troubleshooting, and limitations that apply to every Snapshot CDC source.
# ETL: Gmail Source
Source: https://www.integrate.io/docs/etl/using-components-google-mail-gmail-source
Configure the Gmail source component in Integrate.io ETL to read CSV or Excel attachments and email body content from a Gmail inbox into your pipelines.
Use the Google Mail (Gmail) source component to read CSV or Excel file attachments, or the email body itself, from a Gmail inbox and ingest them into your [Integrate.io](http://integrate.io/) ETL pipeline.
## Connection Setup
Integrate.io ETL supports two authentication methods for Gmail: a Google **Service Account** with domain-wide delegation, or **OAuth 2.0**. Use OAuth when you don't have Google Workspace Admin access or want to connect a personal Gmail account.
### Service Account (domain-wide delegation)
#### Step 1: Create a GCP Project
Access your [Google Cloud Console](https://console.cloud.google.com/).
Click the project dropdown at the top of the page and select existing project or create a **New Project**.
If a new project is created, enter a project name (e.g. `my-gmail-connector`) and click **Create**. Make sure the newly created project is selected in the project dropdown before proceeding.
#### Step 2: Enable the Gmail API
In the left sidebar, navigate to **APIs & Services → Library**.
Search for **Gmail API**.
Click the Gmail API result and click **Enable**.
#### Step 3: Create a Service Account
In the left sidebar, navigate to **IAM & Admin → Service Accounts**.
Click **+ Create Service Account**.
Fill in the service account details:
* **Name**: e.g. `gmail-connector-reader`
* **Description**: e.g. `Service account for Integrate.io Gmail CSV connector`
Click **Create and Continue**.
Skip the optional role grant steps. Gmail access is controlled via domain-wide delegation, not IAM roles.
Click **Done**.
#### Step 4: Enable Domain-Wide Delegation
From the **Service Accounts** list, click the service account you just created.
Go to the **Details** tab.
Scroll down to **Advanced settings** and find the **Domain-wide Delegation** section.
Click **Enable Google Workspace Domain-wide Delegation**.
Enter a product name (e.g. `Integrate.io Gmail Connector`) and click **Save**.
Note the **Client ID** displayed. This is the long numeric ID (e.g. `118304762983475829341`) you will need in Step 6.
**Finding the Client ID**: The Client ID is also available inside the JSON key file downloaded in Step 5, under the `client_id` field.
#### Step 5: Download the JSON Key
Still on the service account page, go to the **Keys** tab.
Click **Add Key → Create new key**.
Select **JSON** and click **Create**.
A `.json` key file will be automatically downloaded to your machine. Keep this file secure; it contains the private key used to authenticate the service account.
The JSON file will contain a structure similar to:
```
{
"type": "service_account",
"project_id": "your-project",
"private_key_id": "...",
"private_key": "-----BEGIN RSA PRIVATE KEY-----\\n...",
"client_email": "gmail-connector-reader@your-project.iam.gserviceaccount.com",
"client_id": "118304762983475829341",
...
}
```
This is the file you will upload to Integrate.io in Step 7.
#### Step 6: Authorize the Scope in Google Workspace Admin
This step requires **Google Workspace Admin** access to the organization whose Gmail you want to connect. If you are setting this up for a client, this step must be completed by their Workspace administrator.
Go to [admin.google.com](http://admin.google.com/) and sign in as a Workspace admin.
Navigate to **Security → Access and data control → API controls**.
Click **Manage Domain-Wide Delegation**.
Click **Add new**.
Enter the following:
* **Client ID**: The numeric Client ID from Step 4 (e.g. `118304762983475829341`)
* **OAuth scopes**: `https://www.googleapis.com/auth/gmail.readonly`
Click **Authorize**.
This authorizes the service account to impersonate users in the domain and access their Gmail data on behalf of Integrate.io.
#### Step 7: Create the Gmail Connection in Integrate.io ETL
Navigate to the **Connections** tab and click **Gmail.**
Fill up the Connection Name
Upload the JSON key you generated in Step 5.
Click **Test Connection** and **Create Connection**
### OAuth 2.0
As an alternative to Service Account authentication, Integrate.io ETL also supports connecting to Gmail via OAuth 2.0. This is the recommended method when you don't have Google Workspace Admin access or want to connect a personal Gmail account.
Navigate to the Connections tab and click + New connection.
Search for and select **Gmail (OAuth)**.
Click Authenticate.
A Google sign-in popup will appear. Select the Google account you want to connect.
Review the requested permissions.
Once authentication completes, fill in the Connection Name (e.g. My Gmail OAuth).
Click Test Connection to verify, then click Create Connection.
Once created, the OAuth connection is maintained automatically: access tokens refresh every 30 minutes in the background, and a token that expires during a job run is renewed using the stored refresh token. To revoke access, remove Integrate.io from your [Google Account permissions](https://myaccount.google.com/permissions), or delete the connection from the Connections page (which also invalidates the refresh token).
## Source Properties
The source component is configured in Step 02 of the component editor.
### Gmail Query
Enter a Gmail search query to filter which emails are fetched. This field supports all standard [Gmail search operators](https://support.google.com/mail/answer/7190).
Examples:
* `from:supplier@example.com has:attachment filename:*.csv`: CSV attachments from a specific sender
* `from:@example.com has:attachment filename:*.csv`: CSV attachments from any sender at a domain
* `subject:"monthly report" has:attachment filename:*.csv`: Emails with a specific subject
* `{from:alice@example.com } has:attachment filename:*.csv`: OR logic across multiple senders
A space between operators means AND, so all conditions must match. Use `OR` (uppercase) or `{}` for OR logic between values of the same operator.
### Read From
Select what to extract from each matching email:
* **Attachments.** Read CSV or Excel attachments from matching emails. Each row in the file becomes a row in the pipeline, with email metadata appended as extra columns. This is the default.
* **Email body.** Read each matching email as a single row. The body, subject, sender, recipient, date, and message ID are exposed as columns. No attachment is required.
Use **Email body** when the data you need lives in the email text itself (for example, transactional notifications, form submissions, or system alerts that don't carry a file). Use **Attachments** when partners or systems send tabular data as CSV or Excel files.
### File Type
This option appears only when **Read From** is set to **Attachments**. Select the format of the attachment files to ingest:
* **CSV.** Comma-separated values
* **Excel.** `.xlsx` / `.xls` spreadsheet files
### Delimiter, Quote Character, and Header Row
These options appear only when **Read From** is set to **Attachments** and **File Type** is **CSV**.
* **Delimiter.** Character that separates fields in the CSV file.
* **Quote character.** Character used to quote field values.
* **File contains a header row.** Check this box if the first row of the file contains column headers. When enabled, the connector uses the header row to name the schema fields. This is checked by default.
### Load Type
Select how records are loaded on each pipeline run:
* **Full Load.** Fetches all emails matching the Gmail query on every run.
* **Incremental Load.** Fetches only emails received after a reference date. The connector appends an `after:YYYY/MM/DD` operator to your Gmail query at runtime so that only new emails are returned by the Gmail API, keeping API usage low and execution fast.
### Incremental Load Settings
When **Incremental Load** is selected, the following options appear:
**Load records.** Select the filter condition:
* `newer than ( > )`: Fetch emails received after the reference date.
**Reference date.** Choose the source of the date value:
* **Last successful run.** Track emails received since the last successful run of this pipeline. Selecting this option auto-fills `incremental_load_date` with the `$_PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP` system variable, so each scheduled run picks up only what arrived since the previous run finished. Recommended for scheduled pipelines.
* **Fixed Date.** Select a specific calendar date using the date picker. Use this for a one-time historical backfill.
* **Variable.** Use a custom package variable as the reference date. Select this when you need to drive the start date from a value other than the last successful run timestamp.
**Timezone note**: The Gmail `after:` operator interprets dates in **PST/PDT**, not UTC. If your variable is UTC-based, consider subtracting a 1-day buffer to avoid missing emails near the date boundary.
## Schema
After configuring the source properties, the **Schema** section (Step 03) displays the fields available in the pipeline. The columns depend on the **Read From** mode.
### Attachments Mode
Schema fields are derived from the header row of the first matching email attachment. In addition to the columns from the file itself, the connector appends the following metadata columns to every row:
| Column | Description | Example |
| -------------------- | ------------------------------------------- | --------------------------------------------------------- |
| email\_message\_id | Gmail message ID of the source email | 18d4f2e3a7b1c9d0 |
| email\_date | Date the email was received (ISO 8601, UTC) | 2026-03-10T14:30:00Z |
| attachment\_filename | Original filename of the attachment | sales\_report\_march.csv |
| email\_from | Sender email address | [supplier@example.com](mailto:supplier@example.com) |
| email\_to | Recipient email address | [reports@yourcompany.com](mailto:reports@yourcompany.com) |
| email\_subject | Subject line of the email | Monthly Sales Report |
| email\_body | Plain-text body of the email | Please find the report attached. |
These metadata columns let you trace each row back to its source email and file for deduplication and auditing downstream.
### Email Body Mode
Each matching email produces one row with the following columns:
| Column | Description | Example |
| ------------------ | ------------------------------------------- | ------------------------------------------------- |
| email\_message\_id | Gmail message ID of the source email | 18d4f2e3a7b1c9d0 |
| email\_date | Date the email was received (ISO 8601, UTC) | 2026-03-10T14:30:00Z |
| email\_from | Sender email address | [alerts@example.com](mailto:alerts@example.com) |
| email\_to | Recipient email address | [ops@yourcompany.com](mailto:ops@yourcompany.com) |
| email\_subject | Subject line of the email | Order #12345 confirmed |
| email\_body | Plain-text body of the email | Your order has been confirmed... |
Use a downstream **Select** or **Cross Join with Function** component to parse fields out of `email_body` (for example, with regular expressions) when you need structured values from the email text.
### Example: Extract Order IDs from Notification Emails
Configure the source with:
* **Gmail query:** `from:notifications@example.com subject:"Order confirmed"`
* **Read From:** `Email body`
* **Load Type:** `Incremental Load` with `$package_last_successful_job_submission_timestamp` as the reference date
In a downstream **Select** component, extract the order ID from the body using a regular expression:
```text theme={null}
RegexExtract(email_body, 'Order #(\\d+)', 1) AS order_id
```
# ETL: Google Sheets Source
Source: https://www.integrate.io/docs/etl/using-components-google-sheets-source
Configure the Google Sheets source component in Integrate.io ETL to read spreadsheet data with OAuth authentication, sheet selection, and cell range options.
Use the **Google Sheets source component** to read data from a Google Sheets spreadsheet and ingest it into your Integrate.io ETL pipeline.
## Connection Setup
Integrate.io ETL supports two ways to connect to Google Sheets: a Google Cloud **service account** (recommended for team or workspace-owned spreadsheets), or user-based **OAuth**.
### Service Account (recommended)
Use a service account when you want to avoid user-based OAuth, share access across a team, or connect spreadsheets owned by a workspace rather than an individual user.
#### Step 1: Create a service account and JSON key
Access your [Google Cloud Console](https://console.cloud.google.com/) and select the project you want to use.
On the sidebar, choose **IAM & Admin → Service Accounts → Create Service Account**.
Fill in the **Service account name** (for example, `integrateio-google-sheets`) and click **Done**. Roles are not required at the project level for Google Sheets; access is granted per spreadsheet in Step 3.
From the **Service Accounts** list, click the service account you just created and open the **Keys** tab.
Click **Add Key → Create new key**, choose **JSON**, and click **Create**. A `.json` key file is downloaded to your machine. Keep this file secure. You will upload it to Integrate.io in Step 4.
Copy the service account's email address (it ends in `@.iam.gserviceaccount.com`). You will need it in Step 3.
#### Step 2: Enable the Google Sheets API
In the Google Cloud Console, go to **APIs & Services → Library**.
Search for **Google Sheets API** and click **Enable**. If you plan to let Integrate.io list spreadsheets by name, also enable the **Google Drive API**.
#### Step 3: Share your spreadsheets with the service account
The service account can only access spreadsheets that have been explicitly shared with it.
Open the Google Sheets spreadsheet you want to connect to.
Click **Share** in the top-right corner.
Paste the service account's email address into the sharing field.
Choose the access level:
* **Viewer**, for source components that only read data.
* **Editor**, for destination components that write data.
Uncheck **Notify people** and click **Share**.
Repeat this step for every spreadsheet that should be available to Integrate.io.
#### Step 4: Create the connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) in the top-left menu.
Click **New connection**.
Select **Google Sheets (Service Account)**.
Name the connection and upload the JSON key file you downloaded in Step 1. The service account email is filled in automatically from the key file.
Click **Test Connection** to verify the credentials, then click **Create connection**.
### OAuth
To connect with a personal or user-based Google account instead of a service account:
Click the **Connections** icon (lightning bolt) on the top-left menu, then click **New connection**.
Select **Google Sheets**.
Click **Authenticate** and, if required, sign in to Google.
Click **Connect** to authorize Integrate.io ETL access to your Google Sheets account.
Name the connection and click **Create Google Sheets connection**.
Reconnect a connection if your password changes or the token expires. To delete a connection, click the three vertical dots on the far right of the connection listing and select **Delete connection**.
## Source Properties
The source component is configured in **Step 02** of the component editor.
### Spreadsheet
Select the spreadsheet to read from. The dropdown lists all spreadsheets that have been shared with the service account. Use the search field to filter by name. If your spreadsheet is not listed, make sure it has been shared with the service account's email address. See **Step 3** under Connection Setup above.
Click the **Refresh** button to reload the spreadsheet list if you have recently shared a new spreadsheet.
### Sheet
Select the sheet (tab) within the spreadsheet to read from. The dropdown is populated automatically after a spreadsheet is selected.
## Schema
After configuring the source properties, the **Schema** section (**Step 03**) displays the fields available in the pipeline. These are derived from the header row (row 1) of the selected sheet.
* The first row of the sheet is treated as the header row and defines the column names.
* Data types are automatically inferred from sample data rows. Supported types include: `string`, `long`, `double`, `boolean`, and `datetime`.
* If a column contains mixed types or cannot be inferred, it defaults to `string`.
**The sheet must have a header row.** Sheets without headers will produce unnamed columns (e.g. `column_0`, `column_1`)
## Related
# ETL: Heroku Postgres Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-heroku-postgres-cdc-source
How to configure the Heroku Postgres Snapshot CDC source component to read changed records from Heroku Postgres in your Integrate.io ETL pipeline.
Use the Heroku Postgres (Snapshot CDC) source component to read only the records that have changed in your Heroku Postgres tables since the previous pipeline run, instead of reprocessing the entire table every time. The component produces two outputs: **Upserted records** (new and updated rows) and **Deleted records** (rows removed from the source).
## Connection Setup
Before reading changes, set up your Heroku Postgres connection. See [Connecting to Heroku Postgres](/docs/etl/allowing-integrateio-etl-access-to-heroku-postgres-databases) for the credentials and network access required. Once the connection exists, add the **Heroku Postgres (Snapshot CDC)** source component to your pipeline and select it.
## Snapshot Storage
Heroku Postgres Snapshot CDC uses **File Based** snapshot storage. The snapshot of your previous run is stored as a Parquet file on Integrate.io managed cloud storage, which means:
* Only read access to your Heroku Postgres database is required.
* No snapshot tables are created in your source database.
* On the first run, every current record is treated as upserted because there is no previous snapshot to compare against.
Database (table-based) snapshot storage is only available for SQL Server connections. For Heroku Postgres, File Based storage is the only option, and you do not need to configure an S3 connection of your own.
## Change Detection
Choose how the component decides whether a row has changed:
* **Primary Key**: matches rows by a unique identifier column and detects updates by comparing the remaining column values. Best when the table has a reliable key.
* **Composite Hash**: builds a hash from all or selected columns and compares hashes between runs. Best when the table has no reliable primary key.
With File Based storage you can also switch the **Source Mode** from **Table** to **Query** to track changes across a custom SQL query, including JOINs across multiple tables.
## Full Configuration Reference
Configuration steps, change detection methods, query mode, best practices, troubleshooting, and limitations that apply to every Snapshot CDC source.
# ETL: MongoDB Source
Source: https://www.integrate.io/docs/etl/using-components-mongodb-source
Configure the MongoDB source component to read data from MongoDB collections and run queries in your Integrate.io ETL data pipeline.
## Connection Setup
Integrate.io ETL can access [your MongoDB](https://www.integrate.io/blog/mongodb-etl/) on a variety of services and deployment models. This article discusses providing Integrate.io ETL access on your MongoDB and then details creating the MongoDB connection in Integrate.io ETL.
You must provide Integrate.io ETL access to MongoDB. If it is behind a firewall:
* Create a MongoDB user and grant it minimum permissions required for Integrate.io ETL to read or write data from the database.
* Allow access from [Integrate.io ETL's IP addresses](/docs/etl/integrateio-etls-ip-list/) to MongoDB's port (Refer to [this](/docs/etl/allowing-integrateio-etl-access-to-my-server-behind-a-firewall/) article if you'd prefer to create a reverse SSH tunnel.)
## To create a MongoDB connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select MongoDB.
In the new Mongo DB connection window, name the connection and enter the connection information.
* **Name** - name for the new connection
* **User name** - database user name
* **Password** - database user's password
* **Hostname** - name of the host to connect to
* **Port** - TCP port to connect to. Allow Integrate.io ETL access to this port on the specified host for connectivity
* **Read Preference** - Determines how the connection should route read operations to members of a replica set.
* **Connection Scheme**
* DNS Seed List (SRV) - Connects via the mongodb+srv:// syntax (Read more [here](https://www.mongodb.com/docs/manual/reference/connection-string/#dns-seed-list-connection-format))
* Replica Set Members - Connects via the mongodb:// syntax. Specify the replica set members hostname manually.
* **Database** - name of database to use
* **Authentication Database** - name of the database to use for authentication. Leave empty to use default database.
* **Connect using SSL** - Determines whether to connect to the database using SSL. SSL allows encryption of client/server communications for increased security. *Always check SSL if connecting to Mongo Atlas.*
You can test the MongoDB connection by clicking **Test connection**. Note that although the test may fail, a job may be able to use the connection because the cluster on which you execute the job may have access to the database, whereas the Integrate.io ETL web application may not.
## To modify MongoDB connections in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
Click a connection to open and modify it. Make any necessary changes, then click Test connection, and Save changes. To exit the MongoDB connection window without changes, click Back to connections (grey tab on the left side) on the MongoDB connection window.
To delete a MongoDB connection, click the three vertical dots on the far right of the connection listing and select the Delete connection option.
**Note:**
For information on connecting to MongoDB Atlas, see [here](https://www.mongodb.com/docs/atlas/connect-to-database-deployment/).
***
Use the MongoDB source component to read data stored in a MongoDB collection.
## Connection
Select an existing MongoDB connection or create a new one (for more information, see [Allowing Integrate.io ETL access to MongoDB](/docs/etl/allowing-integrateio-etl-access-to-mongodb/ "Link: /etl/allowing-integrateio-etl-access-to-mongodb/").)
## Source Properties
* **Source collection** - the collection name from which the data will be imported.
* **Filter query** - use [MongoDB extended JSON](https://www.mongodb.com/docs/manual/reference/mongodb-extended-json/ "Link: http://docs.mongodb.org/manual/reference/mongodb-extended-json/") to apply a filter on MongoDB's server side, or leave empty to query the entire collection. Note that \$ is a special character that denotes a variable, so it must be escaped by a single back-slash in your extended JSON filter. For example:
* `{"age":{"\$gt":24}}` - extract all documents where age is greater than 24.
* `{"\$or":[{"price":{"\$exists":false}},{"price":{"\$eq":0}}]}` - extract all documents where price is zero or does not exist.
* `{"timestamp":{"\$gt":{"\$date":"2014-01-01T00:00:00.000Z"}}}` - extract all documents where timestamp is greater than the date value 2014-01-01T00:00:00.000Z
## Source Schema
After defining the source collection, select the fields to use in the source.
The fields you select are the only ones pulled from the source collection.
Define the data type for the field. Use the following table when matching MongoDB data types to Integrate.io ETL data types.
| **MongoDB** | **Integrate.io ETL** |
| :------------- | :------------------- |
| String | String |
| 32 Bit Integer | Integer |
| 64 Bit Integer | Long |
| Double | Double |
| Date | DateTime |
| Object | Json |
| Array | Json Array |
| Boolean | Boolean |
| ObjectID | String |
## Reading data incrementally from MongoDB
In order to read data incrementally (changes and additions) from a collection, we need a timestamp column that specifies when the data was updated (or inserted in collections where data is only inserted). In our example, this column is called "updated\_at". When reading from the source collection, we’ll use a **filter query** to only read the rows that were updated since the last time the package executed. We can use the following **filter query,** in which $last\_updated\_at is a package variable (make sure to use a single back-slash to escape the $ as is mentioned above):
`{"updated_at": {"\$gt":{"\$date": $last_updated_at}}}`
Note that the schema detection or data preview fails when using the variable in the **filter query,** as variables are not evaluated in design time.
You can use the predefined variable \_PACKAGE\_LAST\_SUCCESSFUL\_JOB\_SUBMISSION\_TIMESTAMP which returns the submission timestamp for the last successful execution of the package as you can see in the example below as a value for the variable, or use the ExecuteSqlDatetime function to execute a query on the target database to get the max (last) value of updated\_at in the target. Wrap the variable with a CASE statement to handle empty values and to allow full load if required. Then convert the datetime timestamp to a Unix timestamp and multiply it times 1000 to result in a Unix timestamp in milliseconds like MongoDB uses.
| **Variable name** | **Expression** |
| :---------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| full\_load | `0` |
| last\_updated\_at | `CASE ` ` WHEN (COALESCE($_PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP,'')=='' OR $full_load==1) THEN 0 ` ` ELSE ToUnixTime(ToDate($_PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP)) * 1000` `END` |
In order to store additions or changes in your database destination, make sure to mark the id column as key and change the operation type to "merge."
## Related
# ETL: Mouseflow Source
Source: https://www.integrate.io/docs/etl/using-components-mouseflow-source
Configure the Mouseflow source component in Integrate.io ETL to read websites, recordings, funnels, forms, and feedback into your data pipeline.
Use the Mouseflow source component to read website analytics, session recordings, funnels, forms, and feedback from your Mouseflow account into your [Integrate.io](http://integrate.io/) ETL pipeline.
## Connection
Select an existing Mouseflow connection or create a new one.
A Mouseflow connection requires:
* **Email.** The email address tied to your Mouseflow account.
* **API Key.** The API key generated in your Mouseflow account settings.
* **Website ID** *(optional)*. A default website to query when a source component does not specify its own.
The **Website ID** field on the connection is optional. Leave it blank when a single set of API credentials needs to read from multiple Mouseflow websites. Set the website per source component instead. The field stays visible only on legacy connections that already have a value saved.
## Source Properties
The source component is configured in Step 02 of the component editor.
### Website
The **Website** picker selects which Mouseflow website the source reads from. The picker loads the list of websites available to the connection's API credentials.
* The **Website** picker is shown when the connection has **no default Website ID** set. In that case, every source component must pick its own website.
* When the connection has a default Website ID, the picker is hidden and the connection-level value is used automatically. To read from a different website, create a separate connection without a default Website ID.
The **websites** object does not require a website selection; it lists every website your credentials can access.
The **recordings**, **funnels**, **forms**, and **feedback** objects require a website. The pipeline fails to start if no website is set on either the source or the connection.
Click the **Refresh** button next to the dropdowns to reload the lists of available websites and objects.
### Source Table (Object)
Select the Mouseflow object to read data from:
| Object | Description | Website required |
| ---------- | -------------------------------------------------------------------------------------------------------------------- | ---------------- |
| websites | All websites available to the connection's API credentials. Useful for discovering the Website IDs to use elsewhere. | No |
| recordings | Session recordings captured for the selected website, including visitor metadata, duration, and page counts. | Yes |
| funnels | Conversion funnels configured for the selected website, including step definitions and conversion stats. | Yes |
| forms | Form analytics for the selected website, including drop-off and completion data per field. | Yes |
| feedback | Feedback campaign responses collected from visitors on the selected website. | Yes |
### Load Type
Select how records are loaded on each pipeline run:
* **Full Load.** Fetches all records for the selected object on every run.
* **Incremental Load.** Fetches only records created or updated after a reference date. Use this for scheduled pipelines to avoid re-processing historical data.
### Incremental Load Settings
When **Incremental Load** is selected, the following options appear:
**Sync date field.** The date field used to filter records (for example, `created_at` or `updated_at`).
**Load records.** The filter condition:
* `newer than ( > )`: Fetch records after the reference date.
* `older than ( < )`: Fetch records before the reference date.
**Reference date.** Choose the source of the date value:
* **Last successful run.** Track records since the last successful run of this pipeline. Selecting this option auto-fills `incremental_load_date` with the `$_PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP` system variable, so each scheduled run picks up only what changed since the previous run finished. Recommended for scheduled pipelines.
* **Fixed Date.** Pick a specific calendar date. Use this for a one-time historical backfill.
* **Variable.** Use a custom package variable. Select this when you need to drive the start date from a value other than the last successful run timestamp.
## Schema
After configuring the source properties, the **Schema** section (Step 03) displays the available fields with their detected data types. Use the field selector to choose which columns to include in your pipeline. You can rename fields with aliases and change data types as needed.
The schema for **recordings**, **funnels**, **forms**, and **feedback** depends on the website you select, because field availability is scoped to each website's configuration.
## API: List Websites for a Connection
The component editor's **Website** picker calls a metadata endpoint that lists every Mouseflow website reachable through a stored connection. Build custom tooling against the same endpoint:
```http theme={null}
GET /:account_id/api/connections/metadata/mouseflow/:connection_id/websites
```
The response contains the websites returned by the Mouseflow API for the credentials saved on the connection.
# ETL: MySQL Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-mysql-cdc-source
How to configure the MySQL Snapshot CDC source component to read changed records from MySQL in your Integrate.io ETL pipeline.
Use the MySQL (Snapshot CDC) source component to read only the records that have changed in your MySQL tables since the previous pipeline run, instead of reprocessing the entire table every time. The component produces two outputs: **Upserted records** (new and updated rows) and **Deleted records** (rows removed from the source).
## Connection Setup
Before reading changes, set up your MySQL connection. See [Connecting to MySQL](/docs/etl/allowing-integrateio-etl-access-to-mysql-databases) for the credentials and network access required. Once the connection exists, add the **MySQL (Snapshot CDC)** source component to your pipeline and select it.
## Snapshot Storage
MySQL Snapshot CDC uses **File Based** snapshot storage. The snapshot of your previous run is stored as a Parquet file on Integrate.io managed cloud storage, which means:
* Only read access to your MySQL database is required.
* No snapshot tables are created in your source database.
* On the first run, every current record is treated as upserted because there is no previous snapshot to compare against.
Database (table-based) snapshot storage is only available for SQL Server connections. For MySQL, File Based storage is the only option, and you do not need to configure an S3 connection of your own.
## Change Detection
Choose how the component decides whether a row has changed:
* **Primary Key**: matches rows by a unique identifier column (for example `id` or `customer_id`) and detects updates by comparing the remaining column values. Best when the table has a reliable key.
* **Composite Hash**: builds a hash from all or selected columns and compares hashes between runs. Best when the table has no reliable primary key.
With File Based storage you can also switch the **Source Mode** from **Table** to **Query** to track changes across a custom SQL query, including JOINs across multiple tables.
## Example
Track customer information changes for an audit trail:
* **Source table**: `customers`
* **Method**: Primary Key
* **Primary key**: `customer_id`
* **Upserted records**: log changes for the audit trail
* **Deleted records**: process data deletion requests
## Full Configuration Reference
Configuration steps, change detection methods, query mode, best practices, troubleshooting, and limitations that apply to every Snapshot CDC source.
# ETL: NetSuite Source
Source: https://www.integrate.io/docs/etl/using-components-netsuite-source
Use the NetSuite source component to read NetSuite standard and custom records (tables) using the NetSuite JDBC drivers (SuiteAnalytics Connect).
## Connection Setup: NetSuite
## To create a NetSuite JDBC(Source) connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select **NetSuite JDBC(Source)**.
In the new NetSuite JDBC(Source) connection window, name the connection and enter the connection information.
* **Name** - name for the new connection
* **Hostname** - copy from SuiteAnalytics Connect configuration (see image below)
* **Port** - copy from SuiteAnalytics Connect configuration (see image below)
* **User name** - your NetSuite user name.
* **Password** - your NetSuite password.
* **Service data source** - copy from SuiteAnalytics Connect configuration (see image below).
* **Account ID** - copy from SuiteAnalytics Connect configuration (see image below).
* **Role ID** - copy from SuiteAnalytics Connect configuration (see image below).
Then, test the NetSuite connection by clicking **Test connection**. Once the connection tests successful, click **Create connection**.
You can find the connection information needed above in the SuiteAnalytics Connect configuration panel shown here:
## To setup Netsuite2.com as data source
Create a new role. Go to **Setup** > **Users/Roles** > **Manage Roles** > **New**.
Enter a name for this role. Select **Permissions** tab and then click **Setup** tab and assign following permissions:
* Log in using Access Tokens
* SuiteAnalytics Connect
**Note:**
The 'Subsidiary Restriction' section may limit access to certain records you wish to retrieve. Depending on the type of subsidiary structure your organization operates under, you should carefully select these options to ensure you obtain the desired data.
Grant an access to the selected tables. On the same role creation page, go to **Lists** tab and select tables.
**Note:**
The screenshot below is demonstrating how to enable access to the Account table. Before proceeding, we recommend coordinating with your business team to determine the complete list of NetSuite tables required for your integration. Once the list is finalized, please contact your NetSuite Account Manager for assistance in configuring the appropriate permissions to ensure all relevant tables are both visible and accessible for integration purposes.
Assign the Roles to the selected User. Select **Access** tab and then add following roles under **Roles** tab:
* The role that has just been created from previous step
* **Data Warehouse Integrator** role
Configure DB connection in Integrate.io ETL. Enter connection information (see steps "To create a Netsuite connection in Integrate.io ETL" from above) along with **Netsuite2.com** as Service data source and Role ID from the role that we just created from step 2.
## Connection Setup: NetSuite (SOAP)
## Setting up on NetSuite dashboard
To enable SOAP Web Services and Token Based Authentication, you need to log in to your Netsuite dashboard with admin roles and please follow these steps:
Go to Setup > Company > Enable Features > SuiteTalk (Web Services) and enable both "SOAP Web Services" and "Token-based Authentication".
Create a new Integration. Navigate to Setup > Integrations > Manage Integrations > New. Please note that "Token-based Authentication" must be checked.
After finished creation integration, save the Consumer Key and Consumer Secret as it will be only shown once and used for connection configuration on Integrate.io ETL.
Create a new role. Go to Setup > User Management > Manage Roles > New. Check "Web Services Only Roles" and add following permissions on Permissions > Setup tab:
* SOAP Web Services
* Log in using Access Tokens
* Custom Body Fields
* Custom Item Fields
* Custom Fields
**Note:**
The 'Subsidiary Restriction' section may limit access to certain records you wish to retrieve. Depending on the type of subsidiary structure your organization operates under, you should carefully select these options to ensure you obtain the desired data.
1. Assign the role from previous step to existing or a new user. Navigate to Setup > User Management > Manage Users.
2. Create a new access token. Go to Setup > User Management > Access Tokens. Select the name of Integration, User and Role from previous steps. After saving it, Token ID and Token Secret will be shown and keep those values securely.
## To create a NetSuite SOAP connection in Integrate.io ETL
Click the Connections icon (lightning bolt) on the top left menu.
To create a connection, click **New connection**.
Select **NetSuite SOAP**.
In the new NetSuite connection window, name the connection and enter the connection information.
* **Name** - name for the new connection
* **Account ID** - need to include the suffix `_SB` for Sandbox account.
* **Consumer Key** - copy from previous step or SuiteAnalytics Connect configuration.
* **Consumer Secret** - copy from previous step or SuiteAnalytics Connect configuration.
* **Token ID** - copy from previous step or SuiteAnalytics Connect configuration.
* **Token Secret Account ID** - copy from previous step or SuiteAnalytics Connect configuration.
Then, test the NetSuite connection by clicking **Test connection.** Once the connection tests successful, click **Create connection.**
***
## Connection
Select an existing NetSuite connection or create a new one (for more information, see [Allowing Integrate.io ETL access to NetSuite](/docs/etl/allowing-integrateio-etl-access-to-netsuite).)
## Source Properties
* **Source table** - the table name from which the data will be imported. Use the [Suitetalk Connect schema browser](https://www.netsuite.com/portal/developers/resources/suitetalk-documentation.shtml "Link: https://www.netsuite.com/portal/developers/resources/suitetalk-documentation.shtml") to look up standard records and columns.
* **where clause** - optional. You can add predicates clauses to the WHERE clause as part of the SQL query that is built in order to get the data from the database. Make sure to skip the keyword WHERE.
| Good | `prod_category = 1 AND prod_color = 'red'` |
| :---------------------------------------------------------------------------------------------------------------------------- | :----------------------------------------------- |
| Bad | `WHERE prod_category = 1 AND prod_color = 'red'` |
| **Notes:** in NetSuite2, the table names for entities such as customers, items, and contacts are singular rather than plural. | |
## Source Schema
After defining the source table/query select the fields to use in the source.
The fields you select are used to build the query that will be executed to read the data.
Define the data type for the field. Use the following table when matching NetSuite data types to Integrate.io ETL data types.
| NetSuite | Integrate.io ETL |
| :-------- | :--------------- |
| varchar2 | String |
| number | Double |
| timestamp | Datetime |
# ETL: Oracle ADW Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-oracle-adw-cdc-source
How to configure the Oracle ADW Snapshot CDC source component to read changed records from Oracle Autonomous Data Warehouse in your Integrate.io ETL pipeline.
Use the Oracle ADW (Snapshot CDC) source component to read only the records that have changed in your Oracle Autonomous Data Warehouse tables since the previous pipeline run, instead of reprocessing the entire table every time. The component produces two outputs: **Upserted records** (new and updated rows) and **Deleted records** (rows removed from the source).
## Connection Setup
Configure your Oracle ADW connection in Integrate.io, then add the **Oracle ADW (Snapshot CDC)** source component to your pipeline and select that connection. See the [Snapshot CDC Source reference](/docs/etl/using-components-cdc-database-source) for full configuration details.
## Snapshot Storage
Oracle ADW Snapshot CDC uses **File Based** snapshot storage. The snapshot of your previous run is stored as a Parquet file on Integrate.io managed cloud storage, which means:
* Only read access to your Oracle ADW instance is required.
* No snapshot tables are created in your source database.
* On the first run, every current record is treated as upserted because there is no previous snapshot to compare against.
Database (table-based) snapshot storage is only available for SQL Server connections. For Oracle ADW, File Based storage is the only option, and you do not need to configure an S3 connection of your own.
## Change Detection
Choose how the component decides whether a row has changed:
* **Primary Key**: matches rows by a unique identifier column and detects updates by comparing the remaining column values. Best when the table has a reliable key.
* **Composite Hash**: builds a hash from all or selected columns and compares hashes between runs. Best when the table has no reliable primary key.
With File Based storage you can also switch the **Source Mode** from **Table** to **Query** to track changes across a custom SQL query, including JOINs across multiple tables.
## Full Configuration Reference
Configuration steps, change detection methods, query mode, best practices, troubleshooting, and limitations that apply to every Snapshot CDC source.
# ETL: Oracle Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-oracle-cdc-source
How to configure the Oracle Snapshot CDC source component to read changed records from Oracle in your Integrate.io ETL pipeline.
Use the Oracle (Snapshot CDC) source component to read only the records that have changed in your Oracle tables since the previous pipeline run, instead of reprocessing the entire table every time. The component produces two outputs: **Upserted records** (new and updated rows) and **Deleted records** (rows removed from the source).
## Connection Setup
Before reading changes, set up your Oracle connection. See [Connecting to Oracle](/docs/etl/allowing-integrateio-etl-access-to-oracle-databases) for the credentials and network access required. Once the connection exists, add the **Oracle (Snapshot CDC)** source component to your pipeline and select it.
## Snapshot Storage
Oracle Snapshot CDC uses **File Based** snapshot storage. The snapshot of your previous run is stored as a Parquet file on Integrate.io managed cloud storage, which means:
* Only read access to your Oracle database is required.
* No snapshot tables are created in your source database.
* On the first run, every current record is treated as upserted because there is no previous snapshot to compare against.
Database (table-based) snapshot storage is only available for SQL Server connections. For Oracle, File Based storage is the only option, and you do not need to configure an S3 connection of your own.
## Change Detection
Choose how the component decides whether a row has changed:
* **Primary Key**: matches rows by a unique identifier column (for example `id` or `transaction_id`) and detects updates by comparing the remaining column values. Best when the table has a reliable key.
* **Composite Hash**: builds a hash from all or selected columns and compares hashes between runs. Best when the table has no reliable primary key.
With File Based storage you can also switch the **Source Mode** from **Table** to **Query** to track changes across a custom SQL query, including JOINs across multiple tables.
## Example
Track changes on a transactions table for downstream analytics:
* **Source table**: `transactions`
* **Method**: Primary Key
* **Primary key**: `transaction_id`
* **Upserted records**: load into the analytics warehouse
* **Deleted records**: flag as reversed in the warehouse
## Full Configuration Reference
Configuration steps, change detection methods, query mode, best practices, troubleshooting, and limitations that apply to every Snapshot CDC source.
# ETL: PDF to CSV
Source: https://www.integrate.io/docs/etl/using-components-pdf-to-csv-workflow
Configure the PDF to CSV workflow component to convert PDF files into CSV format as an automated step in your Integrate.io ETL workflow.
The PDF to CSV workflow component converts PDF files to CSV format as a step in your workflow.
## Overview
* Extracts tabular data from PDF files and converts to CSV
* Useful for processing invoices, reports, or other PDF-based data sources
* Outputs CSV files that can be used as input for subsequent dataflow packages
## Configuration
Select the source connection and path for the PDF file(s).Select the destination connection and path for the CSV output.Configure extraction options (page range, table detection settings).
# ETL: PostgreSQL Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-postgresql-cdc-source
How to configure the PostgreSQL Snapshot CDC source component to read changed records from PostgreSQL in your Integrate.io ETL pipeline.
Use the PostgreSQL (Snapshot CDC) source component to read only the records that have changed in your PostgreSQL tables since the previous pipeline run, instead of reprocessing the entire table every time. The component produces two outputs: **Upserted records** (new and updated rows) and **Deleted records** (rows removed from the source).
## Connection Setup
Before reading changes, set up your PostgreSQL connection. See [Connecting to PostgreSQL](/docs/etl/allowing-integrateio-etl-access-to-postgresql-databases) for the credentials and network access required. Once the connection exists, add the **PostgreSQL (Snapshot CDC)** source component to your pipeline and select it.
## Snapshot Storage
PostgreSQL Snapshot CDC uses **File Based** snapshot storage. The snapshot of your previous run is stored as a Parquet file on Integrate.io managed cloud storage, which means:
* Only read access to your PostgreSQL database is required.
* No snapshot tables are created in your source database.
* On the first run, every current record is treated as upserted because there is no previous snapshot to compare against.
Database (table-based) snapshot storage is only available for SQL Server connections. For PostgreSQL, File Based storage is the only option, and you do not need to configure an S3 connection of your own.
## Change Detection
Choose how the component decides whether a row has changed:
* **Primary Key**: matches rows by a unique identifier column (for example `id` or `order_id`) and detects updates by comparing the remaining column values. Best when the table has a reliable key.
* **Composite Hash**: builds a hash from all or selected columns and compares hashes between runs. Best when the table has no reliable primary key.
With File Based storage you can also switch the **Source Mode** from **Table** to **Query** to track changes across a custom SQL query, including JOINs across multiple tables.
## Example
Sync inventory changes to a downstream system:
* **Source table**: `inventory`
* **Method**: Composite Hash
* **Custom columns**: `sku`, `quantity`, `warehouse_id`
* **Upserted records**: update the inventory management system
* **Deleted records**: remove discontinued items
## Full Configuration Reference
Configuration steps, change detection methods, query mode, best practices, troubleshooting, and limitations that apply to every Snapshot CDC source.
# ETL: REST API Source
Source: https://www.integrate.io/docs/etl/using-components-rest-api-source
Configure the REST API source component to read data from HTTP endpoints and web services. Supports pagination and authentication methods.
Using the Rest API source provides access to read data from HTTP endpoints such as Rest Web Services. Use the Rest API source component to define the authentication method, request parameters, and response fields to use in the package. For OAuth-protected APIs, use the Connection option with a Universal OAuth connection to handle token management automatically.
## Authentication
* **None** - No authentication
* **Basic** - Use basic authentication (user and/or password).
* **Connection** - Use a saved Integrate.io ETL connection to handle authentication automatically. When selected, a **Choose connection** dropdown appears with two types of connections:
* **Pre-built service connections** – Ready-made connections for popular platforms, including: Google Analytics, Facebook Ads Insights, HubSpot (Service Token), HubSpot, Facebook Ads (Destination), Intercom, Xero, LinkedIn, Instagram, Google Sheets, Google Drive, YouTube, Shopify, Salesforce, Marketing Cloud (SOAP), and Marketing Cloud (REST).
* **Universal OAuth** – A flexible connection for any OAuth-protected API not covered by the pre-built options. Supports three authentication methods: OAuth 2.0 (authorization code flow), Client Credentials (server-to-server), and Custom Auth (non-standard endpoints). See [Allowing Integrate.io ETL access to any API with Universal OAuth](/docs/etl/allowing-integrateio-etl-access-to-any-api-with-oauth/) to set one up.
* **Tip:** If the API you want to connect to appears in the pre-built list, select it for the simplest setup experience. If your API is not listed, use the **Universal OAuth** connection to configure authentication manually.
## Using a Universal OAuth Connection
When you select **Connection** as your authentication method:
1. A **Choose connection** dropdown appears. Select an existing connection or create a new one.
2. The component will automatically attach the correct authentication header (typically `Authorization: Bearer {token}`) to every outbound API request.
3. If you configured a custom authentication header in your connection (e.g., `X-Shopify-Access-Token` for Shopify), that will be used instead, so no changes are needed in the Headers section.
4. Tokens are refreshed automatically when possible, so long-running jobs won't fail due to token expiry mid-execution.
## Request and response
### Request
* **method** - The HTTP method. Select GET (default), POST, PATCH or PUT.
* **URL** - Enter the URL of your source endpoint.
* **Headers** - Optionally add request headers (names and values)
* **Body** - Optionally add a request body string
### Pagination
Check **Use pagination** to have the source component make paginated requests. See [here](/docs/etl/api-endpoints-with-pagination-support) for list of supported API endpoints in addition to the standard [link headers](https://datatracker.ietf.org/doc/html/rfc5988).
* **Pagination scheme** - The method to paginate through response data (e.g. page number, offset+limit, cursor, link headers) and how to detect the final page.
* **Automatic** (Default) - the source detects the pagination scheme to use by the URL host/path.
* **Jira** - Use with hosted Jira installations.
* **Magento** - Use with hosted Magento installations.
* **BigCommerce** - Use with hosted BigCommerce installations.
* **Elasticsearch** - Use with hosted Elasticsearch installations.
* **Link headers** - the fallback option which will be used with the default **automatic** as well.
* **Sleep interval between paginated requests (ms)** - use to throttle API requests to avoid rate limitations. Default is 0 (no sleep).
* **Maximum paginated requests** - use to control the maximum number of pages for the source to go through. Default is unlimited, 0 or 1 means a single request will be made.
### Response
Select the response type to determine how to parse the response from the API endpoint.
* **Raw** - returns 3 fields: status code (integer), body (string) and headers (map).
* **JSON** - Response is processed using a JSONPath expression.\
You can use a custom JSONPath expression to extract nested objects and arrays or the presets for object and array. Using the object preset, the response fields are the keys of the JSON object. Using the array preset, the response fields are the keys of the JSON objects within the array and each object is returned as a record in the component's output.
* **Line Delimited JSON** - Response is a single JSON object per line. Note that the response as a whole is not a valid JSON. The response fields are the keys of the JSON objects and each object is processed as a record.
* **Line Delimited raw data** - Response is broken to record per line. Useful when data contains records delimited by new line.
### Extended JSON extraction
Setting this option to true enables extracting nested json fields into schema. As you can see on the screenshot below, each nested object property or array item can be selected directly in the schema section.
You can also modify key item directly in the schema in the case, where the nested attribute is missing in the list. The job will take that field for given record if it will be available. If not, then it will be set to null.
For example, in the below screenshot, tags is an array of object. However, first item has empty tags, so it's not available in the fields list. But you can modify Key manually to tags\[\*].name.
**Multiple output parses**
If you want to extract data from the REST API response for multiple components, then you can add new parse using + Add output parse button. It will add a new schema section. Each section/edge can be named differently.
## Schema
After defining the request and the response, select the fields to use in the source.
## Known Limitations
When using a \$ sign in the call, it needs to be escaped with back slash - \\
Please note - it will run successfully at runtime, but will fail with a preview of the API source component or component preview in the flow.
Only one bag field can be flattened per parse. To flatten another BAG field, create a new parse.
## Related
# ETL: Run Package
Source: https://www.integrate.io/docs/etl/using-components-run-package-workflow
Use the Run Package workflow task to execute a dataflow as a step in your Integrate.io ETL workflow and pass variable overrides.
The Run Package task executes a dataflow as a step in the current workflow.
## Overview
* Runs a dataflow as a step inside the current workflow.
* The parent workflow waits for the dataflow to finish before continuing.
* Supports overriding the dataflow's variables at run time.
* The dataflow's outcome (success or failure) decides which branch of the workflow runs next.
To run another workflow from a workflow, use the [Run Workflow task](/docs/etl/using-components-run-workflow) instead. Run Package only accepts dataflows.
## Configuration
Select the dataflow to execute.Optionally override the dataflow's variables with runtime values.Connect the task's outgoing edges to choose what runs on success, failure, or completion.
## Variables and secrets
The Run Package task always reads the child dataflow's current variables and secrets when it runs or is displayed in the workflow editor.
* **Variables you override on the task** are preserved. An override is any value you set on the task that the child dataflow has never had for that key.
* **Inherited variables** (values you did not change on the task) track the child dataflow. If you rename, add, remove, or change a variable on the child, the workflow reflects the change automatically.
* **Secrets** always come from the child dataflow. Per-task secret overrides are not supported. The editor shows the child's current masked secret values, and the run uses the child's real values.
You do not need to re-save the workflow when you update variables or secrets on a child dataflow. Changes are picked up on the next render or run.
### Exported workflows
When you export a workflow, each Run Package task keeps its variables as stored on the task. Inherited secrets are dropped from the export and re-inherited from the child dataflow on import.
## Use cases
* Chain multiple dataflows in sequence (for example, load staging, transform, load warehouse).
* Run a shared utility dataflow (for example, cleanup or notifications) from multiple workflows.
* Orchestrate multi-step pipelines with conditional branching based on each dataflow's outcome.
# ETL: Run Workflow
Source: https://www.integrate.io/docs/etl/using-components-run-workflow
Use the Run Workflow task to execute another workflow as a sub-run inside an Integrate.io ETL workflow, with scoped variables and branching.
The Run Workflow task executes another workflow as a sub-run of the current workflow. The parent workflow blocks until the child workflow reaches a terminal state, then branches on the outcome.
Use Run Workflow to compose workflows out of smaller workflows. To run a single dataflow as a step, use the [Run Package task](/docs/etl/using-components-run-package-workflow) instead.
## Overview
* Runs another workflow as a sub-run attached beneath the parent.
* The parent workflow waits until the child workflow finishes (succeeds, fails, or is stopped) before continuing.
* The child sub-run runs on the same cluster as the parent.
* Stopping the parent run cascades and stops any child sub-runs.
* The child workflow's outcome drives the parent's success, failure, and completion branches.
## Configuration
Add a **Run Workflow** task to the workflow canvas.Select the workflow you want to run. The selector only lists workflows, and you cannot reference the workflow the task lives in.Optionally set variable overrides to pass into the child workflow.Connect the outgoing edges to define what runs on success, failure, or completion of the child workflow.
## Nesting limit
A workflow can run other workflows, but those child workflows cannot themselves contain a Run Workflow task. Nesting is limited to one level.
Integrate.io checks this rule when you save the workflow and again when you start a run. If a workflow violates the limit, the validation error is:
> A Workflow that is run by another Workflow cannot itself run a Workflow (nesting is limited to one level)
A workflow also cannot reference itself, directly or through another workflow. Integrate.io blocks cycles at save time and at run launch.
## Variables and secrets
* **Variable overrides** you set on the task pass into the child run and merge over the child workflow's stored variables for that run only.
* Values you do not override track the child workflow. If the child's variables change, the next run picks up the change without re-saving the parent.
* **Secrets** always come from the child workflow. Per-task secret overrides are not supported.
## Run tree and monitoring
The child workflow appears as a sub-run beneath the parent run in the job history. You can open it to see its own tasks, logs, and status. Stopping the parent run also stops the child sub-run.
## Use cases
* Reuse an existing workflow as a step inside a larger orchestration.
* Split a long workflow into smaller workflows that can also run on their own.
* Branch the parent workflow based on whether a child workflow succeeds or fails.
# ETL: Salesforce Source
Source: https://www.integrate.io/docs/etl/using-components-salesforce-source
Configure the Salesforce source to read standard and custom objects using the Bulk API. Supports SOQL queries and incremental data loading.
## Connection Setup: Salesforce
Integrate.io ETL can read your Salesforce data. This article details creating the [Salesforce connection in Integrate.io ETL.](https://www.integrate.io/blog/salesforce-to-salesforce/) Note that Integrate.io ETL can only be used with [Salesforce](https://www.integrate.io/blog/category/salesforce/) editions that have API access. Some plan types, including trial versions, do not have API access by default and cannot be used with Integrate.io ETL.
**Salesforce orgs with IP login restrictions:** Salesforce connections now route through Integrate.io's secure proxy by default (enabled for all accounts). If your Salesforce org limits access by IP (for example, **Login IP Ranges** on a user profile, or **Trusted IP Ranges** under **Setup → Network Access**), allowlist Integrate.io's IP addresses so the connection can authenticate and run. Use the current list here: [Integrate.io ETL's IP addresses](/docs/etl/integrateio-etls-ip-list/).
### To create a Salesforce connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection.**
Select Salesforce.
Select the Salesforce environment you'd like to connect to - production or sandbox - and click **authorize**.
In the Salesforce login window, sign in with your Salesforce account. (Note: The user name for a sandbox account is your user name with a dot sandbox account name suffix. For example, If your user name is [danielle@ilovemycompany.com](mailto:danielle@ilovemycompany.com) and your sandbox account name is test, your user name for it is [danielle@ilovemycompany.com.test](mailto:danielle@ilovemycompany.com.test))
In the next page, click "Allow" to allow Integrate.io ETL access on your behalf to Salesforce.
In the new Salesforce connection window, name the connection and click **create salesforce connection.**
### To modify Salesforce connections in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
Click a connection to open and modify it. Make any necessary changes, then click **Reconnect**, and **Save changes**. To exit the Salesforce connection window without changes, click **Back to connections** (grey tab on the left side) on the Salesforce connection window.
To delete a Salesforce connection, click the three vertical dots on the far right of the connection listing and select the **Delete connection** option.
## Salesforce Apps Installation Restrictions (Oct 2025)
Salesforce recently introduced controls for **uninstalled connected apps**, which affect how users can approve third-party integrations like our ETL Salesforce connector. If a connected app isn't explicitly installed in a customer's Salesforce org, users without the proper permissions will encounter OAuth errors (such as `OAUTH_APPROVAL_ERROR_GENERIC`) during authentication. To fix this, customers must either install the connected app within their org or grant users the **"Approve Uninstalled Connected Apps"** permission through a profile or permission set. This permission allows users to authorize and connect to apps that aren't officially installed in their org.
For orgs that have **API Access Control** enabled, Salesforce replaces this with the **"Use Any API Client"** permission, which serves the same purpose. Support teams should advise customers to check their **Connected Apps OAuth Usage** page to confirm whether our ETL app appears as "uninstalled." If so, the Salesforce admin should either install it or assign one of the above permissions to affected users. This change ensures users can continue authenticating successfully with third-party apps like ours.
For more info, please check Salesforce article [here](https://help.salesforce.com/s/articleView?id=005132365\&type=1).
## Connection Setup: Salesforce (SOAP)
Integrate.io ETL can read your Salesforce SOAP data. This article details creating the Salesforce SOAP connection in Integrate.io ETL. Note that Integrate.io ETL can only be used with [Salesforce](https://www.integrate.io/blog/category/salesforce/) editions that have API access. Some plan types, including trial versions, do not have API access by default and cannot be used with Integrate.io ETL.
### To create a Salesforce SOAP connection in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection.**
Select Salesforce (SOAP).
In the new Salesforce (SOAP) window, name the connection and enter the connection information.
* **Name** - Name of the new connection
* **Instance URL** - Your Salesforce SOAP Instance URL (Login Server URL)
* **Username** - Your Salesforce SOAP Account Username
* **Password** - Your Salesforce SOAP Account Password
* **Security Token** - Your Salesforce SOAP Security Token. Obtain your security token by changing your password or resetting or security via Salesforce user interface. For more information, please refer to the [documentation](https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_concepts_security.htm)
Test the Salesforce SOAP connection by clicking Test connection. Once the connection tests successful, click Create connection.
### To modify Salesforce SOAP connections in Integrate.io ETL
Click the **Connections** icon (lightning bolt) on the top left menu.
Click a connection to open and modify it. Make any necessary changes, then click **Reconnect**, and **Save changes**. To exit the Salesforce SOAP connection window without changes, click **Back to connections** (grey tab on the left side) on the Salesforce SOAP connection window.
To delete a Salesforce SOAP connection, click the three vertical dots on the far right of the connection listing and select the **Delete connection** option.
## Connection Setup: Salesforce (Customer OAuth)
This guide explains how to retrieve the **Client ID**, **Client Secret**, **Authorization Code**, **Access Token**, **Refresh Token**, and **Instance URL** for OAuth 2.0 integration with Salesforce. We'll use **Postman** and curl to demonstrate the flow.
### Step 1: Get `Client ID` and `Client Secret` from Salesforce Dashboard
Log into Salesforce with an admin account.
In the top-right, click the **gear icon → Setup**.
In the left sidebar, search for **App Manager** and click it.
Click **New External Client App** (or find an existing one and click **View**).
Fill in:
* **Connected App Name**: `My OAuth App`
* **API Name**: auto-generated
* **Contact Email**: your email.
Scroll down to **API (Enable OAuth Settings)** and:
* Check **Enable OAuth Settings**
* **Callback URL**: use `https://oauth.pstmn.io/v1/callback` for Postman testing
* **Selected OAuth Scopes**:
* Perform requests at any time (refresh\_token, offline\_access)
* Manage user data via APIs (api)
* Full access (full)
* Uncheck **Require Proof Key for Code Exchange (PKCE)** option
* Check **Require secret for Web Server Flow** and **Require secret for Refresh Token Flow** options.
Click **Save**. You may need to wait a few minutes for the app to become active.
Go back to **App Manager**, find your app, and click **View**.
You'll now see:
* **Consumer Key** → this is your `client_id`
* **Consumer Secret** → this is your `client_secret` (click "Reveal")
### Step 2: Generate Authorization Code (using Postman)
Open Postman.
Create a new **OAuth 2.0** authorization under the **Authorization tab**.
Fill in:
* **Grant Type**: Authorization Code
* **Callback URL**: `https://oauth.pstmn.io/v1/callback`
* **Auth URL**: `https://login.salesforce.com/services/oauth2/authorize`
* **Access Token URL**: `https://login.salesforce.com/services/oauth2/token`
* **Client ID**: *(from Salesforce Dashboard)*
* **Client Secret**: *(from Salesforce Dashboard)*
* **Scope**: `full refresh_token api`
Click **Get New Access Token**.
You will be redirected to Salesforce → login and allow access.
Postman will receive a redirect like: `https://oauth.pstmn.io/v1/callback?code=YOUR_AUTH_CODE`
Copy the value of `code=...` – this is your **Authorization Code**.
### Step 3: Exchange Authorization Code for Tokens
Use the following `curl` command to exchange the authorization code for:
* `access_token`
* `refresh_token`
* `instance_url`
### CURL example:
```bash theme={null}
curl -X POST https://login.salesforce.com/services/oauth2/token \
-d "grant_type=authorization_code" \
-d "client_id=YOUR_CLIENT_ID" \
-d "client_secret=YOUR_CLIENT_SECRET" \
-d "redirect_uri=https://oauth.pstmn.io/v1/callback" \
-d "code=YOUR_AUTHORIZATION_CODE"
```
### Example Response:
```json theme={null}
{
"access_token": "00Dxx000000S1on!...",
"refresh_token": "5Aep86...etc",
"instance_url": "https://na123.salesforce.com",
"id": "https://login.salesforce.com/id/...",
"issued_at": "1711734351000",
"signature": "abcdef123456="
}
```
***
## Connection
Connections are part of configuring a no-code Salesforce integration, allowing secure API-based access without writing custom code.
Select an existing Salesforce connection or create a new one (for more information, see [Allowing Integrate.io ETL access to my data on Salesforce](/docs/etl/allowing-integrateio-etl-access-to-my-data-on-salesforce).)
## Source Properties
* **API Version -** list of Salesforce Bulk API versions we support. Read more about Bulk API 2.0 [here](https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/asynch_api_intro.htm)
* **Access mode** - select object to extract an entire object or query to execute a SOQL query.
* **Source object** - the table name from which the data will be imported.
* **where clause** - optional. You can add predicates clauses to the WHERE clause as part of the SQL query that is built in order to get the data from the database. Make sure to skip the keyword WHERE.
| Good | `prod_category = 1 AND prod_color = 'red'` |
| :--- | :----------------------------------------------- |
| Bad | `WHERE prod_category = 1 AND prod_color = 'red'` |
* **Source action** - Use `bulk query` (default) to read available records or `bulk query all` to read available and deleted records.
* **Query** - type in a SOQL query. Read more about SOQL syntax [here](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm).
* **Max records** - Specify the number of records to retrieve per page/API call to prevent timeouts. If left empty, all records will be retrieved in a single API call. Only available in Bulk API 2.0
## Source Schema
After defining the source object, select the fields to use in the source.
The fields you select are used to build the SOQL query that will be executed to read the data. Careful field selection helps reduce unnecessary API usage and improves performance in no-code Salesforce integrations.
## Loading data incrementally from Salesforce
Incremental loading is a common pattern in ETL products that support pulling data from systems like Marketing Cloud, transforming it, and writing updates back into Salesforce while minimizing API consumption
In order to load data incrementally (changes and additions) to objects, the object to synchronize should have the *systemmodstamp* column. This column is automatically updated whenever a user or an automated process updates a record. Use the following condition in the **where clause** field with a variable:
SystemModstamp > \$lastsysmod
You can use the last successful submission timestamp for the package as you can see in the example below as a value for the variable, or use ExecuteSqlDatetime function to get the last SystemModstamp in your destination database table.
```sql theme={null}
lastsysmod = CASE
WHEN (COALESCE($_PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP,'')=='') THEN '1900-01-01T00:00:00Z'
ELSE $_PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP END
```
Merge operations are typically used when synchronizing transformed data back into Salesforce to avoid duplicate records. In order to store additions or changes in your database destination, make sure to mark the id column as key and change the operation type to "merge":
## Enabling PK Chunking
You can enable **PK Chunking** for large datasets such as initial bulk load. By enabling this feature, Salesforce automatically splits the Bulk API Query job into multiple batches. Integrate.io ETL then polls for the progress of each of the batch then process them in parallel once all are done. The parallelism depends on the cluster node count (higher node count can pull more batches in parallel). Note that PK Chunking is disabled in Bulk API 2.0
## Limitations
It is currently not possible to join a Salesforce Sandbox environment source connection with a Salesforce Production environment source connection.
This limitation applies to Join, Union and Cross-join components to join Sandbox with Production source data.
## Querying Parent Fields
It is possible to execute query containing parent fields, for example following query:
```sql theme={null}
SELECT CreatedDate, Id, Sales_User__r.Id, Sales_User__r.Name
FROM Account
```
`Sales_User__r.Id` and `Sales_User__r.Name` are fields from `Account`'s parent object: `Sales__User__c` (`Sales_User__r` is the relationship name prefix for the `Account` object, for more details about the difference on `__c` and `__r` is [here](https://developer.salesforce.com/forums/?id=906F00000008llXIAQ))
Please note that when doing the query, the schema column might not pop up if the data returned is empty.
## Related
# ETL: SharePoint Source
Source: https://www.integrate.io/docs/etl/using-components-sharepoint-source
Configure the SharePoint source component in Integrate.io ETL to read documents and list items from Microsoft SharePoint into your data pipelines.
This article explains how to connect Integrate.io ETL to Microsoft SharePoint and how to use the SharePoint source component and its elements.
## Connection Setup
Integrate.io ETL can read your SharePoint data. This section details creating the SharePoint connection in Integrate.io ETL.
Click the **Connections** icon (lightning bolt) on the top left menu.
To create a connection, click **New connection.**
Select SharePoint.
Specify **Instance URL**. Enter your SharePoint Online tenant root domain in the format `.sharepoint.com` (for example, `contoso.sharepoint.com`). The `https://` prefix is optional and gets added automatically. Site collection paths, document library names, and folder paths are not allowed here; set those on the SharePoint source or destination component instead.
Only worldwide SharePoint Online tenants are supported. National cloud hosts (`.us`, `.cn`, `.de`) and on-premises SharePoint Server are not supported.
Click **Authenticate**.
In the Microsoft authentication window, select the account you want to use and authenticate.
After authentication, Integrate.io redirects you back to the connection form.
Specify the connection **Name**.
Click **Create Account**.
## SharePoint Source Component
### Choose input connection
Select a valid Sharepoint connection from the connections list.
### Source Properties
* **Site** - property indicates a Sharepoint site which contains needed files. You can find the current site in your browser's URL. It should have the following format: **https\://\.sharepoint.com/sites/\.** Only the site name should be specified in the related input. **https\://\.sharepoint.com** was already specified in the Sharepoint connection.
* **Source path** - property holds a path to a file/files. It can point to a concrete file or a number of files that can be matched with path patterns.
* If a concrete file should be matched, source input must have the following format: **/\/\/\.\**.
* If there are multiple files that have the same schema they can be merged and loaded using the following special characters:
* \* - matches any number of any characters, including none. For example, \*.csv matches all files ending in .csv. Any part of file path might be replaced with **\***.
* \*\* - matches any number of directories recursively. For example if file is located in /Shared Library/dir1/dir2/dir3/data.csv it can be matched like /Share Library/\*\*/data.csv or \*\*/data.csv.
* **Record type** - defines a file type that should be loaded.
* **Delimited values** - the files like csv, tsv or with any custom delimiter
* **Excel** - Microsoft excel files. Specify a **Sheet Name** to read a single sheet, or leave it blank to combine all sheets (all sheets must share the same schema). Enable **Ingest large Excel file** to use streaming mode for large workbooks.
* **Json Object** - files with json data
* **Raw** - any format, but should be further parsed with Integrate.io available functions
* **Load Type** - indicates how to load records:
* **Full Load** - loads all the data that source files have
* **Incremental Load** - loads files newer/older than specified date
### Header detection
For delimited and Excel files with a header row, you can enable **Detect header by name** to locate the header dynamically by matching your schema column names. Use this when the header isn't always on row 1 (for example, files with a leading title or blank rows).
* For delimited files, the full schema header must still appear in column order in each file.
* For Excel files, the option also matches columns by name, so the header can be on any row and the columns can be in any order. Schema preview reads from the file specified in **Schema file path**.
Enable **Match columns by name** on Excel sources to map workbook columns to your schema by header name instead of by position when the header is on row 1 but column order varies.
### Schema
Represents a view of sample data and properties parsed from the specified files. You can select needed properties and data types for further processing.
When streaming with a sheet name (**Ingest large Excel file** enabled and **Sheet Name** set), date cells come through as Excel serial numbers (e.g. `45306`) instead of formatted dates. Declare those columns as `int` or `decimal` in your schema, or transform them downstream.
# ETL: Snowflake Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-snowflake-cdc-source
How to configure the Snowflake Snapshot CDC source component to read changed records from Snowflake in your Integrate.io ETL pipeline.
Use the Snowflake (Snapshot CDC) source component to read only the records that have changed in your Snowflake tables since the previous pipeline run, instead of reprocessing the entire table every time. The component produces two outputs: **Upserted records** (new and updated rows) and **Deleted records** (rows removed from the source).
## Connection Setup
Configure your Snowflake connection in Integrate.io, then add the **Snowflake (Snapshot CDC)** source component to your pipeline and select that connection. See the [Snapshot CDC Source reference](/docs/etl/using-components-cdc-database-source) for full configuration details.
## Snapshot Storage
Snowflake Snapshot CDC uses **File Based** snapshot storage. The snapshot of your previous run is stored as a Parquet file on Integrate.io managed cloud storage, which means:
* Only read access to your Snowflake database is required.
* No snapshot tables are created in your source database.
* On the first run, every current record is treated as upserted because there is no previous snapshot to compare against.
Database (table-based) snapshot storage is only available for SQL Server connections. For Snowflake, File Based storage is the only option, and you do not need to configure an S3 connection of your own.
## Change Detection
Choose how the component decides whether a row has changed:
* **Primary Key**: matches rows by a unique identifier column and detects updates by comparing the remaining column values. Best when the table has a reliable key.
* **Composite Hash**: builds a hash from all or selected columns and compares hashes between runs. Best when the table has no reliable primary key.
With File Based storage you can also switch the **Source Mode** from **Table** to **Query** to track changes across a custom SQL query, including JOINs across multiple tables.
## Full Configuration Reference
Configuration steps, change detection methods, query mode, best practices, troubleshooting, and limitations that apply to every Snapshot CDC source.
# ETL: Snowflake Source
Source: https://www.integrate.io/docs/etl/using-components-snowflake-source
Configure the Snowflake source component to read data from Snowflake tables and views in your Integrate.io ETL data pipeline for processing.
Use the Snowflake source component to read data from Snowflake tables and views in your ETL pipeline.
## Connection Setup
To create a Snowflake connection, you need:
* Account locator (e.g., xy12345.us-east-1)
* Warehouse name
* Database name
* Schema name
* Username and password (or key pair authentication)
* Role (optional)
* Custom host (optional, for routing through a proxy such as Yuki Data)
Create the connection in Integrate.io ETL: click **Connections > New Connection > Snowflake**, enter your credentials, and test the connection.
## Source Configuration
Select an existing Snowflake connection or create a new one.Choose the source type: Table or Query.For Table mode: select the schema and table.For Query mode: write a custom SQL query.Configure field selection in the Source Schema tab.
## Options
* **Warehouse**: Specify which Snowflake warehouse to use for the query.
* **Role**: Override the default role for this component.
* **Query timeout**: Set a maximum execution time.
# ETL: SQL Server Snapshot CDC Source
Source: https://www.integrate.io/docs/etl/using-components-sql-server-cdc-source
How to configure the SQL Server Snapshot CDC source component to read changed records from SQL Server in your Integrate.io ETL pipeline.
Use the SQL Server (Snapshot CDC) source component to read only the records that have changed in your SQL Server tables since the previous pipeline run, instead of reprocessing the entire table every time. The component produces two outputs: **Upserted records** (new and updated rows) and **Deleted records** (rows removed from the source).
## Connection Setup
Before reading changes, set up your SQL Server connection. See [Connecting to SQL Server](/docs/etl/allowing-integrateio-etl-access-to-microsoft-sql-server-databases) for the credentials and network access required. Once the connection exists, add the **SQL Server (Snapshot CDC)** source component to your pipeline and select it.
## Snapshot Storage
SQL Server is the only connection type that supports both snapshot storage methods:
* **Database**: the snapshot is stored as a table in your SQL Server database, and change detection runs entirely in SQL. Requires write access (`CREATE TABLE`, `INSERT`, and `DELETE`) on the source database. This is the most efficient option when you have write access.
* **File Based**: the snapshot is stored as a Parquet file on Integrate.io managed cloud storage. Use this when your database user has read-only access, or when you do not want additional tables created in the source database.
When you use Database storage, a snapshot table is created automatically in the same schema as the source table and updated after each successful run. Do not modify or delete it manually.
## Change Detection
Choose how the component decides whether a row has changed:
* **Primary Key**: matches rows by a unique identifier column (for example `id` or `order_id`) and detects updates by comparing the remaining column values. Best when the table has a reliable key.
* **Composite Hash**: builds a hash from all or selected columns and compares hashes between runs. Best when the table has no reliable primary key.
Query mode (a custom SQL query instead of a single table) is available only with File Based storage. With Database storage, use Table mode with a Where clause to filter rows.
## Example
Track new and updated orders to sync with a data warehouse:
* **Source table**: `orders`
* **Method**: Primary Key
* **Primary key**: `order_id`
* **Snapshot Storage**: Database
* **Upserted records**: send to the data warehouse for processing
* **Deleted records**: mark as cancelled in the warehouse
## Full Configuration Reference
Configuration steps, change detection methods, query mode, best practices, troubleshooting, and limitations that apply to every Snapshot CDC source.
# ETL: SugarCRM Source
Source: https://www.integrate.io/docs/etl/using-components-sugarcrm-source
How to configure the SugarCRM component to read CRM data (Accounts, Contacts, Leads, and custom modules) in your Integrate.io ETL pipeline.
Use the SugarCRM source component to read records from any module in your SugarCRM instance, including standard modules (Accounts, Contacts, Leads, Opportunities, Cases, Tasks, Calls, Meetings, etc.) and any custom modules your tenant has, and ingest them into your [Integrate.io](http://integrate.io/) ETL pipeline. The connector authenticates against the SugarCRM REST API v11\_24 using your SugarCRM username and password, fetches an OAuth token internally on every job, and supports both full and incremental loads.
## Connection Setup
Create a SugarCRM connection from **Connections → New connection → SugarCRM**.
| Field | Description |
| :----------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Name | Display name for the connection inside Integrate.io |
| Instance URL | The base URL of your SugarCRM instance, e.g. `https://yourcompany.sugarondemand.com`. Do not include `/rest/...`; Integrate.io appends the API path automatically. |
| Username | The SugarCRM user the connector authenticates as. This user's role determines which modules and records the pipeline can read. |
| Password | The password for that SugarCRM user. Integrate.io uses this once per pipeline run to obtain a short-lived OAuth token from `/rest/v11_24/oauth2/token`; the password itself is never sent to SugarCRM modules. |
Use a dedicated **integration user** with read-only access to the modules you want to extract, rather than a regular user account. This isolates pipeline activity in Sugar's audit log and avoids breakage when a real user changes their password or leaves.
After filling the form, click **Test connection**. A successful test confirms that:
1. The instance URL resolves and `/rest/v11_24/oauth2/token` accepted the username + password
2. The user has at least read access to the `me` endpoint
If the test fails with `invalid_grant`, double-check the password and confirm the user can log in to SugarCRM's web UI directly. SugarCRM's password-grant OAuth flow uses the same credentials.
## Source Properties
The source component is configured in Step 02 of the component editor.
### Source Table (Object)
The dropdown lists every SugarCRM module the authenticated user can see. This includes:
* **Curated standard modules.** Exposed by default with rich datetime field metadata so incremental loading works out of the box
* **Discovered custom modules.** Anything else the user can see on the Sugar instance, queried from `/rest/v11_24/metadata?type_filter=full_module_list` once per pipeline run and merged into the picker
#### Curated standard modules
| Object | SugarCRM module | Datetime fields available for incremental load |
| :------------ | :-------------- | :------------------------------------------------------------ |
| accounts | Accounts | `date_entered`, `date_modified` |
| contacts | Contacts | `date_entered`, `date_modified` |
| leads | Leads | `date_entered`, `date_modified` |
| opportunities | Opportunities | `date_entered`, `date_modified`, `date_closed` |
| cases | Cases | `date_entered`, `date_modified` |
| notes | Notes | `date_entered`, `date_modified` |
| tasks | Tasks | `date_entered`, `date_modified`, `date_start`, `date_due` |
| calls | Calls | `date_entered`, `date_modified`, `date_start`, `date_end` |
| meetings | Meetings | `date_entered`, `date_modified`, `date_start`, `date_end` |
| emails | Emails | `date_entered`, `date_modified`, `date_sent` |
| campaigns | Campaigns | `date_entered`, `date_modified`, `start_date`, `end_date` |
| users | Users | `date_entered`, `date_modified` |
| documents | Documents | `date_entered`, `date_modified` |
| quotes | Quotes | `date_entered`, `date_modified`, `date_quote_expected_closed` |
| contracts | Contracts | `date_entered`, `date_modified`, `start_date`, `end_date` |
| bugs | Bugs | `date_entered`, `date_modified` |
| prospects | Prospects | `date_entered`, `date_modified` |
#### Custom modules
If your SugarCRM tenant has custom modules (e.g. `Subsc_Subscriptions`, `My_Custom_Module`), they appear in the dropdown under the lowercased module name (`subsc_subscriptions`, `my_custom_module`). The connector hits the SugarCRM REST endpoint at `/rest/v11_24/{ModuleName}` and exposes `date_entered` and `date_modified` as the default incremental fields. These are inherited from `SugarBean`, the base class every Sugar module extends, so they're present on every record by default.
Click the **Refresh** button next to the dropdown to re-query SugarCRM's module list. This is useful after an admin adds a new custom module or grants the integration user access to additional modules.
### Load Type
Select how records are loaded on each pipeline run:
* **Full Load.** Fetches all records for the selected object on every run.
* **Incremental Load.** Fetches only records created or updated after a reference date. Recommended for scheduled pipelines once the initial historical load is complete, so you don't re-process records that haven't changed.
### Incremental Load Settings
When **Incremental Load** is selected, three additional fields appear:
| Field | Description |
| :-------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Sync date field | The datetime field used to filter records server-side. Defaults to `date_modified` for most modules. You can pick any datetime field listed for the object (for example `date_closed` on Opportunities or `date_start` on Meetings), and SugarCRM will filter on that field instead. The connector substitutes the picked field into the API filter `filter[0][{field}][$gte]=...`, so jobs work consistently regardless of which datetime field you choose. |
| Load records | The filter direction. `newer than ( > )` fetches records with `{field} >= reference date`. `older than ( < )` fetches records with `{field} <= reference date`. |
| Reference date | The source of the date value. `Last successful run` auto-fills `incremental_load_date` with the `$_PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP` system variable, so each scheduled run picks up only what changed since the previous run finished. This is the recommended option for scheduled pipelines. `Fixed Date` uses a specific calendar date, useful for one-off backfills. `Variable` uses a custom package variable, for cases where you need to drive the start date from a value other than the last successful run timestamp. |
SugarCRM expects ISO 8601 dates. The connector passes the date value directly to Sugar as provided. If you use a system variable like `$package_last_successful_job_submission_timestamp`, Integrate.io fills it in with a UTC ISO 8601 timestamp automatically.
## Field Flattening
The SugarCRM REST API returns nested JSON for some fields, including relate links, address objects, and custom field groups. The connector automatically **flattens** these into individual columns using underscore-separated naming so each leaf value lands in its own column instead of a serialized JSON blob.
For example, a relate field that the API returns as:
```json theme={null}
{
"assigned_user_link": {
"name": "Jane Doe",
"id": "abc-123"
}
}
```
is flattened into two columns: `assigned_user_link_name` and `assigned_user_link_id`. This lets you map the columns directly to your destination schema without writing a downstream transformation to parse JSON.
## Schema
After configuring the source properties, the **Schema** section (Step 03) displays all available fields with their detected data types.
Common SugarCRM field types and how they map:
| SugarCRM type | Integrate.io type | Notes |
| :--------------------------------------------- | :---------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------- |
| varchar / text / name / phone / email | string | All string-shaped fields |
| int / id | string | SugarCRM IDs are 36-char UUIDs returned as strings |
| datetime / date | string | ISO 8601 strings (e.g. `2026-04-28T18:01:12+10:00`); cast to `datetime` in a downstream Select transformation if your destination needs typed timestamps |
| bool | boolean | |
| decimal / float / currency | double | |
| enum / multienum | string | Multi-select enums are returned as comma-separated strings |
| relate (assigned\_user\_id, account\_id, etc.) | string | The ID of the related record. The display name is in the corresponding `*_link_name` field after flattening. |
Use the field selector to choose which columns to include in your pipeline. You can rename fields with aliases and override the detected data type as needed.
## Best Practices
* **Use a dedicated integration user** with the minimum module-level role needed. Avoid using a real person's account. If their password changes, every pipeline breaks at the next run.
* **Start with full load, switch to incremental** once the destination is seeded with historical data. Use `date_modified` as the sync field for most modules, as it covers both inserts and updates. Only switch to `date_entered` if you specifically want inserts only.
* **Pick the right datetime field for the module's semantics.** `date_modified` catches every change. `date_closed` on Opportunities only fires when the opportunity stage moves to Closed Won/Lost, which is useful for daily revenue reporting but skips opportunities still in flight. `date_start` on Calls/Meetings reflects when the activity is scheduled, not when it was logged.
* **Custom module datetime fields**: discovered custom modules default to `date_entered` and `date_modified` only. If your custom module has additional date fields you want to filter on (e.g. `effective_date_c`), pick the field manually in the dropdown. The connector will still build a valid filter.
* **Refresh the object list** after Sugar admins add or rename modules. The list is cached for 1 hour per connection; the **Refresh** button bypasses the cache.
## FAQ
**Q: Why is my custom module not in the dropdown?**
The connector lists modules using the SugarCRM `/rest/v11_24/metadata?type_filter=full_module_list` endpoint, which only returns modules the authenticated user can see. If the integration user doesn't have a role granting access to that module, it won't appear. Have a SugarCRM admin grant the user a role with read access to the module, then click **Refresh** in the source dropdown.
**Q: I picked `date_entered` for incremental load, but the pipeline returns data older than my reference date. What's wrong?**
This was a bug in earlier versions of the connector. The filter was hard-coded for `date_modified`, so when you picked a different field the filter was silently dropped and SugarCRM returned all records. The current connector substitutes the picked field into the filter automatically, so the issue should be resolved. If you're still seeing it, check the pipeline log for the line `Incremental date filter for API:` and confirm it shows `filter[0][date_entered][$gte]=...` (not `date_entered=...`). If the wrapper is missing, your job is running against an older Pig JAR. Contact support to upgrade.
**Q: What SugarCRM API version does the connector use?**
`v11_24`. This applies to both the OAuth token endpoint (`/rest/v11_24/oauth2/token`) and all data endpoints (`/rest/v11_24/{Module}`). SugarCRM's `v11` is an alias for the latest minor, but the connector pins to a specific version for stability across SugarCRM tenant upgrades.
**Q: How does pagination work?**
The connector uses SugarCRM's offset-based pagination (`offset=` + `max_num=1000` per page). Up to 10,000 pages are followed per object per run, which translates to 10 million records, enough for any single full load. The schema-discovery and data-preview steps cap at 50 records to keep the UI responsive.
**Q: Is there a rate limit?**
The connector throttles outbound requests to 15 requests per second by default and retries up to 3 times on HTTP 429 / 503 with exponential backoff capped at 60 seconds. SugarCRM's per-tenant API quotas are typically much higher, so this rarely matters in practice, but if you run many concurrent pipelines against the same tenant you may want to stagger them.
## Related
# ETL: Trustpilot Source
Source: https://www.integrate.io/docs/etl/using-components-trustpilot-source
How to configure the Trustpilot component to read service reviews and business categories in your Integrate.io ETL pipeline.
Use the Trustpilot source component to read your company's service reviews and business categories from the Trustpilot Business Units API and ingest them into your [Integrate.io](http://integrate.io/) ETL pipeline. The connector supports two authentication modes: an API key alone reads public review data, and adding your API secret unlocks private reviews with server-side incremental loading.
## Connection Setup
Create a Trustpilot connection from **Connections → New connection → Trustpilot**.
| Field | Description |
| :--------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Name | Display name for the connection inside Integrate.io |
| Business Unit ID | The identifier of your business profile on Trustpilot (a 24-character ID such as `46d95a2b0000640005003f6f`). Every source table is scoped to this business unit. See [Finding your Business Unit ID](#finding-your-business-unit-id) below. |
| Business User ID | Optional. Not used by the source component; reserved for future features such as sending review invitations. Leave blank unless instructed otherwise. |
| API Key | The API key (also called the Client ID) of your Trustpilot application. Created in the Trustpilot Business portal under **Integrations → Developers → APIs → Create Application**. |
| API Secret | Optional. The client secret of the same Trustpilot application. When set, the connector authenticates through Trustpilot's OAuth client\_credentials flow and the **Private Reviews** table becomes available, including incremental loading. When left blank, the connector uses the public API with the key alone. |
API access requires a Trustpilot Business account with the API module. If you do not see the Developers section in your Trustpilot portal, contact Trustpilot to enable API access on your plan.
After filling the form, click **Test connection**:
* With an API key only, the test reads your public business profile.
* With an API key and secret, the test obtains an OAuth token and reads the private reviews endpoint, so a passing test confirms the whole private data path works.
### Finding your Business Unit ID
The quickest way is Trustpilot's public lookup endpoint. Replace `YOUR_API_KEY` and your website domain:
```bash theme={null}
curl -H "apikey: YOUR_API_KEY" \
"https://api.trustpilot.com/v1/business-units/find?name=yourcompany.com"
```
The `id` field in the response is your Business Unit ID.
## Source Tables
The dropdown in Step 02 of the component editor lists the available tables. Which tables appear depends on the connection's authentication mode:
| Table | Requires API Secret | Incremental Load | Contents |
| :--------------- | :------------------ | :--------------- | :--------------------------------------------------------------------------------------------------------------------------------- |
| reviews | No | No | Public service reviews for your business unit: stars, title, text, language, consumer display info, timestamps |
| categories | No | No | The business categories your company is listed under, with ranking position |
| private\_reviews | Yes | Yes | The same service reviews through Trustpilot's private API, which adds consumer email, reference ID, and other business-only fields |
Public and private reviews cover the same underlying review stream. The private table exists because Trustpilot's public API neither returns business-only fields nor accepts date filters. If you have the API secret, prefer **private\_reviews** for scheduled pipelines.
## Load Type
Select how records are loaded on each pipeline run:
* **Full Load.** Fetches all records for the selected table on every run. This is the only mode for `reviews` and `categories`.
* **Incremental Load.** Available on `private_reviews` only. Fetches only reviews created after (or before) a reference date, using Trustpilot's server-side `startDateTime`/`endDateTime` filters.
### Why only private\_reviews supports incremental loading
This is a Trustpilot API constraint, not a connector limitation. The public reviews endpoint accepts no date-filter parameters at all (only star rating, language, page, and sort order), so there is nothing for an incremental filter to hook into. Offering the option would silently load the full table on every run. The private endpoint accepts `startDateTime` and `endDateTime`, so incremental loading is offered exactly where the API supports it.
### Incremental Load Settings
When **Incremental Load** is selected on `private_reviews`, three additional fields appear:
| Field | Description |
| :-------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Sync date field | `createdAt`, the time the review was written. This is the only field offered because Trustpilot's date filters operate on creation time. Filtering on other datetime fields is not supported by the API. |
| Load records | The filter direction: `newer than ( > )` or `older than ( < )` the reference date. |
| Reference date | `Last successful run` auto-fills the date from the previous successful job, so each scheduled run picks up only new reviews. This is the recommended option for scheduled pipelines. `Fixed Date` and `Variable` are useful for one-off backfills. |
Because the filter operates on `createdAt`, an edited review does not re-sync on incremental runs; only newly written reviews are picked up. If you need edits reflected, schedule a periodic full load alongside the incremental pipeline.
## The 100,000 Record Limit on Public Reviews
Trustpilot caps the public reviews endpoint at 100,000 retrievable records. This is a hard API ceiling: requests beyond it are rejected by Trustpilot regardless of the client.
* A full load of `reviews` on a business unit with more than 100,000 reviews stops at 100,000 records (the most recent ones, in the API's default ordering). The job completes successfully at that point, so if your business unit has more reviews than that, compare the job's output record count with your review total on Trustpilot to know whether you hit the ceiling.
* The remaining older reviews cannot be fetched through the public endpoint.
To load a complete history of more than 100,000 reviews, use `private_reviews` and split the history into two one-off loads around a midpoint date, since each run applies a single date bound:
1. One run with **Load records** set to `older than ( < )` and a `Fixed Date` at the midpoint
2. One run with **Load records** set to `newer than ( > )` and the same `Fixed Date`
Pick a midpoint that puts fewer than 100,000 reviews on each side. If your destination appends records, use the review `id` as the merge or dedupe key so a review written exactly on the boundary date is not loaded twice. After the backfill, switch to Incremental Load with `Last successful run` for ongoing syncs. For histories above 200,000 reviews, two segments are not enough; contact support to plan the backfill.
## Field Flattening
The Trustpilot API returns nested JSON objects on each review (the consumer who wrote it, the business unit, invitation metadata). The connector automatically flattens these into individual columns with underscore-separated names, so each value lands in its own column instead of a serialized JSON blob.
For example, the nested consumer object:
```json theme={null}
{
"consumer": {
"displayName": "Jane D.",
"numberOfReviews": 12
}
}
```
becomes the columns `consumer_displayName` and `consumer_numberOfReviews`.
## Schema
After configuring the source properties, the **Schema** section (Step 03) displays the discovered fields with detected data types. Typical fields on the reviews tables:
| Field | Type | Notes |
| :-------------------- | :------- | :----------------------------------------------------------------------------------- |
| id | string | Trustpilot review ID |
| stars | long | Star rating, 1 to 5 |
| title / text | string | Review title and body |
| language | string | Two-letter language code |
| createdAt | datetime | When the review was written; the incremental sync field on private\_reviews |
| experiencedAt | datetime | When the reviewed experience happened |
| isVerified | boolean | Whether the review is verified |
| consumer\_displayName | string | Flattened from the nested consumer object |
| businessUnit\_id | string | Flattened from the nested business unit object |
| referenceId | string | private\_reviews only: your order or reference identifier from the review invitation |
Use the field selector to choose columns, rename with aliases, and override detected types as needed.
## Best Practices
* **Set the API secret if you have it.** It unlocks incremental loading and the business-only fields, and scheduled pipelines become dramatically cheaper: an incremental run fetches only new reviews instead of re-reading the whole table.
* **Use incremental with Last successful run** on `private_reviews` for scheduled pipelines, after seeding the destination with one full load (or date-windowed backfill for histories over 100,000 reviews).
* **Full loads of public reviews re-read everything.** A business unit with 100,000 reviews takes roughly 1,000 API calls per run. This is fine for one-off or occasional pulls, but prefer the private table for recurring syncs.
* **Restart clusters after changing the connection.** Connection settings, including adding the API secret, are applied to a cluster when it starts. Jobs on an already-running cluster keep the old settings until the cluster is restarted.
## FAQ
**Q: Why do reviews and categories not support incremental loading?**
Trustpilot's public API endpoints accept no date-filtering parameters, so there is no way to ask the API for "reviews since date X". The private reviews endpoint does accept date filters, which is why `private_reviews` (available once the API secret is set) supports incremental loading. See [Why only private\_reviews supports incremental loading](#why-only-private_reviews-supports-incremental-loading).
**Q: I set the API secret but do not see the private\_reviews table. Why?**
Click the **Refresh** button next to the table dropdown to bypass the cached table list. If it still does not appear, run **Test connection** on the connection: in secret mode the test validates the OAuth token, so a failing test means the key and secret pair is not valid for token minting (check for typos, and confirm both values come from the same Trustpilot application).
**Q: How does authentication work in each mode?**
With an API key only, requests carry the key in Trustpilot's `apikey` header. With a key and secret, the connector obtains an OAuth access token through Trustpilot's client\_credentials grant and sends it as a bearer token. Tokens live for around 100 hours and are cached and reused across runs; obtaining a new token does not invalidate existing ones.
**Q: Is the API secret visible after I save it?**
No. The secret is write-only: it is stored encrypted and never returned to the browser. The connection form shows a masked placeholder with a change link, the same pattern as the API key.
**Q: How does pagination and rate limiting work?**
The connector pages through results 100 records at a time and throttles to 5 requests per second, retrying up to 3 times on transient errors with backoff. Public reviews stop at Trustpilot's 100,000-record ceiling; see [the section above](#the-100000-record-limit-on-public-reviews) for loading longer histories.
## Related
# ETL: Zendesk Source
Source: https://www.integrate.io/docs/etl/using-components-zendesk-source
Configure the Zendesk source component in Integrate.io ETL to read tickets, users, organizations, and other Zendesk objects into your pipeline.
Use the Zendesk source component to read data from your Zendesk Support account — including tickets, users, organizations, ticket events, and configuration objects — into your [Integrate.io](http://integrate.io/) ETL pipeline.
## Connection
Select an existing Zendesk connection or create a new one. A Zendesk connection authenticates with your Zendesk subdomain over OAuth.
## Source Properties
The source component is configured in Step 02 of the component editor.
### Source Table (Object)
Select the Zendesk object to read data from. The connector exposes 21 objects:
| Object | Endpoint | Incremental Load |
| ------------------------- | ------------------------------------------ | ---------------------- |
| tickets | `/api/v2/tickets` | Supported (after-only) |
| users | `/api/v2/users` | Supported (after-only) |
| organizations | `/api/v2/organizations` | Supported (after-only) |
| ticket\_events | `/api/v2/incremental/ticket_events` | Supported (after-only) |
| ticket\_metric\_events | `/api/v2/incremental/ticket_metric_events` | Supported (after-only) |
| groups | `/api/v2/groups` | Not supported |
| brands | `/api/v2/brands` | Not supported |
| ticket\_fields | `/api/v2/ticket_fields` | Not supported |
| ticket\_forms | `/api/v2/ticket_forms` | Not supported |
| ticket\_metrics | `/api/v2/ticket_metrics` | Not supported |
| ticket\_activities | `/api/v2/activities` | Not supported |
| satisfaction\_ratings | `/api/v2/satisfaction_ratings` | Not supported |
| tags | `/api/v2/tags` | Not supported |
| user\_fields | `/api/v2/user_fields` | Not supported |
| organization\_fields | `/api/v2/organization_fields` | Not supported |
| organization\_memberships | `/api/v2/organization_memberships` | Not supported |
| group\_memberships | `/api/v2/group_memberships` | Not supported |
| macros | `/api/v2/macros` | Not supported |
| automations | `/api/v2/automations` | Not supported |
| triggers | `/api/v2/triggers` | Not supported |
| schedules | `/api/v2/business_hours/schedules` | Not supported |
Click the **Refresh** button next to the dropdown to reload the list of available objects.`ticket_events` and `ticket_metric_events` are only available through Zendesk's incremental export endpoints. In Full Load mode they fetch the full history from epoch `0`.
### Load Type
Select how records are loaded on each pipeline run:
* **Full Load.** Fetches all records for the selected object on every run.
* **Incremental Load.** Fetches only records updated after a reference date. Use this for scheduled pipelines to avoid re-processing historical data.
Incremental Load is available for `tickets`, `users`, `organizations`, `ticket_events`, and `ticket_metric_events`. For other objects the UI shows **Incremental Load not supported** — Zendesk's REST endpoints for those objects don't accept a date filter.
### Incremental Load Settings
When **Incremental Load** is selected, the following options appear:
**Sync date field.** The date field used to filter records. The default per object:
| Object | Sync date field |
| ---------------------- | --------------- |
| tickets | `updated_at` |
| users | `updated_at` |
| organizations | `updated_at` |
| ticket\_events | `created_at` |
| ticket\_metric\_events | `time` |
**Load records.** Zendesk's incremental export API is after-only. The `start_time` query param has no `end_time` equivalent, so only `newer than ( > )` is available for Zendesk objects. The `older than ( < )` option is hidden in the Load Type dropdown.
**Reference date.** Choose the source of the date value:
* **Last successful run.** Track records since the last successful run of this pipeline. Selecting this option auto-fills `incremental_load_date` with the `$_PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP` system variable, so each scheduled run picks up only what changed since the previous run finished. Recommended for scheduled pipelines.
* **Fixed Date.** Pick a specific calendar date. Use this for a one-time historical backfill.
* **Variable.** Use a custom package variable. Select this when you need to drive the start date from a value other than the last successful run timestamp.
Timestamps are sent to Zendesk as Unix seconds via the `start_time` query parameter. The connector handles the conversion internally — you pick a date or variable, not a Unix timestamp.
### How Incremental Load Works per Object
* **tickets.** The connector appends `?start_time=` to `/api/v2/tickets`. No endpoint change.
* **users, organizations, ticket\_events, ticket\_metric\_events.** Zendesk doesn't accept a date filter on the regular list endpoints, so the connector swaps to `/api/v2/incremental/