Connector Improvement: Provide a way to override the target column naming behavior to avoid duplicate column warnings
Answered
We periodically add new columns to our SFDC objects (which we replicate to Snowflake via Fivetran). On occasion, we encounter duplicate column warnings which say that the column didn't get replicated because another column already exists with that name.
For example:
"TotalAmount_c" when parsed by Fivetran to determine the target column name will result in "total_amount_c" but this column already exists.
It would be nice if instead of a warning and not replicating the column, that Fivetran either creates a warning but generates a new column name such as "total_amount_c_2" or refuses to replicate the object any further until the customer either fixes the naming issue at source (not usually possible) or is provided with a dialog that allows the customer to explicitly provide what the column should be named (and then in the Fivetran metadata tables - create a new table to log where this renaming has occurred). Once the new name is provided, replication will resume.
-
Official comment
Hi Tim,
We are working on a renaming feature to be used in those cases. Schema page should already tell you what are the incompatible names, be it on fivetran_naming or source_naming.
Best,
-
Hi Tim,
Thank you for sharing this need and suggestion and I have added your request to our feature improvements backlog for review. We are actively developing some features in this area specifically the ability to use source names in the destination. It would be great to hear more about how often you encounter these duplicate names, and what use cases cause it? We're thinking that being able to use source naming will reduce the frequency of encountering problems significantly.
We will keep the community updated on this thread with any progress related to this enhancement.
Thanks,
Alison -
Hi Allison,
We see the duplicate column name warning perhaps once or twice per quarter (maybe a bit less). This is almost entirely related to our SFDC usage where managed packages come and go and 3rd party consultants have been working with the business to change certain aspects of how we use SFDC. There may be a column in a sfdc table called 'User_Count_c' which may have existed for many years but is not really used anymore. Then someone adds some functionality to our sfdc instance which is similar and they create a new field in sfdc called 'UserCount_c" thinking the name is different enough. But of course Fivetran sees this as a colillion. and will omitt the new column.
-
Hi Tim,
Thank you for the detailed explanation, really helpful. I'm hopeful our new Source naming feature will help you. Its coming soon to Salesforce.
Best Alison
-
hi Tim,
The new naming option is now available for Salesforce, allowing you to retain the same names used in the source. You can find more details in our documentation: https://fivetran.com/docs/core-concepts/naming-conventions#namingconventions
Additionally, our Platform Connector includes tables with lineage information that map the source schema to the resulting destination schema.
Best regards,
-
Egidio,
This is not really a solution. We have many pipelines already built upon the Fivetran SFDC connector that uses the Fivetran naming conventions. What I need is something that detects the duplicate rationalized column name and then renames it when it detects a duplicate.
What I would like to see is that Fivetran not disable replicating the column with a duplicate name but instead generate an alternate name and send a warning that it had to take this strategy. Basically don't drop columns just because your naming rule result in a collision.
Hope that makes sense.
Please sign in to leave a comment.
Comments
6 comments