{"id":774,"date":"2025-08-19T16:02:03","date_gmt":"2025-08-19T16:02:03","guid":{"rendered":"https:\/\/dataopsschool.com\/blog\/?p=774"},"modified":"2025-08-19T16:46:22","modified_gmt":"2025-08-19T16:46:22","slug":"databricks-catalog-schemas-tables-with-external-location","status":"publish","type":"post","link":"https:\/\/dataopsschool.com\/blog\/databricks-catalog-schemas-tables-with-external-location\/","title":{"rendered":"Databricks – Catalog, Schemas & Tables with External Location"},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

this is exactly the core of Unity Catalog\u2019s object model<\/strong>. The way Databricks resolves storage paths for managed tables<\/strong> depends on where<\/em> you attach the external\/managed location. Let\u2019s break it down carefully by Catalog \u2192 Schema \u2192 Table<\/strong> levels.<\/p>\n\n\n\n

\n\n\n\n

\ud83d\udd11 Unity Catalog Storage Hierarchy<\/h1>\n\n\n\n

Unity Catalog object model:
Metastore \u2192 Catalog \u2192 Schema \u2192 Table<\/strong><\/p>\n\n\n\n

    \n
  • You can set managed storage locations<\/strong> (via external locations) at Metastore<\/strong>, Catalog<\/strong>, or Schema<\/strong> level.<\/li>\n\n\n\n
  • Tables may also define a LOCATION<\/strong> clause (explicit path).<\/li>\n\n\n\n
  • Databricks applies a fallback precedence<\/strong>:
    Schema location (if exists) \u2192 Catalog location (if exists) \u2192 Metastore location (if exists).<\/strong><\/li>\n<\/ul>\n\n\n\n
    \n\n\n\n

    1\ufe0f\u20e3 Catalog with External Location<\/h2>\n\n\n\n

    When you create a catalog with an external location<\/strong>:<\/p>\n\n\n\n

    CREATE CATALOG dev_ext\nMANAGED LOCATION 'abfss:\/\/data@<storage_account>.dfs.core.windows.net\/ADB\/catalog\/';\n<\/code><\/pre>\n\n\n\n
      \n
    • Effect:<\/strong>\n
        \n
      • Any managed tables<\/strong> created inside this catalog (and its schemas) will, by default, write to this catalog\u2019s external location.<\/li>\n\n\n\n
      • The actual storage path will be: \/ADB\/catalog\/<catalog-id>\/tables\/<table-id>\/<\/code><\/li>\n\n\n\n
      • If no schema-level location is set, the catalog\u2019s location is used.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
      • Example:<\/strong> CREATE SCHEMA dev_ext.bronze; CREATE TABLE dev_ext.bronze.sales (...) ;<\/code> \u2192 Data files stored under the catalog-level path<\/strong>.<\/li>\n<\/ul>\n\n\n\n
        \n\n\n\n
        2\ufe0f\u20e3 Schema with External Location<\/h2>\n\n\n\n
        When you create a schema with an external location<\/strong>:<\/p>\n\n\n\n
        CREATE SCHEMA dev_ext.bronze_ext\nMANAGED LOCATION 'abfss:\/\/data@<storage_account>.dfs.core.windows.net\/ADB\/schema\/bronze_ext\/';\n<\/code><\/pre>\n\n\n\n
          \n
        • Effect:<\/strong>\n
            \n
          • Any managed tables<\/strong> inside this schema are stored under the schema\u2019s external location.<\/li>\n\n\n\n
          • Schema location overrides the catalog location<\/strong>.<\/li>\n\n\n\n
          • Actual storage path: \/ADB\/schema\/bronze_ext\/<schema-id>\/tables\/<table-id>\/<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n
          • Example:<\/strong> CREATE TABLE dev_ext.bronze_ext.sales (...) ;<\/code> \u2192 Data files stored at the schema-level location<\/strong>.<\/li>\n<\/ul>\n\n\n\n
            \n\n\n\n
            3\ufe0f\u20e3 Table with External Location<\/h2>\n\n\n\n
            When you create a table with a LOCATION clause<\/strong>:<\/p>\n\n\n\n
            CREATE TABLE dev_ext.bronze.sales_external (\n  id INT, product STRING, amount DOUBLE\n)\nLOCATION 'abfss:\/\/data@<storage_account>.dfs.core.windows.net\/ADB\/external-tables\/sales_external';\n<\/code><\/pre>\n\n\n\n
              \n
            • Effect:<\/strong>\n
                \n
              • This is an external table<\/strong>, not a managed one.<\/li>\n\n\n\n
              • The data files live exactly at the path you specify.<\/li>\n\n\n\n
              • Databricks only manages metadata<\/strong> in Unity Catalog; it will never delete the underlying files if you drop the table.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
              • Example:<\/strong>\n
                  \n
                • Drop the table \u2192 metadata gone, files remain<\/strong> at \/ADB\/external-tables\/sales_external\/<\/code>.<\/li>\n\n\n\n
                • Undrop \u2192 metadata is restored (within 7 days).<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
                  \n\n\n\n
                  \ud83d\udd04 Comparison Across Scenarios<\/h1>\n\n\n\n
                  Scenario<\/th>Default Data Storage<\/th>Cleanup Behavior on DROP TABLE<\/code><\/th><\/tr><\/thead>
                  Catalog with external location<\/strong><\/td>Tables stored under catalog\u2019s managed path (\/catalog\/<catalog-id>\/tables\/<table-id>\/<\/code>)<\/td>Dropping table: metadata removed, data files deleted after 7\u201330 days<\/strong> (grace period for undrop).<\/td><\/tr>
                  Schema with external location<\/strong><\/td>Tables stored under schema\u2019s managed path (\/schema\/<schema-id>\/tables\/<table-id>\/<\/code>)<\/td>Same as above: metadata gone immediately, data eventually deleted after retention period.<\/td><\/tr>
                  Table with LOCATION (external table)<\/strong><\/td>Data stored exactly at the specified path (\/external-tables\/<name>\/<\/code>)<\/td>Metadata dropped immediately, data files always remain<\/strong>. UC never deletes them.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
                  \n\n\n\n
                  \u26a1 Key Takeaways<\/h1>\n\n\n\n
                    \n
                  • Metastore-level location<\/strong> = global default (used if neither catalog nor schema has a location).<\/li>\n\n\n\n
                  • Catalog-level location<\/strong> = default for all schemas under it (unless schema overrides).<\/li>\n\n\n\n
                  • Schema-level location<\/strong> = strongest override for managed tables.<\/li>\n\n\n\n
                  • Table with LOCATION<\/strong> = always external \u2192 UC does not manage lifecycle of data files.<\/li>\n<\/ul>\n\n\n\n
                    \n\n\n\n
                    \ud83d\udc49 So, in short:<\/p>\n\n\n\n
                      \n
                    • Catalog external location<\/strong> = whole catalog defaults there.<\/li>\n\n\n\n
                    • Schema external location<\/strong> = just that schema overrides catalog.<\/li>\n\n\n\n
                    • Table external location<\/strong> = explicit \u2192 always external, metadata-only management.<\/li>\n<\/ul>\n\n\n\n
                      \n\n\n\n
                      <\/p>\n","protected":false},"excerpt":{"rendered":"
                      this is exactly the core of Unity Catalog\u2019s object model. The way Databricks resolves storage paths for managed tables depends on where you attach the external\/managed location…. <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-774","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/774","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=774"}],"version-history":[{"count":3,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/774\/revisions"}],"predecessor-version":[{"id":779,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/774\/revisions\/779"}],"wp:attachment":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=774"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=774"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=774"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}