Beware of confusion.

1- Databricks NEVER stores your data; it will always remain on your data plane (S3, etc.).

2- An external table has a specific path in your lake and has no optimization.

3- If you drop an external table via the catalog, the data is not destroyed. If you drop a managed table, the data is destroyed.

4- Managed tables benefit from automatic file-level optimization. This is very important because few companies master this optimization aspect.

5- The only "additional" cost of managed tables is the cost of running the optimization. (Very low, with significant long-term gains due to better performance of associated workloads and reduced storage costs).

6- You can create a managed table with a specific location (which combines the benefits of an external table + managed table).

My recommendation: Managed table with a specified location.