https://linen.dev logo
Join Slack
Powered by
Just want to say hi and post my solution to common...
# community-support
m
Just want to say hi and post my solution to common noobie problem with custom schemas and having elementary builds on the correct schema in dev/prod environments. Probably everyone has gone through this so this is more of a reference for new users, in thread:
So, here is how i put my 
profiles.yml
config, important to note the 
elementary
block is a top-level profile, equal to the indentation of whatever profiles you already set up:
Copy code
# this is my existing dbt profile
muh_team:
   target: dev    # this is when I'm on development branch 'dev' I run this target
   outputs:
    dev:
      type: databricks
      method: http
      catalog: muh_team_catalog
      schema: dev
      threads: 32
      http_path: /sql/1.0/warehouses/xxxx
      host: xxxxx.databricks.com
      token: "{{ env_var('DATABRICKS_TOKEN') }}"

elementary:
  target: dev        # note same target name as above
  outputs:
    dev:
      type: databricks
      method: http
      catalog: muh_team_catalog
      schema: dev
      threads: 32
      http_path: /sql/1.0/warehouses/xxxx
      host: xxxxx.cloud.databricks.com
      token: "{{ env_var('DATABRICKS_TOKEN') }}"
In 
dbt_project.yml
I have model defs like:
Copy code
models:    
  muhname:

  elementary:
    +schema: elem
So my 
generate_schema_name
is customized to give these:
Copy code
muhname           production environment schema
muhname_dev       dev schema
muhname_dev_elem  dev elementary schema
muhname_elem      production data for elementary
I do 
dbt build --target dev
and elementary tasks are creating tables in correct schema, which was not working before. stay tuned to see if this works when I do 
edr report
, but
Okay, elementary is running its tests and hooks as expected, and tables are getting populated in my expected schemas:
Copy code
select count(*) from muhname_dev_elem.elementary_test_results;
| count |
| 24    |
Great! But this isnt working:
Copy code
edr report
Apparently the edr has an internal package that doesnt resprect custom schemas, it can't find the tables. Error message:
Copy code
2024-09-17 13:10:33 — ERROR — Unable to get the latest invocation: list index out of range

[TABLE_OR_VIEW_NOT_FOUND] The table or view `sandbox_desa`.`dev`.`elementary_test_results` cannot be found.
Seems like I need to specify edit 
profiles.yml
to give 
schema: muhname_dev_elem
as a literal to get edr to find the custom locations depending on environment.
By rewriting profiles.yml for the cli invocation 
edr report
it can find the schema with artifacts are uploaded:
Copy code
#   change
    schema: dev
  ->
    schema: muhname_dev_elem
Unfortunately I still get an empty report file, here are the logs I've blown enough time on this today:
Copy code
Document: <file://localhost/Users/merlin/muh_project/edr_target/elementary_report.html>
Number of lines: 0
Number of pages: 0 page (approximately)
Here is the full command in poetry environment and output. No errors thrown but the report html file is empty.
Copy code
poetry run edr report --project-dir ~/zillpo/muh-repo/muh_project --profiles-dir ~/zillpo/muh-repo/muh_project --profile-target dev --project-profile-target dev

