Documentation

Dashboard

Overview

Dashboard gives an overview of current data quality based on the validations inserted into the application. It contains status-based charts for selected test suites/profilings, schedule, information about ongoing and upcoming executions, and logs.

Overall data quality

Current data quality for test cases and profilings. Only test cases that are in some test suites are included. Test cases can be filtered by test suites and profiling objects by connection and object name. Only test suites/profilings, that the user has access to, are displayed.

Daily data quality

Data quality history (30 days) by statuses for filtered test suites/profilings. Clicking on specific statuses in legend shows/hide these statuses from the chart.

Daily executions chart

Daily count of executions and statuses for filtered test suites/profilings.

Executions

Schedule

Alarms

Logs

Logs can be refreshed by clicking on the refresh icon. Detailed history view of logs can be opened by clicking on the list button:

General logs for user and system actions / SQL execution logs / Security logs / VCS logs

Connections

Overview

Connections page is for managing all databases that are connected to the DQM.

  • Add new connections (by clicking “Add new connection” button)
  • Manage existing connections (change settings by clicking on a connection)
  • View and manage metadata for a connection
  • Check connection status (update status by clicking on status icon)

Adding a new connection

Only possible with ADD/EDIT CONNECTIONS privileges.

  1. On Connections page click “Add new connection” button:
  2. Insert database connection information:
  • Driver/Connector – database JDBC driver (see chapter Connector settings to add new drivers)
  • Connection name – custom name for the connection
  • Server – database server address
  • Port – database port
  • Database name – database name in server
  • Properties – additional connection properties
  • User – database user (recommended to use user with only SELECT privileges)
  • Password – password for the user
  • Parallel queries – maximum parallel connections to database for a user
  • Query timeout (s) – maximum number of seconds the connection waits for a response from the database
  1. Click “Add new connection” button
  2. New connection will appear on the list and connection status will show if connection was successful (to use database’s metadata for test creation see chapter Metadata)

Example Spark SQL/Databricks connection

Make sure User is inserted as a “token” and the generated token is inserted as a password. 
Spark java driver for Databricks: https://databricks.com/spark/jdbc-drivers-download

Guide on how to create tokens in Databricks:
https://docs.databricks.com/dev-tools/api/latest/authentication.html#token-management

Example Redshift connection

Example Snowflake connection

Deleting an existing connection

  1. Click on the connection you would like to delete
  1. From opened window click “Delete connection” button

Metadata

Metadata is used to automatically create test cases, generate SQLs and manage Profiling. Metadata can be managed by clicking on a specific connection: 

  • Select schemas or insert them manually (comma separated):
  • Click “+” button to import schemas
  • Metadata should now be available

Metadata is automatically updated every 120 minutes (can be changed in Settings -> General) to be in sync with available objects and columns in the database. Metadata can also be manually updated or deleted by selecting schemas and clicking “Update selected” or “Delete selected” button:

Test suites

Overview

Test suites page allows users to create and manage test suites, which are used to package test cases. Click on a test suite to:

  • Edit – edit test suite properties, scheduling and alerts
  • View test cases – open test cases page for this test suite
  • Execute – execute the test suite
  • Get report – download HTML report with latest results for the test suite

Adding a new test suite

  1. Press the “Add new test suite” button on the sidebar
  2. Fill in the test suite properties
  • Test suite name – display name for test suite
  • Directory – directory of the test suite
  • Tags – see chapter Adding tags to a test suite
  • Private – check if test suite should not be visible and executable by other users
  • Results in report – sample results of test cases (max 10 rows) are displayed in HTML report
  • Schedule – schedule when the test suite should be executed.
  • Allowed errors – % value of errors allowed. When the test failure percentage is larger than the allowed threshold, an alert will be sent (0% to send an alert if any of the tests fails)
  • Webhook – Webhook to which an alert is sent if error threshold is exceeded
  • Emails – comma separated emails to which report is sent if error threshold is exceeded
  1. Click on “Add new test suite” button

