A possible solution I came up with would be to create another CTE that identifies staging models that reference more than one source, and then do a listagg() on the recommended paths to roll them up into a single column with a message that mentions the offending staging model should be split into separate staging models that each reference a single source. The error mess could look like this:

More than one source. Split into separate staging models in: models/staging/sales/ AND models/staging/finance/

Would your expectation be that the current suggested path still appears for the staging model (in fct_model_directores.change_file_path_to) in addition to the new message about splitting the sources? If not, then the inappropriate_subdirectories_staging CTE can be modified to JUST skip over staging models who have more than one source, and the multi-source models would have to be identified somewhere else (in a new model, perhaps). Or would you want to suppress the individual errors until the multiple sources get resolved and only present the multi-error at first? I don't think that would be ideal because then a user will fail the test, fix it, and then fail the test a second time in order to see both sets of errors.

SQL
Here are the two CTEs I propose, the output of which can unioned in the final CTE at the end:

--Add this to get all the stage models with count of parent source. Next step will be to count which ones have more than one source parent
staging_by_parent_source_count as (
    select
        count(distinct parent_source_name) as resource_count
        ,child as resource_name
        ,child_resource_type as resource_type
        ,child_model_type as model_type
        ,child_file_path as current_file_path
    from staging_models
    group by child, child_resource_type, child_model_type, child_file_path
),

--Added this CTE to listagg() the multuple suggested paths and advise the user to split the source file into those two places.
multiple_sources_staging_to_split as (
    select 
        staging_models.child as resource_name,
        staging_models.child_resource_type as resource_type,
        staging_models.child_model_type as model_type,
        staging_models.child_file_path as current_file_path,
        'More than one source. Split into separate staging models in: ' ||
          listagg('models/' || 'staging' || '/' || staging_models.parent_source_name || '/', ' AND ')
          within group(order by 'models/' || 'staging' || '/' || staging_models.parent_source_name || '/') as change_file_path_to
    from staging_models
    join staging_by_parent_source_count on staging_models.child = staging_by_parent_source_count.resource_name and
                                           staging_models.child_resource_type = staging_by_parent_source_count.resource_type and
                                           staging_models.child_model_type = staging_by_parent_source_count.model_type and
                                           staging_models.child_file_path = staging_by_parent_source_count.current_file_path
    where staging_by_parent_source_count.resource_count > 1
    group by staging_models.child, staging_models.child_resource_type, staging_models.child_model_type, staging_models.child_file_path
),

unioned as (
    select * from inappropriate_subdirectories_staging
    union all
    select * from innappropriate_subdirectories_non_staging_models
    union all --Added union all to append these results
    select * from multiple_sources_staging_to_split
)

select * from unioned;

I also found a small typo in the comments at the top of the model's query:

Where the second comment line says, "should be in nested in" there's an extra "in".

-- For staging models: The files should be in nested in the staging folder in a subfolder that matches their source parent's name.

how to handle staging models that depend on multiple sources in fct_staging_directories #111

Description

Metadata

Metadata

Assignees

Labels

Type

Fields

Projects

Milestone

Relationships

Development

Issue actions