Merging dbt source yaml files to preserve existing elements, add new tables, columns

[davesgonechina]

The data transformation tool dbt uses source.yaml files to expose, document and test raw data tables. While there is tooling that will generate fresh yaml that includes any schema changes since it last ran, the original file accumulates additional yaml elements that are typically added manually such as description, tests, etc.

I have been unable to find the right combination of yq parameters to merge the old file (with accumulated nested metdata) and the new file (that has tables and columns that no longer exist). Either the result is the same as the old file, or it is the same as the new file. I have tried various combinations of:

yq ea 'select(fileIndex == 0) * select(fileIndex == 1)' original.yaml update.yaml --results == update.yaml
yq ea 'select(fileIndex == 0) *? select(fileIndex == 1)' update.yaml original.yaml --results == original.yaml
yq ea 'select(fileIndex == 0) *n select(fileIndex == 1)' update.yaml original.yaml --results == update.yaml

For the moment, I am not concerned with changes such as deleted tables or columns (that would be a great bonus), and I'm only concerned with the tables: section of this yaml format.

So for example if I have the following where the original has two tables with descriptive metadata, and then I have an update yaml file that has a new table customers_orders and there's also a new column added to orders called creation_timestamp.

Appreciate any help!

original.yaml

version: 2

sources:
  - name: jaffle_shop
    description: This is a replica of the Postgres database used by our app
    tables:
      - name: customers
        description: One record per customer.
        columns:
          - name: id
            description: Primary key of the customers table
            tests:
              - unique
              - not_null
          - name: email_address
            description: customer email address.
      - name: orders
        description: One record per order. Includes cancelled and deleted orders.
        columns:
          - name: id
            description: Primary key of the orders table
            tests:
              - unique
              - not_null
          - name: status
            description: Note that the status can change over time

update.yaml

version: 2

sources:
  - name: jaffle_shop
    description: This is a replica of the Postgres database used by our app
    tables:
      - name: customers
        columns:
          - name: id
          - name: email_address
      - name: customers_orders
        columns:
          - name: customer_id
          - name: order_id
      - name: orders
        columns:
          - name: id
          - name: status
          - name: creation_timestamp

merged.yaml

version: 2

sources:
  - name: jaffle_shop
    description: This is a replica of the Postgres database used by our app
    tables:
      - name: customers
        description: One record per customer.
        columns:
          - name: id
            description: Primary key of the customers table
            tests:
              - unique
              - not_null
          - name: email_address
            description: customer email address.
      - name: customers_orders
        columns:
          - name: customer_id
          - name: order_id
      - name: orders
        description: One record per order. Includes cancelled and deleted orders.
        columns:
          - name: id
            description: Primary key of the orders table
            tests:
              - unique
              - not_null
          - name: status
            description: Note that the status can change over time
          - name: creation_timestamp

[mikefarah]

Ok I understand what you are trying to do. The yq merge operator however is a naive merge, it has no knowledge of your datastructure (and does not know, for instance, that the 'name' field uniquely identifies entries). The naive merge just merges the data structures matching on the yaml key name, and arrays are either appended together, or merged together by their index position.

That said, there is a more complex merge that you can use this script here: https://mikefarah.gitbook.io/yq/operators/multiply-merge#merge-arrays-of-objects-together-matching-on-a-key

However this only does a single level of merge, where you actually need two; one on the table name, and then again on the matching column. I think you can still do this in yq by extending that script...could get hairy though

[davesgonechina]

@mikefarah cheers for the quick reply - I've had some success with yamlpath's deep merge on Arrays-of-Hashes.