⚡

ETL/ELT数据管道模式Skill etl-elt-patterns

本技能详细介绍了ETL和ELT数据管道模式，包括设计、实施、优化和数据质量控制，适用于数据工程和数据分析，关键词包括ETL、ELT、数据管道、数据转换、数据仓库、数据质量、现代数据栈。

ETL开发 1 次安装 3 次浏览 更新于 3/11/2026

name: etl-elt-patterns description: 用于设计数据管道、选择ETL和ELT方法或实施数据转换模式时使用。涵盖现代数据管道架构。 allowed-tools: Read, Glob, Grep

ETL/ELT 模式

数据提取、加载和转换的模式，包括现代ELT方法和管道设计。

何时使用此技能

  • 选择ETL和ELT之间
  • 设计数据管道
  • 实施数据转换
  • 构建现代数据栈
  • 处理管道中的数据质量

ETL vs ELT

ETL（提取、转换、加载）

┌─────────┐     ┌─────────────┐     ┌─────────────┐
│ 源数据  │ ──► │  转换服务器 │ ──► │  数据仓库  │
└─────────┘     └─────────────┘     └─────────────┘
                （转换发生在加载之前）

特性：
- 加载前转换
- 需要ETL服务器/工具
- 写入时定义模式
- 传统方法

最佳适用场景：
- 复杂转换
- 目标存储有限
- 严格的数据质量要求
- 遗留系统

ELT（提取、加载、转换）

┌─────────┐     ┌─────────────┐     ┌─────────────┐
│ 源数据  │ ──► │   目标系统  │ ──► │   转换过程  │
└─────────┘     │ （加载原始数据）│     │ （就地转换）│
                └─────────────┘     └─────────────┘
                                    （转换发生在加载之后）

特性：
- 先加载，后转换
- 利用目标系统的计算能力
- 读取时定义模式
- 现代方法

最佳适用场景：
- 云数据仓库
- 灵活探索
- 迭代开发
- 大数据量

比较

因素 ETL ELT
转换时机 加载前 加载后
计算位置 独立服务器 目标系统
原始数据访问 有限 完整
灵活性 低 高
延迟 较高 较低
成本模型 ETL服务器 + 存储 存储 + 目标计算
最佳适用 复杂、预定义 探索性、迭代

现代数据栈

提取：        Fivetran, Airbyte, Stitch, 自定义
                        │
                        ▼
加载：           云数据仓库（Snowflake, BigQuery, Redshift）
                        │
                        ▼
转换：            dbt, Dataform, SQLMesh
                        │
                        ▼
可视化：          Looker, Tableau, Metabase

dbt（数据构建工具）

核心概念：
- 模型：定义转换的SQL SELECT语句
- 测试：数据质量断言
- 文档：内联文档和血缘关系
- 宏：可重用的SQL代码片段

示例模型：
-- models/customers.sql
SELECT
    customer_id,
    first_name,
    last_name,
    order_count
FROM {{ ref('stg_customers') }}
LEFT JOIN {{ ref('customer_orders') }} USING (customer_id)

管道模式

全量刷新

策略：删除并重新创建整个表

过程：
1. 从源提取所有数据
2. 清空目标表
3. 加载所有数据

优点：简单、一致
缺点：对大表慢，无法处理删除
最佳适用：小维度表、参考数据

增量加载

策略：只处理新增或更改的记录

过程：
1. 跟踪高水位标记（最后处理的时间戳/ID）
2. 提取大于高水位标记的记录
3. 合并到目标

优点：快速、高效
缺点：复杂，可能错过更新
最佳适用：大事实表、事件数据

变更数据捕获（CDC）

策略：捕获源的所有更改

方法：
┌────────────────────────────────────────────────┐
│ 基于日志的CDC（Debezium, AWS DMS）              │
│ - 读取数据库事务日志                           │
│ - 捕获插入、更新、删除                         │
│ - 无需修改源表                                 │
└────────────────────────────────────────────────┘

┌────────────────────────────────────────────────┐
│ 基于触发器的CDC                                │
│ - 数据库触发器响应更改                         │
│ - 写入变更表                                   │
│ - 增加源负载                                   │
└────────────────────────────────────────────────┘

┌────────────────────────────────────────────────┐
│ 基于时间戳的CDC                                │
│ - 通过updated_at时间戳查询                      │
│ - 简单但可能错过硬删除                         │
└────────────────────────────────────────────────┘

合并（更新插入）模式

-- Snowflake/BigQuery风格的MERGE
MERGE INTO target t
USING source s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET
    t.name = s.name,
    t.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (id, name, created_at)
    VALUES (s.id, s.name, CURRENT_TIMESTAMP);

数据质量模式

验证门

带有质量门的管道：

提取 → 验证 → 加载 → 转换 → 验证 → 服务
            │                              │
            ▼                              ▼
         隔离区                       警报/阻止
         （坏记录）                   （质量问题）

质量检查

模式验证：
- 必填字段存在
- 数据类型匹配
- 字段长度在限制内

数据验证：
- 空值检查
- 范围检查
- 格式验证（日期、邮箱）
- 参照完整性

统计验证：
- 行数在预期范围内
- 值分布正常
- 无意外重复

数据合约

定义生产者和消费者之间的期望：

{
  "contract_version": "1.0",
  "schema": {
    "customer_id": {"type": "string", "required": true},
    "email": {"type": "string", "format": "email"},
    "created_at": {"type": "timestamp"}
  },
  "quality": {
    "freshness": "< 1 小时",
    "completeness": "> 99%",
    "row_count": "10000-100000"
  }
}

管道架构

批处理管道

基于调度的处理：

┌─────────┐     ┌─────────┐     ┌─────────┐
│  Cron   │ ──► │  Spark  │ ──► │   数据仓库  │
│（每日）  │     │（ETL）  │     │         │
└─────────┘     └─────────┘     └─────────┘

最佳适用：非实时、大容量
工具：Airflow, Dagster, Prefect

流处理管道

实时处理：

┌─────────┐     ┌─────────┐     ┌─────────┐
│  Kafka  │ ──► │  Flink  │ ──► │   数据仓库  │
│（事件）  │     │（处理）  │     │（流式）  │
└─────────┘     └─────────┘     └─────────┘

最佳适用：实时分析、事件驱动
工具：Kafka Streams, Flink, Spark Streaming

Lambda架构

批处理 + 速度层：

                    ┌─────────────────┐
             ┌────► │   批处理层     │ ────┐
             │      │ （全面）       │     │
┌─────────┐  │      └─────────────────┘     │  ┌─────────┐
│  数据源  │──┤                              ├─►│  服务层  │
│         │  │      ┌─────────────────┐     │  │         │
└─────────┘  └────► │   速度层       │ ────┘  └─────────┘
                    │ （实时）       │
                    └─────────────────┘

优点：完整 + 实时
缺点：复杂，逻辑重复

Kappa架构

纯流处理（从日志重播）：

┌─────────┐     ┌─────────┐     ┌─────────┐
│  Kafka  │ ──► │ 流处理  │ ──► │  服务层  │
│ （日志）  │     │ 进程   │     │         │
└─────────┘     └─────────┘     └─────────┘
      │
      └── 用于重处理的回放

优点：简单，单一代码库
缺点：需要可重播日志

编排

基于DAG的编排

任务的有向无环图：

    extract_a ──┐
                ├── transform ── load
    extract_b ──┘

工具：Airflow, Dagster, Prefect

编排最佳实践

1. 幂等任务（安全重试）
2. 清晰的依赖关系
3. 适当的粒度
4. 监控和警报
5. 回填支持
6. 参数化运行

错误处理

重试策略

暂时性错误（网络、超时）：
- 指数退避
- 最大重试次数
- 熔断器

数据错误（验证失败）：
- 隔离坏记录
- 继续处理好记录
- 警报审查

死信队列

失败记录 → DLQ → 手动审查 → 重处理

捕获：
- 原始记录
- 错误消息
- 时间戳
- 重试计数

最佳实践

管道设计

1. 幂等转换
2. 清晰的谱系跟踪
3. 适当的检查点
4. 优雅的故障处理
5. 全面的日志记录
6. 数据质量门

性能

1. 适当分区数据
2. 尽可能增量处理
3. 并行提取
4. 高效文件格式（Parquet, ORC）
5. 压缩
6. 资源大小调整

相关技能

  • data-architecture - 数据平台设计
  • stream-processing - 实时处理
  • ml-system-design - 特征工程