SQL 与 dbt 分析工程实践——面向分析的数据建模在今天这个 data-driven 的世界中，组织越来越依赖 da

在今天这个 data-driven 的世界中，组织越来越依赖 data analytics 来获得有价值的洞察，并做出 informed decisions。Data modeling 在这个过程中发挥着至关重要的作用，它为 structuring 和 organizing data 提供坚实基础，从而支持有效分析。此外，理解 data modeling 和 normalization 的概念，对于充分发挥 analytics 的潜力，并从复杂 datasets 中获得 actionable insights 至关重要。

Data modeling 关注的是定义系统中 data entities 的 structure、relationships 和 attributes。Data modeling 的一个关键方面是 data normalization。Data normalization 是一种消除 data redundancy 并提升 data integrity 的技术。它会将 data 拆分为 logical units，并组织到独立 tables 中，从而减少 data duplication，并提升整体 database efficiency。Normalization 确保 data 以 structured 和 consistent 的方式存储，这对于 accurate analysis 和 reliable results 至关重要。

在 analytics 方面，data modeling 为创建 analytical models 提供坚实基础。通过理解 entities 和 data structures 之间的 relationships，analysts 可以设计有效 models，捕获 relevant information，并支持期望的 analytics objectives。换句话说，一个设计良好的 data model 使 analysts 能够执行 complex queries、join tables，并 aggregate data，从而生成 meaningful insights。

理解 data modeling 和 normalization 对 practical data analysis 非常关键。如果缺乏合适的 data model，analysts 可能难以访问并正确解释 data，从而导致错误结论和无效决策。此外，缺少 normalization 可能导致 data anomalies、inconsistencies，以及聚合 data 的困难，从而阻碍 analysis process。

本书强调 SQL 和 dbt 是支撑有效 analytics engineering project 的两项核心技术，这同样适用于设计和实现有效 data model。原因在于，SQL 通过强大的 query capabilities，使 users 能够定义 tables、操作 data，并检索 information。它无与伦比的 flexibility 和 versatility，使其成为构建和维护 data models 的强大工具，让 users 能够表达复杂 relationships，并轻松访问特定 data subsets。

作为 SQL 的补充，dbt 在这个叙事中发挥核心作用，将 data modeling 的艺术提升到全新层级。它是一个综合 framework，用于构建和编排 complex data pipelines。在这个 framework 内，users 可以定义 transformation logic，应用必要 business rules，并创建可复用的 modular code components，也就是 models。值得注意的是，dbt 不只是具备 standalone functionality：它可以与 version control systems 无缝集成，使 collaboration 变得轻松，并确保 data models 保持 consistency、auditability 和 effortless reproducibility。

SQL 和 dbt 在 data modeling 中的另一个关键方面，是它们都强调 testing 和 documentation，尽管二者之间有一些值得澄清的区别。在 data modeling context 中，testing 涉及验证 data model 的 accuracy、reliability，以及是否遵守 business rules。需要注意的是，dbt 的 testing capabilities 与 software development 中传统 unit testing 不同，但它们服务于类似目的。dbt 不是提供传统 unit tests，而是提供 validation queries，这与 analysts 习惯运行的检查非常相似。这些 validation queries 会检查 data quality、data integrity，以及对 defined rules 的遵守情况，从而增强对 model outputs 的信心。此外，dbt 在 documentation 方面表现突出，它为 analysts 和 stakeholders 提供有价值的资源。这些 documentation 简化了对支撑 data model 的 underlying logic 和 assumptions 的理解，从而增强 transparency，并促进有效 collaboration。

SQL 和 dbt 共同赋能 data professionals 创建 robust、scalable 和 maintainable data models，从而推动 insightful analytics 和 informed decision making。通过利用这些工具，组织能够释放 data 的全部潜力，推动 innovation，并在今天 data-driven 的格局中获得 competitive advantage。在同一个 data architecture 和 strategy 中结合二者，会为 data modeling 带来显著优势。

A Brief on Data Modeling

在 database design 的世界中，创建 structured 和 organized environment，对于有效存储、操作和利用 data 至关重要。Database modeling 在实现这一目标中发挥重要作用，它为表示某个特定 reality 或 business，并支持其 processes 和 rules，提供 blueprint。

然而，在深入创建这个 blueprint 之前，我们应先专注于理解 business 的细微差别。理解 business operations、terminology 和 processes，对于创建 accurate 且 meaningful 的 data models 至关重要。通过 interviews、document analysis 和 process studies 收集 requirements，我们可以获得关于 business needs 和 data requirements 的洞察。在这个收集过程中，我们应专注于 natural communication，也就是 written language。通过用无歧义句子表达 business facts，我们可以确保对 business 的 representation 准确且不依赖 interpretation。将复杂句子拆解为带有 subjects、verbs 和 direct objects 的简单结构，有助于简洁捕获 business realities。

除了这些基本实践，还值得注意的是，领域专家 Lawrence Corr 在其流行著作 Agile Data Warehouse Design（DecisionOne Press）中提倡在 data model design 初始阶段进一步使用 whiteboarding 和 canvassing 等技术。这些额外策略可以为过程增添细微度，使 business requirements 的探索更加全面，并确保最终 data models 与 business objectives 和 intricacies 无缝对齐。

一旦 understanding phase 完成，我们就进入 database modeling 的三个基本步骤：

Conceptual phase
Logical phase
Physical phase

这些步骤共同构成创建 robust 且 well-organized database structure 的旅程。让我们用 book publisher 的例子来说明这个过程。

The Conceptual Phase of Modeling

Database modeling 的 conceptual phase 需要几个关键步骤。首先，需要识别 database 的 purpose 和 goals，并明确它需要解决的具体 problems 或 requirements。下一步是通过采访 stakeholders 和 subject matter experts 收集 requirements，以全面理解所需 data elements、relationships 和 constraints。随后是 entity analysis 和 definition，这涉及识别 database 中要表示的 key objects 或 concepts，并定义它们的 attributes 和 relationships。

在为 database 外观设计初始草图时，我们从轻量 normalization 开始。这可以确保已识别 entities 与 relationships 之间的 integrity，并通过围绕 conceptually related 的 semantic structures 组织 entities 和 attributes，将 redundancy 最小化。识别 keys，包括 primary keys 和 foreign keys，对于维护 uniqueness 并建立 tables 之间的 relationships 至关重要。

这些 database designs 通常通过 diagrams、textual descriptions 或其他方法创建，用于捕获并有效传达 database 的 design 和 concepts。最常用于视觉化表示 database concepts 的工具之一是 entity-relationship diagram（ERD）。使用 ERD 创建的 visual models 是一种 diagrammatic representation，可以有效描述要建模的 entities、它们的 relationships，以及这些 relationships 的 cardinality。通过使用 ERD model，我们可以视觉化描述 database structure，包括作为主要 components 的 entities、entities 之间的 connections 或 associations，以及 relationships 的数量或范围。

让我们做一个非常简单的 database conceptual design。假设 O’Reilly 想跟踪已经出版的 books 和 authors，以及尚未出版的新书 launch dates。我们与 publisher managers 进行一系列 interviews，开始准确理解 database 中需要存储哪些 data。主要目标是识别涉及的 entities、它们的 relationships，以及每个 entity 的 attributes。请记住，这个练习是说明性的，并且有意简化。我们在 book management 的这个子 universe 中识别出三个 distinct entities：

Book

该 entity 表示 O’Reilly 出版的一本书。Attributes 可能包括 book_id、title、publication_date、ISBN、price，以及某个特定 category。Interviewers 表示，在这个 model 中，一本 book 只能有一个 category。

Author

该 entity 表示为 O’Reilly 写书的 author。Attributes 可能包括 author_id、author_name、email 和 bio。

Category

该 entity 表示 book category，可以包含 category_id 作为 unique identifier，以及 category_name 等 attributes。

下一步是识别 entities 之间的 relationships。在 database design 中，entities 之间可以存在几种 relationship types，而 relationship 的类型可以称为该 relationship 的 cardinality。例如，在 one-to-one relationship 中，可以有一个 Book entity 连接到一个 Author entity，其中每本书与单个作者关联，反之亦然。在 one-to-many relationship 中，可以考虑一个 Category entity 连接到 Book entity，其中每本书只能属于一个 category，但每个 category 可以有多本书。相反，在 many-to-one relationship 中，可以想象 Publisher entity 连接到 Book entity，其中同一个 publisher 出版多本 books。最后，在 many-to-many relationship 中，可以有一个 Book entity 与 Reader entity 关联，表示多个 readers 可以拥有多本 books。继续我们的练习，我们也识别出两个清晰 relationships：

Book-Category relationship

建立 books 与 categories 之间的连接。一本 book 可以有一个 category，一个 category 可以有多本 books。该 relationship 表示为 one-to-many relationship。

Book-Author relationship

建立 books 与 authors 之间的连接。一本 book 可以有多个 authors，一个 author 也可以写多本 books。该 relationship 表示为 many-to-many relationship。正是在这个 relationship 中，某本具体 book 的 publication 会发生。

在识别 relationships 时，常见做法是使用代表 entities 之间真实 interaction 的 relationship names。例如，不叫 Book-Category，而可以叫 Classifies，因为 category classifies book；或者不叫 Book-Author，而叫 Publishes，因为 author has books published。

现在我们已经有了 entities、attributes 和 relationships 的想法，就具备了使用 ERD 设计 database 所需的内容。通过这样做，我们可以视觉化表示 entities、relationships 和 cardinality，如图 2-1 所示。

图 2-1：books database 的 ERD 示例

可以看到，entities 被表示为白色 rectangular boxes，代表 real-world objects 或 concepts，例如 Book 或 Author。Relationships 被表示为 diamonds，用于说明 entities 如何相关。

Attributes 被表示为 shaded boxes，用于描述 entity 的 properties 或 characteristics。例如 Name 或 Publish date。此外，attributes 可以分类为 key attributes，即带下划线的 shaded boxes，用于唯一标识 entity；也可以分类为 nonkey attributes，即不带下划线的 shaded boxes，用于提供关于 entity 的附加信息。在设计这类 diagrams 时还有更多 attribute 类型，但这里我们只使用基础内容。

ERD 中的其他 components 包括 cardinality 和 participation constraints。Cardinality 定义 relationship 中 instances 的数量，通常用 1、M 或 N 等符号表示 one-to-one 或 one-to-many relationship。（N 表示存在未确定数量的 relationships。）

