做数据仓库有几年了，从最开始的写SQL、做报表，到后来的数据建模、ETL开发、性能优化、数据治理，踩了不少坑，也积累了一些经验。今天分享一些数据仓库的进阶技巧，包括数据建模、ETL开发、性能优化、数据治理等方面，希望能帮到正在做数仓的朋友。

先说说背景。我是做数据开发的，主要负责公司的数据仓库建设。公司业务比较复杂，有十几个业务系统，每天产生大量的数据。我们的数据仓库，从最开始的几张表，到现在的几百张表，从最开始的简单报表，到现在的复杂指标体系，经历了很多次重构和优化。

在这个过程中，我踩了很多坑，也学到了很多东西。比如，数据建模怎么设计才合理，ETL怎么写才高效稳定，性能怎么优化才快，数据怎么治理才干净。这些东西，书上可能不会讲得太细，都是在实际项目中摸爬滚打总结出来的。

今天，我就把这些经验和技巧分享出来，希望能帮到正在做数仓的朋友，让大家少走弯路。

一、数据建模的技巧

数据建模，是数据仓库的基础。模型设计得好，后面的开发和维护都会很顺利；模型设计得不好，后面会有很多麻烦，甚至要重构。

1. 分层设计，不要把所有数据都放在一层

这是最基本的，但也是很多人容易忽视的。数据仓库一定要分层，常见的分层是：ODS（操作数据层）、DWD（明细数据层）、DWS（汇总数据层）、ADS（应用数据层）。每一层有不同的职责，不要混在一起。

  • ODS层：存放原始数据，和业务系统保持一致，不做太多处理，只是简单的清洗和标准化。
  • DWD层：存放明细数据，对ODS层的数据进行清洗、转换、维度退化，形成统一的明细数据。
  • DWS层：存放汇总数据，按主题对DWD层的数据进行汇总，形成公共的汇总指标。
  • ADS层：存放应用数据，面向具体的业务场景，提供给报表、看板、算法等使用。

分层的好处是：职责清晰，每一层只做自己的事情；便于维护，修改某一层不会影响其他层；便于复用，公共的数据放在中间层，可以被多个上层应用复用。

很多新手，容易把所有数据都放在一层，或者分层不清晰，ODS层和DWD层混在一起，DWS层和ADS层混在一起。这样，一开始可能觉得方便，但随着数据量越来越大，表越来越多，就会变得很乱，很难维护，最后只能重构。

所以，从一开始就要做好分层设计，严格按照分层的规范来建表。

2. 维度建模，优先考虑星型模型

数据仓库的建模方法，主要有两种：范式建模（ER模型）和维度建模（星型模型/雪花模型）。在数据仓库中，尤其是面向分析的场景，维度建模更常用，因为它更符合分析的思维，查询效率也更高。

维度建模的核心是：事实表 + 维度表。事实表存放业务过程的度量（比如订单金额、数量、时长等），维度表存放业务过程的上下文（比如时间、用户、商品、地点等）。事实表和维度表通过外键关联，形成星型模型。

星型模型的好处是：结构简单，容易理解；查询效率高，因为不需要太多的join；便于扩展，可以很方便地增加维度和度量。

雪花模型是星型模型的扩展，维度表还可以再关联其他维度表，形成雪花状。雪花模型可以减少数据冗余，但会增加join的数量，查询效率可能会降低。一般来说，优先用星型模型，除非维度表的冗余特别严重，才考虑雪花模型。

在设计事实表的时候，要注意：

  • 事实表的粒度要明确，是一行一个订单，还是一行一个订单商品，还是一行一个用户一天。粒度明确了，后面的开发才不会乱。
  • 事实表要尽量包含所有相关的度量，不要把度量分散在多个表中。
  • 事实表要尽量包含退化维度（比如订单号、订单状态等），减少维度表的数量，提高查询效率。

在设计维度表的时候，要注意：

  • 维度表要包含足够的属性，便于分析和筛选。
  • 维度表要处理缓慢变化维度（SCD），比如用户的等级会变化，商品的价格会变化，要记录历史变化。
  • 维度表要统一命名和编码，比如时间维度、用户维度、商品维度，在整个数仓中要统一。

3. 处理缓慢变化维度（SCD）

