Introduction
Fields in Dexibit datasets have both a physical and logical type. The logical type controls how a value is formatted in visualisations, while physical type deal with the actual storage and representation of data at the hardware level.
When onboarding your data, Dexibit will set the correct data types for each of your fields so that you don't have to.
When creating a derived metric, you will need to pick the most appropriate physical and logic type for the resulting values. If you're unsure what to choose, let us know. Choosing the wrong types can easily be changed, and are unlikely to cause any issues.
Physical types
Think of physical data types like containers. Just like you have different containers for storing different things in your kitchen (like a jar for sugar, a bottle for oil, etc.), computers also have different ways of storing different types of data.
For example, an integer (like the number 5) might be stored differently than a piece of text (like "hello"). Each type of data has its own container, or "format," that it's stored in. This includes details like how much space it takes up and how it's organized.
VARCHAR | "Variable Character" Use this type when the field consists of a sequence of characters, typically letters, numbers, and symbols, that are treated as text rather than numerical values. "Hello, world!" and "12345" are both string values. |
DATE | Use this type for when the field consists of a date type. For example: MM/DD/YY or DD/MM/YY |
TIME | Use this type for when the field contains a time type. For example: HH:MM:SS (hours, minutes, seconds) or HH:MM:SS.SSS (hours, minutes, seconds, milliseconds) |
TIMESTAMP | Use this type for when the field consists of both date and time. YYYY-MM-DD HH:MM:SS (e.g., 2024-04-30 12:00:00) or YYYY-MM-DD HH:MM:SS.SSS (with milliseconds). |
INTEGER | Use this type when the field consists of whole numbers without any fractional or decimal part. The numbers can be positive, negative, or zero. 1456 -200 |
FLOAT | Use this type when the field consists of numbers that have both a whole part and a fractional part e.g. 3.14 or 0.5. They are typically used for scientific calculations and situations where precision is not critical. |
DECIMAL | Use this type when the field consists of decimal numbers with fixed precision. It is particularly useful for financial calculations and situations where precise decimal arithmetic is required. |
BOOLEAN | Use this type when the field consists of TRUE or FALSE options. When using this the output will be TRUE/FALSE, no other options (no yes/no or other binary options etc) If you want the output to be something other than TRUE/FALSE, please select VARCHAR. For example Member and Non-member will need to be set at VARCHAR.
|
BLOB | Not currently supported |
UUID | Use this type when the field consists of sequence of 32 hexadecimal digits, typically displayed in five groups separated by hyphens, such as 8-4-4-4-12 or 550e8400-e29b-41d4-a716-446655440000 |
LIST(<type>) | Any of the above types can be implemented as a list. |
MAP(<type>, <type>) | Any of the above types can be used within a map. |
STRUCT(…) | Not currently supported |
Logical types
Logical data types are like the labels you put on those containers in your kitchen. They help you understand what's inside each container without having to open it up and look.
In the same way, logical data types describe what kind of information is stored in a particular container in the computer. For instance, you might have a logical data type for numbers, another for text, and so on. These labels help software understand how to work with the data without worrying about the technical details of how it's stored.
For example, they control what functions are available to that column in our app, like percent or growth rate. They also control what can be selected - some logical types can't be the select measure.
IDENTIFIER | Any sort of row ID or sequence number (not a reference to a category) |
CATEGORY | Any sort of catch-all category where might be useful to know cardinality. |
MONETARY | Monetary (financial value) |
PERCENTAGE | Percentage amount (<= 1.0) |
ZIP | US ZIP-code |
DATETIME | Any valid date/time series |
COUNT | Any counted value or total |
GAUGE | A current value snapshot. Use this type when the represent instantaneous values can go up or down, such as temperature and performance tracking of a metric. |
RATE | Normalisation of count per time interval. Use this type when the metric measures the change in a quantity over a specific period, usually expressed as a rate of change per unit of time. Average footfall per visitor Attrition rate |
RATING | A rating out of 5 |