Hello Team, currently, we use Fivetran and would like to see if Meltano could replace it. It worked really well with Salesforce replication in Snowflake. In Fivetran, the names are roughly converted to snake cases for table and column names. If we decide to use Meltano, I wonder if there is a way to configure target-snowflake to convert the names to snake cases. Or please let me know if there is a better way to handle this. Otherwise, it will be a difficult task to migrate our existing queries. Many thanks. Below are a few examples.

SOURCE NAME	NAME CONVERSION	
My_Name	    my_name	
My Name	    my_name	
MyName	    my_name	
mYName	    m_yname	
MyName123	my_name_123	
My123name	my_123_name	
My_Name!	my_name_	
My_Name____c	my_name_c

It depends on the variant of

target-snowflake

that you use. I don't see a setting for that feature in https://github.com/transferwise/pipelinewise-target-snowflake But I will say this is a good use case for dbt. If you're able to build thin source models on top of your raw data, you can build all of your downstream models off of that source layer and then if you migrate data EL tools you'd only have a single place to update any mappings like that. I did that at GitLab and it worked quite well as we migrated from Stitch to Fivetran to Meltano for different data sources https://gitlab.com/gitlab-data/analytics/-/blob/master/transform/snowflake-dbt/models/sources/sfdc/sfdc_opportunity_source.sql

It could be a valid case for a stream mapper. I'll investigate 😉

Thank you @taylor and @alexander_butler. I think it will be stream mapper will be a better option. DBT methods may pose challenges since we have to continue running them after migration. We have a large number of BI reports based on Snowflake’s data model. Thus, we must keep the existing table columns as they were created by Fivetran. Using fal, it works, but it just adds an unnecessary layer of complexity. It would be great if there were a stream mapper with regex replace functionality which will have a big impact on the Fivetran user who wants to make a switch. @taylor Please correct me if I misunderstood your suggestion.

@yijie_wang if you're using Snowflake then dbt is a good solution for managing transformations in the warehouse, but if you're not currently using it then it would be a bigger lift to solve this immediate problem.

Agreed dbt could be used after loading, but if you want to control the target tables naming, stream maps are a good option. There seems to be a very common use case here, actually, even if it is not robustly handled as of today. If anyone wanted to provide this as a PR on Meltano's mapper plugin, one way this could be made generic would be to accept a config like

stream_name_transform_rules

, which might be a list of regex search/replace patterns... 🤔

Thank you @aaronsteers! I don’t have the technical skill to contribute. However, from the perspective of a Fivetran user. If we decide to make a migration, this functionality will be crucial.

Hey @yijie_wang, @aaronsteers would probably be asking the same thing: What's stopping you from using the meltano-map-transform? Renaming columns seems like a very good use case for this mapper. (And yes, a general renaming-transformer would also make sense, but this one will do the job just fine)

@Sven Balnojan, thanks for the reply. My understanding is that meltano-map-transform can’t rename columns at the moment. Is this not the case?

So in general it can, but you'll have to do it one by one, the issue @aaronsteers created aims at doing it semi-automatically as I understand it, e.g. just telling the stream "convert to snake case using this specific regex". But this should work:

mappings:
    - name: rename_column
      config:
        stream_maps:
          new_table:
            oldEmailColumn: null # drop 
            new_email_column: oldEmailColumn
            ...

Thanks for the example,@Sven Balnojan. In my case, there are tens of thousands of columns that need to be transformed. It is impossible to use this method.

Yeah, so the Meltanoee way would be to use a mapper. Let me take a look into that next week I'll get back to you. And I#m sure @aaronsteers can give you a general update (but I think as far as I could see, nothing has changed to June, not in this regard).

Thank you so much @Sven Balnojan! A feature like this would make transiting to Meltano easier for Fivetran users. It would be great if this feature can accept a function as argument.

Confirmed that we're not actively prioritizing this as of now. This conversation did spark a much more robust spec exploration here for a V2 mapping syntax that - but while we're excited to add a new capability, is not likely to be in our timeline within the near future. (Contributions always welcome.)

Hi @yijie_wang just catching up on your FiveTran conversation. For the compatibility / portability, I like the idea of a configuration in the target. We've done this already for Postgres pipelinewise tap - our motivation was similar, to keep our existing transforms. DM me, maybe we could help with that change on Snowflake target. https://github.com/transferwise/pipelinewise-target-postgres/compare/master...Matatika:pipelinewise-target-postgres:master

Thank you @aaronsteers for the comprehensive proposal. I have upvoted.

Looks like it would be better to avoid the global, but need to look at the code. (On something better than my phone…)