This page shows you the details about Time Slice filter, Row Filter, and Limit, and how to configure them. Select from one of the options below depending on whether you are using Pullup or Pushdown mode.
- Pullup
- Pushdown
Time Slice
A Time Slice filter is a dynamic filter that scans a range of dates or times and lets you perform incremental DQ runs on your time series data.
Time Slice uses the current timestamp as your run date to isolate the latest time slice data from your table and runs a DQ job on that portion for that day. For example, a run from the previous day checks for yesterday's date and groups it as a time slice, and, as long as a column that includes today's date exists in the table, a run from the current day will create a time slice for today's date and include it in the run. This grouping of time slices is necessary if you use Replay when you run your DQ Job because it replaces the run date with the run date from the time slice.
You can use it to perform incremental DQ runs on your time series data.
Click Add to create a Time Slice filter. The following table shows the available options for the Time Slice filter:
| Option | Description |
|---|---|
| Column of Reference | The column that the Time Slice filter analyzes and includes in the query. |
| Date Format | The default format is YYYY-MM-DD. When you select a date column as your Column of Reference, the format is the same as the column you select from your table, file, or view. |
| Group by |
Lets you change the grouping of the x-axis on the Data Distribution chart by Day, Week, or Year. Click Visualize for this option to appear. Note Group by is only available when the Column of Reference is a date column. |
| Data Distribution (chart) |
A chart of the distribution of available data for the Column of Reference. Click any of the bars in the chart to apply your selection to the Start Date and automatically update the Query Preview. Note Data Distribution is only available after you select a Column of Reference option. |
| Operator |
The symbol that specifies the action upon which the SQL query performs one or more expressions. Available operators include:
|
| Start Date |
A run date that allows the application to scan sections of your table, add a new date to it, and then run again. By selecting Start Date, a dynamic result is returned at runtime. Select a datetime from the date picker. |
| Query Preview | A preview of the automatically compiled SQL query. |
Row Filter
A Row Filter is a static filter that scans a section of rows. You can use this to isolate your DQ Jobs to filter based on certain groups of data. For example, you can use the Row Filter to filter data based on a particular region, such as North America.
Click Add to create a Row Filter. The following table shows the available options for the Row Filter:
| Option | Description |
|---|---|
| Column of Reference | The column that the Time Slice filter analyzes and includes in the query. |
| Date Format | The default format is YYYY-MM-DD. When you select a date column as your Column of Reference, the format is the same as the column you select from your table, file, or view. |
| Group by |
Lets you change the grouping of the x-axis on the Data Distribution chart by Day, Week, or Year. Click Visualize for this option to appear. |
| Data Distribution (chart) |
A chart of the distribution of available data for the Column of Reference. Click any of the bars in the chart to apply your selection to the Start Date and automatically update the Query Preview. Note Data Distribution is only available after you select a Column of Reference option. |
| Operator |
The symbol that specifies the action upon which the SQL query performs one or more expressions. Available operators include:
|
| Start Date |
A run date that allows the application to scan sections of your table, add a new date to it, and then run again. By selecting Start Date, a dynamic result is returned at runtime. Select a datetime from the date picker. |
| Query Preview | A preview of the automatically compiled SQL query. |
Note As of Collibra DQ 2023.04 version, you can only apply one Row Filter from the UI. However, the ability to apply multiple Row Filters from the UI is planned for a future release. To add an additional filter, you can switch to the manual SQL query mode and modify the query.
Limit
Scans a random sample of rows based on the maximum number of rows you set. Enter a value greater than 0 or click the arrows in the Max. rows field to apply a limit.
Escape Character
The Escape Character dropdown menu allows you to optionally select the special character with which to wrap column names. This can be especially helpful if your column name contains a special character that you need to escape in order for Collibra DQ to properly identify it. If you do not specify an escape character, then the column name is used as it appears in your table, file, or view. Available escape characters include:
- Double Quotes (
" ") - Single Quote (
' ') - Backtick (
` `) - Brackets (
[ ]) - Custom
- When you select Custom, enter your custom escape character into the input field to the right of the Escape Character dropdown menu.
SQL View
SQL View allows advanced SQL users to write and compile SQL queries manually. You can click the three dots menu in the upper right corner of the Filter Type window to switch to SQL View. Click the three dots again to switch back to Standard View.
Time Slice
A Time Slice filter is a dynamic filter that scans a range of dates or times and lets you perform incremental DQ runs on your time series data.
Time Slice uses the current timestamp as your run date to isolate the latest time slice data from your table and runs a DQ job on that portion for that day. For example, a run from the previous day checks for yesterday's date and groups it as a time slice, and, as long as a column that includes today's date exists in the table, a run from the current day will create a time slice for today's date and include it in the run. This grouping of time slices is necessary if you use Replay when you run your DQ Job because it replaces the run date with the run date from the time slice.
You can use it to perform incremental DQ runs on your time series data.
Click Add to create a Time Slice filter. The following table shows the available options for the Time Slice filter:
| Option | Description |
|---|---|
| Column of Reference | The column that the Time Slice filter analyzes and includes in the query. |
| Date Format | The default format is YYYY-MM-DD. When you select a date column as your Column of Reference, the format is the same as the column you select from your table, file, or view. |
| Group by |
Lets you change the grouping of the x-axis on the Data Distribution chart by Day, Week, or Year. Click Visualize for this option to appear. Note Group by is only available when the Column of Reference is a date column. |
| Data Distribution (chart) |
A chart of the distribution of available data for the Column of Reference. Click any of the bars in the chart to apply your selection to the Start Date and automatically update the Query Preview. Note Data Distribution is only available after you select a Column of Reference option. |
| Operator |
The symbol that specifies the action upon which the SQL query performs one or more expressions. Available operators include:
|
| Start Date |
A run date that allows the application to scan sections of your table, add a new date to it, and then run again. By selecting Start Date, a dynamic result is returned at runtime. Select a datetime from the date picker. |
| Query Preview | A preview of the automatically compiled SQL query. |
Row Filter
A Row Filter is a static filter that scans a section of rows. You can use this to isolate your DQ Jobs to filter based on certain groups of data. For example, you can use the Row Filter to filter data based on a particular region, such as North America.
Click Add to create a Row Filter. The following table shows the available options for the Row Filter:
| Option | Description |
|---|---|
| Column of Reference | The column that the Time Slice filter analyzes and includes in the query. |
| Date Format | The default format is YYYY-MM-DD. When you select a date column as your Column of Reference, the format is the same as the column you select from your table, file, or view. |
| Group by |
Lets you change the grouping of the x-axis on the Data Distribution chart by Day, Week, or Year. Click Visualize for this option to appear. |
| Data Distribution (chart) |
A chart of the distribution of available data for the Column of Reference. Click any of the bars in the chart to apply your selection to the Start Date and automatically update the Query Preview. Note Data Distribution is only available after you select a Column of Reference option. |
| Operator |
The symbol that specifies the action upon which the SQL query performs one or more expressions. Available operators include:
|
| Start Date |
A run date that allows the application to scan sections of your table, add a new date to it, and then run again. By selecting Start Date, a dynamic result is returned at runtime. Select a datetime from the date picker. |
| Query Preview | A preview of the automatically compiled SQL query. |
Note As of the April 2023 release version, you can only apply one Row Filter from the UI. However, the ability to apply multiple Row Filters from the UI is planned for a future release. To add an additional filter, you can switch to the manual SQL query mode and modify the query.
Limit
Scans a random sample of rows based on the maximum number of rows you set. Enter a value greater than 0 or click the arrows in the Max. rows field to apply a limit.
Escape Character
The Escape Character dropdown menu allows you to optionally select the special character with which to wrap column names. This can be especially helpful if your column name contains a special character that you need to escape in order for Collibra DQ to properly identify it. If you do not specify an escape character, then the column name is used as it appears in your table, file, or view. Available escape characters include:
- Double Quotes (
" ") - Single Quote (
' ') - Backtick (
` `) - Brackets (
[ ]) - Custom
- When you select Custom, enter your custom escape character into the input field to the right of the Escape Character dropdown menu.
SQL View
You can click the three dots menu in the upper right corner of the Filter Type window to switch to SQL View. SQL View allows advanced SQL users to write and compile SQL queries manually. Click the three dots again to switch back to Standard View.
Important When you include a binary date type column in your query for a Pushdown dataset, ensure that you use an alias to prevent job failure. For example, to_varchar("EXAMPLE_COLUMN") as EXAMPLE_ALIAS