Build an Exploration Query

locThe Query Builder feature enables you to understand the structure, content and lineage of your datasets. You can explore from any dataset that is displayed in the Flow Area, e.g. after preparing your data, you would like to explore the transformation results.

Data exploration can be viewed and performed from the nodes operation stack or the 'Explore' tab in the Data Grid and provides validation and exploration options like:

Filtering: for adding filters or finding values within a dataset
Aggregate: to summarize a dataset
Sorting: to sort a dataset

Note that by default all columns will be shown and the data preview is limited to a maximum of 1,000 rows.

To build an exploration query, click on the "+" icon of a node and then select "Query Builder" or click on the node and select "Explore" in the Data Grid. The query builder opens.

The query result will be shown as a Query node in the Flow Area. The query can be found in the 'Transform' tab in the Inspector.

When building a query from the Data Grid's 'Explore' tab, you can do the following:

drag and drop the required 'Exploration' tab directly into the Flow Area to create an exploration node that is connected to the source data
for each 'Exploration' tab you can: edit the exploration, convert it to a transformation, refresh it, and delete the exploration; all via the context menu

Exploring Via 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. To perform an exploration, just click on the required node in the Flow Area. You can apply one or multiple filters on a node.

To filter:

Click on the "+" icon of a node and then select "Query Builder". The dialog 'Build a Query' opens.
Click on "Filter". The 'Filter' section collapses.
Select the column, enter the operator and the filter value.
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.

Exploring Via 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:

Click on the "+" icon of a node and then select "Query Builder". The dialog 'Build a Query' opens.
Click on "Aggregate". The 'Aggregate' section collapses.
Select the dimension and measures from the drop-down and the function.
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.

Exploring Via Sorting#

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

Click on the "+" icon of a node and then select "Query Builder". The dialog 'Build a Query' opens.
Click on "Sort" and fill in the exploration crite. The 'Sort' section collapses.
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.

Explore Node Options for Scheduled Queries#

Only when scheduling a query via Email, the according query node is displayed in the Flow Area. You can do the following:

edit the query
show the query details
deploy the query data to Snowflake
delete the query