The Logical Phase of Modeling

在 modeling 的 logical phase 中，重点是对 data 进行 normalization，以消除 redundancies、提升 data integrity，并优化 query performance。结果是一个 normalized logical model，能够准确反映 entities 之间的 relationships 和 dependencies。

这个阶段可以分为两个步骤。第一步是重构 Entity-Relationship schema，重点是基于特定 criteria 优化 schema。这个步骤不绑定到任何特定 logical model。第二步是将优化后的 ERD 转换为某个具体 logical model。

假设我们已经决定将 ERD 映射到 relational database model，这也是我们的案例，而不是 document 或 graph database，那么 conceptual ERD 练习中的每个 entity 都表示为一个 table。每个 entity 的 attributes 变成相应 table 的 columns。Primary-key constraint 会为每个 table 的 primary-key columns 指明。此外，many-to-many relationships 会通过单独的 junction tables 表示，这些 tables 保存引用相应 entities 的 foreign keys。

通过使用 relational model 将 conceptual ERD 练习转换为 logical schema，我们建立了 entities、attributes 和 relationships 的结构化表示。这个 logical schema 可以作为在特定 database management system（DBMS）中实现 database 的基础，同时保持独立于任何特定系统。为了有效完成这种转换，所有 normalization steps 都适用，但这里想分享一个有效 algorithm：

Entity E 被转换为 table T。
E 的名称成为 T 的名称。
E 的 primary key 成为 T 的 primary key。
E 的 simple attributes 成为 T 的 simple attributes。

对于 relationships，我们也可以分享几个步骤：

N:1 relationships

在 table T1 中定义一个 foreign key，用于引用 table T2 的 primary key。这建立了两个 tables 之间的连接，表示 N:1 relationship。与 relationship 相关的 attributes（Attrs）会被映射并包含在 table T1 中。

N:N relationships

创建一个特定 cross-reference table 来表示 relationship REL。REL 的 primary key 被定义为 tables T1 和 T2 的 primary keys 的组合，它们在 cross-reference table 中充当 foreign keys。与 relationship 相关的 attributes（Attrs）会被映射并包含在 cross-reference table 中。

现在让我们将这些规则应用到之前的 conceptual model；见图 2-2。

图 2-2：books database 的 logical ERD 示例

在我们的例子中，有几个 entities 按照 algorithm 建议被直接映射为 tables。Authors、Books 和 Category 都属于这种情况。

我们识别出 Books 和 Category 之间有一个 1:N relationship，其中一本 book 有一个 category，而一个 category 有多本 books。为了映射这种 relationship，我们在 books table 中创建一个 foreign key，引用相应 category。

我们还有一个 N:N relationship。在这种情况下，必须创建一个新 table，也就是 cross-reference table，来存储 relationship。在我们的案例中，我们创建 Publishes table，其 primary key 变成 related entities，即 Book ID 和 Author ID 的 composite。同时，relationship 的 attributes 也成为这个 cross-reference table 的 attributes。

The Physical Phase of Modeling

现在我们已经准备好将 normalized logical model 转换成 physical database design，这一步称为 physical phase，或 physical model creation。该步骤定义 storage structures、indexing strategies 和 data types，以确保高效 data storage 和 retrieval。Logical model 关注 conceptual representation，而 physical model 则处理 smooth data management 所需的 implementation details。

在我们的案例中，继续基于之前的 logical model，并假设我们使用 MySQL database engine。Example 2-1 展示了 books database 的 physical model。

Example 2-1：physical model 中的 books database

CREATE TABLE category (
  category_id INT PRIMARY KEY,
  category_name VARCHAR(255)
);

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  ISBN VARCHAR(13),
  title VARCHAR(50),
  summary VARCHAR(255)
  FOREIGN KEY (category_id) REFERENCES category(category_id),
);

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(255),
  date_birth DATETIME
);

CREATE TABLE publishes (
  book_id INT,
  author_id INT,
  publish_date DATE,
  planned_publish_date DATE
  FOREIGN KEY (book_id) REFERENCES books(book_id),
  FOREIGN KEY (author_id) REFERENCES author(author_id)
);

在 Example 2-1 中，我们创建了四个 tables：category、books、authors 和 publishes。Physical design aspect 会微调 table structures、data types 和 constraints，使其与 MySQL database system 对齐。

例如，在 category table 中，我们可以将 category_id column 的 data type 指定为 INT，确保它适合存储 integer values，同时将其定义为 primary key，因为它用于标识 table 中的 unique records。类似地，category_name column 可以被定义为 VARCHAR(255)，以容纳 variable-length category names。

在 books table 中，可以为 book_id（INT）、ISBN（VARCHAR(13)）、title（VARCHAR(50）和 summary（VARCHAR(255)）等 columns 分配适当 data types 和 lengths。此外，category_id column 可以配置为 foreign key，用于引用 category table 中的 category_id column。注意，每个 ISBN code 都由 13-character-length strings 组成。因此，我们不需要比这更大的 strings。

类似地，在 authors table 中，可以为 author_id（INT）、author_name（VARCHAR(255)）和 date_birth（DATETIME）等 columns 定义 data types，并且都要尊重 expected domain of values。

在 publishes table 中，我们强调定义了 foreign-key constraints，用于建立 books table 中的 book_id column 和 authors table 中的 author_id column 之间的 relationships。同时，foreign key 由它关联的两个 tables 的 primary keys 组成。

完成所有这些步骤后，我们已经成功从 requirements 走到 concept，再到 logical relational model，最后完成 MySQL 中 model 的 practical implementation，从而构建出 database。

The Data Normalization Process

Data normalization technique 包含多个步骤，每个步骤都旨在将 data 组织为 logical 和 efficient structures。Example 2-2 展示了包含几个相关 attributes 的 books table。

Example 2-2：待 normalization 的 books table

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    publication_year INT,
    genre VARCHAR(50)
);

Normalization 的第一步称为 first normal form（1NF），要求通过将 data 拆分为更小 atomic units 来消除 repeating groups。我们将创建一个名为 authors 的 table，其中包含 author ID 和 author name。现在，books table 引用 author ID，而不是重复存储 full name，如 Example 2-3 所示。

Example 2-3：1NF 中的 books table

-- Table Authors
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(100)
);

-- Table Books
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    publication_year INT,
    genre VARCHAR(50),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

进入 second normal form（2NF）时，我们检查 data 内部的 dependencies。我们观察到 publication year functionally depends on book ID，而 genre depends on author ID。为了遵循 2NF，我们将 books table 拆成三个 tables：

books，包含 book ID 和 title
authors，包含 author ID 和 name
bookDetails，存储 book ID、publication year 和 genre

这确保每个 column 仅依赖 primary key，如 Example 2-4 所示。

Example 2-4：2NF 中的 books table

-- Table Authors
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(100)
);

-- Table Books
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
);

-- Table book details
CREATE TABLE bookDetails (
    book_id INT PRIMARY KEY,
    author_id INT,
    genre VARCHAR(50),
    publication_year INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

Third normal form（3NF）关注消除 transitive dependencies。我们意识到 genre 可以通过 bookDetails table 从 book ID 推导出来。为了解决这一点，我们创建一个名为 genres 的新 table，其中包含 genre ID 和 genre name，而 bookDetails table 现在引用 genre ID，而不是直接存储 genre name（Example 2-5）。

Example 2-5：3NF 中的 books table

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(100)
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
);

CREATE TABLE genres (
    genre_id INT PRIMARY KEY,
    genre_name VARCHAR(50)
);

