### Virtual Sources Configuration Example

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/05-VirtualSources.md

An example demonstrating the structure of the 'virtualSources' section, showcasing different types of virtual source definitions including SQL, Join, Filter, Select, and Aggregate.

```HOCON
jobConfig: {
  virtualSources: [
    {
      id: "sqlVS"
      kind: "sql"
      description: "Filter data for specific date only"
      parentSources: ["hive_source_1"]
      persist: "disk_only"
      save: {
        kind: "orc"
        path: "some/path/to/vs/location"
      }
      query: "select id, name, entity, description from hive_source_1 where load_date == '2023-06-30'"
      metadata: [
        "source.owner=some.preson@some.domain"
        "critical.source=false"
      ]
    }
    {
      id: "joinVS"
      kind: "join"
      parentSources: ["hdfs_avro_source", "hdfs_orc_source"]
      joinBy: ["id"]
      joinType: "leftouter"
      persist: "memory_only"
      keyFields: ["id", "order_id"]
    }
    {
      id: "filterVS"
      kind: "filter"
      parentSources: ["kafka_source"]
      expr: ["key is not null"]
      keyFields: ["orderId", "dttm"]
    }
    {
      id: "selectVS"
      kind: "select"
      parentSources: ["table_source_1"]
      expr: [
        "count(id) as id_cnt",
        "sum(amount) as total_amount"
      ]
    }
    {
      id: "aggVS"
      kind: "aggregate"
      parentSources: ["hdfs_fixed_file"]
      groupBy: ["col1"]
      expr: [
        "avg(col2) as avg_col2",
        "sum(col3) as sum_col3"
      ],
      keyFields: ["col1", "avg_col2", "sum_col3"]
    }
  ]
}
```

--------------------------------

### Example Environment Variables

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/02-general-information/02-EnvironmentAndExtraVariables.md

Examples of environment variable names that conform to the specified regex pattern, demonstrating how to prefix variables for use in Hocon configuration.

```Environment Variables
DQ_STORAGE_PASSOWRD
dqMattermostToken
```

--------------------------------

### Example Application Configuration File

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/01-application-setup/01-ApplicationSettings.md

An example of a complete application configuration file using HOCON format, demonstrating settings for application name, date/time options, enablers, Spark options, storage, email, Mattermost, and encryption.

```HOCON
appConfig: {

  applicationName: "Custom Data Quality Application Name"
  
  dateTimeOptions: {
    timeZone: "GMT+3"
    referenceDateFormat: "yyyy-MM-dd"
    executionDateFormat: "yyyy-MM-dd-HH-mm-ss"
  }

  enablers: {
    allowSqlQueries: false
    allowNotifications: true
    aggregatedKafkaOutput: true
  }

  defaultSparkOptions: [
    "spark.sql.orc.enabled=true"
    "spark.sql.parquet.compression.codec=snappy"
    "spark.sql.autoBroadcastJoinThreshold=-1"
  ]

  storage: {
    dbType: "postgres"
    url: "localhost:5432/public"
    username: "postgres"
    password: "postgres"
    schema: "dqdb"
    saveErrorsToStorage: true
  }

  email: {
    host: "smtp.some-company.domain"
    port: "25"
    username: "emailUser"
    password: "emailPassword"
    address: "some.service@some-company.domain"
    name: "Data Quality Service"
    sslOnConnect: true
  }

  mattermost: {
    host: "https://some-team.mattermost.com"
    token: ${dqMattermostToken}
  }

  encryption: {
    secret: "secretmustbeatleastthirtytwocharacters"
    keyFields: ["password", "username", "url"]
    encryptErrorData: true
  }
}

```

--------------------------------

### Sources Configuration Example

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/03-Sources.md

Provides a HOCON configuration example showing how different source types (file, hive, table, kafka, greenplum) are grouped and defined. Each source type subsection contains a list of source configurations with their specific parameters.

```hocon
sources: {
  file: [
    {id: "hdfs_fixed_file", kind: "fixed", path: "path/to/fixed/file.txt", schema: "schema2"}
    {
      id: "hdfs_delimited_source",
      description: "Reading static data from CSV file"
      kind: "delimited",
      path: "path/to/csv/file.csv"
      schema: "schema1"
      medadata: [
        "data.owner=some.person@some.domain"
        "file.version=1.1"
      ]
    }
    {id: "hdfs_avro_source", kind: "avro", path: "path/to/avro/file.avro", schema: "avro_schema"}
    {id: "hdfs_orc_source", kind: "orc", path: "path/to/orc/file.orc"}
  ]
  hive: [
    {
      id: "hive_source_1", schema: "some_schema", table: "some_table",
      partitions: [{name: "load_date", values: ["2023-06-30", "2023-07-01"]}],
      keyFields: ["id", "name"]
    }
  ]
  table: [
    {id: "table_source_1", connection: "oracle_db1", table: "some_table", keyFields: ["id", "name"]}
    {id: "table_source_2", connection: "sqlite_db", table: "other_table"}
  ]
  kafka: [
    {
      id: "kafka_source_1",
      connection: "kafka_broker",
      topics: ["topic1.pub", "topic2.pub"]
      format: "json"
    }
    {
      id: "kafka_source_2",
      brokerId: "kafka_broker",
      topics: ["topic3.pub@[1,3]"]
      startingOffsets: "{\"topic3.pub\":{\"1\":1234,\"3\":2314}}"
      options: ["kafkaConsumer.pollTimeoutMs=300000"]
      format: "json"
    }
  ]
  greenplum: [
    {id: "greenplum_source_1", connection: "greenplum_db", table: "some_table"}
  ]
}
```

