Databricks: Expanded Object Attribute Coverage

The Databricks driver now covers a much wider set of attributes across schemas, tables, views, materialized views, functions, procedures, and permissions.

The Databricks driver received a broad upgrade: every object it supports now accepts a wider set of attributes in HCL, and Atlas inspects, diffs, and plans them in both the declarative and the versioned workflow.

The examples below show some of the attributes each object accepts and the SQL Atlas plans for them. They are not exhaustive; the complete list is in the Databricks schema reference.

Schemas

A schema carries a comment and a default collation for the objects inside it:

schema "retail_analytics" {
  comment   = "curated retail data for the analytics team"
  collation = "UTF8_BINARY"
}

CREATE SCHEMA `retail_analytics` COMMENT 'curated retail data for the analytics team' DEFAULT COLLATION UTF8_BINARY;

Functions

A function declares its language, determinism, data access, and per-argument comments. The one below is used as a row filter:

function "filter_by_region" {
  schema          = schema.retail_analytics
  lang            = SQL
  comment         = "restricts rows to the region of the caller"
  deterministic   = false
  sql_data_access = READS_SQL_DATA
  arg "region" {
    type    = STRING
    comment = "the region column of the row"
  }
  return = BOOLEAN
  as     = "region = 'EU' OR is_account_group_member('admins')"
}

CREATE OR REPLACE FUNCTION `retail_analytics`.`filter_by_region` (`region` STRING COMMENT 'the region column of the row')
RETURNS BOOLEAN
COMMENT 'restricts rows to the region of the caller'
NOT DETERMINISTIC
READS SQL DATA
RETURN region = 'EU' OR is_account_group_member('admins');

Tables

A table declares its provider and properties, partitioning, check constraints, and per-column identity, collation, default, and generated expressions:

table "orders" {
  schema   = schema.retail_analytics
  comment  = "one row per placed order"
  provider = "delta"
  properties = {
    "delta.enableDeletionVectors" = "true"
    "delta.enableChangeDataFeed"  = "true"
  }
  column "order_id" {
    null    = false
    type    = BIGINT
    comment = "surrogate key"
    identity {
      by_default = true
      start      = 1000
      increment  = 1
    }
  }
  column "customer_email" {
    null      = false
    type      = STRING
    collation = "UTF8_LCASE"
    comment   = "compared case-insensitively"
  }
  column "region" {
    null    = false
    type    = STRING
    default = "EU"
  }
  column "total_amount" {
    null    = false
    type    = DECIMAL(12,2)
    comment = "order total in EUR"
  }
  column "total_with_vat" {
    null = true
    type = DECIMAL(12,2)
    as   = "CAST(total_amount * 1.21 AS DECIMAL(12,2))"
  }
  column "placed_on" {
    null = false
    type = DATE
  }
  primary_key "pk_orders" {
    columns = [column.order_id]
  }
  check "region_is_known" {
    expr = "region IN ('EU', 'US', 'APAC')"
  }
  partition {
    columns = [column.placed_on]
  }
}

CREATE TABLE IF NOT EXISTS `retail_analytics`.`orders` (
  `order_id` BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1000 INCREMENT BY 1) COMMENT 'surrogate key',
  `customer_email` STRING COLLATE UTF8_LCASE NOT NULL COMMENT 'compared case-insensitively',
  `region` STRING NOT NULL DEFAULT 'EU',
  `total_amount` DECIMAL(12,2) NOT NULL COMMENT 'order total in EUR',
  `total_with_vat` DECIMAL(12,2) GENERATED ALWAYS AS (CAST(total_amount * 1.21 AS DECIMAL(12,2))),
  `placed_on` DATE NOT NULL,
  CONSTRAINT `pk_orders` PRIMARY KEY (`order_id`)
)
PARTITIONED BY (`placed_on`)
COMMENT 'one row per placed order'
TBLPROPERTIES ('delta.enableChangeDataFeed' = 'true', 'delta.enableDeletionVectors' = 'true', 'delta.feature.allowColumnDefaults' = 'supported');
ALTER TABLE `retail_analytics`.`orders` ADD CONSTRAINT `region_is_known` CHECK (region IN ('EU', 'US', 'APAC'));

Views

A view declares schema binding, a default collation, properties, and column comments:

view "recent_orders" {
  schema         = schema.retail_analytics
  comment        = "orders placed in the last 30 days"
  collation      = "UTF8_BINARY"
  schema_binding = BINDING
  properties = {
    "owner" = "analytics-team"
  }
  column "order_id" {
    null    = false
    type    = BIGINT
    comment = "carried over from the orders table"
  }
  column "region" {
    null = false
    type = STRING
  }
  column "total_amount" {
    null = false
    type = DECIMAL(12,2)
  }
  as         = "SELECT order_id, region, total_amount FROM retail_analytics.orders WHERE placed_on > date_sub(current_date(), 30)"
  depends_on = [table.orders]
}

CREATE OR REPLACE VIEW `retail_analytics`.`recent_orders` (
  `order_id` COMMENT 'carried over from the orders table',
  `region`,
  `total_amount` COMMENT 'order total in EUR'
)
WITH SCHEMA BINDING
COMMENT 'orders placed in the last 30 days'
DEFAULT COLLATION UTF8_BINARY
TBLPROPERTIES ('owner' = 'analytics-team') AS SELECT order_id, region, total_amount FROM retail_analytics.orders WHERE placed_on > date_sub(current_date(), 30);

