How to Test and Validate Data Models in Looker

Building a powerful LookML model is one thing, but trusting the data it produces is everything. A flawless-looking dashboard is useless - and even dangerous - if the underlying calculations are wrong. We'll walk through the essential practices for testing and validating your data models in Looker to ensure your charts are not just beautiful, but accurate.

Good Data is Built on Trust

Before diving into the "how," let's understand the "why." Failing to properly validate your Looker model is like building a house on a shaky foundation. Sooner or later, things will start to break, and the consequences can be costly.

Here's what you risk with an untested model:

• Poor Business Decisions: Imagine your marketing team cranked up spending on a campaign because a dashboard showed an incredibly low cost-per-lead (CPL). Weeks later, they realize a LEFT JOIN should have been an INNER JOIN, and the CPL was actually 3x higher. Untested data leads to misallocated resources and missed opportunities.
• Erosion of Stakeholder Trust: When a VP points out a number on your dashboard that doesn't match their records, confidence in the entire data stack plummets. Once lost, that trust is incredibly difficult to win back. Every report you produce from that point forward will be met with skepticism.
• Wasted Development Time: An "innocent" change to a LookML view can break dozens of saved Looks and mission-critical dashboards. Without validation, you won't know until angry emails fill your inbox. This pulls you away from building new features and forces you into reactive debugging sessions.

Treating data validation as a formal quality assurance step is non-negotiable. It’s what transforms your data model from a fragile, uncertain structure into a reliable source of truth for the entire organization.

The Two-Pillar Approach to Looker Model Testing

A robust testing strategy in Looker relies on two distinct but complementary methods. Relying on only one leaves you exposed to potential errors.

1. Automated Validation: These are your first lines of defense, built directly into the Looker IDE. Tools like the LookML Validator and Content Validator check the syntactical integrity of your code and its impact on existing content. They ensure your model is structurally sound and won't cause system errors.
2. Manual Validation: This is where you verify the business logic of your model. Automated tools can't tell you if your definition of "Active User" is correct, but manual validation can. This involves comparing the output from Looker with a known-good external source to confirm your calculations are meaningful and accurate.

A comprehensive workflow uses both. Let's break down how to use each effectively.

Using Looker's Automated Validators

Looker provides two powerful tools to catch common errors before they ever make it to your end-users. Make them a regular part of your development process.

The LookML Validator: Your Constant Companion

The LookML Validator is a real-time linter that scans your LookML project for errors. Think of it as a spell-checker for your code. It checks for everything from typos to incorrect field references and SQL syntax issues.

When to use it: All the time! Run the validator after any significant change and always before you commit your code and deploy it to production.

How to Run the LookML Validator:

1. Make sure you are in Development Mode.
2. Navigate to your LookML project.
3. In the top-right corner of the IDE, you'll see a button labeled Validate LookML. Click it.
4. Looker will scan all the files in your project. Once it's done, you'll either see "No LookML errors found" or a list of errors and warnings.

It's important to understand the difference between its outputs:

• Errors: These are show-stoppers. They represent issues that will break your model, such as pointing to a non-existent field, using incorrect LookML syntax, or having an SQL error in a derived table. You must fix all errors before you can deploy your changes.
• Warnings: These are suggestions for improvement or calls to attention for potentially problematic code. For example, a warning might be for a dimension that might fan out a measure. The model will still run, but the results might be unexpected if you're not careful. It's best practice to resolve all warnings as well.

The Content Validator: Preventing Downstream Breakage

The Content Validator is your safety net after validation. It's a powerful tool when you're making changes that can affect other pieces of saved content. If you have a popular field users.age saved in multiple dashboards, looks, and reports, and you need to rename this field to users.user_age, the Content Validator makes this really simple. It will check everywhere this field is used, flag any errors caused by this, and do a bulk find-and-replace to make the change!

When to use it: Before deploying any significant model changes, especially refactoring like renaming fields/views, changing calculation logic in a measure, or removing something entirely.

