Applies toDatabricks, Microsoft Fabric, Synapse, AWS (S3 + Glue/EMR), OCI Data Lake; Delta Lake or Apache Iceberg
1. Layers and contracts
| Layer | Content | Rules |
|---|---|---|
| Bronze (raw) | Data as received, append-only, with load metadata (_ingested_at, _source_file) | Never edited; replayable; limited access |
| Silver (cleaned) | Deduplicated, typed, conformed keys, quality-checked | One table per business entity; schema enforced |
| Gold (curated) | Aggregates, star schemas, data products for BI/ML | Owned by a domain team; documented; SLAs |
Each layer has a contract: who writes, who reads, freshness SLA and schema policy.
2. Storage layout per environment
<env>-lake (one storage account / bucket per env, or per domain in large orgs)
├── bronze/<source_system>/<entity>/ e.g. bronze/sap/orders/
├── silver/<domain>/<entity>/ e.g. silver/sales/orders/
├── gold/<domain>/<data_product>/ e.g. gold/sales/daily_revenue/
└── _checkpoints/, _landing/ (transient)
- Separate prod / non-prod storage and catalogs. Never let dev jobs write to prod paths.
- Hierarchical namespace (ADLS Gen2) on Azure for directory-level ACLs and fast renames.
3. Table naming
<catalog>.<schema>.<table> with catalogs per environment and schemas per layer/domain:
prod.bronze_sap.orders
prod.silver_sales.orders
prod.gold_sales.daily_revenue
dev.silver_sales.orders
Lowercase, snake_case, no spaces, no dates in table names (use partitions/columns).
4. Formats and table properties
- Delta Lake or Iceberg for silver and gold (ACID, time travel, schema evolution).
- Bronze may keep source format (JSON/CSV) but convert to Delta/Iceberg early for performance.
- Default properties: optimised writes / auto compaction, retention for time travel (for example 7–30 days), column mapping for renames.
5. Processing patterns
- Incremental loads (Auto Loader,
COPY INTO, CDC feeds) instead of full reloads. - Idempotent writes:
MERGEon keys or partition overwrite, so reruns are safe. - Quality checks between layers (expectations/tests) that block gold publication on failure.
6. Ownership and access
- Bronze: data engineering only.
- Silver: engineering + analysts in the domain.
- Gold: broad read access through the catalog, with row/column masks for sensitive data.
Foundation checklist
- Environments and storage per env created by IaC
- Catalog (Unity Catalog, Fabric OneLake, Glue, etc.) mapped to the layout
- Naming standard published
- Ingestion metadata columns standard
- Quality gate between silver and gold
- Backup/DR approach per layer (bronze must be replayable or backed up)