--------------------------------

### Result Target Configuration Examples

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/10-Targets.md

Examples demonstrating how to configure different types of result targets: saving to a file, storing in a Hive table, and sending to a Kafka topic. Each configuration specifies the target type and necessary parameters.

```yaml
results:
  - type: file
    resultTypes:
      - regularMetrics
      - composedMetrics
    save:
      path: "/path/to/results/output.json"
      format: "json"

  - type: hive
    resultTypes:
      - loadChecks
      - checks
    schema: "dq_results_schema"
    table: "dq_checks_table"

  - type: kafka
    resultTypes:
      - jobState
    connection: "kafka_connection_id"
    topic: "dq_results_topic"
    options:
      - "compression.type=gzip"
```

--------------------------------

### Checkita DQ Notification Template Example

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/10-Targets.md

Example of a Markdown notification template for Checkita Data Quality job alerts. It demonstrates how to use Mustache syntax to dynamically insert job details into the notification body.

```markdown
# Checkita Data Quality Notification - Failed Check Alert

You requested notifications on failed checks in Data Quality Job: `{{ jobId}}`.

Inform you that some watched checks have failed for job started for:

* Reference date: `{{ referenceDate }}`
* Execution date: `{{ executionDate }}`

Attached files contain information about failed checks. Please, review them.

```

--------------------------------

### HOCON Connections Configuration Example

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/01-Connections.md

An example HOCON configuration demonstrating how to group and define connections for various data sources including PostgreSQL, Oracle, SQLite, MySQL, MSSQL, H2, ClickHouse, Kafka, and Greenplum. Connections of the same type are grouped within subsections named after the connection type.

```hocon
jobConfig: {
  connections: {
    postgres: [
      {
        id: "postgre_db1",
        description: "Connection to production instance of DB"
        url: "postgre1.db.com:5432/public", 
        username: "dq-user", 
        password: "dq-password",
        metadata: [
          "db.owner=some.user@some.domain",
          "environment=prod"
        ]
      }
      {
        id: "postgre_db2",
        description: "Connection to test instance of DB"
        url: "postgre2.db.com:5432/public",
        username: "dq-user",
        password: "dq-password",
        schema: "dataquality",
        metadata: [
          "db.owner=some.user@some.domain",
          "environment=test"
        ]
      }
    ]
    oracle: [
      {id: "oracle_db1", url: "oracle.db.com:1521/public", username: "db-user", password: "dq-password"}
    ]
    sqlite: [
      {id: "sqlite_db", url: "some/path/to/db.sqlite"}
    ],
    mysql: [
      {id: "mysql_db1", url: "mysql.db.com:8306/public", username: "user", password: "pass"}
    ],
    mssql: [
      {id: "mssql_db1", url: "mssql.db.com:8433", username: "user", password: "pass"}
    ],
    h2: [
      {id: "h2_db1", url: "h2.db.com:9092/default", username: "user", password: "pass"}
    ],
    clickhouse: [
      {id: "clickhouse_db1", url: "clickhouse.db.com:8123", username: "user", password: "pass"}
    ],
    kafka: [
      {id: "kafka_cluster_1", servers: ["server1:9092", "server2:9092"]}
      {
        id: "kafka_cluster_2",
        servers: ["kafka-broker1:9092", "kafka-broker2:9092", "kafka-broker3:9092"]
        parameters: [
          "security.protocol=SASL_PLAINTEXT",
          "sasl.mechanism=GSSAPI",
          "sasl.kerberos.service.name=kafka-service"
        ]
      }
    ],
    greenplum: [
      {
        id: "greenplum_db1", 
        url: "greenplum.db.com:5432/postgres", 
        username: "user", 
        password: "pass",
        schema: "public"
      }
    ]
  }
}
```

--------------------------------

### Configure Summary Reporting Targets

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/12-JobConfigExample.md

Defines destinations for summary reports, including email, Mattermost, and Kafka.

```APIDOC
targets: {
  summary: {
    email: {
      attachMetricErrors: true
      metrics: ["hive_table_nulls", "fixed_file_dist_name", "table_source1_inn_regex"]
      dumpSize: 10
      recipients: ["some.person@some.domain"]
    },
    mattermost: {
      attachMetricErrors: true
      metrics: ["hive_table_nulls", "fixed_file_dist_name", "table_source1_inn_regex"]
      dumpSize: 10
      recipients: ["@someUser", "#someChannel"]
    },
    kafka: {
      connection: "kafka_broker"
      topic: "dev.dq_results.topic"
    }
  }
}
```

--------------------------------

### Spark DataFrame Reader Example

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/03-Sources.md

Demonstrates how a Spark DataFrame reader is dynamically configured using format, schema, options, and path parameters. Missing optional parameters result in the corresponding reader configuration not being set.

```scala
val df = spark.read.format(format).schema(schema).options(options).load(path)
```