CREATE TABLE bookDetails (
    book_id INT PRIMARY KEY,
    author_id INT,
    genre_id INT,
    publication_year INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

这些 resulting normalized structures（3NF）通常用于 operational systems，也称为 online transaction processing systems（OLTP）。这类系统设计用于高效处理和存储 transactions，并检索 transaction data，例如 customer orders、bank transactions，甚至 payroll。需要强调的是，如有必要，我们可以进一步应用 normalization steps，例如 fourth normal form（4NF）和 fifth normal form（5NF），以处理复杂 data dependencies，并确保更高 levels of data integrity。

Data normalization 对 OLTP system 中 individual transactions 的高效处理和存储至关重要。在这个过程中，data 被划分为更小、更少 redundancy 的部分，以实现这一目标，并为 OLTP systems 带来多项优势。Data normalization 以强调减少 data redundancy 和提升 data integrity 而闻名，因为 data 被组织到多个 tables 中，每个 table 服务于特定目的。这些 tables 通过 primary 和 foreign keys 连接，以建立 relationships，确保每个 table 中的 records 唯一，并确保同一个 field 不会在多个 tables 中重复，除了 key fields 或 system fields，例如 ID 或 creation timestamps。

Data normalization 相关的另一个原因是，它会增强并最大化 performance。这些 normalized databases 被设计为通过最小化 data redundancy，并在 tables 之间建立 well-defined relationships，高效处理 fast reads 和 writes，使 database 能够以极快 performance 处理大量 transactions。这对 transactional systems 很重要，因为在这类系统中，operations 的及时执行至关重要。

最后但同样重要的是，normalized database 关注只存储 current data，使 database 表示当前可用的最新 information。在存储 customer information 的 table 中，每条 record 始终反映 customer 的最新 details，例如 first name、phone number 和其他 relevant data，从而确保 database 准确表示 current state of affairs。

然而，当涉及 analytics project 或 system 时，paradigm 有些不同。通常，users 希望能够获取所需 data，而不必进行大量 linking，而大量 linking 正是 normalization process 的自然结果。OLTP system 针对 write operations 优化，以避免 web application 等 live systems 中 increased latency；而 analytics systems 的 users 希望 read optimization，以便尽快获得 analytics data。不同于存储 live data 的 normalized transactional databases，analytics databases 预期包含 real-time 和 non-real-time data，并充当 past data 的 historical archive。此外，analytics database 往往预期包含来自多个 OLTP systems 的 data，以提供 business processes 的 integrated view。

这些差异确实非常关键，因为它们构成了 data organization、retention 和 utilization 的不同 requirements 的基础。然而，需要澄清的是，我们刚刚探讨的内容主要属于 normalization for performance optimization 和遵守 OLTP database design best practices 的领域。虽然这一基础很有价值，但它只是 analytics engineering 更广阔领域中的一个侧面。

为了提供更清晰的路线图，我们先明确：我们的旅程从探索这种 foundational type of data modeling 开始，它构成 OLTP systems 的基础。随后，我们将转向讨论针对 OLAP environments 优化的 data modeling approaches。通过做出这种区分，我们旨在全面理解 data modeling 的两个方面，为后续章节深入讨论 analytics engineering methodologies 及其应用做好铺垫。

Dimensional Data Modeling

Data modeling 是设计和组织 databases 以高效存储和管理 data 的基础方面。正如前面讨论的，它涉及定义系统中 data entities 的 structure、relationships 和 attributes。

一种流行的 data modeling 方法是 dimensional modeling，它聚焦于建模 data 以支持 analytics 和 reporting requirements。Dimensional modeling 尤其适合 data warehousing 和 BI applications。它强调创建 dimensional models，这些 models 由表示 measurable data 的 fact tables 和提供 descriptive context 的 dimension tables 组成。通过使用 star schemas 和 snowflake schemas 等 dimensional modeling techniques，可以以简化 complex queries 并支持高效 data analysis 的方式组织 data。

Data modeling 与 dimensional modeling 之间的关系在于二者互补。Data modeling 为捕获和结构化 data 提供基础，而 dimensional modeling 则提供一种专门技术，用于建模 data，以支持 analytical 和 reporting needs。二者结合，使组织能够设计 robust 和 flexible databases，既促进 transactional processing，也支持深入 data analysis。

要理解 dimensional modeling，我们首先应向两位被认为是 data warehousing 和 dimensional modeling 之父的人致敬：Bill Inmon 和 Ralph Kimball。他们被认为是 enterprise-wide information gathering、management 和用于 decision support 的 analytics 领域的 pioneers。

他们对 data warehousing 这一主题做出了重要争论，二人分别倡导不同 philosophy 和 approach。Inmon 主张创建一个 centralized data warehouse，覆盖整个 enterprise，目标是生成 comprehensive BI system。另一方面，Kimball 建议创建多个更小的 data marts，聚焦特定 departments，支持 department-level analysis 和 reporting。他们不同的观点带来了 data warehousing 在 design techniques 和 implementation strategies 上的对比。

除了 approaches 不同，Inmon 和 Kimball 在 data warehousing context 中对 data structure 的方法也不同。Inmon 主张在 enterprise data warehouse 中使用 relational（ERD）model，尤其是 third normal form（3NF）。相反，Kimball 的方法在 dimensional data warehouse 中使用 multidimensional model，采用 star schemas 和 snowflakes。

Inmon 认为，以 relational model 组织 data 可以确保 enterprise-wide consistency。这种 consistency 使得在 dimensional model 中创建 data marts 相对容易。另一方面，Kimball 认为，以 dimensional model 组织 data 可以促进 information bus，使 users 更有效地理解、分析、聚合和探索 data inconsistencies。此外，Kimball 的 approach 使 analytics systems 可以直接访问 data。相比之下，Inmon 的 approach 限制 analytics systems 不能仅从 enterprise data warehouse 访问 data，而需要与 data marts 交互来 retrieval。

TIP

Data mart 是 data warehouse 的特定部分，用于满足某个特定 department 或 business unit 的独特需求。

在接下来的章节中，我们将深入讨论三种 modeling techniques：star schema、snowflake modeling，以及新兴的 Data Vault。Data Vault 由 Dan Linstedt 于 2000 年提出，近年来势头不断增强。它遵循一种更 normalized 的 structure，虽然并不完全与 Inmon 的方法一致，但比较相似。

Modeling with the Star Schema

Star schema 是 relational data warehouses 中广泛使用的 modeling approach，尤其适用于 analysis 和 reporting purposes。它涉及将 tables 分类为 dimension tables 或 fact tables，从而有效组织和表示 business units 以及相关 observations 或 events。

Dimension tables 用于描述要建模的 business entities。这些 entities 可以包括 products、people、places 和 concepts 等各种方面，也包括 time。在 star schema 中，你通常会找到 date dimension table，它为 analysis 提供一套完整 dates。Dimension table 通常由一个或多个 key columns 组成，用作每个 entity 的 unique identifiers，同时还包括 additional descriptive columns，用于提供关于 entities 的进一步 information。

Fact tables 则存储 business 中发生的 observations 或 events。这包括 sales orders、inventory levels、exchange rates、temperatures 和其他 measurable data。Fact table 包含 dimension key columns，这些 columns 引用 dimension tables，以及 numeric measurement columns。Dimension key columns 决定 fact table 的 dimensionality，并指定 analysis 中包含哪些 dimensions。例如，存储 sales targets 的 fact table 可能包含 Date 和 ProductKey 的 dimension key columns，表示该 analysis 包括与 time 和 products 相关的 dimensions。

Fact table 的 granularity 由其 dimension key columns 中的 values 决定。例如，如果 sales target fact table 中的 Date column 存储表示每个月第一天的 values，那么该 table 的 granularity 就是 Month/Product level。这意味着 fact table 捕获的是 monthly level、针对每个 product 的 sales target data。

通过使用 dimension tables 表示 business units，并使用 fact tables 捕获 observations 或 events，在 star schema 中 structuring data，companies 可以高效执行 complex analysis 并获得 meaningful insights。Star schema 为 querying 和 aggregating data 提供清晰直观的结构，使分析和理解 dataset 中 dimensions 与 facts 之间的 relationships 更加容易。

回到我们的 books table，我们将按照 modeling steps 开发一个简单的 star schema model。第一步是识别 dimension tables。但首先，让我们回忆 Example 2-6 中的 base table。

Example 2-6：star schema 的 base table

-- This is our base table
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    publication_year INT,
    genre VARCHAR(50)
);

我们应该识别 books table 中所有 individual dimensions，也就是与特定 business entity 相关的 attributes，并为每个 dimension 创建单独的 dimension tables。在我们的示例中，与 normalization steps 类似，我们识别出三个 entities：books、authors 和 genres。让我们在 Example 2-7 中看看 physical model。

Example 2-7：star schema 的 dimension tables

-- Create the dimension tables
CREATE TABLE dimBooks (
    book_id INT PRIMARY KEY,
    title VARCHAR(100)
);

CREATE TABLE dimAuthors (
    author_id INT PRIMARY KEY,
    author VARCHAR(100)
);

CREATE TABLE dimGenres (
    genre_id INT PRIMARY KEY,
    genre VARCHAR(50)
);

在命名 dimension tables 时，建议使用 descriptive 和 intuitive names，反映它们所代表的 entities。例如，如果有一个表示 books 的 dimension table，可以将其命名为 dimBook 或简单地叫 books。类似地，表示 authors、genres 或其他 entities 的 dimension tables，可以使用 dimAuthor 或 dimGenre 等相关且自解释的名称。

对于 fact tables，建议使用能够表明 captured measurements 或 events 的名称。例如，如果有一个记录 book sales 的 fact table，可以命名为 factBookSales 或 salesFact。这些名称表明该 table 包含与 book sales 相关的数据。

现在，我们可以创建一个名为 factBookPublish 的 fact table，如 Example 2-8 所示，用于捕获 publication data。

Example 2-8：star schema 的 fact table

-- Create the fact table
CREATE TABLE factBookPublish (
    book_id INT,
    author_id INT,
    genre_id INT,
    publication_year INT,
    FOREIGN KEY (book_id) REFERENCES dimBooks (book_id),
    FOREIGN KEY (author_id) REFERENCES dimAuthors (author_id),
    FOREIGN KEY (genre_id) REFERENCES dimGenres (genre_id)
);

这段代码创建了一个新的 fact table factBookPublish，其 columns 表示与 dimensions 相关的 measurements 或 events。在这个案例中，只有 publication year。Foreign-key constraints 建立了 fact table 和 dimension tables 之间的 relationships。

有了表示 books dataset 的 star schema model，我们现在拥有了执行各种 analytical operations 并提取 valuable insights 的坚实基础。Star schema 的 dimensional structure 支持高效且直观的 querying，使我们能够从不同 perspectives 探索 data。一旦完成 modeling process，最终应得到类似图 2-3 的 model，它看起来像一颗星，因此得名 star schema。

图 2-3：Star schema model

使用这个 model，我们现在可以通过应用 genre、author 或 publication year 等 filters，轻松分析 book publications。例如，我们可以快速检索某个 specific genre 的 total publications。通过 join dimension tables 和 fact table，如 Example 2-9 所示，我们可以轻松获得 books、authors、genres 和 sales 之间 relationships 的 insights。

Example 2-9：从 star schema 中检索 data

-- Example for retrieving the total publications for a specific genre.
SELECT COALESCE(dg.genre, 'Not Available'), -- Or '-1'
 		COUNT(*) AS total_publications
FROM factBookPublish bp
LEFT JOIN dimGenres dg ON dg.genre_id = bp.genre_id
GROUP BY g.genre;

可以看到，在 join fact table 和 dimension table 时，我们使用了 LEFT JOIN。这是很常见的做法。它确保 fact table 中的所有 records 都包含在结果中，无论 dimension table 中是否存在 matching record。这一点很重要，因为它承认并非每条 fact record 都必然在每个 dimension 中有对应 entry。

通过使用 LEFT JOIN，你可以保留 fact table 中的所有 data，同时用 dimension table 中的 relevant attributes enrich 它。这使你能够基于各种 dimensions 进行 analysis 和 aggregations，并从不同 perspectives 探索 data。不过，我们必须处理任何 missing correspondence。因此，我们使用 COALESCE operator，它通常用于设置 default value，例如 -1 或 Not available。

LEFT JOIN 也支持 incremental dimension updates。如果 dimension table 中添加了新 records，LEFT JOIN 仍会包含已有 fact records，并将其与可用的 dimension data 关联起来。这种 flexibility 确保即使 dimension data 随时间演进，analysis 和 reporting 也保持一致。

总体而言，star schema 的 simplicity 和 denormalized structure 有利于 aggregations 和 summarizations。你可以生成各种 reports，例如 sales trends over time、best-selling genres，或 revenue by author。此外，star schema 支持 drill-down 和 roll-up operations，使你能够深入到更详细 information，或上卷到更高 aggregation levels，以获得 data 的 comprehensive view。

这种 modeling technique 也可以与 data visualization tools 和 BI platforms 无缝集成。通过将 model 连接到 Tableau、Power BI 或 Looker 等工具，你可以创建视觉上吸引人的 dashboards 和 interactive reports。这些资源使 stakeholders 能够快速理解 insights，并一眼做出 data-driven decisions。

