Data Identification
Dictionaries & Data Patterns ..
Data Identification
Pentaho Data Catalog uses data identification methods called Dictionaries and Data Patterns to help you identify data.
Data Dictionaries in Data Catalogs
A data dictionary in a data catalog is a specialized collection of predefined terms, values, and definitions used to automatically classify and tag data elements within an organization's datasets. Unlike traditional data dictionaries that simply define terminology, catalog data dictionaries function as intelligent matching tools that scan actual data content to identify and categorize information.
These dictionaries serve as automated reference systems that help data catalogs recognize specific types of data by comparing column values against predefined lists of known terms. For example, a country codes dictionary might contain "US," "CA," "GB" to automatically identify geography-related columns, while a product name dictionary could contain specific product identifiers to classify commercial data.
How Data Dictionaries Enable Data Discovery
Data dictionaries in catalogs primarily support column data matching - the process of automatically identifying what type of information a data column contains based on its actual values rather than just column names or metadata. This is particularly valuable for data elements that can't be identified through pattern matching alone, such as:
Country or state codes
Product names or SKUs
Department codes
Custom business terminology
Industry-specific classifications
Benefits for Data Management
By implementing data dictionaries, organizations can achieve:
Automated Data Classification: Systematic identification of data types without manual tagging
Improved Data Discovery: Users can find relevant datasets by searching for business terms rather than technical column names
Consistency Across Systems: Standardized identification of similar data elements across different databases and applications
Enhanced Data Governance: Better understanding of what data exists and where it's located
This automated approach transforms data catalogs from passive repositories into active discovery tools that understand the semantic meaning of organizational data.
Log into Data Catalog:
Username: [email protected]
Password: Welcome123!
Types of Data Dictionaries
Modern data catalogs typically support two categories of dictionaries:
System-Defined Dictionaries: Built-in collections that come pre-configured with the catalog, containing common data types like ISO country codes, currency symbols, or standard industry classifications.
User-Defined Dictionaries: Custom collections created by organizations to match their specific business context and terminology. These can be created through multiple approaches:
Importing structured files (CSV with JSON definitions)
Building dictionaries through the user interface
Extracting dictionary terms directly from existing profiled data columns
Pentaho Data Catalog ships with 95 In-built Dictionaries, pre-configured with common data types: e.g. ISO country codes, currency symbols, or standard industry classifications.
Let's take a look at: Marital_Status
Click: Data Operations > Data Identification Methods.
Click on Dictionaries.
Click on the Name to sort A - Z.
Scroll to Page ⅘
Click on the 3 dots for Marital_Status > View
When the data is profiled, in our example: 'Marital_Status' the value is compared, using a Rule, against (with a degree of confidence) the predefined dictionary.
Once matched: Tags - PII, Marital Status, Non-Sensitive are then applied.
Click on Rules
It provides insight into logic for the dictionary to apply tags mentioned in the JSON file, such as conditions and confidence scores. Based on these data factors, you can apply dictionaries to datasets.
For example, in the following JSON file for Marital_Status the dictionary rule specifies:
type is "Dictionary".
confidence score is calculated based on the weighted sum of "similarity=0.9" x "metadataScore=0.1" with conditions set to apply when the confidence score is greater than or equal to 0.7 and the column cardinality is greater than or equal to 3.
if these conditions are met, the action is to apply the tags: PII, Sensitive: Marital Status to the dataset.
This demonstrates how the provided logic guides the application of tags to datasets based on specified criteria.
Follow steps 1-6 outlined in : View - System Dictionary
Select: Edit
Dictionary UI
For the time being a quick overview of the Fields and Description as we'll be covering the UI in more detail when creating our own User-defined Dictionaries:
Category
Select a category or type a category name and click Add New to add it as a new category.
You can also remove an existing category.
Apply Values
Upload Dictionary
Click Upload Dictionary to upload a one-column CSV file containing dictionary file definitions and enter a number 0.00 to 1.0 for the confidence score.
Select Column
Click Select Column to set a profiled column of data to use for the dictionary.
Click Add Column to browse the navigation tree for a column to add for dictionary values.
Select the column you want to use. Note: The column you select must already be profiled.
Click Update.
Enter a number 0.00 to 1.0 for the confidence score.
Column Name Regex (1)
Regex
Add a regex as a metadata hint for the column name. Enter a number 0.00 to 1.0 for the confidence score.
Condition
AND / OR
In the Condition pane, you can click the Delete icon and remove the existing condition or click Add Condition and select an Attribute, Operator, and Value to set an additional condition.
Select either AND or OR to apply multiple conditions to evaluate and match the data.
Actions
Assign Tags
Click Assign Tags and enter a tag to assign to the data.
Assign Table Tags
Click Assign Table Tags and enter a table tag to assign to the data.
Assign Business Term
Click Assign Business Term to select a business term to assign to the data.
Browse the navigation tree for one or more business terms and select the associated checkboxes.
A number on the Add button shows the number of terms you have selected.
Click Add.
Data Patterns
A pattern analysis (or data patterns) defines the data pattern, regular expression, column alias(es) and tags that you can use to identify a column of data. You can use data patterns for a variety of purposes, such as regular expression (RegEx) generation, data identification, and data quality checking.
Data Patterns in Data Identification
Data patterns play a crucial role in identifying and categorizing data within a data catalog. These patterns are essentially recurring characteristics or behaviors in data sets that can be recognized and used to automate data management.
'Getting Started' -> 'Identify the data' explained how data patterns are used to profile the data
Pattern Analysis
Data Pattern Analysis reduces each data item into a simple pattern essentially using dimensional reduction for each character position in the input text. The result is a string which indicates where alphabetic characters, numeric characters, symbols, and whitespace appear.
Widget Parts #
KT1734-B
KT189A-D
KT2231-C
Part Code: KT-1734B
generates a data pattern of “AAnnnn-A” to indicate two letters, followed by a dash, followed by 4 digits and another letter.
Case sensitivity could optionally be tracked as well. Also, the set of “significant” symbols might be user-configurable (i.e., “As a data quality engineer, for this column, a dash and an underscore are significant”).
The base process iterates over every character in the data item and performs a simple character-for-character substitution, resulting in a “data pattern” string for the item.
The pattern consists of the following characters:
a
lower case alphabet character
A
upper case alphabetic character
n
digit 0 -> 9
w
whitespace character (space, tab)
s
symbol character (e.g., -/|!£$%^&*()+=[]{}@#~;:,.?¬¥§¢" )
-
Some other character (control, special symbol, etc.)
Others
Any other symbol may be treated as “significant” (such as a dash, underscore, or colon). These are output as-is in the generated data pattern for the entry.
Additional tests could be built into the algorithm to look for certain additional characteristics. For example, date formats can be very tricky. PDC could observe that ‘nn/nn/nnnn’ is a date and could then observe whether it is predominantly ‘mm/dd/yyyy’ or if its ‘dd/mm/yyyy.’
Another enhancement is detecting credit card numbers.
Pattern Character Poistion
Lets dive a bit deeper into: Part Codes
Part numbers often begin with two or three designated letters. This observation helps in defining a more precise RegEx rule based on observed patterns.
Additionally, tracking the "largest" and "smallest" values for each character position in these patterns reveals the degree of variability per position. Each time a pattern recurs, a counter tallies its occurrence; upon identifying a new pattern, the system stores the analyzed data as a distinct “sample” for that pattern.
The first step is to generate a substitution string (for purpose of the example, not all possible characters are shown):
abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789/|"!£$%^&*()+=[]{}@#~;:,.?¬¥§¢
aaaaaaaaaaaaaaaaaaaaaaaaaawAAAAAAAAAAAAAAAAAAAAAAAAAAnnnnnnnnnn/sssssssssssssssssssssssssssss
The top row is the character lookup row; and the bottom row is the substitution to be made for each character position.
For example, “KT127-3” would generate a simple pattern “AAnnn-n”. Additionally, the largest and smallest character seen for each character position is also tracked.
Consider a set of Part Codes and the associated pattern for each:
KT17341
AAnnnnn
KL91632
AAnnnnn
KW81234
AAnnnnn
KW91020
AAnnnnn
KA002021
AAnnnnnn
Additionally, we capture the largest and smallest character seen in each character position. This allows us to potentially determine if there are fixed characters in the pattern, and to generate stricter RegEx recommendations.
AAnnnnn – Occurs 4 times
KA11220 – Lowest character seen in each position
KW97644 – Highest character seen in each position
AAnnnnnn – Occurs 1 time
KA002021 – Lowest character seen in each position
KA002021 – Highest character seen in each position
The top ~20 data patterns will be captured and stored for subsequent consumption by data quality related and other processes as needed
Let's run through an example: USA_GBR_Passport_Numbers.
Navigate to the 'Management' tile & click on: patterns.
InSearch for: USA_GBR_Passport_Numbers.
When the data is profiled, in our example: 'passport' the regexMatch will produce a profilePattern that can be compared (with a degree of confidence) against the predefined profilePattern; once matched Tags - PII, GBR and USA Passport, Sensitive are then applied.
Fundamental to data quality analysis is either the use of a regular expression to check data, or to statistically analyze the data itself to find patterns and outlier patterns (which could indicate bad data).
View the data in the Patients table.
The data identification process generates roughly the top 20 most common patterns which capture the characteristics of the data. You can then use these patterns, along with their statistical frequency and supplementary information, to generate regular expression (RegEx) recommendations for your data. You can tune the RegEx to meet your specific needs, or select valid patterns, so that subsequent data quality checks will identify any data entries that are outside the accepted patterns.
For further information: take a look at Data Patterns in Data Identification Methods.
Next -> 2.2 Rules
Policies
The dictionaries and data patterns are together referred to as data identification policies. There are many policies included with Data Catalog, covering categories from a wide range of business sectors, such as Finance, Education, Aviation, Law Enforcement, PCI-DSS and Data Privacy.
After running data identification, you can use the Galaxy View feature to visualize the data tagging, identify the data flow, locate your data, and view the sensitivity and security.
Select the following 'Method Names' to define your policy:
The search bar at the top of the pop-up window can be used to search policies, this feature becomes more helpful as the list of policies grows.
Once the Data Identification step has completed, check the results in the Data Canvas.
Note the relevant 'patients' fields have now been tagged.
To illustrate, let's apply a data identification policy to the patients 'table'. This will apply identify and tag various columns in the table, this helps:
to quickly locate relevant information, saving time and effort when exploring vast data repositories.
link related data across different sections of the taxonomy.
derive maximum value from information assets by understanding their context and purpose.
Click the Data Identification tile.
Click 'Select Methods'.
Applying Data Dictionary + Pattern Analysis = Policy
Select the following Data Dictionaries & Data Patterns:
USA_SSN
Social Security Number
Country Codes
Country Names
Country Names
Country Names
DoB
Date of Birth
USA States
States in USA
Click Start.
Track the Job in the Workers.
In Data Canvas, check that the sensitive data in the Synthea -> 'patients' table has now been identified - tagged as PII & Sensitive.
x
x
Last updated
Was this helpful?