This page describes three data quality tools, and the configuration file that they share:
data_quality_tools_release_5_2.tar.xz
sha-256 hash: f529974dc2db0010ab6673d60ac02a8bd370fe2851b9528180e0d6d417876677
or
data_quality_tools_release_5_2.zip
sha-256 hash: 74f02c2da0c11ed51d431e3e03ec9d3cd21ac546664844f4987937c2cffd0d91
On Linux, run this command:
or
On Macos the command is:
The hash should match the number shown above for your file. If not, do not use the downloaded file, and please Contact Us at The Data Studio.
The extract will produce a directory structure like this:
The following sections explain what to do with these files.
These tools all use a configuration file to get them started. This identifies the connection details for a database. For TableDefinition this is the database you want information about. For the Data Profiler and the Data Quality Monitor this file defines the Data Quality database (which is described below). Here is an example configuration file that shows typical connections for some leading databases. Host addresses and passwords are fakes (of course) and you would not normally use literal passwords anyway. Here we have one database block (the postgres_local one) and all the others are commented out. In your own configuration file you can do the same, or you can delete those blocks that are irrelevant in your environment. Only one block may be uncommented.
# lines beginning with # are comments
# headings are shown in square brackets. These are comments too.
[postgres_local]
host = localhost
port = 5433
connection_string = jdbc:postgresql://
user_specified_as = literal # literal, environment-variable or integrated-security
user = postgres
password_specified_as = literal # literal, environment-variable or integrated-security
password = postgres
integrated_security = false
database = dev
schema = public
table = unload_test
output_directory = /home/ron/Desktop/ # used only for TableDefinition
[mariadb]
# host = localhost
# port = 3306
# connection_string = jdbc:mariadb://
# user_specified_as = literal # literal, environment-variable or integrated-security
# user = ron
# password_specified_as = literal # literal, environment-variable or integrated-security
# password = ron
# integrated_security = false
# database = data_quality
# schema =
# table =
# output_directory = /home/ron/Desktop/ # used only for TableDefinition
[microsoft_sql_server]
# host = localhost
# port = 1433
# connection_string = jdbc:sqlserver://
# user_specified_as = integrated-security # literal, environment-variable or integrated-security
# user =
# password_specified_as = integrated-security # literal, environment-variable or integrated-security
# password =
# integrated_security = true
# database = data_quality
# schema = dbo
# table =
# output_directory = /home/ron/Desktop/ # used only for TableDefinition
[oracle]
# host = localhost
# port = 1521
# connection_string = jdbc:oracle:thin:@//
# user_specified_as = environment-variable # literal, environment-variable or integrated-security
# user =
# password_specified_as = environment-variable # literal, environment-variable or integrated-security
# password =
# integrated_security = false
# database = XE
# schema = RON
# output_directory = /home/ron/Desktop/ # used only for TableDefinition
I often run
If you run
$ java -jar TableDefinition.jar
Usage: TableDefinition configuration-file [schema-override [table-override]]
configuration: path of configuration file, always needed
schema: name of schema containing table definitions to be extracted, needed if table specified
table: name of table for which definition is to be extracted
The schema and table parameters override the corresponding entries in the configuration file, if they are provided as parameters.
If schema is not specified in the configuration-file, and not overridden,
If schema is specified and table is not specified in configuration-file, and not overridden,
If schema and table are specified,
All database access is done using JDBC. There are JDBC drivers for every SQL database (as far as I know) and some that have only a passing resemblance to SQL. A quick search should find the one you need and most (if not all) can be downloaded free-of-charge.
The Data Profiler and the Data Quality Monitor share a database which looks like this:
There is a script to create these tables in the downloaded installation file, so you don't need to type it. Just navigate to the directory:
[where you downloaded the zip file]/data_quality_tools/release_5_2/set_up_data_quality_database/
Then run the PostgreSQL command line, and the commands shown in bold:
$ psql -U [your user name] postgres
psql (17.4 (Ubuntu 17.4-1.pgdg24.04+2))
Type "help" for help.
postgres=# create database data_quality;
CREATE DATABASE
postgres=# \c data_quality
You are now connected to database "data_quality" as user "[your user name]".
data_quality=#
\i create_tables.sql
configuration
CREATE TABLE
connection
CREATE TABLE
query
CREATE TABLE
factor
CREATE TABLE
factor_group
CREATE TABLE
group_factor_map
CREATE TABLE
measure
CREATE TABLE
table_profile
CREATE TABLE
column_profile
CREATE TABLE
pattern
CREATE TABLE
frequency
CREATE TABLE
You will use a SQL script for every factor you measure. Usually these scripts are small. You may end up measuring hundreds of factors with a separate script for each. We strongly recommend that you keep them all in a version control system. You can use Git of course, but we find Subversion to be a better fit for small teams.
This database contains configuration information, database connections and the results of the quality and profile processes. These tables are used to produce web pages that show the results of the profiling and monitoring.
The scripts are written for our preferred database, which is PostgreSQL but we have used Oracle, Microsoft SQL Server and other SQL databases for this Data Quality database. Some small adjustments are needed for non-PostgreSQL databases.
Whatever database you use for the Data Quality database, you can use these tools to profile and monitor
You may choose to run these tools directly on live systems, but it is more common to run them on the staging area in a data warehouse. In either case these tools must log in to the database that is being measured. The username that is used for these automated accesses to databases, should have select-only access to the database tables being measured, and select + insert + update access to the data quality database.
The configuration file enables the tools to connect to the Data Quality database. The tools then get all further configuration details from the Data Quality database. So you will need to populate some of the tables in the Data Quality Database, as described here. There are examples, that you will need to edit for your situation, here and in the
The character values in the configuration table should be composed of lowercase letters, underscores and numeric digits only, with the following exceptions. File locations may also contain one or more dots (full-stops) and forward slashes for Linux and Mac servers or backward slashes for Windows servers. Avoid embedded spaces in all cases. End users will not see any of these names.
| Column Name | Example Value | Purpose |
|---|---|---|
| data_warehouse | You must have at least one row in this table; the This name is useful when profiling or monitoring multiple systems, when you will want multiple rows in this table. |
|
| /home/data_quality/log/ | Where log files are to be stored | |
| /home/data_quality/sql/ | Directory/folder containing SQL script files that implement the quality measurements. | |
| /home/data_quality/html/ | Where the profile or monitor html files are to be stored. There should be separate configuration records for each of these tools. | |
| group_ | The example says that group files are named group_999.htm, where 999 is a number from 1 to 999. | |
| data_table_ | The example says that data table files are named data_table_999.htm, where 999 is a number from 1 to 999. | |
| data_chart_ | The example says that chart files are named data_chart_999.svg, where 999 is a number from 1 to 999. | |
| /home/data_quality/png | This tells the presentation program where to find the images (red, amber, green and grey quality ratings, and navigation buttons). | |
| https://apps.net/trac/newticket/ | Other issue tracking products are available. You are not obliged by these programs to record issues in an issue-tracking system, but it is a good idea to do so. | |
| /home/data_quality/stylesheets | location of the stylesheet which is used to format the output web pages. | |
| The now() function gives the current date and time in PostgreSQL. Other databases use functions with different names. | ||
| null when first created. |
Here is a sample script that you can edit and use to create your own configurations. Mostly you will want to change file locations and web addresses from the examples shown here.
insert into configuration
(
configuration_name,
log_file_location,
query_file_location,
html_file_location,
group_prefix,
data_table_prefix,
chart_prefix,
link_to_images,
link_to_issue_tracking_new,
link_to_stylesheets,
created_at
)
values
(
'local', -- configuration_name
'/home/the_data_studio/data_quality/log/', -- log_file_location
'/home/the_data_studio/data_quality/sql/', -- query_file_location
'/home/the_data_studio/data_quality_monitor/htm/', -- html_file_location
'group_', -- group_prefix
'data_table_', -- data_table_prefix
'data_chart_', -- chart_prefix
'/home/the_data_studio/data_quality/png/', -- link_to_images
'https://thedatastudio-server:8000/trac/newticket/', -- link_to_issue_tracking_new
'/home/the_data_studio/data_quality/stylesheets/', -- link_to_stylesheets
now() -- created_at
);
To produce the web pages for the Profiler and the Monitor, you will need to provide values in the configuration table. You must also:
Remember to copy the images (.png image files) and stylesheet (style.css) to the locations you have defined here.
The Data Quality Monitor and the Data Profiler must have separate configurations. The
Note that the locations and links (should all be absolute file paths and should end with a forward slash ("/") for Linux and MacOS servers, and with a backslash ("\") for Windows servers.
The Data Profiler reports on the structure of tables in a database, and collects statistics about the content of those tables. It looks at each column to see how unique, or otherwise, those values are, what are the maximum and minimum values, how many values are missing (
These reports can be used to help understand the meaning of the data, and also how reliable it might be. We frequently find data quality issues this way, so the Profiler can help us to improve our data, simplifying processing and building confidence in it. If the data always matches the expected values then we spend much less time handling exceptions, and we get more useful and reliable results in our reports and analyses.
When copying data into a Data Warehouse, I always profile the source data at the earliest opportunity, usually when we first get it into the Staging Area. This helps us to understand what we are dealing with and to begin to build a Data Dictionary, that we will refine as we work with the data.
This following sections describe how to configure and use the Data Profiler.
The Data Profiler uses the
There are two programs:
Both programs use a configuration file and the data quality database for database connection details and for file locations.
The
DataProfileAnalyse configuration_file configuration_name connection [database [schema [table]]]
configuration_file: path of configuration file
configuration_name: name of configuration in database configurationtable
connection: name of connection in connections table
database: name of database to be profiled, needed if schema specified
schema: name of schema to be profiled, needed if table specified
table: name of table to be profiled
With only the first three parameters,
Database |Encoding book |UTF8 data_quality |UTF8 postgres |UTF8 public_data_sets|UTF8 template1 |UTF8
If we add a database name,
Database |Encoding|Schema public_data_sets|UTF8 |information_schema public_data_sets|UTF8 |pg_catalog public_data_sets|UTF8 |public
If we add the schema as well,
Database |Encoding|Schema|Table |Type |Remarks |Row Count public_data_sets|UTF8 |public|banana_price |TABLE|from https://www.gov.uk/government/statistical-data-sets|13380 public_data_sets|UTF8 |public|ordnance_survey|TABLE|from https://www.ordnancesurvey.co.uk |7684 public_data_sets|UTF8 |public|postcode |TABLE|Office for National Statistics Postcode Directory |2709658 public_data_sets|UTF8 |public|staff_category |TABLE|from https://www.hesa.ac.uk/ |1560
The row counts are useful, especially when we are looking at a database in the early stages of our data warehouse project. The remarks can be a useful start to our Data Dictionary (see .
We usually want to profile many tables in the same database. In such cases we can use a script like this to avoid a lot of typing:
#!/bin/bash
. $HOME/.profile
NOW=`date +%Y%m%d%H%M%S`
PROGRAM_DIRECTORY="/home/the_data_studio/profile/jar/"
CONFIGURATION_FILE="/home/the_data_studio/profile/conf/data_quality.conf"
CONFIGURATION_NAME="profile"
CONNECTION="public_data_sets"
DATABASE="public_data_sets"
SCHEMA="public"
#
TABLE=${1}
echo "Starting DataProfileAnalyse"
/usr/bin/java -jar ${PROGRAM_DIRECTORY}DataProfileAnalyse.jar ${CONFIGURATION_FILE} ${CONFIGURATION_NAME} ${CONNECTION} ${DATABASE} ${SCHEMA} ${TABLE}
echo "Starting DataProfileWebsite"
/usr/bin/java -jar ${PROGRAM_DIRECTORY}DataProfileWebsite.jar ${CONFIGURATION_FILE} ${CONFIGURATION_NAME}
#
exit 0
Then we can simply type:
./run_profile.bash banana_prices
to get a profile of the
The Data Quality Monitor runs a series of SQL scripts, usually configured to run every night, to check all sorts of things that you consider to be important in your database. The Data Quality Monitor page explains what the monitor can do, what options you have for different types of monitoring and what outputs you can expect. This page describes how to make it happen.
For the Data Quality Monitor, we need to do a bit more configuration. Currently this is also done with SQL scripts. [An application is being built to help with this, but this is not ready yet. We have run the monitor effectively in several large organisations, using only the scripts described here.]
We need to add data into the following tables:
Here are some sample scripts:
insert into connection
(
connection_name,
connection_string,
user_name_specified_as, /* literal, environment-variable or integrated-security */
user_name,
password_specified_as, /* literal, environment-variable or integrated-security */
password,
created_at,
updated_at
)
values
(
'accounts', -- connection_name
'jdbc:postgresql://localhost:5432/accounts', -- connection_string
'literal', -- user_name_specified_as
'ron', -- user_name
'environment_variable', -- password_specified_as
'DATA_QUALITY_MONITOR', -- password
now(), -- created_at
null -- updated_at
);
insert into query
(
connection_id,
query_file,
run_frequency,
last_run_date,
last_elapsed_time,
created_at
)
values
(
1, -- connection_id (foreign key to connection table)
'factor_123.sql', -- query_file (in form: factor_999.sql where 999 is the factor id)
'Daily', -- run_frequency (Usually 'Daily', otherwise 'Weekly')
current_date - interval '1 day', -- last_run_date (this value will get it started)
0, -- last_elapsed_time (will get filled in every time this query runs)
now() -- created_at
);
The query file may be any file name valid for your operating system. (Lowercase letters, numeric digits, underscores, one dot (full-stop) and no spaces will work for any operating system you are likely to be using, so this is a good convention.) We usually name files factor_999.sql, where 999 is the factor_id, but this is just for our human convenience in finding the sql script file for a particular factor. There is no database link between the number in the file name and any
insert into factor_group
(
title,
group_sequence,
email_of_group_owner,
created_at
)
values
(
'title of group', -- title of group to appear on front page of monitor
1, -- group_sequence: controls the order in which groups appear on the front page
'ron@thedatastudio.net', -- email of the administrator for this group
now() -- created_at
);
insert into factor
(
title,
description,
trend_or_zero_sum,
months_on_graph,
green_limit,
amber_limit,
source_measured,
created_at
)
values
(
'Sum of daily transactions', -- title
'Total transaction value for each day', -- description
'Zero-sum', -- factor_type (Trend or Zero-sum)
6, -- months_on_graph (normally 12)
0, -- green_limit (normally 0)
0, -- amber_limit (normally 0)
'[table name]', -- source_measured (main table being measured)
now() -- created_at
);
insert into group_factor_map(factor_group_id, factor_id, factor_sequence)
values
(
1, -- group_id (reference to table: factor_group.id)
1, -- factor_id (reference to table: factor.id)
3 -- factor_sequence (sequence of factor in group on report)
);
Now we can start writing tests. Most are very simple, but some can challenge our creativity in producing results that measure more subtle data quality factors.
Every factor is implemented by a query that returns any number of rows with the following columns:
| column_name | value |
|---|---|
| The id of a factor described in the | |
| The date for which the | |
| the integer value calculated for this |
There must be, at most, one record for every
The results of the factor query are stored in the measures table. If there is already a row for this
How the measures are presented depends on the
Here are some examples of the kinds of things you can measure.
This query collects the total transaction value for every day for the last year. You will probably see weekly or monthly cycles of transaction values. If there is something outside the normal pattern, and therefore probably highlighted in pink on the report, you would want to see if there is an explanation, which might be market conditions, or an error in processing. An investigation might save embarrassment or financial loss.
/* Factor 001: */
/* Calculate total transaction value for every day in the last year */
select
1 as factor_id,
date_trunc('day', transaction_date) as run_date,
sum(amount) as value
from
staging.financial_transaction
where
transaction_date > (now() - interval '1 year')
group by
2;
/* Factor 009: */
/* Check to see if we sent the same magazine more than once to the same customer. */
/* Restrict to company magazine "source codes" (appnum like 'FSM%') only */
select
9 as factor_id,
coalesce(y.date_created, x.calendar_date) as run_date,
sum(coalesce(y.distinct_source_codes - 1, 0)) as value
from
(
/* This inner query makes sure that we get a value for every day in the last year */
/* The calendar table is one we created ,containing every day we are interested in. */
select
calendar_date as calendar_date
from
warehouse.calendar
where
calendar_date between (current_date - interval '1 year') and current_date
) x
left outer join
(
select
connum as supporter_number,
date_trunc('day', datecr) as date_created,
count(distinct source_code) as distinct_source_codes
from
staging.corresp_hist
where
appnum like 'FSM%' and
datecr > current_date - interval '1 year'
group by
connum,
date_trunc('day', datecr)
having count(distinct source_code) > 1
) y
on x.calendar_date = y.date_created
group by
coalesce(y.date_created, x.calendar_date)
order by
coalesce(y.date_created, x.calendar_date);
Having done all the set-up, running the tools is easy. The following script runs the DataQualityMonitor and DataQualityWebsite programs in a Linux environment. It is also possible to run these on a Windows server.
#!/bin/bash java -jar DataQualityMonitor.jar data_quality.conf local java -jar DataQualityWebsite.jar data_quality.conf local
The
The
When we look at the results, the exceptional values will be highlighted for us. When we get a red or amber result, we need to decide what to do about it.
Sometimes the problem is obvious; more often it needs some analysis. If it is highlighting a real exception in our systems, then we need to analyse the problem and solve it. This may involve correcting bad data, or fixing an application. In either case we should keep the test. "Regression" errors are surprisingly common - errors we think we have fixed often show up again. It maybe because of some slightly different combination of events that expose a weakness in our system. Whatever the reason, we should keep tests even (especially!) when we have resolved the problem.
Sometimes a particularly extreme measure has a bad effect on our reports. It may, for example, be a big deviation from the norm which causes the scale of the graph to be adjusted so that all the other points appear to lie on a flat line. If this happens we can set the
If you get an error the cause of which is not obvious, the first place to look is the log directory. You will have defined, in the database
On the initial set-up, the most common error I have made is to forget the trailing oblique slash ("/" for Linux and MacOS servers; "\" for Windows servers). If the system can't access the right directories, you don't get very far.
If you can't resolve an issue, contact us at The Data Studio. We will respond as soon as we can.
While you are in the log directory, you might want to delete the oldest log files.