不过，需要注意的是，前面的例子并没有充分突出 star schemas 所倡导的 denormalization aspect。例如，如果你的 dataset 严格遵守 one-genre-per-book scenario，那么你可以通过将 genre information 直接合并到统一的 dimBooks table 中，进一步简化 model，推动 denormalization 并简化 data access。

Modeling with the Snowflake Schema

在 snowflake schema 中，data model 比 star schema 更 normalized。它通过将 dimension tables 拆分成多个连续 tables，包含额外的 normalization levels。这可以提升 data integrity，并减少 data redundancy。例如，考虑一个 ecommerce database 的 snowflake schema。我们有一个 dimension table customers，其中包含 customer information，例如 ID、name 和 address。在 snowflake schema 中，可以将这个 table 拆分为几个连续 tables。

customers table 可以拆分为一个 customers table 和一个独立的 addresses table。customers table 包含 customer-specific attributes，例如 ID 和 customer name。相比之下，addresses table 包含 address-related information，例如 ID、customer 的 street、city 和 zip code。如果多个 customers 拥有相同 address，我们只需要在 addresses table 中存储一次 address information，并将其链接到相应 customers。

为了从 snowflake schema 检索 data，我们通常需要对相关 tables 执行多次 joins，以获得所需 information。例如，如果我们想查询 customer name 和 address，必须基于 ID page 将 customers table 与 addresses table join。虽然 snowflake schema 提供更好的 data integrity，但由于增加了 links，它也需要更复杂的 queries。然而，该 schema 对 large datasets 和 complex relationships 可能有好处，因为它在 data management 中提供更好的 normalization 和 flexibility。

Star schema 和 snowflake schema 是两种常见 data warehouse schema designs。在 star schema 中，dimension tables 是 denormalized 的，意味着它们包含 redundant data。Star schema 的优势包括更易访问的 design 和 implementation，以及由于 JOIN operations 更少而带来的更高 querying efficiency。然而，由于 denormalized data，它可能需要更多 storage space，并且更新和 troubleshooting 可能更具挑战。

这也是我们经常看到 hybrid models 的原因之一。在这些模型中，companies 建模 star schemas，并经常为不同 optimization strategies normalized 少数 dimensions。选择高度依赖你的 unique needs 和 requirements。如果你优先考虑 data warehouse solution 中的 simplicity 和 efficiency，那么 star schema 可能是理想选择。这种 schema 易于实现并且 querying 高效，适合 straightforward data analysis tasks。然而，如果你预期 data requirements 经常变化，并需要更高 flexibility，那么 snowflake schema 可能更好，因为它更容易适应不断演进的 data structures。

想象我们有一个 dimension，表示全球范围内 specific customers 的 location。在 star schema 中建模它的一种方式，是创建一个包含所有 location hierarchies denormalized 的单一 dimension table。Example 2-10 展示了 star schema paradigm 下的 dimLocation。

Example 2-10：Star schema location dimension

CREATE TABLE dimLocation (
  locationID INT PRIMARY KEY,
  country VARCHAR(50),
  city VARCHAR(50),
  State VARCHAR(50)
);

Example 2-11 按照 snowflake schema 建模 location dimension。

Example 2-11：Snowflake schema location dimension

CREATE TABLE dimLocation (
  locationID INT PRIMARY KEY,
  locationName VARCHAR(50),
  cityID INT
);

CREATE TABLE dimCity (
  cityID INT PRIMARY KEY,
  city VARCHAR(50),
  stateID INT
);

CREATE TABLE dimState (
  stateID INT PRIMARY KEY,
  state VARCHAR(50),
  countryID INT
);

CREATE TABLE dimCountry (
  countryID INT PRIMARY KEY,
  country VARCHAR(50),
);

在 snowflake schema 示例中，location dimension 被拆分为四个 tables：dimLocation、dimCity、dimState 和 dimCountry。这些 tables 通过 primary 和 foreign keys 连接，以建立它们之间的 relationships。

一个重要话题是：虽然我们用四个 tables 来表示 location dimension，但只有最高 hierarchy 的 table 会通过其 primary key 连接到 fact table 或 fact tables。所有其他 hierarchy levels 会从最高到最低 granularity 追随 lineage。图 2-4 展示了这种情况。

图 2-4：Snowflake schema model

Modeling with Data Vault

Data Vault 2.0 是一种 modeling approach，它不属于 dimensional modeling，但仍然值得提及。它的方法结合了 3NF elements 和 dimensional modeling，用于创建 logical enterprise data warehouse。它被设计为通过 flexible 和 scalable patterns 处理各种 data types，包括 structured、semi-structured 和 unstructured data。其最突出的特征之一是，它专注于构建 modular 和 incremental Data Vault model，并基于 business keys 集成 raw data。这种 approach 确保 data warehouse 可以适应变化中的 business requirements 和不断演进的 datasets。

深入来看，这种 modeling technique 提供 scalable 和 flexible 的 data warehousing 与 analytics solution。它被设计为处理 large data volumes、changing business requirements，以及 evolving data sources。Data Vault 的 model 由三个主要 components 组成：hubs、links 和 satellites。

Hubs 表示 business entities，并作为存储 unique identifiers 的中心点，这些 identifiers 称为 business keys。每个 hub 对应一个 specific entity，例如 customers、products 或 locations。Hub table 包含 business-key column，以及与 entity 相关的任何 descriptive attributes。通过将 business key 与 descriptive attributes 分离，Data Vault 支持轻松跟踪 descriptive information 的变化，而不会破坏 business key 的 integrity。

Links 捕获 business entities 之间的 relationships。它们被创建用于表示 many-to-many relationships 或 complex associations。Link table 包含来自参与 hubs 的 foreign keys，形成 linked entities 之间的 bridge。这种 approach 支持建模 complicated relationships，而不会复制 data 或制造不必要 complexity。

Satellites 存储与 hubs 和 links 相关的 context-specific attributes。它们包含不属于 business key 但为 entities 提供有价值 context 的 additional descriptive information。Satellites 通过 foreign keys 与相应 hubs 或 links 关联，从而支持存储 time-varying data 并保留 historical records。一个 hub 或 link 可以关联多个 satellites，每个 satellite 捕获特定时间点或不同 perspective 的特定 attributes。

Data Vault architecture 推动 traceability、scalability 和 auditability，同时为 data integration、analytics 和 data governance 提供坚实基础。通过使用 hubs、links 和 satellites，组织可以构建支持 analytical needs、适应 changing business requirements，并维护可靠 data changes historical record 的 Data Vault。

回到我们的 books table，让我们遵循三个 modeling steps，开发一个简单的 Data Vault model。第一步是识别 business keys，并创建相应 hub 和 satellite tables。在这个案例中，我们只有一个 business entity，因此不会使用 links。Example 2-12 展示了 books table 的 Data Vault modeling。

Example 2-12：使用 Data Vault 2.0 建模 books table

-- This is our base table
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    publication_year INT,
    genre VARCHAR(50)
);

在 Data Vault modeling 中，我们首先识别 business keys，也就是每个 entity 的 unique identifiers。在这个案例中，books table 的 primary key book_id 充当 business key。

现在是建模并创建第一个 table 的时候：hub table，它存储 unique business keys 以及对应 hash keys，以确保 stability。Example 2-13 创建 hub table。

Example 2-13：Hub creation

CREATE TABLE hubBooks (
    bookKey INT PRIMARY KEY,
    bookHashKey VARCHAR(50),
    Title VARCHAR(100)
);

在 hub table 中，我们将每本 book 的 unique identifier 存储为 primary key（bookKey），并存储一个 hash key（bookHashKey）用于 stability。Title column 包含关于 book 的 descriptive information。

接下来是 satellite table，如 Example 2-14 所示，它捕获 additional book details 并维护 historical changes。

Example 2-14：Satellite creation

CREATE TABLE satBooks (
    bookKey INT,
    loadDate DATETIME,
    author VARCHAR(100),
    publicationYear INT,
    genre VARCHAR(50),
    PRIMARY KEY (bookKey, loaddate),
    FOREIGN KEY (bookKey) REFERENCES hubBooks(bookKey)
);

通过将 core book information 分离到 hub table，并将 historical details 存储到 satellite table 中，我们确保 author、publication year 或 genre 等 attributes 的变化可以随时间被捕获，而无需修改 existing records。

在 Data Vault model 中，我们可能还有 additional tables，例如用于表示 entities 之间 relationships 的 link tables，或其他 satellite tables，用于捕获特定 attributes 的 historical changes。

Monolith Data Modeling

直到不久前，data modeling 的主流方法都围绕创建大型 SQL scripts 展开。在这种传统方法中，一个 SQL file 通常长达数千行，包含整个 data modeling process。对于更复杂的 workflow，practitioners 可能会将该文件拆分为多个 SQL scripts 或 stored procedures，然后通过 Python scripts 顺序执行。为了让 workflow 更复杂，这些 scripts 通常在组织内部几乎不为人所知。因此，即使另一个人想以类似方式进行 data modeling，也往往会从零开始，放弃利用已有工作的机会。

这种方法可以恰当地描述为 monolithic 或 traditional approach to data modeling。在这种方法中，每个 data consumer 都独立地从 raw source data 重建自己的 data transformations。在这个 paradigm 中，存在几个显著挑战，包括 scripts 缺乏 version control，管理 views 之间 dependencies 的任务艰巨，以及从 raw data sources 到最终 reporting stage 反复创建新的 views 或 tables，从而损害 reusability。此外，idempotency 概念并未统一应用于 large tables，有时导致 redundancy，而 backfills 虽然常见，但通常复杂且 labor-intensive。

在今天快速演进的 data engineering 世界中，monolithic data models，尤其是在 SQL transformations context 中，是 engineers 需要应对的重大挑战。考虑以下场景：你发现 production system 中有东西 broken，但随后发现原本看似简单的 change 触发了一连串 errors，并传播到整个 infrastructure。这种噩梦般的场景，以高度 interconnected systems 和一个 minor alteration 触发 cascading domino effect 为特征，是许多 data professionals 都非常熟悉的问题。

Monolithic data models 的相关风险，正是我们在设计 data model 时希望避免的。你最不希望看到的，是 tightly coupled data models，使 debugging 和 implementing changes 变成艰巨任务，因为每个 change 都可能破坏整个 data pipeline。缺乏 modularity 会阻碍今天 data-driven landscape 中至关重要的 flexibility、scalability 和 maintainability。

在 monolithic data model 中，所有 components 都紧密互联，使识别和隔离问题变得困难。本质上，这种传统 data systems 设计方法倾向于将整个 system 统一成一个单一单元，尽管它并不总是 cohesive。

Model 的这种 interconnectedness 意味着，看似不相关的 changes 可能产生影响整个 system 的 unintended consequences。这种 complexity 不仅使 troubleshooting 更困难，也增加了引入 errors 或忽略 critical dependencies 的风险。所有 data 和 functionality 都如此紧密集成和相互依赖，以至于如果不影响整个 system，就很难修改或更新 system 的任一部分。

此外，data models 缺乏 modularity，会阻碍其适应变化中的 business requirements。在 data needs 动态变化且 sources 不断变化的环境中，monolithic model 会成为进展的 bottleneck。引入 new data sources、扩展 infrastructure，或集成 new technologies 和 frameworks 都会变得越来越困难。

同时，monolithic data model 的 maintenance 和 updates 会变得耗时且消耗资源。由于 system 内部复杂 dependencies，每次 change 都带来更高风险。担心无意中破坏 critical components，会导致过度谨慎的方法，拖慢 development cycles 并抑制 innovation。

今天 data engineering 格局中 monolithic data models 带来的挑战非常显著。Interdependencies、缺乏 flexibility，以及 maintenance 和 scalability 困难等风险，要求我们转向 modular data models。通过采用 modularity，data engineers 可以在 data infrastructure 中获得更高 flexibility、robustness 和 adaptability，以管理快速演进 data ecosystem 的复杂性。通过摆脱 monolithic structures，组织可以释放 data 的全部潜力，推动 innovation，并在我们所处的 data-driven 世界中获得 competitive advantage。

dbt 在采用 modular approach 并克服 monolithic models 的挑战方面发挥了重要作用。它允许我们将单一 data model 拆分为 individual modules，每个 module 拥有自己的 SQL code 和 dependencies，从而提升 maintainability、flexibility 和 scalability。这种 modular structure 允许我们独立处理 individual modules，使开发、测试和 debug data model 中特定部分更加容易。这消除了 unintended changes 影响整个 system 的风险，使引入 changes 和 updates 更安全。

dbt 中 modularity 这个话题会在后续小节中得到更多关注，第 4 章将全面深入探索 dbt。

Building Modular Data Models

前面的例子强调了 dbt 以及 data model modularization 总体上能够为更好的 data development process 做出多大贡献。然而，为什么这对 data engineers 和 scientists 来说并不是理所当然的？事实是，在 software development 世界中，过去几十年里，engineers 和 architects 已经选择了新的方式来使用 modularization，以简化 coding process。与一次处理一大段 code 不同，modularization 会将 coding process 拆分为多个 steps。与其他 strategies 相比，这种方法提供了多项优势。

Modularization 的一个主要优势是提升 manageability。在开发大型 software program 时，持续专注于单一 piece of coding 可能很有挑战。但通过将其拆分成 individual tasks，工作会变得更可管理。这有助于 developers 保持进度，并防止他们被项目规模压垮。

Modularization 的另一个优势是支持 team programming。与其将一个 large job 分配给单个 programmer，不如将其分配给一个 team。每个 programmer 负责 overall program 中的 specific tasks。最终，所有 programmers 的工作会被组合起来，形成 final program。这种方式加速 development process，并允许 team 内部 specialization。

Modularization 也有助于提升 code quality。将 code 拆分成 small parts，并将责任分配给 individual programmers，可以提升每个 section 的质量。当 programmer 专注于自己负责的 section，而不用担心 entire program 时，就能确保自己的 code 没有缺陷。因此，当所有 parts 被集成时，overall program 更不容易包含 errors。

此外，modularization 支持复用已经被证明有效的 code modules。通过将 program 拆分为 modules，fundamental aspects 被分解。如果某段 code 对某个 specific task 工作良好，就不需要重新发明它。相反，可以复用同一段 code，节省 programmers 的时间和精力。当 program 中需要类似 features 时，这可以不断重复，从而进一步 streamlining development。

另外，modular code 高度 organized，这增强了 readability。通过基于 tasks 组织 code，programmers 可以根据 organization scheme 轻松找到并引用 specific sections。这改善了多个 developers 之间的 collaboration，因为他们可以遵循相同 organization scheme，并更高效地理解 code。

Modularization 的所有这些优势最终都会提升 reliability。更容易阅读、debug、maintain 和 share 的 code，会以更少 errors 运行。当处理大量 developers 参与的大型项目时，这一点变得至关重要，因为他们未来需要共享 code，或与彼此的 code 交互。Modularization 使以可靠方式创建复杂 software 成为可能。

尽管 modularization 在 software engineering 世界中是必须且理所当然的，在 data space 中，它却长期落后，直到近几年才被重新重视。背后的原因是 data architecture 和 software engineering 之间需要更清晰的界定。不过，最近行业已经演进为二者的融合，因为前面提到的优势同样适用于 data analytics 和 engineering。

正如 modularization 简化 coding process，它也可以简化 data models 的设计和开发。通过将 complex data structures 拆分为 modular components，data engineers 可以在不同 granularity levels 上更好地管理和操作 data。这种 modular approach 支持高效 data integration、scalability 和 flexibility，使 overall data architecture 的 updates、maintenance 和 enhancements 更加容易。

同时，modularization 促进 data modules 的复用，确保 data models 之间 consistency 和 accuracy，并减少 redundancy。总体而言，modularization principles 为有效 data modeling 和 engineering 提供坚实基础，增强 data systems 的 organization、accessibility 和 reliability。

因此，modular data modeling 是一种强大的技术，用于设计 efficient 和 scalable data systems。Developers 可以通过将 complex data structures 拆分为更小的 reusable components，构建更 robust 和 maintainable systems。这是设计 efficient 和 scalable data systems 的强大技术，而 dbt 和 SQL 都提供了高效工具，帮助我们实现这一技术。

总结来看，modular data modeling 的核心原则可以定义如下：

Decomposition

将 data model 拆分为更小、更易管理的 components。

Abstraction

将 data model 的 implementation details 隐藏在 interfaces 背后。

Reusability

创建可以在 system 的多个部分复用的 components。

这种 data modeling 可以通过 normalization、data warehousing 和 data virtualization techniques 实现。例如，使用 normalization technique 时，data 会根据其 characteristics 和 relationships 被分离到 tables 中，从而形成 modular data model。

另一种选择是利用 dbt，因为它帮助自动化创建 modular data model 的过程，并提供多个支持 modular data modeling principles 的 features。例如，dbt 允许我们通过将 data model 拆分为更小的 reusable components 来处理 decomposition，并提供创建 reusable macros 和 modular model files 的方式。它还通过为处理 data sources 提供简单、一致的 interface，帮助我们 abstract data model 的 implementation details。

此外，dbt 通过提供在 various models 中定义和复用 common code 的方式来鼓励 reusability。与此同时，dbt 通过提供测试和文档化 data models 的方式，帮助提升 maintainability。最后，dbt 允许你为 models 定义和测试不同 materialization strategies，从而优化 performance。最终，这使你能够 fine-tune data model 中 individual components 的 performance。

然而，也必须承认 modularity 也伴随潜在 drawbacks 和 risks。Integrated systems 往往可以比 modular systems 更好地优化，这可能来自 minimizing data movement 和 memory usage，也可能来自 database optimizer 能在幕后优化 SQL。先创建 views，再创建 tables，有时可能导致 suboptimal models。不过，考虑到 modularity 的收益，这种 trade-off 通常是值得的。Modularity 会创建更多 files，这可能意味着更多 objects 需要拥有、治理，并可能废弃。如果没有成熟的 data governance strategy，这可能导致大量 modular 但 unowned tables 的扩散，一旦出现问题会很难管理。

Enabling Modular Data Models with dbt

正如前面强调的，构建 modular data models 是开发 robust 和 maintainable data infrastructure 的重要方面。然而，随着 project size 和 complexity 增长，管理和编排这些 models 的过程可能变得复杂。

这正是 dbt 这种 robust data transformation tool 发挥作用的地方。通过将 modular data modeling principles 与 dbt features 结合，我们可以轻松释放 data infrastructure 中全新层级的 efficiency 和 scalability。

采用这种 modular approach 后，组织内每个 data producer 或 consumer 都能够基于他人已经完成的 foundational data modeling work 继续构建，从而无需每次都从 source data 从零开始。

将 dbt 集成到 data modeling framework 后，视角会发生转变：data models 不再被视为 monolithic entity，而是被视为 distinct component。每个 model 的 individual contributor 会开始识别可以在多个 data models 之间共享的 transformations。这些 shared transformations 会被提取并组织为 foundational models，使其能在多个 contexts 中高效引用。

如图 2-5 所示，在多个 instances 中使用 basic data models，而不是每次都从零开始，可以简化 data modeling 中 DAG 的可视化。这种 modularized multilevel structure 清楚展示 data modeling logic 的 layers 如何相互构建，并展示 dependencies。不过必须注意，仅仅采用 dbt 这样的 data modeling framework，并不会自动确保 modular data models 或 easy-to-understand DAG。

图 2-5：dbt modularity

DAG 的结构取决于 team 的 data modeling ideas 和 thought processes，以及它们表达的一致性。为了实现 modular data modeling，应考虑 naming conventions、readability，以及 debugging 和 optimization 的便利性等原则。这些原则可以应用于 dbt 中的各种 models，包括 staging models、intermediate models 和 mart models，以提升 modularity 并维持 well-structured DAG。

让我们通过理解 dbt 如何借助 Jinja syntax，通过引用 data models 的方式，即 {{ ref() }}，实现 model reusability，开启利用 dbt 构建 modular data models 的旅程。

Referencing data models

通过采用 dbt 的 features，例如 model referencing 和 Jinja syntax，data engineers 和 analysts 可以在 models 之间建立清晰 dependencies，增强 code reusability，并确保 data pipelines 的 consistency 和 accuracy。在这里，Jinja 是一种 templating language，允许在 SQL code 中进行 dynamic 和 programmatic transformations，为 customizing 和 automating data transformations 提供强大工具。Modularity 和 dbt capabilities 的强大组合，使 teams 能够构建 flexible 和 maintainable data models，加速 development process，并支持 stakeholders 之间的 seamless collaboration。

