[Example] Zendesk Support Airbyte dbt Package

[marcosmarxm]

This tutorial shows how to create and validate the transformation layer to make Airbyte data compatible with Fivetran dbt package.

Resources:

• Airbyte Zendesk Schema
• Fivetran Final Modeling dbt Package
• Fivetran Intermediate dbt Package

an example of how the Zendesk Support dbt package was created to ensure compatibility with the Fivetran Modeling dbt Package. The task is challenging, but it can be easily achieved by following some steps.

Airbyte and Fivetran connectors, in general, read data from the API endpoints. The output data can be different as each provider can apply different methodologies. Here is some examples of what can be different:

• Airbyte names tables in plural (users, tickets) and Fivetran always use in singular (user, ticket).
• Fivetran unnest simple objects for some specific columns and Airbyte keep them as JSON column.

Step 1: Identify what streams

First go to the Intermediate dbt Package and access the models/ folder.
Here you can check the src_<source>.yaml and see all tables or go into each SQL file to check what stream they're using.

You must compare if the streams used here are available in Airbyte (check Airbyte schema or the connectors/source_zendesk_support/models/source/<stream>.yml files.

Important

Fivetran sometimes created tables for nested objects. One example from Amplitude was Airbyte has only events and Fivetran uses event and event_type this means they have a table generated from a object type inside of event table.

Fivetran	Airbyte
brand	brands
daylight_time	❌ it doesn't exist
domain_name	❌ it doesn't exist
group	groups
organization	organizations
organization_tag	❌ it doesn't exist
schedule	schedules
schedule_holiday	❌ it doesn't exist
ticket	tickets
ticket_comment	ticket_comments
ticket_field_history	❌ it doesn't exist, but similar to ticket_audits
ticket_form_history	❌ it doesn't exist
ticket_schedule	❌ it doesn't exist
ticket_tag	❌ it doesn't exist
time_zone	❌ it doesn't exist
user	users
user_tag	user_fields

As you can see there are a couple of tables doesn't exist in Airbyte side. This happens because there are some endpoints only accessible for Zendesk Enterprise customers.

It isn't a problem in most situations as we can disable the models for this case, check here as example.

The only problem here is the ticket_field_history this table is generated by Fivetran from the ticket_audits.
For this case we must generate a transformation to create the table for Airbyte.

In the end the basic Fivetran model uses couple of tables: brand, group, organization, ticket, ticket_comment, ticket_field_history, users and user_fields.
In the future if Airbyte got access to enterprise endpoints or users can contribute to make the model more complete.

After finishing this phase, post your findings in the Issue to get approval for Step 2.

Warning

⚠️ ** ONLY GO TO STEP 2 AFTER GETTING APPROVAL FROM STEP 1**

Step 2: Identify what column each stream is using

Fivetran creates staging models and later create the analytical models based on these intermediate tables.
The critical part of this task is make Airbyte compatible with these intermediate models.
For this step you must go to Fivetran Intermediate dbt Package (find the one for the source you're working) and access the models/ folder.
Inside the models/ folder it will contain mostly SQL files querying tables are generated by the connector.
You must identify if the Airbyte connector has all tables used by the model and for each table it has all columns.

Example 1 when things go all right no changes needed user table

final as ( 
    
    select 
        id as user_id,
        external_id,
        _fivetran_synced,
        cast(last_login_at as {{ dbt.type_timestamp() }}) as last_login_at,
        cast(created_at as {{ dbt.type_timestamp() }}) as created_at,
        cast(updated_at as {{ dbt.type_timestamp() }}) as updated_at,
        email,
        name,
        organization_id,
        phone,
        {% if var('internal_user_criteria', false) -%}
            case 
                when role in ('admin', 'agent') then role
                when {{ var('internal_user_criteria', false) }} then 'agent'
            else role end as role,
        {% else -%}
        role,
        {% endif -%}
        ticket_restriction,
        time_zone,
        locale,
        active as is_active,
        suspended as is_suspended


        {{ fivetran_utils.fill_pass_through_columns('zendesk__user_passthrough_columns') }}


    from fields
)

This are all the columns used by Fivetran for the user. Now let's check if Airbyte has the same ones:

airbyte-dbt-models/connectors/source_zendesk_support/models/source/users.yaml

Lines 10 to 128 in acc1186

	- name: users
	columns:
	- name: verified
	description: Indicates if the user's account is verified.
	data_type: boolean
	- name: role
	description: The role or position of the user.
	data_type: string
	- name: tags
	description: Tags associated with the user.
	data_type: array
	- name: chat_only
	description: Specifies if the user can only participate in chat interactions.
	data_type: boolean
	- name: role_type
	description: The type of role assigned to the user.
	data_type: integer
	- name: phone
	description: The phone number associated with the user.
	data_type: string
	- name: organization_id
	description: The ID of the organization the user belongs to.
	data_type: integer
	- name: details
	description: Additional details or information about the user.
	data_type: string
	- name: email
	description: The email address associated with the user.
	data_type: string
	- name: only_private_comments
	description: Indicates if the user can only create private comments.
	data_type: boolean
	- name: signature
	description: The user's email signature.
	data_type: string
	- name: restricted_agent
	description: Specifies if the user is a restricted agent.
	data_type: boolean
	- name: moderator
	description: Specifies if the user has moderator privileges.
	data_type: boolean
	- name: updated_at
	description: The datetime when the user account was last updated.
	data_type: string
	- name: external_id
	description: An external identifier for the user.
	data_type: string
	- name: time_zone
	description: The time zone of the user.
	data_type: string
	- name: photo
	description: Information about the user's profile photo.
	data_type: object
	- name: name
	description: The name of the user.
	data_type: string
	- name: shared
	description: Indicates if the user's account is shared.
	data_type: boolean
	- name: id
	description: Unique identifier for the user.
	data_type: integer
	- name: created_at
	description: The datetime when the user account was created.
	data_type: string
	- name: suspended
	description: Indicates if the user's account is suspended.
	data_type: boolean
	- name: shared_agent
	description: Specifies if the user is a shared agent.
	data_type: boolean
	- name: shared_phone_number
	description: Specifies if the user has a shared phone number.
	data_type: boolean
	- name: user_fields
	description: Custom fields associated with the user.
	data_type: object
	- name: last_login_at
	description: The datetime of the user's last login.
	data_type: string
	- name: alias
	description: An alternative name or identifier for the user.
	data_type: string
	- name: two_factor_auth_enabled
	description: Specifies if two-factor authentication is enabled for the user.
	data_type: boolean
	- name: notes
	description: Any notes or comments related to the user.
	data_type: string
	- name: default_group_id
	description: The default group ID for the user.
	data_type: integer
	- name: url
	description: The URL of the user's details.
	data_type: string
	- name: active
	description: Indicates if the user is currently active.
	data_type: boolean
	- name: permanently_deleted
	description: Specifies if the user has been permanently deleted.
	data_type: boolean
	- name: locale_id
	description: The locale ID of the user.
	data_type: integer
	- name: custom_role_id
	description: The custom role ID assigned to the user.
	data_type: integer
	- name: ticket_restriction
	description: Specifies the user's ticket restriction.
	data_type: string
	- name: locale
	description: The preferred locale of the user.
	data_type: string
	- name: report_csv
	description: The report in CSV format associated with the user.
	data_type: boolean
	- name: iana_time_zone
	description: The IANA time zone of the user.
	data_type: string

Comparing both of them you can see Airbyte has all the columns and the column have the same name.

---

Example 2: column names are different what to do?

It didn't happen for Zendesk, let's use Asana as example.

User table:

final as (
    
    select 
        id as user_id,
        email,
        name as user_name
    from fields
    where not coalesce(_fivetran_deleted, false)
)

Airbyte names id as gid the definition declared by Asana, Fivetran converts it to id

airbyte-dbt-models/connectors/source_asana/models/source/users.yaml

Lines 10 to 14 in acc1186

	- name: users
	columns:
	- name: gid
	description: ''
	data_type: string

What to do?

• In this case you need create a stg table to represent the user properly. Something:

    select 
        gid as id,
        email,
        name
    from users

From here you can start running dbt run to validate if your model is working as expected.

Important

Submit your pull request for each table you're working on, let's do step by step :)

Step 3: Write instructions

You can copy Zendesk README and edit to your connector.