[Epic] Applied State (part 2)

[graciegoheen]

This epic comprises the remaining work on the Applied State initiative to support better visibility into the current state of the database in a more performant way.

dbt-core 1.8

Beta Give feedback

1. [CT-3383][Bug] Metadata freshness checks that include a filter cause dbt to hang #9078
   High Severity backport 1.7.latest bug +3
   
2. [CT-3150] [Implementation] allow source freshness to be calculated in batch, when using warehouse metadata tables #8705
   Impact: CA enhancement user docs +3
   
3. [Feature] regardless if the catalog query itself is pre-filtered or not, post-filter its results #9666
   enhancement +1
   
4. [CT-3515] [Feature] Source freshness: allow loaded_at_field set to null at table level to overwrite default value set at the source level #9320
   enhancement help_wanted +2
   

next

Beta Give feedback

1. [SPIKE] Experiment with performance of catalog queries #9506
   enhancement +1
2. [Feature] freshness tests on external tables should fail to parse loaded_at_field #9979
   enhancement freshness user docs +3

[adamcunnington-mlg]

@graciegoheen / @MichelleArk sorry for the direct Q on this but struggling to understand from docs / issue / PRs. Has the support for batch metadata collection been added for BQ specifically? I saw the general work in dbt-adapters and snowflake and redshift specific but I don't think BQ? Thank you

[jtcohen6]

@adamcunnington-mlg I believe so, yes:

• Implementation of metadata-based freshness dbt-bigquery#1060

[adamcunnington-mlg]

@jtcohen6 I think that is just the initial support and not the batch-route - which is the critical bit. Please advise - many thanks

[MichelleArk]

Hey @adamcunnington-mlg -- I've summarized some spiking done to evaluate the cost/benefit of implementing a batch-route for metadata freshness in BigQuery here: dbt-labs/dbt-bigquery#938. There are more details in the spike report, but my overall conclusion is that there isn't currently a way to implement a batch-strategy that achieves performance improvements for metadata-based source freshness given limitations of BigQuery's Python SDK.

[adamcunnington-mlg]

@MichelleArk I believe this is a missed conclusion here. I've left some details against your more comprehensive response; dbt-labs/dbt-bigquery#938 (comment)

These details are also in the original FR; #7012 (comment)

[graciegoheen]

I'm going to close this issue out since it was for our 1.8 release

[adamcunnington-mlg]

@graciegoheen I just wanted to confirm understanding - that the batch route for BQ metadata is still not implemented. Michelle completed a spike and there's a branch with changes on but it wasn't finished/merged. Is there an ETA on when that will be done? It feels like 90% of the work was done

[graciegoheen]

Hi @adamcunnington-mlg - that is correct, it is still not implemented.

@jtcohen6 left a response in the issue in dbt-bigquery back in May (see here):

If you could provide us with concrete numbers for both approaches, that would help me a lot in deciding on the appropriate next step here — to switch this behavior for everyone (strictly better), to consider implementing it as another configurable option (with both pros & cons relative to the v1.8 behavior), or to put it down for good ("do nothing").

This is not a top priority for us atm, but would be helpful to understand the difference in performance between these approaches to decide on the appropriate next step.

[graciegoheen]

Ah! @adamcunnington-mlg I see you follow up in this issue.

The implementation has been de-risked, so there's nothing blocking this work. This is not a focus for our team right now, but we would definitely review a PR for it if someone from the community wanted to open one up :)