Looker Interview Questions And Answers

1. How do I reference a derived table within the SQL of another derived table?

You can use ${view_name.SQL_TABLE_NAME} in the SQL of a derived table to reference the PDT defined in view_name.

2. What is the “Rebuild Derived Tables and Run” button?

The “Rebuild Derived Tables and Run” button will initiate a rebuild of all persistent derived tables involved in the query. It will also initiate a rebuild of all upstream PDTs.

The “Rebuild Derived Tables and Run” button will appear in the cog of an Explore or look if:

• The user has the “develop” permission (as of 4.18)
• The query uses a PDT that has already been built.
• The query has finished running.

Note: The option will not appear in an Embedded context (with /embed/ in the URL) – public or private, regardless of permissions.

Does it affect other users?

Sort of. Only the user that clicked the button will have to wait for the rebuild to finish. All other users use the existing table. However, all users will use the new version of the table once it is complete. Other users may also be impacted by the database strain that the rebuild may cause.

If you hit this button while in dev mode, will it affect the production version? 
Yes, if the PDT SQL is identical. If the PDT in your dev mode is different, then the production version will be completely unaffected.

3. What are Native Derived Tables (NDTs)?

Native derived tables are a feature released in Looker 4.18 as a labs feature, which came out of labs in Looker 5.0. They allow you to write derived tables “natively” in LookML.

4. Why is a native derived table giving the error “could not find an explore name ‘explore_name'” in the declaration ‘explore_source’ line?

One common fix for this error is to add the line include: “*.model” in the view file.

5. Can I use templated filters in PDTs?

​In general, using templated filters in PDTs (or any liquid, like parameters, really) is NOT a good idea. The dynamic filters will cause the table to rebuild every time the filter changes, and it causes a huge amount of strain on your database. Stick with an ephemeral derived table, since the table must build from scratch anyway every time the filter value is changed.

6. If I have a templated filter in a derived table and I reference that table in another derived table, can I pass the filter value through?

No, you would have to create the templated filter in your new derived table. The DT doesn’t “store” the templated filter, it’s part of the SQL.

7. Can I persist a derived table with a datagroup that’s *not* included in the model the derived table is used in?

No, you cannot. If a derived table is persisted by datagroup_trigger, that datagroup needs to be defined in every model that derived table is used in. If a datagroup isn’t defined in a model that a derived table is used in, you’ll get the following error:
invalid datagroup_trigger {datagroup_name} declared for {derived_table_name}

8. Does Looker require access to the scratch schema to write ephemeral derived tables?

For most dialects, the answer is no – you do not need to set up a scratch schema.

For the MySQL family (Clustrix, MemSQL, MariaDB) you do need to do some additional setup to allow ephemeral derived tables. According to docs, you can grant the proper permissions to a schema that doesn’t necessarily exist.

GRANT 
  SELECT, 
  INDEX, 
  INSERT,   
  DROP,   
  CREATE TEMPORARY TABLES
ON looker_tmp.*; 
Note that the looker_tmp schema does not need to actually exist, even though these permission grants are still needed.

9. Why are there multiple copies of the same PDT in my database?

Changing the SQL of a derived table in developer mode and querying it builds a new copy of the table. This is only relevant when you change the SQL of the PDT, or the SQL in your dev mode is different than in production mode. Dev mode versions of tables are built when that PDT is queried, and last for 24 hours. This is equivalent to the dev mode table using persist_for: 24 hours.

10. How do I use CASE WHEN in table calculations?

You achieve CASE WHEN logic with nested if() statements. If we want to bucket our values into ranges (less than one, one to two, two to three, three to four, four to five, more than five), we can use multiple if() statements to sort the data. 

An example follows:coalesce( 
if(${field} < 1, “<1”, null), 
if(${field} < 2, “1 -2”, null), 
if(${field} < 3, “2- 3”, null), 
if(${field} < 4, “3 – 4”, null), 
if(${field} < 5, “4 – 5”, null), “>5”)

