Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

index column missing in table conversation_channel_history

Answered
Abhijeet Purwar User

Connector name: Kustomer

Table name: conversation_channel_history

Column to add: index

API documentation link:

Additional details: In earlier features when we used to get only current record tables and not the _history tables (maintaining all historical changes), we used to have index column.

 

For example Big Query destination connector for Kustomer do not have index column

SELECT *,
conversation_id AS ticket_id
FROM `tide-ms-prj-prod-bqd-uk`.`kustomer`.`conversation_channel_history`
WHERE _fivetran_active = True
and conversation_id = '698b477bfe5c37b5bdf3dc84'

 

but Snowflake destination connector for Kustomer have index column

select top 10 *
from member_support_prod.kustomer.conversation_channel
where conversation_id='698b477bfe5c37b5bdf3dc84';

Both tables have duplicate records for id given in a query but since snowflake has index column I am able to fetch the record where index=1

More details can be found here : https://support.fivetran.com/hc/en-us/requests/365869?page=1

Please sign in to leave a comment.

Comments

2 comments

Date Votes
  • Official comment
    Vignesh Rengaraj Fivetranner

    Hi Abhijeet,

    Thank you for raising this.

    The difference you’re seeing is due to a change in how newer Kustomer connections are modeled. New connections now use history mode by default, which captures all changes over time and provides a more complete view of the data. Older connections (like your Snowflake one) still use the previous “live table” model.

    Because of this:

    • The conversation_channel table (with the index column) exists only in older connections
    • Newer connections use conversation_channel_history, and the index column is not available there, as it was not a field returned by the source and was specific to the older table design

    We are in the process of migrating older connections to history mode so that behavior is consistent across all destinations. If you’d like, we can help enable this for your existing connection as well.

    Regarding your use case of querying for records with index=1 :

    • If your goal is to get the current/latest record per conversation, you can use:
      WHERE _fivetran_active = TRUE

       
    • If you need the earliest version of a record, you can use the row with the minimum _fivetran_start

    This approach replaces the earlier use of index = 1 and gives you more flexibility depending on whether you want current or historical views.
    Let me know if you still have any questions or concerns. If you’d like to enable history mode for your Snowflake connection as well, we’d be happy to include you in an earlier rollout.

    Best,
    Vignesh 
    Fivetran Product Team 

  • Abhijeet Purwar User

    Hi Vignesh,

    The issue is _fivetran_active = TRUE gives latest record. But for this condition there are certain id for which we are getting 2 records and for both of them _fivetran_start is having exactly same value.

    select*
    from `tide-ms-prj-prod-bqd-uk.kustomer.conversation_channel_history`
    where _fivetran_active = true
    and conversation_id = '68b04911962b3accd4732d6e'
    ;
     
    select *
    from member_support_prod.kustomer.conversation_channel
    where conversation_id = '68b04911962b3accd4732d6e'
    ;
    How index column is coming in snowflake's case, I want to achieve same result.
     
     
     
    andconversation_id = '68b04911962b3accd4732d6e'
    ;