--------------------------------

### HOCON Configuration Example for Load Checks

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/07-LoadChecks.md

This example demonstrates how to configure various load checks within the 'loadChecks' section of a HOCON job configuration file. It showcases the structure for 'minColumnNum', 'exactColumnNum', 'columnsExist', and 'schemaMatch' checks, including common and specific parameters.

```hocon
jobConfig: {
  loadChecks: {
    minColumnNum: [
      {id: "load_check_1", source: "kafka_source", option: 2}
    ]
    exactColumnNum: [
      {
        id: "load_check_2", 
        description: "Checking that source has exactly required number of columns", 
        source: "hdfs_delimited_source", option: 3
        metadata: [
          "critical.loadcheck=true"
        ]
      }
    ]
    columnsExist: [
      {id: "loadCheck3", source: "sqlVS", columns: ["id", "name", "entity", "description"]},
      {id: "load_check_4", source: "hdfs_delimited_source", columns: ["id", "name", "value"]}
    ]
    schemaMatch: [
      {id: "load_check_5", source: "kafka_source", schema: "hive_schema"}
    ]
  }
}
```

--------------------------------

### Configure Snapshot Checks

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/12-JobConfigExample.md

Defines snapshot-based data quality checks, comparing current data against a reference or a fixed threshold.

```APIDOC
checks: {
  snapshot: {
    differByLT: [
      {
        id: "row_cnt_diff",
        description: "Number of rows in two tables should not differ on more than 5%.",
        metric: "hive_table_row_cnt"
        compareMetric: "csv_file_row_cnt"
        threshold: 0.05
      }
    ],
    equalTo: [
      {id: "zero_nulls", description: "Hive Table1 mustn't contain nulls", metric: "hive_table_nulls", threshold: 0}
    ],
    greaterThan: [
      {id: "completeness_check", metric: "orc_data_compl", threshold: 0.99}
    ],
    lessThan: [
      {id: "null_threshold", metric: "pct_of_null", threshold: 0.01}
    ]
  }
}
```

--------------------------------

### Configure Results Targets

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/12-JobConfigExample.md

Defines destinations for storing data quality check results, including file, Hive, and Kafka.

```APIDOC
targets: {
  results: {
    file: {
      resultTypes: ["checks", "loadChecks"]
      save: {
        kind: "delimited"
        path: ${basePath}"/results/"${referenceDate}
        header: true
      }
    },
    hive: {
      resultTypes: ["regularMetrics", "composedMetrics", "loadChecks", "checks"],
      schema: "DQ_SCHEMA",
      table: "DQ_TARGETS"
    },
    kafka: {
      resultTypes: ["regularMetrics", "composedMetrics", "loadChecks", "checks"],
      connection: "kafka_broker"
      topic: "some.topic"
    }
  }
}
```

--------------------------------

### Submit Checkita Data Quality Application (YARN Cluster Mode)

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/01-application-setup/02-ApplicationSubmit.md

An example of submitting a Checkita Data Quality batch application to a YARN cluster in cluster mode. It includes setting environment variables for paths and passing application/job configurations and extra variables.

```Bash
export DQ_APPLICATION="<local or remote (HDFS, S3) path to application jar>"
export DQ_DEPENDENCIES="<local or remote (HDFS, S3) path to uber-jar with framework dependencies>"
export DQ_APP_CONFIG="<local or remote (HDFS, S3) path to application configuration file>"
export DQ_JOB_CONFIGS="<local or remote (HDFS, S3) paths to job configuration files separated by commas>"

# As configuration files are uploaded to driver and executors they will be located in working directories.
# Therefore, in application arguments it is required to list just their file names:
export DQ_APP_CONFIG_FILE=$(basename $DQ_APP_CONFIG)
export DQ_JOB_CONFIG_FILES="<job configuration files separated by commas (only file names)>"
export REFERENCE_DATE="2023-08-01"

# application entry point (executable class): org.checkita.dqf.apps.batch.DataQualityBatchApp
# --name spark-submit argument has a higher priority over application name set in `application.conf`

spark-submit\
   --class org.checkita.dqf.apps.batch.DataQualityBatchApp \
   --name "Checkita Data Quality" \
   --master yarn \
   --deploy-mode cluster \
   --num-executors 1 \
   --executor-memory 2g \
   --executor-cores 4 \
   --driver-memory 2g \
   --jars $DQ_DEPENDENCIES \
   --files "$DQ_APP_CONFIG,$DQ_DQ_JOB_CONFIGS" \
   --conf "spark.executor.memoryOverhead=2g" \
   --conf "spark.driver.memoryOverhead=2g" \
   --conf "spark.driver.maxResultSize=4g" \
   $DQ_APPLICATION \
   -a $DQ_APP_CONFIG_FILE \
   -j $DQ_JOB_CONFIG_FILES \
   -d $REFERENCE_DATE \
   -e "storage_db_user=some_db_user,storage_db_password=some_db_password"
```

--------------------------------

### HOCON Job Configuration for Checkita Data Quality

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/12-JobConfigExample.md

