Adapter Feature Support

[peterallenwebb]

Context

We would like to implement a mechanism by which dbt adapters can indicate feature support. The new mechanism will allow dbt-core to check whether an adapter is "aware" of a given feature, whether the underlying warehouse/DBMS supports it, and whether the currently connected user is able to use it in their current role.

This work is motivated by our desire to support new features without requiring adapter maintainers to update their implementations for every new dbt-core version, especially when the underlying warehouse/DBMS does not support or would not benefit from a new feature in dbt-core.

In the short term, we plan to use this mechanism to check for support of partial catalog retrieval and also metadata-driven freshness, but we believe it will continue to be important for new dbt-core features. It will also allow us to automatically generate adapter-specific documentation in a standard format.

Design Requirements

Even without instantiating an adapter object, it should be possible to check:

1. Whether the adapter implementation is "aware" of a feature and can therefore provide further information, like...
2. Whether the warehouse/DBMS supports the feature
3. The earliest warehouse/DBMS version with support for the feature

For an instantiated adapter with an active connection, it should be also be possible to check:

1. Whether the currently connected database supports the feature
2. Whether the currently connected user, in their current role, can use the feature (i.e. has adequate permissions)

A provisional design is described below for discussion.

Suggested Design

A new enumeration of optional features will be added to base.py:

class AdapterFeature(str, Enum):
    """Enumeration of optional adapter features which can be probed using BaseAdapter.has_feature()"""

    CatalogByRelations = "CatalogByRelations"
    """Indicates support for retrieving catalog information using a list of relations, rather than always retrieving all
    the relations in a schema """

    MetadataFreshness = "MetadataFreshness"
    """Indicates support for determining freshness by querying database metadata"""

To support requirements 1-3 above, a new static method will be added to base.py:

@classmethod
def feature_support(cls) -> List[FeatureSupport]:
    ... # Base adapter will return []

@dataclass
class FeatureSupport:
	feature: AdapterFeature
	adapter_support: bool  # Whether or not the adapter supports the feature 
	dbms_support: Optional[bool]  # Whether or not the DBMS/warehouse supports the feature
	first_version: Optional[str]  # The first DBMS/Warehouse version supporting this feature

	def __bool__(self):
	     return self.adapter_support and dbms_support

Providing a way to statically list supported features will allow us to easily generate documentation for adapters, without a connection.

In order to support the dynamic features, an implementation method will be added to base.py:

def feature_check(self, feature: AdapterFeature) -> FeatureCheck:
    # Throw if adapter is not connected
    ... # Base adapter will always return a FeatureCheck with adapter_support = None (adapter is unaware of the feature)

@dataclass
class FeatureCheck:
	feature: AdapterFeature  # The feature being checked
	adapter_support: Optional[bool]  # Same as in FeatureSupport
	dbms_support: Optional[bool]  # Same as in FeatureSupport
	first_version: Optional[str]  # Same as in FeatureSupport
	version_support: Optional[bool]  # Whether or not the connected warehouse/DBMS version supports this feature
        database_support: Optional[bool] # Whether or not the active db supports this feature
	user_support: Optional[bool]  # Whether or not the current user can access the feature


	def __bool__(self):
	    ...  # Return True if current connection/user/role supports feature, False otherwise

Providing a way to dynamically determine feature support will allow us to provide informative error messages when the user attempts to use a feature which is not available at runtime.

[colin-rogers-dbt]

Is AdapterFeature an enum representing all supported adapter features or an object meant to represent a specific feature?

[peterallenwebb]

It's an enum of all the features dbt-core has added.

[MichelleArk]

The delineation between static and dynamic feature support in this proposed design is neat! It should make it easy to generate static (albiet crude) documentation, while also providing more granular feature support information at runtime.

My only reservation with this design is how flat it is - it may be necessary to create a large number of AdapterFeatures to represent more nuanced levels of support. For example, in order to migrate how static constraint support is currently implemented, I imagine we'd need to introduce an AdapterFeature for each combination of ConstraintType and enforcement support:

Current implementation

CONSTRAINT_SUPPORT = {
        ConstraintType.check: ConstraintSupport.NOT_SUPPORTED,
        ConstraintType.not_null: ConstraintSupport.ENFORCED,
        ConstraintType.unique: ConstraintSupport.NOT_ENFORCED,
        ConstraintType.primary_key: ConstraintSupport.NOT_ENFORCED,
        ConstraintType.foreign_key: ConstraintSupport.ENFORCED,
    }