Deleting a test suite

  1. Find the test suite you want to delete and click “edit” icon
  1. Press the “Delete test suite” button
  2. When prompted with following message
  • Delete only test suite – only test suite will be deleted. All tests will remain in DQM without a test suite reference
  • Delete with test cases – all tests that are in the test suite will also be deleted

Modifying a test suite

  1. Find the test suite and click “edit”
  2. Modify properties that need to be changed
  3. Press “Save changes” button

Adding tags to a test suite

  1. Locate the test suite where you want to change tags and click “edit”
  2. Insert or delete tags from “Tags” field

Note For different colors you can also use red:, blue:, green:, black: (e.g. “blue:tagName”)

  1. Press “Save changes” button

Directories

Each test suite can be assigned into a custom directory to create a directory-based structure for test suite management. Directories can be created by clicking on a plus sign on the sidebar. To create or edit sub-directories, hover on a directory name and click the plus icon or pencil icon.

Adding test suites to directories

To add a test suite to a directory, hover on a test suite name. A directory icon should appear. Click and grab the icon and drag it to a directory. To remove a directory, drag it to “Unassign” section in the sidebar. Directories can also be changed by editing a test suite.

Profiling

Overview

Profiling page allows users to set profiling rules for selected objects and columns. This allows the DQM to analyze data based on profiling rule execution results and report results to find possible anomalies.

Adding a new object to profile

  1. Press “Add objects” button on the sidebar
  2. Filter out objects you wish to profile
  1. Choose an object to profile by pressing “+” button or checking checkboxes and clicking “Insert selected” button
  2. New profiling object will now be shown on the page

Note: If “Add columns” is set as enabled, then all columns with rules will be generated automatically when adding an object.

Modifying profiling object

Opening profiling object allows to:

  • Schedule rules to run
  • Set alerts for scheduled executions
  • Add SQL filters to profiled object
  • Execute rules manually
  • Add/remove columns to profile
  • Add/remove rules for a column
  • Delete object/remove profiling for an object
  • Set threshold for the object

Deleting a profiling object 

  1. Open profiling object and click “Delete object” button
  1. Select object from list by clicking “Add objects” button and click “-” icon

Column values

It is possible to analyze how values in column change over time. This allows the application to detect deviations/anomalies in date and alert users.

By default, column values are analyzed by finding counts for each value. It is possible to change the SQL used to get column value results by clicking “SQL” button:

You can use predefined queries to set SQL for finding counts for each value or for range (numeric values):

Disabling column value rule execution

It is possible to disable column values from being analyzed my clicking the slider button:

Delete values from column value history

You can delete values that no longer exist in column by clicking the trashcan icon for a value:

Exclude values from column value history

Excluded value results are still saved, but are no longer analyzed for anomalies (excluded values do not flag as failed). You can exclude values by clicking the exclude icon for a value:

Exclude executions from column value history

To exclude column value executions from history, simply open the execution history list and deselect executions:

Profiling rules

Profiling rules are used to profile data for columns. By default, a set of rules is available by the application, such as:

  • Average
  • Count of NULLs
  • Count of NOT NULLs
  • Unique values
  • SUM of values

Adding profiling rules to a column

There are 4 ways to add rules to a column:

1) When adding new profiling objects:

2) When adding profiled columns for an object:

3) Adding rules manually for a specific column by clicking “+”:

Removing profiling rules from a column

Profiling rules are removed/deleted when:

  • Deleting profiling objects
  • Deleting profiled column
  • Deleting specific rule by clicking “trash” icon:

Executing profiling rules for a column

To execute a single rule, open the column and click on “refresh” icon:

Note: If Scheduling is enabled, all object rules will be executed based on schedule

Exclude executions from rule history

There are two ways to exclude executions from rule history.

1) Select executions from history and click disable button:

2) Click on a execution from history chart:

View all existing rules for an object

All existing rules can be viewed by clicking Actions -> View all rules:

It is possible to delete specific rules or delete execution history for selected rules:

Manage profiling rules

