@mgpowerlytics/database-design

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