dim_data_dictionary - SourceMedium

dim_data_dictionary describes every table and column SourceMedium delivers to your warehouse, including this one. Use it to check which tables you have, whether they are fresh, and what a column means before you write SQL.

Grain: One row per column per table.
Refreshes: With every data refresh, so freshness flags reflect the latest run.
Sample values: column_sample_min, column_sample_max, and categorical_value_distribution come from your own data. They are there so you can find the exact filter strings a column uses without guessing.

Example query

Which tables have data, and when did each last receive it:

SELECT
  dataset_name,
  table_name,
  table_type,
  ANY_VALUE(table_has_fresh_data_14d) AS has_fresh_data_14d,
  ANY_VALUE(table_last_data_date) AS last_data_date,
  COUNT(*) AS columns
FROM `your_project.sm_metadata.dim_data_dictionary`
GROUP BY 1, 2, 3
ORDER BY 1, 2

The values a categorical column actually holds, for example sm_channel on obt_orders:

SELECT dist.value, dist.pct
FROM `your_project.sm_metadata.dim_data_dictionary` AS dictionary,
  UNNEST(dictionary.categorical_value_distribution) AS dist
WHERE dictionary.table_name = 'obt_orders'
  AND dictionary.column_name = 'sm_channel'
ORDER BY dist.pct DESC

Columns

Column	Type	Description
`sm_store_id`	STRING	SourceMedium’s store identifier. Present when the row’s statistics are computed per store.
`dataset_name`	STRING	Dataset containing the table, for example `sm_transformed_v2`.
`table_name`	STRING	Table name, for example `obt_orders`.
`table_type`	STRING	Table shape: `Dimension`, `Fact`, `One Big Table`, or `Report`.
`dataset_location`	STRING	BigQuery location of the dataset.
`business_domain`	STRING	Business area the table serves, for example `Orders`, `Marketing`, `Customers`.
`refresh_frequency`	STRING	How often the table refreshes, for example `Daily`.
`primary_use_case`	STRING	Short label for what the table is best used for.
`table_description`	STRING	Plain-language description of the table, including its grain and key joins.
`table_description_updated_at`	TIMESTAMP	When the table description last changed.
`table_has_data`	BOOL	Whether the table has any rows for at least one store.
`table_has_fresh_data_14d`	BOOL	Whether the table received data in the last 14 days for at least one store.
`table_last_data_date`	DATE	Most recent date with data in the table.
`column_name`	STRING	Column name within the table.
`data_type`	STRING	BigQuery data type of the column, for example `STRING`, `FLOAT64`, `STRUCT<...>`.
`is_nullable`	BOOL	Whether the column can hold NULL.
`column_description`	STRING	Plain-language description of the column.
`column_description_updated_at`	TIMESTAMP	When the column description last changed.
`is_key_column`	BOOL	Whether the column is a join key such as `sm_order_key` or `sm_customer_key`.
`is_temporal_column`	BOOL	Whether the column is a date or timestamp.
`is_numeric_column`	BOOL	Whether the column is numeric.
`column_null_percentage`	FLOAT64	Percent of rows where the column is NULL, from 0 to 100.
`column_distinct_count`	INT64	Number of distinct values observed in the column.
`column_sample_min`	STRING	Smallest value observed, as text.
`column_sample_max`	STRING	Largest value observed, as text.
`categorical_value_distribution`	`ARRAY<STRUCT>`	Top values of a categorical column and their share of rows, as `value` and `pct`.