11. How do I create a running function down a column or across a pivot row?

You can do this using a custom table calculation formula. 
Down a column: offset_list( 
  ${view_name.field_name}, 
  -1*row()+1, 
  row() 
) 

Across a pivot row:
pivot_offset_list( 
  ${view_name.field_name}, 
  -1*pivot_column()+1, 
  pivot_column() 
) Wrap your aggregation function around one of these formulas, and you’re good to go.

12. Does the sort order matter when using an offset list function?

The sort order is very important. In this function, you’re telling the table calculation to either go up the table or down the table. Say you’re looking at yearly data and have created a calculation to show the previous year’s earnings for each row. If you then changed the sort order, the table calculation would show the next year’s earnings instead, displaying the wrong information.

13. Why would I want to use a $ symbol in a table calculation?

The $ is Looker syntax that we use when referencing other columns. Typing in the $ sign is a short way of bringing up a list of all available fields for a given table calculation.

14. Is it better to break calculations into steps and reference table calculations within one another, or to create one longer table calculation?

We recommend using one long table calculation. This minimizes the chances that a given table calculation will break due to other calculations it references being deleted or changed, and it may also improve performance by minimizing the number of columns rendering in your browser.

However, the best process to construct that one table calculation is to break it into steps that reference one another. This allows you to check your work as you construct each part by making sure you get the results you expect for each step of the process. Once everything works as expected, combine these steps into a single calculation.

15. Can I base a table calculation on a particular pivoted column?

You can use the pivot_where function to have a table calculation based on a particular, specified column. 

Say you have a table like this, and you want to calculate the percentage of orders canceled per month.

You can use pivot_where to specify that the calculation should target the cancelled column. 

pivot_where(${orders.status} = “cancelled”, ${orders.count})

Then, to calculate the percent of orders cancelled per month, we can divide by the summed row value.

pivot_where(${orders.status} = “cancelled”, ${orders.count}) / sum(pivot_row(${orders.count}))

16. Can I filter data using table calculations?

Yes, we can use yes or no logic to filter using a table calculation.

17. Why isn’t my conditional formatting rule applying to my percent values in table visuals/table calculations?

This can happen if you are applying a Default Format option or a value_format OR value_format_name on your Table Calculation. For example:

${products.count}/sum(${products.count})

This will typically produce a value on the scale of 0.0 to 1.0, where 0.75 represents 75%. In the UI you can apply a format option from the format dropdown like “Percent( 1 ) — 123,456.7%” to always show 75%. However, any conditional format rules still need to apply to values in the 0.0 to 1.0 range.

18. Why don’t my table calculations have totals?

Prior to Looker 6.2, this is expected behavior. The table calculations columns will not have totals since table calculations are performed after the SQL query finishes.

If you would like to total a table calculation, we recommend creating a second table calculation that sums up the original one. This will give you a second column that has the total of the first in every row. sum(${table_calc_1})

Starting in Looker 6.2, numeric table calculations will have totals included automatically.

19. What data is included in table calculations?

Because table calculations run on the front end after the query has returned, they operate on only data that is in the Explore table.

20. Does finding the mean of the average consider the populations of the initial average or is it the mean of two averages?

When you are finding the mean using a table calculation, the mean will be of the values shown in the data results. So if the measure is an average, then the mean will of the calculated averages shown in the table without taking the initial populations into consideration.

21. If I added a filter to restrict the data to a certain timeframe (say the past five years), would the table calculation get messed up?

Table calculations will always just use the data present in the results set, so if you add a filter to view only the 5 most recent years, then the table calculation output would also be specific to just the past 5 years. If you are using the max row number to get to the bottom of the results set, then everything would still work regardless of filtering since that max row will be calculated dynamically.

22. How is Looker different from other BI tools?

Looker’s main differentiator is its modeling layer, which provides a semantic abstraction over the underlying data sources. This allows users to write SQL-like queries using business logic concepts rather than database schema knowledge. Looker also provides a robust set of tools for creating and sharing data visualizations, as well as a collaborative environment for data analysis.

