CVE Lakehouse on Databricks

DIC 587 - Data Intensive Computing | Fall 2025

📋 Project Overview

This project implements a comprehensive cybersecurity data lakehouse using the Medallion Architecture pattern on Databricks Community Edition. The pipeline ingests, transforms, and analyzes 40,274+ CVE (Common Vulnerabilities and Exposures) records from 2024.

🏗️ Architecture

Medallion Architecture Layers

Bronze (Raw) → Silver (Normalized) → Gold (Analytics)

• Bronze Layer: Raw JSON ingestion from CVEProject/cvelistV5 repository

• Silver Layer: Cleaned and normalized relational tables

• Gold Layer: Business intelligence and exploratory data analysis

• 🔧 Unique Pipeline Implementation

The Challenge

Traditional approaches to reading 300,000+ small JSON files in Databricks Community Edition face critical bottlenecks:

• Unity Catalog Volumes have strict I/O restrictions for direct Python access
• Spark's recursiveFileLookup on 300,000 files causes severe performance degradation
• Network transfers of the entire 1.8 GB dataset are prohibitively slow
• The "small file problem" creates massive overhead in distributed processing

Our Solution: 6-Stage Optimized Pipeline

Bronze Layer Pipeline

Stage 1: Python reads from /tmp (No Spark, No Restrictions)

• Downloads CVE repository ZIP to /tmp (unrestricted local storage)
• Why: The cluster's /tmp directory provides fast, unrestricted access for Python file operations, unlike Unity Catalog Volumes

Stage 2: Filter to 2024 in Python (Fast, In-Memory)

• Iterates through 300,000 files and keeps only 2024 records (~40,000)
• Why: "Shift-left" filtering reduces 300,000 files → 40,000 records before any network transfer
• Impact: 87% data reduction before expensive operations

Stage 3: Convert to Parquet using pandas/pyarrow

• Aggregates 40,000 records into ONE optimized file
• Why: Solves the "small file problem" with 70% compression and columnar storage
• Impact: 40,000 files → 1 file = 99.9975% reduction in file count

Stage 4: Upload ONE Parquet to Unity Catalog Volume

• Single file upload (fast and secure)
• Impact: ~2 seconds vs. hours for 40,000 individual files

Stage 5: Spark Reads ONE File (Instant)

• Spark excels at reading large, optimized files
• Reads single Parquet file in <1 second with no restrictions

Stage 6: Spark Writes to Delta Bronze Table

• Creates cve_bronze.records with ACID guarantees and versioning
• Final Result: 40,274 records ready for Silver layer

Silver Layer Pipeline

Stage 1: JSON Schema Definition

• Defines explicit schema for CVE v5 nested structure
• Handles complex arrays and nested objects

Stage 2: Parse Nested JSON

• Converts string JSON into queryable Spark structs
• Enables dot-notation access to nested fields

Stage 3: Extract and Clean Core CVE Data

• Handles multiple CVSS versions (v3.0 and v3.1) with coalesce logic
• Creates: cve_silver.core_cves (40,274 records)

Stage 4: Explode Vendor/Product Relationships

• Transforms one-to-many relationships into relational format
• Example: CVE-2024-12345 affecting Microsoft Windows + Apple macOS becomes 2 separate rows
• Creates: cve_silver.affected_products (~100,000+ records)

Stage 5: Data Quality Validation

• Assertion-based checks ensure data integrity
• Validates record counts, null values, and uniqueness

Gold Layer Pipeline

Stage 1: Temporal Analysis

• Monthly and weekly publication trends
• Identifies seasonal patterns

Stage 2: Risk Distribution Analysis

• CVSS severity bucketing and distribution
• Insight: 42.55% of CVEs remain unscored

Stage 3: Vendor Intelligence

• Top vendors by vulnerability count
• Filters out placeholder values (unknown, n/a)

Stage 4: Vendor Risk Profiling

• Severity breakdown by vendor (Critical/High/Medium/Low)
• Enables risk-based prioritization

Stage 5: Market Concentration Analysis

• HHI index calculation
• Reveals competitive vs. concentrated markets

Pipeline Performance Metrics

Stage	Traditional Approach	Our Optimized Approach	Speedup
File Count	300,000 JSON files	1 Parquet file	300,000x
Bronze Layer Runtime	2-3 hours	3-5 minutes	~40x faster
Data Transfer	1.8 GB (all years)	~50 MB (2024 only)	36x reduction
Silver Layer Runtime	5-10 minutes	30-60 seconds	~10x faster

🎯 Key Features

✅ Processed 40,274 CVE vulnerability records from 2024
✅ Normalized nested JSON into relational schema
✅ Implemented explode operation for vendor/product relationships
✅ Comprehensive data quality checks with assertions
✅ Interactive visualizations and security analytics

💻 Technologies Used

• Databricks Community Edition - Cloud data platform
• Apache Spark (PySpark) - Distributed data processing
• Delta Lake - ACID transactions and versioning
• SQL - Analytics and exploratory data analysis

📊 Key Results

Metric	Value
Total CVEs (2024)	40,274
CVEs with CVSS Scores	23,138 (57.45%)
Critical Vulnerabilities	1,816 (4.51%)
High Severity	8,183 (20.32%)
Unique Vendors Affected	1,000+
Vendor/Product Combinations	100,000+

🔍 Key Insights

1. Data Quality Challenge: 42.55% of 2024 CVEs remain unscored, highlighting delays in vulnerability assessment
2. Severity Distribution: Among scored CVEs, High and Medium severity dominate (50%+ combined)
3. Vendor Concentration: Top 25 vendors account for majority of reported vulnerabilities
4. Temporal Patterns: CVE publications show clear monthly and weekly trends