缓慢变化维度，是维度建模中一个很重要的问题。维度表中的属性，不是一成不变的，会随着时间变化，比如用户的等级、商品的分类、员工的部门等。怎么处理这些变化，就是缓慢变化维度的问题。

常见的SCD处理方式有三种：

  • SCD Type 1：直接覆盖，不保留历史。新值覆盖旧值，简单，但丢失了历史信息。适用于不需要保留历史的属性，比如用户的昵称、邮箱等。
  • SCD Type 2：增加新行，保留历史。当维度属性变化时，在维度表中增加一行新的记录，标记生效时间和失效时间，旧的记录标记为失效。这样可以保留完整的历史，适用于需要保留历史的属性，比如用户的等级、商品的分类等。
  • SCD Type 3：增加新列，保留有限历史。在维度表中增加新的列，存放旧值，只保留最近一次的变化。适用于只需要保留最近历史的场景。

在实际项目中，最常用的是SCD Type 2，因为它能保留完整的历史，便于分析历史变化。但SCD Type 2会增加维度表的行数，增加join的复杂度，需要在设计的时候考虑好。

处理SCD Type 2的时候，要注意：

  • 维度表要有代理键（surrogate key），不要用业务主键，因为业务主键可能会重复（同一业务主键有多条历史记录）。
  • 要有生效时间和失效时间，标记每条记录的有效期。
  • 要有当前记录的标记，比如is_current=1，表示当前有效的记录，便于查询。
  • 事实表关联维度表的时候，要根据业务时间和维度的有效期，关联到正确的维度记录，而不是只关联当前记录。

4. 一致性维度和一致性事实

在数据仓库中，可能有多个业务过程，多个事实表，多个维度表。这时候，要保证一致性维度和一致性事实。

一致性维度，是指同一个维度，在不同的事实表中，定义和编码是一致的。比如，时间维度，在订单事实表和用户行为事实表中，定义是一样的，日期格式、周的定义、月的定义，都是一致的。再比如，用户维度，在不同的事实表中，用户的ID、属性、编码，都是一致的。

一致性维度的好处是，不同的事实表可以共享同一个维度，便于跨业务过程的分析，比如分析订单和用户行为的关系。如果维度不一致，就很难关联起来，分析就会很困难。

一致性事实，是指同一个度量，在不同的事实表中，定义和计算方式是一致的。比如，订单金额，在订单事实表和销售汇总表中，定义是一样的，都是订单的总金额，计算方式一致。如果同一个度量，在不同的表中定义不一样，就会出现数据不一致的问题，报表出来的数字对不上，业务方会质疑数据的准确性。

所以，在建模的时候，要建立一致性维度和一致性事实，统一维度和度量的定义。可以建立一个公共的维度层，所有事实表都用公共的维度；建立指标管理体系，统一定义所有的度量和指标。

二、ETL开发的技巧

ETL（抽取、转换、加载），是数据仓库的核心环节。ETL写得好不好，直接影响数据的质量、效率和稳定性。

1. 幂等性设计，重跑不会出问题

这是ETL开发最重要的原则之一。ETL任务一定要保证幂等性，也就是说，同一个任务，跑一次和跑多次，结果是一样的，不会因为重跑而导致数据重复或者错误。

因为在实际项目中，ETL任务失败是很常见的事情，可能是因为源系统数据延迟，可能是因为网络问题，可能是因为代码bug。任务失败了，就需要重跑。如果任务不是幂等的，重跑就会导致数据重复，或者数据错误，还要手动清理数据，很麻烦。

保证幂等性的常见方法：

  • 按分区覆盖：每个任务处理一个分区（比如一天），跑的时候先删除这个分区的数据，再插入新的数据。这样，重跑的时候，旧的数据被删除了，新的数据插进去，不会重复。
  • 用merge/upsert：根据主键，有就更新，没有就插入。这样，重跑的时候，已有的数据会被更新，不会重复。
  • 事务保证：在一个事务中，先删除再插入，要么都成功，要么都失败，不会出现中间状态。

我一般用按分区覆盖的方式，因为最简单，也最高效。每个任务处理一天的数据，跑的时候先删除当天的分区，再插入新的数据。这样，不管跑多少次，结果都是一样的。

2. 增量抽取，不要每次都全量

