Skip to content

Columns

A column entry says what a column is and which unit it is in. Every key is optional. Columns sit under datasets.<name>.columns, keyed by the column name as stored.

Together, the column names, each column's type (and physical_type) and unit, the columns' aliases and the dataset's removed_columns are the dataset's schema. The types come from the data, so a tool can compare two builds and flag a type change, a likely unit or scale change, or blanks that appear. What to declare for each change, and what a tool detects on its own, is on Schema changes.

Example

dates_asof:                     # this dataset draws on two vendor sources
  vendor_a: release_a
  vendor_b: release_b
columns:
  rent:
    type: number
    description: asking rent
    currency: USD
    per: year
    as_of_sources: [vendor_a]   # only vendor_a: its citation shows vendor_a's as-of date only
  rent_growth:                  # no as_of_sources: cited with every source's as-of date
    type: number
    description: quarter-on-quarter rent growth
    unit: decimal
  area:
    type: number
    description: floor area
    unit: "[sft_i]"
  amount:
    type: number
    description: amount in the currency named in the column amount_unit
    unit_from: amount_unit
  amount_unit:
    type: string
    description: "ISO 4217 code of each row's amount, such as USD or EUR"
  rent_old:
    description: replaced
    use_instead: rent

Type, unit and money

These are three different questions about a column:

Question Field Applies to Example
What kind of value is stored? type (from the data today; see below) every column number, integer, string, date
What does a number measure? unit (UCUM) numeric measures only square feet, percent, a count of buildings
Which money, per which period? currency + per monetary columns only USD per year
  • Type says how to read the stored value, using the ODCS logical types. A tool fills it from the data on every build and writes it into a new file, so you do not type it by hand; the card shows it in the type column. A declared type that disagrees with the latest build is a warning. physical_type (the storage type, such as int64 or large_string) is optional: a producer may declare it, a tool keeps the build's value. A column of mixed values (text and numbers) has type object: store one type so readers can parse it. A column that is entirely blank has no type yet.
  • Unit says what a number means. 0.012 stored as a number could be 1.2% (decimal) or 0.012% (%); only the unit tells them apart. Names, codes, ids, flags and dates have no unit: leave unit out (a unit on a non-numeric type is a warning).
  • Money takes currency (an ISO 4217 code such as USD) and, for a flow or rate, per (the period): rent of USD per year is currency: USD and per: year. Never put a currency code in unit; that is an error.
  • Money per something else (USD per square foot per year) has no field yet: write currency: USD, per: year and say "per square foot" in the description.

Units (UCUM)

unit takes a code from UCUM, the Unified Code for Units of Measure, plus three short aliases (decimal, pct, bp). A code is checked against the full UCUM table: an unknown symbol is an error, not only bad syntax. [sqft] is not UCUM; the code is [sft_i].

Where to look a unit up:

How a code is built:

Part Means Example
a unit symbol one unit m2 square metres, a year, mo month, d day
[...] a customary (non-metric) unit [sft_i] square foot, [acr_us] acre, [mi_i] mile
10*n. times 10 to the power n: use it for thousands, millions 10*3.[sft_i] thousand square feet
. and / multiply and divide 1/a per year, [sft_i]/{building} square feet per building
{...} a label for what is counted; it does not change the number {building} a count of buildings
1 a pure number with no unit: a ratio or a count 3

Common units

Write Means Example value
decimal A plain fraction (an alias of UCUM 1) 0.012 is 1.2%
pct or % Percent; both are accepted 1.2 is 1.2%
bp Basis points (an alias of UCUM 10*-4) 120 is 1.2%
1 A ratio or index with no unit 1.04
{building} A count of buildings (any word in braces names what is counted) 3
[sft_i] Square feet 412.5
10*3.[sft_i] Thousands of square feet 412.5 is 412,500 sq ft
10*6.[sft_i] Millions of square feet 1.2 is 1.2 million sq ft
m2 Square metres 38.3
[acr_us] Acres 12
a Years (a duration, such as a lease term) 7.5
mo Months (a duration) 18

Codes with brackets, braces or * must be quoted in YAML: unit: "[sft_i]", unit: "{building}".

Definitions

Key Type Required Priority Check Description
columns.<name> map of name to Column no Critical error if the column is not in the data The key is the column name as stored.
description string no Critical prose lint (warning) What the column is. Say what it is; put "what not to do" in do_not.
type string, integer, number, boolean, date, timestamp, time, object or array no Useful error unless one of the ODCS logicalType values; warning if it disagrees with the latest build How the value is stored (an ODCS logical type). A tool fills it from the data.
physical_type string no None - The storage type as the engine names it, such as int64, large_string or timestamp[us, tz=UTC]. A tool keeps the build's value; a producer may declare it.
unit string, UCUM no Critical error if not a known UCUM unit (unknown symbol or syntax; a tool may report it as a warning while publishing); warning if the column's type is not numeric; error if it is a three-letter currency code; error if set together with unit_from The unit. The aliases pct (%), bp (10*-4) and decimal (1) are accepted and expanded.
currency string no Critical - ISO 4217 code for a monetary column. Use this, not unit, for currencies.
per day, week, month, quarter, year no Critical error on any other value The period a flow or rate is per, for example rent per year.
unit_from column name no Critical error if set together with unit; error if the column is not in the data Another column holds the unit of each row: a UCUM code, or an ISO 4217 code for money. Declare that column too.
use_instead column name, or <project>/<Dataset>#<column> no Important error if a / form is not a valid id; a plain name must be in the data The column to use instead of this one.
as_of_sources list of source names no Useful error if a name is not a key of the dataset's dates_asof The as-of sources this column derives from, so its citation lists only those. Leave it out when the column draws on every source in the dataset's dates_asof (the default); set it only when a dataset mixes sources and this column comes from some of them.
aliases list of string or object (name, until) no Important error unless each name uses only letters, digits and _ and does not start with a digit; error if a name is in the dataset's removed_columns; error if a name is a current column of the dataset (a tool may report this one as a warning while publishing) Former names of this column. A citation or lookup by a former name reads this column, and the card says "formerly".