This snippet showcases a complete HOCON configuration for a Checkita Data Quality job. It covers defining connections to various databases and Kafka, specifying data schemas (delimited, fixed-width, Hive, Avro), configuring different data sources, creating virtual data sources through transformations like SQL queries, joins, filters, and aggregations, setting up load checks for data integrity, and defining metrics for data quality monitoring.

```HOCON
jobConfig: {
  jobId: "job_id_for_this_configuration"

  connections: {
    oracle: [
      {id: "oracle_db1", url: "oracle.db.com:1521/public", username: "db-user", password: "dq-password"}
    ]
    sqlite: [
      {id: "sqlite_db", url: "some/path/to/db.sqlite"}
    ],
    kafka: [
      {id: "kafka_broker", servers: ["server1:9092", "server2:9092"]}
    ]
  }

  schemas: [
    {
      id: "schema1"
      kind: "delimited"
      schema: [
        {name: "colA", type: "string"},
        {name: "colB", type: "timestamp"},
        {name: "colC", type: "decimal(10, 3)"}
      ]
    },
    {
      id: "schema2"
      kind: "fixedFull",
      schema: [
        {name: "col1", type: "integer", width: 5},
        {name: "col2", type: "double", width: 6},
        {name: "col3", type: "boolean", width: 4}
      ]
    },
    {id: "schema3", kind: "fixedShort", schema: ["colOne:5", "colTwo:7", "colThree:9"]}
    {id: "hive_schema", kind: "hive", schema: "some_schema", table: "some_table"}
    {id: "avro_schema", kind: "avro", schema: "some/path/to/avro_schema.avsc"}

  ]

  sources: {
    table: [
      {id: "table_source_1", connection: "oracle_db1", table: "some_table", keyFields: ["id", "name"]}
      {id: "table_source_2", connection: "sqlite_db", table: "other_table"}
    ]
    hive: [
      {
        id: "hive_source_1", schema: "some_schema", table: "some_table",
        partitions: [{name: "dlk_cob_date", values: ["2023-06-30", "2023-07-01"}]},
        keyFields: ["id", "name"]
      }
    ]
    file: [
      {id: "hdfs_avro_source", kind: "avro", path: "path/to/avro/file.avro", schema: "avro_schema"},
      {id: "hdfs_orc_source", kind: "orc", path: "path/to/orc/file.orc"},
      {
        id: "hdfs_delimited_source",
        kind: "delimited",
        path: "path/to/csv/file.csv"
        schema: "schema1"
      },
      {id: "hdfs_fixed_file", kind: "fixed", path: "path/to/fixed/file.txt", schema: "schema2"},
    ],
    kafka: [
      {
        id: "kafka_source",
        connection: "kafka_broker",
        topics: ["topic1.pub", "topic2.pub"]
        format: "json"
      }
    ]
  }

  virtualSources: [
    {
      id: "sqlVS"
      kind: "sql"
      parentSources: ["hive_source_1"]
      persist: "disk_only"
      save: {
        kind: "orc"
        path: ${basePath}"/sqlVs"
      }
      query: "select id, name, entity, description from hive_source_1 where dlk_cob_date == '2023-06-30'"
    }
    {
      id: "joinVS"
      kind: "join"
      parentSources: ["hdfs_avro_source", "hdfs_orc_source"]
      joinBy: ["id"]
      joinType: "leftouter"
      persist: "memory_only"
      keyFields: ["id", "order_id"]
    }
    {
      id: "filterVS"
      kind: "filter"
      parentSources: ["kafka_source"]
      expr: ["key is not null"]
      keyFields: ["batchId", "dttm"]
    }
    {
      id: "selectVS"
      kind: "select"
      parentSources: ["table_source_1"]
      expr: [
        "count(id) as id_cnt",
        "count(name) as name_cnt"
      ]
    }
    {
      id: "aggVS"
      kind: "aggregate"
      parentSources: ["hdfs_fixed_file"]
      groupBy: ["col1"]
      expr: [
        "avg(col2) as avg_col2",
        "sum(col3) as sum_col3"
      ],
      keyFields: ["col1", "avg_col2", "sum_col3"]
    }
  ]

  loadChecks: {
    exactColumnNum: [
      {id: "loadCheck1", source: "hdfs_delimited_source", option: 3}
    ]
    minColumnNum: [
      {id: "loadCheck2", source: "kafka_source", option: 2}
    ]
    columnsExist: [
      {id: "loadCheck3", source: "sqlVS", columns: ["id", "name", "entity", "description"]},
      {id: "load_check_4", source: "hdfs_delimited_source", columns: ["id", "name", "value"]}
    ]
    schemaMatch: [
      {id: "load_check_5", source: "kafka_source", schema: "hive_schema"}
    ]
  }

  metrics: {
    regular: {
      rowCount: [
        {id: "hive_table_row_cnt", description: "Row count in hive_source_1", source: "hive_source_1"},
        {id: "csv_file_row_cnt", description: "Row count in hdfs_delimited_source", source: "hdfs_delimited_source"}
      ]
      distinctValues: [
        {
          id: "fixed_file_dist_name", description: "Distinct values in hdfs_fixed_file",
          source: "hdfs_fixed_file", columns: ["colA"]
        }
      ]
      nullValues: [
        {id: "hive_table_nulls", description: "Null values in columns id and name", source: "hive_source_1", columns: ["id", "name"]}
      ]
      completeness: [
        {id: "orc_data_compl", description: "Completness of column id", source: "hdfs_orc_source", columns: ["id"]}
      ]
      avgNumber: [
        {id: "avro_file1_avg_bal", description: "Avg number of column balance", source: "hdfs_avro_source", columns: ["balance"]}
      ]
      regexMatch: [
        {

```

