基于Azure Data Factory实现多结构Excel文件动态数据质量校验

核心思路 #

通过批量遍历+动态规则匹配+数据流分流的组合，实现无需为每个Excel文件单独创建流水线的通用校验流程，核心依赖ADF的For Each、Lookup和Data Flow组件完成动态逻辑。

具体实现步骤 #

1. 批量遍历Blob中的Excel文件 #

• 用Get Metadata活动指向目标Azure Blob容器，配置获取Child Items字段，同时添加过滤器（比如只筛选.xlsx/.xls后缀的文件），拿到容器内所有待处理的Excel文件列表。
• 接For Each活动，遍历Get Metadata输出的文件集合，将每个文件的名称、存储路径作为变量/参数传入后续流程。

2. 动态匹配对应业务规则 #

• 在For Each内部添加Lookup活动，根据当前Excel文件的名称（需确保文件名包含ExcelTemplateName或ExcelTemplateId，比如SalesData_Template001.xlsx），从数据库规则表中查询该模板对应的所有校验规则（列名、数据类型、最大长度等）。
• 将Lookup返回的规则集合（可转为JSON格式）作为参数传递给后续的Data Flow活动。

3. Data Flow核心校验与分流 #

这是实现动态校验的关键环节：

3.1 动态读取Excel源

创建Excel数据源，通过参数传递当前遍历的文件路径/名称，开启动态列模式，让Data Flow自动识别当前Excel的列结构，无需提前定义列。

3.2 解析校验规则

添加Derived Column转换，用json($ruleParam)将传入的规则参数解析为可遍历的数组，再通过map()函数生成每一列对应的校验逻辑表达式。

3.3 执行数据质量校验

针对每一列应用规则：

• 数据类型校验：用cast()函数尝试将列值转换为规则指定的类型，捕获转换失败的记录。
• 最大长度校验：对字符串类型列，用length()函数判断是否超过规则定义的MaxLength。
• 用Conditional Split转换将记录分为两支：合规记录（满足所有规则）和不合规记录（至少违反一条规则），同时为不合规记录添加错误描述字段（比如concat('列', $columnName, '长度超过最大限制', $maxLength)）。

3.4 分流输出

• 合规记录输出到数据库的location01表（如果不同模板对应不同业务表，可通过参数传递目标表名）。
• 不合规记录输出到location02表，同时保留原记录内容、错误描述、模板ID、文件名等信息，方便后续排查修复。

4. 流水线参数化配置 #

为流水线设置通用参数，比如BlobContainerName、RuleTableSchema、TargetSchema等，避免硬编码，提升维护性；在For Each和Data Flow中通过参数传递动态值，确保流程的通用性。

关键注意事项 #

• 文件命名规范：必须保证Excel文件名能关联到ExcelTemplateId/ExcelTemplateName，否则无法匹配对应规则。
• 规则表设计：建议规则表包含ExcelTemplateId、ExcelTemplateName、ColumnName、DataType、MaxLength、IsRequired等字段，便于扩展更多校验规则（比如必填校验）。
• 性能优化：如果待处理文件数量多，可设置For Each的并行度；针对大文件，开启Data Flow的分区功能提升处理效率。
• 错误记录完整性：务必在location02中保留足够的上下文信息（文件名、模板ID、错误原因），方便后续数据修复。

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