Skip to content

Attribute table

An Attribute defines the metadata information of a column of the content, for example the type of the values in the column, the order respect other columns of the same Entity, the name, etc. It also contains information about how the content will be ingested into the Data Lake. Each Attribute belongs to an Entity.

Attribute contains data about the schema of the files. This information is used to identify columns in a file, create Databricks tables, and, when necessary, create SQL tables in which the data is going to be extracted from Data Lake (this is for Data Products). Most of the metadata about the columns is used to validate fields.

Column Description Format Required
Id Attribute identifier int Yes
IdEntity The identifier of the related Entity int Yes
IdProvider Identifier of the Provider that generates this type of file int
IdDataStorageUnit Identifier of the Data Storage Unit in which the Entity is contained int
SourceName Name of the column in the data source. It is used in case it is different to the name used in Sidra varchar(150)
Name Name of the column. Space character is not allowed. The metadata API will sanitize spaces in characters if they are provided in Entity or Attribute names, by replacing with _ character (only when created through the API) varchar(150) Yes
HiveType The type of the column. It will be used to generate the Hive table varchar(30) Yes
MaxLen Maximum size of the column. This applies to columns with STRING type. int
IsNullable Tells if the column can have null values or not. bit Yes
NeedTrim Tells if the columns need to be trimmed, taking out leading and trailing spaces in the value bit Yes
RemoveQuotes true when removing quotes from the column is necessary. Otherwise, false bit Yes
ReplacedText Indicates a text in the column ReplacedText to be replaced by ReplacementText value varchar(5)
ReplacementText The text to replace the ReplacedText with varchar(5)
SpecialFormat If the column need to pass a validation that cannot be enforced using the rest of the columns, it is specified here. For example, to validate a date’s format. If the field of the column (which defines the content of this column) is calculated, then, this field can describe how to calculate the column value itself. varchar(512)
TreatEmptyAsNull Tells if empty values should be considered nulls bit Yes
IsPrimaryKey Used when the IsMerged in Entity, is true to determine what data is the first key in origin and new. For plain text files, it is quite often set to false for all the Attributes bit Yes
Order The order of the column inside the file smallint Yes
IsCalculated Tells if the column is calculated, and not a column inside the file. In SpecialFormat column can be checked how to obtain the column value. If SpecialFormat is not defined, an error will be displayed bit Yes
IsPartitionColumn Tells if the column is used in the table partition or not. True applies to the fields used like partition when storing the data in the DSU bit Yes
IsMetadata Tells if the column is metadata for the system, and not data of the file itself bit Yes
SQLType Indicates the type of the column to create a SQL table. Example values: "INT", "DATETIME2", "BIT", "DATE", "DECIMAL" varchar(30)
ValidationText Defines specific validations to be performed for this data instead of using the validations generated by the GenerateTransferQuery custom activity. When a value for ValidationText is provided, any other validation is override and the only one applied is the ValidationText varchar (512)
Description Description of this Attribute nvarchar(max)
ParentSecurityPath The SecurityPath of the Entity to which the Attribute belongs varchar (100) Yes
DataMask This field indicates the Data masking rule that will be applied in the preview of this field in the Data Catalog varchar(200)
ItemId GUID of the Attribute.
SourceType Represents the type that the Attribute has in the source system (DB type in the case of databases) uniqueidentifier Yes
SecurityPath Path of identifiers used for authorization varchar(50)
LastUpdated Attribute last update date datetime2(0) Yes

TreatEmptyAsNull column

If it is true and the origin value is "empty", then the final result is NULL. This condition affects the result in the case the NULL value is accepted or not.

For CSV files, the reading of an "empty" value equals to a NULL value by default, regardless how TreatEmptyAsNull field is configured.

DataMask column

DataMask relies on the Microsoft SQL server implementation. For DataMask functions, please refer to the list with all SQL Server functions from Microsoft documentation. An example of such value of DataMask field can be:

MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')

, which will mean that a custom data masking function will be applied, where only the first character in the string will not be masked.

SQLType column

Many Data Products end up storing data extracted from the Data Lake in an Azure SQL Database. In order to auto-generate the tables to hold that data, this column contains specific SQL Server types such as decimal with scale and precision, or datetime2.

IsPartitionColumn column

As it has been described in previous sections, data is stored in Data Lake in a optimized way. One of these optimization steps is by partitioning, so extraction can be more accurate and faster. Setting this column to true will make the table created in the Data Lake to be partitioned by this column. It is important to point that, if there are 2 columns acting as partition column, the order will impact on how the table is partitioned. That is, in the example below partitions will be: SidraFileDate then SidraIdAsset. It is important to make sure that columns are in the correct order.

IsPrimaryKey

Only used for "merged" entities. For plain text files, it is quite often set to false for all the Attributes.

ValidationText

When a value for ValidationText is provided, any other validation is overridden and the only one applied is the ValidationText.

Versions

From Sidra 2022.R3 version onwards, the field RemoveQuotes has been deprecated.

Sidra system Attributes

There are five Attributes that are added to the original set of specific Attributes for the Entity. They are used by Sidra for multiple purposes and it is mandatory to have them. Those Attributes are: SourceByteOffset, SidraLoadDate , SidraPassedValidation, SidraFileDate and SidraIdAsset.

By default, Sidra partitions the Data Lake tables by SidraFileDate and SidraIdAsset, but it is up to the team configuring the system to choose which partitions to use. Sidra team strongly encourages to use these two Attributes or, at least, SidraIdAsset which is widely used by the platform and has a good effect on its performance.