Field format
format describes how a field’s values should be presented. 0sql validates it at deploy and stores it on the field. Nothing in SQL generation reads it: the statement you get back returns raw values, and your client applies the format. It is not one of the keys GET .../fields returns, so your client gets it from the model you deployed, not from the discovery endpoints.
Overview
Set format on a dimension or measure in table YAML, in one of two equivalent forms:
- Shortcut string, compact colon-separated syntax such as
currency:2orpercent:1 - Mapping, an explicit
typeplus options
Both normalize to the same stored JSON, for example {"type":"currency","precision":2}. Use the shortcut for the common cases and the mapping when you need several options or want the review diff to be obvious.
Shortcut syntax
type[:precision[:unit]][:abbreviate]
| Shortcut | Stored format |
|---|---|
number:2 | { type: number, precision: 2 } |
number:2:abbreviate | { type: number, precision: 2, abbreviate: true } |
currency:2 | { type: currency, precision: 2 } |
currency:2:USD | { type: currency, precision: 2, unit: "USD" } |
currency:2:abbreviate | { type: currency, precision: 2, abbreviate: true } |
percent:1 | { type: percent, precision: 1 } |
date:short | { type: date, pattern: "%b %d, %Y" } |
date:long | { type: date, pattern: "%B %d, %Y" } |
date:iso | { type: date, pattern: "%Y-%m-%d" } |
date:%m/%d/%Y | { type: date, pattern: "%m/%d/%Y" }, any strftime pattern |
datetime:short | { type: datetime, pattern: "%b %d, %Y %I:%M %p" } |
datetime:iso | { type: datetime, pattern: "%Y-%m-%d %H:%M:%S" } |
html:<strong>{{value}}</strong> | { type: html, template: "..." } |
javascript:formatValue | { type: javascript, function: "formatValue" } |
Types: number, currency, percent, date, datetime, html, javascript. A shortcut with an unknown type is dropped silently and the field has no format. If a shortcut is ambiguous in your YAML editor, quote it: format: "percent:2".
html and javascript are stored like any other type. What they mean is up to your rendering layer; 0sql does not evaluate templates or functions.
Mapping syntax
| type | Keys |
|---|---|
number | precision, abbreviate |
currency | precision, unit, abbreviate |
percent | precision |
date, datetime | pattern (strftime) |
html | template ({{value}} stands for the value) |
javascript | function |
Normalization: blank values are dropped, precision is coerced to an integer, and abbreviate is kept only when true. Other keys are stored as given.
Examples
- type: measure
name: Store Net Paid
data_type: decimal
format: currency:2
expression:
sql: sum(ss_net_paid)
- type: measure
name: Profit Margin
data_type: decimal
format:
type: percent
precision: 2
expression:
sql: sum(ss_net_profit) / nullif(sum(ss_net_paid), 0)
- type: measure
name: Store Quantity
data_type: integer
format: number:0
expression:
sql: sum(ss_quantity)
- type: dimension
name: Date
data_type: date
format: date:short
expression:
sql: d_date
A client that reads the stored format would show these as $1,234.56, 45.67% (for a stored ratio of 0.4567), 1,234 and Jan 05, 2026.
Validation
zsql check and zsql deploy reject a format that is neither a string nor a mapping with Field <name> errors: format must be a shortcut string or a mapping. There is no default format: a field without format carries none, and your client decides how to print a decimal or a date.
Best practices
- Use
currency:2, or a mapping withunit, for monetary measures - Use
percentfor ratios stored as decimals (0 to 1) - Use
number:0for integer counts - Keep business logic in SQL;
formatis presentation only
Next Steps
- Dimensions and measures
- Data types
- Field discovery, for the field keys the API does return