--------------------------------

### Top N Metric Example SQL Query (SQL)

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/02-general-information/06-MetricSQLEquivalency.md

An example SQL query demonstrating how to find the top N most frequent values and their occurrences in a column. This is an exact calculation, unlike the metric's approximate method.

```sql
SELECT value, num_occurrences / row_cnt AS freq
FROM (
  SELECT CAST(textCol1 AS STRING) AS value,
         COUNT(1) AS num_occurrences
  FROM sample_table
  WHERE CAST(textCol1 AS STRING) IS NOT NULL
  GROUP BY CAST(textCol1 AS STRING)
) t1 CROSS JOIN (
  SELECT COUNT(1) AS row_cnt
  FROM sample_table
  WHERE CAST(textCol1 AS STRING) IS NOT NULL
) t2
ORDER BY num_occurrences DESC
LIMIT 2;
```

--------------------------------

### Checkita Application Command-Line Arguments

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/01-application-setup/02-ApplicationSubmit.md

Lists and describes the command-line arguments required and optional for running Checkita Data Quality applications. These arguments control application settings, job configurations, execution context, and logging.

```APIDOC
-a <path_to_hocon_file>
  Required. Path to the HOCON file containing application settings.

-j <comma_separated_paths>
  Required. List of paths to job configuration files, separated by commas.

-d <date_string>
  Optional. Datetime for which the Data Quality job is run. Must conform to referenceDateFormat. Ignored for streaming applications.

-l
  Optional. Flag to run the application in local mode.

-s
  Optional. Flag to use a Shared Spark Context, preventing the application from stopping the existing context.

-m
  Optional. Flag to perform storage database migration before saving results.

-e "key=value,key=value,..."
  Optional. Extra variables to add to configuration during parsing, useful for passing secrets.

-v
  Optional. Application log verbosity level. Defaults to INFO.
```

--------------------------------

### SQL Virtual Source Configuration

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/05-VirtualSources.md

Configuration for creating a virtual source using an arbitrary SQL query. Requires the 'allowSqlQueries' setting to be true and specifies the SQL query to execute.

```APIDOC
SQLVirtualSource:
  kind: "sql" (Required) - Specifies the virtual source type as SQL.
  query: string (Required) - The SQL query to execute. Parent source IDs are referenced directly within the query.
```

--------------------------------

### Xerial Snappy Java Dependency

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.13-3.3.4.txt

Java implementation of the Snappy compression algorithm.

```maven
org.xerial.snappy:snappy-java:1.1.10.5
```

--------------------------------

### Configuration and Dependency Injection

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.13-3.3.0.txt

Libraries for managing application configuration and dependency injection, including PureConfig and Typesafe Config.

```Maven
com.github.pureconfig:pureconfig-core_2.13:0.17.3
```

```Maven
com.github.pureconfig:pureconfig-generic-base_2.13:0.17.3
```

```Maven
com.github.pureconfig:pureconfig-generic_2.13:0.17.3
```

```Maven
com.github.pureconfig:pureconfig_2.13:0.17.3
```

```Maven
com.github.scopt:scopt_2.13:4.1.0
```

```Maven
com.typesafe:config:1.4.2
```

```Maven
eu.timepit:refined-pureconfig_2.13:0.10.3
```

```Maven
eu.timepit:refined-scopt_2.13:0.10.3
```

--------------------------------

### Miscellaneous Utilities

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.13-3.3.0.txt

Various utility libraries including compression, parsing, and Java EWah.

```Maven
com.esotericsoftware:kryo-shaded:4.0.2
```

```Maven
com.univocity:univocity-parsers:2.9.1
```

```Maven
com.googlecode.javaewah:JavaEWAH:1.1.13
```

```Maven
jline:jline:2.12
```

```Maven
com.github.spullara.mustache.java:compiler:0.9.10
```

--------------------------------

### Get Percentile Metric

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/08-Metrics.md

Calculates the percentile value (quantile in %) corresponding to a specified number from column values, using the TDigest library. This metric is the inverse of the Get Quantile Metric, works with one column, and is not reversible. It returns a Failure status for rows where the column value cannot be cast to a number.

```APIDOC
getPercentile:
  __init__(column: str, accuracyError: float = 0.01, target: float)
    Calculates the percentile value for a specified number within the column's values.
    Parameters:
      column: The column to calculate the percentile for.
      accuracyError: Optional, default is 0.01. Accuracy error for percentile calculation.
      target: Required. The number from the column's values for which the percentile is determined.
    Returns: The calculated percentile or Failure status.
```

--------------------------------

### Database Migration Dependencies

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.12-3.2.1.txt

Libraries for database schema migration management using Flyway, supporting various database dialects.

```maven
org.flywaydb:flyway-core:9.21.1
org.flywaydb:flyway-database-oracle:9.21.1
org.flywaydb:flyway-mysql:9.21.1
org.flywaydb:flyway-sqlserver:9.21.1
```

