Prepare and test your skills
Prepare and test your skills
Free sample question, answered and explained below. The Associate Data Practitioner practice bank has 1,156 more like it. Try 10 free: instant score, no account.
A data team is updating an existing LookML view file (orders.view) to support new reporting requirements. The view already defines a base dimension for customer IDs:
dimension: user_id {
type: number
sql: ${TABLE}.user_id ;;
}
The business requires a new aggregate field that calculates the distinct count of customers who placed orders within the last 30 days. Following LookML modeling best practices for maintainability, how should this field be declared in the view file?
dimension_group with type: time and define timeframes: [distinct_count] pointing directly to ${TABLE}.user_id ;;.A dimension_group is a LookML parameter used to create a set of time-based or duration-based dimensions simultaneously from a single timestamp, date, or interval column.
distinct_count is not a valid timeframe option for a dimension group. Valid timeframes include periods such as raw, date, week, month, quarter, and year.The requirement was to create a set of standard date intervals (e.g., day, week, month, year) from an order_created_at timestamp column in the database.
measure with type: count_distinct, reference the existing dimension using sql: ${user_id} ;;, and apply the date condition using the filters parameter.In LookML, a measure is a field that computes an aggregate calculation across multiple rows of data, such as counts, sums, averages, or distinct counts. A dimension represents a row-level attribute or fact. When building data models, LookML uses the substitution operator (${...}) to reference database columns or other LookML fields in a modular, maintainable hierarchy.
type: count_distinct directs Looker to generate the appropriate SQL aggregation (such as COUNT(DISTINCT ...)) across rows.filters parameter applies a conditional filter directly to the calculation (e.g., filters: [created_date: "30 days"]), ensuring only qualifying rows are aggregated.${user_id} instead of ${TABLE}.user_id ensures that if the underlying database column name ever changes in the future, developers only need to update the base dimension's sql parameter in one location.
${field_name} creates a single reference point for the physical column in the data model.type: count_distinct into dialect-specific SQL functions without manual SQL string manipulation.Declaring an aggregate metric as a measure and referencing the base dimension with ${field_name} follows core LookML modeling standards. It separates row-level dimensions from aggregated metrics while keeping the code maintainable and DRY (Don't Repeat Yourself).
measure with type: sum, set sql: ${TABLE}.user_id ;;, and omit filters so all rows are calculated.sum Measure Type IsA measure with type: sum generates a SQL SUM() aggregation. It adds up all numerical values in a specified column across the selected rows.
SUM() yields an arithmetic total of ID numbers, which is meaningless for counting unique customers. The requirement explicitly asks for the distinct count of customers (count_distinct).filters parameter calculates values across all historical records rather than restricting the metric to orders placed within the last 30 days.${TABLE}.user_id creates multiple direct dependencies on the raw database schema instead of referencing the existing ${user_id} dimension.type: sum is designed for numeric quantities (e.g., revenue, quantity sold, items in stock).The requirement asked for calculating total order revenue across all time using a numerical column like sale_price (e.g., measure: total_revenue { type: sum sql: ${sale_price} ;; }).
dimension with type: number, write sql: COUNT(DISTINCT ${TABLE}.user_id) ;;, and specify the timeframe inside the SQL definition.A dimension in LookML represents an unaggregated, row-level attribute or fact in a table (such as a category, ID, timestamp, or string). Dimensions form the GROUP BY and WHERE clauses in generated SQL queries.
COUNT(DISTINCT ...) inside a dimension violates LookML modeling principles. Dimensions are evaluated row-by-row and grouped in SQL; placing aggregates in dimensions leads to SQL syntax errors (such as nested grouping or invalid aggregate placement in the GROUP BY clause).${TABLE}.user_id duplicates references to the physical database column rather than referencing the already established ${user_id} LookML dimension.filters subparameter to scope aggregations by timeframes.SUM, COUNT, and AVG.The requirement called for transforming a row-level identifier or creating a non-aggregated attribute, such as concatenating customer strings or extracting a substring from a single record.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated