Approach to conforming stream property names to SQL target identifier constraints

[kgpayne]

This has come up in development of target-snowflake but applies more generally to warehouse and some database targets.

The key issue is that stream property identifiers are much more permissive than database object identifiers (database, schema, table and column names). e.g.

// example record snippet
...
"record": {
  "select": "all",
  "Select": "also all",
  "I can be anything 🤦‍♂️": "doh",
  "MoreRealisticMixedCase": "this comes up a lot",
}

is perfectly valid from a Singer perspective. However:

1. select is a reserved word in many databases, requiring escaping/quoting.
2. Select != select in JSON terms, but Snowflake and Redshift are case insensitive meaning both will map to equal values if unquoted. Warehouses (at least Snowflake and Redshift) treats quoted values particularly poorly - objects created with double-quotes must always be referred to with those quotes thereafter, which leads to a terrible user experience down stream (in dbt and BI tools).
3. Spaces and non-ascii characters are permitted in JSON, but are not handled neatly (if at all) in DBMSs.
4. Camel case is insidious for Snowflake/Redshift, which are both case insensitive. SQLAlchemy correctly double-quotes mixed case identifiers, however as in 2. quoting is generally undesirable.

For these and other similar cases, commonly used targets (like pipelinewise-target-snowflake) have methods for conforming stream property identifiers according to the target systems constraints. Currently, in the SDK, no transformation is done by default; property identifiers are passed directly to SQLAlchemy, which tries to resolve any issues using quoting (which again is suboptimal in common warehouses). The only way to interject transformations is to overload all the methods that access stream properties (lots of them).

Therefore, to better support SQL target types, it would be useful to allow users to provide a conform_identifier method (or similar) as part of their target implementation that the SDK uses when creating/updating/inserting objects into the database.

References:

• Redshift constraints

  ASCII letters in standard and delimited identifiers are case-insensitive and are folded to lowercase in the database. In query results, column names are returned as lowercase by default.

• Snowflake constraints

  Unquoted object identifiers:

  • Start with a letter (A-Z, a-z) or an underscore (“_”).
  • Contain only letters, underscores, decimal digits (0-9), and dollar signs (“$”).
  • Are stored and resolved as uppercase characters (e.g. id is stored and resolved as ID).

[visch]

With the issues I've been hitting with schema(/column) name conforming, I really wonder if we should be messing with column names much. I understand potentially changing all columns to lowercase by default but anything beyond that seems like a nasty can of worms. Allow folks to override the column names in the catalog seems more clean. We could also put some of the more "crazy" column conforming stuff behind a flag like conform_schema_names_strategy with the default being lowercase, and options of lowercase, none, and smart

[edgarrmondragon]

@visch catalog approach seems interesting. I don't see anything in the metadata spec that would help with that. It does support extra fields, though.

Do you think an extension to the metadata spec would help?

[visch]

hmm, I was I was thinking like schema mappings in meltano where you can override the types of certain fields (I was wrong). Mappers with meltano could do this as well.

The config option of conform_schema_names_strategy or something like it (Would have to take into account tables, and schemas as well) seems better now