lookml-field-curation

LookML field curation (lookml-field-curation)

Scope and Medallion refinement boundary

Base Bronze views (bronze/:table.view.lkml) are autogenerated 1:1 from database tables by generate-view-from-table with generate_descriptions=True and add_autogenerated_header=True (# AUTOGENERATED). That generation step pulls existing database INFORMATION_SCHEMA column comments directly into bronze/:table.view.lkml. Never hand-edit bronze/:table.view.lkml.

Run lookml-field-curation only on demand when asked to curate, refine, or enrich LookML field descriptions, field visibility (hidden:), or filter autocomplete suggestions (suggestable:) for self-service BI exploration or Conversational Analytics and Gemini in Looker semantic modeling. Do not run this skill automatically as part of agentic-lookml-workflow.

Place single-table field curations (hidden:, suggestable:, description:, primary_key: yes, and single-table calculated dimensions and measures) in bronze/:table.refinement.lkml (or bronze/:schema/:table.refinement.lkml when schema collision routing applies), starting with include: ":table.view.lkml" on line 1 and view: +:table { ... }. Place cross-view calculated dimensions and measures in silver/:view_a_:view_b_fields.view.lkml or Silver and Gold derived views.

Why hidden and suggestable matter

Setting hidden: yes removes a field from the Explore field picker and excludes it from the Explore schema fetched by Conversational Analytics and Gemini in Looker at query time. The field remains available for LookML ${field_name} substitution, sql_on: joins, measure definitions, and derived tables. Hiding surrogate keys, foreign keys, intermediate calculation helpers, raw JSON payloads, and ETL audit columns cuts LLM context noise and stops users or agents from grouping by internal keys. Because hidden: yes only controls presentation and schema context, use required_access_grants when a column requires access control.

By default (suggestable: yes), opening a filter dropdown on a type: string dimension or running Conversational Analytics value sampling triggers an autocomplete query against the warehouse:

SELECT DISTINCT <field_name>
FROM <table>
WHERE (<field_name> LIKE '%' OR <field_name> LIKE '% %')
GROUP BY 1
ORDER BY 1
LIMIT 1000

On high-cardinality strings, free-text notes, URLs, or unparsed JSON columns, this SELECT DISTINCT ... LIMIT 1000 scan wastes warehouse compute and returns an arbitrary 1,000-row slice. Setting suggestable: no disables the SELECT DISTINCT autocomplete query while keeping visible dimensions (hidden: no) groupable and filterable by exact value or pattern.

Decision matrix for hidden and suggestable

Field category	Detection signal / profiling heuristic	hidden:	suggestable:	Rationale
Surrogate or technical primary and foreign keys	primary_key: yes, composite dimension: pk, or join-only foreign keys (id, *_id, *_sk, *_pk, *_fk) where human-readable entity attributes exist	yes	no (if type: string)	Required for sql_on: joins and symmetric aggregates, but clutters the Explore picker and LLM schema context.
Raw JSON or semi-structured payload blobs	Columns storing unparsed JSON strings or JSON objects (*_json, payload, raw_event, or values starting with {...} / [...])	yes	no	Prevents SELECT DISTINCT json_col LIMIT 1000 full-column scans. Expose extracted scalar paths via bronze/:table__:col.view.lkml instead.
Intermediate helper dimensions	Fields referenced only in downstream ${...} LookML expressions (_*, helper_*, tmp_*, raw_*, or compound boolean flags for measure filters:)	yes	no (if type: string)	Internal modeling scaffolding; exposing helpers causes users and agents to pick intermediate flags instead of canonical fields.
System, audit, and ETL metadata columns	Ingestion and replication columns (_fivetran_*, _airbyte_*, _sdc_*, _baton_*, etl_*, ingest_*)	yes	no (if type: string)	Pipeline bookkeeping irrelevant to business reporting.
High-cardinality hashes and opaque UUIDs	Columns matching uuid, guid, hash, md5, sha256, record_hash, or $\ge 80%$ of sample values matching UUID or 32 to 64 char hex format	yes	no	Zero analytical grouping value and expensive to scan in filter suggestions.
Empty or all-NULL columns and deprecated duplicates	Profiling returns distinct_count == 0 (only NULL or ""), or duplicate foreign key already exposed on the joined parent view	yes	no	Removes dead or ambiguous duplicate fields from the Explore.
Low-cardinality business enums and statuses	String dimensions with $1 \le \text{distinct_count} \le 15$ and max_length <= 120 (status, payment_method, plan_tier)	no	yes	Core slicing and filtering attributes. Enumerate all valid values directly in description:.
Medium-cardinality categorical strings	String dimensions with $16 \le \text{distinct_count} \le 100$ and max_length <= 120 (country, department, category)	no	yes	Core grouping dimensions. List the top 10 values by frequency and total distinct count in description:.
Visible high-cardinality identifiers and free-text strings	Human-searchable strings with $\text{distinct_count} > 100$, max_length > 120, avg_length > 60, or free-text names (*notes, *comment, *message, *url, email, sku)	no	no	Keep visible for detail tables and exact or substring filtering, but disable SELECT DISTINCT autocomplete scans.
Partition and clustering dimensions	Physical date or timestamp partition dimension_group or clustered dimensions in INFORMATION_SCHEMA	no	yes (no if high-card ID)	Must remain visible so users and agents apply partition and cluster filters for scan pruning.
Canonical business measures and KPIs	Aggregations (sum, count, count_distinct, average, number ratios)	no	(N/A for measures)	Primary analytical metrics; always document math, grain, and filter exclusions in description:.

String profiling tiers

The profile_string_fields.py script classifies every type: string dimension in priority order and preserves any existing INFORMATION_SCHEMA description as an optional leading sentence (<Base>. ) without echoing the field name when no prior description exists:

Priority	classification value	Trigger condition	recommended_hidden	recommended_suggestable	recommended_description format
1	json_payload	Name matches (^|_)(json|payload|raw_event|proto|xml)($|_), JSON_ in sql:, or $\ge 50%$ of sample values parse as JSON {...} / [...]	True (yes)	False (no)	"Raw JSON payload; hidden and filter suggestions disabled to prevent full-column scans."
2	surrogate_or_system_key	primary_key: yes, is_helper, name matches ^(_fivetran_|_airbyte_|_sdc_|_baton_|etl_|ingest_)|(_pk|_fk|_sk)$, or (id / *_id with distinct_count > categorical_threshold)	True (yes)	False (no)	"Internal key or system metadata column not intended for end-user grouping."
3	uuid_or_hash	Name matches (^|_)(uuid|guid|hash|md5|sha256|row_hash|record_hash)($|_) or $\ge 80%$ of sample values match UUID or 32 to 64 char hex regex	True (yes)	False (no)	"High-cardinality UUID or hash identifier."
4	empty_or_null_only	distinct_count == 0 (all sampled values are NULL or empty string)	True (yes)	False (no)	"Column contains only NULL or empty values in sampled data."
5	low_cardinality_enum	Not free-text and 1 <= distinct_count <= enum_threshold (default 15)	False (no)	True (yes)	"Values: '<v1>', '<v2>', '<v3>'." (prefixed with <Base>. if an INFORMATION_SCHEMA comment exists)
6	medium_cardinality_categorical	Not free-text and enum_threshold < distinct_count <= categorical_threshold (default 100)	False (no)	True (yes)	"Top values include: '<v1>', ..., '<v10>' (<distinct_count> distinct values)."
7	high_cardinality_or_free_text	distinct_count > categorical_threshold, max_length > 120, avg_length > 60.0, or free-text suffix (comment, notes, message, body, url, uri, path, etc.)	False (no)	False (no)	"High-cardinality or free-text string (<distinct_count>+ distinct values, max length <max_length>); filter suggestions disabled. Example values: '<v1>', '<v2>', '<v3>'."

Field description templates

Keep curated descriptions to 1 or 2 user-friendly sentences that retain any accurate domain definition from INFORMATION_SCHEMA while adding concrete operational metadata:

• Never include raw LookML field identifiers (order_items.sale_price, created_date, _raw_amount), table aliases, or raw SQL syntax (COUNT(DISTINCT ...), NULLIF(...)) in description:. Always use human-readable field labels (Sale Price, Cost, Created Date, Completed Revenue, Order Count), and do not repeat the field's own name at the start of its description.
• When referencing other fields in a description, either insert their resolved short labels (label_short from lkr parse lookml-to-api and inspect_local_lookml.py, such as Sale Price - Cost) or use Looker Liquid label variables ({{ order_items.sale_price._label }} - {{ inventory_items.cost._label }} across views, or {{ sale_price._label }} within the same view). Looker evaluates ._label when rendering the Explore field picker metadata (lookml_model_explore) without adding the referenced field or join to the SQL query. Note that ._label returns the full Explore label (<View Label> <Field Short Label>, such as Order Items Sale Price - Inventory Items Cost).

String dimensions

# File: bronze/orders.refinement.lkml
include: "orders.view.lkml"

view: +orders {
  # Low-cardinality enum (<= 15 distinct values): list all valid literals with exact casing
  dimension: status {
    hidden: no
    suggestable: yes
    description: "Order fulfillment status. Values: 'Pending', 'Processing', 'Shipped', 'Cancelled'."
  }

  # Medium-cardinality categorical (16..100 distinct values): list top-N most frequent values + total distinct count
  dimension: category {
    hidden: no
    suggestable: yes
    description: "Merchandise category. Top values include: 'Apparel', 'Footwear', 'Accessories', 'Outerwear', 'Home Goods' (25 distinct values)."
  }

  # High-cardinality or free-text string: keep visible (hidden: no) but disable SELECT DISTINCT scans (suggestable: no)
  dimension: customer_notes {
    hidden: no
    suggestable: no
    description: "High-cardinality or free-text string (140+ distinct values, max length 142); filter suggestions disabled."
  }

  # Raw JSON payload: hide and disable suggestions
  dimension: metadata_json {
    hidden: yes
    suggestable: no
    description: "Raw JSON payload; hidden and filter suggestions disabled to prevent full-column scans."
  }
}

Partitioning and clustering guidance

When a table is partitioned or clustered in INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.PARTITIONS, add the # PARTITION_COLUMN: ... | MIN_DATE: ... | CLUSTERED_BY: ... header comment in bronze/:table.refinement.lkml, document the minimum partition date and "Do not use 'is any value'" warning on the partition dimension_group, and document the clustering order and block-pruning hint (using the partition field's human-readable label, such as Created Date) on each clustering dimension:

# File: bronze/orders.refinement.lkml
# PARTITION_COLUMN: created_at (TIMESTAMP) | MIN_DATE: 2023-01-15 | CLUSTERED_BY: user_id, country
include: "orders.view.lkml"

view: +orders {
  dimension_group: created {
    description: "Physical partition column (required). Min date available: 2023-01-15. Do not use 'is any value'."
  }

  dimension: user_id {
    suggestable: no
    description: "Clustering column (order 1). Filtering alongside Created Date enables block pruning."
  }

  dimension: country {
    description: "Clustering column (order 2). Filtering alongside Created Date enables block pruning."
  }
}

Pair partitioned tables with an abstract base explore in bronze/explores/:table_base.explore.lkml enforcing always_filter: on the partition _date timeframe:

# File: bronze/explores/orders_base.explore.lkml
explore: orders_base {
  extension: required
  view_name: orders

  always_filter: {
    filters: [orders.created_date: "7 days"]
  }
}

Measures and calculated dimensions

Specify the business formula, entity grain, and included or excluded filter conditions using human-readable field labels so analysts and Conversational Analytics agents can distinguish similar KPIs:

# File: bronze/orders.refinement.lkml
include: "orders.view.lkml"

view: +orders {
  dimension: order_id {
    primary_key: yes
    hidden: yes
    suggestable: no
    description: "Internal key or system metadata column not intended for end-user grouping."
  }

  dimension: _raw_amount {
    hidden: yes
    sql: ${TABLE}.amount ;;
    description: "Internal helper dimension referenced by Net Amount."
  }

  dimension: net_amount {
    type: number
    sql: ${_raw_amount} * 0.9 ;;
    value_format_name: usd
    description: "Order line net amount after 10% standard discount."
  }

  measure: order_count {
    type: count_distinct
    sql: ${order_id} ;;
    value_format_name: decimal_0
    description: "Count of distinct orders across all statuses."
  }

  measure: completed_revenue {
    type: sum
    sql: ${net_amount} ;;
    filters: [status: "Complete"]
    value_format_name: usd
    description: "Total Net Amount at order grain for completed orders only (Status is 'Complete'; excludes 'Pending', 'Cancelled', and 'Returned')."
  }

  measure: average_order_value {
    type: number
    sql: 1.0 * ${completed_revenue} / NULLIF(${order_count}, 0) ;;
    value_format_name: usd
    description: "Completed Revenue divided by Order Count."
  }
}

For cross-view calculations in silver/:view_a_:view_b_fields.view.lkml, reference the human-readable field labels directly (Sale Price - Cost) or via Liquid ._label ({{ order_items.sale_price._label }} - {{ inventory_items.cost._label }}):

# File: silver/inventory_items_order_items_fields.view.lkml
view: inventory_items_order_items_fields {
  dimension: gross_margin {
    type: number
    sql: ${order_items.sale_price} - ${inventory_items.cost} ;;
    value_format_name: usd
    description: "Line-item gross margin before tax and shipping (Sale Price - Cost)."
  }

  measure: total_gross_margin {
    type: sum
    sql: ${gross_margin} ;;
    value_format_name: usd
    description: "Total Gross Margin across joined order items and inventory items (Sale Price - Cost)."
  }
}

Curation workflow and scripts

Inspect local LookML metadata

Use lkr parse lookml-to-api paired with inspect_local_lookml.py to extract model, explore, view, and field metadata (all_lookml_models and lookml_model_explores schema shapes, existing description, hidden, suggestable, type, primary_key, dimension_group, sql, static_curation_candidates, and pre-built sandbox_vars) offline without remote Looker metadata API calls:

# Option A: Pipe `uv run lkr parse lookml-to-api` into `inspect_local_lookml.py`
uv run lkr parse lookml-to-api --path ./lookml_project --model ecommerce --explore orders \
  | uv run python skills/lookml-field-curation/scripts/inspect_local_lookml.py \
      --api-json - --view orders --output /tmp/curation_meta.json

# Option B: Inspect a LookML directory or standalone .view.lkml file directly
# (automatically synthesizes ephemeral explores for standalone views not yet in an explore)
uv run python skills/lookml-field-curation/scripts/inspect_local_lookml.py \
  --path ./lookml_project --view orders --output /tmp/curation_meta.json

Profile string fields in code-mode

Feed the extracted fields_json into profile_string_fields.py inside lkr code-mode sandbox. The script returns structured JSON containing per-field metrics (distinct_count, null_or_empty_count, has_null_or_empty, max_length, avg_length, sample_values), deterministic classification, recommended_hidden, recommended_suggestable, recommended_description, lookml_snippet, and a combined refinement_lookml block.

# Mode 1: Explore mode (queries via run_inline_query using local LookML fields_json)
FIELDS_JSON=$(jq -r '.explores["ecommerce::orders"].sandbox_vars.profile_string_fields.fields_json' /tmp/curation_meta.json)
uv run lkr code-mode sandbox --dev-mode \
  --file=skills/lookml-field-curation/scripts/profile_string_fields.py \
  --var model_name="ecommerce" \
  --var explore_name="orders" \
  --var view_name="orders" \
  --var query_mode="explore" \
  --var fields_json="$FIELDS_JSON"

# Mode 2: SQL Runner mode (queries physical table directly via create_sql_query + run_sql_query)
uv run lkr code-mode sandbox --dev-mode \
  --file=skills/lookml-field-curation/scripts/profile_string_fields.py \
  --var connection_name="bigquery_prod" \
  --var view_name="orders" \
  --var table_name="\`analytics-prod.ecomm.orders\`" \
  --var query_mode="sql" \
  --var fields_json="$FIELDS_JSON"

Optional --var overrides for profile_string_fields.py:

• field_names accepts a comma-separated list of fields (such as "orders.status,orders.category") when running without fields_json.
• enum_threshold sets the maximum distinct values for low_cardinality_enum (default "15").
• categorical_threshold sets the maximum distinct values for medium_cardinality_categorical (default "100").
• top_n sets the number of top values by frequency to list for categorical fields (default "10").
• query_limit sets the row or group limit per profiling query (default "500").
• filters_json passes an optional JSON object of Explore filters for query_mode="explore" (such as '{"orders.created_date": "30 days"}').

Enrich partitioning and clustering metadata

Query INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.PARTITIONS via enrich_partition_clustering.py to detect the physical partition column, minimum partition date (min_partition_date), and ordered clustering columns, mapping them to their LookML dimension_group or dimension:

ENRICH_FIELDS_JSON=$(jq -r '.explores["ecommerce::orders"].sandbox_vars.enrich_partition_clustering.fields_json' /tmp/curation_meta.json)
uv run lkr code-mode sandbox --dev-mode \
  --file=skills/lookml-field-curation/scripts/enrich_partition_clustering.py \
  --var model_name="ecommerce" \
  --var connection_name="bigquery_prod" \
  --var project_id="analytics-prod" \
  --var dataset_name="ecomm" \
  --var table_name="orders" \
  --var view_name="orders" \
  --var fields_json="$ENRICH_FIELDS_JSON"

Verify scripts locally

Run verify_curation_scripts.py to validate Monty sandbox compliance, lkr parse lookml-to-api extraction, and both sandbox scripts:

uv run python skills/lookml-field-curation/scripts/verify_curation_scripts.py
uv run pytest skills/lookml-field-curation/scripts/verify_curation_scripts.py --no-cov