📋 Client Entity Data Quality & Compliance Dashboard

This project implements a data validation system for 2,000+ catering client records to identify inconsistencies in tax IDs, contact emails, and billing information. The goal is to enhance audit readiness and improve client data integrity using SQL, Excel (VBA), and Tableau.

🔧 Tools Used

• SQL (SQLite or MS Access)
• Excel (VBA macro-enabled)
• Tableau (or Power BI) for dashboard visualization

📂 Files Overview

• setup_and_checks.sql: SQL code to create the table, insert sample data, and detect data quality issues
• ExportIssues.bas: Excel VBA macro to export issue data from SQL into a formatted worksheet
• client_data.accdb: Example database file (if using Access/SQLite)

▶️ How to Run

1. Setup Database

Create your database (SQLite or Access) and run the contents of setup_and_checks.sql to:

• Create the client_entities table
• Insert example records
• Create a data_quality_issues view

2. Export Issues to Excel

Open Excel and press Alt + F11 to open the VBA Editor. Insert a new module and paste the contents of ExportIssues.bas.

Create a new worksheet named Issues in your Excel file. Update the connection string in the macro to match your local database path. Run the macro to pull issue data into Excel.

3. Visualize in Tableau

Connect Tableau to your database and use the data_quality_issues view as the data source.

• Create bar charts by issue type (e.g., Missing Tax ID, Invalid Email)
• Add filters by client name or issue category
• Display total flagged clients and percentages

✅ Validations Performed

• Missing or empty Tax ID
• Improper or malformed email addresses
• Missing or empty Billing ID

📊 Optional: Data Quality Scoring

Use an additional SQL query to assign a quality score (0–100) to each client based on how complete their records are.

SELECT 
  client_id,
  client_name,
  100 
  - (CASE WHEN tax_id IS NULL OR TRIM(tax_id) = '' THEN 33 ELSE 0 END)
  - (CASE WHEN contact_email NOT LIKE '%@%.%' OR contact_email LIKE '%@@%' THEN 33 ELSE 0 END)
  - (CASE WHEN billing_id IS NULL OR TRIM(billing_id) = '' THEN 34 ELSE 0 END)
  AS quality_score
FROM client_entities;
  

Note: You can expand this project to include automated email alerts, audit trail logging, or integrate with CRM systems for real-time sync.