--------------------------------

### Configure Check Alerting

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/12-JobConfigExample.md

Defines specific alerts for data quality checks, routing them to email and Mattermost.

```APIDOC
targets: {
  checkAlerts: {
    email: [
      {
        id: "alert1"
        checks: ["avg_bal_check", "zero_nulls"]
        recipients: ["some.peron@some.domain"]
      },
      {
        id: "alert2"
        checks: ["top2_curr_match", "completeness_check"]
        recipients: ["another.peron@some.domain"]
      }
    ],
    mattermost: [
      {
        id: "alert3"
        checks: ["avg_bal_check", "zero_nulls"]
        recipients: ["@someUser"]
      },
      {
        id: "alert4"
        checks: ["top2_curr_match", "completeness_check"]
        recipients: ["#someChannel"]
      }
    ]
  }
}
```

--------------------------------

### Metrics and Monitoring

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.13-3.3.0.txt

Libraries for collecting and reporting application metrics, including Dropwizard Metrics.

```Maven
io.dropwizard.metrics:metrics-core:4.2.7
```

```Maven
io.dropwizard.metrics:metrics-graphite:4.2.7
```

```Maven
io.dropwizard.metrics:metrics-jmx:4.2.7
```

```Maven
io.dropwizard.metrics:metrics-json:4.2.7
```

```Maven
io.dropwizard.metrics:metrics-jvm:4.2.7
```

```Maven
com.github.joshelser:dropwizard-metrics-hadoop-metrics2-reporter:0.1.2
```

--------------------------------

### Join Virtual Source Configuration

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/05-VirtualSources.md

Configuration for creating a virtual source by joining exactly two parent sources. Requires specifying the columns to join by and the type of join operation.

```APIDOC
JoinVirtualSource:
  kind: "join" (Required) - Specifies the virtual source type as Join.
  joinBy: array<string> (Required) - List of column names to use for joining. Parent sources must have matching column names.
  joinType: string (Required) - The type of Spark join to perform. Supported types include: inner, outer, cross, full, right, left, semi, anti, fullOuter, rightOuter, leftOuter, leftSemi, leftAnti.
```

--------------------------------

### Configure Error Collection Targets

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/12-JobConfigExample.md

Specifies destinations for collecting detailed error information, including file and Kafka.

```APIDOC
targets: {
  errorCollection: {
    file: {
      metrics: ["pct_of_null", "hive_table_row_cnt", "hive_table_nulls"]
      dumpSize: 50
      save: {
        kind: "orc"
        path: ${basePath}"/errors/"${referenceDate}
      }
    },
    kafka: {
      metrics: ["hive_table_nulls", "fixed_file_dist_name", "table_source1_inn_regex"]
      dumpSize: 25
      connection: "kafka_broker"
      topic: "some.topic"
      options: ["addParam=true"]
    }
  }
}
```

--------------------------------

### Serialization and Data Formats

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.13-3.3.0.txt

Libraries for serialization (Kryo, Chill) and data formats (Protocol Buffers, FlatBuffers).

```Maven
com.esotericsoftware:kryo-shaded:4.0.2
```

```Maven
com.esotericsoftware:minlog:1.3.0
```

```Maven
com.google.protobuf:protobuf-java:3.21.9
```

```Maven
com.google.flatbuffers:flatbuffers-java:1.12.0
```

```Maven
com.twitter:chill-java:0.10.0
```

```Maven
org.antlr:ST4:4.0.4
```

```Maven
org.antlr:antlr-runtime:3.5.2
```

```Maven
org.antlr:antlr4-runtime:4.8
```

--------------------------------

### Networking and Compression

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.13-3.3.0.txt

Libraries for network communication (Netty) and data compression (Zstd, LZF).

```Maven
io.netty:netty-all:4.1.74.Final
```

```Maven
io.netty:netty-buffer:4.1.74.Final
```

```Maven
io.netty:netty-codec:4.1.74.Final
```

```Maven
io.netty:netty-common:4.1.74.Final
```

```Maven
io.netty:netty-handler:4.1.74.Final
```

```Maven
io.netty:netty-resolver:4.1.74.Final
```

```Maven
io.netty:netty-tcnative-classes:2.0.48.Final
```

```Maven
io.netty:netty-transport:4.1.74.Final
```

```Maven
io.netty:netty-transport-classes-epoll:4.1.74.Final
```

```Maven
io.netty:netty-transport-classes-kqueue:4.1.74.Final
```

```Maven
io.netty:netty-transport-native-epoll:4.1.74.Final
```

```Maven
io.netty:netty-transport-native-kqueue:4.1.74.Final
```

```Maven
io.netty:netty-transport-native-unix-common:4.1.74.Final
```

```Maven
com.github.luben:zstd-jni:1.5.5-1
```

```Maven
com.ning:compress-lzf:1.1
```

```Maven
io.airlift:aircompressor:0.21
```

--------------------------------

### Parquet File Output Configuration

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/11-FileOutputs.md

Example configuration for saving Checkita results to a Parquet file. Specifies the output format and the target directory.

```hocon
{
  kind: "parquet"
  path: "/tmp/parquet_file_ooutput"
}
```

--------------------------------

