ServiceTrack: Job Tracking & Customer Visit Analytics Pipeline

A Databricks Lakehouse pipeline built on the Medallion architecture (Bronze → Silver → Gold) that processes service center job data and produces business-ready analytics using SQL.

Overview

ServiceTrack ingests raw customer, device, and service job data, cleans and standardizes it, then builds curated Gold tables answering key business questions such as technician performance, delay analysis, customer repeat visits, and device brand trends. Analysts can query these tables directly using Databricks SQL without understanding the underlying data pipeline.

Architecture

Raw files are ingested into the Bronze layer with explicit schemas and audit metadata. Silver notebooks perform entity-specific cleansing and standardization. The Gold layer builds one enriched base model by joining Silver tables, then creates four business-focused analytical tables. SQL notebooks query these Gold tables to generate operational and business insights.

See docs/architecture/servicetrack_architecture.png for the complete pipeline architecture.

Tech Stack

• Databricks
• PySpark
• SQL (Databricks SQL)
• Delta Lake
• Unity Catalog
• Git & GitHub

Key Features

• Medallion Architecture (Bronze → Silver → Gold)
• Data ingestion with explicit schemas and audit metadata
• Entity-level data cleansing and standardization
• Curated Gold tables for business analytics
• SQL notebooks covering fundamental and advanced SQL concepts
• Business-focused analytical queries and insights

Folder Structure

ServiceTrack/
│   .gitignore
│   README.md
│
├── datasets
│   ├── customers.csv
│   ├── devices.csv
│   └── service_jobs.csv
│
├── docs
│   │   Project_proposal.pdf
│   │   ServiceTrack_Documentation.pdf
│   │
│   ├── architecture
│   │   └── servicetrack_architecture.png
│   │
│   └── screenshots
│       ├── databricks_catalog_tables.png
│       ├── databricks_setup.png
│       ├── databricks_workspace.png
│       ├── lineage_graph.png
│       └── sql_business_insights.png
│
└── notebooks
    ├── 01_bronze
    │   ├── 01_load_bronze.ipynb
    │   └── 01_load_bronze_learning.ipynb
    │
    ├── 02_silver
    │   └── 01_transform_silver.ipynb
    │
    ├── 03_gold
    │   ├── 01_gold_base_model.ipynb
    │   ├── 02_gold_delay_analysis.ipynb
    │   ├── 03_gold_customer_repeat_visits.ipynb
    │   ├── 04_gold_technician_performance.ipynb
    │   └── 05_gold_device_brand_trends.ipynb
    │
    └── 04_sql
        ├── 01_select_where_orderby.sql
        ├── 02_agg_groupby_having.sql
        ├── 03_joins.sql
        ├── 04_case_cte_subqueries.sql
        ├── 05_window_functions.sql
        └── 06_business_insights.sql

Pipeline Layers

Bronze Layer

• Ingest raw CSV files into Delta tables.
• Apply explicit schemas.
• Add audit metadata using current_timestamp() and input_file_name().

Silver Layer

• Clean and standardize customer, device, and service job datasets.
• Apply entity-specific business rules.
• Produce consistent datasets for downstream processing.

Gold Layer

• Build a single enriched base model by joining Silver tables.
• Create business-specific analytical tables:
  • Delay Analysis
  • Customer Repeat Visits
  • Technician Performance
  • Device Brand Trends

SQL Analytics

SQL notebooks demonstrate:

• Data filtering and sorting
• Aggregate functions
• GROUP BY and HAVING
• JOINs
• CASE expressions
• CTEs
• Subqueries
• Window functions
• Business-oriented analytical queries

How to Run

1. Upload the datasets to a Unity Catalog Volume.
2. Execute the notebooks in order:
   • 01_bronze
   • 02_silver
   • 03_gold (01 through 05)
3. Run the SQL scripts in 04_sql using Databricks SQL against the Gold tables.

Business Value

• Technician Performance — Identify technicians who consistently complete more or fewer repair jobs to support workload balancing and training.
• Delay Reduction — Identify jobs exceeding promised turnaround time.
• Customer Experience — Analyze repeat visits to better understand customer service history.
• Operational Planning — Identify device brand trends to support spare parts planning and resource allocation.

Repository Contents

• Medallion data pipeline implementation
• Curated Gold analytical tables
• SQL learning notebooks
• Advanced business insight queries
• Architecture diagram
• Project documentation
• Sample project screenshots

Author

Sarayu
Data Engineering Intern Celebal Technologies