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

Community

Other: HVR: Allow ORDER BY when refreshing table

Answered
Dylan Janszen User

We have a scenario where our SQL Server source table has a RowID Identity primary key. The table has an additional column for CustomerId as well as a DateAssigned date field. We would like the ability to perform an in-order table refresh by specifying a channel action for a given table that would send the events in order during a refresh. Right now, a refresh with no slicing does a generic SELECT COLUMNS FROM TABLE. The lack of an ORDER BY clause makes it impossible for us to guarantee in-order processing when refreshing a table to a kafka topic with 1 partition. 

Please sign in to leave a comment.

Comments

4 comments

Date Votes
  • Mark Van de Wiel User

    Hi Dylan,

    Thank you for your suggestion. I think it is a good idea.

    Would I be correct assuming that you may not want to order by the primary key in all cases?

    Please note that a select with order by may use a lot more resources on the source database (depending on the column(s) selected to order by, and the table definition). It may also take long for the query to produce its first row - and hence duration for the data refresh will increase - because all data must be sorted.

    Thanks again,
    Mark.

  • Mark Van de Wiel User

    Hi Dylan,

    Thank you for your suggestion.

    Am I correct assuming you want to order by CustomerId and DateAssigned, rather than your primary key RowID? I.e. you want to order by arbitrary columns?

    Thank you,
    Mark.

  • Mark Van de Wiel User

    Dylan,

    Can you please comment whether you always want to order by primary key columns? Or not necessarily?

    Thank you,
    Mark.

  • Dylan Janszen User

    Hey Mark,

     

    I apologize for the delayed response; I was OOO since September 19th.

     

    Your initial comment (and concerns) are correct. In this particular case, the primary key is a surrogate key and does not properly represent the in-order processing required for downstream applications. We would like a flag/option to be able to adjust this for Fivetran REFRESH operations specifically. Depending on the size of the table and available indexing, this could absolutely lead to performance degradation, but I imagine anybody utilizing this proposed feature would be aware of this. This particular situation isn't common, but it is fairly large deal as it represents a problem that we can't easily fix ourselves.

     

    From the Fivetran side, I imagine the implementation wouldn't be too difficult depending on how the query is built. If it's using some sort of stringbuilder process, it could simply be appended to the end of query assuming it is SQL Server. Of course, I am making a big assumption with the aforementioned statement.