Create and Organize Objects in Unity Catalog

A production table in Unity Catalog has a name like prod.sales.orders, and that same three-part name identifies it from SQL, Python, or any BI tool attached to the workspace. Every governed object follows this three-level namespace, catalog.schema.object, which replaces the legacy two-level hive_metastore.schema.table layout. The metastore is the top-level container for a region; a catalog is the first level you create inside it; a schema (also called a database) is the second level; and the object itself, a table, view, volume, function, or model, is the third (Unity Catalog object model^[1]).

The catalog is not just an organizational folder. It is the primary unit of data isolation in Unity Catalog, and schemas add a finer layer of organization beneath it (Unity Catalog best practices^[2]). Because isolation lives at the catalog level, the common convention is one catalog per environment, for example dev, test, and prod. Separating environments only by schema inside a single shared catalog is the misread to avoid: it organizes the data but leaves every environment inside the same isolation boundary, so a grant on the catalog reaches all of them. Put each environment in its own catalog instead. Whoever runs the CREATE statement becomes the object's first owner, so Databricks recommends reassigning production catalogs and schemas to a group rather than leaving an individual as owner.

When data and its processing environment share the same isolation requirement, you can go further and bind a catalog to specific workspaces. Workspace-catalog binding restricts a catalog so only the workspaces you assign can reach it; access from an unbound workspace is denied even for a user who holds an explicit SELECT grant on a table inside it (Workspace-catalog binding^[3]). That is how you keep a prod catalog reachable only from production workspaces. The figure below shows the full hierarchy from the metastore down to the object level.

Two CREATE statements stand up the containers, and each carries a privilege gate and a storage decision. Start at the top: CREATE CATALOG makes a top-level container inside the metastore, and only a metastore admin or a principal granted the CREATE CATALOG privilege on the metastore can run it (Create catalogs^[4]). A frequent slip is expecting CREATE CATALOG to give you a place to put tables directly; it does not. A catalog holds schemas, so a table needs a schema first, created with the two-level name CREATE SCHEMA catalog.schema (Create schemas^[5]). A schema cannot be a top-level object.

The storage decision is where a Hive-metastore habit trips people up. In Unity Catalog you set a schema's managed storage with the MANAGED LOCATION clause, not LOCATION. LOCATION is the legacy Hive-metastore syntax; on a Unity Catalog schema it is rejected, and MANAGED LOCATION is the supported form (Create schemas^[5]). Setting a MANAGED LOCATION requires the CREATE MANAGED STORAGE privilege on the external location (the Unity Catalog object that registers a cloud storage path) that covers the path. Omit the clause and the object inherits managed storage from the level above: managed storage resolves at the lowest level that sets it, the schema first, then the catalog, then the metastore (Managed storage best practices^[2]).

Create a catalog, then a schema inside it

This pair creates a prod catalog and a sales schema, each with its own managed storage; the bracketed keyword is optional and other clauses are omitted:

CREATE CATALOG IF NOT EXISTS prod
  MANAGED LOCATION 'abfss://managed@company.dfs.core.windows.net/prod';
-- ...
CREATE SCHEMA IF NOT EXISTS prod.sales
  MANAGED LOCATION 'abfss://managed@company.dfs.core.windows.net/prod/sales';

Each MANAGED LOCATION path must sit under a Unity Catalog external location you hold CREATE MANAGED STORAGE on. Drop the clause and that object inherits managed storage from its parent instead.

One distinction decides who owns your data files and what a DROP destroys, and it applies the same way to tables and to volumes: managed versus external. In a managed object, Unity Catalog owns both the governance and the underlying files, which live in managed storage. In an external object, Unity Catalog governs access to the metadata, but the files stay in a cloud location you manage (Managed vs external assets^[2]).

For tables, a managed table is the default and recommended type. It stores rows in the Delta Lake format unless you request USING iceberg, and you can create one empty, with CREATE TABLE AS SELECT (CTAS), or with CREATE OR REPLACE TABLE (Managed tables^[6]). Dropping a managed table deletes both the metadata and the data files; Unity Catalog keeps them recoverable with UNDROP for a default of seven days, then removes them from cloud storage. An external table is created with a LOCATION clause pointing at a path under a Unity Catalog external location, and DROP TABLE removes only the metadata and leaves the files untouched (External tables^[7]). That difference is the exam's favorite table trap: if the files must survive the drop, the answer is an external table.

Volumes extend the same model to non-tabular data. A volume is a Unity Catalog object under a schema that governs files of any format, such as images, CSVs, and model artifacts, reached through the path /Volumes/catalog/schema/volume (Volumes^[8]). A managed volume stores its files in the schema's managed storage; an external volume, created with CREATE EXTERNAL VOLUME ... LOCATION, registers an existing path under an external location (Create volumes^[9]). The drop rule mirrors tables exactly: dropping a managed volume marks its files for deletion, while dropping an external volume leaves them in place. The figure groups the four objects by the only question that matters at drop time.

