Should project evaluator track progress over time?

[jschintz-nytimes]

Describe the feature

Allow a configuration that tracks progress & changes to a projects health by capturing snapshots or metrics overtime.

Getting users to onboard to project evaluator is hard - its even harder to show if material progress is being made. Give a carrot for people to substantiate just how much work they've done.

Describe alternatives you've considered

1. For anything in the marts or tests - take a snapshot. This feels like too much data. I'll never get into the weeds with which models had a PK a quarter ago.
2. Capture a summary of each test into a singular table each time this runs.
3. Maybe this should just be a feature for Explorer.
4. Maybe not - is this just overkill to satisfy people who like #s?

Additional context

This is looking for ways to track progress - becomes teams aren't making much progress.
It should help managers & governance figure out if progress is being made

Who will this benefit?

Devs - they deserve more credit for the hard work they do.

Are you interested in contributing this feature?

I sure am!

[b-per]

Yes, that would be a great way to use the package.

But, I am wondering, should this be baked into this project itself?

Or is too specific to individual use cases and means that we can't provide a single generic solution to it.

Or maybe we just provide an example of query for people to create themselves?

My initial thought would be that someone could create an incremental model (or a snapshot) in their own project that would query whatever fct_... table of this package, and add info about the date, the project etc...

It could be:

-- model dbt_project_evaluator_hist

{{
    config(
        materialized='incremental',
        full_refresh = false
    )
}}


with num_issues_fct_direct_join_to_source as (
select count(*) as cnt from {{ ref("fct_direct_join_to_source") }} 
)

, num_issues_fct_staging_dependent_on_marts_or_intermediate as (
select count(*) as cnt from {{ ref("fct_staging_dependent_on_marts_or_intermediate") }} 
)

...

select 
  {{ run_started_at }} as run_started_at,
  {{ project_name }} as dbt_project_name,
  {{ env_var('DBT_CLOUD_PROJECT_ID','') }} as dbt_cloud_project_id,
  {{ env_var('DBT_CLOUD_PR_ID','') }} as dbt_cloud_pr_id,
...
  fct_direct_join_to_source.cnt as cnt_issues_fct_direct_join_to_source,
  fct_staging_dependent_on_marts_or_intermediate.cnt as cnt_issues_fct_staging_dependent_on_marts_or_intermediate,
...
from num_issues_fct_direct_join_to_source cross join num_issues_fct_staging_dependent_on_marts_or_intermediate

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[LBrummer]

Hey, just wanted to comment that I found your proposal very useful and I ended up building a model that gets executed on a schedule to track the stats over time.
It is pretty generic, so maybe it turns out to be useful to someone else in the future. Any feedback welcome!

/* model dbt_project_evaluator_stats_over_time
   This model collects weekly dbt project evaluator results to track evolution over time */

{{
    config(
        materialized='incremental',
        unique_key='measured_at',
        full_refresh = false
    )
}}

/* Get counts for all dbt project evaluator fact tables */
with issues as (
/* issues */
    select

        /* modeling issues */
        count(*) as cnt_direct_join_to_source,
        (select count(*) from {{ ref("fct_duplicate_sources") }}) as cnt_duplicate_sources,
        (select count(*) from {{ ref("fct_hard_coded_references") }}) as cnt_hard_coded_references,
        (select count(*) from {{ ref("fct_marts_or_intermediate_dependent_on_source") }}) as cnt_marts_or_intermediate_dependent_on_source,
        (select count(*) from {{ ref("fct_model_fanout") }}) as cnt_model_fanout,
        (select count(*) from {{ ref("fct_multiple_sources_joined") }}) as cnt_multiple_sources_joined,
        (select count(*) from {{ ref("fct_rejoining_of_upstream_concepts") }}) as cnt_rejoining_of_upstream_concepts,
        (select count(*) from {{ ref("fct_root_models") }}) as cnt_root_models,
        (select count(*) from {{ ref("fct_source_fanout") }}) as cnt_source_fanout,
        (select count(*) from {{ ref("fct_staging_dependent_on_marts_or_intermediate") }}) as cnt_staging_dependent_on_marts_or_intermediate,
        (select count(*) from {{ ref("fct_staging_dependent_on_staging") }}) as cnt_staging_dependent_on_staging,
        (select count(*) from {{ ref("fct_too_many_joins") }}) as cnt_too_many_joins,
        (select count(*) from {{ ref("fct_unused_sources") }}) as cnt_unused_sources,
        /* documentation issues */
        (select count(*) from {{ ref("fct_undocumented_models") }}) as cnt_undocumented_models,
        (select count(*) from {{ ref("fct_undocumented_source_tables") }}) as cnt_undocumented_source_tables,
        (select count(*) from {{ ref("fct_undocumented_sources") }}) as cnt_undocumented_sources,
        /* governance issues */
        (select count(*) from {{ ref("fct_exposures_dependent_on_private_models") }}) as cnt_exposures_dependent_on_private_models,
        (select count(*) from {{ ref("fct_public_models_without_contract") }}) as cnt_public_models_without_contract,
        (select count(*) from {{ ref("fct_undocumented_public_models") }}) as cnt_undocumented_public_models,
        /* performance issues */
        (select count(*) from {{ ref("fct_chained_views_dependencies") }}) as cnt_chained_views_dependencies,
        (select count(*) from {{ ref("fct_exposure_parents_materializations") }}) as cnt_exposure_parents_materializations,
        /* Structure issues */
        (select count(*) from {{ ref("fct_model_directories") }}) as cnt_model_directories,
        (select count(*) from {{ ref("fct_model_naming_conventions") }}) as cnt_model_naming_conventions,
        (select count(*) from {{ ref("fct_source_directories") }}) as cnt_source_directories,
        (select count(*) from {{ ref("fct_test_directories") }}) as cnt_test_directories,
        /* testing issues */
        (select count(*) from {{ ref("fct_missing_primary_key_tests") }}) as cnt_missing_primary_key_tests,
        (select count(*) from {{ ref("fct_sources_without_freshness") }}) as cnt_sources_without_freshness
    from {{ ref("fct_direct_join_to_source") }}
)


