数据工程终极设计模式——数据工程基础

数据智能老司机
0 阅读21分钟

引言

组织会从各种数据源接收海量数据，例如 transactional systems、social media、IoT devices 和 enterprise applications。随着数据量、数据类型和数据速度持续增长，data engineers 必须设计高效的数据管道，并构思能够让业务通过分析复杂数据流来做出决策的数据基础设施。

也就是说，并不是所有数据都是一样的。有些数据集高度组织化、结构化，并且可以很容易地适配传统数据库架构。这类数据被称为 structured data，处理起来更容易。然而，相当一部分数据以 semi-structured 和 unstructured formats 存在。因此，我们需要根据数据本身的性质以及组织的性质，选择合适的 storage solution。

因此，本章会先探索数据和信息处理的三类形态：structured、semi-structured 和 unstructured。我们将详细强调每类数据的独特特征和挑战。此外，读者还将了解现代 data storage architecture，包括 relational databases、data lakes 和 data warehouses，以及 data lakehouses 等新兴架构。Data lakehouses 结合了两类架构的优势。

我们还将探索 ETL 和 ELT 等 data processing approaches，并通过 use cases 理解它们。此外，我们还会学习 batch 和 real-time data processing。这是决定数据如何被摄入，并随后用于 analytics 和 decision-making 的两种基本范式。

接下来，我们会介绍 databases 的 data modeling 和 schema design 概念。这在许多方面都非常重要，因为它决定了哪些数据需要被存储，以及这些数据如何被业务和用户消费。我们可以按用户和角色限制数据访问，使用户只能查看其部门数据。Snowflake 等现代 cloud data warehouses 使用 Role-Based Access Control（RBAC）在 databases、schemas、tables、views，甚至 rows 层面执行这一点，这使 access control 比单纯依赖 views 更加细粒度。随后，我们会理解数据如何被 partitioned 和 indexed，这样就可以根据 logical partitions 对数据进行分类，再通过 indexing 以更快速、hashed 的方式搜索数据。

因此，到本章结束时，你将具备扎实的数据工程概念基础，并能够在 data storage 和 processing workflows 方面做出经过计算的决策。本章也解释了 data modelling 和 schema 的必要知识，以及 data partitioning 和 indexing。这些原则将成为设计 scalable、efficient 和 well-governed data pipelines 的蓝图。

结构

本章将覆盖以下主题：

  • 理解 Structured、Semi-Structured 和 Unstructured Data
  • Data Storage Systems 概览：Databases、Data Lakes 和 Data Warehouses
  • 关键 Data Engineering Concepts：ETL vs. ELT、Batch vs. Stream Processing 和 Data Governance
  • Data Modeling 和 Schema Design 入门
  • Data Partitioning 和 Indexing 基础

理解 Structured、Semi-Structured 和 Unstructured Data

Data structuring 指的是信息如何被组织和存储，以便高效处理、检索和分析。我们可以将数据分为以下类型：

Structured Data：组织良好，并以 tabular format 存储，例如 relational databases。

Semi-Structured Data：具备一定结构，但不遵循严格 schema，例如 JSON、XML。

Unstructured Data：没有预定义格式的 raw data，例如 images、videos、text documents。

因此，每种数据类型都会用于不同 applications，并需要不同的 storage 和 processing solutions。

Structured Data

Structured data 高度组织化，并存储在预定义 schema 中，通常位于 relational databases 中。它由 rows 和 columns 组成，因此更容易查询。

我们使用 structured data，是为了通过 SQL queries 实现更快检索。它们使用 ACID（Atomicity、Consistency、Isolation 和 Durability）compliance 来确保 data integrity。Structured data 针对 transactional systems 和 analytics 进行了优化。

它也用于 financial transactions（Banking System）、Inventory management 和 Customer Relationship Management（CRM）systems 等场景。Structured data 的各种 storage solutions 如下：

  • Relational Databases（SQL-based） ：MySQL、PostgreSQL、Microsoft SQL Server 和 Oracle
  • Cloud-Based Databases：Amazon RDS、Google Cloud SQL 和 Azure SQL Database
  • Cloud Data Warehouses（OLAP / Analytics-first） ：Snowflake、Google BigQuery、Amazon Redshift 和 Azure Synapse Analytics

Semi Structured Data

Semi-structured data 没有固定 schema，但包含 tags、attributes 和 markers，这些元素提供了一定结构。几个例子包括 JSON、XML 和 YAML。

