Company Name
ZenQMS
Industry
Software Development
Status
Public
Size
51-200 Employees
Location
United States
Website
www.zenqms.com

Streamlining Data Export Process for Improved Customer Satisfaction

Successful data export automation for ZenQMS customers, by leveraging Lambda, Glue workflows and S3 buckets.

Challenge
  1. Accessibility of data to ZenQMS customers
  2. Manual effort on data extraction
Solution
  1. Automated the Data Export using Glue Workflows.
  2. Notification in case of success or failure.
Results
  1. Successfully automated data export process while managing access to specific data
  2. Improved customer satisfaction
New Services Added
  1. Amazon S3
  2. Amazon Glue
  3. AWS Lambda
  4. Amazon CloudWatch
  5. AWS Systems Manager
  6. AWS KMS

Problem Statement

ZenQMS needed to provide a secure and efficient way for its customers to access and download data in CSV format. Each customer can define data set with filter that needs to be exported based on a predefined schedule. The process had to be automated to minimize manual effort, including notifications in case of failure or success. Customers will be notified once their export is ready to download.

Proposed Solution Architecture

The proposed solution involved the use of Terraform scripts to provision infrastructure. Glue Workflows were scheduled using CloudWatch Event Rules, which triggered Lambda function with configurations for each member. Glue workflows were then started for one or more members, and Glue jobs were used to submit export queries to export specific data for each member. The exported data was stored in CSV files in the respective S3 path, and it was packaged into a zip file. The Notification job then sent an email with a pre-signed URL to the S3 zip file. In case of any failure during the process, notifications were sent to the respective stakeholders.

Outcomes of Project & Success Metrics

NETSOL successfully implemented an automated data export process while managing access to specific data, effectively reducing manual efforts. This automated process helped ZenQMS to achieve and deliver improved services to their users, enhancing customer satisfaction.

Lesson Learned

  1. When generating an AWS pre-signed URL, it needs to be created using IAM user credentials, else the URL will expire after the expiry of temporary credentials, which can cause data accessibility issues.
  2. NAT gateway is required to connect to Glue boto3 Client, as the job has to run in a Private subnet to connect to Private RDS.
  3. AWS Glue Python Shell jobs have issues with psycopg2 Library, therefore, pg8000 library had to be used for connection with Aurora.