Designing Data Warehouse Architecture
1. Designing Data Warehouse Schema
| Schema | Description |
|---|---|
| Star | Central fact + denormalized dimensions |
| Snowflake | Normalized dimensions |
| Galaxy / Constellation | Multiple facts share dims |
| Data Vault | Hubs/Links/Satellites; auditable |
| One Big Table (OBT) | Wide table for analytic engines |
2. Designing ETL vs ELT Strategy
| Aspect | ETL | ELT |
|---|---|---|
| Transform Location | Before load | In warehouse |
| Compute | Spark cluster | Snowflake/BigQuery |
| Tooling | Informatica, Talend | dbt, Fivetran + dbt |
| Best for | Sensitive PII filtering early | Modern cloud DW |
3. Designing Data Mart Architecture
| Type | Detail |
|---|---|
| Dependent | Built from central DW |
| Independent | Standalone from sources |
| Hybrid | Mix; common in practice |
| Use case | Department-specific (Finance, Marketing) |
4. Designing Slowly Changing Dimensions
| Type | Behavior |
|---|---|
| SCD 0 | Never change |
| SCD 1 | Overwrite (no history) |
| SCD 2 | New row + valid_from/to + current_flag |
| SCD 3 | Add prev value column |
| SCD 4 | History table separate |
| SCD 6 | Combination of 1+2+3 |
5. Designing Fact and Dimension Tables
| Table | Detail |
|---|---|
| Fact | Measurable events; FKs to dims; numeric measures |
| Dimension | Descriptive context (date, product, customer) |
| Grain | Define what one row of fact represents |
| Conformed dim | Shared across facts |
| Surrogate key | Integer PK independent of source |
6. Designing Data Warehouse Partitioning
| Strategy | Detail |
|---|---|
| Time partition | By day/month for fact tables |
| Cluster keys | Snowflake clustering / BigQuery clustering |
| Z-order (Delta) | Multi-dim co-locate |
| Partition pruning | Engine skips irrelevant partitions |
7. Designing Data Warehouse Indexing
| Index Type | Detail |
|---|---|
| Sort key (Redshift) | Order data on disk |
| Min/max stats | Auto file-skipping (Parquet, Iceberg) |
| Bloom filters | Existence pruning |
| Materialized views | Precomputed aggregates |
| Bitmap (legacy) | Low-cardinality columns |
8. Designing Data Quality Framework
| Dimension | Check |
|---|---|
| Completeness | Not-null % |
| Uniqueness | PK / unique constraints |
| Validity | Range, regex, enum |
| Consistency | Cross-table relationships |
| Timeliness | Freshness SLA |
| Accuracy | Reconcile vs source |
9. Designing Data Lineage Tracking
| Tool | Detail |
|---|---|
| OpenLineage | Open standard |
| DataHub / Amundsen | Catalogs with lineage |
| dbt docs | SQL-derived lineage graph |
| Marquez | Lineage server |
| Use | Impact analysis, debugging, compliance |
10. Designing Data Warehouse Performance Optimization
| Lever | Detail |
|---|---|
| Right partitioning | Most queries hit few partitions |
| Cluster / sort keys | Reduce data scanned |
| Avoid SELECT * | Columnar projection |
| Pre-aggregate | MVs / cubes |
| Workload mgmt | Resource queues per workload |
11. Designing Data Warehouse Security
| Control | Detail |
|---|---|
| RBAC | Roles for analyst/engineer/admin |
| Row-level security | Predicate per user/tenant |
| Column masking | Hide PII for non-privileged |
| Encryption | At rest + in transit; KMS-backed |
| Audit log | Who queried what |
12. Designing Data Catalog and Metadata Management
| Capability | Tool |
|---|---|
| Discovery | DataHub, Amundsen, Atlan, Collibra |
| Tagging | PII, owner, domain |
| Glossary | Business terms ↔ technical fields |
| Quality scoring | Surface trust signals |