Is there some way to infer the correct type for each field when uploading a CSV?
For a CSV, everything will come in as a string data type. You’ll need to apply field mappings/transformations at the time of ingestion. You should solve your issue using the above CAST when you do the transformation. You can read more about field mappings.
But if you’re uploading a JSON or other files, there should be no need to specify the type when you upload because Rockset is dynamically typed.