[Feature] Reduce duplicate job definitions when using import-jobs/link across multiple projects and environments

[justbldwn]

Describe the feature
We are looking to import/link jobs from multiple projects/environments to be used in a single jobs.yml file without duplicates defined. We were hoping that using the --templated-fields flag on the import-jobs command would recognize that jobs exist with duplicate names in the projects/environments we pass into the command, but instead it still creates 3 entries with 3 unique linked ids, even if the rest of the job definition is the same. If this was a feature that could be supported, that would be great.

Here is some information on how our current project structure looks:

jaffle_shop_dev

• project_id: xxx
• environment: dev
  • environment_id: 111

jaffle_shop_qa

• project_id: yyy
• environment: qa
  • environment_id: 222

jaffle_shop_prod

• project_id: zzz
• environment: prod
  • environment_id: 333

While there are 3 separate dbt projects, they are all linked to 1 repository that developers work out of and are inherently linked by being a "jaffle_shop" project. Historically in our dbt instance, developers would create a job via the dbt Cloud UI in the dev project, and then replicate the job into their qa project and prod project once deployment and promotion required it. So for example, "job1" would get created in dev, "job1" would be created in qa after testing in dev, then "job1" would finally be created in prod.

Currently, when running dbt-jobs-as-code import-jobs --account-id 000 --project-id xxx --environment-id 111 --project-id yyy --environment-id 222 --project-id zzz --environment-id 333 --include-linked-id >> jobs.yml, we'll receive an output of something like this:

# jobs.yml
  import_1:
    linked_id: 000001
    account_id: 000
    project_id: xxx
    environment_id: 111
    dbt_version:
    name: job1
    settings:
      threads: 4
      target_name: default
    execution:
      timeout_seconds: 0
    deferring_job_definition_id:
    deferring_environment_id:
    run_generate_sources: false
    execute_steps:
      - dbt build --selector mymodel
    generate_docs: false
    schedule:
      cron: 9 */12 * * 0,1,2,3,4,5,6
    triggers:
      github_webhook: false
      git_provider_webhook: false
      schedule: false
      on_merge: false
    description: ''
    run_compare_changes: false
    compare_changes_flags: --select state:modified
    job_type: other
    triggers_on_draft_pr: false
    job_completion_trigger_condition:
    custom_environment_variables: []
  import_2:
    linked_id: 000002
    account_id: 000
    project_id: yyy
    environment_id: 222
    dbt_version:
    name: job1
    settings:
      threads: 4
      target_name: default
    execution:
      timeout_seconds: 0
    deferring_job_definition_id:
    deferring_environment_id:
    run_generate_sources: false
    execute_steps:
      - dbt build --selector mymodel
    generate_docs: false
    schedule:
      cron: 9 */12 * * 0,1,2,3,4,5,6
    triggers:
      github_webhook: false
      git_provider_webhook: false
      schedule: false
      on_merge: false
    description: ''
    run_compare_changes: false
    compare_changes_flags: --select state:modified
    job_type: other
    triggers_on_draft_pr: false
    job_completion_trigger_condition:
    custom_environment_variables: []
  import_3:
    linked_id: 000003
    account_id: 000
    project_id: zzz
    environment_id: 333
    dbt_version:
    name: job1
    settings:
      threads: 4
      target_name: default
    execution:
      timeout_seconds: 0
    deferring_job_definition_id:
    deferring_environment_id:
    run_generate_sources: false
    execute_steps:
      - dbt build --selector mymodel
    generate_docs: false
    schedule:
      cron: 9 */12 * * 0,1,2,3,4,5,6
    triggers:
      github_webhook: false
      git_provider_webhook: false
      schedule: false
      on_merge: false
    description: ''
    run_compare_changes: false
    compare_changes_flags: --select state:modified
    job_type: other
    triggers_on_draft_pr: false
    job_completion_trigger_condition:
    custom_environment_variables: []

Even when using the --templated-fields flag on import-jobs, it would simply replace the hardcoded project_id and environment_id in each job with {{ project_id }} and {{ environment_id }} respectively and have 3 job definitions.

