动态表设计方法：从加工目标到增量管道

增量计算机制与动态表 讲了为什么用增量计算，数据新鲜度与动态表 讲了怎么按业务需要给数据分级。本文承接这两篇，讲一件具体的事：拿到一个数据加工目标，如何把它落成动态表。 先看核心机制，再看动态表的四种典型加工形态（每种在增量引擎下行为不同），最后是分层、新鲜度这些设计权衡。

先看看不用动态表你要写什么

设想一个最常见的加工目标：从原始订单维护一张"各产品有效销售汇总"（只算已完成订单，按产品统计单数和金额），并随业务变化保持新鲜。

如果用传统方式自己维护增量，你要写的不是一条查询，而是一套过程：

  • 记录上次处理到哪个版本，每次捞出这之后的新订单；
  • 新订单里属于"已完成"的，累加进对应产品的汇总；
  • 可订单状态会变——一笔已完成订单被退款了，你得找到它原先计进了哪个产品，把单数和金额再减回去；
  • 退款、改金额这类"撤销"操作（业内叫 retraction），是手写增量里最难写对的部分，漏一处汇总就长期偏差；
  • 还要安排调度、处理失败重试、保证不重不漏。

这套过程才是 ETL 真正的成本所在，也是最容易出 bug 的地方。动态表的设计出发点，就是把这套过程整个拿走。

核心机制：声明结果，而不是编写过程

动态表带来的根本转变，是从"编写并维护一个更新过程"到"声明一个会自我维护的结果"。

你只写结果的全量定义——一条标准 SQL，说明"这张表应该等于什么"。至于增量怎么算、撤销怎么减、什么时候刷新，全部交给引擎：

  • 增量怎么算：引擎识别上游变更（新增、修改、删除），与历史结果合并，只处理变化的部分。
  • 什么时候算：由 
    REFRESH INTERVAL
    REFRESH INTERVAL
    声明刷新间隔，引擎按间隔自适应选择全量或增量执行。

这条机制落到具体 SQL 上，按加工意图不同有几种典型形态。它们的写法你都熟悉——过滤、聚合、关联、去重——关键差别在于每种形态在增量引擎下的行为不同，这决定了各自的刷新成本和适用边界。下面逐一看，每种都给一个完整可跑的例子，连它刷新时怎么增量更新一起看清楚。

动态表的四种加工形态

全文示例共用一张原始订单表（ODS）。其中订单 4 是"待支付"，用来观察过滤形态如何把它挡在外面：

CREATE TABLE doc_dt_orders (order_id INT, product STRING, amount BIGINT, status STRING); INSERT INTO doc_dt_orders VALUES (1,'手机',5000,'已完成'), (2,'手机',3000,'已完成'), (3,'耳机', 800,'已完成'), (4,'平板',2000,'待支付');

过滤清洗型（Filter / Project）

你想做的事：从一堆原始数据里只留下你要的那部分，或者只取几列、统一一下格式——把脏数据、用不上的行挡在门外。比如：只看"已完成"的订单、只留付费用户、把日志里的测试数据滤掉。

下面的例子只保留已完成订单。

CREATE DYNAMIC TABLE doc_dt_dwd REFRESH INTERVAL 5 MINUTE VCLUSTER DEFAULT AS SELECT order_id, product, amount FROM doc_dt_orders WHERE status = '已完成'; REFRESH DYNAMIC TABLE doc_dt_dwd; SELECT * FROM doc_dt_dwd ORDER BY order_id;

+----------+---------+--------+ | order_id | product | amount | +----------+---------+--------+ | 1 | 手机 | 5000 | | 2 | 手机 | 3000 | | 3 | 耳机 | 800 | +----------+---------+--------+

待支付的订单 4 被挡在外面。现在新增一笔已完成、一笔待支付，刷新后看增量：

INSERT INTO doc_dt_orders VALUES (5,'耳机',1200,'已完成'),(6,'平板',999,'待支付'); REFRESH DYNAMIC TABLE doc_dt_dwd;

查刷新历史，这次是 

INCREMENTAL
INCREMENTAL
，
stats
stats
为 
{"rows_deleted":"0","rows_inserted":"1"}
{"rows_deleted":"0","rows_inserted":"1"}
——只有订单 5 进来，订单 6（待支付）被过滤掉，根本没触发写入。

