04Storage & cost

CREATE TABLE gold.fct_order_line (
  order_line_sk   STRING         COMMENT 'Surrogate key: sha2 of order_id + line_number',
  order_id        STRING,
  line_number     INT,
  customer_sk     STRING         COMMENT '-> dim_customer (informational only)',
  product_sk      STRING,
  date_sk         DATE,
  region_sk       STRING,
  order_ts        TIMESTAMP,
  quantity        DECIMAL(12,3),
  unit_price      DECIMAL(18,2),
  currency        STRING         COMMENT 'EUR | CHF',
  fx_rate         DECIMAL(18,6),
  amount_eur      DECIMAL(18,2)  COMMENT 'ROUND(quantity * unit_price * fx_rate, 2)',
  status          STRING         COMMENT 'OPEN | SHIPPED | CANCELLED',
  _tech_loaded_at TIMESTAMP
)
CLUSTER BY (customer_sk, order_ts)
COMMENT 'One row per order line. Owner: dwh-sales. Cadence: hourly.';

-- unpartitioned table -> clustered
ALTER TABLE gold.dim_customer CLUSTER BY (customer_id);

-- ALREADY PARTITIONED -> clustered (this is the migration path)
ALTER TABLE gold.fct_order_line
  REPLACE PARTITIONED BY WITH CLUSTER BY (customer_sk, order_ts);

-- hand the key choice to predictive optimization
ALTER TABLE gold.agg_revenue_month CLUSTER BY AUTO;

-- turn it off entirely
ALTER TABLE gold.agg_revenue_month CLUSTER BY NONE;

-- none of the above rewrites existing data. THIS does, once:
OPTIMIZE gold.fct_order_line FULL;

-- catalog level, so schemas and tables created later are covered
ALTER CATALOG dwh_prod ENABLE PREDICTIVE OPTIMIZATION;

-- narrower levers, same grammar: ENABLE | DISABLE | INHERIT
ALTER SCHEMA dwh_prod.gold INHERIT PREDICTIVE OPTIMIZATION;
ALTER TABLE  dwh_prod.gold.fct_order_line INHERIT PREDICTIVE OPTIMIZATION;

-- what it did, and what it cost (usage_unit is ESTIMATED_DBU)
SELECT
  operation_type,                     -- COMPACTION | VACUUM | ANALYZE | CLUSTERING
  COUNT(*)                      AS operations,
  ROUND(SUM(usage_quantity), 2) AS estimated_dbus
FROM system.storage.predictive_optimization_operations_history
WHERE catalog_name = 'dwh_prod'
  AND start_time >= current_date() - INTERVAL 30 DAYS
GROUP BY 1
ORDER BY estimated_dbus DESC;

-- managed: no LOCATION. The default for every table in the warehouse.
CREATE TABLE gold.dim_product (
  product_sk STRING,
  product_id STRING
)
CLUSTER BY (product_id);

-- external: you own the path, and everything that follows from that.
-- The webshop landing zone is the ONE place this warehouse does it — an
-- external process drops JSON here and Autoloader reads it from the Volume.
CREATE EXTERNAL VOLUME bronze.landing_shop
LOCATION 'abfss://landing@example.dfs.core.windows.net/shop_order/'
COMMENT 'Webshop JSON drop. Written by the shop, never by Databricks.';

-- bronze.shop_order itself is still a MANAGED table. The path is external;
-- the table is not. Those are two decisions and only the first is forced.

# Shown as YAML because that is how it lives in the bundle. The policy
# API takes the same document as a JSON string in `definition`; the
# bundle serialises it for you. 'fixed' means a user cannot override it.
autotermination_minutes:
  type: fixed
  value: 30
data_security_mode:
  type: fixed
  value: SINGLE_USER
node_type_id:
  type: allowlist
  values: [Standard_D4ads_v6, Standard_D8ads_v6]
custom_tags.domain:
  type: fixed
  value: sales
custom_tags.environment:
  type: fixed
  value: prod

# Declarative Automation Bundles (formerly Asset Bundles; the CLI
# command and the file format are unchanged).
targets:
  prod:
    variables:
      catalog: dwh_prod
    resources:
      jobs:
        silver_customer:
          job_clusters:
            - job_cluster_key: main
              new_cluster:
                custom_tags:
                  domain: sales
                  environment: prod
                  cost_centre: CC-4711
                  owner: dwh-sales@example.com

SELECT
  u.custom_tags['domain']      AS domain,
  u.custom_tags['environment'] AS environment,
  u.billing_origin_product     AS product,
  ROUND(SUM(u.usage_quantity * p.pricing.effective_list.default), 2) AS list_cost
FROM system.billing.usage u
JOIN system.billing.list_prices p
  ON  u.cloud    = p.cloud
  AND u.sku_name = p.sku_name
  AND u.usage_start_time >= p.price_start_time
  AND (p.price_end_time IS NULL OR u.usage_start_time < p.price_end_time)
WHERE u.usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY 1, 2, 3
ORDER BY list_cost DESC;

SELECT
  COALESCE(custom_tags['domain'], '<untagged>') AS domain,
  ROUND(SUM(usage_quantity), 2)                 AS dbus,
  ROUND(100 * SUM(usage_quantity)
        / SUM(SUM(usage_quantity)) OVER (), 1)  AS pct
FROM system.billing.usage
WHERE usage_date >= date_trunc('QUARTER', current_date())
GROUP BY 1
ORDER BY dbus DESC;