Assert View
    • Dark
      Light

    Assert View

    • Dark
      Light

    Article Summary

    Assert View

    This component forms part of Matillion ETL's assert components suite. This suite of components is an Enterprise Mode feature. Click the link to learn more about Matillion ETL Enterprise Mode.

    Learn about Matillion ETL's other assert components in the Assert Components Overview guide.

    Overview

    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 Job, 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

    Snowflake Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    MetadataNameThe name of each column.
    TypeSelect a data type.
    VARCHAR: this type can hold any kind of data, subject to a maximum size. More...
    NUMBER: this type is suitable for whole-number types (no decimals). More...
    Note: You can enter AUTOINCREMENT or IDENTITY as the default value for any numeric-type column and the column will automatically give an incremented value on each new row starting from 1. This feature is useful when creating a unique key on the table.
    FLOAT: this type is suitable for numeric types, with or without decimals. More...
    BOOLEAN: this type is suitable for data that is either 'true' or 'false'. More...
    DATE: this type is suitable for dates without times. More...
    TIMESTAMP: this type is suitable for timestamps. More...
    TIME: this type is suitable for times target="_blank">More...
    VARIANT: a flexible type that can be used for any purpose. More...
    SizeFor 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.
    ScaleRelevant only for numeric data, it is the maximum number of digits that may appear to the right of the decimal point.
    ValuesStringInput the values for each column. Separate values in the same column with a comma.
    Ignore Metadata OrderBooleanWhen set to "True", Matillion ETL will ignore the metadata order. Default is "False".
    Row Count Comparison TypeSelectSelect how row count values are compared.
    Possible comparison operators include: "Equal to", "Greater than or equal to", "Less than or equal to", and "Range".
    Equal to: The value in the Input Column must be equal to that specified in the Value Column. This is the default comparison operator.
    Greater than or equal to: The value in the Input Column must be greater than or equal to the value in the Value Column.
    Less than or equal to: The value in the Input Column must be less than or equal to the value in the Value Column.
    Range: This allows users to specify the range for the value of the row counts.
    Selecting "Range" makes the Upper Value and Lower Value properties available.
    Note: Not all data sources support all comparison operators; thus, it is likely that only a subset of the above comparison operators will be available to choose from.
    Lower ValueIntegerSet the lower value. This property is only available when Row Count Comparison Type is set to "Range".
    Upper ValueIntegerSet the upper value. This property is only available when Row Count Comparison Type is set to "Range".
    Row Count ValueIntegerSpecify the number of rows.

    Redshift Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    MetadataNameThe column name.
    TypeSelect the data type.
    Text: a string can hold any kind of data, subject to a maximum size. More...
    Integer: an integer data type is suitable for whole numbers (no decimals). More...
    Numeric: the numeric data type accepts numbers, with or without decimals. More...
    Real: this type is suitable for data of a single precision floating-point number. More...
    Double Precision: this type is suitable for data of a double precision floating-point number. More...
    Boolean: data with a Boolean data type can be either "true" or "false". More...
    Date: this type is suitable for dates without times. More...
    DateTime: this type is suitable for dates, times, or timestamps (both date and time). More...
    SUPER: use the SUPER data type to store semi-structured data or documents as values. More...
    SizeFor text types, this is the maximum length. This is a limit on the number of bytes, not characters. With Redshift, since all data is stored using UTF-8, any non-ASCII character will count as 2 or more bytes.
    For Numeric types, this is the total number of digits allowed, whether before or after the decimal point.
    ScaleRelevant only for numeric data, it is the maximum number of digits that may appear to the right of the decimal point.
    ValuesStringInput the values for each column. Separate values in the same column with a comma.
    Ignore Metadata OrderBooleanWhen set to "True", Matillion ETL will ignore the metadata order. Default is "False".
    Row Count Comparison TypeSelectSelect how row count values are compared.
    Possible comparison operators include: "Equal to", "Greater than or equal to", "Less than or equal to", and "Range".
    Equal to: The value in the Input Column must be equal to that specified in the Value Column. This is the default comparison operator.
    Greater than or equal to: The value in the Input Column must be greater than or equal to the value in the Value Column.
    Less than or equal to: The value in the Input Column must be less than or equal to the value in the Value Column.
    Range: This allows users to specify the range for the value of the row counts.
    Selecting "Range" makes the Upper Value and Lower Value properties available.
    Note: Not all data sources support all comparison operators; thus, it is likely that only a subset of the above comparison operators will be available to choose from.
    Lower ValueIntegerSet the lower value. This property is only available when Row Count Comparison Type is set to "Range".
    Upper ValueIntegerSet the upper value. This property is only available when Row Count Comparison Type is set to "Range".
    Row Count ValueIntegerSpecify the number of rows.

    BigQuery Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    MetadataNameThe name of each column.
    Data TypeString: a string can hold any kind of data, subject to a maximum size. More...
    Integer: an integer data type is suitable for whole numbers (no decimals). More...
    Float: floating point values are approximate numeric values with fractional components. More...
    Numeric: the numeric data type accepts numbers, with or without decimals. More...
    Boolean: data with a Boolean data type can be either "true" or "false". More...
    Date: this data type is suitable for dates without times. More...
    Time: this data type is suitable for time, independent of a specific date or timezoneMore...
    DateTime: this type is suitable for dates, times, or timestamps (both date and time). More...
    Timestamp: this type is a timestamp left unformatted (exists as Unix/Epoch Time). More...
    Struct: this data type is suitable for a struct. More...
    Record: this data type is suitable for a record. More...
    ModeBigQuery supports the following modes for your columns. Using a mode is optional. If the mode is unspecified, the column defaults to NULLABLE. NULLABLE: Column allows NULL values (default).
    REPEATED: Column contains an array of values of the specified type.
    ValuesStringInput the values for each column. Separate values in the same column with a comma.
    Ignore Metadata OrderBooleanWhen set to "True", Matillion ETL will ignore the metadata order. Default is "False".
    Row Count Comparison TypeSelectSelect how row count values are compared.
    Possible comparison operators include: "Equal to", "Greater than or equal to", "Less than or equal to", and "Range".
    Equal to: The value in the Input Column must be equal to that specified in the Value Column. This is the default comparison operator.
    Greater than or equal to: The value in the Input Column must be greater than or equal to the value in the Value Column.
    Less than or equal to: The value in the Input Column must be less than or equal to the value in the Value Column.
    Range: This allows users to specify the range for the value of the row counts.
    Selecting "Range" makes the Upper Value and Lower Value properties available.
    Note: Not all data sources support all comparison operators; thus, it is likely that only a subset of the above comparison operators will be available to choose from.
    Lower ValueIntegerSet the lower value. This property is only available when Row Count Comparison Type is set to "Range".
    Upper ValueIntegerSet the upper value. This property is only available when Row Count Comparison Type is set to "Range".
    Row Count ValueIntegerSpecify the number of rows.

    Synapse Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    MetadataColumn NameInput the name of the column.
    Data TypeSelect the data type. The available data types are: DATE: this data type is suitable for dates without times. More...
    DATETIME: this data type is suitable for timestamps. More...
    TIME: this data type is suitable for times target="_blank">More...
    INTEGER: this data type is suitable for whole number types (no decimals). More...
    NUMERIC: this data type is suitable for numeric types, with or without decimals. More...
    TEXT: this data type is suitable for text types. More...
    FLOAT: this data type is suitable for approximate number data types for use with floating point numeric data. More...
    BOOLEAN: this data type is suitable for data whether values are either "true" or "false". More...
    SizeDefine the size. For T-SQL, this is denoted as Precision. More...
    PrecisionDefine the precision. For T-SQL, this is denoted as Scale. More...
    ValuesStringInput the values for each column. Separate values in the same column with a comma.
    Ignore Metadata OrderBooleanWhen set to "True", Matillion ETL will ignore the metadata order. Default is "False".
    Row Count Comparison TypeSelectSelect how row count values are compared.
    Possible comparison operators include: "Equal to", "Greater than or equal to", "Less than or equal to", and "Range".
    Equal to: The value in the Input Column must be equal to that specified in the Value Column. This is the default comparison operator.
    Greater than or equal to: The value in the Input Column must be greater than or equal to the value in the Value Column.
    Less than or equal to: The value in the Input Column must be less than or equal to the value in the Value Column.
    Range: This allows users to specify the range for the value of the row counts.
    Selecting "Range" makes the Upper Value and Lower Value properties available.
    Note: Not all data sources support all comparison operators; thus, it is likely that only a subset of the above comparison operators will be available to choose from.
    Lower ValueIntegerSet the lower value. This property is only available when Row Count Comparison Type is set to "Range".
    Upper ValueIntegerSet the upper value. This property is only available when Row Count Comparison Type is set to "Range".
    Row Count ValueIntegerSpecify the number of rows.

    Delta Lake Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    MetadataColumn NameSpecify the name of the input column.
    Data TypeSelect from INTEGER, NUMBER, FLOAT, TEXT, TIMESTAMP, DATE, BOOLEAN, BINARY as the data type for this column. For more information, read Data Types.
    SizeSet the data type size.
    ScaleSet the data type scale.
    ValuesStringInput the values for each column. Separate values in the same column with a comma.
    Ignore Metadata OrderBooleanWhen set to "True", Matillion ETL will ignore the metadata order. Default is "False".
    Row Count Comparison TypeSelectSelect how row count values are compared.
    Possible comparison operators include: "Equal to", "Greater than or equal to", "Less than or equal to", and "Range".
    Equal to: The value in the Input Column must be equal to that specified in the Value Column. This is the default comparison operator.
    Greater than or equal to: The value in the Input Column must be greater than or equal to the value in the Value Column.
    Less than or equal to: The value in the Input Column must be less than or equal to the value in the Value Column.
    Range: This allows users to specify the range for the value of the row counts.
    Selecting "Range" makes the Upper Value and Lower Value properties available.
    Note: Not all data sources support all comparison operators; thus, it is likely that only a subset of the above comparison operators will be available to choose from.
    Lower ValueIntegerSet the lower value. This property is only available when Row Count Comparison Type is set to "Range".
    Upper ValueIntegerSet the upper value. This property is only available when Row Count Comparison Type is set to "Range".
    Row Count ValueIntegerSpecify the number of rows.

    Date, DateTime, Time, and Timestamp Validation

    Matillion ETL accepts a variety of date, time, datetime, and timestamp values. Some of the most common values accepted are listed below.

    Please 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.
    • Please note that 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