Better Together: Setting up Proactive Fivetran Monitoring With Sigma Templates
We recently announced Sigma’s new Fivetran Usage Template built upon Fivetran’s Quickstart data models. The template gives insight into Fivetran cost and performance, helping you ensure that your Fivetran Connectors are operating efficiently without having to dig through millions of rows of data. In this post, we’ll walk through getting them set up.
Computing Cost for Fivetran Connectors
Fivetran’s cost model is based on a metric called monthly active rows (MAR). Each Fivetran Connector extracts data from a source and loads it into a destination, generating MAR. As rows of data are written to tables (called syncing), Fivetran tracks the number of primary keys that are synced each month. Fivetran then charges you once a month for each row updated. Each primary key (unique row) counts only as a single active row regardless of how many times it is updated in a given month.
These connectors may encounter anomalies when moving data from source to destination. Sometimes, these issues cause connectors to generate more MAR than necessary, or not provide end users with the up-to-date data they need to make decisions. This is why it is necessary to have an accurate and detailed breakdown of both connector cost and performance.
The Fivetran Log Connector
Fivetran provides each customer with its cost and performance data via the Log Connector, but that data needs to be transformed and visualized to deliver value. Traditionally, data teams had to build ELT procedures and BI reports from scratch just to gain a basic understanding of what aggregate Fivetran cost and performance looked like. Determining which tables generated the most MAR or what caused connectors to fail was hard to answer.
Fivetran Quickstart Data Models and Sigma Templates help you proactively manage this process. The Fivetran Log Quickstart transforms the Log Connector data using a dbt model, with no maintenance or intervention required by the user. Then, Sigma's Fivetran Usage Template provides a prebuilt analysis of the modeled data. The Fivetran Quickstart and Sigma Template are deployed in minutes with no code required, shortening a process that used to take days or even weeks.
Setting Up the Templates
Sigma's Fivetran Usage Template gives teams a headstart on tracking Fivetran cost and performance metrics. Fivetran provides usage data with identical structure to every customer, and this is the data that powers the Sigma Template. Setting up the Fivetran Log Connector and Quickstart is a prerequisite for the Sigma Template. First, configure the Fivetran Log Connector to load data into the CDW and then deploy the Fivetran Quickstart for the Log Connector. After a few minutes of setup, the data needed for the template will be arriving in the CDW on a regular cadence. Then, in Sigma, the Fivetran Usage Template can be launched and repointed from sample data to this new dbt-modeled Fivetran Log data. For more information on how to launch the template, check out the blog on Sigma's Snowflake Usage Templates.
Using the Template
The Fivetran Usage template has three pages, the first of which is an overview of spend and connectors. The KPI tiles at the top of the page provide a summary of the current month's spend, which raises the question of how that compares to previous months. Based on the Monthly Spend chart below, it's clear that costs spiked in November and January.
The Detailed Cost Breakdown pivot table provides more insight into the cause for these price surges. Without this template and the data powering it, determining a root cause for cost spikes would be difficult. Using the pivot table, it's clear that the Hubspot connector is the culprit, particularly the contact_property_history and contact_list_member tables. This high cost is associated with high MAR, which means there were a lot of updates on these tables. The reason for this is not always clear; it requires a more detailed exploration that can be done on the second page of the template.
On the Detailed MAR Analysis page, the first chart highlights days and connectors that generated an unusually high number of active rows. This is done by comparing daily active rows for a connector to the previous seven-day average of active rows that month for that connector. Immediately, the dates January 15th and 16th jump out as outliers for the two tables in question.
Moving down to the log events for these tables in the dates specified, there are a lot of record_modified events where many rows were replaced or inserted. The hyperlink in the connector column redirects to Fivetran's documentation page for that connector, which details sync strategies and potential reasons for a high amount of active rows. Based on the documentation for the contact_property_history table, it seems like a feature called "history mode" may be the culprit.
Sigma's conditional alerting capabilities ensure that performance issues or high spend are caught before they happen. On the Usage Summary tab, the six KPIs can be great areas to build alerting, like when spend is at 80% of target or the percentage of healthy connectors drops below 100%. To set this up, go to Scheduled Exports, enter the basic info, then scroll down to the "Send" section and select "If a condition is met.” Using the current month spend-to-target as an example, select that single value visualization and set the value to Spend vs Target. If it's greater than .8 (80%), Sigma will send an alert to the specified email/slack.
This Fivetran Usage Template and the Quickstart data model it runs on top of are powerful, and without them, visibility into Fivetran cost and performance is quite limited. The Sigma Template is a robust out-of-the-box solution, but Templates are meant to be a starting point. Sign up for a free trial and start customizing your Fivetran Usage Template today.
To learn more, please visit our booth at this year's Modern Data Stack Conference for a live demo and learn more about Sigma.
Request a Sigma Demo here.
Not a Fivetran customer? They offer a 14 day free trial. Sign up for it here.
