# Ensuring Data Quality in CData Virtuality: A Guide for Data Engineers

**URL:** <https://community.cdata.com/t/ensuring-data-quality-in-cdata-virtuality-a-guide-for-data-engineers/1023>\
**Category:** CData Virtuality \[Archived\]\
**Tags:** data-quality, data-engineers, discussion, cdata-virtuality\
**Created:** [February 19, 2025, 12:26pm UTC](https://community.cdata.com/t/ensuring-data-quality-in-cdata-virtuality-a-guide-for-data-engineers/1023 "2025-02-19T12:26:55Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Marijan](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/marijan/32/134_2.png) [@Marijan](https://community.cdata.com/u/Marijan)\
**Post date:** [February 19, 2025, 12:26pm UTC](https://community.cdata.com/t/ensuring-data-quality-in-cdata-virtuality-a-guide-for-data-engineers/1023/1 "2025-02-19T12:26:55Z")

</div>

## Why is Data Quality Important?

In enterprise environments, data quality directly impacts operational efficiency, regulatory compliance, and decision-making. Poor-quality data can lead to inaccurate reports, financial losses, and wasted resources. Data engineers play a critical role in ensuring that data remains accurate, consistent, and reliable.

## How CData Virtuality Helps with Data Quality Assurance

CData Virtuality provides a **virtualized data integration laye** r that allows organizations to query and analyze data from multiple sources in real time. Instead of replicating data, it offers **centralized access** , enabling engineers to apply quality checks and transformations **without altering the original sources**. Key advantages include:

- **Real-time metadata querying** to understand schema consistency.
- **Automated data validation rules** to prevent incorrect entries.
- **Data governance controls** to manage access and security.
- **Integration capabilities** with **DataSpot** and other data governance and quality monitoring systems.

### Key Aspects of Data Quality

Ensuring data quality involves multiple dimensions:

1. **Completeness** – No critical data should be missing.

2. **Accuracy** – Values should be correct and reflect reality.

3. **Consistency** – Data should be uniform across different sources.

4. **Uniqueness** – Duplicates should be identified and removed.

5. **Timeliness** – Data should be up-to-date and refreshed as needed.

6. **Data**  **Governance** – Access permissions and compliance rules should be enforced.

* * *

## Using CData Virtuality to Check and Validate Data

### Identifying Missing or Incomplete Data

Missing data can indicate an issue with data ingestion or transformation pipelines. Engineers can use **INFORMATION\_SCHEMA** to identify nullable fields:

`SELECT TABLE_NAME, COLUMN_NAME`

`FROM INFORMATION_SCHEMA.COLUMNS`

`WHERE IS_NULLABLE = 'YES';`

 ![2ed88e9f-329c-4112-9d4e-4521d999c2d2.jpg](https://us1.discourse-cdn.com/cdata/original/2X/c/cce09b35d073246f6605642a0ad9cb859a0e96f3.jpeg)

_ **Example Use Case:** _

A retail company using CData Virtuality to consolidate sales data from multiple regions can run this query to identify if critical fields like **product\_price** or **customer\_id** allow `NULL` values, which could indicate a schema inconsistency.

`SELECT * FROM sales_data WHERE customer_id IS NULL OR product_price IS NULL;`

### Detecting Data Anomalies and Outliers

Outliers can distort business insights, leading to incorrect conclusions. Detect anomalies in numeric data using **average and standard deviation** :

`SELECT customerid, AVG(total) AS avg_price, STDDEV_POP(total) AS price_variability`

`FROM postgres.Orders`

`GROUP BY customerid;`

 ![bc7012c2-5d8a-4ecf-b614-9981bf7b6e0f.jpg](https://us1.discourse-cdn.com/cdata/original/1X/5f31c6c23055ad89188776556da10eb7d4090468.jpeg)

_ **Example Use Case:** &nbsp;&nbsp;_

A financial institution analyzing transaction data may find that a particular **region’s sales prices** deviate significantly from the average, indicating either an input error or fraud.

### To detect extreme values:

`SELECT orderid, orderdate, total`

`FROM postgres.Orders`

`WHERE total > (SELECT AVG(total) * 3 FROM postgres.Orders);`

 ![0821610f-8603-4e24-a50c-474ffc948e41.jpg](https://us1.discourse-cdn.com/cdata/original/2X/6/6651b2095b06f313d8414e7d06931c4335b335f3.jpeg)

### &nbsp;

### Validating Data Consistency Across Sources

In enterprise environments, data is often stored across different systems. Engineers can compare values to ensure **consistency between two sources** :

`SELECT`

` source1.orderid,`

` source1.total AS source1_amount,`

` source2.total AS source2_amount`

`FROM`

` postgres.Orders AS source1`

` FULL OUTER JOIN mysql.Orders AS source2 ON source1.orderid = source2.orderid`

`WHERE`

` source1.total <> source2.total`

` OR source1.total IS NULL`

` OR source2.total IS NULL;`

**Example Use Case:**

A multinational e-commerce company integrates **SAP** and **Salesforce** through CData Virtuality. This query helps them validate whether the order amounts stored in both systems match.

### Profiling Data to Identify Common Errors

Data profiling provides insights into **column-level statistics** to detect **unexpected duplicates, missing values,** or **incorrect formats** :

`SELECT companyname, COUNT(DISTINCT companyname) AS unique_values,`

`      MIN(companyname) AS min_value, MAX(companyname) AS max_value`

`FROM postgres.Customers`

`GROUP BY companyname;`

 ![3cdf57fe-22e5-420f-9660-f2274f830b08.png](https://us1.discourse-cdn.com/cdata/original/2X/d/df4d647bf216cc8dfa0ddb9ee26e19cbd2780ebd.png)

**Example Use Case:**

A telecom provider assessing customer subscription data might use this query to determine if **phone numbers follow a valid pattern**.

### Applying Business Rules for Data Validation

CData Virtuality allows engineers to create **virtual views** that enforce business rules dynamically without altering the raw data.

`CREATE VIEW validated_orders AS`

`SELECT orderid, customerid,`

`      CASE`

`          WHEN total < 0 THEN NULL`

`          ELSE total`

`      END AS valid_total`

`FROM postgres.Orders;`

**Example Use Case:**

A **B2B supply chain platform** can use this view to prevent processing of **orders with negative values** , ensuring data accuracy.

# &nbsp;

* * *

## Automating Data Quality Checks

Data engineers can automate quality checks using **scheduled jobs** and **alerts**.

### &nbsp;

### Setting Up Automated Data Quality Jobs

Run periodic checks using a **scheduled query** :

`SELECT  'Table: ' || 'Orders' || ' has ' || COUNT(*) || ' NULL values in critical columns'`

`FROM postgres.Orders`

`WHERE customerid IS NULL OR COST IS NULL;`

 ![c3aafe2f-4a77-4bc4-a127-9ba2e739cb25.png](https://us1.discourse-cdn.com/cdata/original/1X/e65f0074d4e2be78adf5e710a4d52591b899334a.png)

A **logging mechanism** can be added to capture issues:

`INSERT INTO`

` error_log (error_message, TIMESTAMP)`

`SELECT`

` 'Missing data detected in sales_data',`

` NOW()`

`FROM`

` sales_data`

`WHERE`

` customer_id IS NULL;`

* * *

## Integrating with External Systems (e.g., DataSpot)

To enhance data quality monitoring, CData Virtuality can integrate with **DataSpot** , an enterprise data governance platform. CData Virtuality’s DataSpot connector allows automated upload and sync of **metadata** , including information about all of the data sources, tables, columns, data types, virtual views as well as full **data lineage information**. In this example, we are only using the Data Quality upload features.

 ![c3c8f87f-11e2-4c5e-8da3-e633a78aef8e.jpg](https://us1.discourse-cdn.com/cdata/original/1X/ead07f05d9fa89348b56f8db00877641e46e4133.jpeg)

### Expose Data Quality Metrics as a Virtual Table

Dataspot expects data quality measurements to be uploaded as JSON and containing certain attributes such as data quality indicator name, exact time when quality was measured and the measurement results.

The easiest way to provide this is by creating a Virtual view with these exact values.

`CREATE OR REPLACE VIEW DQ."Industry column filled" AS`

`-- Reports the number of rows where "Industry" exists and is not null`

`SELECT 'Industry column filled' as observationOf,`

`       TimestampDiff(SQL_TSI_SECOND, from_unixtime(0) , Now()) * 1000 as dateObserved,`

`       '/CData Virtuality Platform/postgresdemo/Customers' as computedOn,`

`       (SELECT COUNT(*) FROM "postgres.Customers" WHERE "industry" IS NOT NULL) as amountValue,`

`       COUNT(*) as totalCount`

`FROM postgres.Customers;;`

### &nbsp;

### Upload the DQ measurement results to DataSpot&nbsp;

To calculate the new data quality results and automatically upload them to Dataspot, we invoke the upload procedure passing it the name of the virtual view we prepared earlier.

`CALL DQ.upload (`

` "testName" => 'Industry column filled' /* Mandatory  */`

`);`

 ![694d1f63-0339-41a1-8b1f-90401fa4d0a8.jpg](https://us1.discourse-cdn.com/cdata/original/2X/5/5ef1de9b7539474ab83667e5d7a275fb4105e2c9.jpeg)

* * *

## Conclusion: Ensuring Reliable and High-Quality Data in CData Virtuality

Maintaining **data quality** is essential for enterprises that rely on virtualized data integration. **CData Virtuality** provides robust tools to help data engineers **identify inconsistencies, detect anomalies, validate accuracy, and enforce business rules** —all without modifying raw data sources.

### &nbsp;

### Next Steps

- Make use of CData Virtuality’s powerful [Jobs](https://docs.datavirtuality.com/v4/jobs) and Schedules management to Implement **scheduled data validation queries** and automate DQ checks.
- Integrate **automated error detection and logging** to track recurring issues. Consider making use of [Notification procedures](https://docs.datavirtuality.com/v4/notification-procedures) to send email or Slack notifications in order to catch data quality issues in a timely manner.
- Integrate with dedicated 3rd party tools or use [REST API](https://docs.datavirtuality.com/v4/rest-api) capabilities of CData Virtuality to set up **real-time dashboards for data monitoring** to proactively catch data quality problems before they propagate.

By applying these strategies, data engineers can establish **trust in enterprise data** , minimize data integrity risks, and ensure that all analytics and business decisions are based on **accurate, complete, and high-quality** information.