How to Run the Content Validator:

1. While in Development Mode, navigate to the Develop tab in the main Looker navigation bar.
2. Select Content Validator.
3. Click the blue Validate button. The tool will begin scanning all saved Looks and Dashboards across your Looker instance.
4. The results page will present a grouped list of errors, showing which content is broken and why (e.g., "Unknown field users.age referenced in a Look").
5. For issues like renamed fields, you can use the built-in Find & Replace functionality to update all affected content in one go, saving you from manually editing dozens of reports.

Using the Content Validator proactively turns a potentially chaotic fire drill into a smooth, controlled update process.

Manual Validation: Establishing the Ground Truth

No amount of automated testing can confirm that the logic behind your model is correct. The LookML might be perfectly valid, but what if your formula for "Monthly Recurring Revenue" is just plain wrong? This is where manual validation becomes the ultimate trust-builder.

The core principle is simple: compare the data generated by Looker against a "source of truth." This usually means writing your own SQL query directly against your database and seeing if the results match what you see in a Looker Explore.

A Step-by-Step Manual Validation Workflow:

Step 1: Create a Test Explore

Start by building the simplest possible view in a Looker Explore. If you're testing a new measure, like order_items.average_profit, create an Explore that contains just that new measure and maybe one or two dimensions, such as orders.created_date and products.category.

Step 2: Grab the Looker-Generated SQL

After you run your simple Explore, click on the SQL tab in the data pane. Looker shows you the exact SQL query it generated and sent to your database to get the results. Copy this entire query to your clipboard.

Step 3: Write Your Own "Source of Truth" Query

Now, open your preferred SQL client (e.g., Snowflake's UI, BigQuery's console, DBeaver) that connects to the same database Looker uses. Write a SQL query from scratch that you believe calculates the exact same metric. This query is your trusted baseline. For example, to verify average_profit, your manual query might look like this:

SELECT
  DATE(o.created_at),
  p.category,
  AVG(oi.sale_price - oi.cost) as avg_profit
FROM public.orders o
JOIN public.order_items oi ON o.order_id = oi.order_id
JOIN public.products p ON oi.product_id = p.product_id
GROUP BY 1, 2
ORDER BY 1, 2

Step 4: Compare the Results

Run both your manual query and the query you copied from Looker. Do the results match down to the decimal point?

• If yes: Congratulations! You’ve manually validated your logic. Your LookML is doing exactly what you designed it to do.
• If no: This is a powerful moment for debugging. The discrepancy points you exactly where the problem is. Did your JOIN in Looker have a slightly different condition? Is your aggregation type set to sum instead of average? Analyze the differences in the SQL and your LookML definitions to find and fix the logical error.

Best Practices for a Resilient Validation Process

To really embed testing into your workflow, adopt these habits:

• Enforce Peer Reviews: Use Git integration features like pull requests to require another set of eyes on any LookML changes. A teammate might question a logical assumption you took for granted.
• Test the Edges: Don't just validate the "happy path." Use filters in your Explores to test edge cases. How does your model handle NULL values? Does your weekly cohort report work correctly over New Year's week? Intentionally try to break things.
• Always Add Descriptions: Use the description parameter in your LookML fields to document, in plain English, what the field represents and how it’s calculated. This makes it infinitely easier for others (and your future self) to understand the logic and validate it later.

Final Thoughts

Validating your Looker model is an investment that pays dividends in data accuracy and company-wide trust. Combining Looker's built-in automated tools with a disciplined manual SQL review process ensures your model is not only syntactically correct but also logically sound. This transforms your Looker instance from a simple visualization tool into a reliable engine for business decisions.

The complexity of building, validating, and maintaining LookML models is exactly why we built Graphed. Instead of hours spent in the IDE writing joins and debugging SQL, our AI-powered approach allows you to connect data sources and simply ask questions in natural language. We handle the complexities of data modeling and validation behind the scenes, so you can go from raw data to a trusted, real-time dashboard in seconds, not weeks.