Profiling rules can be added/removed/changed by clicking on “Manage rules” button from sidebar. Each rule can be assigned for specific database drivers/connectors and data types. A rule must have a unique name.
Parameters available to use:

  • <column> – column name
  • <object> – table/view name with schema (schema.table)

A rule can be assigned to all applicable columns by clicking the “Actions” button and then selecting “Assign” for the selected rule. The option “Clear” will remove rule from all columns that are no longer applicable by data type or driver/connector.

Rules with “default” value turned off are not automatically created when generating rules for an object/column. They can only be added manually.

Manage data types

Data types for rules can be added/removed/changed by clicking on “Manage data types” button from sidebar. Rule types categorize which rule can be created/assigned for a column based on its data type. Data types with parentheses must be inserted without the parentheses (e.g. VARCHAR(150) as VARCHAR ).

Test cases

Overview

From Test cases page you can:

  • Add a new test case – manually add new test case row
  • Connection filter – filter by databases
  • Test suite filter – filter by test suites
  • Status filter – filter by status
  • Description/SQL – search by test SQL or description values
  • Tags – search by test suite tags
  • Labels – search by labels
  • Execute – execute selected tests
  • Delete – delete selected tests
  • More options
    • Import tests – import tests from exported JSON file
    • Export selected tests – export selected tests as a ZIP or JSON file
    • Duplicate tests – duplicate selected tests
    • Disable/enable tests – disable or enable selected tests
    • Assign new test suite  – assign new test suite to selected tests
    • Assign new connection  – assign connection to selected tests
    • Set threshold – set prediction threshold and type to selected tests
  • Save changes – save changed tests
  • Test generation – generate test cases (see chapter Object based test generation)
  • Variables – view global variables used in test case SQLs
  • Rules – view dynamic test case template rules used for test generation
  • Page settings
    • Execution timeout – maximum number of seconds query is allowed to run before timeout. This will be overwritten if connection allowed query timeout is smaller
    • Rows displayed – maximum number of rows displayed on the page. Mostly for loading performance
    • Show change column – difference between previous and latest result is shown in a separate column with green/red indicators and percentage of change
    • Show row status colors – status is used as a row background for a test case

Adding new test

  1. Click “New test case” button on the top navigation bar
  2. Modify test properties
  • Test type – query or compare. For a comparison test see chapter Compare type test case
  • Description – test description
    • Extra info icon – additional info about the test case displayed in test report if test fails
  • Expected – expected result – either numeric or string value
  • Test suite – test suite where test will be added
  • Save – save this test
  • Execute – execute this test
  • Generate SQL – generate SQL automatically (see chapter Generating SQL for a single test case)
  • SQL – test execution SQL (if SQL uses multiple resultsets, e.g. creating temp table, then */allowMultipleRows*/ comment must be added in front of the SQL)
  • Target – test target database connection

Modifying a test case

Test case properties can be modified in test case detailed view (by clicking on a specific test case in the test case list).

Comments

Test case comments can be viewed by first opening a test case and then clicking the messages icon on the bottom bar:

A comment can be edited or deleted by its owner (or an admin user)

Relations

Test case relations are displayed on the bottom bar of opened test case view:

Relations to schema, object and columns are automatically set if a test case’s SQL is generated using dynamic rules. Relation can be changed or assigned manually by clicking on the relation.

Labels

Test case labels are displayed on the bottom bar of opened test case view:

Labels can be added and deleted by all users who have privileges to add/edit test cases. Labels are used to filter test cases (in application’s UI or using API)

Display test results

It is possible to see test results (for tests that use COUNT to get results) by clicking on “View query results” button in test history. The query will be executed (COUNT will be replaced with column names or *) and results are displayed. A maximum of 1000 rows are displayed. CSV with displayed results is possible to download by clicking CSV button in the opened window.

Multi-column result

It is possible to select multiple columns and save results in the application. The first column value is used to compare against the defined expected result. A column that is used to view query results should be defined with an alias of “dqm_query_result“.

For example, if an user would want to validate a percentage of rows is not correct (invalid rows out of all rows), but would still want to use “View query results” functionality to view these invalid rows:

Test result prediction with threshold

If a test expects a numeric value, then it is possible to set a percentage threshold to find deviations in data. If the threshold is exceeded then test status is set as “Failed”. Erroneous results can be removed from history by selecting them and clicking .

There are a total of 4 types of predictions to use:

  • Linear – uses linear regression
  • Polynomial – uses polynomial regression
  • Previous – compares to previous available result
  • Average – compares to average of all previous results

Compare type test case

It is possible to compare data from different data sources by comparing SQL results. You can compare numeric values (SUMs, COUNTs, AVGs etc), Strings and data sets row by row/column by column. Sample of missing data is displayed when comparing data sets. Please note that comparing large data sets (millions of rows) is limited by Java memory.

Click “View compare results” to view differences in data after execution. It is possible to see differences in each column by clicking on a row from either side. A maximum of 1000 rows is displayed from each side and CSV files are available to download to get all results.

Deleting test cases

  1. Select test cases (you can use shift+left click to select multiple)
  1. Click “Delete” button on the sidebar

Manage rules

Rules allow to set SQL generation rules, which can be used for Test generation (see chapter Test generation). Users can define their custom SQL generation rules based on their needs. Some default rules are defined on initial setup.

  • Editing a rule – edit existing rule for test generation (click on rule to edit)
  • Add a new rule – add a new dynamic rule for automatic test generation (click on “Add new rule” button)
  • Parameters – used in rule generation. Users can add new parameters or use existing default parameters (see chapter Parameters)

Adding/editing a rule

  1. Click on “Add new rule” button
  2. Fill in rule properties
  • Test name – description which is added to all tests generated by this rule. <parameter> will be replaced with custom parameters or metadata from the database. For example: “myschema.mytable – Full duplicates”
  • Rule description – explanation for the rule
  • SQL – SQL syntax with <parameters>.
  • Expected result – expected numeric or string value from the query (can be empty).
    • greater than – >0
    • lesser than – <1000000
    • equal – =10
    • previous – >previous
    • conjunctions (&&, ||):
      • AND – >0&&>previous
      • OR >0||=previous
  • Drivers/connectors – JDBC drivers which this rule applies to
  1. Press “Add new rule” button

Example rules

These are some dynamic rules that use database metadata as parameters. 

Check if object has any rows inserted

Test name: <schema>.<object> – Count of rows
Rule description: Check if object has any rows inserted
Expected result: >0
SQL: 

SELECT COUNT(*) FROM <schema>.<object> 

Check if object has any duplicate rows

Test name: <schema>.<object> – Duplicates
Rule description: Check if object has any duplicate rows
Expected result: =0
SQL: 

SELECT COUNT(*) FROM (SELECT <columns> FROM <schema>.<object> GROUP BY <columns> HAVING COUNT(*)>1) a

Check if object has any columns that are NULL in all rows

Test name: <schema>.<object> – Duplicates
Rule description: Check if object has any columns that are NULL in all rows
Expected result: empty string – leave empty
SQL: 

SELECT ” <forEachColumn> || CASE WHEN COUNT(<column_name>)=0 THEN ‘<column_name>;’ ELSE ” END  </forEachColumn> FROM <schema>.<object>

Note: this rule might not work for all database engines because of differences in syntax (confirmed to work for Postgres, Vertica, Redshift).

Deleting existing rules

  1. Find the rule you want to delete and click on it
  1. Click “Delete rule” button

Parameters

Parameters are part of rules that are replaced by database metadata or user input to dynamically generate test cases. Default parameters are replaced by database metadata:

  • <schema> – replaced with database schema name
  • <object> – replaced with table or view name
  • – replaced with all (or selected) column names separated by commas. Column names are wrapped based on database syntax:
    • postgres, oracle, sap iq – “column”
    • MS SQL server – [column]
    • MySQL, MariaDB – `column`
  • <pk_columns> – replaced by primary key columns (comma separated). If primary keys are not found then the test is not generated.
  • <forEachColumn> </forEachColumn> – content between these two parameters are repeated for all (or selected) columns. It is also possible to use parameters <column_name> and <column_data_type> in the content.

