Unity Catalog: Managing Data with SQL Commands

The Object Creation Hierarchy

Just as navigation follows the catalog -> schema -> table path, creation does as well. You'll typically create a catalog first, then a schema within it, and finally tables inside the schema.

1. Creating and Managing Catalogs

A catalog is the top-level container for organizing your data assets. It's common to create catalogs to separate data by environment (e.g., dev, staging, prod) or by business unit (e.g., finance, marketing).

The primary command is CREATE CATALOG. It's a best practice to include the IF NOT EXISTS clause to prevent your code from failing if the catalog has already been created.

As you saw, the basic syntax is:

CREATE CATALOG IF NOT EXISTS my_new_catalog
COMMENT 'A catalog for a specific project.';

Once created, you can verify its existence and set it as your current context. The following video demonstrates this workflow.

2. Creating and Managing Schemas

Schemas (or databases) are the second level of the namespace, residing within a catalog. They are used to group related tables, views, and functions.

To create a schema, you must specify which catalog it belongs to, using the catalog_name.schema_name dot notation.

The syntax is:

CREATE SCHEMA IF NOT EXISTS my_new_catalog.my_new_schema
COMMENT 'A schema for raw data.';

You can then use SHOW SCHEMAS to list schemas in the current or a specified catalog, and DESCRIBE SCHEMA to get more details.

3. Creating and Managing Tables

Tables are the fundamental objects that store your data. In Unity Catalog, there are two main types: managed and external. For this course and for most use cases, we will focus on managed tables, which are the default and recommended type. With managed tables, Unity Catalog fully manages the underlying data files' lifecycle, storage location, and optimizations. This simplifies data management and governance significantly.

To create a managed table, you use the standard CREATE TABLE syntax, but you must use the three-level namespace to specify its location.

Let's see this in a hands-on demonstration.

4. Dropping Objects: RESTRICT vs. CASCADE

A critical part of managing objects is knowing how to remove them. The commands are simple: DROP CATALOG, DROP SCHEMA, and DROP TABLE. However, there's a crucial detail that often appears in certification exams: the behavior of RESTRICT (the default) versus CASCADE.

• RESTRICT: Prevents you from dropping an object if it still contains other objects. For example, you cannot DROP CATALOG my_catalog if it still contains schemas. You must drop the child objects first.
• CASCADE: Deletes the specified object and all objects contained within it. For example, DROP CATALOG my_catalog CASCADE will delete the catalog, all schemas inside it, and all tables within those schemas. This is powerful but must be used with caution.

For managed tables specifically, Unity Catalog offers a safety net. When you drop a managed table, the underlying data isn't immediately deleted. You have a 7-day window to recover it using the UNDROP TABLE command. This is another key benefit of using managed tables.

Hands-On Practice Scenario

Now, let's bring all these commands together. In your Databricks SQL Editor or a notebook, execute the following commands step-by-step to simulate a full create-and-cleanup lifecycle. This will solidify your understanding.

Step 1: Create the hierarchy

-- Create a new catalog for our project
CREATE CATALOG IF NOT EXISTS dce_pro_practice
  COMMENT 'Catalog for Data Engineer Professional course exercises.';

-- Set the new catalog as our current context
USE CATALOG dce_pro_practice;

-- Create a schema for bronze-level data
CREATE SCHEMA IF NOT EXISTS bronze_layer
  COMMENT 'Schema for raw, ingested data.';

Step 2: Create and inspect a table

-- Create a simple managed table for customer data
CREATE TABLE IF NOT EXISTS bronze_layer.customers (
  customer_id INT,
  first_name STRING,
  last_name STRING,
  email STRING,
  ingested_at TIMESTAMP
)
COMMENT 'Raw customer data from source systems.';

-- Verify that the table exists in the schema
SHOW TABLES IN bronze_layer;

-- Inspect the table's structure and metadata
DESCRIBE TABLE EXTENDED bronze_layer.customers;

Step 3: Clean up using DROP
First, let's try dropping the catalog without CASCADE. This command will fail because the catalog is not empty.

-- This will fail because the 'bronze_layer' schema and 'customers' table exist
DROP CATALOG dce_pro_practice;

You'll receive an error message indicating that you must use CASCADE.

Now, perform the cleanup correctly. You can either drop from the inside out or use CASCADE.

-- Option A: Drop from inside-out (manual)
-- DROP TABLE dce_pro_practice.bronze_layer.customers;
-- DROP SCHEMA dce_pro_practice.bronze_layer;
-- DROP CATALOG dce_pro_practice;

-- Option B: Use CASCADE for a top-down deletion (more common in cleanup scripts)
DROP CATALOG dce_pro_practice CASCADE;

-- Verify the catalog is gone
SHOW CATALOGS;

Running through this scenario provides the practical, hands-on experience you prefer and directly reinforces today's learning objectives.

Conclusion

In this lesson, you've moved from a navigator to an architect within Unity Catalog. You now have the fundamental SQL commands to build and dismantle the core structures that will house all your data assets.

Key Takeaways:

• You create objects following the hierarchy: CREATE CATALOG, then CREATE SCHEMA, then CREATE TABLE.
• Using the IF NOT EXISTS clause is a standard best practice to make your scripts idempotent.
• The three-level namespace (catalog.schema.table) is mandatory when creating objects to define their location.
• Managed tables are the Databricks-recommended standard in Unity Catalog, simplifying management and enabling powerful features like automated optimization and UNDROP.
• DROP ... CASCADE is a powerful but destructive command that removes an object and all its children, while the default RESTRICT behavior acts as a safety measure.

Preview of the Next Lesson:

Creating catalogs, schemas, and tables is only half the story. By default, only you (the owner) can access these new objects. To make them useful for your team, you need to grant access. In the next lesson, we will cover how to grant and revoke object permissions to principals using Unity Catalog, which is the heart of data governance.