data-warehouseUse when designing, building, or optimizing enterprise data warehouses — Kimball vs Inmon methodology, dimensional and normalized modeling, slowly changing dimensions, ETL/ELT pipeline design, partitioning and distribution strategies, materialized views, query optimization, data vault 2.0, warehouse platforms (PostgreSQL/Snowflake/Redshift/BigQuery/Synapse/…
Independent GitHub snapshot · Automated classification · Original documentation governs
Companion skills: python-data-engineer (SQLAlchemy, Alembic, query optimization), python-enterprise-connectors (DB2, Oracle, SQL Server, mainframe integration), rhel-databases / ubuntu-databases (PostgreSQL/MySQL administration).
Detailed code examples, patterns, and configuration are in the reference files below. Read the relevant file when working on that area.
| File | Covers |
|---|---|
| etl-scd-optimization.md | Data Vault 2.0, ETL/ELT design, slowly changing dimensions, partitioning/distribution strategies, and query optimization |
| methodology-modeling.md | Kimball vs Inmon methodology, architecture patterns, dimensional modeling (star/snowflake schemas, fact/dimension tables) |
| operations-testing-platforms.md | platform-specific guidance (Snowflake, BigQuery, Redshift, Databricks), operational patterns (refresh strategies, monitoring, disaster recovery), and testing/quality |
| Anti-Pattern | Why It Fails | Correct Approach |
|---|---|---|
| Using 3NF (normalized) design for user-facing query layers | Joins across 10+ normalized tables destroy query performance; business users cannot write their own queries | Use dimensional modeling (star schema) for consumption layer; normalize only in staging/integration layers |
| No slowly changing dimension strategy | Overwriting dimension attributes loses history; reports retroactively change when a customer moves or a product renames | Choose SCD type per attribute: Type 1 for corrections, Type 2 for historically significant changes, Type 3 for limited history |
| Loading the warehouse during business hours | ETL competes with queries for resources; users experience slow dashboards; lock contention causes failures | Schedule loads during off-hours batch windows; use change data capture (CDC) for near-real-time if business requires it |
| No materialized views or pre-aggregated tables for common queries | Dashboard queries recompute the same aggregations on millions of rows every time; slow and wasteful | Create materialized views for high-frequency query patterns; refresh on schedule; index aggregation columns |
| Skipping data quality checks between ETL stages | Bad data from source systems propagates into production reports; executives lose trust in the numbers | Implement data quality gates at each ETL stage: null checks, range validation, referential integrity, row count reconciliation |
| Domain | Skill |
|---|---|
| SQLAlchemy, Alembic, query optimization | python-data-engineer |
| DB2, Oracle, SQL Server, mainframe connectors | python-enterprise-connectors |
| PostgreSQL/MySQL/Redis on RHEL 9 | rhel-databases |
| PostgreSQL/MySQL/Redis on Ubuntu 24.04 | ubuntu-databases |
| Docker containers for dev/test warehouses | docker-compose-patterns |
| ETL/pipeline parallelism in Python | python-parallelism |
| Data pipeline profiling and load testing | performance |