### Configure Trend Checks

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/12-JobConfigExample.md

Defines time-series based data quality checks, including average bounds, and top N rank comparisons against historical data.

```APIDOC
checks: {
  trend: {
    averageBoundFull: [
      {
        id: "avg_bal_check",
        description: "Check that average balance stays within +/-25% of the week average"
        metric: "avro_file1_avg_bal",
        rule: "datetime"
        windowSize: "8d"
        threshold: 0.25
      }
    ],
    averageBoundUpper: [
      {id: "avg_pct_null", metric: "pct_of_null", rule: "datetime", windowSize: "15d", threshold: 0.5}
    ],
    averageBoundLower: [
      {id: "avg_distinct", metric: "fixed_file_dist_name", rule: "record", windowSize: 31, threshold: 0.3}
    ],
    averageBoundRange: [
      {
        id: "avg_inn_match",
        metric: "table_source1_inn_regex",
        rule: "datetime",
        windowSize: "8d",
        thresholdLower: 0.2,
        thresholdUpper: 0.4
      }
    ],
    topNRank: [
      {id: "top2_curr_match", metric: "filterVS_top3_currency", targetNumber: 2, threshold: 0.1}
    ]
  }
}
```

--------------------------------

### Get Percentile Value Metric

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/02-general-information/07-MetricCalculatorEngines.md

Calculates a specific percentile value for a given column. It requires the source data, the column, and parameters for accuracy error and target percentile.

```hocon
SRC = "ytd"
jobConfig: {
  jobId: "yellow_trip_data"
  jobDescription: "Data Quality job for NY Taxi Data"
  sources: {
    file: [
      {
        id: ${SRC},
        kind: "parquet",
        path: "/datalake/data/workspace/mlcib/ruadlu/yellow_trip_data"
        keyFields: ["vendor_name", "trip_pickup_datetime"]
      }
    ]
  }
  metrics: {
    distribution: {
      getPercentile: [{
        id: ${SRC}"_get_percent_val", source: ${SRC}, columns: ["fare_amt"],
        params: {accuracyError: 0.001, target: 50.0}
      }]
    }
  }
}
```

--------------------------------

### Get Quantile Check

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/02-general-information/07-MetricCalculatorEngines.md

Calculates and checks a specific quantile (e.g., 0.5 for median, 0.25 for first quartile) of a numeric column, with an optional accuracy error parameter.

```hocon
getQuantile: [{
  id: ${SRC}"_get_quant_val",
  source: ${SRC},
  columns: ["fare_amt"],
  params: {accuracyError: 0.001}
}]
```

--------------------------------

### Database Connectivity and ORM

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.13-3.3.0.txt

Database drivers and connection pooling libraries for various databases including H2, MySQL, SQL Server, and Oracle.

```Maven
com.clickhouse:clickhouse-jdbc:0.4.6
```

```Maven
com.h2database:h2:1.4.196
```

```Maven
com.jolbox:bonecp:0.8.0.RELEASE
```

```Maven
com.microsoft.sqlserver:mssql-jdbc:12.2.0.jre8
```

```Maven
com.mysql:mysql-connector-j:8.0.33
```

```Maven
com.oracle.database.jdbc:ojdbc8:23.2.0.0
```

```Maven
com.zaxxer:HikariCP:4.0.3
```

```Maven
com.typesafe.slick:slick-hikaricp_2.13:3.4.1
```

```Maven
com.typesafe.slick:slick_2.13:3.4.1
```

```Maven
commons-dbcp:commons-dbcp:1.4
```

```Maven
commons-pool:commons-pool:1.5.4
```

```Maven
net.sourceforge.jtds:jtds:1.3.1
```

```Maven
org.apache.derby:derby:10.14.2.0
```

```Maven
javax.jdo:jdo-api:3.0.1
```

```Maven
javax.transaction:jta:1.1
```

```Maven
javax.transaction:transaction-api:1.1
```

--------------------------------

### Delimited File Output Configuration

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/11-FileOutputs.md

Example configuration for saving Checkita results to a delimited text file (e.g., CSV or TSV). Includes options for specifying the path and whether to include a header row.

```hocon
{
  kind: "delimited"
  path: "/tmp/dataquality/results"
  header: true
}
```

--------------------------------

### Roaring Bitmap Dependencies

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.13-3.3.4.txt

High-performance compressed bitsets.

```maven
org.roaringbitmap:RoaringBitmap:0.9.25
org.roaringbitmap:shims:0.9.25
```

--------------------------------

### HOCON Multiline String Example

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/03-Sources.md

Demonstrates how to define multiline string values in HOCON format using triple quotes. This is useful for embedding SQL queries or other multi-line configurations within the source definition.

```hocon
multilineString: """
  SELECT * from schema.table
  WHERE load_date = '2023-08-23';
"""
```

--------------------------------

### Define Data Quality Rules

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/12-JobConfigExample.md

Specifies various data quality rules including regex matching, domain validation, top N selection, Levenshtein distance, and composed metrics using formulas.

