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:

  1. Shortcut string, compact colon-separated syntax such as currency:2 or percent:1
  2. Mapping, an explicit type plus 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]

ShortcutStored 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

typeKeys
numberprecision, abbreviate
currencyprecision, unit, abbreviate
percentprecision
date, datetimepattern (strftime)
htmltemplate ({{value}} stands for the value)
javascriptfunction

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

  1. Use currency:2, or a mapping with unit, for monetary measures
  2. Use percent for ratios stored as decimals (0 to 1)
  3. Use number:0 for integer counts
  4. Keep business logic in SQL; format is presentation only

Next Steps