The error you're seeing:

Database Error in model alerts_dbt_models (models/edr/alerts/alerts_dbt_models.sql) Column 5 in UNION ALL has incompatible types: TIMESTAMP, STRING at [37:5]

is caused by a mismatch in column data types between tables or subqueries being combined with a

UNION ALL

. In this case, one source has a column as

TIMESTAMP

, while the other has it as

STRING

. BigQuery (and other SQL engines) require that columns in the same position in each part of a

UNION ALL

have the same data type. This issue has been reported multiple times in the context of Elementary, especially after upgrades or schema changes. It often happens when the schema of Elementary's internal tables (like

alerts_dbt_models

,

alerts_models

, or

elementary_test_results

) is not aligned—sometimes due to manual changes, version upgrades, or differences in how columns like

detected_at

are defined (as

STRING

in older versions,

TIMESTAMP

in newer ones). How to resolve: - Ensure that all relevant Elementary tables have the same data type for the affected column (e.g.,

detected_at

). For BigQuery, this should typically be

TIMESTAMP

. - If you recently upgraded Elementary, you may need to recreate the affected tables or migrate the column types. Some users have resolved similar issues by dropping and recreating the tables, or by running a migration query to cast the column to the correct type. For example:

sql   CREATE OR REPLACE TABLE your_schema.alerts_models AS   SELECT * EXCEPT(detected_at), CAST(detected_at AS TIMESTAMP) AS detected_at   FROM your_schema.alerts_models;

- After fixing the schema, rerun

dbt run -s elementary

to rebuild the models. This type of error has been discussed in the Elementary community, and fixes have been released in newer versions to address schema mismatches. If you continue to encounter this, upgrading both the Elementary dbt package and CLI to the latest version is recommended, as recent releases have addressed several related issues. Always ensure your table schemas are consistent after upgrades to avoid these UNION ALL type mismatches [see example discussion](https://slack.com/archives/C02CTC89LAX/p1701450932832359) and [another relevant thread](https://slack.com/archives/C02CTC89LAX/p1701705697028709). If you need to preserve existing data, consider backing up the tables before making schema changes.

The error you are experiencing—"Column X in UNION ALL has incompatible types: TIMESTAMP, STRING"—is a known issue in Elementary when there is a mismatch in column types (such as TIMESTAMP vs STRING) between tables or views being unioned. This often happens after upgrades or schema changes, and is not necessarily resolved by simply updating to the latest package versions. From the knowledge sources, the most common and effective solution is to drop the affected tables and views (such as those involved in the UNION, e.g.,

alerts_models

,

alerts_dbt_models

, or

elementary_test_results

), and then rerun the Elementary models to allow them to be recreated with the correct schema. This approach has resolved similar issues for other users, even after upgrading to newer versions of the packages: - "The first issue was resolved by dropping _elementary_exposures_ table and _enriched_exposures_ view." - "Dropping the schema and re-generating elementary models basically did the trick" - "Care to drop the

enriched_exposures

view? ... Dropping the view should fix the issue" [See example discussion](https://slack.com/archives/C02CTC89LAX/p1724547060206569) and [another relevant thread](https://slack.com/archives/C02CTC89LAX/p1681384521227129). Recommended steps: 1. Drop the problematic tables/views (e.g.,

alerts_dbt_models

,

elementary_exposures

,

enriched_exposures

, or any other table/view mentioned in the error). 2. Run

dbt run -s elementary --full-refresh

to rebuild the Elementary models with the correct schema. If you are still seeing the error after these steps, please ensure that all Elementary-related tables have consistent column types, especially for columns like

detected_at

. If you have custom modifications or are using a non-standard adapter, additional manual schema alignment may be necessary. If the problem persists even after following these steps with the latest package versions, the knowledge sources do not provide further troubleshooting steps specific to version 0.20.1. In that case, please consider reaching out to the Elementary community with detailed logs for more targeted support.