对于数据量大的表，一定要用增量抽取，不要每次都全量抽取。全量抽取，每次都把所有数据抽一遍，效率低，对源系统的压力也大，而且随着数据量的增长，会越来越慢。

增量抽取的常见方式：

  • 按时间戳增量：源表有更新时间字段，每次只抽取上次抽取之后更新的数据。这是最常用的方式，简单高效。
  • 按自增ID增量：源表有自增主键，每次只抽取上次抽取之后新增的数据。适用于只新增不更新的表，比如日志表、行为表。
  • 全量对比：没有时间戳和自增ID的表，只能全量抽取，然后和目标表对比，找出新增和更新的数据。这种方式效率低，只适用于数据量小的表。

在做增量抽取的时候，要注意：

  • 要记录上次抽取的位置（时间戳或者ID），下次从这个位置开始抽。
  • 要考虑数据延迟，源系统的数据可能会有延迟，比如当天的数据，第二天才更新完。所以，抽取的时候，可以适当延迟，比如T+1抽取，或者抽取的时候多抽一段时间，再去重。
  • 要处理删除的数据，如果源表会删除数据，增量抽取可能捕捉不到删除，需要特殊处理，比如用软删除（标记删除状态），或者定期全量对比。

3. 数据清洗，在ODS层就做好

数据清洗，是ETL的重要环节。源系统的数据，往往有很多问题，比如缺失值、异常值、重复数据、格式不统一、编码不一致等。这些问题，要在ODS层就清洗好，不要带到后面的层。

常见的数据清洗操作：

  • 处理缺失值：缺失的字段，用默认值填充，或者标记为未知，不要留null。
  • 处理异常值：超出正常范围的值，比如年龄200岁，金额负数，要识别出来，要么修正，要么过滤掉，要么标记为异常。
  • 去重：重复的数据，要根据主键去重，保留最新的一条。
  • 格式统一：日期格式、时间格式、字符串编码、大小写等，要统一。
  • 编码统一：性别、状态、类型等枚举值，要统一编码，比如性别统一用0=未知，1=男，2=女，不要有的表用M/F，有的表用男/女。
  • 数据校验：对关键字段做校验，比如手机号格式、身份证格式、邮箱格式等，不符合的标记为异常。

数据清洗的时候，要注意：

  • 不要随意丢弃数据，异常数据要保留，标记为异常，便于后续排查。
  • 清洗规则要记录下来，形成文档，便于维护和排查问题。
  • 清洗后的数据要和源数据对比，检查数据量、关键字段的分布，确保清洗没有问题。

4. 任务依赖和调度，设计合理

数据仓库有很多ETL任务，任务之间有依赖关系，比如DWD层的任务依赖ODS层的任务，DWS层的任务依赖DWD层的任务。任务的依赖和调度，要设计合理，才能保证数据按时产出，不会出现依赖混乱的问题。

常见的调度工具，比如Airflow、DolphinScheduler、Azkaban等，都支持任务依赖和调度。在设计任务依赖的时候，要注意：

  • 依赖关系要清晰，上游任务成功了，下游任务才能跑。不要出现循环依赖。
  • 任务粒度要适中，不要太大（一个任务做很多事情，失败了重跑成本高），也不要太小（任务太多，管理复杂）。一般按表或者按主题来划分任务。
  • 要设置任务的优先级，重要的任务、上游的任务，优先级高一些，先跑。
  • 要设置任务的超时时间，防止任务卡住，影响下游任务。
  • 要设置失败重试，任务失败了，自动重试几次，防止因为临时问题（比如网络抖动）导致任务失败。
  • 要设置告警，任务失败了，或者数据延迟了，及时告警，通知开发人员排查。

在实际项目中，我一般按分层来设计任务依赖：ODS层的任务先跑，然后DWD层，然后DWS层，最后ADS层。同一层的任务，可以并行跑，提高效率。每个任务处理一张表，粒度适中，便于维护和重跑。

三、性能优化的技巧

数据仓库的数据量一般都很大，查询和计算的性能很重要。性能不好，报表跑不出来，业务方等不及，就会质疑数据团队的能力。所以，性能优化是数仓开发的重要技能。

1. 分区和分桶，大数据表的标配

对于大数据表，一定要分区和分桶，这是最基本的优化手段。

