Fivetran

Data integration in Fivetran has three key facets: prebuilt, fully managed connectors; normalisation of the data you are moving; and provision of analysis-ready schemas for target connectors. Between them this means the process of data integration, leaving aside the definition of relevant transformations, is almost completely automatic. Moreover, Fivetran operations are idempotent, meaning that however many times you process a particular workflow you will always get the same result. In other words, once a process flow is correct it will always be correct, and data integrity will be maintained. If it fails, automatic recovery is available. Idempotency is achieved, in part, because Fivetran will automatically add or remove columns whenever there is a schema change.

Fig 2 - Pre-built connectors available in Fivetran

Fivetran currently offers more than 200 connectors to SaaS applications, on-premises and cloud-hosted databases, file systems, and what have you (see Figure 2). In particular, various cloud platforms, including the ‘big 3’ of AWS, Azure and Google Cloud, as well as Snowflake, Redshift, and Databricks, are supported, as is multi-cloud. In addition, the product is well-equipped to work with (and migrate data to) cloud data warehouses. Its robust partner network also provides access to adjacent data management capabilities, including data governance, data cataloguing, data masking, and so on. That said, there are no ODBC/JDBC connectors, and if you want to connect to an unsupported data source, you will need to develop a custom cloud function or write a script that will dump data into a file storage service for Fivetran to pick up.

Instead of traditional transformation building, Fivetran provides a wide selection (presently approaching 50) of pre-built data models that have been custom-built for various different contexts and supply all the necessary transforms, joins and so on to support those contexts. Further transformation capability, if needed, can be accessed through integration with dbt Labs by embedding its SQL-based transformation engine into the Fivetran environment. This also provides version control, logging, alerting, and various other features, perhaps most notably including data quality functionality that can be built into your data integration pipelines.

Additional capabilities include support for stream processing, automatic data updates, and automated schema migrations and management. On the latter point, Fivetran will also standardise your schemas for easy querying and API access (for example, by applying deduplication processes). These revamped schemas are fully documented by the product. It also features integrated scheduling for your data integration jobs, and can also run said jobs automatically whenever data is loaded into your system.

What’s more, security and governance are clearly priorities for Fivetran. Accordingly, the product is certified against industry best practices and other regulations, including GDPR, SOC2, ISO27001, PCI and HIPAA; it encrypts all data, both in transit and at rest, and data moved into the Fivetran environment is deleted as soon as its data integration workflow is verified (or after 24 hours, whichever comes sooner); and it provides role-based access and authorisation, including automated user provisioning, integration with Azure AD (Active Directory), and various other features.

There are a number of reasons to care about Fivetran as a solution for data integration: it is highly automated and easy to use; it is scalable up to hundreds of thousands of tables; it is very reliable, due to both idempotency (effectively guaranteed results) and an extremely high delivery uptime (purportedly 99.9%); and its optimisation of compute usage and attractive pricing options help to minimise the cost of living in the cloud. It provides a high level of governance and security, and for governance in particular, integration with relevant partners is enabled through API access and metadata sharing. It also readily integrates into your DataOps ecosystem via a wide range of integrations, and data pipeline orchestration is available to further facilitate this.

Fivetran is strong in regards to the range of application environments it supports, but for databases it is largely limited to varieties of DynamoDB, MariaDB, MongoDB, MySQL, Oracle, PostgreSQL, and SQL Server. That said, this will still capture a large portion of the market. SAP, DB2, and Sybase are also supported, but only by HVR, limiting functionality to data replication. We are told that support for these databases will come to the rest of the platform within the next six months.

In regards to data models and prebuilt connectors in general, Fivetran’s are robust, numerous, and effective. It is more difficult than we would like to build your own connectors and transformations if your uses cases don’t mesh neatly with the product’s prebuilt assets, but if they do (which, again, will likely refer to a large portion of the market) you benefit from excellent time to value, because everything is already there for you, at a high quality and degree of automation.

The Bottom Line             

If your use cases fit into Fivetran’s framework of connectors and data models, you will most likely find it to be a very reliable, easy-to-use, automated and overall high-value solution for data integration. Moreover, owing to the variety of models and connectors made available by the product, it is more than probable that you will fall into this category. As such, we highly recommend checking it out.