增量特性：过滤/投影是逐行独立的——每条变更行单独判断、单独通过，不依赖历史结果。这是最纯粹、最省的增量形态。

什么时候选它：下游只要一份干净、规整的明细、不做汇总统计时——比如把杂乱的原始订单洗成标准明细，或给数据脱敏。

聚合汇总型（Aggregate）

你想做的事：你要的不是明细，而是统计数字——按某个维度算总数、求和、平均，而且数字要随业务实时变。比如：每个产品卖了多少单、收了多少钱；每个城市今天新增多少用户；每个客户累计花了多少。

下面按产品统计单数和金额，建在上面的 DWD 之上。

CREATE DYNAMIC TABLE doc_dt_dws REFRESH INTERVAL 5 MINUTE VCLUSTER DEFAULT AS SELECT product, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM doc_dt_dwd GROUP BY product; REFRESH DYNAMIC TABLE doc_dt_dws; SELECT * FROM doc_dt_dws ORDER BY product;

+---------+-----------+--------------+ | product | order_cnt | total_amount | +---------+-----------+--------------+ | 手机 | 2 | 8000 | | 耳机 | 2 | 2000 | +---------+-----------+--------------+

这个形态的真正看点是撤销。把订单 2（手机 3000）改成已退款——它本已计进汇总，按业务应该被扣掉：

UPDATE doc_dt_orders SET status = '已退款' WHERE order_id = 2; REFRESH DYNAMIC TABLE doc_dt_dwd; REFRESH DYNAMIC TABLE doc_dt_dws; SELECT * FROM doc_dt_dws ORDER BY product;

+---------+-----------+--------------+ | product | order_cnt | total_amount | +---------+-----------+--------------+ | 手机 | 1 | 5000 | | 耳机 | 2 | 2000 | +---------+-----------+--------------+

手机从 2 单 8000 自己减成了 1 单 5000，耳机不受影响。查刷新历史仍是 

INCREMENTAL
INCREMENTAL
，
stats
stats
为 
{"rows_deleted":"1","rows_inserted":"1"}
{"rows_deleted":"1","rows_inserted":"1"}
——引擎没重算整表，只替换了"手机"这一个分组行。一个 
UPDATE
UPDATE
改状态，穿过 DWD 的 
WHERE
WHERE
变成"踢掉一行"，再穿过 DWS 的 
GROUP BY
GROUP BY
变成"把那笔金额从手机汇总里减掉"。这条"撤销→过滤→减聚合"的链路，正是本文开头列的、手写增量里最容易写错的那部分——而你从头到尾只写了两条 
SELECT
SELECT
。

增量特性：聚合把变更与历史聚合结果合并，因此天然支持 retraction——删除或改小一笔，就是从汇总里做减法。这是动态表最有价值的形态。

什么时候选它：做实时看板、报表或各类计数指标时——凡是"要一个会自动更新的数字"的场景。

维度关联型（Join / Enrich）

你想做的事：你的明细表里只有编码或 ID，想把另一张表里的属性补上，拼成一张"什么都有"的大宽表。比如：订单里只有商品编码，想补上商品名、品类、价格；用户行为里只有用户 ID，想补上用户的地区、等级。

下面先准备一张产品维表，把品类补到订单上：

CREATE TABLE doc_dt_dim (product STRING, category STRING); INSERT INTO doc_dt_dim VALUES ('手机','电子'),('耳机','配件'),('平板','电子'); CREATE DYNAMIC TABLE doc_dt_wide REFRESH INTERVAL 5 MINUTE VCLUSTER DEFAULT AS SELECT o.order_id, o.product, d.category, o.amount FROM doc_dt_dwd o JOIN doc_dt_dim d ON o.product = d.product; REFRESH DYNAMIC TABLE doc_dt_wide; SELECT * FROM doc_dt_wide ORDER BY order_id;

此时 DWD 是退款后的状态（订单 1、3、5），关联维表后每行挂上品类：

