SQL Explorer Guide

For Admins and Analysts who need to validate their data using SQL queries.

Estimated reading time: 5 minutes

Prerequisites: CanViewSQLExplorer permission; familiarity with basic SQL SELECT syntax. 

Summary

SQL Explorer lets Admins and Analysts write and run custom SQL statements directly within their One Model instance to validate data, investigate metric results, and explore underlying tables. Your queries are encoded in the URL so you can bookmark, refresh, or share them instantly. Access is admin-level and users with this permission can see all data in the instance regardless of Data Access Role restrictions.

What you'll learn

After reading this article, you will be able to:

• Access SQL Explorer and understand its permission model
• Write, run, and manage queries in the editor
• Browse your tables and columns using the left-hand panel
• Share queries with teammates via URL
• Explain how One Model's table versioning works and what the numbered tables mean
• Troubleshoot common SQL Explorer errors

What is SQL Explorer?

SQL Explorer is a built-in query tool that lets you write and run SQL statements directly within your One Model instance. Use it when you need to go deeper than what Explore or Storyboards provide, for example, validating that a metric is calculating correctly, checking raw values for a specific employee ID, or confirming that a data load completed as expected.

The tool supports the full SQL SELECT syntax, including JOIN, WHERE, GROUP BY, and ORDER BY clauses. It does not support statements that modify data.

Getting Started

SQL Explorer is available to all One Model customers. To access it, a user must have the CanViewSQLExplorer permission.

Important: This is an admin-level data access tool - users with this permission can see all data in the instance, regardless of any Data Access Role restrictions. Do not grant this permission to users who require data restrictions.

Once the permission is assigned, navigate to Data → SQL Explorer to begin querying.

How to write and run a query

1. Navigate to Data > SQL Explorer.
2. Type your SQL into the editor. For example:

SELECT *
FROM one.prd_employee

3. Press Ctrl + Enter or click Run Query to run it.
4. View your results in the table below the editor.

Tip: Use the Table and Column viewer in the left-hand panel to browse available tables and columns. Drag a table or column name into the editor to insert it with correct quoting.

How to run multiple queries at once

You can write several statements in the editor separated by semicolons. When you click Run Query, each statement opens in its own result tab so you can switch between them without re-running.

SELECT *
FROM one.prd_employee;

SELECT *
FROM one.prd_employee_performance

How to run a single query from a multi-query editor

If you only want to run one of several statements, highlight that statement in the editor before pressing Run Query. Only the highlighted text will run.

Watch out: If you accidentally highlight a partial statement and press Run Query, only the highlighted portion runs, which will likely produce an error. Make sure your selection includes the complete statement.

How to filter and sort your results

SQL Explorer supports the full SELECT syntax you would expect. For example, to pull a single employee's history in effective-date order:

SELECT *
FROM one.prd_employee
WHERE employee_id = '00000000'
ORDER BY effdt

Common queries for the one schema

The Database Navigator on the left side of SQL Explorer displays the schemas where your data is stored. Tables that metrics and dimensions are built from are located in the one schema. Expand one to see the available tables, then expand each table to see its fields.

The examples below cover the most frequently used queries against the one schema.

See all records on the period employee table:

SELECT *
FROM one.prd_employee

See all employment records for a single employee, most recent first:

SELECT *
FROM one.prd_employee
WHERE worker_id = '<employee_id>'
ORDER BY effdt DESC

Note: The effdt and enddt fields define the date range for which each set of attributes is valid on the employee record.

See all event records for a single employee, most recent first:

SELECT *
FROM one.evt_employee
WHERE worker_id = '<employee_id>'
ORDER BY effdt DESC

See records active on a specific date:

SELECT *
FROM one.prd_employee
WHERE CAST('<date>' AS date) BETWEEN effdt AND enddt

Return only specific fields:

SELECT worker_id, effdt, enddt, employment_status
FROM one.prd_employee

See unique values in a field:

SELECT DISTINCT worker_id
FROM one.prd_employee

Search for similar values when the exact value isn't known:

SELECT *
FROM one.prd_employee
WHERE lastname LIKE '%smi%'

How to share a query via URL

Your current query is encoded directly into the browser URL, so the address bar always reflects what's in the editor. This means you can bookmark a query, refresh the page without losing your work, or share a query with a teammate.

To share a query:

1. Run your query.
2. Copy the URL from the browser address bar.
3. Send the URL to your teammate.

Your teammate can open the URL, log in to the same instance, and land on the exact same query, ready to run.

Important: You must already be logged in to the target instance before opening an SQL Explorer link. If you open the link first and then log in, the editor will appear empty.

Understanding table versioning in the One schema

When browsing tables in SQL Explorer, you may notice what look like duplicate tables. This is by design, here's what's happening:

Why some tables have numbered copies

Most tables in the one schema exist as a pair. For example:

During a data load, One Model creates a new underlying table (for example, one.prd_employee_7655). While that table is building, which can take anywhere from a few seconds to over an hour, the view continues to point to the previous table so that Explore and Storyboard queries are uninterrupted. Once processing completes, the view is updated to point to the new table and the old one is deleted.

Best practice: Always query the unnumbered table name (for example, one.prd_employee). This ensures your results match what Explore and Storyboards display.

Why you may see numbered one and processing schemas

You may see schemas like processing_101 or one_101. These are created by test runs of Processing Scripts. The number corresponds to the Processing Script ID that generated them.

Why SQL Explorer quotes identifiers automatically

When you drag a table or column name from the left-hand panel into the editor, SQL Explorer wraps it in double quotes. This is standard SQL practice, it prevents the parser from confusing your identifier with a reserved SQL keyword.

For example, order is both a common column name and a SQL keyword. Without quotes, a query referencing it would fail:

-- This will fail
SELECT order
FROM schema.table
ORDER BY order

With quotes, the parser correctly identifies each reference:

-- This works
SELECT "order"
FROM "schema"."table"
ORDER BY "order"

SQL Explorer quotes all dragged identifiers automatically so you don't need to worry about keyword conflicts.

Troubleshooting common SQL Explorer errors

If your query returns an error instead of results, the table below covers the most common causes and how to resolve them.

Tip: If you encounter an error not listed here, raise a Help Center ticket. Include the full error message, the query you ran (copy the URL from the address bar), and a timestamp of when the error occurred.

Quick reference