Databricks Consulting, Lakehouse Engineering & 24×7 Support

Databricks SQL · liquid clustering DDL, reclustering and layout health

-- Unity Catalog managed Delta table with liquid clustering (Databricks Runtime 15.4 LTS and above)
CREATE TABLE IF NOT EXISTS main.sales.fct_orders (
  order_id     BIGINT        NOT NULL,
  customer_id  STRING        NOT NULL,
  order_ts     TIMESTAMP     NOT NULL,
  amount       DECIMAL(18,2),
  status       STRING,
  CONSTRAINT fct_orders_pk PRIMARY KEY (order_id) NOT ENFORCED
)
CLUSTER BY (customer_id, order_ts)
TBLPROPERTIES (
  'delta.enableDeletionVectors'        = 'true',
  'delta.deletedFileRetentionDuration' = 'interval 7 days',
  'delta.logRetentionDuration'         = 'interval 30 days'
);

-- Existing table: change keys in place, or let predictive optimization choose them
ALTER TABLE main.sales.fct_orders CLUSTER BY (customer_id, order_ts);
-- ALTER TABLE main.sales.fct_orders CLUSTER BY AUTO;

-- Recluster all data once after enabling or changing keys; later OPTIMIZE runs are incremental
OPTIMIZE main.sales.fct_orders FULL;

-- Layout health: file count, size and clustering columns, then the recent write history
DESCRIBE DETAIL main.sales.fct_orders;
DESCRIBE HISTORY main.sales.fct_orders LIMIT 20;

Three signals decide what we change. A table with many small files and a long run of small appends needs compaction or predictive optimization. A table whose queries read most of their files even with selective filters needs clustering on those filter columns. A table with frequent MERGE operations and no deletion vectors is rewriting whole files for single-row changes.

In Databricks consulting work we enable predictive optimization on Unity Catalog managed tables so OPTIMIZE, VACUUM and statistics collection run on observed usage, then watch the results in the system tables instead of scheduling maintenance jobs by hand.

Databricks SQL · list-price cost per day, SKU, team and workload

-- DBU consumption and list-price cost per day, SKU, team and workload (last 30 days)
SELECT u.usage_date,
       u.sku_name,
       COALESCE(u.custom_tags['team'], 'UNTAGGED')          AS team,
       COALESCE(u.usage_metadata.warehouse_id,
                u.usage_metadata.job_id,
                u.usage_metadata.cluster_id)                AS workload_id,
       SUM(u.usage_quantity)                                AS dbus,
       SUM(u.usage_quantity * lp.pricing.default)           AS list_cost
FROM system.billing.usage       AS u
JOIN system.billing.list_prices AS lp
  ON  lp.sku_name   = u.sku_name
  AND lp.cloud      = u.cloud
  AND lp.usage_unit = u.usage_unit
  AND u.usage_end_time >= lp.price_start_time
  AND (lp.price_end_time IS NULL OR u.usage_end_time < lp.price_end_time)
WHERE u.usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY ALL
ORDER BY list_cost DESC;

The query returns list-price cost, which is an upper bound; your negotiated rate lowers it. The shape of the spend matters more than the rate: untagged compute, all-purpose SKUs running scheduled jobs and warehouses that never stop are the patterns Databricks consulting finds most often.

Programme-level cloud cost work across Databricks and the rest of the estate runs through our cloud database optimization and FinOps practice.