Migrate from Looker

How to rebuild a LookML project as a Malloy data model with the modeling agent and its LookML skill, how LookML constructs map to Malloy, and how to check the numbers against Looker, for Looker developers and analytics engineers

The modeling agent has a dedicated skill for LookML, malloy-lookml-review (opens in a new tab) (MIT-licensed, in Malloy Publisher). The agent loads it when it finds .lkml files and you confirm they should be used. Each phase of the skill is a reference file you can read before you run it.

Comparing the two products first? See Credible vs. Looker. For a worked migration, see The Looker Migration Guide.

What Credible Reads

The skill reads these files from a LookML project:

FileWhat the agent takes from it
manifest.lkmlConstants such as @{TABLE_NAME}, read first and resolved before anything else
*.model.lkmlThe Looker connection name and includes
*.view.lkmlTables, primary keys, dimensions, measures, derived tables, descriptions and visibility
*.explore.lkml and explores in model filesJoins, cardinality and explore-level field lists
*.dashboard.lookmlNothing. Dashboard files are skipped

It works in one of two modes:

  • LookML and live data: a connection to the warehouse the LookML reads is available. The LookML supplies the business logic and the agent checks each proposal against the data.
  • LookML only: no connection. The agent works from the LookML alone and marks every proposal as unvalidated.

Prerequisites

  • A Credible environment with a connection to the same warehouse Looker queries. Without it, you get a draft model but no validation.
  • The LookML project as files. Clone the project's Git repository, or download it from Looker.
  • A local modeling directory set up with cred init. See Cred CLI. The LookML skill is part of the modeling skills it installs. It is also available to the agent in the Credible App, where you can paste individual views into the chat.
  • Optional: Looker API credentials, if you want to compare results by running the original explores. See Prove Parity.

Steps

  1. Copy the LookML into the directory. Put the project in a subfolder such as lookml/, and add lookml/ to .credignore so cred publish leaves it out of the package.
  2. Choose the scope. Pick the dashboards and explores that matter. The Migrate to Credible describes how to trim to the fields in real use. Looker's System Activity shows which fields people actually query.
  3. Ask the agent to model from the LookML. For example: "Build a model of our orders domain using the LookML in lookml/ as prior art." The agent inventories the files, resolves manifest constants, lists source and join candidates, and reports what it flagged.
  4. Answer the flagged decisions. The agent asks about refinements, derived tables, synthetic primary keys, long SQL dimensions and access-restricted fields. See What the Agent Flags.
  5. Review the field proposals. The agent proposes dimensions and measures from each view, rewrites description: text into #(doc) tags, and maps visibility. Confirm the visibility mapping before it is applied.
  6. Check parity and coverage. Compare the key numbers against Looker, then review the coverage report, which lists every view and field as modeled, renamed, rearchitected, deferred or skipped, with a reason.
  7. Publish. See Publishing and Versions.

How LookML Maps to Malloy

LookMLMalloyNotes
view:source: over one tableOne base source per physical table
explore:source: with joinsJoins move from the explore into the source
sql_table_name:connection.table('schema.table')The Malloy connection name can differ from the Looker one
dimension:dimension:${TABLE}.column becomes a plain column reference
dimension_group: { type: time }A date or timestamp field, truncated with .day, .month, .yearNo timeframe list to maintain
type: yesnoA Boolean dimension
measure: { type: count }count()
measure: { type: count_distinct }count(field)Malloy's count(field) is a distinct count
type: sum / averagesum(field) / avg(field)
type: numberA measure built from other measuresUsually a ratio
measure: { filters: [...] }measure { where: condition }Filtered aggregate
primary_key: yesprimary_key: field
relationship: many_to_one / one_to_onejoin_one:
relationship: one_to_manyjoin_many:
relationship: many_to_manyjoin_cross:
CASE WHEN ... ENDpick ... when ... else
COALESCE(a, b) / IFNULL(a, b)a ?? b
description:#(doc) tag
value_format_name:Render tags such as # currency, # percent, # number
+view refinementsOne consolidated definition, or extendYou choose
Native derived table (explore_source:)A query-based source: (source -> { group_by: ... aggregate: ... }) extend { ... }
Persistent derived table (sql:)The base table, if it only existed for performance; otherwise flaggedSee below
hidden: yes# hidden tag, or nothingClassified by reason, not mapped to internal:
fields: exclusioninternal: (for tidiness) or private: (to keep data from users)The agent asks which
required_access_grants#(authorize) on the sourceFlagged for you, not converted automatically