If using import-jobs with --templated-fields, what we would ideally like to see would be:

# vars_dev.yml
project_id: xxx
environment_id: 111

# vars_qa.yml
project_id: yyy
environment_id: 222

# vars_prod.yml
project_id: zzz
environment_id: 333

# templated_fields.yml
project_id: "{{ project_id }}"
environment_id: "{{ environment_id }}"

Which then running dbt-jobs-as-code import-jobs --account-id 000 --project-id xxx --environment-id 111 --project-id yyy --environment-id 222 --project-id zzz --environment-id 333 --include-linked-id >> jobs.yml would output the following:

# jobs.yml
  import_1:
    linked_id:
    - 000001
    - 000002
    - 000003
    account_id: 000
    project_id: {{ project_id }}
    environment_id: {{ environment_id }}
    dbt_version:
    name: job1
    settings:
      threads: 4
      target_name: default
    execution:
      timeout_seconds: 0
    deferring_job_definition_id:
    deferring_environment_id:
    run_generate_sources: false
    execute_steps:
      - dbt build --selector mymodel
    generate_docs: false
    schedule:
      cron: 9 */12 * * 0,1,2,3,4,5,6
    triggers:
      github_webhook: false
      git_provider_webhook: false
      schedule: false
      on_merge: false
    description: ''
    run_compare_changes: false
    compare_changes_flags: --select state:modified
    job_type: other
    triggers_on_draft_pr: false
    job_completion_trigger_condition:
    custom_environment_variables: []

Having a list of linked_id is just a proposed idea, I'm not sure if this is currently possible, but just wanted to illustrate the idea to simplify the jobs file while also having a unique linked ID for each imported job.

Describe alternatives you've considered
We've considered creating separate import files for each environment, which would hardcode the project_id/environment_id in the respective files and then a single jobs.yml for any jobs that would be created in code going forward taking advantage of the vars files for templated field injection... but it feels somewhat clunky since now jobs would need to be managed in multiple files if it was a job imported or not. Something like this as a file structure:

dbt/
  jobs/
    import/
      dev_jobs.yml
      qa_jobs.yml
      prd_jobs.yml
    jobs.yml   

Who will this benefit?
dbt users that manage multiple projects in one repository, or dbt instances that use multiple projects with a single environment (instead of 1 project with multiple environments).

Are you interested in contributing this feature?
Sure, happy to help, would just need to know if this was something that can be supported and what the best method for supporting this would be before beginning.

[b-per]

Hi @justbldwn .

All your jobs are the same in the different projects/envs, no?

If so, could you:

1. run the import command on any of the env project with the --templated-fields for project and env
2. this will create 1 version of the job
3. create different template value files for each project/env

Is specific issue about linking the jobs in dbt with the jobs in dbt-jobs-as-code?

There is also a quite niche feature that allows importing jobs to specific envs but it was developed more for people defining all their jobs in the UI in dev and then promoting those via CI/CD

[justbldwn]

Hi @b-per , thanks for the response on this.

Yes, we do have jobs that are the same across different projects/envs. I think I tried to do steps 1-3 previously to import-jobs with the --templated-fields flag, but the issue is when running link, linking the jobs in dbt with the jobs imported in dbt-jobs-as-code doesn't work because in dbt they have a unique linked_id in each project/environment.

I can test this again to verify, but to answer your overall question, I do believe that the issue is linking the imported jobs when they were originally created in each project/env.

[justbldwn]

Running a test on this again I can confirm that the issue is with linking. Since the link command requires the linked-id to be present, templating the fields for project and env doesn't exactly help because we still need to have 3 separate files with 3 separate jobs imported and linked. Feel free to correct me if I'm wrong on that, but I can't seem to figure out a way for existing jobs created in the UI to be defined once and linked to each env without dropping/recreating them.

[b-per]

Thanks for getting back with this additional context. I will try to think of a solution in the next weeks or so (maybe after Coalesce)