Probes Types
The Blindata platform provides different types of probes, which can be performed on multiple source systems.
Single Metric
Allows you to extract a single value. It can be used, for example, to extract the record count or the average of the values of a field.
Example of “Single Metric” Probe for JDBC Connection
| Property | Value |
|---|---|
| Name | First Probe |
| Check | CK-01 |
| Type | SINGLE_METRIC |
| Connection | conn_postgresql_1 (JDBC) |
| Query | SELECT AVG(pinv.Quantity) as metric FROM Production.ProductInventory pinv; |
Example of “Single Metric” Probe for Mongo Connection
| Property | Value |
|---|---|
| Name | First Probe |
| Check | CK-01 |
| Type | SINGLE_METRIC |
| Connection | conn_mongo_1 (MONGO) |
| Collection | productInventory |
| Default Value | 0 |
| Pipeline | [ { $group: { _id: null, metric: { $avg: “$quantity” } } ] |
Example of “Single Metric” Probe for Salesforce Connection
| Property | Value |
|---|---|
| Name | First Probe |
| Check | CK-01 |
| Type | SINGLE_METRIC |
| Connection | conn_salesforce_1 (SALESFORCE) |
| Query | SELECT AVG(AnnualRevenue) FROM Account |
Double Metrics
Compared to the previous one, it allows to extract two distinct values. It is useful if you want to extract both the actual metric and the total number of elements analyzed in the context of a single probe.
Example of “Double Metrics” Probe for JDBC Connection
| Property | Value |
|---|---|
| Name | Second Probe |
| Check | CK-02 |
| Type | DOUBLE_METRICS |
| [Query Definition 1] Connection |
conn_postgresql_1 (JDBC) |
| [Query Definition 1] Query |
SELECT COUNT(*) as totalElements FROM HumanResources.Employee e; |
| [Query Definition 2] Connection |
conn_postgresql_1 (JDBC) |
| [Query Definition 2] Query |
SELECT COUNT(*) as metric FROM HumanResources.Employee e WHERE DATEDIFF(YEAR, e.BirthDate , e.HireDate) < 18; |
Example of “Double Metrics” Probe for Mongo Connection
| Property | Value |
|---|---|
| Name | Second Probe |
| Check | CK-02 |
| Type | DOUBLE_METRICS |
| [Query Definition 1] Connection |
conn_mongo_1 (MONGO) |
| [Query Definition 1] Collection |
employees |
| [Query Definition 1] Default Value |
0 |
| [Query Definition 1] Pipeline |
[ { $count: “totalElements” } ] |
| [Query Definition 2] Connection |
conn_mongo_1 (MONGO) |
| [Query Definition 2] Collection |
productInventory |
| [Query Definition 2] Default Value |
0 |
| [Query Definition 2] Pipeline |
[ { $project: { employeeAge: { $subtract: [ “$hireDate”, “$birthDate” ] } } }, { $match: { employeeAge: { $lt: 18 } } }, { $count: “metric” } ] |
Example of “Double Metrics” Probe for Salesforce Connection
| Property | Value |
|---|---|
| Name | Second Probe |
| Check | CK-02 |
| Type | DOUBLE_METRICS |
| [Query Definition 1] Connection |
conn_salesforce_1 (SALESFORCE) |
| [Query Definition 1] Query |
SELECT COUNT(Id) totalElements FROM Account |
| [Query Definition 2] Connection |
conn_salesforce_1 (SALESFORCE) |
| [Query Definition 2] Query |
SELECT COUNT(Id) metric FROM Account WHERE AnnualRevenue < 75000000 |
Tabular Metrics
Enable simultaneous extraction of metrics related to multiple KQIs. The use of this probe is suggested when several KQIs are defined whose metric is calculated in the same way except for a different grouping. For example, in the case where all the events from various sources are collected in a single point and the need is to individually evaluate the freshness of the data for each source. Metrics can be extracted separately for each source while using only one query, thus optimizing performance. The connection between the data extracted from the probe and the individual KQIs occurs through the use of alias directly inside the query:
- METRIC: is the alias that identifies the value to be considered as a KQI metric
- TOTAL_ELEMENTS: is the alias that identifies the value to be used as the total number of elements to be considered in the KQI (in the case of a scoring strategy of the “Error Percentage” type)
- QUALITY_CHECK_CODE: is the alias that identifies the field to be used as a quality check code to reconcile the extracted data with the actual KQI The examples below show a possible use case: for each department of the organization we want to monitor the number of employees through a dedicated quality control. We are therefore going to define a quality control for each department, each with its own check code (for example “CK-GROUP-03-DEPT1”, “CK-GROUP-03-DEPT2”, etc.) but all with the same check group code (for example “CK-GROUP-03”). To extract the data relating to all these quality controls, a single probe of “TABULAR_METRICS” type is used by setting the aliases correctly (in particular the alias “QUALITY_CHECK_CODE”), as described above.
Example of “Tabular Metrics” Probe for JDBC Connection
| Property | Value |
|---|---|
| Name | Third Probe |
| Check Group Code | CK-GROUP-03 |
| Type | TABULAR_METRICS |
| Connection | conn_postgresql_1 (JDBC) |
| Query | SELECT COUNT(*) as METRIC, ‘CK-GROUP-03-’||department as QUALITY_CHECK_CODE FROM HumanResources.Employee GROUP BY department; |
Example of “Tabular Metrics” Probe for Mongo Connection
| Property | Value |
|---|---|
| Name | Third Probe |
| Check Group Code | CK-GROUP-03 |
| Type | TABULAR_METRICS |
| Connection | conn_mongo_1 (MONGO) |
| Collection | employees |
| Pipeline | [ { $group: { _id: “$department”, metric: { $sum: 1 } }, { $project: { metric: true, qualityCheckCode: { $concat: [ “CK-03-”, “$description” ] } } } ] |
Example of “Tabular Metrics” Probe for Salesforce Connection
| Property | Value |
|---|---|
| Name | Third Probe |
| Check Group Code | CK-GROUP-03 |
| Type | TABULAR_METRICS |
| Connection | conn_salesforce_1 (SALESFORCE) |
| Query | SELECT COUNT(Id) METRIC, Industry QUALITY_CHECK_CODE FROM Account GROUP BY Industry |
Info
Since the data cannot be manipulated directly in a SOQL query, the check codes must necessarily equal the contents of a field. However, you can calculate check codes in Salesforce using a formula field and run the query using this field.
Records Comparison
It allows you to define two distinct queries, possibly each on a different source system, and compare the result of the queries record by record. At the end of the comparison the metrics are calculated - such as number of records that differ, and the total number of items analyzed. It is therefore a type of probe that can be used to evaluate, for example, the consistency of the data.
Example of “Records Comparison” Probe for JDBC Connection
| Property | Value |
|---|---|
| Name | Fourth Probe |
| Check | CK-04 |
| Type | RECORDS_COMPARISON |
| [Query Definition 1] Connection |
conn_postgresql_1 (JDBC) |
| [Query Definition 1] Query |
SELECT e.BusinessEntityID as id, e.HireDate as hireDate FROM AdventureWorks.HumanResources.Employee e ORDER BY id; |
| [Query Definition 2] Connection |
conn_postgresql_1 (JDBC) |
| [Query Definition 2] Query |
SELECT DISTINCT edh.BusinessEntityID as id, min(edh.StartDate) FROM AdventureWorks.HumanResources.EmployeeDepartmentHistory edh GROUP BY edh.BusinessEntityID ORDER BY id; |
Example of “Records Comparison” Probe for Mongo Connection
| Property | Value |
|---|---|
| Name | Fourth Probe |
| Check | CK-04 |
| Type | RECORDS_COMPARISON |
| [Query Definition 1] Connection |
conn_mongo_1 (MONGO) |
| [Query Definition 1] Collection |
employees |
| [Query Definition 1] Pipeline |
[ { $project: { employeeId: true, hireDate: true } }, { $sort: { employeeId: 1 } } ] |
| [Query Definition 2] Connection |
conn_mongo_1 (MONGO) |
| [Query Definition 2] Collection |
employeeDepartmentHistory |
| [Query Definition 2] Pipeline |
[ { $project: { employeeId: true, hireDate: true } }, { $group: { _id: “$employeeId”, hireDate: { $min: “$hireDate” } }, { $sort: { employeeId: 1 } } ] |
Example of “Records Comparison” Probe for Salesforce Connection
In this case, it can be useful to take advantage of this type of probe to verify the consistency of the data present in Salesforce with respect to those present in another system. For the construction of the queries, refer to the examples proposed up to here.
Copy Rows
It allows you to define the extraction and copying of the extracted data within the system chosen by the user. At the end of the operations, the number of rows inserted in the table where the insertion was made is returned. Useful if you want to replicate data on multiple tables within the same system.
Example of “Copy Rows” Probe for JDBC Connection
| Property | Value |
|---|---|
| Name | Fifth Probe |
| Check | CK-05 |
| Type | COPY_ROWS |
| Connection | conn_postgresql_1 (JDBC) |
| Query | INSERT INTO users_errors ( SELECT email, age, birth_date FROM users WHERE age < 18 ); |
Example of “Copy Rows” Probe for Mongo Connection
The feature is not yet implemented for Mongo connections.
Example of “Copy Rows” Probe for Salesforce Connection
The feature is not implemented for Salesforce connections.
Export Rows
It allows you to define two distinct queries, possibly each on a different source system, The first one, wil select some data; the second one will insert the extracted data into the table of choice. At the end of the operations, the number of rows inserted is returned.
Example of “Export Rows” Probe for JDBC Connection
| Property | Value |
|---|---|
| Name | Sixth Probe |
| Check | CK-06 |
| Type | EXPORT_ROWS |
| [Query Definition 1] Connection |
conn_postgresql_1 (JDBC) |
| [Query Definition 1] Query |
SELECT email, age, birth_date FROM users WHERE age < 18; |
| [Query Definition 2] Connection |
conn_postgresql_1 (JDBC) |
| [Query Definition 2] Query |
INSERT INTO users_errors VALUES (?, ?, ?); |
Example of “Export Rows” Probe for Mongo Connection
The functionality is available only if the target system is connected via JDBC. Therefore it is only possible to extract data from Mongo systems to bring it to other systems. For the construction of the queries, refer to the examples proposed up to here.
Example of “Export Rows” Probe for Salesforce Connection
The functionality is available only if the target system is connected via JDBC. Therefore it is only possible to extract data from Salesforce systems to bring them to other systems. For the construction of the queries, refer to the examples proposed up to here.