dim_data_dictionary

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

Grain: One row per store, dataset, table and column for store-scoped observations. Preserve global or missing store scope when present.
Refreshes: The dictionary publication can contain both newly measured and carried observations. Publication time does not establish business-data freshness or completeness.
Sample values: Column ranges and distributions can contain sensitive source values. Do not expose them through a generic metadata browser, telemetry or automatic agent prompts.

Example query

Which tables have published observations, summarized per table. This describes the row’s store scope, not the health of any one report; ANY_VALUE assumes the repeated table-level values agree, so check for conflicts first:

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	Presence observation for the row’s actual store scope; older/global publications can have a wider scope.
`table_has_fresh_data_14d`	BOOL	Coarse observed-date recency flag. Not a load-success timestamp, SLA result or completeness watermark.
`table_last_data_date`	DATE	Observed recency date, or table modification date for configured tables without a recency field; may be carried from a prior assessment.
`value_format`	STRING	Optional published display unit on compatible numeric columns: currency, percent, ratio, count, number or identifier. Older publications can omit it.
`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`.

Scope and interpretation

Always include both dataset and table in field identity. A field marked is_key_column does not establish a safe join, relationship cardinality, or an authorization boundary. Do not derive a join graph from matching names. Repeated table flags describe one observation and must not be added across columns. If repeated values disagree, report inconsistent metadata. No row for a selected store means evidence is unavailable; it does not mean a global flag applies to that store. _synced_at, where exposed, is the dictionary publication time. It does not date every sampled statistic. The currently published flags do not supply a selected-period completeness watermark or verified ingestion, transformation and delivery status. See Data Health for the separate evidence dimensions. value_format is a display hint, not a currency code or a conversion rule. Capability-check older publications and retain reviewed fallback formatting. Permission failures must be reported as denied rather than treated as an older schema. Keep exact decimals and fractional attributed conversions in calculations and exports.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

Example query

Columns

Scope and interpretation

​Example query

​Columns

​Scope and interpretation

Example query

Columns

Scope and interpretation