Modeling data with nullable keys

[psFried]

Working with GTFS data has made me once again re-consider null values in primary keys. The GTFS format is basically just a zip archive with a bunch of CSVs that seem directly exported out of a database schema. Their docs include the primary keys for each file, and many of them are compound keys. Several of the tables have key components that are nullable. The transfers table is one example, and the nullable values seem to be intentionally a part of their modeling. The agencies table has a regular (not compound) primary key which is nullable, and their rationale is that many datasets will include only a single agency, and thus null is a reasonable id for it. As a user, I feel like I should be able to directly use the keys specified in their docs without needing to do any elaborate workarounds. So maybe we should re-consider our stance on nullable keys?

Another good example is the alerts feed. Each alert relates to either a route, a stop, or to both. At least one of routeId or stopId must be non-null, but Flow would not allow using either field as part of a compound key. Flow would allow using a generic non-nullable entityId and entityType pair, where entityType is one of ROUTE or STOP, so there is a potential workaround for this case. But IMO it still feels like a workaround, and I think user expectations would generally be that they can take whatever schema they have and model it directly within Flow. This point is of course open to debate, but that's how I'm seeing it at the moment.

From where I sit right now, I think it makes sense to remove the restriction that keys be non-nullable. AFAIK this restriction was only ever in place because it seemed like a potential foot-gun. But having seen a real-world use case, I now think it's better to allow users to model the data as they wish.

[jgraettinger]

🤔 doing so requires that we answer a related question: how do you compare key components of null?
This use case implies one means: that null is equal to another null, and unequal to anything else.

BUT this runs counter to 30+ years of SQL semantics which consider NULL to be incomparable with anything else.

I can certainly see the rationale here -- we used to have this behavior -- but it does get into some weird territory with SQL. At minimum, we'd probably want to update materializations to prohibit collections with nullable key components, forcing the user to create a derivation which picks some other equivalent modeling.

An argument in favor of this change is that the user at least has a pathway to get a SQL materialization working again (by creating a derivation and recasting the offending key components), whereas they don't have a way to even capture in this use case without getting pretty fancy.

[psFried]

What would you think about updating our SQL materializations to use IS NULL if the value is null? It seems easy enough to update that comparison logic in our connectors, unless you're seeing a problem there that I'm missing.

[jgraettinger]

Interesting, so said equivalently: primary keys are compared on something like where lhs.key_part is not distinct from rhs.key_part and ... rather than equality (lhs.key_part = rhs.key_part), such that null is compared with the same semantics as the runtime uses? I like this. It seems entirely doable. As you know I'm in the guts of the materialize-sql library and will explore this.

[psFried]

Another related issue is that some of their tables explicitly say there is no key. We'd talked in the past about allowing the use of /_meta/uuid as a collection key, which seems appropriate for cases where there is "no key". I seem to remember that we'd decided we should allow it, but that we still need to actually implement that. This question is possibly also relevant to #640 , since it may otherwise be difficult for connectors to output a permissive schema that has a usable key. In particular, the key [/_meta/file, /_meta/offset] can pose a problem with the source-http-file connector since the file itself is always the same, and offset resets to 0 with every invocation (we could certainly address that within the connector itself, though).

[psFried]

Following up belatedly after discussing this on VC: We're agreed that allowing nullable key components seems worthwhile, and that the semantics of nulls should match that of most programming languages (null == null is true). This would allow Flow to accept the following collection spec:

collections:
  acmeCo/with-null-key:
    schema:
      type: object
      properties:
        key: {type: [string, 'null']}
        value: {type: string}
      required: [key, value]
    key: [/key]

An open follow-up question is whether we should treat undefined in the same way. I propose that we should. Specifically, I think that Flow should also accept the following collection spec:

collections:
  acmeCo/with-undefined-key:
    schema:
      type: object
      properties:
        key: {type: string}
        value: {type: string}
      # key is not in `required` fields
    key: [/key]

Given the above spec, they key of a document that doesn't have a /key property would be null.

There's one objection that I'd like to address up front. Allowing key properties to be missing could turn typos into a massive foot-gun, since you could accidentally type key: [/keyy] and end up having all your documents map to the same (null) key. This would indeed be an easy mistake to make, and would also have massive and confusing consequences. A materialization of the collection would result in a single row with all documents reduced into one. But I think we can avoid those scenarios pretty easily using our existing build validation that all key components have a single scalar type (or two types if one is null). This validation ensures that all key components are mentioned in the schema somehow, since otherwise they would have an inferred type that allows any JSON value. So if you accidentally typed key: [/keyy], then you'd get an error naming the misspelled keyy property and saying that it's not valid. The basic rule would be that you have to name the key field in your schema, but it doesn't have to be required. Technically, you could use something like additionalProperties: { type: string }, which would essentially be opting in to the key typo footgun. IMO, this is an acceptable tradeoff, since it's purely opt-in.

The benefit is that it allows us to handle union data types by using a key with the union of keys from each type. For example, given the schema:

type: object
oneOf:
  - properties:
      purchase_id: {type: string}
    required: [purchase_id]
  - properties:
      refund_id: {type: string}
    required: [refund_id]
properties:
  amount: {type: number}

You could use key: [/purchase_id, /refund_id]. This type of thing is awkward today, to say the least. If we had the ability to use compound keys, then we could avoid using /_meta/uuid as a synthetic key in most, if not all, cases. But it would be less useful if we required that all compound key components have explicit null values in each document, since the source data may not always represent it that way.