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:
, 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.