dbt-labs / dbt-labs/dbt-audit-helper
Add Postgres adapter for `quick_are_queries_identical` and `quick_are_queries_identical`
- Dominant language
- No language data
- Stars
- 423
- Forks
- 51
- PR merge metrics
- No merged PRs in 30d
Description
### Describe the feature
Implement a Postgres implementation of `quick_are_queries_identical` and `quick_are_queries_identical` so that Postgres projects can use these macros.
### Describe alternatives you've considered
There are multiple strategies involving hashing that can be used to assert if two tables have exactly the same contents. These include either performing row-level hashes and comparing them across both queries/relations (`EXCEPT` could be used) or even just summarizing the whole table into a single hash and comparing those.
See a silly example below that will compare two tables. Note that this is not the implemention I would add to this package. It would be missing some wrapper to hammer it into the right output format.
```sql
with query_1 as (
select 1,2,3
),
query_2 as (
select 1,2,3
)
SELECT md5(string_agg(t::text, ',' ORDER BY 1)) FROM query_1 t
EXCEPT
SELECT md5(string_agg(t::text, ',' ORDER BY 1)) FROM query_2 t;
-- will return no rows
with query_1 as (
select 1,2,3
),
query_2 as (
select 3,2,1
)
SELECT md5(string_agg(t::text, ',' ORDER BY 1)) FROM query_1 t
EXCEPT
SELECT md5(string_agg(t::text, ',' ORDER BY 1)) FROM query_2 t;
-- will return one row with the hash of the unmatched row in query_1
```
### Additional context
Yes, it is Postgres specific.
### Who will this benefit?
Postgres projects.
### Are you interested in contributing this feature?
I would be happy to give it a shot if the project maintainers can solve these for me:
- Is there a strict benchmark for how _quick_ does this need to be deemed acceptable?
- Could you let me know your expectations regarding testing and docs for the PR? Couldn't find any standard regarding this on the repo.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.