Hi Gim, please reach out to yours or the client's Databricks account team. They should be able to assist more. I would also look into Unity Catalog as it will make your data management much easier.
Having your bronze, silver, and gold containers in separate containers is fine. They can be in the same container if you wanted to as well. The differences are not that big. But overall multiple containers by zone is good.
(1) I would not recommend using a MySQL Database for your DW. You are already storing all your data in delta and ADLS. You should use Databricks SQL as your SQL compute. If you use a MySQL then you will replicate data and add unneeded complexity. You are able to do ETL, Data warehousing, BI, Streaming, and ML in Databricks.
(2) When working with Databricks you should store ALL your business data in your ADLS storage account just like you are doing. However, you can also create databases in Databricks using a location which will allow you to register the tables in the hive metastore while writing the data to ADLS. However, if you use Unity Catalog (linked above) this will automatically be handled for you!
Here is how to create a database with location and save a dataframe as a table:
%python
# create database
spark.sql("CREATE DATABASE IF NOT EXISTS my_db LOCATION ''abfss://mycontainer@mystorage.dfs.core.windows.net/path/to/db ")
# set default database
spark.sql("use my_db")
# read data into df
df = spark.read.json("/path/to/file")
# write as delta table -- note that it will be saved to the default location of the database
df.write.saveAsTable("my_table")
If you already wrote a table to delta in ADLS but want to register it in the hive metastore:
# writing data to a location
df.write.format("delta").save("/path/to/table")
# register the table in the database
spark.sql("""
CREATE TABLE my_table
AS
SELECT *
FROM delta.`/path/to/table`
""")
# read data with sql
spark.sql("SELECT * FROM my_table")
