Unity Catalog Tables, Views, and Materialized Views

Pick the object before you write the DDL

Unity Catalog gives you four ways to put a result in front of your users, and they differ on one question: who stores the rows, and when does the query run? Choosing wrongly is rarely a syntax error. It shows up weeks later as a stale dashboard, a table duplicated once per audience, or storage nobody remembers to clean up.

Work down that table as three questions in order: does the object own data, does it recompute on read, and does its answer change per user.

Persist data with a managed table

A managed table persists data for long-term storage and is the default table type in Unity Catalog. Azure Databricks manages both its metadata and its underlying data files, which is why managed tables also receive integrated storage optimization and lifecycle management.

Create one with CREATE TABLE and column definitions. Managed tables use Delta Lake by default, so they arrive with ACID transactions, time travel, and schema enforcement already in place.

CREATE TABLE production.sales.sales_transactions (
  transaction_id BIGINT,
  customer_id INT,
  product_name STRING,
  amount DECIMAL(10,2),
  transaction_date DATE
);

Because Azure Databricks owns the storage lifecycle, dropping a managed table removes both its metadata and its data files. That is deliberate: it stops managed data from being orphaned in storage after the object disappears from the catalog.

Unity Catalog names objects in three levels, so a table is addressed as catalog.schema.table. You can spell that out on every statement, or establish the current catalog and schema first with USE CATALOG and USE SCHEMA and then use the short table name.

USE CATALOG production;
USE SCHEMA sales;

CREATE TABLE sales_transactions (
  transaction_id BIGINT,
  customer_id INT,
  amount DECIMAL(10,2)
);

Both forms produce the same object. The context statements supply the first two levels of the name; they do not remove the requirement that all three levels exist. There is no two-level shortcut that skips the schema, and no USE TABLE that would set the context for you.

Document rules with constraints and generated columns

Reach for constraints and generated columns when the table definition itself should carry the rules, instead of leaving them in whatever pipeline happens to write the data. A generated column computes its value from other columns, and CHECK and NOT NULL constraints enforce data-quality rules at write time.

CREATE TABLE customer_orders (
  order_id BIGINT NOT NULL,
  customer_email STRING NOT NULL,
  order_total DECIMAL(10,2),
  tax_amount DECIMAL(10,2) GENERATED ALWAYS AS (order_total * 0.08),
  CONSTRAINT valid_total CHECK (order_total > 0)
);

Here the tax never has to be supplied by the loader, and an order with a total of zero never lands.

Primary-key and foreign-key constraints behave differently. They document relationships but are informational: they do not enforce referential integrity at write time. Their value is descriptive and analytical, since they record the data model and let the query optimizer make better join decisions.

Constraints can be declared inside CREATE TABLE or added later with ALTER TABLE. Either way, the enforced and informational halves of that table keep their separate meanings.

Create a standard view

A standard view stores a query rather than table data. Create it with CREATE VIEW followed by a SELECT query, and the view then behaves as a virtual table that any consumer can select from.

CREATE VIEW customer_order_summary AS
SELECT
  c.customer_id,
  c.customer_name,
  c.region,
  COUNT(o.order_id) AS total_orders,
  SUM(o.order_total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.region;

The query runs whenever the view is accessed, so changes in the underlying tables appear automatically in its results. Nobody has to remember to refresh anything.

That on-demand behavior is what makes a standard view suitable for simplifying joins, encapsulating business logic, or presenting a consistent interface when current data matters. It is also the source of its one real weakness: complex joins and aggregations can make a standard view slow, because they are recomputed for each access.

Views can also be layered on top of other views to break complex logic into readable pieces, but too many layers make troubleshooting harder and hide where the time is going.

Vary what each user sees with a dynamic view

A dynamic view adds row filters or column masks that depend on the querying user. It is still a view, so it still runs its defining query on access, but a conditional expression decides what that user is allowed to see.

Column masking uses a CASE expression together with is_account_group_member():

CREATE VIEW sales_redacted AS
SELECT
  user_id,
  CASE WHEN is_account_group_member('auditors') THEN email
       ELSE 'REDACTED'
  END AS email,
  country_region,
  product,
  total
FROM sales_raw;

One object now answers two audiences differently, and neither of them needs its own copy of the data.

Row-level filtering follows the same pattern, moving the condition into the WHERE clause:

CREATE VIEW sales_filtered AS
SELECT user_id, country_region, product, total
FROM sales_raw
WHERE CASE
        WHEN is_account_group_member('managers') THEN TRUE
        ELSE total <= 1000000
      END;

The payoff is organizational rather than technical: a dynamic view can centralize fine-grained access logic without duplicating a table for each audience. The alternative is a separate table or view per access level, with every one of them to maintain, refresh, and audit separately.

Cache expensive results with a materialized view

A materialized view precomputes and caches query results as a Delta table. Create it with CREATE MATERIALIZED VIEW followed by its defining query, and it then returns cached results instead of recalculating the full query on every access.

CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
  transaction_date,
  COUNT(*) AS transaction_count,
  SUM(amount) AS total_sales,
  AVG(amount) AS average_sale
FROM sales_transactions
GROUP BY transaction_date;

The object is created as a view, but unlike a standard view it has stored output behind it. That is what makes it fast, and also what makes its result a snapshot of the last refresh rather than of this instant.

A materialized view is therefore a strong fit for frequently accessed, expensive aggregations when eventual consistency is acceptable. A standard view is the better fit when the result must reflect source changes immediately.

The deciding question is not which object is faster. It is how much staleness the business question can absorb.

Keep the cache current

A cached result is only useful while it is close enough to the source. Refresh a materialized view manually, on a schedule, or when upstream data changes, so the cached result incorporates those source changes. A schedule can be declared with the view itself:

CREATE MATERIALIZED VIEW daily_sales_summary
SCHEDULE EVERY 1 DAY
AS
SELECT transaction_date, COUNT(*) AS transaction_count, SUM(amount) AS total_sales
FROM sales_transactions
GROUP BY transaction_date;

Match the interval to the question being asked. A daily sales summary read each morning does not need an hourly rebuild, and an hourly operational metric is not served by a nightly one.

Materialized views support incremental and full refresh strategies. Incremental refresh processes changed data instead of reprocessing the complete result, which is dramatically cheaper on a large source. It carries a prerequisite: its source tables must be Delta tables with row tracking enabled, because that is how the changed rows are identified.

ALTER TABLE sales_transactions
SET TBLPROPERTIES (delta.enableRowTracking = true);

Without row tracking, the refresh has no way to tell which rows moved and falls back to reprocessing everything. Informational primary keys do not substitute for it, and neither does a mask or a filter on the source: those govern visibility, not change detection.

Key takeaways

• Use a managed table to persist data while Azure Databricks manages metadata and files. Create it with CREATE TABLE, address it as catalog.schema.table or through USE CATALOG and USE SCHEMA, and remember that dropping it removes both metadata and files.
• Delta Lake is the default format for managed tables, which is what brings ACID transactions, time travel, and schema enforcement.
• CHECK and NOT NULL enforce data-quality rules and a generated column derives its value from other columns, while primary and foreign keys are informational and document relationships without enforcing them.
• Use a standard view for an on-demand query result that reflects current source data, and accept that complex joins and aggregations are recomputed on each access.
• Use a dynamic view to apply user-dependent row filters or column masks, centralizing access logic instead of duplicating a table per audience.
• Use a materialized view to cache expensive query results and refresh them manually, on a schedule, or on upstream change. Enable row tracking on Delta source tables so incremental refresh can process only what changed.