A four-layer Data Vault domain for ITSM data
ServiceNow holds two things the business needs to report on: customer service cases and internal IT incidents. The raw data replicates into Snowflake and is close to unusable as it lands. The incident entity alone carries 220+ columns, and every reference field appears twice, once as a human-readable display value and once as an internal system id.
I designed and built the domain that turns that into models people can query without knowing any of it exists.
The shape of it
Four layers, more than 35 models, each layer with a single responsibility:
- Cleansing — raw replicated source
- Staging — typed, narrowed, enriched; the layer where the source’s oddities get resolved
- Refined — business logic and Data Vault structures
- Published — business-named views that analysts and dashboards consume directly
Separating those concerns is what makes the domain maintainable. A change in how ServiceNow represents a user does not ripple into every consumer.
The decisions worth explaining
State history as SCD-2. State transitions are modelled with from and to timestamps rather than overwritten. That is what makes point-in-time questions answerable at all: how long a case sat awaiting customer response, whether SLA breaches are trending worse, what the queue actually looked like last quarter. Without it you can only ever answer “how are things now”.
Governing brand and platform centrally. Cases and incidents connect to the enterprise account hub through Data Vault link tables, so brand and platform resolve from the authoritative account record rather than whatever the source system happens to hold. Multi-brand and multi-platform routing then works the same way everywhere instead of being re-derived per source.
Reference objects built once. Users, groups and SLA definitions are standalone reusable lookups rather than logic embedded in each consumer. If the same join is being copied into five models, it belongs in one.
CDC event logs per source table. Every source has an event-log variant tracking what changed and when, which drives incremental loads and gives an audit trail.
Resolving identifiers properly. The source stores user references as system ids while other fields carry emails. Rather than leaving consumers to reconcile that, the staging layer resolves references to one reliable identifier through lookup joins.
What people do with it
Analysts query published views with business-named columns and never touch a system id. SLA models drive breach and resolution-performance reporting. State change logs power time-in-state analysis. And because the domain links into the enterprise hub, ServiceNow data joins to other domains rather than sitting on its own island.