data-warehouse-semantic-layer

Structure dbt models into four layers: staging (`stg_`) for 1-to-1 source renaming and type casting, intermediate (`int_`) for joins and business logic, dimensions (`dim_`) for conformed entities, and facts (`fct_`) for transactions at an explicit grain. Generate surrogate keys with dbt_utils.generate_surrogate_key.

Define a semantic model YAML referencing your fact table, then declare entities (primary and foreign keys), measures with aggregations like sum or count_distinct, and time dimensions with granularity. Create derived ratio metrics by specifying numerator and denominator measures.

A dbt data contract is a YAML schema specification enforced at build time via `contract: {enforced: true}` in the model config. It declares explicit column data types and constraints like not_null and primary_key, preventing upstream schema changes from silently breaking downstream dashboards.

Use incremental materialization for high-volume fact tables where full refreshes are too slow or costly. Configure a unique_key and choose a strategy like merge for updates or insert_overwrite for partition replacements, filtering new rows with is_incremental() logic.

Dimensional modeling fits analytical warehouse workloads, not application transactional schemas. Use standard relational design for PostgreSQL/MySQL application databases, and route client-side event tracking telemetry or infrastructure log aggregation to their dedicated tooling instead.