data-engineer-handbook 如何实现幂等可重跑的 SCD Type 2 维表:从 streak 回填到增量流水线的实操拆解【免费下载链接】data-engineer-handbookThis is a repo with links to everything youd ever want to learn about data engineering项目地址: https://gitcode.com/GitHub_Trending/da/data-engineer-handbookdata-engineer-handbook 用纯 SQL 做可重跑的 SCD Type 2 维表:拆解 streak 回填与增量合并实现,附实验环境配置和踩坑处置。核心机制拆解:SCD Type 2 维表的可重跑写法先说结论:整套机制没有触发器、没有存储过程,只有三条 set-based 查询串成一条流水线——先用 FULL OUTER JOIN 把上赛季快照 本赛季事实滚成最新快照,再用 gaps-and-islands 一次性回填出历史 SCD 行,最后用四路 UNION ALL 做增量合并。只要上赛季 SCD与本赛季事实表不变,重跑任意一步结果都不变,这是它敢在调度里裸重放的原因。年度快照怎么滚:COALESCE FULL JOIN 的写法players 表里每个球员一行,seasons是season_stats类型的数组,每季追加一行。pipeline_query.sql 把current_season 1997的旧快照与season 1998的 player_seasons 事实表做 FULL OUTER JOIN:维属性(height、college、draft 等)逐个 COALESCE,谁有值取谁;seasons数组用|| ARRAY[ROW(...)]追加本赛季;scoring_class按 pts 阈值重算(20 star、15 good、10 average、否则 bad);is_active直接取ts.season IS NOT NULL——本季没数据的人自动标记退役。FULL JOIN 保证新人和老球员两边都不丢。回填:LAG 窗口累计和压缩连续区间backfill 在 scd_generation_query.sql,是教科书式的 gaps-and-islands:-- lecture-lab/scd_generation_query.sql(streak_started CTE,节选) SELECT player_name, current_season, scoring_class, LAG(scoring_class, 1) OVER (PARTITION BY player_name ORDER BY current_season) scoring_class OR LAG(scoring_class, 1) OVER (PARTITION BY player_name ORDER BY current_season) IS NULL AS did_change -- 首行或取值变化 → 标记新区间起点 FROM players下一步对did_change按同一窗口求累计和,同一段连续取值共享一个streak_identifier;再按 (player, class, streak_id) 分组取 MIN/MAX 赛季,就得到每条 SCD 行的 start_date 与 end_date。一次查询把整张历史表回填完,不依赖逐行循环。增量合并:四类记录 UNION ALL,封口 开档incremental_scd_query.sql 读上赛季 SCD(当前行满足end_season 2021)和本赛季 players,拆成四路拼成新快照:-- lecture-lab/incremental_scd_query.sql(节选,拼出 2022 赛季快照) SELECT * FROM historical_scd -- end_season 2021 的历史行,原样保留 UNION ALL SELECT * FROM unchanged_records -- 未变:把 end_season 续到本赛季 UNION ALL SELECT * FROM unnested_changed_records -- 变值:旧行封口 新行开档 UNION ALL SELECT * FROM new_records -- LEFT JOIN 未命中的新球员变化行一旧一新是靠UNNEST(ARRAY[ROW(...), ROW(...)])从自定义 composite 类型scd_type里展开的,一次 JOIN 顶两次 INSERT。另外注意 players_scd_table.sql 里那列叫end_date,存的其实是赛季号,照抄 DDL 时别被字段名带偏。 场景化配置:从 Docker 实验环境到生产快照流水线本地 Docker 实验环境(推荐入门)cp example.env .env # Makefile 的 up 目标会检查它,缺了只补文件不启动 make up # 起 postgres:14 pgadmin4 make ip # 确认容器网络与端口映射init-db.sh 挂在/docker-entrypoint-initdb.d,容器首次启动时自动pg_restoredata.dump,再按序执行 homework 目录下的 SQL,所以一条make up就能拿到全套样本数据。PGAdmin 入口 http://localhost:5050,登录用 .env 里的 PGADMIN_EMAIL/PGADMIN_PASSWORD,连接主机填my-postgres-container。裸机或 CI 上恢复 data.dumppg_restore -c --if-exists -U postgres -d postgres data.dump-c --if-exists让恢复脚本本身幂等:CI 里重跑同一条命令不会因对象已存在而中断。失败时再退回不带-c的裸pg_restore,报错会更具体。把增量 SCD 接到自己的年度/月度快照把 pipeline 与 incremental 两个文件里的赛季字面量(1997、1998、2021、2022)参数化再执行:# 调度里渲染后执行:把赛季常量替换成参数,再灌库 sed -e s/1997/${LAST_SEASON}/g -e s/1998/${THIS_SEASON}/g \ lecture-lab/pipeline_query.sql /tmp/pipeline.sql psql -U postgres -d postgres -f /tmp/pipeline.sql为什么这么配:增量查询的读集只有上赛季 SCD 本赛季事实表,赛季号参数化后,同一赛季重跑读到的输入不变,输出自然不变,调度失败可以放心重放。⚡ 踩坑实录:幂等 SCD 流水线的三个高频故障改了 .env 但 PGAdmin 密码不生效→ 根因:PGAdmin 只在数据卷为空的首次启动写入默认账户,之后 .env 根本没人读。处置:make restart(down -v 清掉 pgadmin-data 卷再 up)。容器在跑但 \dt 看不到表→ 根因:/docker-entrypoint-initdb.d只在数据目录为空时执行,之前用错 dump 启动过一次,之后每次启动都跳过初始化。处置:docker compose down -v删掉 postgres-data 卷再 make up。make up 报 5432 端口被占→ 根因:本机 Postgres 或别的容器占了宿主端口。处置:别杀进程,把 .env 里HOST_PORT改成 15432——容器内端口恒为 5432,只改宿主侧映射即可。速查与延伸阅读.env— 凭据与端口,先cp example.env .env再 make up;改完需 down -v 才生效HOST_PORT/PGADMIN_PORT— 默认 5432 / 5050,只有宿主侧端口,冲突只改这里data.dump— 基础数据集,容器首次启动自动恢复,不要手动重跑覆盖scoring_class阈值 — pts 20 star、 15 good、 10 average,其余 badend_date(players_scd_table)— 字段名有误导,实际存 end 赛季号延伸阅读(仓库内路径):1-dimensional-data-modeling/README.md — 环境搭建、连接配置与常见报错处理homework/homework.md — 用 actor_films 数据复刻整套 SCD 的作业lecture-lab/ — 全部示例 SQL 所在目录【免费下载链接】data-engineer-handbookThis is a repo with links to everything youd ever want to learn about data engineering项目地址: https://gitcode.com/GitHub_Trending/da/data-engineer-handbook创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
data-engineer-handbook 如何实现幂等可重跑的 SCD Type 2 维表:从 streak 回填到增量流水线的实操拆解
data-engineer-handbook 如何实现幂等可重跑的 SCD Type 2 维表:从 streak 回填到增量流水线的实操拆解【免费下载链接】data-engineer-handbookThis is a repo with links to everything youd ever want to learn about data engineering项目地址: https://gitcode.com/GitHub_Trending/da/data-engineer-handbookdata-engineer-handbook 用纯 SQL 做可重跑的 SCD Type 2 维表:拆解 streak 回填与增量合并实现,附实验环境配置和踩坑处置。核心机制拆解:SCD Type 2 维表的可重跑写法先说结论:整套机制没有触发器、没有存储过程,只有三条 set-based 查询串成一条流水线——先用 FULL OUTER JOIN 把上赛季快照 本赛季事实滚成最新快照,再用 gaps-and-islands 一次性回填出历史 SCD 行,最后用四路 UNION ALL 做增量合并。只要上赛季 SCD与本赛季事实表不变,重跑任意一步结果都不变,这是它敢在调度里裸重放的原因。年度快照怎么滚:COALESCE FULL JOIN 的写法players 表里每个球员一行,seasons是season_stats类型的数组,每季追加一行。pipeline_query.sql 把current_season 1997的旧快照与season 1998的 player_seasons 事实表做 FULL OUTER JOIN:维属性(height、college、draft 等)逐个 COALESCE,谁有值取谁;seasons数组用|| ARRAY[ROW(...)]追加本赛季;scoring_class按 pts 阈值重算(20 star、15 good、10 average、否则 bad);is_active直接取ts.season IS NOT NULL——本季没数据的人自动标记退役。FULL JOIN 保证新人和老球员两边都不丢。回填:LAG 窗口累计和压缩连续区间backfill 在 scd_generation_query.sql,是教科书式的 gaps-and-islands:-- lecture-lab/scd_generation_query.sql(streak_started CTE,节选) SELECT player_name, current_season, scoring_class, LAG(scoring_class, 1) OVER (PARTITION BY player_name ORDER BY current_season) scoring_class OR LAG(scoring_class, 1) OVER (PARTITION BY player_name ORDER BY current_season) IS NULL AS did_change -- 首行或取值变化 → 标记新区间起点 FROM players下一步对did_change按同一窗口求累计和,同一段连续取值共享一个streak_identifier;再按 (player, class, streak_id) 分组取 MIN/MAX 赛季,就得到每条 SCD 行的 start_date 与 end_date。一次查询把整张历史表回填完,不依赖逐行循环。增量合并:四类记录 UNION ALL,封口 开档incremental_scd_query.sql 读上赛季 SCD(当前行满足end_season 2021)和本赛季 players,拆成四路拼成新快照:-- lecture-lab/incremental_scd_query.sql(节选,拼出 2022 赛季快照) SELECT * FROM historical_scd -- end_season 2021 的历史行,原样保留 UNION ALL SELECT * FROM unchanged_records -- 未变:把 end_season 续到本赛季 UNION ALL SELECT * FROM unnested_changed_records -- 变值:旧行封口 新行开档 UNION ALL SELECT * FROM new_records -- LEFT JOIN 未命中的新球员变化行一旧一新是靠UNNEST(ARRAY[ROW(...), ROW(...)])从自定义 composite 类型scd_type里展开的,一次 JOIN 顶两次 INSERT。另外注意 players_scd_table.sql 里那列叫end_date,存的其实是赛季号,照抄 DDL 时别被字段名带偏。 场景化配置:从 Docker 实验环境到生产快照流水线本地 Docker 实验环境(推荐入门)cp example.env .env # Makefile 的 up 目标会检查它,缺了只补文件不启动 make up # 起 postgres:14 pgadmin4 make ip # 确认容器网络与端口映射init-db.sh 挂在/docker-entrypoint-initdb.d,容器首次启动时自动pg_restoredata.dump,再按序执行 homework 目录下的 SQL,所以一条make up就能拿到全套样本数据。PGAdmin 入口 http://localhost:5050,登录用 .env 里的 PGADMIN_EMAIL/PGADMIN_PASSWORD,连接主机填my-postgres-container。裸机或 CI 上恢复 data.dumppg_restore -c --if-exists -U postgres -d postgres data.dump-c --if-exists让恢复脚本本身幂等:CI 里重跑同一条命令不会因对象已存在而中断。失败时再退回不带-c的裸pg_restore,报错会更具体。把增量 SCD 接到自己的年度/月度快照把 pipeline 与 incremental 两个文件里的赛季字面量(1997、1998、2021、2022)参数化再执行:# 调度里渲染后执行:把赛季常量替换成参数,再灌库 sed -e s/1997/${LAST_SEASON}/g -e s/1998/${THIS_SEASON}/g \ lecture-lab/pipeline_query.sql /tmp/pipeline.sql psql -U postgres -d postgres -f /tmp/pipeline.sql为什么这么配:增量查询的读集只有上赛季 SCD 本赛季事实表,赛季号参数化后,同一赛季重跑读到的输入不变,输出自然不变,调度失败可以放心重放。⚡ 踩坑实录:幂等 SCD 流水线的三个高频故障改了 .env 但 PGAdmin 密码不生效→ 根因:PGAdmin 只在数据卷为空的首次启动写入默认账户,之后 .env 根本没人读。处置:make restart(down -v 清掉 pgadmin-data 卷再 up)。容器在跑但 \dt 看不到表→ 根因:/docker-entrypoint-initdb.d只在数据目录为空时执行,之前用错 dump 启动过一次,之后每次启动都跳过初始化。处置:docker compose down -v删掉 postgres-data 卷再 make up。make up 报 5432 端口被占→ 根因:本机 Postgres 或别的容器占了宿主端口。处置:别杀进程,把 .env 里HOST_PORT改成 15432——容器内端口恒为 5432,只改宿主侧映射即可。速查与延伸阅读.env— 凭据与端口,先cp example.env .env再 make up;改完需 down -v 才生效HOST_PORT/PGADMIN_PORT— 默认 5432 / 5050,只有宿主侧端口,冲突只改这里data.dump— 基础数据集,容器首次启动自动恢复,不要手动重跑覆盖scoring_class阈值 — pts 20 star、 15 good、 10 average,其余 badend_date(players_scd_table)— 字段名有误导,实际存 end 赛季号延伸阅读(仓库内路径):1-dimensional-data-modeling/README.md — 环境搭建、连接配置与常见报错处理homework/homework.md — 用 actor_films 数据复刻整套 SCD 的作业lecture-lab/ — 全部示例 SQL 所在目录【免费下载链接】data-engineer-handbookThis is a repo with links to everything youd ever want to learn about data engineering项目地址: https://gitcode.com/GitHub_Trending/da/data-engineer-handbook创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