select
    /* current_timestamp() as data_collected_at, */
    fct_test_coverage.measured_at,
    fct_test_coverage.total_models,
    fct_test_coverage.total_tests,
    fct_test_coverage.tested_models,
    fct_test_coverage.test_coverage_pct,
    fct_test_coverage.stg_test_coverage_pct,
    fct_test_coverage.int_test_coverage_pct,
    fct_test_coverage.mart_test_coverage_pct,
    fct_test_coverage.metric_test_coverage_pct,
    fct_test_coverage.monitor_test_coverage_pct,
    fct_test_coverage.raw_test_coverage_pct,
    fct_test_coverage.test_to_model_ratio,

    /* documentation issues */
    fct_documentation_coverage.documented_models,
    fct_documentation_coverage.documentation_coverage_pct,
    fct_documentation_coverage.stg_documentation_coverage_pct,
    fct_documentation_coverage.int_documentation_coverage_pct,
    fct_documentation_coverage.mart_documentation_coverage_pct,
    fct_documentation_coverage.metric_documentation_coverage_pct,
    fct_documentation_coverage.monitor_documentation_coverage_pct,
    fct_documentation_coverage.raw_documentation_coverage_pct,
    issues.cnt_undocumented_models,
    issues.cnt_undocumented_source_tables,
    issues.cnt_undocumented_sources,
    issues.cnt_chained_views_dependencies,

    /* Performance issues */
    issues.cnt_exposure_parents_materializations,
    issues.cnt_direct_join_to_source,

    /* modeling issues */
    issues.cnt_duplicate_sources,
    issues.cnt_hard_coded_references,
    issues.cnt_marts_or_intermediate_dependent_on_source,
    issues.cnt_model_fanout,
    issues.cnt_multiple_sources_joined,
    issues.cnt_rejoining_of_upstream_concepts,
    issues.cnt_root_models,
    issues.cnt_source_fanout,
    issues.cnt_staging_dependent_on_marts_or_intermediate,
    issues.cnt_staging_dependent_on_staging,
    issues.cnt_too_many_joins,
    issues.cnt_unused_sources,
    issues.cnt_exposures_dependent_on_private_models,

    /* Governance issues */
    issues.cnt_public_models_without_contract,
    issues.cnt_undocumented_public_models,
    issues.cnt_model_directories,

    /* Structure issues */
    issues.cnt_model_naming_conventions,
    issues.cnt_source_directories,
    issues.cnt_test_directories,
    issues.cnt_sources_without_freshness,

    /* Testing issues */
    issues.cnt_missing_primary_key_tests,
    fct_documentation_coverage.total_models - fct_documentation_coverage.documented_models as undocumented_models,

    /* Summary metrics */
    (
        issues.cnt_direct_join_to_source
        + issues.cnt_duplicate_sources
        + issues.cnt_hard_coded_references
        + issues.cnt_marts_or_intermediate_dependent_on_source
        + issues.cnt_model_fanout
        + issues.cnt_multiple_sources_joined
        + issues.cnt_rejoining_of_upstream_concepts
        + issues.cnt_root_models
        + issues.cnt_source_fanout
        + issues.cnt_staging_dependent_on_marts_or_intermediate
        + issues.cnt_staging_dependent_on_staging
        + issues.cnt_too_many_joins
        + issues.cnt_unused_sources
    ) as total_modeling_issues,

    (
        issues.cnt_undocumented_models
        + issues.cnt_undocumented_source_tables
        + issues.cnt_undocumented_sources
    ) as total_documentation_issues,

    (
        issues.cnt_exposures_dependent_on_private_models
        + issues.cnt_public_models_without_contract
        + issues.cnt_undocumented_public_models
    ) as total_governance_issues,

    (
        issues.cnt_chained_views_dependencies
        + issues.cnt_exposure_parents_materializations
    ) as total_performance_issues,

    (
        issues.cnt_model_directories
        + issues.cnt_model_naming_conventions
        + issues.cnt_source_directories
        + issues.cnt_test_directories
    ) as total_structure_issues,
    (
        issues.cnt_missing_primary_key_tests
        + issues.cnt_sources_without_freshness
    ) as total_testing_issues

from {{ ref("fct_test_coverage") }} as fct_test_coverage
cross join {{ ref("fct_documentation_coverage") }} as fct_documentation_coverage
cross join issues


{% if is_incremental() %}
    where fct_test_coverage.measured_at > (
        select measured_at from {{ this }}
        order by measured_at desc limit 1
    )
{% endif %}