我们使用 semi-structured data，是为了实现灵活的数据表示，因为它们很容易与 NoSQL databases 和 APIs 集成。它们支持 hierarchical 和 nested data models。一些常见 use cases 包括 APIs 和 web services、IoT sensor data 以及 log files。以下是 semi-structured data 的一些 storage solutions：

  • NoSQL Databases：MongoDB、Apache Cassandra 和 CouchDB
  • Cloud Storage：AWS DynamoDB 和 Google Firestore
  • Data Lakes：Amazon S3 和 Azure Data Lake

Unstructured Data

Unstructured data 缺少预定义格式，因此很难使用传统 databases 查询。例子包括 images、videos、PDFs、emails 和 social media content。

我们主要在 image recognition，以及 Machine Learning（ML）和 Artificial Intelligence（AI）技术中的 NLP tasks 中使用 unstructured data。Unstructured data 在 Big Data analytics 中非常重要。其常见 use cases 包括 image 和 video processing，例如 self-driving cars、security cameras；social media data 上的 sentiment analysis；以及 document management systems，例如 legal、healthcare。因此，用于 unstructured data 的一些 storage solutions 如下：

  • Object Storage：Amazon S3、Google Cloud Storage 和 Azure Blob Storage
  • Distributed File Systems：Hadoop Distributed File System（HDFS）和用于 streaming data 的 Apache Kafka
  • Specialized Databases：Elasticsearch（用于 text search）、FAISS 和 Pinecone（用于 vector data storage）

Data Types 和 Storage Solutions 对比

下面比较数据类型和常见 storage solutions：

Data TypeExamplesStorage SolutionsBest for
StructuredSQL Tables、SpreadsheetsMySQL、PostgreSQL、Microsoft SQLTransactional Systems、Analytics
Semi-StructuredJSON、XML、YAMLMongoDB、DynamoDB、FirestoreAPIs、Real-Time applications
UnstructuredImages、Videos、PDFsAmazon S3、Google Cloud Storage、Azure Blob StorageAI/ML、Multimedia、Big Data Analytics

表 2.1：Data Types 和 Storage Solutions 对比

Use Case：Smart Healthcare System

一个 smart healthcare system 会从多个来源收集并处理 patient information。以下是信息来源的拆解：

Structured Data：Patient demographics、medical history 和 lab test results 存储在 relational database（MySQL）中。

Semi-Structured Data：IoT devices，例如 smartwatches 和 health monitors，会发送包含 heart rate、oxygen levels 和 step count 的 real-time JSON data。

Unstructured Data：指 medical images，例如 X-rays、MRIs，以及 doctors’ handwritten notes。

现在，我们构建 smart healthcare data pipeline。为了构建 pipeline，我们会使用 MySQL 存储 structured data，使用 MongoDB 存储 semi structured data，并将 images 作为 unstructured data 存储在 local storage 中。

首先，我们创建 patients table，用于存储 structured data，脚本如下：

CREATE DATABASE healthcare_db;
USE healthcare_db;

CREATE TABLE patients (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
age INT,
gender VARCHAR(10),
diagnosis VARCHAR(255)
);

INSERT INTO patients (name, age, gender, diagnosis)
VALUES
('Alice Johnson', 45, 'Female', 'Diabetes'),
('Bob Smith', 60, 'Male', 'Hypertension'),
('Charlie Brown', 29, 'Male', 'Healthy');

SELECT * FROM patients;

Expected Output（Structured Data - SQL Query Result）：

idnameagegenderdiagnosis
1Alice Johnson45FemaleDiabetes
2Bob Smith60MaleHypertension
3Charlie Brown29MaleHealthy

表 2.2：SQL Query 的 Expected Output

接下来，我们存储 semi-structured IoT data（MongoDB - Patient Vitals）。IoT health monitors 会以 JSON 形式发送 patient vitals，例如 heart rate、oxygen level 和 steps。我们将使用 MongoDB 存储这些 semi-structured data，Python 代码如下：

from pymongo import MongoClient

# Connect to MongoDB
client = MongoClient("mongodb://localhost:27017/")
db = client["healthcare_db"]
collection = db["patient_vitals"]

