Do I need to keep the `profiles.yml` file for custom transformation?

No, you can remove the profiles.yml file from the dbt project used by custom transformation. Normalization will inject this file to run the operation during the sync.