分区，就是把表按某个字段（一般是日期）分成多个分区，每个分区是一个目录。查询的时候，只扫描需要的分区，不需要扫描全表，大大提高查询效率。比如，按天分区的表，查询某一天的数据，只扫描那一天的分区，不需要扫描所有日期的数据。

分区的常见方式：

  • 按日期分区：最常用，比如按天、按周、按月分区。适用于时间序列的数据。
  • 按业务字段分区：比如按地区、按部门、按业务线分区。适用于经常按这些字段筛选的场景。

分区的时候，要注意：

  • 分区字段的选择，要选经常作为查询条件的字段，而且基数不要太大（比如按用户ID分区，分区数太多，反而会影响性能）。
  • 分区的粒度要适中，不要太粗（比如按年分区，每个分区数据量太大），也不要太细（比如按小时分区，分区数太多，管理复杂）。一般按天分区比较合适。
  • 不要在分区字段上做函数运算，比如where date(created_at) = '2020-01-01'，这样会导致分区裁剪失效，全表扫描。应该直接用分区字段过滤，比如where dt = '2020-01-01'。

分桶，就是把表按某个字段的哈希值，分成多个桶（文件）。分桶可以提高join和聚合的效率，因为相同key的数据会落在同一个桶里，join的时候不需要shuffle所有数据。

分桶的常见方式：

  • 按join的key分桶：经常join的两个表，按相同的key分相同数量的桶，join的时候可以直接在桶内join，不需要shuffle，效率很高。
  • 按聚合的key分桶：经常聚合的字段，按这个字段分桶，聚合的时候可以在桶内聚合，减少数据传输。

分桶的时候，要注意：

  • 分桶的数量要合适，一般和数据量有关，不要太多也不要太少。
  • 分桶表在插入数据的时候，要设置分桶的参数，确保数据正确分桶。
  • 分桶表的查询，最好带分桶字段的过滤，能进一步提高效率。

在实际项目中，大表一般都会既分区又分桶，按日期分区，按join key分桶，这样查询和join的效率都很高。

2. 列存储和压缩，节省空间提高效率

现在的大数据存储格式，比如Parquet、ORC，都是列存储的。列存储，就是按列来存储数据，而不是按行。列存储的好处是：

  • 查询的时候，只读取需要的列，不需要读取所有列，减少IO。
  • 同一列的数据类型相同，压缩率更高，节省存储空间。
  • 可以针对每列做优化，比如编码、索引等。

所以，在数据仓库中，尽量用列存储格式（Parquet或ORC），不要用行存储格式（比如CSV、JSON）。列存储格式，不仅节省存储空间，还能大大提高查询效率。

压缩，也是很重要的优化手段。数据压缩后，存储空间变小，IO量减少，查询效率提高。常见的压缩算法有Snappy、Gzip、Zstd等。

  • Snappy：压缩率一般，但压缩和解压速度快，CPU占用低，适用于大多数场景。
  • Gzip：压缩率高，但压缩和解压速度慢，CPU占用高，适用于冷数据，不常查询的数据。
  • Zstd：压缩率高，速度也快，是比较新的压缩算法，综合性能好，越来越流行。

在实际项目中，我一般用Parquet + Snappy，综合性能好，存储空间和查询效率都不错。对于冷数据，可以用Gzip或者Zstd，节省更多的存储空间。

3. SQL优化，写高效的SQL

SQL是数仓开发最常用的工具，SQL写得好不好，直接影响查询性能。很多时候，性能问题不是因为数据量大，而是因为SQL写得不好。

常见的SQL优化技巧：

  • 只查需要的列，不要用select 。select 会读取所有列，IO量大，效率低。只查需要的列，减少IO。
  • 尽早过滤，把过滤条件放在最前面，减少后续处理的数据量。比如，where条件尽量下推，在扫描数据的时候就过滤，不要等到聚合后再过滤。
  • 避免在索引/分区字段上用函数，比如where date(createdat) = '2020-01-01'，会导致索引/分区失效。应该直接用字段过滤，比如where createdat >= '2020-01-01' and created_at < '2020-01-02'。
  • 用union all代替union，union会去重，需要排序，效率低；union all不去重，效率高。如果不需要去重，就用union all。
  • 避免用distinct，distinct需要排序去重，效率低。如果可以用group by代替，或者用其他方式去重，就不要用distinct。
  • 大表join小表的时候，用map join（广播join），把小表广播到每个节点，在map端join，不需要shuffle，效率高。
  • 多表join的时候，注意join的顺序，把数据量小的表放在前面，先过滤，减少后续join的数据量。
  • 聚合的时候，尽量用分组聚合，避免用窗口函数做聚合，窗口函数效率低。
  • 避免嵌套子查询，尽量用join或者with子句（CTE）代替，子查询效率低，而且不容易优化。