Add custom parameters for rules

  1. Insert new parameter name and click on “+”
  1. New parameter will be displayed in the list of parameters
  2. This parameter can now be used in rule creation and test generation
  3. Parameters can be deleted from the list by clicking on X

Test generation

Object based test generation

Object based test generation allows you to create multiple tests for multiple objects

  1. Click on “Test generation” on the sidebar
  2. Type will be “Object based”
  1. Filter out objects for test generation
  • Connection(s) – database connections filter
  • Schema – database schema filter
  • Object – table or view name (filters as a wildcard)
  • Column(s) – search for objects that have certain column(s). This input value also replaces the <column> parameter (see chapter Parameters)
  1. Select tests you want to generate for objects
  1. Generate selected tests
  • Test suite – test suite name, where tests are generated into
  • Allow duplicate test cases – a test is not generated if checkbox is not checked and same the test already exists

Dynamic test generation

Dynamic test generation allows you to create multiple tests for specific components

  1. Click on “Test generation” button on the sidebar
  2. Set type to “Dynamic”
  1. Modify generation properties
  • Connection – target connection for test cases
  • Parameters – custom parameters used in selected tests (for custom parameters see Add custom parameters for rules)
    • Inserting multiple parameters will generate multiple test cases
  • Test suite – where tests are generated into
  1. Click “Generate selected”

Generating SQL for a single test case

  1. Open your test case by clicking on it
  2. Press “Generate SQL”
  1. Modify test generation properties
  • Connection – database filter
  • Parameters – parameters that are replaced in selected SQL (see chapter Add custom parameters for rules)
  • SQL – Automatically generated SQL 
  • Test selection – which type of test is generated
  • SQL auto-update – Check if you want your test case to be automatically updated in case of metadata changes (only if all columns are used)
  • Generate SQL – generate SQL with selected options

Global variables

Global variables are used for predefined values in SQLs. This allows to change the SQL for multiple test cases, by just changing/using a single variable.

Settings

General settings

Note: This page is only accessible by users with ADMIN privileges.

From General settings page you can see:

  • Clear memory – manually clear used memory
  • Resource usage graph
    1. red – current used memory
    2. orange – memory occupied by Java
    3. green – total usable memory
    4. black – CPU usage
  • Tool restart button – to restart DQM if needed
  • System timezone – timezone used by the application
  • Server URL – URL that is displayed in HTML reports and alerts
  • Enable guest view – if this is enabled, then guests can view dashboard without logging in
  • Exclude tests with status ‘error’ when sending alerts – Test cases with database error results are excluded when calculating test suite results to send alerts
  • Metadata update (minutes) – interval of automatic metadata updates for added connections
  • Execution history (days) – data retention for test case and profiling execution history in days
  • Parallel scheduled execution – Maximum number of scheduled executions in parallel (recommended 2)
  • Compare rows limit – maximum amount of database rows to be queried in compare test cases. This limit can be set to manage memory performance.
  • HTML test suite report row limit in email content – Maximum number of detailed test rows included in email content (recommended 100)
  • Top N column values profiled – count of values that can be profiled for columns.

License settings

Note: This page is only accessible by users with ADMIN privileges.

  • Licensed to – company name that owns the license
  • Valid until – license expiry date
  • Maximum connections – amount of connections allowed with current license
  • Maximum users – amount of users that can be created with current license
  • License hash

Authentication settings

LDAP/Active Directory

Note: This page is only accessible by users with ADMIN privileges.

  • Create user on first log in – automatically create user with role “User” if not registered on first log in
  • Type – LDAP or Active Directory
  • Server – server url
  • Root DN – LDAP root DN. If group name has whitespaces then wrap it between quotes (e.g. OU=”group name”)
  • Search by – LDAP filter to search for user. For example uid or sAMAccountName
  • Domain – Active Directory domain

Note: certification file must be added when using ldaps (ldap over TLS/SSL).