为了充分利用 dbt capabilities 并确保 accurate model building，必须使用 {{ ref() }} syntax 进行 model referencing。通过这种方式引用 models，dbt 可以基于 upstream tables 自动检测并建立 models 之间的 dependencies。这使 data transformation pipeline 能够 smooth 且 reliable execution。

另一方面，{{ source() }} Jinja syntax 应谨慎使用，通常仅限于从 database 中初始选择 raw data。避免直接引用非 dbt 创建的 tables 非常重要，因为这可能阻碍 dbt workflow 的 flexibility 和 modularity。相反，应通过使用 {{ ref() }} Jinja syntax 专注于建立 models 之间的 relationships，确保 upstream tables 的 changes 正确传播到 downstream，并保持清晰一致的 data transformation process。遵循这些 best practices，dbt 可以实现高效 model management，并促进 analytics workflow 的 scalability 和 maintainability。

例如，假设我们有两个 models：orders 和 customers，其中 orders table 包含 customer orders information，而 customers table 存储 customer details。我们希望对这两个 tables 执行 join，用 customer information enrich orders data（Example 2-15）。

Example 2-15：Referencing model

-- In the orders.sql file
SELECT
  o.order_id,
  o.order_date,
  o.order_amount,
  c.customer_name,
  c.customer_email
FROM
  {{ ref('orders') }} AS o
JOIN
  {{ ref('customers') }} AS c
ON
  o.customer_id = c.customer_id

-- In the customers.sql file
-- customers.sql
SELECT
  customer_id,
  customer_name,
  customer_email
FROM
  raw_customers

这个示例展示了如何在 SQL query 中使用 ref() function 引用 models。该场景涉及两个 model files：orders.sql 和 customers.sql。

在 orders.sql 文件中，写了一个 SELECT statement，用于从 orders model 中检索 order information。{{ ref('orders') }} expression 引用 orders model，使 query 可以使用该 model 中定义的 data。该 query 通过 customer_id column 将 orders model 与 customers model join，从而检索 additional customer information，例如 name 和 email。

在 customers.sql 文件中，写了一个 SELECT statement，用于从 raw_customers table 中提取 customer information。该 model 表示任何 transformations 之前的 raw customer data。

dbt 中的这种 referencing mechanism 支持创建 modular 且 interconnected 的 models，这些 models 相互构建，以生成 meaningful insights 和 reports。为了说明其必要性，考虑一个实际例子：想象你正在处理一个 complex dataset，例如 weekly product orders。没有 structured approach 时，管理这些 data 很快会变得混乱。你可能最终得到一团 tangled web of SQL queries，难以跟踪 dependencies、维护 code，并确保 data accuracy。

通过将 data transformation process 组织为 distinct layers，从 source 到 mart tables，你可以获得多个收益。这会简化 data pipeline，使其更容易理解和管理。它也支持 incremental improvements，因为每一层专注于 specific transformation task。这种 structured approach 增强 data engineers 和 analysts 之间的 collaboration，减少 errors，并最终带来更 reliable 和 insightful reports。

Staging data models

Staging layer 在 data modeling 中发挥关键作用，因为它是 modular construction of more complex data models 的基础。每个 staging model 对应一个 source table，并与 original data source 具有 1:1 relationship。保持 staging models 简单，并最小化该层 transformations 非常重要。可接受的 transformations 包括 type conversion、column renaming、basic calculations（例如 unit conversion），以及使用 CASE WHEN 等 conditional statements 进行 categorization。

Staging models 通常 materialize 为 views，以保持 data timeliness 并优化 storage costs。这种 approach 允许引用 staging layer 的 intermediate 或 mart models 访问 up-to-date data，同时节省 space 和 cost。建议避免在 staging layer 中进行 joins，以防止 redundant 或 duplicate computations。Join operations 更适合后续 layers，在那里建立更复杂 relationships。

此外，staging layer 中应避免 aggregations，因为它们可能 group 并限制对 valuable source data 的访问。Staging layer 的主要目的，是为后续 data models 创建 basic building blocks，为 downstream transformations 提供 flexibility 和 scalability。遵循这些 guidelines，staging layer 会成为 modular data architecture 中构建 robust data models 的 reliable 和 efficient starting point。

在 dbt 中使用 staging models，使我们能够在 code 中采用 Don’t Repeat Yourself（DRY）principle。通过遵循 dbt 的 modular 和 reusable structure，我们的目标是将特定 component model 持续需要的任何 transformations 尽可能推到 upstream。这种 approach 帮助我们避免 duplicating code，从而降低 complexity 和 computational overhead。

例如，假设我们持续需要将 monetary values 从以 cents 表示的 integers 转换为以 dollars 表示的 floats。在这种情况下，在 staging model 中尽早执行 division 和 type casting 会更高效。这样，我们可以在 downstream 引用 transformed values，而不必多次重复相同 transformation。通过利用 staging models，我们可以优化 code reuse，并以 scalable 和 efficient 方式 streamlining data transformation process。

假设有一个名为 raw_books 的 source table，包含 raw books data。现在我们想创建一个名为 stg_books 的 staging model，用于在 further processing 前 transform 和 prepare data。在 dbt project 中，可以创建一个名为 stg_books.sql 的新 dbt model file，并定义生成 staging model 的 logic，如 Example 2-16 所示。

Example 2-16：Staging model

/* This should be file stg_books.sql, and it queries the raw table to create
the new model */

SELECT
  book_id,
  title,
  author,
  publication_year,
  genre
FROM
  raw_books

像本例中的 stg_books 这样的 staging model，会从 raw_books table 中选择 relevant columns。它可以包括 basic transformations，例如 renaming columns 或 converting data types。通过创建 staging model，你将 initial data transformation 与 downstream processing 分离。这确保 data quality、consistency，以及对 standards 的 compliance，然后才进一步使用。Staging models 是 data pipeline 中 intermediate 和 mart layers 中更复杂 data models 的基础。它们 streamlining transformations，维护 data integrity，并提高 dbt project 的 reusability 和 modularity。

Base data models

在 dbt 中，base models 通常充当 staging models，但它们也可以根据 project 的具体需求包含 additional transformation steps。这些 models 通常被设计为直接引用进入 data warehouse 的 raw data，并在 data transformation process 中发挥关键作用。一旦你创建了 staging 或 base models，dbt project 中的其他 models 就可以引用它们。

dbt documentation 中从 “base” models 改为 “staging” models，反映了不希望被 “base” 这个名称所限制，因为 “base” 暗示 data model 构建的第一步。新术语在描述这些 models 在 dbt framework 中的 role 和 purpose 时提供了更高 flexibility。

Intermediate data models

Intermediate layer 在 data modeling 中发挥关键作用，它通过组合 staging layer 中的 atomic building blocks，创建更复杂、更 meaningful 的 models。这些 intermediate models 表示对 business 有意义的 constructs，但通常不会通过 dashboards 或 applications 直接暴露给 end users。

为了保持 separation 并优化 performance，建议将 intermediate models 存储为 ephemeral models。Ephemeral models 不会直接在 database 或 dataset 中创建，而是将其 code 作为 common table expressions（CTEs）插入到引用它们的 models 中。不过，有时将它们 materialize 为 views 更合适。Ephemeral models 不能被直接 selected，这使 troubleshooting 具有挑战。此外，通过 run-operation 调用的 macros 不能引用 ephemeral models。因此，某个特定 intermediate model 应 materialize 为 ephemeral 还是 view，取决于具体 use case，但推荐从 ephemeral materialization 开始。

如果选择将 intermediate models materialize 为 views，那么将它们放在 dbt profile 中定义的 main schema 之外的 custom schema 中可能更有好处。这有助于组织 models，并有效管理 permissions。

Intermediate layer 的主要目的是将不同 entities 汇聚在一起，并从最终 mart models 中吸收 complexity。这些 models 增强整体 data model structure 的 readability 和 flexibility。需要考虑某个 intermediate model 在其他 models 中被引用的频率。多个 models 引用同一个 intermediate model，可能意味着 design issue。在这种情况下，将 intermediate model 转换为 macro 可能是合适方案，以增强 modularity 并维持更干净 design。

通过有效利用 intermediate layer，可以让 data models 更 modular、更易管理，同时在吸收 complexity 的同时保持 components 的 readability 和 flexibility。

假设我们有两个 staging models：stg_books 和 stg_authors，分别表示 book 和 author data。现在我们想创建一个名为 int_book_authors 的 intermediate model，将这两个 staging models 中的 relevant information 结合起来。在 dbt project 中，可以创建一个名为 int_book_authors.sql 的新 dbt model file，如 Example 2-17 所示，并定义生成 intermediate model 的 logic。

Example 2-17：Intermediate model

-- This should be file int_book_authors.sql

-- Reference the staging models
WITH
  books AS (
    SELECT *
    FROM {{ ref('stg_books') }}
  ),
  authors AS (
    SELECT *
    FROM {{ ref('stg_authors') }}
  )

-- Combine the relevant information
SELECT
  b.book_id,
  b.title,
  a.author_id,
  a.author_name
FROM
  books b
JOIN
  authors a ON b.author_id = a.author_id

在 Example 2-17 中，int_book_authors model 使用 {{ ref() }} Jinja syntax 引用 staging models，即 stg_books 和 stg_authors。这确保 dbt 可以正确推断 model dependencies，并基于 upstream tables 构建 intermediate model。

Mart models

Data pipeline 的最上层由 mart models 组成，它们负责整合并通过 dashboards 或 applications 向 end users 展示 business-defined entities。这些 models 结合来自多个 sources 的所有 relevant data，并将其 transform 成 cohesive view。

为了确保 optimal performance，mart models 通常 materialized 为 tables。Materializing models 支持更快 query execution，并在向 end users 交付 results 时提供更好 responsiveness。如果 materializing table 的 creation time 或 cost 是问题，可以考虑配置为 incremental model，使其在 new data 被包含时高效更新。

Simplicity 是 mart models 的关键，应避免 excessive joins。如果你需要在 mart model 中使用多个 joins，应重新思考 design，并考虑重构 intermediate layer。通过保持 mart models 相对简单，可以确保 efficient query execution，并维护 data pipeline 的整体 performance。

