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.

Format vs display type

display_type says what kind of thing the value is (url, email, image, html, phone_number); format says how to print it (numbers, dates, templates). Both are metadata, both can sit on the same field, and neither changes the SQL.

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