📁 Repository Structure

cve-lakehouse-databricks/
├── README.md
├── 01_bronze_layer.py              # Raw data ingestion
├── 02_bronze_to_silver.py          # Data normalization
├── 03_exploratory_analysis.sql     # Analytics & visualizations
└── screenshots/
    ├── bronze/                      # Bronze layer results
    ├── silver/                      # Silver layer results
    └── eda/                         # Exploratory analysis visualizations

🚀 Notebooks

1. Bronze Layer (01_bronze_layer.py)

• Downloads CVE v5 JSON repository from GitHub
• Filters to 2024 data (40,274 records)
• Implements data quality checks:
  • Record count threshold (>= 30,000)
  • CVE ID completeness (no nulls)
  • CVE ID uniqueness (no duplicates)
• Creates managed Delta table: cve_bronze.records

2. Silver Layer (02_bronze_to_silver.py)

• Parses nested JSON structure
• Creates normalized tables:
  • cve_silver.core_cves - Main vulnerability records with CVSS scores
  • cve_silver.affected_products - Exploded vendor/product relationships
• Handles multiple CVSS versions (v3.0, v3.1)
• Implements foreign key relationships via CVE ID

3. Exploratory Data Analysis (03_exploratory_analysis.sql)

• Temporal analysis (monthly/weekly patterns)
• CVSS score distribution and severity analysis
• Vendor intelligence and market concentration
• Risk profiling by vendor
• Interactive visualizations

📸 Screenshots

Bronze Layer

-Added in screenshots folder

Silver Layer

-Added in screenshots folder

Exploratory Data Analysis

-Added in screenshots folder

🚀 How to Run the Pipeline

Prerequisites

1. Databricks Community Edition Account: https://community.cloud.databricks.com
2. Cluster Runtime: DBR 13.x or newer
3. Unity Catalog Setup: Create volume /Volumes/workspace/default/assignment2

Execution Steps

Step 1: Setup Unity Catalog Volume

-- Run in Databricks SQL cell
CREATE CATALOG IF NOT EXISTS workspace;
USE CATALOG workspace;
CREATE SCHEMA IF NOT EXISTS default;
CREATE VOLUME IF NOT EXISTS assignment2;

Step 2: Execute Bronze Layer

1. Import 01_bronze_layer.py into Databricks
2. Attach to cluster (DBR 13.x+)
3. Click "Run All"
4. Expected Runtime: 3-5 minutes
5. Verify: 40,274 records in cve_bronze.records

What Happens:

• Downloads 500 MB CVE repository
• Filters to 2024 data
• Creates optimized Parquet staging file
• Writes Delta table with quality checks

Step 3: Execute Silver Layer

1. Import 02_bronze_to_silver.py
2. Attach to same cluster
3. Click "Run All"
4. Expected Runtime: 30-60 seconds
5. Verify:
   • cve_silver.core_cves (40,274 records)
   • cve_silver.affected_products (~100,000+ records)

What Happens:

• Parses nested JSON structure
• Extracts CVSS scores (v3.0 and v3.1)
• Explodes vendor/product arrays
• Creates normalized tables

Step 4: Execute Exploratory Data Analysis

1. Import 03_exploratory_analysis.sql
2. Change notebook language to SQL
3. Run cells sequentially
4. Create visualizations (charts)
5. Expected Runtime: 1-2 minutes

What Happens:

• Temporal analysis (trends)
• CVSS distribution analysis
• Vendor intelligence
• Market concentration metrics

🐛 Troubleshooting

Bronze Layer: "Permission Denied"

Solution: Ensure Unity Catalog volume exists
spark.sql("CREATE VOLUME IF NOT EXISTS assignment2")

Silver Layer: "INVALID_ARRAY_INDEX"

Solution: Already handled with safe array access in our code
when((col("array").isNotNull()) & (size(col("array")) > 0), ...)

EDA: "UNKNOWN" vendors in results

Solution: Use cleaned queries with vendor filtering
WHERE LOWER(TRIM(vendor)) NOT IN ('unknown', 'n/a', 'na')

🎓 Learning Outcomes

Technical Skills Demonstrated

• Medallion Architecture Implementation - Built Bronze/Silver layers using Delta Lake
• Large-Scale JSON Processing - Normalized 40,000+ nested JSON records
• Data Engineering Pipeline - Implemented Bronze→Silver medallion architecture with proper data normalization and quality controls
• Business Intelligence Analysis - Performed comprehensive exploratory data analysis on cybersecurity vulnerability trends
• Production-Ready Code - Authored reproducible PySpark/SQL notebooks with error handling

Data Engineering Concepts Applied

• Schema-on-read for semi-structured data
• Delta Lake ACID transactions
• Explode operations for array normalization
• Data quality assertions and validation
• Unity Catalog table management

📚 Data Sources

• CVE v5 Repository: https://github.com/CVEProject/cvelistV5
• CVE JSON Schema: https://github.com/CVEProject/cve-schema/tree/master/schema/v5.0
• CVSS v3.1 Specification: https://www.first.org/cvss/v3.1/specification-document

👤 Author

[NIDHI RAJANI]
DIC 587 - Data Intensive Computing | Fall 2025

📝 Assignment Details

• Course: DIC 587 - Data Intensive Computing
• Semester: Fall 2025

---

This project demonstrates real-world data engineering skills using industry-standard tools (Databricks, Spark, Delta Lake) to build a production-quality data pipeline for cybersecurity intelligence.