Enterprise Architectural Strategy: ServiceNow Archiving, Change Risk Analytics & RAG in Databricks

1. Business Problem

Standard ServiceNow instances implement auto-archiving or table cleanup policies (typically purging records after 12–24 months) to prevent database bloat, manage high storage costs, and maintain instance query responsiveness. However, purging historical operational data creates severe operational and enterprise compliance gaps:

• Compliance & Audit Risks: Inability to produce multi-year historical logs required for industry regulatory audits.
• Lost AI/ML Potential: Loss of high-value training data essential for predicting system outages, calculating change risk prior to deployments, and conducting root-cause analyses.
• Search & Knowledge Decay: Inability to semantically query historical resolution notes, work logs, and attached diagnostic files for complex, recurring incidents across multi-year horizons.

Proposed Solution

Build an enterprise-grade ITSM Data Lakehouse in Databricks. By continuously ingesting, curating, and retaining 5+ years of ServiceNow operational data (incident, sys_attachment, sys_audit, and change_request), organizations can preserve full data lineage at low cost without compromising core ServiceNow instance performance.

2. Key Capability Unlocks

3. High-Level Technical Architecture

The platform follows a three-stage Medallion Architecture designed for incremental streaming, idempotent state processing, analytical KPI modeling, and vector index generation:

4. Key Implementation Deliverables

Phase 1: Automated Ingestion & Attachment Downloads (Bronze Layer)

The Bronze pipeline extracts raw JSON payloads incrementally from ServiceNow REST APIs (such as the Table API and Attachment API), stages them into Unity Catalog Volumes, and ingests them into Bronze Delta tables via PySpark Auto Loader (cloudFiles).

Key features of this phase:

• Incremental Ingestion: Auto Loader detects new landing files automatically and tracks ingestion progress via explicit checkpointing.
• Schema Evolution: Handles upstream ServiceNow schema changes gracefully using schema inference and evolution settings.
• Binary Processing: Downloads attached diagnostic files (sys_attachment) directly into Unity Catalog Volumes for downstream text extraction and embedding generation.

Phase 2: Silver Curation, Timestamp Parsing & Deduplication (Silver Layer)

The Silver layer standardizes, cleans, and enforcements schema integrity on raw JSON datasets.

Key operations:

• Timestamp Normalization: Converts string timestamps into native SQL timestamp formats using try_to_timestamp.
• Idempotent Merge: Executes MERGE INTO queries using system keys (sys_id and sys_updated_on) to eliminate duplicates.
• Delta Liquid Clustering: Applies CLUSTER BY (sys_created_on, priority) to optimize read and merge performance without static partitioning overhead.

Phase 3: Gold KPI Marts, Change Risk & GenAI RAG Prep (Gold Layer)

The Gold layer transforms curated Silver datasets into specialized analytical data marts and unstructured feature vectors:

• Executive KPI Mart: Aggregates SLA breaches, Mean Time to Resolve (MTTR), and incident volume trends by business unit.
• Change Risk Analytics Mart: Joins change_request records with historical sys_audit and incident data to calculate risk scores for Change Advisory Board (CAB) reviews prior to deployment.
• RAG Feature Prep: Aggregates incident ticket descriptions, resolution work notes, and extracted text from sys_attachment into unified text chunks formatted for vector embedding generation.

5. Databricks Vector Search & RAG Setup

To enable semantic search across historical ticket contexts and binary attachments, data from main.itsm_gold.gold_incident_vector_prep is ingested into a Databricks Vector Search index.

• Delta Change Data Feed (CDF): Enabled on the Gold table so that new or updated tickets automatically trigger incremental vector index updates.
• Databricks Managed Vector Search: Embeddings are generated automatically using built-in embedding models (or custom Foundation Model APIs) and synchronized with the Vector Search Endpoint.

6. End-to-End Workflow Orchestration

To operationalize the pipeline, we deploy a 3-task Databricks Workflow Job running sequentially:

• Task 1: Ingests raw ServiceNow JSON records via Auto Loader and downloads binary attachments concurrently to Unity Catalog Volumes.
• Task 2: Performs schema validation, standardizes timestamps, and merges curated records into Silver tables using Liquid Clustering.
• Task 3: Updates Executive KPI Marts, builds the Change Risk Mart, and generates the enhanced RAG vector payload to sync with the Databricks Vector Search Endpoint.

7. Summary & Enterprise Takeaways

• Zero Operational Bloat: Offloading historical operational records to Databricks low-cost cloud storage preserves multi-year retention without degrading ServiceNow database responsiveness.
• Idempotent Data Processing: Combining Delta Lake Auto Loader with MERGE INTO operations guarantees deduplicated, failure-resilient pipelines across Bronze, Silver, and Gold layers.
• GenAI Knowledge Retrieval: Combining incident text logs with attachment metadata provides high-precision context for GenAI support agents using Databricks Vector Search.

in exploring more Databricks solutions, visit our Databricks page.