Materialized Views

A materialized view declares expectations with their violation action, clustering, a refresh schedule, and a row filter backed by a function:

materialized "revenue_by_region" {
  schema   = schema.retail_analytics
  comment  = "daily revenue rolled up per region"
  provider = "delta"
  properties = {
    "pipelines.autoOptimize.managed" = "true"
  }
  column "region" {
    null = false
    type = STRING
  }
  column "revenue_day" {
    null = false
    type = DATE
  }
  column "revenue" {
    null = true
    type = DECIMAL(18,2)
  }
  primary_key "pk_revenue_by_region" {
    columns = [column.region, column.revenue_day]
  }
  expect "revenue_is_not_negative" {
    expr         = "revenue >= 0"
    on_violation = FAIL_UPDATE
  }
  expect "region_is_known" {
    expr         = "region IN ('EU', 'US', 'APAC')"
    on_violation = DROP_ROW
  }
  cluster {
    columns = [column.region]
  }
  refresh {
    cron      = "0 0 2 * * ?"
    time_zone = "Europe/Amsterdam"
  }
  row_filter {
    function = function.filter_by_region
    columns  = [column.region]
  }
  as         = "SELECT region, placed_on AS revenue_day, CAST(SUM(total_amount) AS DECIMAL(18,2)) AS revenue FROM retail_analytics.orders GROUP BY region, placed_on"
  depends_on = [table.orders]
}

CREATE OR REPLACE MATERIALIZED VIEW `retail_analytics`.`revenue_by_region` (
  `region` STRING NOT NULL,
  `revenue_day` DATE NOT NULL,
  `revenue` DECIMAL(18,2),
  CONSTRAINT `revenue_is_not_negative` EXPECT (revenue >= 0) ON VIOLATION FAIL UPDATE,
  CONSTRAINT `region_is_known` EXPECT (region IN ('EU', 'US', 'APAC')) ON VIOLATION DROP ROW,
  CONSTRAINT `pk_revenue_by_region` PRIMARY KEY (`region`, `revenue_day`)
)
CLUSTER BY (`region`)
COMMENT 'daily revenue rolled up per region'
TBLPROPERTIES ('pipelines.autoOptimize.managed' = 'true')
SCHEDULE REFRESH CRON '0 0 2 * * ?' AT TIME ZONE 'Europe/Amsterdam'
WITH ROW FILTER `retail_analytics`.`filter_by_region` ON (`region`) AS SELECT region, placed_on AS revenue_day, CAST(SUM(total_amount) AS DECIMAL(18,2)) AS revenue FROM retail_analytics.orders GROUP BY region, placed_on;

Procedures

A procedure declares security and data access clauses, and each argument takes a mode, a default, and a comment:

procedure "archive_old_orders" {
  schema          = schema.retail_analytics
  lang            = SQL
  comment         = "moves orders older than the cutoff into cold storage"
  security        = INVOKER
  sql_data_access = MODIFIES_SQL_DATA
  arg "cutoff" {
    type    = DATE
    mode    = IN
    comment = "orders placed before this date are archived"
  }
  arg "archived_count" {
    type    = BIGINT
    mode    = OUT
    comment = "how many rows were archived"
  }
  arg "dry_run" {
    type    = BOOLEAN
    mode    = IN
    default = true
  }
  as = "BEGIN SET archived_count = (SELECT count(*) FROM retail_analytics.orders WHERE placed_on < cutoff); END"
}

CREATE OR REPLACE PROCEDURE `retail_analytics`.`archive_old_orders` (`cutoff` DATE COMMENT 'orders placed before this date are archived', OUT `archived_count` BIGINT COMMENT 'how many rows were archived', `dry_run` BOOLEAN DEFAULT true)
LANGUAGE SQL
SQL SECURITY INVOKER
COMMENT 'moves orders older than the cutoff into cold storage'
MODIFIES SQL DATA
AS BEGIN SET archived_count = (SELECT count(*) FROM retail_analytics.orders WHERE placed_on < cutoff); END;

Permissions

A permission block grants privileges on an object to a principal:

permission {
  to         = "b21bc072-9110-4af2-81af-21a670c49233"
  for        = table.orders
  privileges = [SELECT, INSERT, UPDATE, DELETE, MODIFY, READ_METADATA]
}

GRANT DELETE, INSERT, MODIFY, READ METADATA, SELECT, UPDATE ON TABLE `retail_analytics`.`orders` TO `b21bc072-9110-4af2-81af-21a670c49233`;

Project Configuration

Permissions are planned only when the permissions schema mode is enabled. A project configuration for the schema above looks like this:

env "local" {
  url = "databricks://...?catalog=your_catalog"
  dev = "databricks://...?catalog=your_dev_catalog"

  migration {
    dir     = "file://migrations"
    tx_mode = "none"
  }
  schema {
    src = "file://schema.hcl"
    mode {
      permissions = true
    }
  }
}

For the full list of blocks and attributes the driver supports, see the Databricks schema reference.