这些SQL优化技巧，看起来都是小事，但组合起来，能带来很大的性能提升。在实际项目中，很多慢SQL，都是因为这些小问题导致的。

4. 数据倾斜，大数据的常见问题

数据倾斜，是大数据计算中最常见的性能问题之一。数据倾斜，就是某些key的数据量特别大，导致某个任务处理的数据量远大于其他任务，成为瓶颈，整个任务的进度被这个慢任务拖住。

数据倾斜的常见表现：任务进度卡在99%，大部分任务都完成了，只有少数几个任务一直在跑，跑很久都不完。

数据倾斜的常见原因和解决方法：

  • join的时候，某个key的数据量特别大：比如join的key中有大量null，或者某个热门key。解决方法：过滤掉null值，或者把null值单独处理；把热门key单独拿出来处理，最后再union回去。
  • group by的时候，某个key的数据量特别大：比如按某个维度分组，某个维度的值特别多。解决方法：开启map端聚合（combiner），先在map端做一次聚合，减少shuffle的数据量；或者用两阶段聚合，先按随机前缀分组聚合，再去掉前缀分组聚合。
  • count distinct的时候，某个key的distinct值特别多：解决方法：用近似算法（比如HyperLogLog）代替精确count distinct，或者用其他方式优化。

在实际项目中，数据倾斜是很常见的问题，遇到了不要慌，先找到倾斜的key，然后根据具体情况选择合适的解决方法。大部分数据倾斜问题，都能通过上面的方法解决。

四、数据治理的技巧

数据仓库建起来之后，数据治理就很重要了。如果不做数据治理，数据会越来越乱，质量越来越差，最后没人敢用。

1. 元数据管理，知道有什么数据

元数据，就是描述数据的数据，比如表的名称、字段、类型、注释、分区、负责人、血缘关系等。元数据管理，就是把这些信息管理起来，让大家知道数仓里有什么数据，数据在哪里，怎么用。

元数据管理的好处：

  • 便于数据发现，找数据的时候，不用到处问，直接在元数据系统里搜。
  • 便于理解数据，看表的注释、字段的注释，就知道数据是什么意思，怎么用。
  • 便于数据血缘，知道数据从哪里来，到哪里去，影响分析的时候，能快速找到上下游。
  • 便于维护，知道每个表的负责人是谁，有问题找谁。

常见的元数据管理工具，比如Apache Atlas、DataHub、Amundsen等。在实际项目中，至少要做到：

  • 所有表和字段都要有注释，说明数据的含义。
  • 记录每个表的负责人，有问题能找到人。
  • 记录表的血缘关系，知道数据的来源和去向。
  • 建立数据地图，让大家能搜索和浏览所有的数据。

2. 数据质量监控，保证数据准确

数据质量，是数据仓库的生命线。如果数据不准确，报表出来的数字不对，业务方就不会信任数据，数仓就失去了价值。所以，数据质量监控非常重要。

数据质量监控的常见维度：

  • 完整性：数据是否完整，有没有缺失，比如主键不为空，关键字段不为空。
  • 准确性：数据是否准确，有没有错误，比如金额非负，年龄在合理范围，枚举值在合法范围内。
  • 一致性：数据是否一致，不同表之间的同一个指标是否一致，比如订单表的总金额和支付表的总金额是否对得上。
  • 及时性：数据是否按时产出，有没有延迟，比如每天的报表是否在早上9点前产出。
  • 唯一性：数据是否有重复，比如主键唯一，没有重复记录。

