Databricks Delta Live Tables数据质量校验失败仍加载下游表求助

核心原理：利用Expectation的fail_on_error模式 #

DLT的Expectations默认采用allow_violations模式（仅记录违规、不阻断流程），要实现校验失败时终止流水线或阻止下游表加载，必须显式指定fail_on_error = true——一旦触发违反规则的情况，整个流水线会立即停止，下游依赖表自然不会执行加载操作。

复杂校验的SQL定义示例 #

1. 单字段主键唯一性校验 #

假设report_table的主键为report_id，要确保其全局唯一，SQL定义如下：

CREATE OR REFRESH LIVE TABLE report_table
TBLPROPERTIES (
  'quality' = 'silver'
)
AS SELECT
  report_id,
  customer_id,
  report_date,
  amount
FROM LIVE.raw_report
WHERE
  report_id IS NOT NULL
-- 定义主键唯一性校验，触发违规则终止流水线
CONSTRAINT report_id_unique EXPECT (COUNT(report_id) = COUNT(DISTINCT report_id)) ON VIOLATION FAIL ON ERROR;

2. 复合主键校验 #

如果采用report_id + customer_id作为复合主键，校验逻辑可调整为：

CREATE OR REFRESH LIVE TABLE report_table
TBLPROPERTIES (
  'quality' = 'silver'
)
AS SELECT
  report_id,
  customer_id,
  report_date,
  amount
FROM LIVE.raw_report
WHERE
  report_id IS NOT NULL AND customer_id IS NOT NULL
-- 复合主键唯一性校验，违规则终止流水线
CONSTRAINT composite_pk_unique EXPECT (COUNT(CONCAT(report_id, '|', customer_id)) = COUNT(DISTINCT CONCAT(report_id, '|', customer_id))) ON VIOLATION FAIL ON ERROR;

3. 多条件组合校验 #

同时校验主键唯一、金额非负、日期合法的场景：

CREATE OR REFRESH LIVE TABLE report_table
TBLPROPERTIES (
  'quality' = 'silver'
)
AS SELECT
  report_id,
  customer_id,
  report_date,
  amount
FROM LIVE.raw_report
WHERE
  report_id IS NOT NULL AND customer_id IS NOT NULL
-- 主键唯一校验
CONSTRAINT report_id_unique EXPECT (COUNT(report_id) = COUNT(DISTINCT report_id)) ON VIOLATION FAIL ON ERROR,
-- 金额非负校验
CONSTRAINT amount_non_negative EXPECT (amount >= 0) ON VIOLATION FAIL ON ERROR,
-- 日期合法性校验（不晚于当前日期）
CONSTRAINT valid_report_date EXPECT (report_date <= CURRENT_DATE()) ON VIOLATION FAIL ON ERROR;

关键注意事项 #

• 触发逻辑：多个FAIL ON ERROR约束中只要有一个被触发，流水线会立即停止，所有下游节点（包括Customer_s表）都不会执行。
• 性能优化：聚合类校验（如主键唯一的COUNT对比）会增加计算开销，大数据量场景下可结合分区或批次逻辑优化，比如仅校验当前批次数据：

  CONSTRAINT report_id_unique_current_batch EXPECT (
    COUNT(report_id) OVER (PARTITION BY batch_id) = COUNT(DISTINCT report_id) OVER (PARTITION BY batch_id)
  ) ON VIOLATION FAIL ON ERROR;
  

• 流水线状态：触发fail_on_error后，DLT流水线会标记为失败，需修复数据或调整校验规则后重新运行。

内容的提问来源于stack exchange，提问作者motivated_penguin