Skip to main content

BimlFlex Data Type Conversions

BimlFlex uses a separate collection of data types that closely mirror .NET data types with a few additions to provide better support in modeling. This allows for easy, hassle free development as the modeler no longer has to worry about what systems support what data types.

Included below are the mapping guidelines used by BimlFlex in order to ensure a consistent data type environment across the multiple platforms that are supported.

General Mappings

The general mappings are the base guidelines that are followed when generating DDL from the contained BimlFlex metadata. Choose the target platform to see how a BimlFlex data type maps to a target data type.

SQL Server

String

BimlFlex Data TypeSQL Server Data Type
AnsiStringFixedLength(length)char(length)
AnsiString(length)varchar(length)
StringFixedLength(length)nchar(length)
String(length)nvarchar(length)

Number

BimlFlex Data TypeSQL Server Data Type
smallmoneysmallmoney
Currencymoney
Booleanbit
Bytetinyint
SBytesmallint
Int16smallint
UInt16smallint
Int32int
UInt32int
Int64bigint
Uint64bigint
Decimal(precision,scale)decimal(precision,scale)
VarNumeric(precision,scale)decimal(precision,scale)
Single(precision)float(precision)
Double(precision)float(precision)

Time

BimlFlex Data TypeSQL Server Data Type
Time(precision)time(precision)
Datedate
DateTime(precision)datetime(precision)
DateTime2(precision)datetime2(precision)
DateTimeOffset(precision)datetimeoffset(precision)

Other

BimlFlex Data TypeSQL Server Data Type
geometrygeometry
geographygeography
hierarchyidhierarchyid
Xmlnvarchar(length)
Binary(length)varbinary(length)
Guiduniqueidentifier
Objectsql_variant

Azure Synapse

String

BimlFlex Data TypeSynapse Data Type
AnsiStringFixedLength(length)char(length)
AnsiString(length)varchar(length)
StringFixedLength(length)nchar(length)
String(length)nvarchar(length)

Number

BimlFlex Data TypeSynapse Data Type
smallmoneysmallmoney
Currencymoney
Booleanbit
Bytetinyint
SBytesmallint
Int16smallint
UInt16smallint
Int32int
UInt32int
Int64bigint
Uint64bigint
Decimal(precision,scale)decimal(precision,scale)
VarNumeric(precision,scale)decimal(precision,scale)
Single(precision)float(precision)
Double(precision)float(precision)

Time

BimlFlex Data TypeSynapse Data Type
Time(precision)time(precision)
Datedate
DateTime(precision)datetime(precision)
DateTime2(precision)datetime2(precision)
DateTimeOffset(precision)datetimeoffset(precision)

Other

BimlFlex Data TypeSynapse Data Type
geometryvarbinary(8000)
geographyvarbinary(8000)
hierarchyidvarbinary(8000)
Xmlvarbinary(8000)
Binary(length)varbinary(length)
Guidvarchar(50)
Objectsql_variant

Snowflake

String

BimlFlex Data TypeSnowflake Data Type
AnsiStringFixedLength(length)char(length)
AnsiString(length)varchar(length)
StringFixedLength(length)char(length)
String(length)varchar(length)

Number

BimlFlex Data TypeSnowflake Data Type
smallmoneydecimal(19,4)
Currencydecimal(19,4)
Booleanboolean
Bytetinyint
SBytesmallint
Int16smallint
UInt16smallint
Int32int
UInt32int
Int64bigint
Uint64bigint
Decimal(precision,scale)decimal(precision,scale)
VarNumeric(precision,scale)decimal(precision,scale)
Single(precision)float(precision)
Double(precision)float(precision)

Time

BimlFlex Data TypeSnowflake Data Type
Time(precision)time(precision)
Datedate
DateTime(precision)datetime(precision)
DateTime2(precision)timestamp(precision)
DateTimeOffset(precision)timestamp_tz(precision)

Other

BimlFlex Data TypeSnowflake Data Type
geometryvarbinary(8388608)
geographyvarbinary(8388608)
hierarchyidvarbinary(8388608)
Xmlvarchar(length)
Binary(length)varbinary(length)
Guidvarchar(50)
Objectvarbinary(8388608)

Full List

String

BimlFlex Data TypeSQL Server Data TypeSynapse Data TypeSnowflake Data Type
AnsiStringFixedLength(length)char(length)
StringFixedLength(length)nchar(length)char(length)
AnsiString(length)varchar(length)
String(length)nvarchar(length)varchar(length)

Number

BimlFlex Data TypeSQL Server Data TypeSynapse Data TypeSnowflake Data Type
smallmoneysmallmoneyvarbinary(8000)decimal(19,4)
Currencymoneydecimal(19,4)
Booleanbitboolean
Bytetinyint
SBytesmallint
Int16smallint
UInt16smallint
Int32int
UInt32int
Int64bigint
Uint64bigint
Decimal(precision,scale)decimal(precision,scale)
VarNumeric(precision,scale)decimal(precision,scale)
Single(precision)float(precision)
Double(precision)float(precision)

Time

BimlFlex Data TypeSQL Server Data TypeSynapse Data TypeSnowflake Data Type
Time(precision)time(precision)
Datedate
DateTime(precision)datetime(precision)
DateTime2(precision)datetime2(precision)timestamp(precision)
DateTimeOffset(precision)datetimeoffset(precision)timestamp_tz(precision)

Other

BimlFlex Data TypeSQL Server Data TypeSynapse Data TypeSnowflake Data Type
geometrygeometryvarbinary(8000)varbinary(8388608)
geographygeographyvarbinary(8000)varbinary(8388608)
hierarchyidhierarchyidvarbinary(8000)varbinary(8388608)
Xmlnvarchar(length)varchar(length)
Binary(length)varbinary(length)
Guiduniqueidentifiernvarchar(50)varchar(50)
Objectsql_variantvarbinary(8388608)
note

A blank column indicates no change in logic vs the SQL Server Data Type.


Implicit Conversions

In a few cases BimlFlex will implicitly convert a source data type into another data type. These are applied to the lesser used or infrequently supported data types to ensure a healthy and operational data mart. These cases are highlighted below.

BimlFlex Data TypeSQL Server Data TypeSynapse Data TypeSnowflake Data Type
geometryvarbinary(8000)varbinary(8388608)
geographyvarbinary(8000)varbinary(8388608)
hierarchyidvarbinary(8000)varbinary(8388608)
smallmoneyvarbinary(8000)decimal(19,4)
Guidnvarchar(50)*nvarchar(50)varchar(50)
Xmlnvarchar(length)varbinary(8000)varchar(length)
note

A blank column indicates no change in logic vs the BimlFlex Data Type.

*: Default logic for SQL Server is to use a uniqueidentifier data type for Guid columns.
The column will only use a nvarchar(50) data type if the CovertGuidToString setting in the Settings Editor is set to Y.

Default Logic

In cases where required metadata is omitted or values exceed certain thresholds BimlFlex will adjust some data types. The below table outlines the rules used. All sources use the Default column in the table below except for Snowflake which does not support a MAX identifier. The values used for Snowflake are annotated in the Snowflake column.

ConditionDefaultSnowflake
char(8000+)char(MAX)char(16777216)
nchar(4000+)nchar(MAX)char(16777216)
varchar(8000+)varchar(MAX)varchar(16777216)
nvarchar(4000+)nvarchar(MAX)varchar(16777216)
varbinary(8000+)varbinary(MAX)varbinary(8388608)
decimal(0-,scale)decimal(18,scale)
decimal(precision,--)decimal(precision,0)
double(--)float(53)
single(--)float(24)
time(--)time(7)
datetime2(--)datetime2(7)timestamp(7)
datetimeoffset(--)datetimeoffset(7)timestamp_tz(7)
note

A blank column indicates no change in logic vs the Default.
+ Indicates any number greater or equal to.
- Indicates any number lower than or equal to.
-- Indicates an unassigned value.