AdapterFeature implementation

class AdapterFeature(str, Enum):
    """Enumeration of optional adapter features which can be probed using BaseAdapter.has_feature()"""

    ...

    ConstraintCheckSupported = "ConstraintCheckSupported"  #adapter_support: false
    """Flags support for providing a 'check' constraint in DDL"""

   ConstraintCheckEnforced = "ConstraintCheckEnforced" #adapter_support: false
    """Flags support for enforcing a 'check' constraint provided in DDL"""

   ConstraintNotNullSupported = "ConstraintNotNullSupported" #adapter_support: true

   ConstraintNotNullEnforced = "ConstraintNotNullEnforced" #adapter_support: true

   ConstraintUniqueSupported = "ConstraintUniqueSupported" #adapter_support: true
   
   ConstraintUniqueEnforced = "ConstraintUniqueEnforced" #adapter_support: false
   ...

This might actually be a good thing in its simplicity - there probably isn't a neat abstraction over all the various feature types and support granularity. That said, we could consider making adapter_support an enum in order to leave some design space for representing more granular levels of support beyond 'true' and 'false'.

[colin-rogers-dbt]

there probably isn't a neat abstraction over all the various feature types and support granularity.

I wish there was a neat way to allow for recursion in enums (i.e. allow a field of AdapterFeature to be a AdapterFeature)

[colin-rogers-dbt]

Overall this is a fantastic idea we just need to be really clear about what a "feature" is here. I think we need to distinguish between database capabilities (i.e. what features the underlying platform supports) and dbt feature support. Perhaps we should have parallel mechanisms to track or perhaps we can infer dbt feature support from the database capabilities.

This can be seen in the two "features" provided as they blur these lines. For example, "freshness" isn't really a db concept and will probably be confusing for adapter maintainers and will probably require them to understand the dbt feature to answer. Is there a more concrete database capability or capabilities we could track?

[peterallenwebb]

These are really good points and I'm trying to figure out how to integrate them before 1.7.0.

I especially agree that the term "freshness" really shouldn't appear in an adapter implementation. (It actually already does because of the collect_freshness() macro, but you've convinced me we shouldn't go further in that direction.)

I'll add you to any review that includes changes to this mechanism.

[peterallenwebb]

For the initial work in 1.7.0, what do y'all think about this revision? I'm leaving the dynamic checks out entirely for now, but still think we will want them eventually.

Revised Suggested Design

Everything below replaces the original design I posted.

Enumeration of capabilities will be added to base.py:

class Capability(str, Enum):
    """Enumeration of capabilities which can be probed using BaseAdapter.capability_support()"""

    SchemaMetadataByRelations = "SchemaMetadataByRelations"
    """Indicates efficient support for retrieving schema metadata for a list of relations, rather than always retrieving all
    the relations in a schema """

    TableLastModifiedMetadata = "TableLastModifiedMetadata"
    """Indicates support for determining the time of the last table modification by querying database metadata"""

A second enumeration of the level of support:

class Support(str, Enum):

    Unknown = "Unknown"
    """The adapter has not declared whether this capability is a feature of the underlying DBMS.""""

    Unsupported = "Unsupported"
    """This capability is not possible with the underlying DBMS, so the adapter does not implement related macros."""

    NotImplemented = "NotImplemented"
    """This capability is available in the underlying DBMS, but support has not yet been implemented in the adapter."""
   
    Versioned = "Versioned"
     """Some versions of the DBMS supported by the adapter support this capability and the adapter has implemented any macros needed to use it."""

    Full = "Full"
    """All versions of the DBMS supported by the adapter support this capability and the adapter has implemented any macros needed to use it."""

Sketch of the remaining implementation:

@classmethod
def capability_support(cls) -> List[CapabilitySupport]:
    ... # Base adapter will return []

@dataclass
class CapabilitySupport:
	capability: DatabaseCapability
        support: Support
	first_version: Optional[str]  # The first DBMS/Warehouse version supporting this capability, if it is not available in all versoins.

	def __bool__(self):
	     return self.capability in [ Support.Supported, Support.Versioned ]

[colin-rogers-dbt]

I think this is a great path forward

[MichelleArk]

Agreed!