what is the best practice to add one or more columns and one or more rows based on a cell contents?

Can you give example(s) of what you mean?

the excel files contain a column named owners with a string formatted like a list. The string it needs to be split to produce more rows based on its content

If you're thinking about the equivalent of

unnest

in sql, you probably need to handle that in your transformation layer. Meltano can help to bring in the raw data (with some light column-level transforms), but if the tap doesn't do that kind of row level manipulation, it's best left to the 'T' layer eg dbt.

+1 To Andy's suggestion to make it the transformation's layer job if you can. Otherwise, you could take a look at https://docs.meltano.com/guide/mappers/.

Thank you Edgar. it will not work with mappers because I’ll need to create a custom python function to parse and output the transformed field. Can I create custom python functions as dbt macros?

I haven't personally used them, but yeah you should be able to use https://docs.getdbt.com/docs/build/python-models.

It seems dbt python models supports only Snowflake, Databricks and Bigquery. There is dbt-fal that supports python models but was discontinued in april this year and works only on older versions of dbt. Somebody made dbt-postgres-python which is based on dbt-fal and works on dbt 1.7.17 I still need to figure out how to plumb everything together…