Config Files

[sirupsen]

We should likely create a config file format to avoid having to create wrapper-scripts with the right bisection factor, threshold, concurrency, URIs, tables, etc.

[abhinav1912]

Using a config to define constants like bisection factor and threshold make sense. What do you mean by "URIs, tables, etc" here?

[sirupsen]

@abhinav1912 I was thinking that you'd have the ability to do e.g. data-diff config.yml, maybe something like this-ish:

---
:tables:
- :bisection_factor: 32
  :bisection_threshold: 100000
  :max_age: 8h
  :columns:
  - amount
  :key_column: id
  :tables:
  - :table: transactions
    :uri: postgres://
  - :table: TRANSACTIONS
    :uri: snowflake://
- :min_age: 1h
  :columns:
  - line_items
  :key_column: id
  :updated_column: updated_at
  :tables:
  - :table: orders
    :uri: mysql://
  - :table: orders
    :uri: presto://

[erezsh]

Part of the reason for having config files is to do away with URIs, so we can instead write something like

[my_database]
type = "postgresql"
host = "localhost"
user = "postgres"
database = "somedb"

(I lean towards a TOML format in this case)

[sirupsen]

I'm good with TOML, but you don't think we should also allow specifying table configuration too?

[erezsh]

Not at all, we should support running from a config file. I'm just saying that unpacking the URI is an important part of it.

[sirupsen]

Ahhh yes, I see what you're saying! Completely agree.

[leoebfolsom]

Hi! I was sent here from Slack. I was interested in comparing multiple tables in one command instead of a single explicitly defined table-to-table comparison. Specifically, I would like to compare all tables in one schema to all tables that have the same name in another schema. Something like a compare_all_where_table_name_matches flag.

The use case is that I'd like to test whether a change in one location of a dbt project impacts anything at all downstream. So, the code from two git branches has written to two schemas in the same (Snowflake, in our case) db.

Ideally, data-diff would use lineage to infer where impacts could possibly have occurred, but I figured that would be a further refinement of the ability to run multiple/all tables in one command.

I realize this is a substantial change from the current setup, but this would be my use case.

Happy to move my comment to a new/different issue if this is too far afield from the config file discussion.

[glebmezh]

@leoebfolsom we have a dedicated product just for that! It integrates with dbt, Github/Gitlab, CI, and your DWH to provide you with full impact analysis for any change to the dbt code. You can see the video demo here: https://www.datafold.com/

[leoebfolsom]

Nice demo! I understand that column-level lineage is part of the paid product, but what about a stupider implementation of "compare all same-named tables" not dependent on lineage? Would you be open to including that in the open source version? I understand if that's not in the road map, just want to get a sense for what might be coming.

[sirupsen]

@leoebfolsom I think we should support --all-mutual-tables to mirror --all-mutual-columns. Those two flags would, together, do what you'd like—yeah?

[leoebfolsom]

@sirupsen Yes! Thanks! I'd be happy to help if I can to contribute / move this forward.

[erezsh]

Another approach for it would be to support a -k switch (like in pytest) to collect all the tables whose names match a certain pattern.

[glebmezh]

@leoebfolsom right, but FWIW it's not just about lineage, the SaaS tool also provides you deeper analysis into diff including how the metric distributions are changing as well as a turnkey CI and dbt integration – it's optimized for regression-testing dbt code, whereas the open-source data diff really shines with validating cross-DB data consistency.

Totally support adding both --all-mutual-tables and -k pattern options to the CLI

[sirupsen]

@leoebfolsom we'd love your contribution! I'd suggest starting with --all-mutual-columns, as --all-mutual-tables will require far more changes (and has the former as a dependency anyway).

The best next step would be for you to dive into the code 🐰 and comment on the issue on the approach you plan to take, to make sure you're not wasting cycles before making it into a PR!