+----------+---------+----------+--------+ | order_id | product | category | amount | +----------+---------+----------+--------+ | 1 | 手机 | 电子 | 5000 | | 3 | 耳机 | 配件 | 800 | | 5 | 耳机 | 配件 | 1200 | +----------+---------+----------+--------+

关联形态有个事实表见不到的现象：改一行维表，会牵动引用它的所有事实行。把"耳机"的品类改成数码：

UPDATE doc_dt_dim SET category = '数码' WHERE product = '耳机'; REFRESH DYNAMIC TABLE doc_dt_wide; SELECT * FROM doc_dt_wide ORDER BY order_id;

+----------+---------+----------+--------+ | order_id | product | category | amount | +----------+---------+----------+--------+ | 1 | 手机 | 电子 | 5000 | | 3 | 耳机 | 数码 | 800 | | 5 | 耳机 | 数码 | 1200 | +----------+---------+----------+--------+

订单 3、5 两行的品类一起变了。这次刷新是 

INCREMENTAL
INCREMENTAL
，
stats
stats
为 
{"rows_deleted":"2","rows_inserted":"2"}
{"rows_deleted":"2","rows_inserted":"2"}
——引擎只重算了受这条维表变更影响的 2 行，没有整表重扫。

增量特性：事实新增时，变更行与维表历史连接；维表变更时，引用该维成员的所有事实行被重算（如上面这次刷新，一行维表改动连带重算了 2 行事实）。关联型也比过滤、聚合更容易回退全量：引擎按成本在增量和全量间自动选择，数据量小或表刚建好时可能直接走全量。例如在本例基础上向订单表新增一笔已完成订单再刷新，首次刷新会走 

FULL
FULL
（整表重算）、之后再新增才转 
INCREMENTAL
INCREMENTAL
。宽表的刷新成本因此波动更大，刷新间隔和集群规格要留余量。

这套增量维护并不挑 join 的复杂度：再接一张维表凑成三表、或把 

JOIN
JOIN
换成 
LEFT JOIN
LEFT JOIN
，都是一样的——
LEFT JOIN
LEFT JOIN
下暂时没匹配上维表的订单会先带着 NULL 出现，等维表补齐，那一行自动填上对应值、且只动这一行。再往大了说，Filter、Project、Join、Aggregate、Window 这些标准算子增量引擎都覆盖，不限查询复杂度，本文的四种形态不过是它们的不同组合；算子级的增量原理见增量计算机制与动态表。

什么时候选它：给报表或 BI 准备一张字段齐全、查的时候不用再临时拼表的大宽表。

最新态去重型（Latest-state / Dedup）

你想做的事：同一条记录在源表里有很多条历史版本（每改一次就追加一条），你只想要每条记录最新的那一条。比如：一个订单的状态改了好几次（待支付→已支付→已完成），你只想知道它现在是什么状态；从数据库同步过来的一串变更记录，你想还原成"现在的样子"。

下面准备一张订单状态流水表，取每个订单的最新状态：

CREATE TABLE doc_dt_orderlog (order_id INT, status STRING, version INT); INSERT INTO doc_dt_orderlog VALUES (1,'待支付',1),(1,'已支付',2),(1,'已完成',3), (2,'待支付',1),(2,'已支付',2); CREATE DYNAMIC TABLE doc_dt_latest REFRESH INTERVAL 5 MINUTE VCLUSTER DEFAULT AS SELECT order_id, status FROM ( SELECT order_id, status, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY version DESC) AS rn FROM doc_dt_orderlog ) WHERE rn = 1; REFRESH DYNAMIC TABLE doc_dt_latest; SELECT * FROM doc_dt_latest ORDER BY order_id;

每个订单取版本号最大的那条，得到当前态：

+----------+--------+ | order_id | status | +----------+--------+ | 1 | 已完成 | | 2 | 已支付 | +----------+--------+

给订单 2 追加一条更新的版本：

INSERT INTO doc_dt_orderlog VALUES (2,'已完成',3); REFRESH DYNAMIC TABLE doc_dt_latest; SELECT * FROM doc_dt_latest ORDER BY order_id;

+----------+--------+ | order_id | status | +----------+--------+ | 1 | 已完成 | | 2 | 已完成 | +----------+--------+

订单 2 的当前行被替换成已完成，订单 1 不动。刷新是 

