Process the data

Data Profiling: An Essential Step in Data Analytics

Before diving into data analytics within a Data Catalog, it's critical to ensure your data has been properly profiled. Data profiling is not just a step but a prerequisite. Without a valid data profile, proceeding to data identification tasks is premature.

What is Data Profiling?

Data profiling involves a detailed examination of your dataset, focusing on specific data objects. During this process, you collect valuable statistics and summaries that provide insights into the nature of your data. The beauty of data profiling is its immediacy. As soon as data from a column, table, or schema is processed, the results are ready for review.

When a data source schema is initially ingested, you're limited to the database metadata. Data profiling enriches this by uncovering additional layers of information, making it an indispensable tool in the data analyst's toolkit.

1. Click the Data Profiling tile.

1. Enable the option: 'Extract samples'

1. Click: 'Start'.

You can view the status of 'Profiling' on the Manage Workers page.

Explore the Data

2.1 Resource

2.2 Schema

2.3 Table

2.4 Column

The 'top level' is the connection resource: postgresql:synthea

1. Highlight the resource: postgresql:synthea

The Summary tab displays the connection properties

1. Select Details option.

The Details tab provides a overview of the Schema name, the number of Tables & Columns.

1. Next -> 2.2 Schema

Contains detailed information about child resources. You can view the items available in the selected resource, along with some additional information. The information varies based on the resource selected.

For example, if you select a data source, you can view available items like schema for structured data and folders for file systems. In addition, you can also get the number of tables and columns, including associated tags. For schemas, you can get additional information like row count and the last profiled date and time.

The bussines_apps_db database can contain a number of Schemas. In PostgreSQL, a schema is a named collection of tables, views, functions, constraints, indexes, sequences etc.

1. Highlight the synthea schema -> Details tab.

Note: The 'Synthea' datasource has alreaddy been profiled.

1. Next -> 2.3 Table

A 'table' is a collection of documents. A document contains fields which are analogous to columns in a relational database.

1. Highlight the synthea patients -> Details tab.

Note: The 'Synthea' datasource has also been profiled. The Data Identification policy has resulted in some columns being tagged as being Personal & Sensitive data.

1. Next -> 2.4 Column

A document is going to be comprised of a number of fields, which can be analogous to a database column.

1. Expand the 'patients' table and select: 'passport'.

2. Once the profiling has completed, you can review the collected metadata.

For instance, selecting the 'passport' field will present various statistical insights, such as Null Count, Cardinality, and Min/Max Width, aiding in understanding the data's characteristics.

The following table identifies the key details available in the Statistics pane when you select a column in a table to view:

Feature

Description

Null Count

Number of entries that are null.

Cardinality

The number of unique values in a field, where a low cardinality number indicates many repeated values.

HLL

An estimate of cardinality of the data, with a roughly ~2% margin of error.

Blank Count

The number of entries that are blank.

Min Width

The minimum number of character count in a value in the column.

Max Width

The maximum number of character count in a value in the column.

Avg Width

The average number of character count in a value in the column.

1. Scroll down to Data Patterns:

1. Next -> 3. Data Identification

---

Viewing Data Patterns

Data Patterns provide insights into the frequency and distribution of data when examining a specific column, including any random values present. Names and values within this view are limited to 200 characters for readability.

Access to this feature requires the appropriate permissions, enabled through the Sample Data Access setting in your user role. Users with administrative rights have the capability to adjust these permissions as needed.

This functionality is essential for understanding sample-profiled resources and accessing various resource-level details efficiently.

This feature is explored in more detail in: Data Patterns

Data Identification - Data Quality

Pentaho Data Catalog uses data identification methods called dictionaries and data patterns to help you identify data.

Dictionaries

Dictionaries are lists of words that may occur in your data that can be used for column data matching. Typically, dictionary words are those that you could not write a pattern to be able to match, such as country codes.There are two kinds of dictionaries, system defined and user defined. System defined, or built-in dictionaries come with Data Catalog and user-defined dictionaries are created by Data Catalog users.You can add a user defined dictionary in three ways:

• by importing a CSV file zipped with a JSON file containing the definition of the directory.

• by uploading a CSV file and creating the JSON file to define the dictionary by entering information in the UI.

• by selecting a profiled column of data from any JDBC table or file.

Data patterns

Data patterns are also used to identify data, by discovering patterns in the data that match a regular expression.

1. Click the Data Identification tile.

1. Click 'Select Methods'.

1. Select the following Data Dictionaries & Data Patterns:

1. Click Start.

2. Track the Job in the Workers.

1. In Data Canvas, check that the sensitive data in the Synthea -> 'patients' table has now been identified - tagged as PII & Sensitive.