OAuth2

Note: Client endpoint is not needed when using OpenID

Example Google OAuth

Example Okta OAuth

Certificates

Certificates section is needed in case your authentication/connection endpoints use TLS/SSL.

Adding a certificate

  1. Navigate to Settings -> Authentication
  2. Press “Add” button under certificates section
  3. Choose a certificate file
  4. Give certificate an alias for distinction
  5. Press “Add new certificate”
Adding certificate

Adding an https certificate (Enabling HTTPS for DQM)

  1. Navigate to Settings -> Authentication
  2. Press “Add” button under certificates section
  3. Choose a certificate file
  4. Give certificate an alias “https” (which is only used for https enabling. Certificate file has to be either .p12 keystore containing RSA private key or .pem file containing RSA private key and certificate chain)
  5. Press “Add new certificate”
  6. Tool will shutdown automatically
  7. Manually start DQM container/WAR file from server side

Replacing a certificate

  1. Navigate to Settings -> Authentication
  2. Press button under certificate row
  3. Choose new certificate file
  4. Press “Change certificate”
  5. If you replaced “https” certificate, you should reload settings from “Load settings” button on the same page

Connector settings

Note: This page is only accessible by users with ADMIN privileges.

List of available connectors

Adding JDBC drivers

  1. Navigate to Settings -> Connectors
  2. Press the “Add new connector” button
  3. Choose driver type and select the driver’s JDBC JAR file to be used
  1. Click “Add new connector” button
  2. Click “Load connectors” button 
  3. DQM will restart and initialize added drivers/connectors
  4. Check that added connector status is OK

If the status check fails then please check that the correct driver file is uploaded.

  1. New connections can now be added using the selected driver.

Users & Roles

Note: Only users with ADMIN privileges will be able to see all users. Regular user will only see own account

Adding a new role

Roles can be used to allow users to only access specific database connections, grant privileges to add/edit connections, rules, profiling objects or test suites. Default role for a new user is “User” which has access to all databases, can add/edit rules, test suites, profiling rules and objects.

Adding a new user

A new user must have a username and at least one role selected. For LDAP/AD/OAuth2 users the password field can be empty. If a password is set for LDAP/AD/Oauth2 users then such users can access the tool with both LDAP/AD/Oauth2 and set password.

Recovering admin user

If in some case admin user credentials are forgotten, admin user password can be reset from the application machine. DQM container/WAR file should be rerun with parameter “dqm.recovery.password”

docker: docker run -d -e … “JAVA_OPTIONS=-Ddqm.recovery.password=mynewtemppassword” {image}

WAR: java -Ddqm.recovery.password=mynewtemppassword … -jar LiTech_DQM.war  

NB! Please be sure to restart application manually without said parameter after admin account has been recovered.

Alerts settings

Note: This page is only accessible by users with ADMIN privileges.

From Alerts settings page you can see:

Email settings:

  • SMTP host – host of your email server
  • SMTP port – port of your email server
  • From email – email that will send out reports
  • Password lock icon – click if you wish to edit password
  • Password – password for email if authentication is needed
  • Subject – email subject title

Webhook settings:

  • Name – custom name for a Slack channel
  • URL – Webhook address

Adding custom webhooks

Custom webooks can be added by clicking “New webhook” button:

Database settings

Note: This page is only accessible by users with ADMIN privileges.

From Database settings page you can see:

  • Reset database – format DQM local database
  • Create a backup – create a backup of the current state of the DQM’s local database
  • Export database – download selected database file
  • Import database – upload database file to list
  • Restore database – restore database from selected backup
  • Delete backup – delete selected backup

Export

Export tab is used to export entities from the application to JSON format for version control. Entities can be imported to the application using API

Test cases

Exporting test cases can be done on test cases page

Other entities

From exporting view you can select export entity and objects you need to export

User can also choose between ZIP and JSON export types, where:

  • ZIP – exports objects as separate JSON files packaged into ZIP archive
  • JSON – exports objects as JSON array, which will be a single JSON file