Looker Business Analyst: Data Modeling and LookML

What the domain tests

Data Modeling and LookML tests whether you can build a trustworthy semantic model: views, dimensions, measures, joins, and derived tables. The exam focuses on why a measure returns the right number when joins multiply rows, how relationship types affect aggregation, and how persistence and access controls are expressed in LookML.

Core rules

• A view's sql_table_name or derived table defines its source. Dimensions are row-level attributes; measures aggregate. Measures can use type: sum, count, average, and so on, and should reference ${TABLE} or other fields deliberately.
• Every view used in a fanout join needs a primary_key for symmetric aggregates to work. Looker uses the primary key to generate a subquery that deduplicates before aggregating. If no reliable key exists, sql_distinct_key can supply one.
• Join relationship (one_to_one, one_to_many, many_to_one, many_to_many) tells Looker how rows multiply. sql_on defines the join condition; foreign_key is a convenience for simple keys.
• Persistent derived tables (PDTs) are LookML-defined tables materialized in the database. datagroup and persist_for control rebuild triggers and staleness.
• access_filter and sql_always_where restrict rows; extends and refinements let you reuse or adjust LookML without copying it.

Worked example

An orders view has one row per order; order_items has many rows per order. The model joins them one_to_many and defines total_revenue as type: sum on order_items.sale_price. Without a primary key on order_items, a query that also joins payments can multiply item rows and overstate revenue. The fix is to set primary_key: order_item_id on order_items and ensure the measure uses the supported symmetric aggregate pattern. Looker then aggregates at the correct grain, even when the user adds another joined view. The reasoning is grain: joins change row counts; symmetric aggregates restore the intended grain using the declared key.

Traps

Missing primary keys are the classic cause of inflated sums. A wrong join relationship can silently duplicate rows even when SQL runs. sql_always_where is not a substitute for user-attribute-based access_filter. PDTs do not refresh on every query unless configured; stale persistence can mislead. Refinements and extends can override fields, so check the final resolved model rather than the original file.

Key points

• Dimensions describe rows; measures aggregate and need the correct grain.
• Symmetric aggregates depend on a primary key or distinct key.
• Join relationship types control row multiplication and must match the data.
• PDTs materialize in the database and are governed by persistence settings.
• Access filters enforce row-level security from user attributes.