A managed table lets Unity Catalog own the storage location and the file lifecycle. An external table means you own a path and UC only records that a table lives there. Everything in dwh_dev, dwh_test and dwh_prod is managed, with two named exceptions.
Why
Teams reach for external tables as a hedge: if we ever leave Databricks, at least we own the files. The hedge is worth little — Delta is open and readable elsewhere either way — and the price is paid daily, because predictive optimization does not run on external tables at all. Insurance against an exit that probably never happens, bought with permanently manual maintenance on the tables that need it most.
How
sqlThe difference is one clause
-- managed: no LOCATION. The default for every table in the warehouse.
CREATE TABLE gold.dim_product (
product_sk STRING,
product_id STRING
)
CLUSTER BY (product_id);
-- external: you own the path, and everything that follows from that.
-- The webshop landing zone is the ONE place this warehouse does it — an
-- external process drops JSON here and Autoloader reads it from the Volume.
-- CLOUD-SPECIFIC: the URL below is Azure ADLS Gen2. On AWS it is
-- s3://landing/shop_order/ ; on GCP, gs://landing/shop_order/.
CREATE EXTERNAL VOLUME bronze.landing_shop
LOCATION 'abfss://landing@example.dfs.core.windows.net/shop_order/'
COMMENT 'Webshop JSON drop. Written by the shop, never by Databricks.';
-- bronze.shop_order itself is still a MANAGED table. The path is external;
-- the table is not. Those are two decisions and only the first is forced.
What the choice actually decides
Managed
External
Predictive optimization
yes
no — never
DROP TABLE
deletes the data; UNDROP within 7 days
metadata only; files remain
Storage location
UC chooses; you never see a path
your path, your lifecycle, your problem
Justified when
always, unless the row below applies
another system writes the files, or a non-Databricks engine reads them in place
The two exceptions are the webshop landing zone — an external Volume the shop writes JSON into, which Autoloader then reads into a managed bronze.shop_order — and any dataset a non-Databricks engine must read in place. Neither is a gold table, and the first one is an external location, not an external table: the files are outside, the table is not.
Doing it
Make managed the default in the DDL templatesExternal then requires someone to type a LOCATION and defend it in review.
Limit who may create external locations and storage credentialsThat permission is the real control, not the CREATE TABLE statement.
Where external is genuinely needed, confine it to bronze landing zonesIf an external Delta table is unavoidable, write the OPTIMIZE and VACUUM job yourself — predictive optimization will never run for it.
Record each exception with a reason and an expiry date, the way the layer contract handles exceptions
Embedding it
Ask the team to list every external table and name what would break if it were managedMost lists shrink to one or two entries during the conversation.
Have them run DROP TABLE and UNDROP TABLE on a throwaway managed table in devThe seven-day window is then something they have used rather than read about.
Let the platform owner, not the consultant, hold the external-location permission from week oneIt is the cleanest piece of ownership to transfer early.
Defaults
Managed for everything from silver upward, without exception
External locations and Volumes only for landing zones another system writes, and only in bronzeNo external Delta table from silver upward.
External-location creation restricted to a named admin group
Every external table carries a written reason and an expiry date
Gotchas
DROP TABLE on a managed table deletes the dataUNDROP TABLE recovers it within seven days; after that it is a cloud-storage restore, if anyone set one up. Teams who learned Delta on external tables generalise the wrong behaviour and find out on a production table.
External tables are invisible to predictive optimizationNothing errors — they simply stop being compacted while their managed neighbours keep improving, so the gap presents as a query regression rather than a maintenance hole.
Registering two tables over the same external pathUC will let you, the schemas drift, and two teams argue about which one is 'the real table' while both are writing to it.
Choosing external for portability without pricing the maintenanceThe exit you are insuring against is hypothetical; the OPTIMIZE and VACUUM jobs you now own are weekly.
Three levers, in descending order of money saved per unit of effort: pick serverless, constrain what can be created, and make idle compute stop itself.
Workload to compute
Workload
Compute
Why
Lakeflow pipelines, scheduled jobs
serverless
no idle time between runs; nothing to size
SQL, BI, metric views over gold.mv_sales
serverless SQL warehouse
starts in seconds, so a short auto-stop is free
Ad-hoc notebook work
serverless notebook
the alternative is a cluster somebody forgets
Native libraries serverless cannot take
job compute under a policy
the exception — it should need a sentence of justification
Why
The recurring cost of a warehouse this size is not storage — for a four-country distributor's order lines that is a rounding error. It is compute, much of it running while nobody uses it. The canonical incident: someone creates an all-purpose cluster for a five-minute investigation, disables auto-termination because the notebook kept detaching, and goes home on a Friday. Nothing is broken, no alert fires, and it runs until Monday. The fix is a policy attribute, not a reminder.
How
yamlThe compute policy definition — fixed, not recommended
# Shown as YAML because that is how it lives in the bundle. The policy
# API takes the same document as a JSON string in `definition`; the
# bundle serialises it for you. 'fixed' means a user cannot override it.
autotermination_minutes:
type: fixed
value: 30
data_security_mode:
type: fixed
value: SINGLE_USER
# CLOUD-SPECIFIC: Azure VM SKUs. On AWS this allowlist holds EC2 instance
# types, on GCP machine types — the values do not port, the attribute does.
node_type_id:
type: allowlist
values: [Standard_D4ads_v6, Standard_D8ads_v6]
custom_tags.domain:
type: fixed
value: sales
custom_tags.environment:
type: fixed
value: prod
SQL warehouse auto-stop, Stand 29.07.2026: serverless defaults to 10 minutes (minimum 5 in the UI, 1 via the API), pro and classic to 45, minimum 10. Defaults move — read them off the client's workspace rather than off this page. The gap exists because a classic warehouse takes minutes to start and a serverless one takes seconds, which is also why 'we use classic to save money' usually costs more.
Doing it
Default every new job and pipeline to serverlessClassic compute is the exception that carries a written reason.
Ship one policy per persona and remove unrestricted cluster-creation permission entirelyA policy that can be bypassed is documentation.
Set autotermination_minutes as fixed, not as a default a user can raise when it annoys them
Leave serverless warehouse auto-stop at 10 minutes, and size by concurrencyAdd clusters before increasing t-shirt size.
Embedding it
Give the team the cost query before you give them the policyA number they pulled themselves argues better than a policy you wrote.
Ask them to find the workspace's most expensive idle compute in system.billing.usage and present itThe policy proposal then comes from them.
Keep policy changes in the bundle and review them as pull requestsA timeout raised by a click in the admin console has no author and no reason.
Defaults
Serverless is the default; classic compute needs a justification in the PR
Cluster creation only through a policyNo unrestricted creation for anyone, including admins doing 'just this once'.
autotermination_minutes fixed at 30 for interactive compute
Serverless SQL warehouse auto-stop at 10 minutes; scale out before scaling up
Gotchas
An all-purpose cluster with auto-termination switched off because a notebook kept detachingIt runs all weekend, no alert fires, and the cost appears a month later in a bill nobody reads line by line. Fix it with a fixed policy attribute; a reminder in a wiki has never once worked.
A classic SQL warehouse on a 45-minute auto-stop serving a dashboard that refreshes every 30 minutesIt is woken before it can ever time out, so it bills around the clock while the usage graph shows a handful of queries a day.
Sizing a warehouse up because one query is slowT-shirt size buys parallelism for a single query; concurrent users need more clusters. Getting the two backwards doubles the bill and leaves the query slow.
Policies apply to new clusters onlyExisting ones keep whatever they were created with, so the policy looks effective while the expensive machines predate it.
A node_type_id allowlist copied from a page written for a different cloudStandard_D4ads_v6 exists only on Azure and an m7gd instance type only on AWS; the policy itself saves and validates, and the failure surfaces later as every cluster creation under it being rejected for an invalid node type — read as a broken policy rather than a wrong list.