Tables, Views & External Catalogs

Managed vs external tables

-- Create a managed table (default)
CREATE TABLE prod_sales.curated.daily_revenue (
  sale_date DATE,
  region STRING,
  revenue DECIMAL(12,2)
)
COMMENT 'Daily revenue aggregated by region';

-- Create an external table pointing to existing data
CREATE TABLE prod_sales.raw.partner_transactions
  LOCATION 'abfss://raw-data@adlsaccount.dfs.core.windows.net/partner/transactions'
  COMMENT 'Partner transaction files — data owned by partner team';

Exam decision tree: If you own the data and it’s part of your lakehouse → managed table. If the data is shared, pre-existing, or owned by another team/system → external table.

Views and materialized views

Standard views

A view is a saved SQL query. It doesn’t store data — it runs the query each time you access it:

CREATE VIEW prod_sales.reports.top_regions AS
SELECT region, SUM(revenue) AS total_revenue
FROM prod_sales.curated.daily_revenue
GROUP BY region
ORDER BY total_revenue DESC
LIMIT 10;

Materialized views

A materialized view stores the query result physically and refreshes it periodically:

CREATE MATERIALIZED VIEW prod_sales.reports.monthly_summary AS
SELECT
  DATE_TRUNC('month', sale_date) AS month,
  region,
  SUM(revenue) AS monthly_revenue
FROM prod_sales.curated.daily_revenue
GROUP BY 1, 2;

Mei Lin uses materialized views at Freshmart for the store manager dashboard — it queries pre-aggregated monthly sales instead of scanning millions of raw transactions each time.

DDL operations on tables

The exam tests your knowledge of DDL (Data Definition Language) for both managed and external tables:

Common DDL operations

-- Add a column
ALTER TABLE prod_sales.curated.daily_revenue
  ADD COLUMN discount_pct DECIMAL(5,2);

-- Rename a column
ALTER TABLE prod_sales.curated.daily_revenue
  RENAME COLUMN discount_pct TO discount_percentage;

-- Drop a column
ALTER TABLE prod_sales.curated.daily_revenue
  DROP COLUMN discount_percentage;

-- Change column type (only widening allowed, e.g., INT to BIGINT)
ALTER TABLE prod_sales.curated.daily_revenue
  ALTER COLUMN revenue TYPE DECIMAL(15,2);

-- Add table comment
COMMENT ON TABLE prod_sales.curated.daily_revenue
  IS 'Daily revenue by region — refreshed nightly at 3 AM';

-- Drop a managed table (DELETES DATA)
DROP TABLE prod_sales.curated.daily_revenue;

-- Drop an external table (data files remain)
DROP TABLE prod_sales.raw.partner_transactions;

Key exam fact: Schema evolution in Delta Lake supports adding columns and widening types. Narrowing types (BIGINT to INT) or renaming columns requires enabling column mapping.

ALTER TABLE my_table SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name');

Foreign catalogs

A foreign catalog connects Unity Catalog to external database systems, making their tables visible in the three-level namespace:

-- Step 1: Create a connection to an external system
CREATE CONNECTION sqlserver_erp
  TYPE SQLSERVER
  OPTIONS (
    host 'erp-server.database.windows.net',
    port '1433',
    user SECRET('kv-scope', 'erp-user'),
    password SECRET('kv-scope', 'erp-password')
  );

-- Step 2: Create a foreign catalog using that connection
CREATE FOREIGN CATALOG erp_data
  USING CONNECTION sqlserver_erp;

Once created, Ravi can query the ERP system’s tables as if they were local:

-- Query a foreign catalog table like any other UC table
SELECT * FROM erp_data.dbo.customers WHERE region = 'APAC';

Supported connection types include SQL Server, MySQL, PostgreSQL, Snowflake, BigQuery, and others.

Exam scenario: “Ravi needs to join DataPulse’s lakehouse data with customer records in an on-premises SQL Server.” → Create a connection + foreign catalog.

AI/BI Genie

AI/BI Genie is a natural-language interface for data exploration. Business users type questions in plain English and Genie generates SQL queries against your lakehouse tables.

As a data engineer, your job is to configure Genie instructions — metadata that helps Genie understand your data:

-- Add descriptions that help Genie
COMMENT ON TABLE prod_sales.curated.daily_revenue
  IS 'Daily revenue aggregated by region. Updated nightly at 3 AM NZST. Use for revenue reporting queries.';

COMMENT ON COLUMN prod_sales.curated.daily_revenue.region
  IS 'Business region: APAC, EMEA, Americas';

Knowledge check

Next up: Securing Unity Catalog: Who Gets What — privileges, table and column-level access control, and row-level security.