{"id":"architecting-data","name":"architecting-data","summary":"最新のデータプラットフォーム設計のための戦略的指針、ストレージパラダイム(データレイク、ウェアハウス、レイクハウス)、モデリング手法(次元、正規化、データボールト、ワイドテーブル)、データメッシュの原則、メダリオンアーキテクチャパターンをカバーします。","body":"# Data Architecture\n\n## Purpose\n\nGuide architects and platform engineers through strategic data architecture decisions for modern cloud-native data platforms.\n\n## When to Use This Skill\n\nInvoke this skill when:\n- Designing a new data platform or modernizing legacy systems\n- Choosing between data lake, data warehouse, or data lakehouse\n- Deciding on data modeling approaches (dimensional, normalized, data vault, wide tables)\n- Evaluating centralized vs data mesh architecture\n- Selecting open table formats (Apache Iceberg, Delta Lake, Apache Hudi)\n- Designing medallion architecture (bronze, silver, gold layers)\n- Implementing data governance and cataloging\n\n## Core Concepts\n\n### 1. Storage Paradigms\n\nThree primary patterns for analytical data storage:\n\n**Data Lake:** Centralized repository for raw data at scale\n- Schema-on-read, cost-optimized ($0.02-0.03/GB/month)\n- Use when: Diverse data sources, exploratory analytics, ML/AI training data\n\n**Data Warehouse:** Structured repository optimized for BI\n- Schema-on-write, ACID transactions, fast queries\n- Use when: Known BI requirements, strong governance needed\n\n**Data Lakehouse:** Hybrid combining lake flexibility with warehouse reliability\n- Open table formats (Iceberg, Delta Lake), ACID on object storage\n- Use when: Mixed BI + ML workloads, cost optimization (60-80% cheaper than warehouse)\n\n**Decision Framework:**\n- BI/Reporting only + Known queries → Data Warehouse\n- ML/AI primary + Raw data needed → Data Lake or Lakehouse\n- Mixed BI + ML + Cost optimization → Data Lakehouse (recommended)\n- Exploratory/Unknown use cases → Data Lake\n\nFor detailed comparison, see [references/storage-paradigms.md](references/storage-paradigms.md).\n\n### 2. Data Modeling Approaches\n\nFour primary modeling patterns:\n\n**Dimensional (Kimball):** Star/snowflake schemas for BI\n- Use when: Known query patterns, BI dashboards, trend analysis\n\n**Normalized (3NF):** Eliminate redundancy for transactional systems\n- Use when: OLTP systems, frequent updates, strong consistency\n\n**Data Vault 2.0:** Flexible model with complete audit trail\n- Use when: Compliance requirements, multiple sources, agile warehousing\n\n**Wide Tables:** Denormalized, optimized for columnar storage\n- Use when: ML feature stores, data science notebooks, high-performance dashboards\n\n**Decision Framework:**\n- Analytical (BI) + Known queries → Dimensional (Star Schema)\n- Transactional (OLTP) → Normalized (3NF)\n- Compliance/Audit → Data Vault 2.0\n- Data Science/ML → Wide Tables\n\nFor detailed patterns, see [references/modeling-approaches.md](references/modeling-approaches.md).\n\n### 3. Data Mesh Principles\n\nDecentralized architecture for large organizations (>500 people).\n\n**Four Core Principles:**\n1. Domain-oriented decentralization\n2. Data as a product (SLAs, quality, documentation)\n3. Self-serve data infrastructure\n4. Federated computational governance\n\n**Readiness Assessment (Score 1-5 each):**\n1. Domain clarity\n2. Team maturity\n3. Platform capability\n4. Governance maturity\n5. Scale need\n6. Organizational buy-in\n\n**Scoring:** 24-30: Strong candidate | 18-23: Hybrid | 12-17: Build foundation first | 6-11: Centralized\n\n**Red Flags:** Small org (<100 people), unclear domains, no platform team, weak governance\n\nFor full guide, see [references/data-mesh-guide.md](references/data-mesh-guide.md).\n\n### 4. Medallion Architecture\n\nStandard lakehouse pattern: Bronze (raw) → Silver (cleaned) → Gold (business-level)\n\n**Bronze Layer:** Exact copy of source data, immutable, append-only\n\n**Silver Layer:** Validated, deduplicated, typed data\n\n**Gold Layer:** Business logic, aggregates, dimensional models, ML features\n\n**Data Quality by Layer:**\n- Bronze → Silver: Schema validation, type checks, deduplication\n- Silver → Gold: Business rule validation, referential integrity\n- Gold: Anomaly detection, statistical checks\n\nFor patterns, see [references/medallion-pattern.md](references/medallion-pattern.md).\n\n### 5. Open Table Formats\n\nEnable ACID transactions on data lakes:\n\n**Apache Iceberg:** Multi-engine, vendor-neutral (Context7: 79.7 score)\n- Use when: Avoid vendor lock-in, multi-engine flexibility\n\n**Delta Lake:** Databricks ecosystem, Spark-optimized\n- Use when: Committed to Databricks\n\n**Apache Hudi:** Optimized for CDC and frequent upserts\n- Use when: CDC-heavy workloads\n\n**Recommendation:** Apache Iceberg for new projects (vendor-neutral, broadest support)\n\nFor comparison, see [references/table-formats.md](references/table-formats.md).\n\n### 6. Modern Data Stack\n\n**Standard Layers:**\n- Ingestion: Fivetran, Airbyte, Kafka\n- Storage: Snowflake, Databricks, BigQuery\n- Transformation: dbt (Context7: 87.0 score), Spark\n- Orchestration: Airflow, Dagster, Prefect\n- Visualization: Tableau, Looker, Power BI\n- Governance: DataHub, Alation, Great Expectations\n\n**Tool Selection:**\n- Fivetran vs Airbyte: Pre-built connectors vs cost-sensitive\n- Snowflake vs Databricks: BI-focused vs ML-focused\n- dbt vs Spark: SQL-based vs large-scale processing\n\nFor detailed recommendations, see [references/tool-recommendations.md](references/tool-recommendations.md) and [references/modern-data-stack.md](references/modern-data-stack.md).\n\n### 7. Data Governance\n\n**Data Catalog:** Searchable inventory (DataHub, Alation, Collibra)\n\n**Data Lineage:** Track data flow (OpenLineage, Marquez)\n\n**Data Quality:** Validation and testing (Great Expectations, Soda, dbt tests)\n\n**Access Control:**\n- RBAC: Role-based (sales_analyst role)\n- ABAC: Attribute-based (row-level security)\n- Column-level: Dynamic data masking for PII\n\nFor governance patterns, see [references/governance-patterns.md](references/governance-patterns.md).\n\n## Decision Frameworks\n\n### Framework 1: Storage Paradigm Selection\n\n**Step 1: Identify Primary Use Case**\n- BI/Reporting only → Data Warehouse\n- ML/AI primary → Data Lake or Lakehouse\n- Mixed BI + ML → Data Lakehouse\n- Exploratory → Data Lake\n\n**Step 2: Evaluate Budget**\n- High budget, known queries → Data Warehouse\n- Cost-sensitive, flexible → Data Lakehouse\n\n**Recommendation by Org Size:**\n- Startup (<50): Data Warehouse (simplicity)\n- Growth (50-500): Data Lakehouse (balance)\n- Enterprise (>500): Hybrid or unified Lakehouse\n\nSee [references/decision-frameworks.md](references/decision-frameworks.md#storage-paradigm).\n\n### Framework 2: Data Modeling Approach\n\n**Decision Tree:**\n- Analytical (BI) workload → Dimensional or Wide Tables\n- Transactional (OLTP) → Normalized (3NF)\n- Compliance/Audit → Data Vault 2.0\n- Data Science/ML → Wide Tables\n\nSee [references/decision-frameworks.md](references/decision-frameworks.md#modeling-approach).\n\n### Framework 3: Data Mesh Readiness\n\nUse 6-factor assessment. Score interpretation:\n- 24-30: Proceed with data mesh\n- 18-23: Hybrid approach\n- 12-17: Build foundation first\n- 6-11: Centralized\n\nSee [references/decision-frameworks.md](references/decision-frameworks.md#data-mesh-readiness).\n\n### Framework 4: Open Table Format Selection\n\n**Decision Tree:**\n- Multi-engine flexibility → Apache Iceberg\n- Databricks ecosystem → Delta Lake\n- Frequent upserts/CDC → Apache Hudi\n\n**Recommendation:** Apache Iceberg for new projects\n\nSee [references/decision-frameworks.md](references/decision-frameworks.md#table-format).\n\n## Common Scenarios\n\n### Startup Data Platform\n\n**Context:** 50-person startup, PostgreSQL + MongoDB + Stripe\n\n**Recommendation:**\n- Storage: BigQuery or Snowflake\n- Ingestion: Airbyte or Fivetran\n- Transformation: dbt\n- Orchestration: dbt Cloud\n- Architecture: Simple data warehouse\n\nSee [references/scenarios.md](references/scenarios.md#startup).\n\n### Enterprise Modernization\n\n**Context:** Legacy Oracle warehouse, need cloud migration\n\n**Recommendation:**\n- Storage: Data Lakehouse (Databricks or Snowflake with Iceberg)\n- Strategy: Incremental migration with CDC\n- Architecture: Medallion (bronze, silver, gold)\n- Cost Savings: 60-80%\n\nSee [references/scenarios.md](references/scenarios.md#enterprise-modernization).\n\n### Data Mesh Assessment\n\n**Context:** 200-person company, 5-person central data team\n\n**Recommendation:** NOT YET. Build foundation first.\n- Organization too small (<500 recommended)\n- Central team not yet bottleneck\n- Invest in self-serve platform and governance\n\nSee [references/scenarios.md](references/scenarios.md#data-mesh).\n\n## Tool Recommendations\n\n### Research-Validated (Context7, December 2025)\n\n**dbt:** Score 87.0, 3,532+ code snippets\n- SQL-based transformations, version control, testing\n- Industry standard for data transformation\n\n**Apache Iceberg:** Score 79.7, 832+ code snippets\n- Open table format, multi-engine, vendor-neutral\n- Production-ready (Netflix, Apple, Adobe)\n\n**Tool Stack by Use Case:**\n\n**Startup:** BigQuery + Airbyte + dbt + Metabase (<$1K/month)\n\n**Growth:** Snowflake + Fivetran + dbt + Airflow + Tableau ($10K-50K/month)\n\n**Enterprise:** Snowflake + Databricks + Fivetran + Kafka + dbt + Airflow + Alation ($50K-500K/month)\n\nSee [references/tool-recommendations.md](references/tool-recommendations.md).\n\n## Implementation Patterns\n\n### Pattern 1: Medallion Architecture\n\n```sql\n-- Bronze: Raw ingestion\nCREATE TABLE bronze.raw_customers (_ingested_at TIMESTAMP, _raw_data STRING);\n\n-- Silver: Cleaned\nCREATE TABLE silver.customers AS\nSELECT json_extract(_raw_data, '$.id') AS customer_id, ...\nFROM bronze.raw_customers\nQUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY _ingested_at DESC) = 1;\n\n-- Gold: Business-level\nCREATE TABLE gold.fact_sales AS\nSELECT s.order_id, d.date_key, c.customer_key, ...\nFROM silver.sales s\nJOIN gold.dim_date d ON s.order_date = d.date;\n```\n\n### Pattern 2: Apache Iceberg Table\n\n```sql\nCREATE TABLE catalog.db.sales (order_id BIGINT, amount DECIMAL(10,2))\nUSING iceberg\nPARTITIONED BY (days(order_date));\n\n-- Time travel\nSELECT * FROM catalog.db.sales TIMESTAMP AS OF '2025-01-01';\n```\n\n### Pattern 3: dbt Transformation\n\n```sql\n-- models/staging/stg_customers.sql\nWITH source AS (SELECT * FROM {{ source('raw', 'customers') }}),\ncleaned AS (\n  SELECT customer_id, UPPER(customer_name) AS customer_name\n  FROM source WHERE customer_id IS NOT NULL\n)\nSELECT * FROM cleaned\n```\n\nFor complete examples, see [examples/](examples/).\n\n## Best Practices\n\n1. **Start simple:** Avoid over-engineering; begin with warehouse or basic lakehouse\n2. **Invest in governance early:** Catalog, lineage, quality from day one\n3. **Medallion architecture:** Use bronze-silver-gold for clear quality layers\n4. **Open table formats:** Prefer Iceberg or Delta Lake to avoid vendor lock-in\n5. **Assess mesh readiness:** Don't decentralize prematurely (<500 people)\n6. **Automate quality:** Integrate tests (Great Expectations, dbt) into CI/CD\n7. **Monitor pipelines:** Observability is critical (freshness, quality, health)\n8. **Document as code:** Use dbt docs, DataHub, YAML for self-service\n9. **Incremental loading:** Only load new/changed data (watermark columns)\n10. **Business alignment:** Align architecture to outcomes, not just technologies\n\n## Anti-Patterns\n\n- ❌ Data swamp: Lake without governance or cataloging\n- ❌ Premature mesh: Mesh before organizational readiness\n- ❌ Tool sprawl: Too many tools without integration\n- ❌ No quality checks: \"Garbage in, garbage out\"\n- ❌ Centralized bottleneck: Single team in large org (>500 people)\n- ❌ Vendor lock-in: Proprietary formats without migration path\n- ❌ No lineage: Can't answer \"where did this come from?\"\n- ❌ Over-engineering: Complex architecture for simple use cases\n\n## Integration with Other Skills\n\n**Direct Dependencies:**\n- **ingesting-data:** ETL/ELT mechanics, Fivetran, Airbyte implementation\n- **data-transformation:** dbt and Dataform detailed implementation\n- **streaming-data:** Kafka, Flink for real-time pipelines\n\n**Complementary:**\n- **databases-relational:** PostgreSQL, MySQL as source systems\n- **databases-document:** MongoDB, DynamoDB as sources\n- **ai-data-engineering:** Feature stores, ML training pipelines\n- **designing-distributed-systems:** CAP theorem, consistency models\n- **observability:** Monitoring pipeline health, data quality metrics\n\n**Downstream:**\n- **visualizing-data:** BI and dashboard patterns\n- **sql-optimization:** Query performance tuning\n\n**Common Workflows:**\n\n**End-to-End Analytics:**\n```\ndata-architecture (warehouse) → ingesting-data (Fivetran) →\ndata-transformation (dbt) → visualizing-data (Tableau)\n```\n\n**Data Platform for AI/ML:**\n```\ndata-architecture (lakehouse) → ingesting-data (Kafka) →\ndata-transformation (dbt features) → ai-data-engineering (feature store)\n```\n\n## Further Reading\n\n**Reference Files:**\n- [decision-frameworks.md](references/decision-frameworks.md) - All 4 decision frameworks in detail\n- [storage-paradigms.md](references/storage-paradigms.md) - Lake vs warehouse vs lakehouse\n- [modeling-approaches.md](references/modeling-approaches.md) - Dimensional, normalized, data vault, wide\n- [data-mesh-guide.md](references/data-mesh-guide.md) - Data mesh principles and implementation\n- [medallion-pattern.md](references/medallion-pattern.md) - Bronze, silver, gold layers\n- [table-formats.md](references/table-formats.md) - Iceberg, Delta Lake, Hudi comparison\n- [tool-recommendations.md](references/tool-recommendations.md) - Tool analysis and recommendations\n- [modern-data-stack.md](references/modern-data-stack.md) - Tool categories and selection\n- [governance-patterns.md](references/governance-patterns.md) - Catalog, lineage, quality, access control\n- [scenarios.md](references/scenarios.md) - Startup, enterprise, data mesh scenarios\n\n**Examples:**\n- [examples/dbt-project/](examples/dbt-project/) - dbt project with medallion architecture\n\n**External Resources:**\n- Apache Iceberg: https://iceberg.apache.org/\n- dbt Documentation: https://docs.getdbt.com/\n- Data Mesh (Zhamak Dehghani): https://www.datamesh-architecture.com/\n- Databricks Medallion: https://www.databricks.com/glossary/medallion-architecture","author":"@ancoleman","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/ancoleman/ai-design-components/tree/main/skills/architecting-data","license":"MIT","category":"writing","lang":"en","tokens":3367,"stars":0,"calls30d":1,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[{"path":"examples/dbt-project/README.md","size":925,"sha256":"a5d749e2c2186140ac7bf2c16ab5e58b854a1a580ed86893649daab095d2e01d"},{"path":"examples/dbt-project/stg_customers.sql","size":380,"sha256":"d34440f6c7940c1c35b2f62d1992c3900dc1ab22be410330cfc86b2208d3a077"},{"path":"outputs.yaml","size":7730,"sha256":"af4b9dde91986da294b2e49bcc4012c6fdcf5208c84f9fe7cc885253e7e6437d"},{"path":"references/data-mesh-guide.md","size":3610,"sha256":"ab6e0584a01130862b841789fb15a057be865166794eccfd686b8b895bf447d0"},{"path":"references/decision-frameworks.md","size":14768,"sha256":"171e6b1d3bf4d0e6c8c5d4206b96ba8b2a7dfa3eb22ac58d9bf17de6fcce6ef0"},{"path":"references/governance-patterns.md","size":3743,"sha256":"14bd517a7fd505729be392e04a9e89348d4a11e2a8960635dd5394e2c312adb6"},{"path":"references/medallion-pattern.md","size":3319,"sha256":"6a10247a550b37936483d14e74cb0ef666ff3b4ba58ffd18cae08f81ef521128"},{"path":"references/modeling-approaches.md","size":13076,"sha256":"b9fa8c0b89096c60db04afd1741bb36874fb083d84b905a762d962e27d8bda6b"},{"path":"references/modern-data-stack.md","size":5942,"sha256":"f43aa907ca5c6918d6e3d6bfe19c507dcdc054f902e01673692f4f2ccfdb21e0"},{"path":"references/scenarios.md","size":7299,"sha256":"18df14ded798038da2a687ff98a03c71cc4dd2f0a80731ba01b809adf4b9fc20"},{"path":"references/storage-paradigms.md","size":14247,"sha256":"9bbae7ea9392bcbfc09111c8b83fbc48f4c9578729e8bfb1092d025fe7ffc38a"},{"path":"references/table-formats.md","size":1541,"sha256":"938864dd203fc0218b53050d7993c3947b3ff3fbed4beeea70f7795cfc1ed3cd"},{"path":"references/tool-recommendations.md","size":11130,"sha256":"2b01fd358044ee6e0daa045c05bcbc22a968c84f65416f14c60668c8451c1721"}],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":["docs.getdbt.com","iceberg.apache.org","www.databricks.com","www.datamesh-architecture.com"]}}