Assert External Table
    • Dark
      Light

    Assert External Table

    • Dark
      Light

    Article Summary

    This article is specific to the following platforms - Snowflake - Redshift - BigQuery - Synapse.

    Assert External Table Component

    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 External Table component asserts the metadata and row count of an external table.


    Properties

    The table below cites the Assert External Table component's setup properties, including any actions required of the user.

    Snowflake Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    DatabaseSelectChoose a database.
    SchemaSelectSelect the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, see this article.
    Table NameSelectSelect the table to assert.
    ExistsSelectThis property asserts that the table exists within the database.
    Table MetadataColumn NameThe name of the new column.
    Data TypeVarchar: This type is suitable for numbers and letters. A varchar or Variable Character Field is a set of character data of indeterminate length. More...
    Number: This type is suitable for numeric types, without or with decimals. More...
    Float: This type of values are approximate numeric values with fractional components. 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...
    Time: This type is suitable for time, independent of a specific date and timezone. More...
    Timestamp: This type is a timestamp left unformatted (exists as Unix/Epoch Time). More...
    Variant: Variant is a tagged universal type that can hold up to 16 MB of any data type supported by Snowflake. 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.
    PrecisionThe precision of the data in the column. Will be 0 (zero) for non-applicable types.
    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.
    SchemaSelectSelect the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, see Schema Support.
    Table NameSelectSelect the table to assert.
    ExistsSelectThis property asserts that the table exists within the database.
    Table MetadataColumn NameThe name of the new column.
    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...
    SizeFor text types, this is the maximum length. This is a limit on the number of bytes, not characters. For 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.
    Decimal PlacesRelevant only for "Numeric" data, it is the maximum number of digits that may appear to the right of the decimal point.
    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.
    ProjectSelectThe target BigQuery project to load data into.
    DatasetSelectThe target BigQuery dataset to load data into.
    Table NameSelectSelect the table to assert.
    ExistsSelectThis property asserts that the table exists within the database.
    Table MetadataField NameThe name of the new field.
    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).
    REQUIRED: NULL values are not allowed.
    REPEATED: Column contains an array of values of the specified type.
    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.
    SchemaSelectSelect the table schema. The special value, [Environment Default], will use the schema defined in the environment.
    For more information on schemas, please see the Azure Synapse documentation.
    Table NameSelectSelect the external table to assert.
    ExistsSelectThis property asserts that the external table exists within the database.
    Table 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 where 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...
    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.