Common Errors
Duplicate/Ambiguous Column Names
You may have encountered an error such as “The query generated by this panel references an ambiguous column name” or “The saved query joins tables with duplicate column names (and may not work properly with the dashboard.”
Frequently, this occurs when the query generating the dataset selects all columns from the result of a JOIN operation, where the tables JOINed together share column names. We deduplicate those column names for you, adding a number to the end of the duplicate. For example, a query could look like:
SELECT * from table_one t1
JOIN table_two t2
ON t1.column_name = t2.column_name
While this dataset alone does not put your dashboard in a bad state, creating a data panel that leverages one of these duplicated columns will cause an error.
How do I resolve this?
Explicitly Select Column Names
Instead of using SELECT *
to generate columns for your dataset, you could
rewrite the query to explicitly name the columns you want to read from. This may
lengthen the query you’re writing, but is generally a best practice that avoids
pulling in unwanted columns.
SELECT t1.column_name, other_column_name from table_one t1
JOIN table_two t2
ON t1.column_name = t2.column_name
If there are too many columns that you need to pull in, you can also still leverage SELECT *, but either select all columns from a single table or additionally alias your duplicated column to avoid ambiguous references. Note that if you alias the column name, the duplicated columns will still show up in your dataset and should not be used.
# select all columns from one table and some from the other
SELECT t1.\*, t2.other_column_name from table_one t1
JOIN table_two t2
ON t1.column_name = t2.column_name
# select all columns and alias duplicated column
SELECT \*, t1.column_name AS duplicate_column_name from table_one t1
JOIN table_two t2
ON t1.column_name = t2.column_name
Use USING Instead of JOIN
Many SQL syntaxes allow for USING statements, which are shorthand for a JOIN on a column name that exists on both tables. This will only generate one column for the duplicated column, but may still generate warnings if your tables have other columns with identical names. Therefore, the USING solution will only work if you’re only using the duplicated column that the query is JOINing on.
SELECT * from table_one t1
JOIN table_two t2
USING (column_name)
Error parsing Date/Time
When building visualizations, you might encounter an error around trying to do a time operation on a date field:
The query generated by this panel is attempted to do time operations on a date field. Please ensure that the field you are operating on has a time part before doing hour, minute, or second operations
This occurs when trying to group a date field by a time component (hour, minute,
seconds). On some databases, this operation can only be done on a DATETIME
or
TIMESTAMP
field.
How do I resolve this?
The easiest approach is to not use the time based grouping on this field. The
DATE
field doesn’t contain time information anyways and so it won’t provide
much value to chart it that way.
Alternatively, you can look into your database or warehouse and see if you can
convert the field to the necessary DATETIME
or TIMESTAMP
type.
Table Does Not Exist
When viewing tables from the Manage Data page, you may have encountered an error:
The data source you are viewing is incompatible with its schema. This is likely because you have added a data source to a schema, but the data source does not contain all tables defined by the default data source of the schema.
Frequently, this error occurs when you’re attempting to query a table using a data source that doesn’t contain that table. Tables are generated for a schema when you choose a default data source for that schema, and it is assumed that all data sources you connect to that schema contain all of these tables.
Because all queries for a schema are expected to be able to run against all data sources connected to that schema, we treat the default data source’s synced tables as the source of truth for that schema.
How do I resolve this?
If you do not need the table synced to Explo, resolving this is as easy as resyncing tables for that schema and not including this table. See Sync New Tables for more information.
Otherwise, you must consider either adding the data source to a different schema or adding that table to your data source. Note that all queries for a schema should run against all data sources in that schema, so any queries you have that use this table would not work for any customers that are associated with the data source.