数据质量监控的常见方法：

  • 规则校验：在ETL任务中，加入数据质量校验规则，比如非空校验、范围校验、枚举校验、唯一性校验等，不通过就告警或者阻断任务。
  • 数据对比：和源数据对比，和历史数据对比，检查数据量、指标的变化，发现异常。
  • 血缘分析：分析数据的血缘关系，找到数据质量问题的根源。
  • 数据质量报告：定期生成数据质量报告，展示数据质量的情况，督促改进。

在实际项目中，我一般会在ETL任务中加入数据质量校验，比如校验主键唯一、关键字段非空、枚举值合法、数据量在合理范围等。校验不通过的，及时告警，通知开发人员排查。重要的指标，还会做跨表对比，确保数据一致。

3. 数据生命周期管理，该删的删，该归档的归档

数据仓库的数据量会越来越大，如果不做生命周期管理，存储成本会越来越高，查询效率也会越来越低。所以，要做数据生命周期管理，该删的删，该归档的归档。

数据生命周期管理的常见策略：

  • 热数据：最近一段时间的数据，比如最近3个月，查询频率高，存在高性能存储上（比如SSD），保留详细数据。
  • 温数据：一段时间之前的数据，比如3个月到1年，查询频率一般，存在普通存储上，保留详细数据或者轻度汇总数据。
  • 冷数据：很久之前的数据，比如1年以上，查询频率低，归档到低成本存储上（比如对象存储、磁带），只保留汇总数据，或者压缩存储。
  • 过期数据：不需要保留的数据，比如临时表、中间表、测试数据，定期删除。

在实际项目中，我一般会：

  • ODS层和DWD层的明细数据，保留最近1年，1年以上的归档到冷存储。
  • DWS层和ADS层的汇总数据，保留3年，3年以上的归档。
  • 临时表和中间表，设置过期时间，自动删除。
  • 定期清理无用的表和分区，释放存储空间。

这样，既能满足业务的查询需求，又能控制存储成本，提高查询效率。

4. 指标管理，统一指标定义

在数据仓库中，指标是很重要的一部分。如果指标定义不统一，不同的报表对同一个指标的定义不一样，出来的数字不一样，业务方就会很困惑，质疑数据的准确性。所以，要做指标管理，统一指标的定义。

指标管理的内容：

  • 指标的名称和编码：统一命名，每个指标有唯一的编码。
  • 指标的定义和计算公式：明确指标的业务含义和计算方式，比如"订单金额"是订单的总金额，还是支付的总金额，是否包含退款，是否包含运费。
  • 指标的维度：指标可以按哪些维度分析，比如时间、地区、用户、商品等。
  • 指标的负责人：每个指标有负责人，有问题找谁。
  • 指标的血缘：指标的数据来源，从哪些表计算来的。

指标管理的常见工具，比如Apache Atlas、DataHub，或者专门的指标管理平台。在实际项目中，至少要做到：

  • 建立指标字典，所有指标都有明确的定义和计算公式。
  • 公共指标统一计算，放在DWS层，所有报表都用公共指标，不要各自计算。
  • 指标变更要走流程，通知所有使用方，避免影响。

这样，就能保证指标的一致性和准确性，避免"一个指标，多个数字"的问题。

五、写在最后

数据仓库，是一个很有挑战性的工作。它不仅仅是写SQL、做报表，还涉及数据建模、ETL开发、性能优化、数据治理等方方面面。要做好数据仓库，需要不断学习，不断实践，不断总结。

今天分享的这些技巧，都是我在实际项目中总结出来的，希望能帮到正在做数仓的朋友。当然，这些只是我的个人经验，不一定适用于所有场景，大家要根据自己的实际情况，灵活运用。

数据仓库的建设，是一个长期的过程，不是一蹴而就的。从最开始的几张表，到后来的几百张表，从最开始的简单报表，到后来的复杂指标体系，需要不断地迭代、优化、重构。在这个过程中，会遇到很多问题，踩很多坑，但只要坚持，不断学习，不断改进，就能把数据仓库建设好，为业务提供有价值的数据。

最后，想说的是，数据仓库的核心，不是技术，而是数据。技术只是手段，数据才是目的。我们做的所有工作，都是为了提供准确、及时、有价值的数据，帮助业务做决策。所以，不要只关注技术，更要关注数据的质量和价值。

希望我的这些分享，能对你有所帮助。如果有什么问题或者不同的看法，欢迎在评论区交流。