跳转到主内容
趣航编程网 - 趣学编程，启航技术之路！
📁 网站地图

SQL 数据仓库建模与 SQL 实践

顺亿
顺亿
• 👁 0人浏览过
星型模型比雪花模型更适合大多数SQL数据仓库查询，因其事实表直接关联维度主键，避免多层JOIN导致的执行计划膨胀、统计失真及分区裁剪失效。 为什么星型模型比雪花模型更适合大多数 SQL 数据仓库查询 因为事实表直接关联维度主键，避免多层
JOIN
带来的执行计划膨胀和统计信息失真。雪花模型看似规范，但在
GROUP BY
、
WHERE
过滤高基数维度属性时，常触发多余维度表扫描，尤其在 BigQuery、Redshift 或 Snowflake 上，优化器难以准确估算中间结果集大小。 实操建议： 把
product_category
、
customer_segment
这类稳定且低变更的属性，冗余进维度表（如
dim_product
），而非拆到单独的
dim_category
仅对真正需要多对一共享的字段（如国家/地区代码）保留雪花结构，且确保该表有唯一主键 + 显式
NOT NULL
约束 在 Snowflake 中，如果用了
CLUSTER BY
，雪花层级会破坏聚簇效果；BigQuery 的分区裁剪也只识别一级 JOIN 后的过滤条件 如何写一个能下推到物化视图的
WHERE
条件 物化视图（如 Snowflake 的
MATERIALIZED VIEW
、BigQuery 的
materialized table
）不是万能缓存，查询能否命中取决于谓词是否可被引擎下推并匹配其预计算范围。常见失效原因是用了无法静态分析的表达式。 实操建议： 避免在
WHERE
中使用
DATE_TRUNC('day', CURRENT_DATE()) - INTERVAL 7 DAY
这类带函数嵌套的时间计算；改用
event_date >= '2024-06-01'
这种字面量 禁止对物化视图的列做函数包装后再比较，例如
UPPER(status) = 'ACTIVE'
不会命中基于原始
status
列构建的 MV；应确保查询列与 MV 定义列完全一致 Redshift 的
CREATE MATERIALIZED VIEW
要求
WHERE
子句必须是确定性布尔表达式，不支持子查询或 UDF 调用
INSERT OVERWRITE
在不同引擎里的语义差异 这个语法看着统一，实际行为天差地别：Spark SQL 是原子替换整个分区目录；Trino 对 Hive connector 是先删后插，无事务保障；而 BigQuery 的
INSERT OVERWRITE TABLE
本质是
TRUNCATE + INSERT
，会清空所有数据再写入——哪怕你只指定了
PARTITION
。 实操建议： 在 Spark 中用
INSERT OVERWRITE TABLE t PARTITION(ds='2024-06-01')
是安全的，它只删对应分区路径 Trino 下不要依赖
OVERWRITE
做增量更新，改用
DELETE + INSERT
配合
WHERE
控制范围 BigQuery 若需覆盖单日分区，请显式写成
INSERT OVERWRITE t PARTITION(ds) SELECT ... WHERE ds = '2024-06-01'
，否则可能误删全表 为什么
LEFT JOIN
后加
WHERE
过滤右表字段等于隐式转成
INNER JOIN
这是 SQL 执行顺序导致的典型陷阱：
ON
条件在连接阶段生效，
WHERE
在连接完成后再过滤整行。一旦你在
WHERE
里写了
dim_user.status = 'active'
，那些原本因右表无匹配而产生的
NULL
行就会被筛掉。 实操建议： 想保留左表全部记录，又只取右表满足某条件的数据，必须把条件移到
ON
子句：
LEFT JOIN dim_user ON fact.order_user_id = dim_user.id AND dim_user.status = 'active'
在 BigQuery 中，这种错误还会导致分区裁剪失效——因为
WHERE
条件被判定为“不可下推到 JOIN 右侧”，从而扫描全部分区 用
EXPLAIN
查看执行计划时，注意
Filter
步骤出现在
Join
之前还是之后，就能快速定位是否发生隐式转换 建模不是画图比赛，SQL 也不是写完就能跑通。最常出问题的地方，往往藏在 JOIN 条件和 WHERE 的边界上，以及你以为“语法一样”的跨平台命令背后。

相关文章