iot_data = [
{
"patient_id": 1,
"device_id": "fitbit_001",
"timestamp": datetime.utcnow(),
"vitals": {
"heart_rate": 75,
"oxygen_level": 98
},
"activity": {
"steps": 5000,
"calories_burned": 230
}
},
{
"patient_id": 2,
"device_id": "apple_watch_023",
"timestamp": datetime.utcnow(),
"vitals": {
"heart_rate": 80
},
"alerts": ["low_oxygen"],
"battery_level": 15
},
{
"patient_id": 3,
"device_id": "medical_patch_v2",
"timestamp": datetime.utcnow(),
"vitals": {
"heart_rate": 72,
"oxygen_level": 99,
"respiratory_rate": 18
},
"activity": {
"steps": 7000
},
"firmware_version": "2.1.4"
}
]

collection.insert_many(iot_data)

# Retrieve and print the data
for data in collection.find():
    print(data)

Expected Output（Semi-Structured Data - MongoDB JSON Documents）：

{
"_id": "ObjectId("…")",
"patient_id": 1,
"device_id": "fitbit_001",
"timestamp": "ISODate("2026-01-04T10:30:00Z")",
"vitals": {
"heart_rate": 75,
"oxygen_level": 98
},
"activity": {
"steps": 5000,
"calories_burned": 230
}
}

{
"_id": "ObjectId("…")",
"patient_id": 2,
"device_id": "apple_watch_023",
"timestamp": "ISODate("2026-01-04T10:31:00Z")",
"vitals": {
"heart_rate": 80
},
"alerts": ["low_oxygen"],
"battery_level": 15
}

{
"_id": "ObjectId("…")",
"patient_id": 3,
"device_id": "medical_patch_v2",
"timestamp": "ISODate("2026-01-04T10:32:00Z")",
"vitals": {
"heart_rate": 72,
"oxygen_level": 99,
"respiratory_rate": 18
},
"activity": {
"steps": 7000
},
"firmware_version": "2.1.4"
}

最后，我们将存储 X-Ray images 等 unstructured data，这些数据来自 local storage。我们使用 OpenCV 加载并显示 image：

import cv2

# Load and display a sample medical image.
image_path = "xray_sample.jpg"
image = cv2.imread(image_path)

# Display the image.
cv2.imshow("Medical X-ray", image)
cv2.waitKey(0)
cv2.destroyAllWindows()

# Save the image to local storage.
cv2.imwrite("processed_xray.jpg", image)
print("Image saved successfully!")

Expected Output（Unstructured Data - Processed Medical Image）

代码会显示 X-ray image，然后在本地系统中保存一份 processed copy。

现在，我们将集成所有数据源，用于 unified healthcare dashboard。这个 dashboard 会显示 patient records、IoT information，以及对应的 X-Ray images。我们将使用以下 Python 代码：

import mysql.connector
from pymongo import MongoClient
import cv2

# Connect to MySQL.
mysql_conn = mysql.connector.connect(
host="localhost", user="root", password="", database="healthcare_db"
)
mysql_cursor = mysql_conn.cursor()

# Assuming patients table has:
# patient_id, name, age, gender, diagnosis, xray_path
mysql_cursor.execute(
"SELECT patient_id, name, age, gender, diagnosis, xray_path FROM patients"
)
patients = mysql_cursor.fetchall()

# Connect to MongoDB.
mongo_client = MongoClient("mongodb://localhost:27017/")
mongo_db = mongo_client["healthcare_db"]
mongo_collection = mongo_db["patient_vitals"]

# Fetch IoT vitals and X-ray per patient.
for patient in patients:
    patient_id, name, age, gender, diagnosis, xray_path = patient
    vitals = mongo_collection.find_one({"patient_id": patient_id})

    print(f"Patient: {name}, Age: {age}, Gender: {gender}, Diagnosis: {diagnosis}")

    heart_rate = vitals.get("heart_rate", "NA")
    oxygen_level = vitals.get("oxygen_level", "NA")
    steps = vitals.get("steps", "NA")

    print(
        f"Heart Rate: {heart_rate} bpm, "
        f"Oxygen Level: {oxygen_level}%, "
        f"Steps: {steps} steps\n"
    )

    # Load and display patient-specific X-ray image.
    if xray_path:
        image = cv2.imread(xray_path)
        if image is not None:
            cv2.imshow(f"Medical X-ray - {name}", image)
            cv2.waitKey(0)
            cv2.destroyAllWindows()

Expected Final Output（All Data Categories Combined）

Patient: Alice Johnson, Age: 45, Gender: Female, Diagnosis: Diabetes
Heart Rate: 75 bpm, Oxygen Level: 98%, Steps: 5000 steps
(X-ray image for Alice Johnson opens in a window)

Patient: Bob Smith, Age: 60, Gender: Male, Diagnosis: Hypertension
Heart Rate: 80 bpm, Oxygen Level: 96%, Steps: 3000 steps
(X-ray image for Bob Smith opens in a window)

Patient: Charlie Brown, Age: 29, Gender: Male, Diagnosis: Healthy
Heart Rate: 72 bpm, Oxygen Level: 99%, Steps: 7000 steps
(X-ray image for Charlie Brown opens in a window)

因此，通过端到端 data integration pipeline 将 structured、semi-structured 和 unstructured data 汇聚在一起，系统可以创建 unified patient view，从而支持更好的 clinical decision-making。

Data Storage Systems 概览：Database、Data Lakes 和 Data Warehouse

本节将探索三种主要 data storage systems：database、data lake 和 data warehouse。了解它们分别是什么，将推动 data engineer 为组织需求选择合适的 storage solution。

Databases（OLTP：Online Transaction Processing）

Database 是一个结构化系统，用于存储和组织数据，使数据易于检索、修改和管理。Databases 被广泛用于存在频繁 read 和 write operations 的场景。

我们将学习两类 databases：relational databases 和 NoSQL databases。

Relational Databases（SQL：Structured Query Language）

它使用具备预定义 schema 的 structured tables。创建 table 前需要先定义 schema，如下：

CREATE DATABASE company_db;
USE company_db;

CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10,2)
);

INSERT INTO employees (name, department, salary)
VALUES ('Alice', 'HR', 60000.00), ('Bob', 'IT', 75000.00);

SELECT * FROM employees;

NoSQL Databases（Non-Relational、Document-Oriented、Key-Value、Columnar、Graph-Based）

NoSQL 具有 flexible schema，存储前不必定义 schema。示例如下：

from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db = client["company_db"]
collection = db["employees"]

employee_data = {"name": "Alice", "department": "HR", "salary": 60000}
collection.insert_one(employee_data)

for employee in collection.find():
    print(employee)

NoSQL databases 有 4 种类型：document-based，例如 MongoDB；column-based，例如 Cassandra；key-value store，例如 Redis；graph-based，例如 Neo4j。

当 applications 需要频繁 CRUD（Create、Read、Update、Delete）operations、real time applications 场景，例如 online shopping、banking、IoT data，以及需要确保 data consistency 和 integrity 时，我们主要使用 databases。

Data Lakes（Big Data 和 AI/ML 的 Raw Storage）

Data lake 是一种 storage system，用于保存海量 raw、unstructured、semi-structured 和 structured data。Data lakes 不强制 schema，因此非常适合 Big Data 和 AI/ML projects。

Data lake 存储所有 data formats。因此，它可扩展且成本高效，并支持 advanced analytics、AI/ML 和 real-time processing。

在基于 S3 的 Data Lake 中存储数据（AWS 示例）

aws s3 mb s3://my-data-lake
aws s3 cp customer_data.json s3://my-data-lake/
aws s3 ls s3://my-data-lake/

我们需要 proper governance 来维护 data lake，否则它会变成 data swamp，也就是充斥过量数据、难以治理的数据沼泽。

Data Warehouses（OLAP：Online Analytical Processing）

Data warehouse 是一种 storage system，针对 business intelligence 和 reporting 中使用的 structured、historical data 进行了优化。它会聚合来自多个来源的数据，并转换为 structured format，使分析更容易执行。

我们使用 data warehouse，是为了 optimized analytics 和 faster processing；它支持 OLAP（Online Analytical Processing），并为 dashboards 转换数据。

我们主要在 business intelligence 和 analytics、aggregated sales、finance 和 operational reports 等场景中使用 data warehouse。这些场景通常需要在分析前进行 data transformation 和 cleaning。

因此，data warehouse 不是为 real-time processing 设计的，并且在 large scale storage 场景中成本较高。

Databases、Data Lakes 和 Data Warehouses 对比

下面基于关键特征比较不同 storage solutions：

FeatureDatabases（SQL / NoSQL）Data LakesData Warehouses
Data TypeStructured、Semi-Structured（NoSQL）All types（Structured、Semi-Structured、Unstructured）Structured
PurposeTransactions、Real-Time ProcessingRaw Data Storage、AI/ML、Big Data AnalyticsBusiness Intelligence、Analytics
SchemaStrict Schema（SQL）/ Flexible（NoSQL）Schema-on-Read（Any Format）Schema-on-Write（Structured）
StorageLimited StorageScalable、Cheap StorageOptimized for Analytics
Use CasesCRM、ERP、E-Commerce、BankingIoT、AI/ML、Big Data、Raw Data StorageBI Dashboards、Historical Analysis、Reporting
ExamplesMySQL、PostgreSQL、MongoDBAWS S3、Azure Data Lake、HadoopSnowflake、Amazon Redshift、Google BigQuery

表 2.3：Data Storage Solutions 对比

关键 Data Engineering Concepts：ETL vs. ELT、Batch vs. Stream Processing

组织从多个来源生成数据，数据可以用两种基本方式进行处理和转换：

  • ETL（Extract、Transform、Load）vs. ELT（Extract、Load、Transform） ：决定数据转换顺序。
  • Batch Processing vs. Stream Processing：决定是分块处理数据，还是实时处理数据。

因此，理解这些概念，对于设计 scalable、efficient 和 real-time data pipelines 至关重要。

ETL vs. ELT（Data Transformation Strategies）

ETL（Extract、Transform、Load）是一种传统方法：先从 source systems 提取数据，将其转换为 structured format，然后加载到 data warehouse 或 database 中。ELT（Extract、Load、Transform）则反转这一过程：先将 raw data 加载到 storage system，例如 data lakes 中，然后通常在 data warehouse 或 Big Data platform 内部再执行 transformation。

FeatureETL（Extract → Transform → Load）ELT（Extract → Load → Transform）
Best ForTraditional databases、BI reportingBig Data、cloud storage、data lakes
Transformation LocationLoading 前，在 external ETL tool 中完成Loading 后，在 storage system 内完成
Performance对 big data 可能较慢更适合 large datasets 的扩展
Use CasesFinancial reports、complianceAI/ML、Big Data analytics
ToolsApache NiFi、Talend、InformaticaSnowflake、BigQuery、Databricks

表 2.4：ETL vs. ELT：关键差异

下面看一个使用 Python 实现 ETL 的例子：

import pandas as pd
import sqlite3

# Extract - Read CSV data
df = pd.read_csv("customers.csv")

# Transform - Clean data
df['email'] = df['email'].str.lower()

# Load - Store into SQL database
conn = sqlite3.connect("customers.db")
df.to_sql("customers", conn, if_exists="replace", index=False)

再来看一个使用 Python 和 BigQuery 实现 ELT 的例子：

from google.cloud import bigquery

client = bigquery.Client()
table_id = "my_project.my_dataset.raw_data"

# Extract and Load - Load CSV into BigQuery
job_config = bigquery.LoadJobConfig(source_format=bigquery.SourceFormat.CSV, autodetect=True)

with open("customers.csv", "rb") as source_file:
    job = client.load_table_from_file(source_file, table_id, job_config=job_config)

job.result()

# Transform - Run SQL transformation in BigQuery
query = """
SELECT LOWER(email) as email, name, country
FROM `my_project.my_dataset.raw_data`
"""

client.query(query).result()
print("ELT Process Completed!")

Batch Processing vs. Stream Processing

Batch processing 会按计划间隔分块处理大量数据。它适合 historical analysis、reporting 和 data warehousing。Stream processing 会实时处理数据，从而支持即时 insights 和 actions。此外，它对于 real-time fraud detection、monitoring 和 AI-based recommendations 至关重要。

FeatureBatch ProcessingStream Processing
Processing ModePeriodic、scheduledReal-time、continuous
Best ForHistorical data、reportingReal-time analytics、event-driven applications
LatencyHigh（minutes to hours）Low（milliseconds to seconds）
Use CasesPayroll processing、BI reportsFraud detection、IoT、stock market monitoring
ToolsApache Spark、Hadoop、AWS GlueApache Kafka、Apache Flink、AWS Kinesis

表 2.5：Batch vs. Stream Processing：关键差异

下面通过 Python 和 Apache Spark 的 batch processing 示例来更好理解：

from pyspark.sql import SparkSession

# Initialize Spark.
spark = SparkSession.builder.appName("BatchProcessing").getOrCreate()

# Load the Data.
df = spark.read.csv("sales_data.csv", header=True, inferSchema=True)

# Transformation
df_filtered = df.filter(df["amount"] > 1000)

# Save the Processed Data.
df_filtered.write.mode("overwrite").csv("processed_sales_data")

print("Batch Processing Completed!")

对于 streaming processing，我们使用 Python 和 Apache Kafka，同时实现 producer 和 consumer scripts，以展示一个核心 data engineering pattern，即 event-driven、decoupled pipelines。Kafka 提供 durable 和 replayable event log，使数据能够实时处理，同时保持可恢复性和可扩展性。Python 因简单且广泛采用而被使用，使焦点保持在 streaming pattern 本身，而不是工具复杂性上。这是现代数据工程系统中的一个基本原则。

Producer（模拟 Data Streaming）

from kafka import KafkaProducer
import json
import time

producer = KafkaProducer(
    bootstrap_servers='localhost:9092',
    value_serializer=lambda v: json.dumps(v).encode('utf-8')
)

for i in range(10):
    data = {"user_id": i, "activity": "login"}
    producer.send("user_activity", value=data)
    time.sleep(1)  # Simulating real-time data streaming

print("Streaming Data Sent!")

Consumer（处理 Real-Time Data）

from kafka import KafkaConsumer
import json

consumer = KafkaConsumer(
    "user_activity",
    bootstrap_servers='localhost:9092',
    auto_offset_reset="earliest",
    value_deserializer=lambda v: json.loads(v.decode('utf-8'))
)

for message in consumer:
    print("Received Data:", message.value)

当 large-scale data 不需要 real-time insights 时，使用 batch processing；当需要 low latency 时，例如 fraud detection，则使用 stream processing。

Data Modeling 和 Schema Design 入门

Data modeling 和 schema design 构成有效 data engineering systems 的基础。Data pipelines 关注移动和转换数据，而 data models 定义数据如何在平台中被结构化、关联、治理和消费。每一种 data engineering pattern，例如 batch processing、streaming、ELT、lakehouse、feature stores 或 real-time analytics，最终都依赖设计良好的 schemas，以确保 correctness、performance 和 scalability。

首先，我们需要理解 data modeling 的概念，然后再进入 schema design。

Data Modeling

Data modeling 是结构化和组织数据的过程，用于定义不同 data elements 之间如何关联。关于数据如何被 stored、retrieved 和 maintained 的规则，都会通过 data modeling 来定义。

Data models 主要有三类：

Conceptual Model：从高层视角展示数据如何与业务需求相关。它定义关键 entities，例如 customers、products，以及它们之间的 relationships。

Logical Model：更详细的版本，会定义 attributes、data types 和 relationships，但与具体技术无关。

Physical Model：数据结构在数据库中的实际实现，定义 tables、columns、constraints 和 indexes。

现在，我们用 library system 作为例子。图书馆需要跟踪 books、members 和 borrowed books。

Conceptual Model（High-Level View）

我们定义关键 entities 及其 relationships，如下：

  • Books：Title、Author、ISBN
  • Members：Name、Membership ID
  • Borrowed Books：Members borrowing books 的 details

Example Relationship：一个 member 可以借 many books，但一本 book 在同一时间只能被一个 member 借走。

Logical Model（更详细；无数据库特定信息）

现在，我们更清楚地定义 attributes 和 relationships：

  • Books：Book_ID、Title、Author、ISBN
  • Members：Member_ID、Name、Email
  • Borrowed_Books：Borrow_ID、Member_ID、Book_ID、Borrow_Date、Return_Date

Keys and Relationships：

  • Member_ID 和 Book_ID 是 Borrowed_Books 中的 foreign keys，分别连接到 Members 和 Books。
  • Book_ID 是 Books 中的 primary key。

Physical Model（实际数据库实现）

现在，我们决定如何在某种具体 database system 中存储数据，例如 MySQL 或 PostgreSQL。

CREATE TABLE Books (
Book_ID INT PRIMARY KEY,
Title VARCHAR(255),
Author VARCHAR(255),
ISBN VARCHAR(20)
);

CREATE TABLE Members (
Member_ID INT PRIMARY KEY,
Name VARCHAR(255),
Email VARCHAR(100)
);

CREATE TABLE Borrowed_Books (
Borrow_ID INT PRIMARY KEY,
Member_ID INT,
Book_ID INT,
Borrow_Date DATE,
Return_Date DATE,
FOREIGN KEY (Member_ID) REFERENCES Members(Member_ID),
FOREIGN KEY (Book_ID) REFERENCES Books(Book_ID)
);

Data Modeling Techniques

Data modeling 定义数据如何被逻辑结构化、关联，并针对 transactions、analytics 和 historical analysis 等不同 workloads 进行优化。因此，在数据工程中，选择正确 data model 很关键，因为它会直接影响 data quality、pipeline complexity、query performance 和 long-term scalability。

因此，与其只关注 database “schemas”，因为 schema 在不同平台中含义可能不同，data engineering patterns 更依赖成熟的数据建模技术，每种技术适合特定目的。

Relational Data Model（3NF：Normalized Model）

它最适合 OLTP systems。

Relational data model 将数据组织成 normalized tables，通常是 Third Normal Form，以便：

  • 最小化 data redundancy。
  • 确保 strong consistency 和 integrity。
  • 支持 frequent inserts、updates 和 deletes。

这个模型常用于 operational databases，例如 MySQL、PostgreSQL、Oracle 和 SQL Server，在这些系统中，transactional correctness 比 analytical query speed 更重要。

Dimensional Data Modeling

它最适合 OLAP 和 data warehouses。

Dimensional modeling 面向 analytical querying 和 reporting 设计。它围绕 business events 和 descriptive attributes 重组数据，以简化 queries 并提升 performance。

常见 patterns 包括：

Star Schema：一个 central fact table，也就是 events 或 measures，连接到 denormalized dimension tables，例如 customer、product、time，并针对 fast queries 和 BI tools 优化。

Snowflake Schema：Star schema 的 normalized variation，其中 dimensions 会拆分成 sub-dimensions；它以增加 joins 为代价，提升 storage efficiency。

Dimensional models 广泛用于现代 warehouses，例如 Snowflake、Amazon Redshift、BigQuery 和 Azure Synapse。

Data Vault Modeling

它最适合 enterprise-scale analytics 和 historical tracking。

Data Vault 是一种 hybrid data modeling technique，设计用于处理：

  • Rapid Schema Evolution
  • Multiple Source Systems
  • Full Historical Traceability

它将数据分为：

  • Hubs：Core Business Entities
  • Links：Entities 之间的 Relationships
  • Satellites：Descriptive 和 Historical Attributes

因此，Data Vault 正越来越多地被大型企业采用，作为 scalable、auditable foundation layer，并通常向下游 dimensional models 提供数据，用于 reporting。

因此，为了展示 data modeling 的实践，我们会扩展 library management system 示例，并使用 relational data model（Third Normal Form - 3NF）设计 database。这种 modeling technique 最适合 OLTP systems，因为 transactional integrity、consistency 和 frequent read-write operations 在其中非常关键。

这个例子展示的是 operational schema design，而不是面向 analytical 或 reporting 的 modeling，例如前面讨论的 star 或 snowflake schemas。

Entity（Table）Attributes（Columns）Primary KeyForeign Key
BooksBook_ID、Title、Author、ISBN、Copies_AvailableBook_ID
MembersMember_ID、Name、Email、Phone、Join_DateMember_ID
Borrowed_BooksBorrow_ID、Member_ID、Book_ID、Borrow_Date、Return_DateBorrow_IDMember_ID、Book_ID

表 2.6：Schema Design

One-to-Many（1:M）Relationships：

  • 一个 Member 可以随时间借阅 multiple Books。
  • 每条 Borrowed_Books record 会将一个 member 连接到一个 book instance。
  • Foreign keys 执行这些 relationships，并维护 tables 之间的 referential integrity。

下面是 SQL 实现：

CREATE TABLE Books (
Book_ID INT PRIMARY KEY,
Title VARCHAR(255) NOT NULL,
Author VARCHAR(255) NOT NULL,
ISBN VARCHAR(20) UNIQUE NOT NULL,
Copies_Available INT CHECK (Copies_Available >= 0)
);

