If your custom SQL for a dimension runs fine on its own but fails with a syntax error when executing Elementary tests, there are a few common causes based on the knowledge sources: 1. Incorrect YAML Syntax for Dimensions: If you define the

dimensions

parameter as a string instead of a list, it can cause SQL compilation errors. For example, this is incorrect:

yaml    dimensions: "app_api_key"

It should be:

yaml    dimensions:      - app_api_key

Double-check your test configuration to ensure the dimension is defined as a list, even if there is only one dimension. This has been a common source of errors for users and can lead to unexpected SQL syntax issues during test compilation [Dimension test YAML syntax issue](https://slack.com/archives/C02CTC89LAX/p1699977181538869). 2. SQL Expressions as Dimensions: If you are using a SQL expression as a dimension (e.g., a function or transformation), ensure you are not wrapping the expression in quotes unless required by the YAML syntax. Quoting the expression can sometimes cause it to be interpreted as a string literal in the generated SQL, leading to syntax errors. Also, the compatibility of complex expressions as dimensions may depend on the data warehouse and how Elementary compiles the test SQL. If you encounter errors, try simplifying the expression or testing with a basic column name to isolate the issue [SQL expression as dimension issue](https://slack.com/archives/C02CTC89LAX/p1710839449760619). 3. Elementary Package Version: Some syntax errors are due to bugs in specific versions of the Elementary dbt package. If you are not on the latest version, consider upgrading, as many SQL compilation issues have been fixed in recent releases [Dimension anomaly test bug fix](https://slack.com/archives/C02CTC89LAX/p1677701666063139). 4. Warehouse-Specific Syntax: If your custom SQL uses warehouse-specific features (e.g., Snowflake's variant fields or BigQuery's quoting), ensure that the generated SQL is compatible with your warehouse. Sometimes, Elementary's macros may not handle all edge cases, especially with complex expressions or data types. If you can share the specific error message and your test YAML configuration, it would help narrow down the root cause. Otherwise, review the points above, as they address the most common issues users have encountered with dimension anomaly tests in Elementary.

Based on the knowledge sources, syntax errors like "Expected ')' but got keyword WHEN" in Elementary tests are often caused by how complex SQL expressions are handled in test configurations, especially when using them as dimensions or in YAML files. Key points from the knowledge sources: - When using SQL expressions as dimensions in Elementary tests, you must ensure the expression is valid in the context where Elementary inserts it. If you wrap the entire CASE statement in quotes and use it as a dimension, Elementary may not handle it correctly, leading to SQL compilation errors. This is especially true for complex expressions like CASE statements, which may not be supported as dimension definitions in the YAML config [SQL expression as dimension issue](https://slack.com/archives/C02CTC89LAX/p1710839449760619). - The knowledge sources do not provide an explicit solution for using a full CASE statement as a dimension. They do mention that using SQL expressions as dimensions can be problematic and may not be supported as expected. - If you need to use a complex CASE statement, the recommended approach is to create a model or a view in your dbt project that defines this logic as a new column, and then reference that column as the dimension in your Elementary test. This avoids embedding complex SQL directly in the test configuration, which is prone to syntax errors [Dimension test YAML syntax issue](https://slack.com/archives/C02CTC89LAX/p1699977181538869). Summary: The knowledge sources do not provide a way to use a full CASE statement as a dimension directly in the Elementary test YAML. The recommended workaround is to define the CASE logic as a column in a model or view, and then use that column as the dimension in your test configuration. This approach is more robust and avoids SQL compilation errors. If you need further help, please share your test YAML configuration and clarify how you are using the CASE statement in your test.

You can view the compiled SQL query that Elementary generates and runs for a test in several ways: - In the data warehouse, check the

elementary_test_results

table, specifically the

test_results_query

column. This column contains the SQL query that was executed for each test result [Slack answer on test_results_query](https://slack.com/archives/C02CTC89LAX/p1689584645853779). - In the Elementary report (EDR report HTML), you can see the query in the test details section, usually labeled as "Result Query." You may need to click or expand the test details to view it [Slack answer on EDR report](https://slack.com/archives/C02CTC89LAX/p1672322657143559). - If you are using dbt, the compiled SQL for tests is also available in the

dbt_run_results

table under the

compiled_sql

column [Slack answer on compiled SQL locations](https://slack.com/archives/C02CTC89LAX/p1672327192977409). These methods allow you to review and debug the exact SQL that Elementary uses to run your tests.

If the test query fails with a syntax error, the

test_results_query

column in the

elementary_test_results

table may remain null or empty. This is because Elementary is unable to successfully compile and store the query when an error occurs during test execution. This behavior is confirmed in the knowledge sources, where users encountered null values in

test_results_query

due to errors such as permission issues or SQL compilation failures. In these cases, Elementary cannot save the faulty query, so the column is left null [Slack thread on null test_results_query](https://slack.com/archives/C02CTC89LAX/p1657883114555219). If you need to debug the error, you may need to check your dbt or Elementary logs for the failed SQL or error message, as the query itself will not be stored in the results table when a syntax error prevents its compilation.