๐ฅ Gold layer โ aggregate & serve¶
Deep-dive companion to Medallion architecture with ELTMaestro. Gold turns conformed Silver into business-grain marts and serving aggregates โ the tables BI dashboards and ML features read directly. Optimize for read.
Aggregation & modeling¶
| Need | Step | Notes |
|---|---|---|
| Aggregate / roll up | Aggregate, Aggregate2 |
Group-bys and measures. Aggregate2 uses the expression builder ฮฃ(x) mode (e.g. COUNT(*), SUM(...)). |
| Build a table (CTAS) | Table (GeneralSQLTable) |
Materialize a mart via $QUERY_CTAS. |
| Bulk load a mart | group loaders (Onstagegroup + per-platform *GroupLoader) |
Stage + load large marts. |
Model to the consumption grain โ pre-join and pre-aggregate so the serving query is trivial. A star/snowflake of Gold facts + Silver-sourced dimensions is the usual shape.
Serving / publish¶
| Need | Step |
|---|---|
| Load to a serving MPP | JdbcTarget{Redshift, Snowflake, Synapse, Clickhouse, Greenplum, Netezza, Yellowbrick, Exasol, Databricks} |
| Write to any JDBC target | WriteJdbc |
| Export to file | WriteFile, Export |
The target platform is the one your BI tool queries; ELTMaestro's COPY/load path stages through $OBJECT_STORAGE and bulk-loads (see the per-platform setup, e.g. Redshift).
Materialization & refresh¶
| Mechanism | Use |
|---|---|
Cache builder ($QUERY_CACHE_BUILDER, $MAX_CACHE_SIZE) |
Pre-build a bounded cached result for fast preview/serving. |
View materialization ($QUERY_VIEW_CREATE, $SYSTEM_VIEW_MATERIALIZATION_THRESHOLD) |
Materialize heavy views past a threshold instead of recomputing. |
| Full rebuild vs incremental | Small marts: CTAS full-rebuild each run (simplest, always correct). Large marts: incrementally merge from Silver deltas (OnstageDelta) to avoid full recompute. |
Pick a refresh strategy per mart. Full-rebuild is simplest and self-correcting; go incremental only when rebuild cost justifies it โ and then guard it with a
ControlTestso an incremental gap is caught.
ML serving (optional extension)¶
Gold is the natural feed for ML: MLFeatureEngineer / MLPolyFeatureEngineer build features from Gold, MLFeatureTrainer trains, MLFeaturePredictor scores โ keeping feature derivation in the same governed pipeline.
Gotchas¶
- Grain discipline โ every Gold table has exactly one declared grain; mixing grains breaks aggregations downstream.
- Refresh atomicity โ swap/rebuild so consumers never read a half-loaded mart (load to a temp, then publish).
- Don't transform in Gold โ cleaning/conforming belongs in Silver; Gold only aggregates and serves, so lineage and trust stay clear.