CREATE TABLE Members (
Member_ID INT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Phone VARCHAR(15),
Join_Date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE Borrowed_Books (
Borrow_ID INT PRIMARY KEY AUTO_INCREMENT,
Member_ID INT NOT NULL,
Book_ID INT NOT NULL,
Borrow_Date DATE DEFAULT CURRENT_DATE,
Return_Date DATE,
FOREIGN KEY (Member_ID) REFERENCES Members(Member_ID),
FOREIGN KEY (Book_ID) REFERENCES Books(Book_ID)
);

Data Partitioning 和 Indexing 基础

Data partitioning 和 indexing 是核心优化技术，用于在处理 large datasets 时提升 query performance、scalability 和 cost efficiency。虽然底层目标在不同系统中相同，但 partitioning 和 indexing 的实现方式在 OLTP databases 和现代 cloud data warehouses（OLAP）之间差异很大。

因此，在 data engineering patterns 中，理解这些技术在哪里以及如何适用，比记住具体语法更重要。

Data Partitioning

Data partitioning 是一种将 large table 划分为更小、可管理部分的技术，这些部分称为 partitions，用于提升 performance 和 maintainability。

Types of Partitioning：

Range Partitioning：基于 value range 切分数据，例如按 publication year 划分 books。

List Partitioning：将数据归类到特定 groups，例如按 membership type 划分 members：student、faculty 等。

Hash Partitioning：使用 hash function 分布数据，适合均匀分散 records。

Composite Partitioning：组合两种或多种 partitioning methods。

现在，我们继续用 library 示例说明 partitioning 如何帮助我们。

由于图书馆随时间会有数百万 borrowing records，将所有记录存放在单一 table 中会拖慢 queries。我们可以基于 Borrow_Date 对数据进行 range partition，例如按年份。

CREATE TABLE Borrowed_Books (
Borrow_ID INT PRIMARY KEY,
Member_ID INT,
Book_ID INT,
Borrow_Date DATE NOT NULL,
Return_Date DATE
)
PARTITION BY RANGE (YEAR(Borrow_Date)) (
PARTITION p_before_2020 VALUES LESS THAN (2020),
PARTITION p_2020 VALUES LESS THAN (2021),
PARTITION p_2021 VALUES LESS THAN (2022),
PARTITION p_2022 VALUES LESS THAN (2023),
PARTITION p_recent VALUES LESS THAN (MAXVALUE)
);

因此，如果 librarian 需要查询过去的 borrowing records，就可以快速查询一个 partition，而不是扫描整张 table。

Indexing

Indexing 是一种通过创建 reference structure 来提升 data retrieval 速度的技术，它允许 database engine 更快定位 rows。

因此，index 不是扫描整张 table，而是创建一个指向实际数据位置的 lookup table。

以下列出了 indexing 的类型。同时，我们也会将这个概念扩展到 library 示例中。

Primary Index：创建在 primary key 上，例如 Books table 中的 Book_ID。

Unique Index：防止 duplicate values，例如 Books table 中的 ISBN。

Composite Index：同时索引多个 columns，例如 Title 和 Author，以加快 book searches。

Full-Text Index：用于高效搜索文本，例如按 description 搜索 books。

Clustered 和 Non-Clustered Indexes：决定数据在 table 中如何物理存储。

Index for Faster Book Searches：当你同时按 title 和 author 搜索 books 时，这会有帮助。

CREATE INDEX idx_books_title_author ON Books (Title, Author);

Index for Borrowed Books by Member：当我们查找某个 member 最近借了哪些 books 时，这会加速 queries。

CREATE INDEX idx_borrowed_books_member ON Borrowed_Books (Member_ID, Borrow_Date);

Full-Text Index for Searching Book Descriptions：如果 books 有 descriptions，full-text index 可以帮助按 keywords 查找 books。

CREATE FULLTEXT INDEX idx_books_description ON Books (Description);

结论

本章一开始，我们理解了如何选择合适的 data storage solution。对于 real-time transactions 和 structured data，应使用 databases（SQL / NoSQL）；对于 raw、unstructured 和 semi-structured Big Data storage，应使用 data lakes；对于 analytics 和 BI reporting，应使用 data warehouses。

随着我们继续讨论优化 data pipelines，也看到现代数据工程在绝大多数情况下都遵循 ELT-first approach，无论 use case 是 compliance-driven reporting、large-scale analytics、AI/ML、batch processing，还是 real-time insights。数据会以 raw form 被摄入，然后 transformation 被下推到可扩展 execution engines 中，例如 data warehouses、lakehouses 或 stream processors，同时利用它们的 compute、governance 和 optimization capabilities。ETL 作为主导模式已经在很大程度上被这种 push-down architecture 取代，在这种架构中，transformation 会发生在数据存储和处理的位置附近。

此外，我们理解了 data modeling 和 schema design 的类型。我们学到的一些关键点包括：对 transactional databases 进行 normalization，以确保 consistency 和 efficiency；对 analytics 进行 denormalization，以加快 queries；并在 BI 和 data warehousing 中使用 Star / Snowflake Schemas。

最后，我们学习了如何使用 partitioning 和 indexing 提升性能。对 large datasets 进行 partition，可以获得更好的 query performance；使用 indexes，则可以加速 lookups 和 filtering。

下一章中，我们将覆盖现代 data architectures，这是 data engineer 必须理解并能在组织中实施的内容。我们将理解 Lambda 和 Kappa data architecture，然后继续学习 data mesh 和 data fabric。此外，我们还会探索面向 real-time、scalable applications 的 agile architectures，最后学习 data lakehouse architecture，这是一种 data lakes 和 data warehouses 的现代混合形态。

avatar
文章
阅读
粉丝