warehouse-architect
Architect-level analytical storage design — warehouse versus lake versus lakehouse, dimensional and wide-table modelling, grain and keys, partitioning and clustering, medallion layering, and the governance that keeps a warehouse from becoming a swamp. Use when designing where analytical data lives and how it is modelled.
- License 许可
- MIT
- 最近更新
- 2026-11-13
- Stars
- ★ 0
- Forks
- 0
01 前言 Front Matter
- name
- warehouse-architect
- license
- MIT
- metadata
- { "crusor-path": "data-engineering-architect/storage-architect/warehouse-architect", "crusor-domain": "data-engineering-architect", "crusor-origin": "original", "crusor-sources": "docs.snowflake.com; cloud.google.com/bigquery/docs; iceberg.apache.org; databricks.com/discover/lakehouse", "crusor-triggers": "data warehouse, data lake, lakehouse, star schema, dimensional model, snowflake, bigquery, iceberg, partitioning, medallion, analytical model", "crusor-verified-on": "2026-10-30" }
- description
- Architect-level analytical storage design — warehouse versus lake versus lakehouse, dimensional and wide-table modelling, grain and keys, partitioning and clustering, medallion layering, and the governance that keeps a warehouse from becoming a swamp. Use when designing where analytical data lives and how it is modelled.
- compatibility
- Snowflake, BigQuery, Redshift, Databricks, Iceberg/Delta, DuckDB
03 SKILL.md 正文
Warehouse Architect
Analytical storage is a product with consumers, not a bucket. The design questions are grain, ownership, and what a query costs.
When to use
- Choosing warehouse/lake/lakehouse for analytics, BI, or ML features.
- A warehouse is slow, expensive, or full of near-duplicate tables.
- Defining layers (raw/silver/gold) and modelling conventions.
When NOT to use
- OLTP schema design for an application →
website-architect/database-*. - A single team's one-off analysis — a notebook and a temporary table.
Decision 1 — Warehouse, lake, or lakehouse
| Need | Choice |
|---|---|
| SQL BI, governed, moderate volume | warehouse |
| cheap bulk storage, many formats, ML | lake with table format |
| BI and ML on the same data | lakehouse (Iceberg/Delta) |
| local analysis, <100 GB | DuckDB or a warehouse free tier |
- Storage and compute separation is now table stakes; choose on ecosystem and governance, not on the brochure.
- Open table formats buy you exit rights — worth real money at scale.
Decision 2 — Layers
raw/bronze append-only, source-shaped, immutable
silver cleaned, typed, deduplicated, conformed keys
gold modelled, metric-ready, one row per grain
- Silver is where business keys are conformed — the highest-leverage layer and the one teams skip.
- Gold tables have a declared grain and an owner. If nobody owns it, it dies slowly and expensively.
- Do not let consumers read raw. That is how two teams compute two truths.
Decision 3 — Modelling
- Dimensional (star) — facts at a declared grain, conformed dimensions; best for BI and stable metrics.
- Wide/OBT — one big denormalised table per mart; fast to build, cheap to query, expensive to change.
- Data Vault — auditable hubs/links/satellites; heavy, justified by regulation and many sources.
- State the grain in the table name or docs (
fact_order_line, notorders_data).
Decision 4 — Physical layout
- Partition on the dominant filter (usually date); cluster/sort on the next most selective.
- Small partitions create overhead; huge partitions defeat pruning.
- Pre-aggregate where BI re-computes the same rollup thousands of times.
- Materialised views/streams for recurring transforms; watch refresh cost.
- Sort and compact small files — the most common silent cost in lakehouses.
Decision 5 — Governance
- Schemas per domain, not per person; naming conventions enforced by CI.
- PII tagged at the column level; masking policies follow the tag.
- Retention per table, automated, not remembered.
- Cost attribution by warehouse/role + query tags, reviewed monthly.
- Deprecation is a process: mark, announce, freeze, drop.
Failure modes at scale
- Three tables named
users,users_v2,users_final, all queried. - Gold tables with no grain: joins that fan out silently and inflate revenue.
- Partitioning by ingestion date while everyone filters on event date.
- Storage cost grows from copies of copies, each labelled a "backup".
- Nobody knows which dashboard depends on which table, so nothing is removable.
Verification receipts
- Layer diagram with owners and allowed readers per layer.
- Grain documented for every gold table; fan-out test on a join.
- Query plan showing partition pruning on a typical BI query.
- Cost report by warehouse/role for the last month.
- Deprecation list with consumers mapped and dates agreed.
04 同仓库更多 Skills
interaction-patterns-architect
Architect-level interaction design in code — choosing established patterns over invented ones, feedback and latency behaviour, destructive-action safeguards, navigation and overlay conventions, and the state model behind every interactive surface. Use when a screen has to behave, not just look.
design-system-ops-architect
Architect-level operation of a design system in code — token pipelines and versioning, component API and variant policy, the catalogue as the contract, adoption metrics, and the governance that stops a system from forking into three. Use when a component library must survive several teams.
design-handoff-architect
Architect-level design handoff — what must be specified before implementation starts, tokens over raw values, responsive and content-limit contracts, state matrices, and the review loop that catches drift between design and code. Use when a design is about to become code.
content-microcopy-architect
Architect-level UI content — where strings live, voice and terminology consistency, error and empty-state copy as part of the interface, translation-ready authoring, and the content model a product needs before it multiplies screens. Use when the words in the interface matter as much as the pixels.
ui-ux-implementation-architect
Architect-level direction for turning a design into working UI — the handoff contract, component ownership versus screen-level layout, interaction and state conventions, design QA gates, and the review habits that keep an implementation faithful without frozen pixels. Use before any specific UI implementation skill.
test-strategy-architect
Architect-level test strategy — allocating assertions across unit, integration, contract and end-to-end layers, designing for testability through dependency boundaries, fixture and data strategy, and the flake and coverage policies that keep a suite trustworthy. Use when defining how a codebase is tested.