Hi, We are doing a POC to setup our data pipeline using Meltano. Most of our source data comes as csv extract and we use Postgres as the DB. I've setup meltano with tap-csv and target-postgres. I've found that the tap-csv outputs every field as a string. Is it possible to load certain csv columns as non-string types (integer, date) while loading into postgres ? And if yes, what is the best mechanism to achieve that.

Do you know what types should be ahead of time or are you looking for something dynamic? If you know things ahead of time then you could consider writing a DBT transformation that moves the raw data into properly typed columns. I don't know if there's anything that could do that dynamically though

We do know in advance what the data types would be. So is this limitation only for tap-csv ? For instance, if we were to pull data from any other source, could we alter the data types before loading into the target.

@ishan_b I had the same question - we are copying data from CSV to Snowflake and every column in Snow end up being varchar data type.

currently meltano's

tap-csv

hard-codes all columns to strings, but I'm sure they'd accept a MR to allow the column types to be overridden by configuration options https://gitlab.com/meltano/tap-csv/blob/master/tap_csv/__init__.py#L28

@ishan_b This is

tap-csv

limitation; taps for other sources will generally detect/know the correct types. You can override the schema that is set in the tap's catalog file (if it supports one) using https://meltano.com/docs/plugins.html#schema-extra, but it's still up to the tap to take that into account and pass it on to the target, which tap-csv doesn't. As @julian_knight suggested, your best bet right now would be to modify the tap to support type hints and automatic casting of values according to those.

Thanks @douwe_maan . Will work on the tap to support some type hints.