Assert View
The Assert View component lets users verify that certain conditions are true of a view, or otherwise stop the query.
Whenever a view is accessible on a transformation pipeline, users can attach an Assert View component and assert against the metadata, values, and row count using comparison operators such as "Equal to", "Less than or equal to", "Greater than or equal to", and "Range".
Properties
Name = string
A human-readable name for the component.
Metadata = column editor
- Name: The name of the column.
- Type: Select the data type.
- Varchar: This type is suitable for numbers and letters. A varchar or Variable Character Field is a set of character data of indeterminate length.
- Number: This type is suitable for numeric types, without or with decimals.
- Float: This type of values are approximate numeric values with fractional components.
- Boolean: This type is suitable for data that is either "true" or "false".
- Date: This type is suitable for dates without times.
- Time: This type is suitable for time, independent of a specific date and timezone.
- Timestamp: This type is a timestamp left unformatted (exists as Unix/Epoch Time).
- Variant: Variant is a tagged universal type that can hold up to 16 MB of any data type supported by Snowflake.
- Size: For Text types, this is the maximum length. This is a limit on the number of bytes, not characters. For Numeric types, this is the total number of digits allowed, whether before or after the decimal point.
- Scale: Relevant only for numeric data, it is the maximum number of digits that may appear to the right of the decimal point.
You can specify table metadata using text mode.
Values = data type dependent
Input the values for each column. Separate values in the same column with a comma.
You can specify table values using text mode.
Ignore Metadata Order = boolean
When set to Yes, ignores the metadata order. Default is No.
Row Count Comparison Type = drop-down
Select how row count values are compared. Possible comparison operators are: "Equal to", "Greater than or equal to", "Less than or equal to", and "Range".
Selecting "Range" makes the Upper Value and Lower Value properties available.
Lower Value = integer
Set the lower value. This property is only available when Row Count Comparison Type is set to Range or Greater than or equal to.
Upper Value = integer
Set the upper value. This property is only available when Row Count Comparison Type is set to Range or Less than or equal to.
Row Count Value = integer
Specify the number of rows.
Name = string
A human-readable name for the component.
Metadata = column editor
- Name: The name of the column.
- Type: Select the data type.
- Integer: This type is a whole number (not a fractional number) that can be positive, negative, or zero.
- Number: This type is suitable for numeric types, with or without decimals.
- Float: This type of values are approximate numeric values with fractional components.
- Text: This type contains alphanumeric and character strings.
- Timestamp: This type represents values comprising values of fields year, month, day, hour, minute, and second, with the session local time-zone. The timestamp value represents an absolute point in time.
- Date: This type is suitable for dates without times.
- Boolean: This type is suitable for data that is either "true" or "false".
- Binary: This type is byte sequence values.
- Size: For Text types, this is the maximum length. This is a limit on the number of bytes, not characters. For Numeric types, this is the total number of digits allowed, whether before or after the decimal point.
- Scale: Relevant only for numeric data, it is the maximum number of digits that may appear to the right of the decimal point.
You can specify table metadata using text mode.
Values = data type dependent
Input the values for each column. Separate values in the same column with a comma.
You can specify table values using text mode.
Ignore Metadata Order = boolean
When set to Yes, ignores the metadata order. Default is No.
Row Count Comparison Type = drop-down
Select how row count values are compared. Possible comparison operators are: "Equal to", "Greater than or equal to", "Less than or equal to", and "Range".
Selecting "Range" makes the Upper Value and Lower Value properties available.
Lower Value = integer
Set the lower value. This property is only available when Row Count Comparison Type is set to Range or Greater than or equal to.
Upper Value = integer
Set the upper value. This property is only available when Row Count Comparison Type is set to Range or Less than or equal to.
Row Count Value = integer
Specify the number of rows.
Name = string
A human-readable name for the component.
Metadata = column editor
- Name: The name of the column.
- Type: Select the data type.
- TEXT: This type contains alphanumeric and character strings.
- INTEGER: This type is a whole number (not a fractional number) that can be positive, negative, or zero.
- NUMERIC: This type is suitable for numeric types, with or without decimals.
- REAL: Single precision floating-point number.
- DOUBLE PRECISION: Double precision floating-point number.
- BOOLEAN: This type is suitable for data that is either "true" or "false".
- DATE: This type is suitable for dates without times.
- DATETIME: Calendar date (year, month, day), and time of day.
- SUPER: A superset data type that encompasses all scalar types of Amazon Redshift including complex types such as ARRAY and STRUCTS.
- Size: For Text types, this is the maximum length. This is a limit on the number of bytes, not characters. For Numeric types, this is the total number of digits allowed, whether before or after the decimal point.
- Scale: Relevant only for numeric data, it is the maximum number of digits that may appear to the right of the decimal point.
You can specify table metadata using text mode.
Values = data type dependent
Input the values for each column. Separate values in the same column with a comma.
You can specify table values using text mode.
Ignore Metadata Order = boolean
When set to Yes, ignores the metadata order. Default is No.
Row Count Comparison Type = drop-down
Select how row count values are compared. Possible comparison operators are: "Equal to", "Greater than or equal to", "Less than or equal to", and "Range".
Selecting "Range" makes the Upper Value and Lower Value properties available.
Lower Value = integer
Set the lower value. This property is only available when Row Count Comparison Type is set to Range or Greater than or equal to.
Upper Value = integer
Set the upper value. This property is only available when Row Count Comparison Type is set to Range or Less than or equal to.
Row Count Value = integer
Specify the number of rows.
Date, datetime, time, and timestamp validation
Designer accepts a variety of date, time, datetime, and timestamp values. Some of the most common values accepted are listed below.
Note
- The list of examples below is not exhaustive, but covers many of the most commonly accepted formats.
- In applicable examples below, the number 13 is used as the value for day, to avoid confusion with month values.
- The backslash character can be replaced by a dash, underscore, full stop, or colon.
- Dates cannot contain time values.
- Times cannot contain date values.
- The datetime/timestamp values listed below can be just the date, but not just the time.
- The datetime/timestamp values accepted combine certain date patterns with all of the time patterns, split by either a whitespace character or a T character.
- There are a number of exceptions that are accepted, such as BC dates, negative dates, and also month values greater than 12. None of these are listed below, and we advise caution when using these values.
The following date values are accepted:
- 13/01/2021
- 01/13/2021
- 2021/1/13
- 13/JAN/2021
- 2021
- JAN 13 2021
- 13 January, 2021
- January 13, 2021
The following time values are accepted:
- 23:12
- 23:12:59
- 23:12:59.123456 (milli/micro/nano are all supported)
The following time values (with offset) are accepted:
- 23:12 +01:00
- 23:12:59 +01:00
- 23:12:59.123456 +01:00 (milli/micro/nano are all supported)
The following datetime and timestamp values are accepted:
- 13-01-2021 23:12:59.123456 +01:00
- 2021-01-13 23:12:59.123456 +01:00
| Snowflake | Databricks | Amazon Redshift |
|---|---|---|
| ✅ | ✅ | ✅ |