Adapter Maintainers: Upgrading to dbt-core 1.2.0

[dataders]

This discussion is for communicating to adapter maintainers the scope of work needed to make use of the changes in 1.2.0. If you have questions and concerns, please ask them here for posterity.

The first release cut for 1.2.0, #dbt-core==1.2.0rc1 was published two days ago (PyPI | Github). We are targeting a second release cut in the next week with a target of releasing the official cut of 1.2.0 by the end of the month. The next release cut will contain likely only contain some smaller changes that will not impact end users (though perhaps #5432 if we're lucky). UPDATE 7/22: #5432 has been merged, so see the new section below on implementing connection retries.

This minor release contains no breaking changes, but has both new features for end-users and some developer quality-of-life improvements that will benefit most adapter maintainers. This means that end users can install run dbt-core==1.2.0 alongside an adapter of a previous minor version (e.g. 1.1.0 or 1.0.0), but they won't necessarily have access to the new features.

Connection Retries

#5432 adds a new method to the BaseAdapter, retry_connection() (source), that allows an adapter to automatically try again when the attempt to open a new a connection on the database has a transient, infrequent error. The option can be set using the retries config in the target of a ~/.dbt/profiles.yml

If relevant to your adapter, you'll need to implement the following changes in your adapters connections.py:

1. Add the retries value to the connection manager class along with it's default number of times to retry (likely: 1?)
2. in the ConnectionManager.open() method:
   1. an intra-method function: connect() (postgres's implementation) that returns a handle object. A handle is the object returned by the standard python DB2 API Cursor object immediately after attempting to connect
   2. a list of retryable_exceptions of exceptions (dbt-postgres' version) sthe underlying driver might expose.
   3. the return value of the .open() method is now a call to cls.retry_connection()` (dbt-postgres's version)

Cross-database Macros

In #5298, we migrated a collection of "cross-database macros" from dbt-utils to dbt-core. Default implementations are automatically inherited by adapters and included in the testing suite. Adapter maintainers may need to override the implementation of one or more macros to align with database-specific syntax or optimize performance. For details on the testing suite, see: "Testing a new adapter".

The TL;DR rationale for this work is:

1. Simplify dbt-utils development
2. Allow some packages to no longer depend on dbt-utils as a package
3. Provide adapter maintainers tests that can but used in the adapter repo CI, as opposed to in a shim package

As for how to make it happen, looking at the following PRs for dbt-Labs-maintained adapters show it clearly:

• Lift + shift for cross-db macros dbt-bigquery#192
• Lift + shift for cross-db macros dbt-redshift#120
• Lift + shift for cross-db macros dbt-snowflake#162

Grants

Managing access grants is one of the most asked for features from dbt users. We’re delivering this capability, but naturally there’s variance across data platforms as to how grants work, so time for adapter maintainers to roll their sleeves up. You might get lucky and not have to override any of them, but in case you do, below are descriptions of the new methods and macros, grouped into level of complexity (start with the easy ones first!)

💡 Note 💡

This new functionality does not add users, only grants access. You'll have to handle adding users elsewhere, and it has implications for the GRANT adapter tests.

Pull requests for adding grants for dbt Labs-maintained adapters should be very useful as a reference, for example: dbt-labs/dbt-bigquery#212.

Override-able macros and methods

The two macros below are simple Boolean-toggles (i.e. True/False value) indicating whether certain features are available for your database. The default of both of these macros are True, because we believe that all databases should support these ergonomic features. However, we've built for flexibility, so overriding these macros for your adapter, will handle the case where your database doesn't support these features.

macro	description	global project’s default	example override
copy_grants()	when an object is fully replaced on your database, do grants copy over? e.g. on Postgres this is never true, on Spark this is different for views vs. non-Delta tables vs. Delta tables, on Snowflake it depends on the user-supplied copy_grants configuration. true by default, which means “play it safe”: grants MIGHT have copied over, so dbt will run an extra query to check them + calculate diffs.	default__copy_grants()	snowflake__copy_grants()
support_multiple_grantees- _per_dcl_statement()	does this database support grant {privilege} to user_a, user_b, ...? or do user_a + user_b need their own separate grant statements?	default__support_multiple_grantees_per_dcl_statement()	spark__support_multiple_grantees_per_dcl_statement()

If the above macros do not suffice, then at least one of these get_*_sql() macros will need to be overwritten. They're all one-liners and might need small syntax tweaks to work on your database.

macro	description	global project’s version	example override
get_show_grant_sql()	SQL that returns the CURRENT grants (privilege-grantee pairs) for a given relation	default__get_show_grant_sql()	redshift__get_show_grant_sql()
get_grant_sql()	generate a GRANT statement for a given relation given a privilege-grantee(s) pairing. grantees will be a list of grantees if supported by this database, otherwise just one.	default__get_grant_sql()	spark__get_grant_sql()
get_revoke_sql()	generate a REVOKE statement for a given relation given a privilege-grantee(s) pairing. grantees will be a list of grantees if supported by this database, otherwise just one.	default__get_revoke_sql()	bigquery__get_revoke_sql()
any custom materialization (or override of a default materialization)	you have to add the lines for fetching and applying the grants {% set grant_config = config.get('grants') %} and {% do apply_grants(target_relation, grant_config) %} by default, the should_revoke argument of apply_grants is True. dbt will first run a query to “show” grants, then calculate diffs, then apply revoke/grant statements. you can use the should_revoke macro to determine whether this extra step is necessary. in cases where dbt is fully replacing an object, or creating one for the first time, grants may not be carried over — so it may be more efficient to skip the “show” step and just add the grants.		BigQuery’s custom incremental materialization

If the above sets of macros still aren't cutting it, here's the final depth of complexity in which to wade.

macro	description	global project’s version	example override
get_dcl_statement_list()	Unpacks grant_config. For each privilege-grantee(s) pairing, call either get_grant_sql or get_revoke_sql and return a list of all needed statements	default__get_dcl_statement_list()
call_dcl_statements()	Call all DCL statements, i.e. actually run them against the database. This is the culmination of apply_grants. By default, this generates one big string (every grant/revoke statement, separated by ;), but some adapters will need to execute these differently.	default__call_dcl_statements()
Adapter.standardize_grants_dict()	Input: result from query to “show grants” Returns: a dictionary of structure {"privilege_name": [list, of, grantees], ...} —> matches the structure of the user-supplied grant_config	(this is a python method in the core/dbt/adapters/base/impl.py's BaseAdapter.standardize_grants_dict()

Testing grants with your adapter

The tests for grants are implemented in the same way as the pytest tests that were introduced in dbt-core v1.1.0, in that they are importable and can you create adapter-specific child classes of each test in your repo. for example see how dbt-bigquery implements the tests. Notice the BaseGrantsBigQuery in which the mapping dict of standard privileges to BigQuery-specific privilege names.

class BaseGrantsBigQuery(BaseGrants):
    def privilege_grantee_name_overrides(self):
        return {
            "select": "roles/bigquery.dataViewer",
            "insert": "roles/bigquery.dataEditor",
            "fake_privilege": "roles/invalid",
            "invalid_user": "user:[email protected]",
        }

It is also worth noting that in your test database, you need to have create three users. If your integration test database is persistent, you'll only need to add the users to the database once, if the database is set up and torn down within the CI testing, you'll need to have the users added as part of your CI testing (or even the docker image).

In the example test.env, the users are prescribed as environment variables as follows:

DBT_TEST_USER_1=dbt_test_role_1
DBT_TEST_USER_2=dbt_test_role_2
DBT_TEST_USER_3=dbt_test_role_3

Materialization inheritance!

Via a community contribution from the @volkangurel at Layer.ai, #5348 enables materializations to be inherited from parent adapters in much the same was as macros are dispatched.

this is a big deal for folks who are inheriting adapters, e.g. as dbt-synapse does with dbt-sqlserver, and for the family of adapters inherit from dbt-spark today.

New basic tests to implement in adapterland: BaseDocsGenerate and BaseDocsGenReferences

#5058 is another step along the path of converting all our functional tests to the new framework in order to empower adapter maintainers and other contributors to make use of the same tests that the core team uses for their own adapters. Effectively, this test is validates an adapter's ability to correctly generate the catalog that serves as the static backend of a project docs site.
If your adapter does not add extra relation-level metadata (e.g. table size (rows + bytes), last modified timestamp) which is the case by default, then you can follow the same inherit and pass pattern to enable your version of BaseDocsGenerate and BaseDocsGenReferences. However, if you are supplementing the catalog with more metadata, you'll have to:

• Add a method that defines stats for this adapter e.g. dbt-bigquerys
• Reimplement the expected_catalog fixture, passing the above into model_stats and seed_stats

Example PRs:

• Implement TestDocsGenerateBigQuery test dbt-bigquery#190
• Ct-603 docs generate test conversion dbt-redshift#116

More python functions now available in the dbt jinja context

python’s set and zip , and the most of the itertools are available in the dbt-jinja context. Yay! (#5107 and #5140). There's no explicit action needed here, only mentioning in case it enables some jinja simplifications.

Slight change to the default seed materialization

💡 Update 💡

this change didn't solve the intended problem in dbt-snowflake, so you can ignore this part. Some version of this work may show up in a future minor version.

who: folks who override the entire seed materialization, and anyone who overrides materializations for small reasons. this is a great example of how the global_project can be modified to reduce boiler plate within adapters.

what: a new macro, get_csv_sql(), was added to macros/materializations/seeds/helpers.sql

why transactions are no longer the default behavior for dbt-snowflake, however, they’re still needed for bundling the seed table creation and insertion. So now we have a new default macro so that dbt-snowflake can implement a version that makes the two statements happen in the same transaction

more info check out the issue #5206 and #5207

[jwills]

Hey @dataders is there a plan for releasing a 1.2-compatible version of dbt-tests-adapter for us to work against? The 1.1.1 version doesn't have the new Docs tests included in it.

[dataders]

great callout! I haven't tried yet, but there is a new 1.2.0rc1 release of dbt-tests-adapter. I think that https://github.com/dbt-labs/dbt-tests-adapter should probably be archived, because the actual package is published to PyPI from here: https://github.com/dbt-labs/dbt-core/tree/main/tests/adapter.

I opened dbt-labs/dbt-tests-adapter#1 to confirm my thinking. Sorry for the confusion!

[jwills]

Okay so all I had to do was pip install dbt-tests-adapter==1.2.0rc1 to get it; since it's a pre-release it didn't show up as the latest version, nbd

[jwills]

Alright, so I have a branch that is passing against 1.2.0rc1 including the new adapter tests: https://github.com/jwills/dbt-duckdb/compare/jwills_120rc1?expand=1

A couple of comments/questions:

1. Is there anything else I need to do to exercise the new cross db macros besides extending the BaseAdapterMethod test? (Also the link to "Testing a new adapter" in the top-level post is broken.) For example, looking at https://github.com/dbt-labs/dbt-core/blob/main/core/dbt/include/global_project/macros/utils/listagg.sql I'm pretty sure that's not going to work as intended on DuckDB w/o some modifications. Are there good examples of one of the Big Four ™️ modifying one of the defaults for the cross db macros and including a test of the modification you can post in here? Ahh, answered my own question-- this PR in particular was very helpful: Lift + shift for cross-db macros dbt-redshift#120

2. DuckDB doesn't support GRANTs; is there anything I should implement in the adapter to indicate that they aren't supported, or do I just rely on a user not specifying them on a project?

[jwills]

Okay, added in the cross-db macro tests as well and got all of those passing, so I think I'm ready whenever y'all are to release a 1.2.0 version.

[shiyuhang0]

If I just support dbt-core v1.2.0 directly regardless of the v1.1.0. will the adapter work well with v1.1.0

[dataders]

great question! because we follow SemVer, we aim to have no breaking changes in our minor releases. End users of your adapter will not have access to some of the new functionality such as GRANT configs.

Also, given that this is only our second attempt at a SemVer compliant minor release, we are asking adapter maintainers to cut a corresponding version release for each new core minor release.

At a bare minimum, you should adjust your install_requires in your setup.py to be something like the below See the Version Specifiers section of PEP-440 PEP 440 – Version Identification and Dependency Specification for more info e.g. the subtle differences between the following:

pip install foo>=1.2
pip install foo~=1.2.0
pip install foo~=1.2

To me, ~=1.1 will give you what you want and allow end users to have minor versions of dbt version 1.0 that are 1.1 or greater

[dataders]

I also am going to update the original post here to mention about compatibility with dbt-utils. TL;DR dbt-utils 1.0 won't work unless the adapter you are using has added the cross-db macros. However, dbt-utils <= 1.0 will stay backwards compatible

[shiyuhang0]

Another Question:
My adapter is compatible with dbt-core v1.0 now, and it uses pytest-dbt-adapter to test.
According to Testing a new adapter, pytest-dbt-adapter is deprecated. Should I use the newer testing framework when I upgrade to dbt-core 1.1 or 1.2

[dataders]

yes the new adapter testing framework is highly recommended because:

1. we've deprecated pytest-dbt-adapter, and
2. the new testing suite is much easier to use, and
3. the core team will be providing more tests as time goes on to ensure the quality of your adapter.

[codeforkjeff]

I'm working on getting dbt-sqlite working with 1.2.0.

I'm finding it difficult to troubleshoot failing tests for the cross-database macros. When tests fail due to valid SQL that produces an unexpected result, "dbt build" will indicate that the test failed but not provide any additional information. I'm able to retain the failures if I override the test_build_assert_equal method to add --store-failures (see below), but I get schemas with names like test16587138822512201497_test_utils_dbt_test__audit and table names like assert_equal_test_hash_actual__expected which make it hard to tell which test it pertains to.

from dbt.tests.util import run_dbt

class BaseDateDiff(BaseUtils):
    # actual test sequence
    def test_build_assert_equal(self, project):
        run_dbt(["build", "--store-failures"])  # seed, model, test

I'm having to do a lot of manual work loading the seed data from the fixtures, then looking at the output from pytest to find the raw SQL to paste into a database client to test.

Is there an easier way?

[dbeatty10]

A few ideas:

• If you add a -s to the pytest command, it will emit the location of the dbt project for the test like this:

  $ python3 -m pytest tests -s
  ...
  === Test project_root: /private/var/folders/dz/_ffl85j129j7t_djdnkg618c0000gp/T/pytest-of-dbeatty/pytest-325/project0
  ...
  

  Then you can copy that directory to a different location so you can perform manual testing on the project (dbt build, etc) and also see the raw SQL in the target directory
• The tests produce a logs directory in the current working directory that store the dbt.log files for each of your test invocations -- these can be useful for debugging
• All the test schemas and relations are cleaned up when the test suite is done running, but you can add a breakpoint() call in your Python code to preserve the state of the database temporarily so you can inspect it

[McKnight-42]

Like using breakpoint to enter into the pdb you could use the {{ debug() }} command to enter a debugger for the macro specifically will just have to apply a local env noted here https://docs.getdbt.com/reference/dbt-jinja-functions/debug-method which can help in finding any indepth sql issues

[codeforkjeff]

Thank you @dbeatty10 and @McKnight-42 ! These are great suggestions, I appreciate the help.

[sdebruyn]

Thank you for this overview! Would it be possible to label this post and the ones for future dbt-core versions with a label? It would make it easier as an adapter maintainer to be able to just follow all posts with that specific label instead of digging through all of the community discussions :)

[dataders]

done!

[genzgd]

If there is a particular cross database macro that just doesn't work (in this case replace in ClickHouse can only take constants for the pattern and replacement string, so using column names for those throws an exception), how do we indicate that?

[Sachin-Thakur]

`file /home/sachin/Documents/VERTICA/denv/lib/python3.10/site-packages/dbt/tests/adapter/basic/test_base.py, line 40
      def test_base(self, project):
E       **fixture 'project' not found**

>       available fixtures: cache, capfd, capfdbinary, caplog, capsys, capsysbinary, doctest_namespace, models, monkeypatch, project_config_update, pytestconfig, record_property, record_testsuite_property, record_xml_attribute, recwarn, seeds, tmp_path, tmp_path_factory, tmpdir, tmpdir_factory
>       use 'pytest --fixtures [testpath]' for help on them.
`

trying to run test cases using command ( pytest test_basic.py ) having issue of project not found

[McKnight-42]

@Sachin-Thakur which adapter are you currently running these tests on?

might need to augment your command like if this was for postgres you would run python -m pytest tests/adapter/dbt/tests/adapter/basic/test_base.py to run the functional test

[Sachin-Thakur]

@McKnight-42 We are running dbt-vertica
We have tried this command for functional test but still its the same issue coming up

[Sachin-Thakur]

please look into this it would great if you can help on this part

[McKnight-42]

hmm seems the dbt-veritica github doesn't currently have functional tests on it so can't really take a look into the code, this could be an issue with your conftest.py. do other functional tests work?

[Sachin-Thakur]

nope as of now, this issue is with all the functional tests we have defined required things in conftest.py

[nrodriguezmicrofocus]

We are in the process of making dbt-vertica compatible with dbt-core 1.2. We encountered the following issue:

run_dbt() fails when the "seed_name" var changes the seed identifier in the schema file:

TestIncrementalVertica inherits function from BaseIncremental. When this test case is executed using pytest, run_dbt() fails on condition when the "seed_name" var has to be changed as seed identifier in the schema file. Error Message: “FAILED test_basic.py::TestIncrementalVertica::test_incremental - AssertionError: dbt exit state did not match expected”.

[dataders]

Pulling info from an email thread for here. @colin-rogers-dbt @Fleid @McKnight-42 can y'all help out the Vertica folks? Thanks!

We are upgrading dbt-vertica to 1.2.2 and 1.3.0 versions. We have migrated all checklists from vertica/dbt-vertica#29 except BaseDocsGenerate and BaseDocsGenReferences.
Also, we have migrated the test framework to Pytest in which we have migrated basic tests TestGenericTests, TestSingularTests, TestSingularTestsEphemeral, and TestValidateConnection from dbt-core tests.
While migrating:
TestIncrementVertica, from BaseIncremental in dbt-core,
TestSnapshotTimestampVertica from BaseSnapshotTimestamp,
and TestSnapshotCheckColsVertica from BaseSnapshotCheckCols
We are facing blockers and files of each test while running the command pytest functional/adapter/test_basic.py on terminals and logs from the terminal are in the attached files.
We look forward for support from your end. Please let us know if any suggestions or road map which can help us to solve these blockers.

[Sachin-Thakur]

Hello team,

we are working on updating as well as enhancing the dbt-vertica adapter.

Right now, we are trying to run adapter basic test cases in that

TestSnapshotCheckColsVertica(BaseSnapshotCheckCols):
      pass

class TestSnapshotTimestampVertica(BaseSnapshotTimestamp):
      pass

which is failing and giving us AssertionError: dbt exit state did not match expected

please look for logs in below-mentioned links

https://simplify3xsoftware-my.sharepoint.com/:u:/g/personal/shilpa_c_simplify3x_com/EQRI9EiMPwFFrR_SC-DMLpYBX6DZnyFqO7YKDgP1nwV7JA?e=OvHDsF

https://simplify3xsoftware-my.sharepoint.com/:u:/g/personal/shilpa_c_simplify3x_com/Efq2G9ilAtJCgLrH-qYJw2QBJOYl5-qSvddcsBCMfO7oSg

We look forward to hearing from you soon.