INCREMENTAL
INCREMENTAL
，
stats
stats
为 
{"rows_deleted":"1","rows_inserted":"1"}
{"rows_deleted":"1","rows_inserted":"1"}
。

增量特性：用 

ROW_NUMBER()
ROW_NUMBER()
取每键最新版本，新事件到达只替换该键的当前行。

什么时候选它：要从一串变更记录里还原"现在是什么样"时——比如数据库同步过来的变更日志（CDC），或订单、工单、设备的最新状态。

四种形态对比

形态形态特征（SQL）增量行为什么时候选它
过滤清洗：只留要的、洗干净WHERE 过滤 / SELECT 投影逐行独立，最省；只处理通过的变更行标准明细层、数据脱敏
聚合汇总：要会实时变的统计数字GROUP BY + COUNT/SUM与历史聚合合并，支持撤销做减法看板、报表、计数
维度关联：把属性补全成大宽表明细 JOIN 维表变更行连历史；维表一改牵动其全部相关行；较易回退全量BI 宽表
最新态去重：同一记录只要最新版ROW_NUMBER 取每键最新新事件替换该键当前行还原当前态（CDC）

形态和分层是两回事

上面用 DWD 演示过滤、DWS 演示聚合，容易让人以为"形态是绑在层上的"。其实不是：层（ODS/DWD/DWS）是数据在管道里的位置，形态是这一层的 SQL 在做什么，两者正交。 同一种形态可以出现在任何层，一层也可能是几种形态的组合。

判断某一层能不能用动态表，只看一条：它的输入是不是已经在仓内。 动态表只在仓内表上做增量计算，做不了"外部数据进仓"那一跳。

这条线正好把 ODS 切成两种情况：

  • 原始落地——外部库、Kafka、文件第一次进仓的那张表——不能是动态表。这一跳要用 Pipe、实时同步或 
    COPY
    COPY
    写进一张普通表，动态表当不了摄入的落点。
  • 落地之后的整理——把刚进仓的原始数据清洗、去重成可用的标准表——可以是动态表，因为此时输入已在仓内。最典型的是 CDC：实时同步把变更流水落成一张追加表，再用一张动态表把它压成"源库的当前镜像"，这就是最新态去重型用在 ODS 层；若原始落地是纯追加事件、只需筛脏规整，那就是过滤清洗型用在 ODS 层。

所以"ODS 要不要用动态表"，取决于它的输入在不在仓内、要不要持续整理，而不是因为它叫 ODS。

由此引出的设计决策

把上面的机制和形态吃透，设计动态表时真正要权衡的是下面几件事。

拆几层。 例子里把过滤和聚合拆成 DWD、DWS 两层，而不是写成一条大 SQL，是因为分层后每一层的结果都能被复查、被复用，出问题也好定位——和数仓 ODS→DWD→DWS 的分层思想一致。层数不是越多越好：每多一层就多一次刷新开销和一段延迟，按加工逻辑的自然边界来分。

每层多新鲜。 刷新间隔不必全链路一刀切：底层贴近源头、可以勤刷，上层面向报表、可以松一些。怎么按业务价值定新鲜度，见数据新鲜度与动态表。两个硬约束要记住：刷新间隔必须大于单次刷新耗时（否则任务积压，用 

SHOW DYNAMIC TABLE REFRESH HISTORY
SHOW DYNAMIC TABLE REFRESH HISTORY
看 
duration
duration
）；上游的刷新频率决定下游能达到的最小延迟，整条链路的端到端延迟约等于各层间隔之和。多层管道建议用 Lakehouse Studio 配置"上游刷新完成触发下游"，而不是每层各自定时轮询。

别在定义里用非确定性函数。 

CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP()
、
RAND()
RAND()
、
UUID()
UUID()
写进动态表定义会让同一列在不同行取到不同时刻的值、无法复现，需要时间戳或唯一标识请在动态表之外处理——详见动态表中的非确定性函数。

相关文档

数据新鲜度与多表实时同步
实时数据管道的三块积木：Pipe、Table Stream、Dynamic Table
Yunqi © 2024 Yunqi, Inc. All Rights Reserved.
联系我们
预约咨询
微信咨询
电话咨询
邮件咨询