https://linen.dev logo
Join Slack
Powered by
Hi <@U088WCP3B4P> what are the columns called uniq...
# ask-elementary-ai
v
Hi @kapa.ai what are the columns called unique_key and incremental strategy in get_snapshots.sql file. While generating edr reports I am getting errors Invalid Identifier Incremental_strategy and unique_key. I am unable to generate the reports due to this tracebacks and compilation error. The actual error message is Error- Could not generate the report- Failed to run dbt command. Database error\n 000904. This all happening when the edr report is triggering dbt command —log-format json run-operation elementary.log_macro_results —args {“macro_name”: “elementary_cli.get_snapshots”}
k
A message from kapa.ai
The error you are encountering—invalid identifier for columns like unique_key and incremental_strategy in the get_snapshots.sql macro—typically occurs when the version of the Elementary dbt package and the Elementary Python CLI (elementary-data) are not aligned. Recent versions of Elementary introduced new columns (such as unique_key and incremental_strategy) in the elementary.dbt_snapshots table, and if your dbt package and CLI versions are mismatched, the CLI may expect columns that do not exist in your current schema, resulting in errors like:
Copy code
Database Error   000904 (42000): SQL compilation error: error line XX at position XX   invalid identifier 'UNIQUE_KEY'
or
Copy code
Unrecognized name: unique_key; Did you mean unique_id?
or similar for incremental_strategy. How to resolve: 1. Align the versions: Make sure that both your dbt package (in packages.yml) and your Python CLI (elementary-data) are on the same minor version. For example, if you are using elementary-data==0.16.2, your dbt package should also be version 0.16.2. Mismatched versions are a common cause of this error, as new columns are sometimes added in minor releases and both components must be upgraded together to avoid schema mismatches. 2. Rebuild the Elementary models: After upgrading both the dbt package and the CLI, you must run:
Copy code
dbt run --select elementary
This will rebuild the Elementary models and update the schema (including adding any new columns like unique_key and incremental_strategy) in your data warehouse. Simply running dbt run without --select elementary may not be sufficient, as it might not rebuild all the necessary Elementary tables and views. 3. Retry the report generation: Once the above steps are complete, try running edr report again. This issue and its resolution have been discussed in several community threads, where users resolved it by aligning versions and running dbt run --select elementary after upgrading. Here are some relevant references: - [Slack thread: unique_key and incremental_strategy errors after upgrade](https://slack.com/archives/C02CTC89LAX/p1740038661814829) - [Slack thread: unique_key column does not exist in dbt_snapshots](https://slack.com/archives/C02CTC89LAX/p1739810287607029) - [Slack thread: Upgrading both CLI and dbt package resolves the issue](https://slack.com/archives/C02CTC89LAX/p1739981606338559) Summary: The unique_key and incremental_strategy columns are new additions to the elementary.dbt_snapshots table, required by recent versions of Elementary. If you see errors about these columns being invalid or missing, upgrade both your dbt package and CLI to the same minor version and run dbt run --select elementary to update your schema. This should resolve the compilation and identifier errors when generating EDR reports.
v
@kapa.ai How to upgrade dbt package and CLI. What is the difference between the two. How to check the existing versions in the system.
k
A message from kapa.ai
A message from kapa.ai
To upgrade the Elementary dbt package and CLI, and to understand the difference between them, here’s what you need to know: ### Difference Between dbt Package and CLI - Elementary dbt package: This is a dbt package (elementary-data/elementary) that you add to your 
packages.yml
file in your dbt project. It contains dbt models, macros, and resources that run inside your dbt project. - Elementary CLI (elementary-data): This is a Python package you install via pip (elementary-data). It provides the 
edr
command-line tool for generating reports, monitoring, and interacting with the Elementary platform outside of dbt. ### How to Check Existing Versions - dbt package version: Check your 
packages.yml
file in your dbt project directory. The version is specified like this:
Copy code
yaml   packages:     - package: elementary-data/elementary       version: <version>
- CLI version: Run the following command in your terminal:
Copy code
pip show elementary-data
or
Copy code
edr --version
### How to Upgrade #### Upgrade the dbt package 1. Edit your 
packages.yml
file and set the version to the latest:
Copy code
yaml    packages:      - package: elementary-data/elementary        version: <latest version>
2. Run:
Copy code
dbt deps
3. If you are changing the minor version (e.g., 0.4.x → 0.5.x), also run:
Copy code
dbt run --select elementary
This ensures all Elementary models and tables are updated in your data warehouse. #### Upgrade the CLI Run:
Copy code
pip install elementary-data --upgrade
For more details, see the official documentation: [How to upgrade Elementary?](https://docs.elementary-data.com/oss/release-notes/upgrading-elementary)
To upgrade the Elementary dbt package and CLI, and to understand the difference between them, here’s what you need to know: ### Difference Between dbt Package and CLI - Elementary dbt package: This is a dbt package (elementary-data/elementary) that you add to your 
packages.yml
in your dbt project. It contains dbt models, macros, and resources that run inside your dbt project. - Elementary CLI: This is a Python package (elementary-data) you install with pip. It provides the 
edr
command-line tool for generating reports, monitoring, and interacting with your data warehouse using the models from the dbt package. ### How to Check Existing Versions - dbt package version: Check your 
packages.yml
file in your dbt project. The version is specified under the elementary package entry. - CLI version: Run the following command in your terminal:
Copy code
edr --version
Or, if you want to check the installed Python package version:
Copy code
pip show elementary-data
### How to Upgrade #### Upgrade the dbt package 1. Edit your 
packages.yml
file to set the latest version:
Copy code
yaml     packages:       - package: elementary-data/elementary         version: <latest version>
2. Run:
Copy code
dbt deps
3. If upgrading to a new minor version (e.g., 0.15.x → 0.16.x), also run:
Copy code
dbt run --select elementary
This ensures all new models and schema changes are applied. #### Upgrade the CLI Run:
Copy code
pip install elementary-data --upgrade
For more details, see the official [Elementary upgrade guide](https://docs.elementary-data.com/oss/release-notes/upgrading-elementary#upgrade-elementary-dbt-package) and [CLI install instructions](https://docs.elementary-data.com/oss/cli-install#how-to-create-profiles-yml). --- Summary: - The dbt package runs inside your dbt project; the CLI is a separate Python tool. - Check versions in 
packages.yml
(dbt package) and with 
edr --version
or 
pip show elementary-data
(CLI). - Upgrade the dbt package by editing 
packages.yml
and running 
dbt deps
(and 
dbt run --select elementary
for minor upgrades). - Upgrade the CLI with 
pip install elementary-data --upgrade
[How to upgrade Elementary?](https://docs.elementary-data.com/oss/release-notes/upgrading-elementary)
v
@kapa.ai I am able to generate the reports. However certain features like Data Health, Catalog, Incidents, Environments are disabled. Also I am generating the report with the project name in the top right corner but next to it I see a text ‘(dev)’ however I am running in the sandbox db not dev db. How to change it and where to change the configuration
k
A message from kapa.ai
The features you mentioned—**Data Health, Catalog, Incidents, Environments**—are only available in Elementary Cloud. If you are using the open-source version and generating reports with the CLI, these features will be disabled by design. The Data Health dashboard, for example, is not part of the open-source report and is exclusive to Elementary Cloud [see here](https://slack.com/archives/C02CTC89LAX/p1731252692458729). Regarding the environment label (e.g., “(dev)”) next to your project name in the report: By default, the report shows “dev” unless you explicitly set the environment using the 
--env
flag when running 
edr report
. This label is just a visual indicator and does not automatically reflect your dbt target or database. To change it, run your report command with the desired environment, for example:
Copy code
edr report --env sandbox
Note: - In older versions, only “dev” and “prod” were supported as values for 
--env
. In more recent versions (0.16+), you can use custom environment names like “sandbox” [see here](https://slack.com/archives/C02CTC89LAX/p1726176545966149). - The 
--env
flag only changes the label in the report UI; it does not affect which database or schema is queried. That is controlled by your dbt target/profile settings [see here](https://slack.com/archives/C02CTC89LAX/p1678730609472549). Summary: - Data Health, Catalog, Incidents, and Environments are only available in Elementary Cloud. - To change the environment label in the report, use the 
--env
flag with your desired environment name (supported in version 0.16+). - The label does not automatically reflect your dbt target; you must set it manually. If you need more control over the environment label, ensure you are using Elementary version 0.16 or higher.
v
@kapa.ai where to configure schema changes tests. under models we have sources.yml and inside we have different layers like landing staging and each folder has landing.yml and stage.yml
k
A message from kapa.ai
You should configure schema changes tests in the YAML files where you define your sources or models. For sources, this is typically in files like 
sources.yml
, 
landing.yml
, or 
stage.yml
under the relevant folders for each layer (e.g., landing, staging). For sources, you can define schema changes tests directly under each table in your source configuration. Here’s an example:
Copy code
yaml version: 2  sources:   - name: my_source     schema: landing     tables:       - name: my_table         tests:           - elementary.schema_changes:               tags: [elementary]
You can also use the 
elementary.schema_changes_from_baseline
test if you want to check against a baseline schema (requires specifying columns and data types):
Copy code
yaml version: 2  sources:   - name: my_source     schema: landing     tables:       - name: my_table         columns:           - name: column1             data_type: string           - name: column2             data_type: integer         tests:           - elementary.schema_changes_from_baseline
[See example and details here](https://docs.elementary-data.com/data-tests/schema-tests/schema-changes-from-baseline) Key points: - Define the tests under the relevant table in the YAML file for that layer (e.g., 
landing.yml
for landing tables, 
stage.yml
for staging tables). - You cannot define a schema changes test at the source name level to apply to all tables automatically; you must specify it for each table individually [as confirmed here](https://slack.com/archives/C02CTC89LAX/p1702891428476719). - For models (not sources), you would define the test under the model in the relevant YAML file. Summary: Configure schema changes tests under each table in your source YAML files (like 
landing.yml
, 
stage.yml
, etc.) for the appropriate layer. There is no way to apply the test to all tables in a source at once; it must be specified per table.
v
@kapa.ai Does schema changes tests require dbt run or dbt test is enough
k
A message from kapa.ai
Schema changes tests (such as 
elementary.schema_changes
or 
elementary.schema_changes_from_baseline
) require running 
dbt run
to execute the Elementary models that monitor schema changes. Running only 
dbt test
is not enough for these tests to function properly, because the monitoring logic is implemented in dbt models, not just in tests. This is confirmed by Elementary support: > "However, monitoring for schema changes (
elementary.schema_changes
test) does require executing the models with 
dbt run
." [Slack discussion](https://slack.com/archives/C02CTC89LAX/p1653308392841579) So, to ensure schema changes are detected, you should run:
Copy code
dbt run --select elementary
in addition to your usual 
dbt test
commands.
v
@kapa.ai when I run the command dbt run-operation elementary.schema_changes it is saying dbt could not find a macro with the schema changes in any package. I am running the edr report with corporate proxy enabled. The edr report is not reflecting these tests. What shall I do?
k
A message from kapa.ai
The error message 
dbt could not find a macro with the name "schema_changes" in any package
usually means that the Elementary dbt package is either not installed, not up to date, or not properly referenced in your project. Here’s what you should check and do: 1. Check your 
packages.yml
 Make sure you have the Elementary dbt package listed in your 
packages.yml
file, for example:
Copy code
yaml    packages:      - package: elementary-data/elementary        version: <your_version>
After editing, run:
Copy code
dbt deps
to install or update the package. 2. Check the version alignment If your CLI (
elementary-data
) and dbt package versions are not aligned, you may encounter missing macros or schema issues. Make sure both are on the same minor version and up to date. You can check the CLI version with:
Copy code
edr --version
and the dbt package version in 
packages.yml
 [see upgrade instructions](https://docs.elementary-data.com/oss/release-notes/upgrading-elementary). 3. Run the correct commands The 
elementary.schema_changes
is a test, not a macro you run with 
dbt run-operation
. You should define it in your YAML files under the relevant models or sources, then run:
Copy code
dbt run --select elementary    dbt test
Running 
dbt run-operation elementary.schema_changes
will not work because there is no such macro; the test is executed as part of 
dbt test
and the monitoring logic as part of 
dbt run
 [see here](https://docs.elementary-data.com/data-tests/schema-tests/schema-changes). 4. Proxy issues If you are behind a corporate proxy, ensure that your proxy settings are not blocking access to download the dbt package or Python dependencies. If 
dbt deps
fails or times out, check your proxy configuration. 5. Why tests may not show in the report If the schema changes tests are not reflected in the EDR report, it could be due to: - The tests not being executed (see above for correct commands). - The Elementary models not being run (
dbt run --select elementary
is required). - Version mismatch between CLI and dbt package. - Data not being written to the Elementary tables due to materialization or schema issues. Summary: - Do not run 
dbt run-operation elementary.schema_changes
(it’s not a macro). - Define the test in your YAML, run 
dbt deps
, then run 
dbt run --select elementary
and 
dbt test
. - Ensure both the dbt package and CLI are installed and on the same version. - Check your proxy is not blocking package downloads. If you follow these steps and still have issues, check your 
dbt.log
and 
edr.log
files for more details, as suggested in the troubleshooting guide [see troubleshooting steps](https://docs.elementary-data.com/oss/general/troubleshooting).
v
@kapa.ai I see transient tables being created in snowflake for schema_changes however it is not logging into edr report. Also when I type dbt ls --select test_type:generic package:element it is showing that list of tests in the terminal
k
A message from kapa.ai
If you see transient tables being created in Snowflake for 
schema_changes
but the results are not appearing in the EDR report, and 
dbt ls --select test_type:generic package:element
shows the tests in the terminal, here are some likely causes and troubleshooting steps based on the knowledge sources: 1. Elementary Models and Test Results Table The EDR report relies on data written to the 
elementary_test_results
table. If the schema changes tests are running (as shown by 
dbt ls
), but not appearing in the report, it could be that the test results are not being written to the correct table or schema. Double-check that your 
elementary
profile in 
profiles.yml
points to the same database and schema where the Elementary models and tables (including 
elementary_test_results
) are created. This is a common cause for missing test results in the report [see this discussion](https://slack.com/archives/C02CTC89LAX/p1659002103951729). 2. Profile and Target Alignment Ensure that you are running 
dbt run --select elementary
and 
dbt test
on the same target/profile as the one used by the EDR CLI. If you run tests on one target and generate the report from another, the report may not find the test results [see here](https://slack.com/archives/C02CTC89LAX/p1683764076675589). 3. Version Compatibility If you recently upgraded Elementary, make sure both the dbt package and the CLI are on the same version, and that you have re-run 
dbt run --select elementary
to update the models and tables as needed. Version mismatches can cause missing or misaligned data in the report. 4. Test Execution vs. Logging Even if the tests are listed and executed, if the underlying tables (like 
elementary_test_results
) are empty or not being populated, the report will not show results. You can check directly in Snowflake if the 
elementary_test_results
table contains recent records for your schema changes tests. 5. Debugging Steps - Check the 
elementary_test_results
table for data. - Ensure the 
elementary
profile in 
profiles.yml
matches the schema where the tables are created. - Run 
edr monitor debug
to validate your configuration. - Review the 
edr.log
and 
dbt.log
files for errors or warnings during test execution and report generation. If after these checks the issue persists, it may be helpful to share your log files with Elementary support for further troubleshooting, as suggested in several support threads. Summary: - The most common cause is a misalignment between the dbt target/profile used for running tests and the one used by the EDR CLI, or the results not being written to the expected schema/table [see here](https://slack.com/archives/C02CTC89LAX/p1659002103951729) and [here](https://slack.com/archives/C02CTC89LAX/p1683764076675589). - Double-check your configuration and ensure all steps are run on the same environment and profile. - Check the 
elementary_test_results
table for data and review logs for errors. If you need more specific help, please provide the relevant configuration and log details.
v
@kapa.ai I don’t see the schema tests in elementary test results table. But I see it in different transient tables in elementary_schema with the name test_1334566_elementary_schema_changes__stg_transaction. How can I move the test results to elementary_test_results table_
k
A message from kapa.ai
The issue you're experiencing—where schema changes tests are creating transient tables (like 
test_1334566_elementary_schema_changes__stg_transaction
) but not populating the 
elementary_test_results
table—typically indicates that the test results are not being collected and loaded into the main Elementary results table. This can happen due to a few common misconfigurations: 1. Elementary Models Not Run: The schema changes tests require the Elementary models to be run, not just the tests themselves. You must execute:
Copy code
dbt run --select elementary
This step is necessary for the logic that collects and loads test results (including schema changes) into the 
elementary_test_results
table. Running only 
dbt test
is not sufficient for schema changes tests to be reflected in the report or the results table [see here](https://slack.com/archives/C02CTC89LAX/p1653308392841579). 2. On-Run-End Hooks Not Triggered: Elementary uses on-run-end hooks to upload test results to the 
elementary_test_results
table. If these hooks are disabled or not running, the results will not be loaded. Check that you do not have any of the following variables set in your dbt command or `dbt_project.yml`:
Copy code
disable_run_results    disable_tests_results    disable_dbt_invocation_autoupload    disable_dbt_artifacts_autoupload
If any of these are set, the upload of test results may be disabled [see here](https://slack.com/archives/C02CTC89LAX/p1703824109693079). 3. Target/Profile Mismatch: Ensure that you are running both 
dbt run --select elementary
and 
dbt test
on the same target/profile as the one used by the EDR CLI. If the targets differ, the results may be written to a different schema or database, and the report will not find them [see here](https://slack.com/archives/C02CTC89LAX/p1684095583307509). 4. Version Compatibility: If you have recently upgraded Elementary, make sure both the dbt package and the CLI are on the same version, and re-run 
dbt run --select elementary
to update the models and tables as needed. What to do: - Run:
Copy code
dbt run --select elementary   dbt test
on the same target/profile. - Check that no disabling variables are set. - Ensure your 
elementary
profile in 
profiles.yml
points to the correct schema. - After running these commands, check if the 
elementary_test_results
table is now populated with your schema changes test results. If you follow these steps and still do not see results, review your 
dbt.log
and 
edr.log
files for errors, as suggested in the troubleshooting guides. Summary: You cannot manually "move" the results from the transient test tables to `elementary_test_results`; the Elementary models and hooks must do this automatically. Ensure you are running the correct commands and your configuration is aligned as described above [see here](https://slack.com/archives/C02CTC89LAX/p1653308392841579) and [here](https://slack.com/archives/C02CTC89LAX/p1703824109693079).
v
@kapa.ai Can we see the last 7 days run in the edr report ? Does it support in desktop standalone version
k
A message from kapa.ai
Yes, you can see the last 7 days of runs in the EDR report. By default, when you generate a report using the 
edr report
command, it includes data from the last 7 days. If you want to adjust this window, you can use the 
--days-back
(or 
-d
) parameter to specify a different number of days, for example:
Copy code
edr report --days-back 30
This functionality is available in the open-source (desktop/standalone) version of Elementary, not just in Elementary Cloud. The "Last 7 days" filter is the default, and you can extend it as needed using the CLI parameter [as confirmed here](https://slack.com/archives/C02CTC89LAX/p1686465000985369) and [here](https://slack.com/archives/C02CTC89LAX/p1708506510889439).
v
@kapa.ai But My edr report ‘last 7days’ is greyed out. How to enable it and how to maket this work
k
A message from kapa.ai
If the "Last 7 days" button is greyed out in your EDR report, this is expected behavior: the report UI does not currently support interactive date range filtering. The "Last 7 days" label simply reflects the data window that was set when you generated the report, and it cannot be changed from within the report itself. To change the number of days shown in the report, you need to regenerate the report using the 
--days-back
parameter. For example, to include the last 30 days:
Copy code
edr report --days-back 30
This will update the report to show data for the specified period, and the label in the top right will reflect your chosen window (e.g., "Last 30 Days"). The UI does not allow you to interactively enable or change this filter after the report is generated [as confirmed here](https://slack.com/archives/C02CTC89LAX/p1708506510889439) and [here](https://slack.com/archives/C02CTC89LAX/p1686465000985369). Summary: - The "Last 7 days" button is not interactive; it is just a label. - To change the time window, regenerate the report with the desired 
--days-back
value. - There is currently no way to enable or interactively change this in the standalone report UI.
v
@kapa.ai then how schema changes from baseline works. Where to make changes to ensure this schema changes from baseline works as expected?
k
A message from kapa.ai
The 
schema_changes_from_baseline
test in Elementary works by comparing the current schema of your table (columns and data types) against a baseline that you define in your dbt YAML configuration. If there are any differences—such as added, removed, or changed columns—the test can fail or alert you, depending on your configuration. ### How it works - You define the expected columns and their data types for a table or source in your YAML file. - The test checks the actual schema in your data warehouse against this baseline. - If there are differences (e.g., a column is added, removed, or its type changes), the test will fail or alert, depending on parameters like 
fail_on_added
and 
enforce_types
. ### Where and how to configure 1. Define the baseline in your YAML file You must specify the columns and their data types under the relevant table in your source or model YAML file. For example:
Copy code
yaml    version: 2     sources:      - name: my_source        schema: my_schema        tables:          - name: my_table            columns:              - name: id                data_type: integer              - name: name                data_type: varchar            tests:              - elementary.schema_changes_from_baseline:                  fail_on_added: true                  enforce_types: true
The test will only work if the columns and data types are defined in the YAML configuration [see documentation](https://docs.elementary-data.com/data-tests/schema-tests/schema-changes-from-baseline). 2. (Optional) Auto-generate the baseline You can use the 
generate_schema_baseline_test
macro to generate the baseline configuration based on your current table schema. Run:
Copy code
dbt run-operation elementary.generate_schema_baseline_test --args '{"name": "my_table", "fail_on_added": true, "enforce_types": true}'
This will output the YAML configuration, which you then copy and paste into your YAML file. The macro does not update your YAML automatically; you must do this step manually [see clarification](https://github.com/elementary-data/elementary/issues/1455). 3. Run dbt commands After updating your YAML, run:
Copy code
dbt run --select elementary    dbt test
This ensures the Elementary models are updated and the tests are executed. ### Key points to ensure it works as expected - The baseline (columns and types) must be present in your YAML. - The test parameters (like 
fail_on_added
and 
enforce_types
) can be set in the YAML under the test. - The test does not update automatically if your schema changes; you must update the baseline in YAML if you want to accept new schema changes as the new baseline. - The test is supported for sources and models, but not for all data warehouses (e.g., not supported in Athena or Trino; Databricks support requires Unity Catalog) [see docs](https://docs.elementary-data.com/data-tests/schema-tests/schema-changes-from-baseline). If you follow these steps, 
schema_changes_from_baseline
will work as expected and alert you to any schema drift from your defined baseline.
40 Views
Open in Slack
PreviousNext
Powered by