A view and a materialized view read almost the same in SQL, but one stores data and one does not, and that single fact drives every question about them. A standard view is a read-only object that saves the text of a SELECT query; creating it writes no data, and only the query text is registered to the schema (Views^[10]). Each time you read the view, Unity Catalog re-runs its query against the base tables, so the results are always current, and the view can restrict or reshape the columns it exposes without copying anything.

A materialized view precomputes and stores its results in an underlying managed table, then keeps them up to date as the source tables change (Views^[10]). When you create one, Unity Catalog automatically provisions a serverless pipeline to build and refresh it, billed as serverless Lakeflow Spark Declarative Pipelines usage; the refresh runs incrementally, merging only changed rows when the query allows, and falls back to a full recompute when it cannot (Standalone materialized views^[11]). You refresh it on a schedule or on update. The trade the exam probes: a materialized view spends storage and a scheduled refresh to make repeated reads fast, while a standard view spends nothing and recomputes on every read. So a materialized view stores data and must be refreshed; a standard view does neither.

Precompute a daily aggregate as a materialized view

This materialized view stores a per-day sales total so a dashboard reads it instantly; the refresh schedule and other options are omitted:

CREATE OR REPLACE MATERIALIZED VIEW prod.sales.daily_totals AS
SELECT date, sum(amount) AS total
FROM prod.sales.orders
GROUP BY date;
-- ...

Because daily_totals is materialized, the sum is computed once per refresh and stored, not recomputed on every dashboard load.

Suppose an operational PostgreSQL database holds live order status, and an analyst needs to join it against lakehouse tables for a quick report without waiting for an ingestion pipeline to copy it in. Lakehouse Federation is the Azure Databricks feature for exactly this: governed, read-only access to an external system that you query in place (Lakehouse Federation^[12]).

It takes two objects, created in order. First a connection, a Unity Catalog securable that stores the source's host, JDBC URL, and credentials. Then a foreign catalog, created from that connection, which mirrors the external database's schemas and tables as Unity Catalog objects so they appear alongside your other catalogs (Lakehouse Federation^[12]). Once you grant privileges on the foreign catalog, queries against it are pushed down to the source and run in place; no data is copied into Unity Catalog managed storage. PostgreSQL is one of many supported sources here, alongside MySQL, SQL Server, Oracle, Snowflake, Amazon Redshift, Azure Synapse, Google BigQuery, and other Databricks workspaces.

The contrast to hold onto: only a foreign catalog queries the source in place. An ingestion pipeline (Lakeflow Connect, Auto Loader, or COPY INTO) or a managed table built from the source would copy the data into Databricks storage instead. When higher data volumes and lower latency matter more than avoiding a copy, Databricks actually recommends ingesting with Lakeflow Connect over federating. The figure traces the connection, the foreign catalog, and the in-place query.

Business users often need an answer, not a query editor. An AI/BI Genie Agent (formerly called a Genie space) is a domain-specific, natural-language interface where a user asks a question in plain English and Genie returns the generated SQL, a results table, and a visualization (Genie^[13]). It runs over a curated set of Unity Catalog tables that a data analyst registers to the agent, which turns ad hoc data discovery into self-service without anyone writing SQL.

Accuracy comes from curation, not magic. The analyst who builds the agent supplies general instructions in plain language, example SQL queries, SQL expressions that encode business semantics, and verified answers to trusted questions; together these steer how Genie interprets domain terms and business logic (Tune Genie Agent quality^[14]). A vague metric like active customer becomes reliable only once an instruction or example pins down what it means.

One boundary is worth stating plainly, because it is a natural misread. Genie answers from the curated tables it was given plus those instructions, and access is still governed by the querying user's Unity Catalog permissions; it does not roam the whole metastore looking for data (Genie Agents concepts^[15]). Widening what Genie can answer means adding tables and instructions to the agent, not granting it blanket access.

Most questions on creating and organizing Unity Catalog objects reduce to picking the right object or clause under a stated constraint. Read the constraint in the stem, then match it.

• Isolate production data from development: create a separate catalog per environment, and bind it to the production workspaces if the environment itself must be restricted. The catalog is the isolation unit; separating environments by schema inside one catalog does not isolate them (best practices^[2]).
• Set a schema's managed storage: use MANAGED LOCATION. LOCATION is Hive-metastore syntax and is rejected on a Unity Catalog schema (create schemas^[5]).
• The files must survive a drop: choose an external table or external volume, defined with LOCATION. A managed DROP deletes the underlying files (managed tables^[6]).
• Speed up an expensive, repeated query: a materialized view precomputes and stores results and refreshes them; a standard view recomputes on every read (materialized views^[11]).
• Query an external database without copying it: create a connection, then a foreign catalog, and query in place. An ingestion pipeline or managed table would copy the data (Lakehouse Federation^[12]).
• Govern images, CSVs, or model artifacts: register a volume and reach files at /Volumes/catalog/schema/volume (volumes^[8]).
• CREATE CATALOG versus CREATE SCHEMA: CREATE CATALOG builds a top-level container; a table needs a schema created as CREATE SCHEMA catalog.schema (create catalogs^[4]).
• Self-service natural-language questions for business users: an AI/BI Genie Agent over a curated set of Unity Catalog tables, steered by instructions and examples (Genie^[13]).