Ticket Status Validation and Transforming Data Using Bold Data Hub

In this article, we will demonstrate how to import tables from a CSV file, validate ticket status through transformations, and move the cleaned data into the destination database using Bold Data Hub. Follow the step-by-step process below.

Sample Data Source:
Sample CSC Data

Step-by-Step Process in Bold Data Hub

Step 1: Open Bold Data Hub

• Click on the Bold Data Hub.

Step 2: Create a New Pipeline

• Click Add Pipeline in the left-side panel.
• Enter the pipeline name and click the tick icon.

Step 3: Choose the Connector

• Select the newly created pipeline and opt for the CSV connector. You can either double-click or click on the Add Template option to include a template.

Step 4: Upload Your CSV File

• Click the “Upload File” button to select and upload your CSV file.

Step 5: Set the Properties

• Copy the file path and paste it into the filePath property field.

Step 6: Save and Choose the Destination

• Click Save, choose the destination, and confirm by clicking the Yes button.

Note: On-Demand Refresh will be triggered when the pipeline is saved. If needed, the pipeline can be scheduled in the Schedules tab.

Step 7: View Logs and Outputs

• Click the pipeline name in the left-side panel and switch to the Logs tab to view logs.

Step 8: Apply Transformations

• Go to the Transform tab and click Add Table.

• Enter the table name to create a transform table for customer satisfaction summary.

Note: The data will initially be transferred to the DuckDB database within the designated {pipeline_name} schema before undergoing transformation for integration into the target databases. As an illustration, in the case of a pipeline named “customer_service_data”, the data will be relocated to the customer_service_data table schema.

Learn more about transformation here

Ticket Status Validation

Overview

To ensure data consistency, tickets marked as “Resolved” should have a valid resolution time, and tickets marked as “Open” should not have one. Any inconsistencies are flagged for correction.

Approach

We use a CASE statement to classify ticket status validity:

• “Invalid” → Resolved tickets with missing or non-positive resolution time
• “Conflict” → Open tickets with a resolution time
• “Valid” → All other cases

SQL Query for Ticket Status Validation

SELECT 
    Ticket_ID, 
    Ticket_Status, 
    Resolution_Time, 
    CASE 
        WHEN Ticket_Status = 'Resolved' AND (Resolution_Time IS NULL OR Resolution_Time <= 0) THEN 'Invalid' 
        WHEN Ticket_Status = 'Open' AND Resolution_Time IS NOT NULL THEN 'Conflict' 
        ELSE 'Valid' 
    END AS Status_Validation 
FROM {pipeline_name}.sample_csc_data;