23. How does Looker handle security and data privacy?

Looker has a number of security features, including user authentication and authorization, data encryption, and activity logging. It also provides granular access controls and allows for integration with external authentication providers like SAML and LDAP.

24. What are Looker Explores?

Looker Explores are interactive interfaces that allow users to explore and query data in their data sources. They provide a SQL-like language called LookML that allows users to create complex queries using business logic concepts rather than database schema knowledge.

25. How do you create a Looker dashboard?

To create a dashboard in Looker, you first need to define the visualizations that you want to include.

You can do this using Looker’s Explore feature, which allows you to query and filter data from your data sources.

Once you have the visualizations that you want, you can add them to a dashboard and customize the layout and formatting as desired.

26. How to optimize Looker performance?

There are several ways to optimize Looker performance, including optimizing data models for query performance, using caching and materialized views to speed up queries, and using Looker’s concurrency and workload management features to prioritize and manage query loads.

27. What are Looker extensions?

Looker extensions are add-ons that allow users to extend Looker’s functionality with custom code. They can be used to add custom visualizations, integrate with external systems, or automate workflows within Looker.

28. How do you integrate Looker with other tools?

Looker provides a number of integration options, including APIs for data ingestion and extraction, pre-built connectors for popular data sources like Salesforce and Google Analytics, and a custom extension framework for integrating with external systems.

29. What is a Looker block?

A Looker block is a pre-built set of code and configurations that provides a specific set of functionality within Looker. Blocks can be used to add custom visualizations, data models, or other features to Looker without requiring users to write code from scratch.

30. How can you create a Looker block?

To create a Looker block, you need to define the functionality that you want to provide and package it as a set of LookML files and other configuration files. You can then publish the block to the Looker Marketplace, where other Looker users can install and use it.

31. How does Looker handle data governance and compliance?

Looker provides a number of features to help organizations maintain compliance with data governance policies, including granular access controls, activity logging, and audit trails. Looker also supports a range of data encryption and data masking options to protect sensitive data.

32. What are some common use cases for Looker?

Some common use cases for Looker include analyzing customer behavior and engagement, monitoring business performance metrics, and identifying trends and patterns in large datasets. Looker can be used in a variety of industries, including e-commerce, finance, healthcare, and more.

33. What are LookML dimensions?

LookML dimensions are fields in Looker that represent columns in your data source. Dimensions can be used to group, filter, and aggregate data in Looker, and can be created using LookML code.

Defining a dimension with a case statement:

dimension: category {
  type: string
  sql: CASE
    WHEN ${TABLE}.product_type = 'Clothing' THEN 'Apparel'
    WHEN ${TABLE}.product_type = 'Electronics' THEN 'Tech'
    ELSE 'Other'
  END ;;
}

In this example, we are defining a dimension called category that uses a case statement to categorize products in a SQL table. If the product_type column in the SQL table is Clothing, the dimension value will be Apparel. If the product_type is Electronics, the dimension value will be Tech. Otherwise, the dimension value will be Other.

34. What are LookML measures?

LookML measures are fields in Looker that represent aggregate calculations on data in your data source. Measures can be used to calculate metrics like sums, averages, and counts, and can be created using LookML code.

Defining a measure with a filter:

measure: count_completed_orders {
  type: count ;;
  filters: {
    field: status ;;
    value: "Completed" ;;
  }
}

In this example, we are defining a measure called count_completed_orders that counts the number of orders with a status of Completed. The measure includes a filter that specifies the status field and the Completed value.

35. How do you create a derived table in LookML?

A derived table is a virtual table that is created by applying SQL transformations to one or more base tables. In LookML, you can create a derived table by defining a view with a sql parameter that specifies the SQL query that defines the derived table.

Here’s an example:

view: customer_orders {
  derived_table: {
    sql: SELECT customer_id, COUNT(*) as order_count
         FROM orders
         GROUP BY customer_id
  }
  dimension: customer_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.customer_id ;;
  }
  measure: order_count {
    type: sum
    sql: ${TABLE}.order_count ;;
  }
}

In this example, we are defining a derived table called customer_orders that counts the number of orders for each customer in the orders table. The sql parameter specifies the SQL query that defines the derived table. We then define a dimension called customer_id and a measure called order_count based on the derived table.

36. How do you define a join in LookML?

A join in LookML is defined by creating a view that combines columns from two or more tables. You can define the join by specifying the relationship between the tables using the sql_on parameter.

Here’s an example:

view: customer_orders {
  sql_table_name: orders ;;
  sql_joins: [
    {
      type: left_outer
      relationship: many_to_one
      sql_on: ${orders.customer_id} = ${customers.id}
    }
  ]
  dimension: customer_name {
    type: string
    sql: ${customers.name} ;;
  }
  measure: order_count {
    type: count
    sql: ${TABLE}.id ;;
  }
}

In this example, we are defining a view called customer_orders that joins the orders table with the customers table based on the customer_id and id columns, respectively. The sql_joins parameter specifies the join, and we define a dimension called customer_name and a measure called order_count based on the joined table

37. How do you use LookML to create a dashboard?

In LookML, you can use the dashboard parameter to create a dashboard that displays one or more LookML dashboards. Here’s an example:

dashboard: sales_dashboard {
  title: "Sales Dashboard"
  layout: vertical
  elements: [
    {
      look: sales_report
      width: 6
    },
    {
      look: revenue_by_product
      width: 6
    }
  ]
}

In this example, we are defining a dashboard called sales_dashboard that displays two LookML dashboards: sales_report and revenue_by_product. The elements parameter specifies the dashboards to display and their widths within the overall layout. We also set the title parameter to “Sales Dashboard” and the layout parameter to vertical.

37. What is the Looker Parameter? How Do you use LookML parameter ?

There is a LookML parameter that is actually called “parameter“. The parameter parameter creates a filter-only field that can be used to filter Explores, Looks, and dashboards but that cannot be added to a result set. The value that a user selects for this filter-only field can create interactive query results, labels, URLs, and more when it is used with the {% parameter parameter_name %} and parameter_name._parameter_value Liquid variables. The parameter parameter can also simplify LookML models, as different results can be displayed in a single field.

Parameters in Looker are often used in conjunction with LookML, Looker’s modeling language, to create flexible and interactive reports. There are different types of parameters in Looker, including:

1. Filter Parameters: These parameters allow users to dynamically filter the data displayed in a report based on certain criteria, such as date ranges, product categories, or geographic regions.
2. Liquid Parameters: Liquid is Looker’s templating language, and Liquid parameters allow for dynamic text substitution within Looks and dashboards. They can be used to customize the text or labels in a report based on user input or other conditions.
3. User Attributes: User attributes are parameters that represent specific user characteristics, such as region, department, or role. They can be used to personalize the data displayed for each user based on their attributes.

For more details on looker parameters click here

More Imp Topics

How Looker Writes SQL?

How to calculate percent of total in looker?

Table Calculations in Looker

What are Derived tables in Looker?

How to set conditional formatting in LOOKER?

Looker Interview Questions And Answers

New Looker Performance Recommendations Dashboard

Looker Git Version Control: How To Revert To A Specific Commit In Looker, No Git Commands Necessary

________________________________________________________________________________________

Reference :

About Me:-
I am Om Prakash Singh – Data Analytics Consultant, Looker Consultant, and Solution Architect.
I am a Highly analytical and process-oriented Data Analyst with in-depth knowledge of database types; research methodologies; and big data capture, manipulation, and visualization. Furnish insights, analytics, and business intelligence used to advance opportunity identification.

You’ve got data and lots of it. If you’re like most enterprises, you’re struggling to transform massive information into actionable insights for better decision-making and increased business results.
Reach out to us if you are interested in evaluating if Looker is right for you or any other BI solution.