Fivetran
The Fivetran destination lets you synchronize data from over 400 sources to Propel's Serverless ClickHouse infrastructure. It provides an easy way to power your customer-facing analytics and data applications with data from any SaaS application, database, or platform supported by Fivetran.
Consider using Fivetran to sync data to Propel when:
- You need to sync data from a SaaS application or platform like Hubspot, Stripe or Shopify.
- You need to sync data from a database that Propel does not support natively like Oracle, MSSQL, MySQL, or PostgreSQL.
- You need to sync data from streaming sources that Propel does not support natively like AWS Kinesis or Google Pub/Sub.
Request access to the preview
Contact us if you are interesting in getting early access to the Fivetran destination to sync your data to Propel.
Check out the Setup Guide.
Check out the Propel Fivetran destination docs.
Features
Data Pools created with the Fivetran destination are Webhook Data Pools that support the following features:
| Feature name | Supported | Notes |
|---|---|---|
| Syncs inserts, updates, and deletes | ✅ | Propel processes inserts, updates, and deletes sent from Fivetran. |
| Re-sync | ✅ | You can trigger a resync in Fivetran. |
| Configurable sync interval | ✅ | You can configure sync interval in Fivetran. |
| Sync Pausing / Resuming | ✅ | You can pause / resume syncs in Fivetran. |
| Schema Changes: Add Column | ✅ | When Fivetran detects a column was added in your source, it will automatically add that column in Propel. |
| Schema Changes: Change column types | ❌ | Changing columns types is not supported as it is a breaking schema change. |
| Schema Changes: Delete column | ❌ | Deleting columns is not supported as it is a breaking schema change. |
| Batch Delete API | ✅ | See Batch Delete API. |
| Batch Update API | ✅ | See Batch Update API. |
| API configurable | ✅ | See API reference docs. |
| Terraform configurable | ✅ | See Propel Terraform docs. |
How does the Fivetran destination work?
When syncing a Fivetran source, Propel creates a Webhook Data Pool for every table. The example below shows the tables that are created for a Google Search Console Fivetran source. Propel will create a Webhook Data Pool for each table.
The Data Pools will have the same columns as the source table plus the following columns added by Fivetran and Propel.
| Column | Type | Description |
|---|---|---|
| _propel_received_at | TIMESTAMP | The timestamp when the record was received by Propel in UTC. |
| _propel_payload | JSON | The JSON Payload of the event. |
| _fivetran_synced | TIMESTAMP | The timestamp when the sync was initiated by Fivetran in UTC. |
Once connected, Fivetran will perform an initial sync and subsequent syncs based on the sync frequency you have configured. Fivetran will send the data to Propel via the Webhook Data Pool API. Propel will process the data and make it available for querying via the SQL and API.
Default timestamp column
Propel will use the first timestamp column in the table as the deafult timestamp. If the table does not have any timestamp columns, Propel will use the _propel_received_at as the default timestamp.
Primary key
Propel will use the tables primary key as the Data Pool's primary key. If the source table does not have a primary key, Fivetran will generate one.
Sort Key
Propel uses the primary key and the default timestamp as the Data Pool's sort key to order the data. You use a Materialized View to create a new Data Pool with a different Sort Key that orders the data by a different set of fields.
Updates and deletes
If a record is updated in the source, Fivetran will send the updated record to Propel. Propel will identify the record based on the primary key and update the record in the Data Pool.
If a record is deleted in the source, Fivetran will send a delete event to Propel. Propel will identify the record based on the primary key and delete the record from the Data Pool.
Schema changes
When Fivetran detects a column was added in your source, it will automatically add that column in Propel. Changing columns types or deleting columns is not supported as it is a breaking schema change.
Data Types
The following table illustrates how we transform your Fivetran data types into Propel-supported types:
| FIVETRAN DATA TYPE | PROPEL DATA TYPE |
|---|---|
| BOOLEAN | BOOLEAN |
| SHORT | INT16 |
| INT | INT32 |
| LONG | INT64 |
| BIGDECIMAL | DOUBLE |
| FLOAT | FLOAT |
| DOUBLE | DOUBLE |
| LOCALDATE | DATE |
| LOCALDATETIME | TIMESTAMP |
| INSTANT | TIMESTAMP |
| STRING | STRING |
| XML | STRING |
| JSON | JSON |
| BINARY | STRING |
Transforming data
Once the data has been ingested into the Webhook Data Pool, you can create Materialized Views to transform the data. This includes transformations such as ordering, filtering, aggregation, and joining with other data. These transformations are defined using SQL and can be updated in real time as new data arrives.
Materialized Views can be used to:
- Separate the messages from a specific topic into their own tables, each with its own schema.
- Handle real-time updates and deletes for mutable data.
- Transform data in real time.
- Enrich data joining with other Data Pools.
Learn more about Transforming your data with Materialized Views.
API reference documentation
Below is the relevant API documentation for the Webhook Data Pool.