What the Agent Skips

  • Looker interface constructs: link:, drill_fields:, html:, action:.
  • Liquid templating ({% %}, {{ }}). The intent is noted.
  • parameter: definitions. The intent is noted.
  • Persistent derived table settings: datagroup_trigger:, partition_keys:, increment_key:.
  • view_label:, group_item_label:, convert_tz:.
  • Dashboard files.

What the Agent Flags

These need a decision from you:

  • Derived tables. A persistent derived table that only selects from one table for speed is replaced by the base table. One that joins, uses CTEs or computes columns is a transformation: the agent recommends dimensions on the base table, or moving the transformation upstream to dbt or SQL. Native derived tables with bind_filters have no direct equivalent.
  • Synthetic primary keys. A key built with generate_uuid() or concat() hides the real grain. The agent queries the data to find it.
  • Long SQL dimensions. Fifty lines or more. The default is to simplify or push the logic upstream.
  • Refinements. Consolidate into one definition, or keep the layering with extend.
  • sql_always_where:. Recorded as context, not built into the model. If it restricts access, re-create it yourself as an #(access_filter) or #(authorize) rule.
  • Access-restricted fields. required_access_grants keys on the viewer's user attributes. A Malloy model has no viewer context of its own, so the equivalent is an #(authorize) rule on the source driven by Credible's secure givens. The agent proposes it and you decide.
  • access_filter. Not converted by the skill. Re-create it as an #(access_filter) row filter on the column it filtered.

Prove Parity

The skill uses two ways to compare results:

  1. SQL against the same warehouse. Run the equivalent SQL directly against the warehouse the LookML reads, and compare it with the Malloy result. Both sides read the same data, so a difference is a difference in logic. This is the method that works in practice, with or without Looker API access.
  2. The Looker API. Run the original explore through the API and compare. This only works if the API service account satisfies the explore's required_access_grants. The agent checks this before it builds anything on this path.

Troubleshooting

ProblemCauseFix
A restricted explore looks like it doesn't exist through the Looker API (404)The service account does not satisfy the explore's required_access_grants, usually because a user attribute the grant depends on is empty. Looker answers as if the explore didn't existSet the attribute for the service account (this needs a Looker admin), or validate with SQL against the warehouse instead
Table references don't resolveThe view uses manifest constants such as @{BILLING_TABLE}Include manifest.lkml in the folder so the agent can resolve them
Queries fail on the connection nameThe connection: in a Looker model file is Looker's name for the database, not Credible'sThe agent uses the connection name from your Credible environment; correct any source that still points at the Looker name

Example

A LookML view and explore:

view: orders {
  sql_table_name: sales.orders ;;

  dimension: order_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.order_id ;;
  }

  dimension: status {
    description: "Current fulfillment status of the order"
    type: string
    sql: ${TABLE}.order_status ;;
  }

  dimension_group: created {
    type: time
    timeframes: [date, week, month, year]
    sql: ${TABLE}.created_at ;;
  }

  measure: order_count {
    type: count
    drill_fields: [order_id, status, created_date]
  }

  measure: total_revenue {
    description: "Total revenue in USD"
    type: sum
    sql: ${TABLE}.amount ;;
    value_format_name: usd
  }

  measure: cancelled_orders {
    type: count
    filters: [status: "cancelled"]
  }
}

explore: orders {
  join: customers {
    sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
    relationship: many_to_one
  }
}

The same logic in Malloy:

source: orders is warehouse.table('sales.orders') extend {
  primary_key: order_id
  join_one: customers is warehouse.table('sales.customers')
    on customer_id = customers.customer_id

  dimension:
    #(doc) Current fulfillment status of the order
    status is order_status

  measure:
    #(doc) Number of orders
    order_count is count()

    #(doc) Total revenue in USD
    # currency
    total_revenue is sum(amount)

    #(doc) Orders that were cancelled
    cancelled_orders is count() { where: status = 'cancelled' }
}

The time dimension group becomes the created_at column, which a query truncates with created_at.month or created_at.year. drill_fields is skipped and reported as Looker interface detail.

Next Steps

On this page