2024-09-17 13:41:20 — INFO — Running with edr=0.16.1
using legacy validation callback
2024-09-17 13:41:26 — INFO — edr (0.16.1) and Elementary's dbt package (0.16.1) are compatible.
2024-09-17 13:41:26 — INFO — Elementary's database and schema: '"muh_team_catalog.muhname_dev_elem"'
2024-09-17 13:41:26 — INFO — Running dbt command --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_test_results", "macro_args": {"days_back": 7, "invocations_per_test": 720, "disable_passed_test_metrics": false}} --project-dir ~/Library/Caches/pypoetry/virtualenvs/ikeloa-dbt-iL6m7d2w-py3.12/lib/python3.12/site-packages/elementary/monitor/dbt_project --profiles-dir ~/zillpo/muh-repo/muh_project --target dev
2024-09-17 13:41:29 — INFO — Running dbt command --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_source_freshness_results", "macro_args": {"days_back": 7, "invocations_per_test": 720}} --project-dir ~/Library/Caches/pypoetry/virtualenvs/ikeloa-dbt-iL6m7d2w-py3.12/lib/python3.12/site-packages/elementary/monitor/dbt_project --profiles-dir ~/zillpo/muh-repo/muh_project --target dev
2024-09-17 13:41:31 — INFO — Running dbt command --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_models", "macro_args": {"exclude_elementary": true}} --project-dir ~/Library/Caches/pypoetry/virtualenvs/ikeloa-dbt-iL6m7d2w-py3.12/lib/python3.12/site-packages/elementary/monitor/dbt_project --profiles-dir ~/zillpo/muh-repo/muh_project --target dev
2024-09-17 13:41:33 — INFO — Running dbt command --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_sources", "macro_args": {}} --project-dir ~/Library/Caches/pypoetry/virtualenvs/ikeloa-dbt-iL6m7d2w-py3.12/lib/python3.12/site-packages/elementary/monitor/dbt_project --profiles-dir ~/zillpo/muh-repo/muh_project --target dev
2024-09-17 13:41:35 — INFO — Running dbt command --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_exposures", "macro_args": {}} --project-dir ~/Library/Caches/pypoetry/virtualenvs/ikeloa-dbt-iL6m7d2w-py3.12/lib/python3.12/site-packages/elementary/monitor/dbt_project --profiles-dir ~/zillpo/muh-repo/muh_project --target dev
2024-09-17 13:41:38 — INFO — Running dbt command --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_singular_tests", "macro_args": {}} --project-dir ~/Library/Caches/pypoetry/virtualenvs/ikeloa-dbt-iL6m7d2w-py3.12/lib/python3.12/site-packages/elementary/monitor/dbt_project --profiles-dir ~/zillpo/muh-repo/muh_project --target dev
2024-09-17 13:41:39 — INFO — Running dbt command --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_models_runs", "macro_args": {"days_back": 7, "exclude_elementary": true}} --project-dir ~/Library/Caches/pypoetry/virtualenvs/ikeloa-dbt-iL6m7d2w-py3.12/lib/python3.12/site-packages/elementary/monitor/dbt_project --profiles-dir ~/zillpo/muh-repo/muh_project --target dev
2024-09-17 13:41:41 — INFO — Running dbt command --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_dbt_models_test_coverage", "macro_args": {}} --project-dir ~/Library/Caches/pypoetry/virtualenvs/ikeloa-dbt-iL6m7d2w-py3.12/lib/python3.12/site-packages/elementary/monitor/dbt_project --profiles-dir ~/zillpo/muh-repo/muh_project --target dev
2024-09-17 13:41:43 — INFO — Running dbt command --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_nodes_depends_on_nodes", "macro_args": {"exclude_elementary": true}} --project-dir ~/Library/Caches/pypoetry/virtualenvs/ikeloa-dbt-iL6m7d2w-py3.12/lib/python3.12/site-packages/elementary/monitor/dbt_project --profiles-dir ~/zillpo/muh-repo/muh_project --target dev
2024-09-17 13:41:45 — INFO — Running dbt command --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_models_latest_invocation", "macro_args": {}} --project-dir ~/Library/Caches/pypoetry/virtualenvs/ikeloa-dbt-iL6m7d2w-py3.12/lib/python3.12/site-packages/elementary/monitor/dbt_project --profiles-dir ~/zillpo/muh-repo/muh_project --target dev
2024-09-17 13:41:46 — INFO — Running dbt command --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_models_latest_invocations_data", "macro_args": {}} --project-dir ~/Library/Caches/pypoetry/virtualenvs/ikeloa-dbt-iL6m7d2w-py3.12/lib/python3.12/site-packages/elementary/monitor/dbt_project --profiles-dir ~/zillpo/muh-repo/muh_project --target dev
💯 1
s
Facing the same issue
m
I found a solution, I needed to understand that edr isnt a dbt package, its using the 
profiles.yml
or other configuration to just read the tables in the schema you give it. So it does not use the 
generate_schema_name
macro and wont reference schema names the same way as my dbt calls do. So I fixed the behavior for dev/stable/prod with three entries in the database connection profiles:
Copy code
# my profiles.yml for databricks, also has my dbt project connect details above but those are not relevant to edr cli commands.

elementary:
  outputs:
    dev_elem:
      type: "databricks"
      host: "xxxxxx.cloud.databricks.com"
      http_path: "/sql/1.0/warehouses/xxxxxxxxxxxx"
      catalog: "team_catalog_name"
      schema: muhschema_dev_elem
      token: "{{ env_var('DBT_ACCESS_TOKEN') }}"
      threads: 32
    stable_elem:
      type: "databricks"
      host: "xxxxxx.cloud.databricks.com"
      http_path: "/sql/1.0/warehouses/xxxxxxxxxxxx"
      catalog: "team_catalog_name"
      schema: muhschema_stable_elem
      token: "{{ env_var('DBT_ACCESS_TOKEN') }}"
      threads: 32
    main_elem:
      type: "databricks"
      host: "xxxxxx.cloud.databricks.com"
      http_path: "/sql/1.0/warehouses/xxxxxxxxxxxx"
      catalog: "team_catalog_name"
      schema: muhschema_elem
      token: "{{ env_var('DBT_ACCESS_TOKEN') }}"
      threads: 32
The main point is each profile name references the full schema name in the DB, not doing the dbt thing of assembling schema name by parts. I'm calling edr like this:
Copy code
edr report --profile-target dev_elem --project-dir . \
   --profiles-dir . --project-profile-target dev \
   --file-path elem-report.html --open-browser false
actually the 
--project-profile-target dev
part is not needed. EDR does not reference the dbt_project.yml information or configs at all. The 
dbt test
runs all your elementarty tests, and edr looks in the specified schemas for specific tables.
33 Views
Open in Slack
PreviousNext
Powered by