Links

Multi Table Metadata JSON

This guide describes the multi table metadata JSON spec.
An example of multi table data. The tables are connected to each other through primary/foreign keys.
Click to see the metadata JSON file
This is an example of a JSON file describing a multi table schema.
{
"METADATA_SPEC_VERSION": "MULTI_TABLE_V1",
"tables": {
"hotels": {
"primary_key": "hotel_id",
"columns": {
"hotel_id": { "sdtype": "id", "regex_format": "HID_[0-9]{3}" },
"city": { "sdtype": "categorical" },
"rating": { "sdtype": "numerical" }
}
},
"guests": {
"primary_key": "guest_email",
"columns": {
"guest_email": { "sdtype": "email" },
"hotel_id": { "sdtype": "id", "regex_format": "HID_[0-9]{3}" },
"checkin_date": { "sdtype": "datetime", "datetime_format": "%d %b %Y" },
"checkout_date": { "sdtype": "datetime", "datetime_format": "%d %b %Y" },
"room_type": { "sdtype": "categorical" }
}
}
},
"relationships": [{
"parent_table_name": "hotels",
"parent_primary_key": "hotel_id",
"child_table_name": "guests",
"child_foreign_key": "hotel_id"
}]
}
Create your metadata programmatically. Use the Python API to automatically detect the metadata based on your data.

Overview

The metadata for a single table contains the following elements:
  • (required) "METADATA_SPEC_VERSION": The version of the metadata. If you are using this, the metadata version will be "MULTI_TABLE_V1", indicating that it is a multi table dataset that is compatible with SDV version 1.
  • (required) "tables": A dictionary that maps the table names to the table-specific metadata such as primary keys, column names and data types
  • (required) "relationships": A list of dictionaries that specify the connections between the tables

Tables

The tables dictionary maps each table name to the table-specific metadata. This includes:
  • (required) "columns": A dictionary that maps the column names to the data types they represent and any other attributes.
  • "primary_key": The column name that is the primary key in the table
  • "alternate_keys": A list of column names that can act as alternate keys in the table

Table Columns

When describing a column, you will provide the column name and the data type, known as the sdtype.
The 5 common sdtypes are: "numerical", "datetime", "categorical", "boolean" and "text". Click on the type below to learn more about the type and how to specify it in the metadata.
boolean
categorical
datetime
numerical
id
other
Boolean columns represent True or False values.
"is_active" : {
"sdtype": "boolean"
}
Properties (None)
Categorical columns represent discrete data. By default, they are unordered (aka nominal data).
"room_type" : {
"sdtype": "categorical"
}
Properties (None)
Date columns represent a point in time
"checkin_date": {
"sdtype": "datetime",
"datetime_format": "%d %b %Y"
}
Properties
Numerical columns represents discrete or continuous numerical values.
"rating": {
"sdtype": "numerical",
"computer_representation": "Float"
}
Properties
  • computer_representation: A string that represents how you'll ultimately store the data. This determines the min and max values allowed Available options are: 'Float', 'Int8', 'Int16', 'Int32', 'Int64', 'UInt8', 'UInt16', 'UInt32', 'UInt64'
ID columns represent identifiers that do not have any special mathematical or semantic meaning
"hotel_id": {
"sdtype": "id",
"regex_format": "HID_[0-9]{3}"
}
Properties
You can input any other data type such as 'phone_number', 'ssn' or 'email'. See the Sdtypes Reference for a full list.
"guest_email": {
"sdtype": "email",
"pii": true
}
Properties
  • pii: A boolean denoting whether the data is sensitive
    • (default) true: The column is sensitive, meaning the values should be anonymized
    • false: The column is not sensitive, meaning the exact set of values can be reused in the synthetic data

Relationships

A list of dictionary objects that describe the relationship between 2 connected tables, parent and child. The parent table contains the primary key references while the child table has rows that refer to its parent. Multiple child rows can refer to the same parent row.
  • "parent_table_name": The name of the parent table
  • "parent_primary_key": The primary key column in the parent table. This column uniquely identifies each row in the parent table .
  • "child_table_name": The name of the child table that refers to the parent
  • "child_foreign_key": The foreign key column in the child table. The values in this column contain a reference to a row in the parent table
Use multiple dictionaries to represent multiple tables.
"relationships": [{
"parent_table_name": "users",
"parent_primary_key": "user_id",
"child_table_name": "sessions",
"child_foreign_key": "user_id"
}, {
"parent_table_name": "sessions",
"parent_primary_key": "session_id",
"child_table_name": "transaction",
"child_foreign_key": "transacted_session_id"
}]
Copyright (c) 2023, DataCebo, Inc.