```APIDOC
dataQualityRules: {
  regexMatch: [
    {
      id: "table_source1_inn_regex", description: "Regex match for inn column", source: "table_source_1",
      columns: ["inn"], params: {regex: "^\\d{10}$"},
      reversed: true
    }
  ],
  stringInDomain: [
    {
      id: "orc_data_segment_domain", source: "hdfs_orc_source",
      columns: ["segment"], params: {domain: ["FI", "MID", "SME", "INTL", "CIB"]},
      reversed: true
    }
  ],
  topN: [
    {
      id: "filterVS_top3_currency", description: "Top 3 currency in filterVS", source: "filterVS",
      columns: ["id"], params: {targetNumber: 3, maxCapacity: 10}
    }
  ],
  levenshteinDistance: [
    {
      id: "lvnstDist", source: "table_source_2", columns: ["col1", "col2"],
      params: {normalize: true, threshold: 0.3}
    }
  ]
},
composed: [
  {
    id: "pct_of_null", description: "Percent of null values in hive_table1",
    formula: "100 * {{ hive_table_nulls }} ^ 2 / ( {{ hive_table_row_cnt }} + 1)"
  }
]
```

--------------------------------

### WildFly OpenSSL Dependency

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.13-3.3.4.txt

OpenSSL integration for WildFly.

```maven
org.wildfly.openssl:wildfly-openssl:1.0.7.Final
```

--------------------------------

### Virtual Sources Common Parameters

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/05-VirtualSources.md

Defines common configuration parameters applicable to all virtual source types in Checkita, including ID, description, parent sources, caching, saving, key fields, and metadata.

```APIDOC
VirtualSourceCommonParameters:
  id: string (Required) - Unique identifier for the virtual source.
  description: string (Optional) - A human-readable description of the virtual source.
  parentSources: array<string> (Required) - List of IDs of parent sources used to create this virtual source.
  persist: SparkStorageLevel (Optional) - Specifies how the virtual source should be cached (e.g., MEMORY_ONLY, DISK_ONLY). Defaults to not cached.
  save: FileOutputConfiguration (Optional) - Configuration for saving the virtual source to a file.
  keyFields: array<string> (Optional) - List of columns used to identify rows, primarily for error reporting.
  metadata: map<string, string> (Optional) - User-defined key-value pairs for metadata.
```

--------------------------------

### Get Quantile Metric

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/08-Metrics.md

Calculates an arbitrary quantile for values in a specified column using the TDigest library. This metric works with only one column and is not reversible. It returns a Failure status for rows where the column value cannot be cast to a number.

```APIDOC
getQuantile:
  __init__(column: str, accuracyError: float = 0.01, target: float)
    Calculates an arbitrary quantile for the values in the specified column.
    Parameters:
      column: The column to calculate the quantile for.
      accuracyError: Optional, default is 0.01. Accuracy error for quantile calculation.
      target: Required. A number in the interval [0, 1] corresponding to the quantile to be calculated.
    Returns: The calculated quantile value or Failure status.
```

--------------------------------

### Security and Cryptography

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.13-3.3.0.txt

Libraries for security and cryptography, including Google Tink.

```Maven
com.google.crypto.tink:tink:1.6.1
```

--------------------------------

### Get Percentile Metric Description

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/02-general-information/06-MetricSQLEquivalency.md

Describes the process for calculating a specific percentile value using the T-Digest algorithm when no direct SQL equivalent exists. It involves incrementing the T-Digest calculator with numeric values after casting and omitting nulls.

```text
There is no equivalent Spark SQL function to calculate percentile value for provided number from collection. But the general process remains the same: value is cast to number and omitted from computation if casting yields null. After that T-Digest calculator is incremented with resultant numeric value and, after all values are consumed, the final result of the calculator is returned.
```

--------------------------------

### JetBrains Annotations

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/dependencies/checita-core-dependencies-2.13-3.3.4.txt

Annotations for IDEs and static analysis tools from JetBrains.

```maven
org.jetbrains:annotations:17.0.0
```

--------------------------------

### Kafka Connection Configuration Parameters

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/03-job-configuration/01-Connections.md

Outlines the parameters for establishing a connection to Kafka brokers. Requires an ID and a list of servers, with optional Spark and metadata parameters for authentication and other settings.

```APIDOC
Kafka Connection Configuration:
  id: string (Required). Connection ID.
  description: string (Optional). Connection description.
  servers: list<string> (Required). List of Kafka broker servers.
  parameters: list<string> (Optional). Spark parameters in 'spark.param.name=spark.param.value' format. Used for Kafka authorization settings.
  metadata: list<string> (Optional). User-defined metadata parameters in 'param.name=param.value' format.
```

--------------------------------

### Get Quantile Metric Configuration and SQL

Source: https://github.com/raiffeisen-dgtl/checkita-data-quality/blob/main/docs/02-general-information/06-MetricSQLEquivalency.md

Retrieves a specific quantile (percentile) value from a numeric column using the T-Digest algorithm. The HOCON configuration specifies the target quantile and an optional accuracy error, with the SQL query using the PERCENTILE function.

```hocon
getQuantile: [{
  id: "get_quantile",
  source: "sample_table",
  columns: ["numCol1"],
  params: {
    accuracyError: 0.001,
    target: 0.85
  }
}]
```

```sql
SELECT PERCENTILE(CAST(numCol1 AS DOUBLE), 0.85) AS get_quantile
FROM sample_table;
```