Skip to content
Queria Docs
English
日本語English
Esc
↑↓navigate↵open⌘Jpreview
On this page

Dataset conventions

The rules that make published tables joinable with other datasets without preprocessing

These are the rules that make a published table joinable with other datasets without any preprocessing. They apply alike to Queria’s own datasets and to everyone else’s.

What a machine can check is left to queria validate (which also runs inside queria compile and queria sync) and to dbt tests; this page holds what you need to make the judgement calls. Which layer a description belongs in is covered in Writing descriptions.

Names

Every name is lowercase letters, digits and underscores.

  • Dataset: the name of the publisher or the source (nlftp, e_stat, gif). Never changes once published
  • Schema: the subject (boundary, facility, vocab). Do not leave it as main, and do not expose layer names (stg, mart, silver)
  • Table: a noun for what one row is (shelter, term, code_system). No dates or versions in the name

When you rename a table or a schema, keep the old name around for a while.

Column names

Column names follow a vocabulary chosen by the kind of data.

  • General data (maps, places, data from outside Japan): schema.org terms in snake_case (postalCode → postal_code)
  • Japanese public data (national and municipal statistics and registers): the English names of the Digital Agency’s Government Interoperability Framework (GIF) in snake_case (localGovernmentCode → local_government_code). When a GIF term only adds a qualifier to a schema.org term, use the shorter one (latitude, not latitudeOfRepresentativePoint)
  • A column’s title is the GIF Japanese name either way, the IMI term when GIF has none, and the source’s own heading when neither does

Columns that mean the same thing under different names are tied together in the vocabulary queria-io/vocabulary. What a column represents is in the data element vocabulary data_elements.csv; what it identifies is in the entity vocabulary entities.csv. When naming a new column:

  1. If the data element is in data_elements.csv, use its title. The column name is the value in its name column for Japanese public data, and its schema_org term in snake_case for general data (the name value when schema_org is empty)
  2. If not, look the term up in your vocabulary (the schemaorg or gif dataset below) and use it in snake_case
  3. If no vocabulary has it, name it as you like, but without abbreviations (lat, pref, num)

Some common data elements:

  • Local government code (6 digits): local_government_code (also seen as lg_code)
  • Prefecture: prefecture in public data, address_region in general data (pref_name)
  • Postal code: postal_code (postcode, zipcode)
  • Latitude and longitude: latitude / longitude (lat / lon)

A column in a code system of the source’s own is qualified with that system’s name, so it is not mistaken for the standard one (police_city_code for police jurisdiction codes).

There is no need to rename existing columns. Abbreviations such as lat or pref_code are the exception: add a column with the new name beside the old one, mark the old one deprecated in its description, and drop it later.

Types and values

Publish values in a type that can be joined and aggregated as is. Whatever meaning the conversion loses goes in another column. Types are read from the data, so they are not written in *.table.yml.

  • Codes (local government codes, corporate numbers, postal codes, grid squares): VARCHAR. Keep leading zeros and state the number of digits and whether there is a check digit in the column description. Postal codes are 7 digits with no hyphen
  • Quantities, amounts, counts: a numeric type, never VARCHAR. State the unit in the column description
  • Dates and points in time: DATE, or an INTEGER Gregorian year when only the year is known. Convert Japanese era years
  • Coordinates: latitude / longitude as decimal-degree DOUBLE in a world geodetic datum (JGD2011 or WGS 84). Convert sources in the old Tokyo Datum. Point data also gets a geometry column, with its coordinate reference system in the column description as EPSG:xxxx
  • Booleans: BOOLEAN. Convert ○, 有, 1 and the like
  • Strings: NFKC-normalized, with surrounding whitespace removed
  • Tables that mix national, prefectural and municipal rows in one column: add an area_level column, and mark subtotal rows

Missing and suppressed values

Where a statistical table puts a symbol such as -, x or … in place of a number, split the column into a value and its status.

  • value: the number; 0 where the symbol means zero, NULL on rows with any other symbol
  • value_flag: the observation status, as a one-letter code from the observation status (OBS_STATUS) of SDMX, the international standard for exchanging statistical data
  • value_raw: the source’s symbol as it was

How the symbols common in Japanese statistical tables map to value_flag. Where the source’s legend says otherwise, follow the legend and state the mapping in the column description.

  • Normal value: A
  • Estimated value: E
  • Provisional or preliminary value: P
  • No cases (- and the like, where the legend means a count of zero): value set to 0, flag A
  • Not applicable (where the legend says a value cannot exist, such as a municipality in a year before it was formed): M
  • Suppressed (x and the like): Q
  • Not collected or unknown (… and the like): L

The column names may change (population and population_flag, for example).

Lookup tables and shared conversions

Lookup tables that turn codes into names go in dbt seeds/, not in a SQL CASE, so a test can catch values with no entry. Conversions every dataset needs, such as date notation variants, degrees-minutes-seconds and restoring leading zeros, are shared rather than rewritten per dataset. For Queria’s own datasets, they belong in the macros/ of dataset-shared: a conversion that is not there yet is added there as a dbt macro, not written inside the dataset.

Grain

Decide what one row of a published table is, and hold it with tests. Duplicate rows silently skew aggregates, so they are stopped before publishing.

  • State the grain in the first sentence of the table description (“One row per municipality per year”)
  • Write the grain columns in primary_key in *.table.yml (see Publish data)
  • For data the source revises (corrections, final figures replacing preliminary ones, municipal mergers), decide per dataset whether a column records which snapshot a row belongs to, and say so in the description
  • Data whose source varies row by row keeps it in a column (source_url)

Key columns

Declare a key with semantic: { role: entity, name: ... } on every column that can be matched across datasets. The syntax and the list of keys are in Publish data.

  • If a published table has a column that can be matched, it always gets a key
  • name is a name from the entity vocabulary entities.csv. Any other name makes queria compile warn, and the table does not show up under it in search. Propose new keys to queria-io/vocabulary
  • A column without semantic whose name is a data element in data_elements.csv (or one of its aliases) that identifies a key is warned about. If the column is not meant as a key, write semantic: { role: dimension }
  • Values to aggregate get role: measure, with agg set to one of sum / avg / min / max / count

Metadata

Write dataset.yml so that it maps directly onto DCAT, the W3C data catalog vocabulary (DCAT-GoJ is the Japanese government’s profile of it). queria export --format dcat-goj writes it out as JSON-LD, so filling in each field is what makes the DCAT metadata.

  • title / title_kana → dct:title: the source’s formal name and its reading
  • description → dct:description: what the data is, what it is for, then the attribution text
  • licenses → dct:license: an SPDX or Queria registry ID. For a license not in the registry, spell out the terms (commercial use, share-alike, attribution)
  • contributors with roles → dct:publisher, dct:rightsHolder and so on: keep the rights holder and the provider apart
  • sources → dct:source: where the data was fetched from. A table’s own sources go in that table’s sources. A table that has them replaces the dataset’s rather than adding to them, so list both if both apply (DCAT carries only the dataset’s)
  • update_frequency → dct:accrualPeriodicity: how often it is actually updated
  • spatial_coverage → dct:spatial: a country code or local government codes
  • temporal_coverage → dct:temporal: the period covered
  • keywords → dcat:keyword: classifying words and the other names people use for the data

Looking up the vocabularies

When choosing a column name or title, the vocabularies themselves can be queried with SQL as Queria datasets. Each is refreshed from its source regularly, so there is no need to keep a copy.

  • gif: items for Japanese public data (vocab.term), code lists (vocab.code), and the definitions and publishers of government code systems (vocab.code_system)
  • imi: IMI terms with their permanent URIs (vocab.term) and the properties of each class (vocab.class_property)
  • schemaorg: schema.org properties (vocab.property, with the snake_case form in snake_case_name) and types (vocab.type, vocab.type_property)
-- GIF items about postal codes, with candidate column names
SELECT class_name, term_ja, snake_case_name FROM gif.vocab.term WHERE term_ja LIKE '%郵便%';

-- Is there a schema.org property of the same name?
SELECT name, snake_case_name, description FROM schemaorg.vocab.property WHERE snake_case_name = 'postal_code';

Checks

Before publishing, queria validate checks:

  • the declaration schema, rights (licenses and sources), and that descriptions are present
  • the role and agg values in semantic (errors)
  • that key names are in the entity vocabulary, and that columns that should be keys have semantic (warnings)
  • that the primary_key columns exist in the data, and their values are unique and never NULL (errors)

Was this page helpful?