/dbt_fivetran_log

Data models for Fivetran's internal log connector built using dbt.

Apache License 2.0Apache-2.0

Fivetran Log (docs)

This package models Fivetran Log data from our free internal connector. It uses account-level data in the format described by this ERD.

This package helps you understand:

  • How you are spending money in Fivetran according to our consumption-based pricing model. We display consumption data at the table, connector, destination, and account levels.
  • How your data is flowing in Fivetran:
    • Connector health and sync statuses
    • Transformation run statuses
    • Daily API calls, schema changes, and records modified
    • Table-level details of each connector sync

The package's main goals are to:

  • Create a history of measured monthly active rows (MAR), credit consumption, and the relationship between the two
  • Enhance the connector table with sync metrics and relevant alert messages
  • Enhance the transformation table with run metrics
  • Create a history of vital daily events for each connector
  • Create an audit log of records inserted, deleted, an updated in each table during connector syncs

Note: An earlier version of this package unioned destination-level connector data to the account level. As of December 2020, the Fivetran Log now supports the creation of account-level connectors. We have removed the Fivetran Log dbt package's unioning functionality and recommend that users resync their Log connectors at the account level.

Models

model description
fivetran_log__connector_status Each record represents a connector loading data into a destination, enriched with data about the connector's data sync status.
fivetran_log__transformation_status Each record represents a transformation, enriched with data about the transformation's last sync and any tables whose new data triggers the transformation to run.
fivetran_log__mar_table_history Each record represents a table's active volume for a month, complete with data about its connector and destination.
fivetran_log__credit_mar_destination_history Each record represents a destination's consumption by showing its MAR, total credits used, and credits per millions MAR.
fivetran_log__connector_daily_events Each record represents a daily measurement of the API calls, schema changes, and record modifications made by a connector, starting from the date on which the connector was set up.
fivetran_log__schema_changelog Each record represents a schema change (altering/creating tables, creating schemas, and changing schema configurations) made to a connector and contains detailed information about the schema change event.
fivetran_log__audit_table Replaces the deprecated fivetran_audit table. Each record represents a table being written to during a connector sync. Contains timestamps related to the connector and table-level sync progress and the sum of records inserted/replaced, updated, and deleted in the table.

Installation Instructions

Check dbt Hub for the latest installation instructions, or read the dbt docs for more information on installing packages.

Configuration

By default, this package will run using your target database and the fivetran_log schema. If this is not where your Fivetran Log data is, add the following configuration to your dbt_project.yml file:

# dbt_project.yml

...
config-version: 2

vars:
  fivetran_log:
    fivetran_log_database: your_database_name
    fivetran_log_schema: your_schema_name

Disabling Transformation Models

If you have never created Fivetran-orchestrated basic SQL transformations, your source data will not contain the transformation and trigger_table tables. Moreover, if you have only created scheduled basic transformations that are not triggered by table syncs, your source data will not contain the trigger_table table (though it will contain transformation).

To disable the corresponding functionality in the package, you must add the following variable(s) to your dbt_project.yml file. By default, all variables are assumed to be true:

# dbt_project.yml

...
config-version: 2

vars:
  fivetran_log:
    fivetran_log_using_transformations: false # this will disable all transformation + trigger_table logic
    fivetran_log_using_triggers: false # this will disable only trigger_table logic

Changing the Build Schema

By default this package will build the Fivetran Log staging models within a schema titled (<target_schema> + _stg_fivetran_log) and the Fivetran Log final models within your <target_schema> + _fivetran_log in your target database. If this is not where you would like you Fivetran Log staging and final models to be written to, add the following configuration to your dbt_project.yml file:

# dbt_project.yml

...
models:
  fivetran_log:
    +schema: my_new_final_models_schema # leave blank for just the target_schema
    staging:
      +schema: my_new_staging_models_schema # leave blank for just the target_schema

Read more about using custom schemas in dbt here.

Contributions

Don't see a model or specific metric you would have liked to be included? Notice any bugs when installing and running the package? If so, we highly encourage and welcome contributions to this package! Please create issues or open PRs against master. See the Discourse post for information on how to contribute to a package.

Database Support

This package has been tested on BigQuery, Snowflake and Redshift.

Resources: