majiayu000 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)?