我们以 book publication analysis 的 data mart 为例。我们有一个名为 int_book_authors 的 intermediate model，其中包含 raw books data，包括每本书 authors 的信息（Example 2-18）。

Example 2-18：Mart model

-- This should be file mart_book_authors.sql

{{
  config(
    materialized='table',
    unique_key='author_id',
    sort='author_id'
  )
}}

WITH book_counts AS (
  SELECT
    author_id,
    COUNT(*) AS total_books
  FROM {{ ref('int_book_authors') }}
  GROUP BY author_id
)
SELECT
  author_id,
  total_books
FROM book_counts

我们首先为 model 设置 configuration，指定它应 materialized 为 table。Unique key 设置为 author_id 以确保 uniqueness，并且也基于 author_id 排序。

接下来，我们使用一个名为 book_counts 的 CTE 来 aggregate book data。我们从 stg_books staging model 中选择 author_id column，并统计与每个 author 关联的 books 数量。最后，SELECT statement 从 book_counts CTE 中检索 aggregated data，返回每个 author 的 author_id 和对应 books count。由于这是 materialized table，该 model 可以在需要时刷新，以反映 original data 的任何变化。

Testing Your Data Models

dbt 中的 testing 是确保 data models 和 data sources 准确性与可靠性的关键方面。dbt 提供 comprehensive testing framework，使你能够使用 SQL queries 定义并执行 tests。这些 tests 的设计目的，是识别不满足指定 assertion criteria 的 rows 或 records，而不是检查某些 specific conditions 的 correctness。

dbt 有两种主要 tests：singular 和 generic。Singular tests 是 specific 和 targeted tests，以 SQL statements 编写，并存储在独立 SQL files 中。它们允许你测试 data 的特定方面，例如检查 fact table 中是否不存在 NULL values，或验证某些 data transformations。借助 singular tests，我们可以利用 Jinja 的力量，根据 data 和 business requirements 动态定义 assertions。让我们通过 Example 2-19 查看 dbt 中的 singular test。

Example 2-19：dbt 中的 singular test 示例

version: 2

models:
  - name: my_model
    tests:
      - not_null_columns:
          columns:
            - column1
            - column2

在这个例子中，我们为 dbt model my_model 定义了一个名为 not_null_columns 的单一 test。这个 test 检查 model 中特定 columns 是否包含 NULL values。columns parameter 指定要检查 NULL values 的 columns。在这个案例中，指定的是 column1 和 column2。如果这些 columns 中任何一个包含 NULL values，test 就会失败。

Generic tests 则更 versatile，可以应用于多个 models 或 data sources。它们使用特殊 syntax 在 dbt project files 中定义。这些 tests 允许我们定义更 comprehensive criteria 来验证 data，例如检查 tables 之间的 data consistency，或确保特定 columns 的 integrity。此外，它们提供 flexible 和 reusable 的方式来定义 assertions，并可跨 dbt models 应用。这些 tests 写入并存储在 YAML（.yml）files 中，使我们可以 parameterize queries，并在不同 contexts 中轻松复用它们。Generic tests 中 queries 的 parameterization 让你可以快速适配多个 scenarios。例如，在将 generic test 应用到不同 models 或 datasets 时，可以指定不同 column names 或 condition parameters。

让我们在 Example 2-20 中看一个 generic test。

Example 2-20：dbt 中的 generic test 示例

version: 2

tests:
  - name: non_negative_values
    severity: warn
    description: Check for non-negative values in specific columns
    columns:
      - column_name: amount
        assert_non_negative: {}
      - column_name: quantity
        assert_non_negative: {}

在这个例子中，generic test 被定义为 non_negative_values。这里可以看到要测试的 columns，以及每个 column 的 assertion criteria。该 test 检查 amount 和 quantity columns 中的 values 是否为 nonnegative。Generic tests 允许你编写 reusable test logic，并将其应用于 dbt project 中的多个 models。

为了在多个 models 中复用 generic test，我们可以在每个 individual model 的 YAML file 的 tests section 中引用它，如 Example 2-21 所示。

Example 2-21：复用 generic test

version: 2

models:
  - name: my_model
    columns:
      - column_name: amount
        tests: ["my_project.non_negative_values"]
      - column_name: quantity
        tests: ["my_project.non_negative_values"]

在这个例子中，定义了 model my_model，并为 amount 和 quantity columns 指定对应 tests。这些 tests 引用 namespace my_project 中的 generic test non_negative_values，假设 my_project 是你的 dbt project 名称。

通过在每个 model 的 tests section 中指定 generic test，你可以在多个 models 中复用相同 test logic。这种 approach 确保 data validation 的 consistency，并允许你轻松地将 generic test 应用到不同 models 中的特定 columns，而无需重复 test logic。

注意，你必须确保 generic test 的 YAML file 位于 dbt project structure 中的正确 directory 内，并且可能需要修改 test reference，以匹配你的 project namespace 和 folder structure。

Generating Data Documentation

Proper data modeling 的另一个重要组成部分是 documentation。具体来说，确保组织中的每个人，包括 business users，都能轻松理解并访问 ARR（annual recurring revenue）、NPS（net promoter score），甚至 MAU（monthly active users）等 metrics，对于支持 data-driven decision making 至关重要。

通过利用 dbt 的 features，我们可以记录这些 metrics 如何被定义，以及它们依赖的具体 source data。该 documentation 成为任何人都可以访问的宝贵资源，促进 transparency，并支持 self-service data exploration。

随着我们消除这些 semantics barriers，并提供 accessible documentation，dbt 使不同技术水平的 users 都能浏览和探索 datasets，确保 valuable insights 能被更广泛 audience 获取。

假设我们有一个 dbt project，其中有一个名为 nps_metrics.sql 的 model，用于计算 net promoter score。我们可以使用 SQL file 中的 comments，并结合 Markdown syntax，轻松记录这个 metric，如 Example 2-22 所示。

Example 2-22：Documentation

/* nps_metrics.sql

-- This model calculates the Net Promoter Score (NPS)
for our product based on customer feedback.

Dependencies:
- This model relies on the "customer_feedback"
table in the "feedback" schema, which stores customer feedback data.
 - It also depends on the "customer" table in the "users"
schema, containing customer information.

Calculation:
-- The NPS is calculated by categorizing customer
feedback from Promoters, Passives, and Detractors
based on their ratings.
-- Promoters: Customers with ratings of 9 or 10.
-- Passives: Customers with ratings of 7 or 8.
-- Detractors: Customers with ratings of 0 to 6.
-- The NPS is then derived by subtracting the percentage
of Detractors from the percentage of Promoters.
*/

-- SQL Query:
WITH feedback_summary AS (
  SELECT
    CASE
      WHEN feedback_rating >= 9 THEN 'Promoter'
      WHEN feedback_rating >= 7 THEN 'Passive'
      ELSE 'Detractor'
    END AS feedback_category
  FROM
    feedback.customer_feedback
  JOIN
    users.customer
    ON customer_feedback.customer_id = customer.customer_id
)
SELECT
  (COUNT(*) FILTER (WHERE feedback_category = 'Promoter')
  - COUNT(*) FILTER (WHERE feedback_category = 'Detractor')) AS nps
FROM
  feedback_summary;

在这个例子中，comments 提供了 NPS metric 的 essential details。它们指定了 nps_metrics model 的 dependencies，解释 calculation process，并提到 query 中涉及的 relevant tables。

记录 model 后，可以使用 dbt command-line interface（CLI）运行以下命令，为 dbt project 生成 documentation（Example 2-23）。

Example 2-23：运行 documentation generation

dbt docs generate

运行该命令会为整个 dbt project 生成 HTML documentation，包括已记录的 NPS metric。生成的 documentation 可以托管并提供给组织中的 users，使他们能够轻松找到并理解 NPS metric。

Debugging and Optimizing Data Models

提升 dbt performance 的一个有价值 optimization suggestion，是认真分析和优化 queries 本身。一种方法是利用 query planner 的能力，例如 PostgreSQL（Postgres）query planner。理解 query planner 可以帮助你识别 query execution 中潜在 bottlenecks 和 inefficiencies。

另一种有效 optimization technique 是将 complex queries 分解为更小 components，例如 CTEs。根据所涉及 operations 的 complexity 和 nature，这些 CTEs 随后可以转换为 views 或 tables。涉及轻量 computations 的 simple queries 可以 materialized 为 views，而更复杂且 computationally intensive 的 queries 可以 materialized 为 tables。dbt config block 可用于为每个 query 指定 desired materialization approach。

通过有选择地使用合适 materialization technique，可以实现显著 performance improvements。这可能带来更快 query execution times、减少 processing delays，并提高整体 data modeling efficiency。尤其是使用 table materialization 已显示出令人印象深刻的 performance gains，具体速度提升取决于 scenario。

实施这些 optimization recommendations 将支持更精简、更高效的 dbt workflow。通过优化 queries 并使用合适 materialization strategies，你可以优化 dbt models 的 performance，从而实现更好的 data processing 和更高效的 data transformations。

让我们看看 Example 2-24 中的 complex query。

Example 2-24：Complex query 1

SELECT column1, column2, SUM(column3) AS total_sum
  FROM table1
  INNER JOIN table2 ON table1.id = table2.id
  WHERE column4 = 'some_value'
  GROUP BY column1, column2
  HAVING total_sum > 1000

这个 query 涉及 joining tables、applying filters 和 performing aggregations。让我们在创建最终 model 之前，将其分解为多个 CTEs（Example 2-25）。

Example 2-25：Deconstructing complex query 1

-- Deconstructing a complex query using CTEs for optimization

-- CTE 1: Joining required data
WITH join_query AS (
  SELECT table1.column1, table1.column2, table2.column3
  FROM table1
  INNER JOIN table2 ON table1.id = table2.id
)
-- CTE 2: Filtering rows
, filter_query AS (
  SELECT column1, column2, column3
  FROM join_query
  WHERE column4 = 'some_value'
)

-- CTE 3: Aggregating and filtering results
, aggregate_query AS (
  SELECT column1, column2, SUM(column3) AS total_sum
  FROM filter_query
  GROUP BY column1, column2
  HAVING total_sum > 1000
)

-- Final query to retrieve the optimized results, and this will be our model
SELECT *
FROM aggregate_query;

join_query CTE 专注于 joining 所需 tables，而 filter_query CTE 应用 filter condition 来缩小 rows 范围。随后，aggregate_query CTE 执行 aggregation 并应用最终 filter condition。

