Skip to content

Building an Exploration Query

Accessing the Exploration Feature#

To begin exploring and validating your data, you can access the 'Explore' tab in the Data Grid from the Workbench. This tab offers various options for data exploration and validation, which can be combined as needed. These options include:

  • Filtering: you can add filters to narrow down your dataset or search for specific values within it
  • Aggregating: allows you to summarize your dataset, providing aggregated information such counts
  • Sorting: you can sort your dataset based on specific columns, arranging the data in ascending or descending order

To perform an exploration from here, click on "Explore". The Query Builder opens.

Converting the Exploration to a Transformation#

Once you have completed the intermediate exploration, you have several options available:

  • directly drag and drop the required 'Exploration' tab into the Flow Area to create an associated transformation node that is connected to the source data node from where you initiated your exploration
  • for each 'Exploration' tab you can:
    • Edit: the exploration that gets automatically executed after the modification
    • Convert to Transformation: to incorporate it into the pipeline flow while containing the exact logic that was defined during the validation - beside the runtime aspect, the converted node inherits other characteristics of a regular transformation node, such as showing a data preview based on sample records
    • Refresh: the exploration query is rerun, taking into account any modifications or updates that have occurred in the pipeline or the source data to ensure you have the most up-to-date and accurate results
    • Delete: deletes the exploration without impacting the pipeline

Validation Query Options#

Filtering#

You can filter your created view in search of specific values in order to validate your transformations afterwards, e.g. you applied the 'Manage Columns' operation to your source and created the view and now want to check whether a certain value is included. You can apply one or multiple filters on a node.

To filter:

  1. Click on the "Explore" pill. The dialog 'Explore Results' opens.

  1. Click on "Filter". The 'Filter' section expands.

  2. Select the column, enter the operator and the filter value.

  3. If you don't want to apply another filter, confirm with "Create". Otherwise select the next column, operator, value ... The filter applies. The results are shown in the respective 'Transform' tab in the Data Grid.

Aggregating#

You can summarize your results by dimensions and measures to validate your data. Summarizing can be done for zero or more columns:

  • you don't have to summarize by any dimension but can still aggregate which is great for grant total summarization queries
  • you don't have to aggregate by anything but still can group by any number of dimension which is great for seeing distinct dimension values

To aggregate:

  1. Click on the "Explore" pill. The dialog 'Explore Results' opens.

  1. Click on "Aggregate". The 'Aggregate' section expands.

  2. Select the dimension and measures from the drop-down and the function.

  3. If you don't want to apply another aggregation, confirm with "Create". Otherwise select the next dimension, measure ... The aggregation applies. The results are shown in the respective 'Transform' tab in the Data Grid.

Sorting#

You can sort your data according to your needs, e.g. sort a specific column with regard to ascending values.

  1. Click on the "Explore" pill. The dialog 'Explore Results' opens.

  1. Click on "Sort". The 'Sort' section expands.

  2. Select the column, direction and if needed the limit and confirm with "Create". The sort applies. The results are shown in the respective 'Transform' tab in the Data Grid.

Renaming an Exploration#

To keep your explorations clear and precise, you can rename any exploration.

  1. Right-click on the required 'Exploration' tab's menu and select "Rename". The 'Rename' dialog opens.

  2. Enter the new name and confirm with "OK". The renamed 'Exploration' tab shows the preview.

Quick Filter#

Aggregating within the exploration interface can reduce your data to help with discovery use-cases. Aggregation alone may not be sufficient when looking for a specific detail in the results.

INFO: Quick filters can only be applied to aggregation explorations.

STRING dimension quick filter#

After creating an exploration by aggregating a STRING dimension column, the quick filter allows you to choose a subset of values. The STRING column values are prepopulated in the filter dialog and can be selected or deselected on click:

  1. Select your exploration query and click to open the dialog. The quick filter dialog opens.

  2. Select or deselect the required values and confirm with 'Apply'. The quick filter dialog closes and the preview is updated.

Numeric dimension quick filter#

After creating an exploration by aggregating a numeric dimension column, the quick filter allows you to choose a range of values:

  1. Select your exploration query and click to open the dialog. The quick filter dialog opens.

  2. Enter the values for the required range and confirm with 'Apply'. The quick filter dialog closes and the preview is updated.

DATE dimension quick filter#

After creating an exploration by aggregating a DATE dimension column, the quick filter allows you to choose a date range:

  1. Select your exploration query and click to open the dialog. The quick filter dialog opens.

  2. Enter the date range manually or select via the date picker and confirm with 'Apply'. The quick filter dialog closes and the preview is updated.