Hi everyone! Following to my previous message regading performance implications (https://elementary-community.slack.com/archives/C02CTC89LAX/p1705567419922439) i would like to add information to check if this is normal or it is a related problem with Databricks. 1. In regards of your reply @Elon Gliksberg: I looked at your link and i have tried to add the parameter:

vars:
  disable_dbt_artifacts_autoupload: true

The amount of queries have reduced, but the time has not reduced significantly. We are still facing a >1min extra execution time for the insert in invocations, run_results and test results tables. How many rows are there inserted so that it takes 1 minute? I have added the total of the queries and it only should take 15 seconds, but the run of the model, on average, takes one minute more with elementary. I attach one image of the queries below. 2. We have already added elementary to another project, and we are facing quite long (13 minute insert) queries that are doing the insert into the dbt_run_results table:

/* {"app": "dbt", "dbt_version": "1.5.2", "dbt_databricks_version": "1.5.5", "databricks_sql_connector_version": "2.7.0", "profile_name": "dataplatform", "target_name": "databricks", "connection_name": "master"} */
insert into
  `hive_metastore`.`quality`.`dbt_run_results` (
    model_execution_id,
    unique_id,
    invocation_id,
    generated_at,
    created_at,
    name,
    message,
    status,
    resource_type,
    execution_time,
    execute_started_at,
    execute_completed_at,
    compile_started_at,
    compile_completed_at,
    rows_affected,
    full_refresh,
    compiled_code,
    failures,
    query_id,
    thread_id,
    materialization
  )
values
  (
    '2aa75e16-875e-454e-b231-cc751c5509ec.model.Datalake.slv_dim_prd_fac_stock_unificado',
    'model.Datalake.slv_dim_prd_fac_stock_unificado',
    '2aa75e16-875e-454e-b231-cc751c5509ec',
    '2024-01-19 04:43:08',
    current_timestamp :: timestamp,
    'slv_dim_prd_fac_stock_unificado',
    'OK',
    'success',
    'model',
    418.8722710609436,
    '2024-01-19T04:35:58.159210Z',
    '2024-01-19T04:42:56.957968Z',
    '2024-01-19T04:35:58.142517Z',
    '2024-01-19T04:35:58.157508Z',
    NULL,
    True,
    '\n\nwith dim_prd_fac_stock_unificado_tmp_vw as (\n    select\n        dlk_raw_file_timestamp,\n        dlk_raw_filename,\n        fec_carga,\n        fec_dia,\n        id_centro_stock,\n        cast(id_item as int) as id_item,\n        cast(val_saldo as int) as val_saldo,\n        cast(val_ubicado as int) as val_ubicado,\n        cast(val_reserva as int) as val_reserva,\n        cast(val_stock2 as int) as val_stock2,\n        cast(val_transito_pd as int) as val_transito_pd,\n        cast(val_transito_ol as int) as val_transito_ol,\n        cast(val_stock_disp_llica as int) as val_stock_disp_llica\n    from `hive_metastore`.`bronze`.`dim_prd_fac_stock_unificado`\n)\n\n\n\nselect \n    dlk_slv_insert_timestamp,\n    dlk_raw_file_timestamp,\n    dlk_raw_filename,\n    fec_carga,\n    fec_dia,\n    id_centro_stock,\n    id_item,\n    val_saldo,\n    val_ubicado,\n    val_reserva,\n    val_stock2,\n    val_transito_pd,\n    val_transito_ol,\n    val_stock_disp_llica\nfrom (\n\n    \n        select\n        current_timestamp as dlk_slv_insert_timestamp,\n        \n            \n    md5(cast(concat() as string))\n as dlk_slv_unique_key,\n        \n        v.*\n    from dim_prd_fac_stock_unificado_tmp_vw v\n    ) T',
    NULL,
    '01eeb684-3e34-1402-ba9a-665fa6b9c34b',
    'Thread-1 (worker)',
    'table'
  )

It is a completely different project so execution times of the model are now different. My question is: how can we improve elementary's performance? Is it normal a 13 minute insert (Medium size SQL serverless warehouse)? Let me know if you need more information, Thanks in advance! :)

Hey @Arnau Font 👋🏼 The Elementary dbt package provides two main features: • Collecting artifacts during the on-run-end hook. • dbt tests, like anomaly detection I see in the attached images that you are running some Elementary tests as part of your project. The package queries the data from your schema and does some calculations (including creating temp tables for some tests). Some changes in your test configuration can reduce/increase run time (also will affect the accuracy of the test). You can also disable some artifacts collection, but this is not very recommended, some features won’t work without the artifacts. The package eventually runs extra queries and calculations. So it is normal that the package increases the run time. But I can’t say what is a “normal time increment”, there are many factors that affect this (the table size, number of tables, number of tests, configuration of each test, and so) Hope it is a bit clearer now 🙂

Hello @Noy Arie, In my model there are no tests. The queries shown are the result of a "dbt run --select model". Therefore, I can't understand the reason behind those minutes. Is there a best practices section on how to use elementary?

The

on-run-end

is running for

dbt run

as well. You can read about this hook here. I don’t recommend disabling the artifact collections, some of the features won’t work after this. You can read about the performance impact of the on-run-end hook (you are already using dbt version +1.4.0)