Fields and Types

Understand dimensions, measures, data types and field metadata in 0sql.

Learning Objectives

After completing this guide, you will be able to:

• Distinguish between dimensions and measures
• Choose appropriate data types
• Configure field metadata (format, display types, synonyms, hidden)
• Understand when to use different field options

Dimensions vs Measures

Dimensions

Dimensions are categorical fields used for grouping and filtering. They represent attributes of your data.

Characteristics:

• Used in GROUP BY clauses
• Can be filtered
• Typically lower cardinality
• Examples: Customer Name, Product Category, Order Date

Example:

- type: dimension
  name: Customer Name
  data_type: string
  expression:
    sql: customer_name

Measures

Measures are aggregatable quantities. They represent quantitative values that can be summed, averaged, or counted.

Characteristics:

• Used with aggregation functions (SUM, AVG, COUNT)
• Cannot be used in GROUP BY
• Examples: Total Revenue, Order Count, Average Order Value

Example:

- type: measure
  name: Total Revenue
  data_type: decimal
  expression:
    sql: sum(amount)

Field Properties

Beyond type, name, data_type and expression, a field carries metadata. 0sql validates these keys at deploy and stores them on the field. None of them changes the SQL. Some of them come back from GET .../fields: description, synonyms, tags and hidden are in that response; format and display_type are not, so your client reads those from the model it deployed. See Discovery.

Display Types

display_type tells your client what kind of value this is:

• default: plain value
• html: an HTML fragment
• url: a link
• email: an email address
• phone_number: a phone number
• image: an image URL

- type: dimension
  name: Product Image
  data_type: string
  display_type: image
  expression:
    sql: image_url

Format

format describes how your client should print the value, as a shortcut string or a mapping. It rides along with the field’s metadata; nothing in SQL generation uses it. See Field format.

- type: measure
  name: Total Revenue
  data_type: decimal
  format: currency:2
  expression:
    sql: sum(amount)

Hidden Fields

hidden: true hides a field from discovery: the fields endpoint and zsql fields leave it out unless asked for hidden fields, and explore never suggests it. A spec can still reference it by name, and compound measures and calculations can build on it:

- type: dimension
  name: Internal ID
  data_type: integer
  hidden: true
  expression:
    sql: internal_id

Synonyms

Alternative names for a field. A field reference in a spec resolves by uid, name or synonym, and GET .../fields?q= matches synonyms too, so a caller that says client name lands on Customer Name.

- type: dimension
  name: Customer Name
  data_type: string
  synonyms:
    - client name
    - buyer name
    - account name
  expression:
    sql: customer_name

Grains (Date/DateTime Only)

Specify supported granularities for date fields:

- type: dimension
  name: Order Date
  data_type: date
  grains:
    - day
    - week
    - month
    - quarter
    - year
  expression:
    sql: order_date

Available grains: raw, millisecond, second, minute, hour, day, week, month, quarter, year. The list advertises what the field supports; the grain itself is applied at request time with a truncate decorator (see Projections).

Tags

tags are free-form labels. Security policies trigger on them, so a pii tag on a field is what makes a policy fire when that field is projected.

Data Types

Every field declares a data_type that matches the warehouse column (or, for a measure, the aggregate’s result):

Date types also declare which grains callers may group by (see Grains above):

- type: dimension
  name: Order Date
  data_type: date
  grains: [day, week, month, quarter, year]
  expression:
    sql: order_date

- type: measure
  name: Total Revenue
  data_type: decimal
  expression:
    sql: sum(amount)

Full reference: Data Types.

Complete Example

fields:
  # Primary key dimension
  - type: dimension
    name: Order ID
    description: Unique order identifier
    data_type: integer
    expression:
      primary_key: true
      sql: order_id

  # Date dimension with multiple grains
  - type: dimension
    name: Order Date
    description: Date when order was placed
    data_type: date
    grains:
      - day
      - week
      - month
      - quarter
      - year
    expression:
      sql: order_date

  # High-cardinality dimension
  - type: dimension
    name: Customer ID
    description: Customer identifier
    data_type: integer
    expression:
      sql: customer_id

  # Formatted measure with synonyms
  - type: measure
    name: Total Revenue
    description: Sum of all order amounts
    data_type: decimal
    format: currency:2
    synonyms:
      - total sales
      - gross revenue
      - sales amount
    expression:
      sql: sum(amount)

  # Count measure
  - type: measure
    name: Order Count
    description: Number of orders
    data_type: integer
    expression:
      sql: count(*)

Best Practices

1. Use appropriate data types - Match your database column types
2. Add descriptions - Help users understand what each field represents
3. Set grains for dates - Enable flexible date grouping
4. Set format on money and ratio measures so your client prints them consistently
5. Mark primary keys - Helps with query optimization

Next Steps

• Learn about expressions (SQL, lookups, arrays)
• Read the dimensions deep-dive
• Read the measures deep-dive
• Explore data types reference