通过将 complex query 拆分为 individual CTEs，可以简化并组织 logic，以优化 execution。这种 approach 提供更好的 readability、maintainability，以及潜在 performance improvements，因为 database engine 可以为每个 CTE 优化 execution plan。最终 query 通过从 aggregate_query CTE 中选择 columns 来检索 optimized results。

现在让我们探索 dbt 中 debugging materialized models 的过程。一开始这可能是个困难任务，因为它需要 thorough validation。一个重要方面是确保 data model 看起来符合预期，并且 values 与 non-materialized version 匹配。

为了便于 debugging 和 validation，可能需要 fully refresh 整个 table，并将其视为非 incremental。这可以通过 dbt run --full-refresh command 完成，该命令会更新 table，并像第一次执行 model 一样运行它。

在某些情况下，前几天并行执行 model 和 incremental model 的 full update 可能很有帮助。这种 comparative approach 允许验证两个 versions 之间的一致性，并最小化未来 data discrepancies 的风险。当处理已经在 production 中稳定且可靠的 data model 时，这种技术尤其有效，因为它可以增强对所做 changes 的信心。通过比较 updated 和 incremental models，我们可以确保 changes 的准确性，并缓解潜在 data-related issues。

考虑一个 example scenario：有一个 materialized dbt model，基于 transactional data 计算 monthly revenue。我们想 debug 并验证这个 model，以确保其 accuracy。我们开始怀疑 materialized model 生成的 values 可能与 expected results 不匹配。为了 troubleshoot，我们决定 fully refresh table，把它当成非 incremental。使用 dbt full-refresh command，我们触发更新整个 table 并从头运行 model 的 process。

在前几天，我们还并行运行一个 process，更新 materialized 和 incremental models。这使我们能够比较两个 versions 之间的 results，并确保它们一致。通过检查 updated model 和 incremental model 的 consistency，我们增强了对 changes accuracy 的信心。

例如，如果我们有一个 well-established revenue model，它已经在 production 中运行一段时间，并且被认为可靠，那么比较 updated 和 incremental models 会更有意义。通过这种方式，我们可以确认对 model 所做的 changes 没有在 calculated revenue figures 中造成任何 unintended discrepancies。此外，comprehensive testing 对确保 data models 的 accuracy 和 reliability 至关重要。在整个 workflow 中实施 tests，可以帮助早期识别问题，并提供关于 SQL queries performance 的有价值 insights。

NOTE

所有这些 dbt functionalities，从构建 dbt models 到 testing 和 documentation，都会在第 4 章和第 5 章中讨论并强化。

Medallion Architecture Pattern

Data warehouses 在 decision support 和 BI 中有丰富历史，但在处理 unstructured、semi-structured 和 high-variety data 时存在限制。与此同时，data lakes 作为存储 diverse data formats 的 repositories 出现，但它们缺少 transaction support、data quality enforcement 和 consistency 等关键 features。

这限制了它们兑现承诺的能力，并导致与 data warehouses 相关的 benefits 流失。为了满足 companies 不断演进的需求，需要一个 flexible 且 high-performance 的 system，支持 SQL analytics、real-time monitoring、data science 和 machine learning 等 diverse data applications。然而，AI 最近的一些进展聚焦于处理广泛 data types，包括 semi-structured 和 unstructured data，而 traditional data warehouses 并未为此优化。

因此，组织经常使用多个 systems，包括 data lakes、data warehouses 和 specialized databases。这引入了 complexity 和 delays，因为 data 需要在 systems 之间移动和复制。为了将所有这些传统 systems 组合成一个能够响应所有新市场需求的东西，一个新的系统类型正在出现：data lakehouse。

Data lakehouse 结合 data lakes 和 data warehouses 的优势，直接在 cost-effective cloud storage 上，以 Apache Delta Lake、Iceberg 或 Apache Hudi 等 open formats 实现类似 warehouses 的 data structures 和 management features。这些 formats 相比 CSV 和 JSON 等传统 file formats 有多种优势。CSV 缺少 columns typing；JSON 提供更灵活 structures，但 typing 不一致。Parquet、Apache Avro 和 ORC（optimized row columnar）file format 在此基础上改进，它们是 column-oriented 且更 strongly typed，但并不 ACID（atomicity、consistency、isolation、durability）compliant，ORC 在某些情况下除外。相比之下，Delta Lake、Iceberg 和 Hudi 通过添加 ACID compliance，以及支持作为 two-way data stores 的能力，增强 data storage，使其能够支持高 throughput modifications，同时支持大量 analytical queries。这些 formats 尤其适合 modern cloud-based data systems，而不同于传统 Parquet 这类最初为 on-premises Hadoop-based systems 设计的 formats。

Lakehouses 提供关键 features，例如支持 concurrent data reading 和 writing 的 transaction support、schema enforcement 和 governance、direct BI tool support、storage 和 computing 解耦以支持 scalability、使用 standardized storage formats 和 APIs 实现 openness 和 efficient data access、支持 diverse data types，以及兼容 data science、machine learning 和 SQL analytics 等多种 workloads。它们还经常提供 end-to-end streaming capabilities，从而消除实时 data applications 对 separate systems 的需求。Enterprise-grade lakehouse systems 包含 security、access control、data governance、data discovery tools，以及遵守 privacy regulations 等 features。实施 lakehouse 可以让组织将这些 essential features 整合到一个 single system 中，供 data engineers、analytics engineers、scientists、analysts，甚至 machine learning engineers 共享，从而协作开发新的 data products。

正是在 lakehouses 和新 open formats 的 context 中，medallion architecture 出现了。简单来说，这是一种 data modeling paradigm，用于在 lakehouse environment 中 strategic structure data，目标是在 data 穿越不同 iteration levels 时，逐步提升 data quality。这个 architectural framework 通常包含三个可识别 tiers，也就是 bronze、silver 和 gold layers，每一层象征 data refinement 的递进程度：

Bronze layer

这是来自 external source systems 的 data 的初始目的地。该层中的 tables 会按原样 mirror source system tables 的 structures，包括任何 extra metadata columns，用于捕获 load date/time 和 process ID 等信息。该层优先考虑高效 change data capture（CDC），维护 source data 的 historical archive，确保 data lineage，支持 audits，并支持在不重新从 source system 读取 data 的情况下进行 reprocessing。

Silver layer

在 lakehouse architecture 中，该层在 consolidating 和 refining 来自 bronze layer 的 data 方面发挥重要作用。Silver layer 通过 matching、merging、conforming 和 cleansing 等 processes，创建一个 holistic view，涵盖 key business entities、concepts 和 transactions。这包括 master customers、stores、nonduplicated transactions 和 cross-reference tables。Silver layer 作为 self-service analytics 的 comprehensive source，赋能 users 执行 ad hoc reporting、advanced analytics 和 machine learning capabilities。经常可以看到，silver layer 可以采用 3NF data model、star schema、Data Vault，甚至 snowflake 的形式。类似 traditional data warehouse，这是任何利用 data 开展 projects 和 analyses 以解决 business problems 的人都非常有价值的资源。

Gold layer

该层交付能够回答 business questions 的 valuable insights。它聚合来自 silver layer 的 data，并将其服务于 BI ad hoc reporting tools 和 machine learning applications。该层为 data lakes 确保 reliability、improved performance 和 ACID transactions，同时还在 cloud data stores 之上统一 streaming 和 batch transactions。

图 2-6 在 lakehouse context 中展示了 medallion architecture，并显示了 dbt 可以在哪些位置支持创建这类 systems。

图 2-6：Medallion architecture 的表示及其与 dbt 的关系

从 bronze 到 gold layers 的推进过程中，data 会经历多个步骤，例如 ingestion、cleansing、enhancement 和 aggregation processes，从而交付难以估量的 business insights。与 conventional data architectures 相比，这种 approach 是显著进步，例如带有 staging 和 dimensional model layer 的 data warehouse，或者单一 data lake。后者通常更多涉及 file organization，而不是创建 proper semantic layers。

NOTE

Medallion architecture 并不替代其他 dimensional modeling techniques。每一层中 schemas 和 tables 的结构可以根据 data updates 的频率和类型，以及 data 的 intended uses 而变化。相反，它指导 data 应如何跨三层组织，以支持更 modular 的 data modeling approach。

Analytics engineers 理解 medallion architecture 的基础以及 lakehouse 背后的概念很有价值，因为在某些场景中，他们可能会把大量时间花在这里。这种 involvement 可能包括建模要部署在 medallion 某一层的 structures，利用 open formats 提供的 interface，或构建 transformation scripts，例如使用 dbt 等 tools，来支持 data 在 architecture layers 之间推进。

不过，需要注意的是，open formats 和 lakehouses 的重要性会因具体 data architecture 而异。例如，在 Snowflake 这类架构中，data 可能主要被 ingested 到 native tables，而不是 Iceberg 这类 open formats 中，因此对 lakehouses 的理解可能更多是 nice-to-have，而不是 analytics engineering 的 essential requirement。

Summary

Data modeling 在 analysis 领域已经显著演进，以适应多样化 business insights 和 reporting requirements。Star schema 通过一个 central fact table 被 dimension tables 包围，提供一种简单的 query 方法。Snowflake schema 通过进一步拆分这些 dimensions，支持更深 granularity。相比之下，Data Vault approach 优先考虑 flexibility，以处理 data sources 快速变化的 environments。新的 medallion design 将这些 models 组合起来，形成针对各种 analytical needs 的完整计划。

所有 modeling advancements 都是为解决特定 analytical issues 而设计的。核心目标是高效提供可以被采取行动的 insights，无论是在 star 和 snowflake schemas 的 performance improvements 中，还是在 Data Vault 的 versatility 中。随着 analytics requirements 变得更加复杂，选择正确 modeling approach 至关重要，这不仅是为了让 data 可用，也是为了确保它 meaningful，并能够提供 insights。

Analytics engineers 使用 star、snowflake、Data Vault 或 medallion 等 modeling structures，创建和维护 robust、scalable 和 efficient data structures。他们的工作确保 data 得到 optimal organization，使其能够被 data analysts 和 scientists 轻松访问并有效使用。Analytics engineers 通过理解并应用这些 models，从 massive data streams 中创建 coherent datasets，为 accurate insights 和 informed decision making 奠定基础。