Add location to create database/schema statement in databricks to enable creation of managed tables on external storage accounts

Help
1

The problem I’m having

I want to store my tables as managed tables on an external location like adls or blob.
By default databricks stores managed tables in: dbfs:/user/hive/warehouse, it is possible to change this default location by creating the database / schema and definining the location.

Example SQL statement:
CREATE DATABASE test_db LOCATION ‘abfss://testdata@storage-account.dfs.core.windows.net/test_db’
or:
CREATE SCHEMA test_db LOCATION ‘abfss://testdata@storage-account.dfs.core.windows.net/test_db’

(database / schema are equivalent in databricks)

This way all tables created in this database/schema will be managed tables and the data as well as the metadata will be deleted if a table is dropped. I can’t find a way how to add the location to the create schema statement in dbt.

The context of why I’m trying to do this

What I’ve already tried

Consulted dbt documentation

Question:
How can I add the location to the CREATE DATABASE / SCHEMA statement in dbt in such tables within this database / schema will become managed tables and data is stored on an external storage account?

2

You can add the ‘location_root’ config parameter described in

This will allow you to set the default storage location. You might need to permanently mount the the azure storage container in a /mnt folder first.