Expert data modeling covering star schema, snowflake schema, Data Vault 2.0, dimensional modeling, slowly changing dimensions, bridge tables, fact table types, conformed dimensions, ERD creation, and modeling tool usage for building enterprise-grade analytical and operational data models.
Use when the user asks about data modeler, data modeler best practices, or needs guidance on data modeler implementation.
Do NOT use when the user needs a different specialized skill or is asking about an unrelated technology domain.
Expert data modeling covering star schema, snowflake schema, Data Vault 2.0, dimensional modeling, slowly changing dimensions, bridge tables, fact table types, conformed dimensions, ERD creation, and modeling tool usage for building enterprise-grade analytical and operational data models.
Use when the user asks about data modeler, data modeler best practices, or needs guidance on data modeler implementation.
Do NOT use when the user needs a different specialized skill or is asking about an unrelated technology domain.
Data modeling is the discipline of designing the structure, relationships, and constraints of data to support both operational and analytical workloads. This skill covers the full spectrum from third normal form (3NF) operational models through dimensional models for analytics and Data Vault for enterprise data integration.
Dimensional Modeling (Kimball Methodology)
Star Schema
The star schema is the foundation of dimensional modeling. A central fact table connects to denormalized dimension tables via foreign keys.
Dimension tables are very large (>10M rows) and share sub-dimensions
Storage cost is a primary concern
Query patterns always filter on sub-dimension attributes
ETL team prefers normalized source-of-truth dimensions
When to prefer star:
Query performance is the priority (fewer joins)
BI tools work better with flat dimensions
Dimension tables are small-to-medium (<1M rows)
Team prefers simplicity
Data Vault 2.0
Data Vault is an enterprise modeling methodology designed for agility, auditability, and parallel loading.
Core Components
-- HUB: Business keys (immutable once loaded)CREATE TABLE hub_customer (
hub_customer_hk CHAR(32) PRIMARY KEY, -- Hash of business key
customer_bk VARCHAR(50) NOT NULL, -- Business key
load_date TIMESTAMPNOT NULL,
record_source VARCHAR(100) NOT NULL,
UNIQUE (customer_bk)
);
-- LINK: Relationships between hubsCREATE TABLE link_order (
link_order_hk CHAR(32) PRIMARY KEY, -- Hash of all parent HKs
hub_customer_hk CHAR(32) NOT NULLREFERENCES hub_customer,
hub_product_hk CHAR(32) NOT NULLREFERENCES hub_product,
hub_store_hk CHAR(32) NOT NULLREFERENCES hub_store,
order_bk VARCHAR(50) NOT NULL, -- Degenerate key
load_date TIMESTAMPNOT NULL,
record_source VARCHAR(100) NOT NULL,
UNIQUE (hub_customer_hk, hub_product_hk, hub_store_hk, order_bk)
);
-- SATELLITE: Descriptive attributes (change tracked)CREATE TABLE sat_customer_details (
hub_customer_hk CHAR(32) NOT NULLREFERENCES hub_customer,
load_date TIMESTAMPNOT NULL,
load_end_date TIMESTAMPDEFAULT'9999-12-31',
record_source VARCHAR(100) NOT NULL,
hash_diff CHAR(32) NOT NULL, -- Hash of all attributes-- Descriptive attributes
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(200),
phone VARCHAR(20),
tier VARCHAR(20),
PRIMARY KEY (hub_customer_hk, load_date)
);
-- EFFECTIVITY SATELLITE: Tracks relationship validity over timeCREATE TABLE eff_sat_order (
link_order_hk CHAR(32) NOT NULLREFERENCES link_order,
load_date TIMESTAMPNOT NULL,
load_end_date TIMESTAMPDEFAULT'9999-12-31',
record_source VARCHAR(100) NOT NULL,
is_active BOOLEANNOT NULLDEFAULTTRUE,
PRIMARY KEY (link_order_hk, load_date)
);
-- POINT-IN-TIME (PIT) table: precomputed join optimizationCREATE TABLE pit_customer (
hub_customer_hk CHAR(32) NOT NULL,
snapshot_date TIMESTAMPNOT NULL,
sat_customer_details_ldts TIMESTAMP,
sat_customer_contact_ldts TIMESTAMP,
sat_customer_finance_ldts TIMESTAMP,
PRIMARY KEY (hub_customer_hk, snapshot_date)
);
Data Vault Loading Pattern
import hashlib
defhash_key(*business_keys):
"""Generate hash key from business key components."""
concatenated = '||'.join(str(k).strip().upper() for k in business_keys)
# MD5 used for non-cryptographic checksum/routing only. Do NOT use MD5 for passwords or security.return hashlib.md5(concatenated.encode('utf-8')).hexdigest()
defhash_diff(**attributes):
"""Generate hash diff from satellite attributes for change detection."""
concatenated = '||'.join(
str(attributes.get(k, '')).strip()
for k insorted(attributes.keys())
)
# MD5 used for non-cryptographic checksum only. Do NOT use MD5 for passwords or security.return hashlib.md5(concatenated.encode('utf-8')).hexdigest()
defload_hub(source_df, hub_table, business_key_col, record_source, engine):
"""Load hub table - insert only new business keys."""
source_df['hub_hk'] = source_df[business_key_col].apply(hash_key)
source_df['load_date'] = pd.Timestamp.utcnow()
source_df['record_source'] = record_source
# Only insert keys not already present
sql = f"""
INSERT INTO {hub_table} (hub_hk, business_key, load_date, record_source)
SELECT hub_hk, business_key, load_date, record_source
FROM staging
WHERE hub_hk NOT IN (SELECT hub_hk FROM {hub_table})
"""
Bridge tables resolve many-to-many relationships between facts and dimensions.
-- Patient can have multiple diagnoses per visitCREATE TABLE bridge_diagnosis (
diagnosis_group_key INTNOT NULL,
diagnosis_key INTNOT NULLREFERENCES dim_diagnosis,
diagnosis_rank SMALLINTNOT NULL, -- primary=1, secondary=2, etc.
weighting_factor NUMERIC(5,4) NOT NULL, -- weights sum to 1.0 per groupPRIMARY KEY (diagnosis_group_key, diagnosis_key)
);
-- Fact table references the bridge groupCREATE TABLE fact_medical_visit (
visit_key BIGINTPRIMARY KEY,
patient_key INTREFERENCES dim_patient,
provider_key INTREFERENCES dim_provider,
date_key INTREFERENCES dim_date,
diagnosis_group_key INTNOT NULL, -- FK to bridge
total_charge NUMERIC(12,2)
);
-- Query using bridge with weighting to avoid double-countingSELECT
d.diagnosis_name,
SUM(f.total_charge * b.weighting_factor) AS weighted_charges
FROM fact_medical_visit f
JOIN bridge_diagnosis b ON f.diagnosis_group_key = b.diagnosis_group_key
JOIN dim_diagnosis d ON b.diagnosis_key = d.diagnosis_key
GROUPBY d.diagnosis_name;
Conformed Dimensions
Conformed dimensions are shared across multiple fact tables and business processes, ensuring consistent reporting.
Design Principles
Single source of truth: One dim_customer used by sales, support, marketing
Consistent grain: Same business key definition across all consumers
Shared attributes: Common descriptive columns with identical meanings
Bus matrix: Documents which dimensions are used by which business processes
Business Process Matrix (Bus Matrix):
| dim_date | dim_customer | dim_product | dim_store | dim_employee |
---------------------+----------+--------------+-------------+-----------+--------------+
fact_sales | X | X | X | X | X |
fact_inventory | X | | X | X | |
fact_customer_svc | X | X | X | | X |
fact_web_clickstream | X | X | X | | |
fact_hr_attendance | X | | | X | X |
Grain statement: "One row per [X] per [Y] per [Z]"
Source mapping: Which source systems feed each column
Business rules: How derived columns are calculated
SCD strategy: Which type applies to each dimension attribute
Refresh frequency: How often the table is updated
Row count estimates: Expected volume and growth rate
Partitioning strategy: If applicable for the target platform
Modeling Anti-Patterns
Centipede fact table: Too many dimensions joined directly; use bridge tables or junk dimensions
Enterprise bus without conformed dimensions: Siloed facts that cannot be compared
Overloaded dimensions: Mixing unrelated entities in one dimension (customer + vendor)
Missing surrogate keys: Using natural keys as PKs prevents SCD Type 2
Null foreign keys: Use "Unknown" or "Not Applicable" dimension rows instead
Inappropriate grain: Mixing daily and monthly granularity in the same fact table
Header/line fact split: Breaking an order into separate header and line fact tables when a single line-level fact suffices
When to Use
Use this skill when:
Designing or implementing data modeler solutions
Reviewing or improving existing data modeler approaches
Making architectural or implementation decisions about data modeler
Learning data modeler patterns and best practices
Troubleshooting data modeler-related issues
Do NOT use this skill when:
The question is about a fundamentally different technology domain
A more specific sibling skill covers the exact topic needed
The user needs a complete hands-on tutorial rather than expert guidance
Output Format
# Data Modeler Analysis## Context Assessment
[Situation summary and constraints]
## Recommended Approach
[Primary recommendation with rationale]
## Implementation Steps1. [Step with specific details]
2. [Step with specific details]
3. [Step with specific details]
## Trade-offs and Considerations- [Key trade-off 1]
- [Key trade-off 2]
## Next Steps- [Immediate action item]
- [Follow-up action item]
Example
Input: "Help me implement data modeler for a medium-scale production application"
Output: A structured analysis covering current state assessment, recommended data modeler approach with specific patterns, implementation roadmap with milestones, and risk mitigation strategies tailored to the application scale and constraints.
Edge Cases
Legacy system integration: When data modeler must coexist with legacy approaches, provide a gradual migration path rather than a complete rewrite
Scale mismatch: When the solution complexity exceeds the project scale, recommend a simpler approach and note when to revisit
Team skill gaps: When the team lacks experience with the recommended approach, include learning resources and simpler alternatives
Conflicting requirements: When constraints conflict (e.g., performance vs. maintainability), explicitly state the trade-off and recommend based on stated priorities
