AgentSkillsCN

database-design

为设计、实施与维护高质量数据模型提供指导，确保数据完整性、性能与可扩展性。

SKILL.md
--- frontmatter
name: database-design
description: Guidelines for designing, implementing, and maintaining high-quality data models, ensuring data integrity, performance, and scalability.
version: 1.0.0

Data Modeling & Design Maintenance

🎯 Purpose

To design, implement, and maintain high-quality data models that ensure data integrity, performance, and ease of use for downstream analytics and machine learning.

🏗️ Design Principles

1. Architectural Standards

  • •Modular Design: Use a layered approach (e.g., Bronze/Silver/Gold or Staging/Intermediate/Mart).
  • •Star Schema Preference: For BI layers, prioritize Fact and Dimension tables to optimize join performance and readability.
  • •Idempotency: Every transformation must be repeatable. If run multiple times with the same input, it must produce the same output.

2. Technical Requirements

  • •Primary Keys: Every table must have a defined Primary Key (composite or surrogate).
  • •Naming Conventions: Use snake_case. Prefix tables based on layer (e.g., stg_, fct_, dim_).
  • •Data Types: Use the most efficient types possible (e.g., INT vs BIGINT) and ensure consistent timestamp formats (UTC preferred).

🛠️ Implementation Workflow

Step 1: Requirements Gathering

  • •Identify the grain of the table (e.g., "One row per transaction").
  • •Define the business logic for every calculated field.

Step 2: DDL & Schema Design

  • •Apply constraints where supported (NOT NULL, UNIQUE).
  • •Document columns using descriptions within the code or yml files.

Step 3: Orchestration Integration (Airflow 3.x)

  • •Dynamic Task Mapping: Use Airflow 3.x features to scale model processing across partitions.
  • •Task Flow API: Use decorators for Python-based transformations to maintain clean, readable DAGs.
  • •Retries: Configure sensible retry logic for transient failures during model builds.

🧹 Maintenance & Governance

Quality Assurance

  • •Schema Tests: Run tests for null values, uniqueness, and referential integrity (e.g., using dbt tests or Airflow SQL sensors).
  • •Volume Monitoring: Alert if row counts deviate significantly from historical averages.

Refactoring Logic

  • •Impact Analysis: Use lineage tools to identify which downstream reports will be affected by a schema change.

📝 Quality Checklist

  • • Is the grain clearly defined?
  • • Are all columns documented?
  • • Does the model handle incremental loads correctly?
  • • Have you verified the join logic doesn't cause fan-out (duplicate rows)?