Skip to content
Skills Library
Data & analytics · Building & coding

schema-null-defaults

/schema-null-defaults

Handle NULL values and set default values for missing fields in collections and materializations. Use when fields need defaults, nullable primary key errors occur, or source data has gaps. Use when user says "NULL values", "default value", "missing field", "nullable primary key error", or "fill in missing data".

Licence
Apache-2.0
Last repository change
24.09.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
1
Deprecation

No signals detected. Repository changed on 24.09.2026, without references to retired models or APIs.

Licence

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

What it does and when to use it

Handle NULL values and set default values for missing fields in collections and materializations. Use when fields need defaults, nullable primary key errors occur, or source data has gaps. Use when user says "NULL values", "default value", "missing field", "nullable primary key error", or "fill in missing data".

Triggers from the description

NULL valuesdefault valuemissing fieldnullable primary key errorfill in missing data
Original author description

Handle NULL values and set default values for missing fields in collections and materializations. Use when fields need defaults, nullable primary key errors occur, or source data has gaps. Use when user says "NULL values", "default value", "missing field", "nullable primary key error", or "fill in missing data".

How it works

  1. When to Use
  2. Prerequisites
  3. Key Concepts
  4. Procedure
  5. Common Patterns
  6. Handling Explicit NULLs
  7. Troubleshooting
  8. Related Skills
Original SKILL.md headings · 168 lines, 698 words
SKILL.md preview
---
name: schema-null-defaults
description: Handle NULL values and set default values for missing fields in collections and materializations. Use when fields need defaults, nullable primary key errors occur, or source data has gaps. Use when user says "NULL values", "default value", "missing field", "nullable primary key error", or "fill in missing data".
---

# Handle NULLs and Default Values

Handle NULL values and set default values for missing fields in materializations.

**Docs:** https://docs.estuary.dev/concepts/schemas/#default-annotations

## When to Use

- Need to provide default values for missing/optional fields
- Handling nullable primary key errors in materializations (`cannot materialize collection with nullable key field unless it has a default value annotation`)
- Ensuring consistent values when source data has gaps

## Prerequisites

- Existing collection to modify
- `flowctl` authenticated

## Key Concepts

- `default` applies only to **missing** fields, not to explicit `null` values. A field present with `null` stays `null`.
- `default` is read by materializations only — captures and derivations ignore it. Add defaults to the readSchema so auto-discover (which only touches writeSchema/connector-schema) can't overwrite them.

## Procedure

### Step 1: Pull Collection Specs

```bash
flowctl catalog pull-specs --name <tenant>/<collection-name>
```

### Step 2: Identify the Read Schema

Find the read schema file (e.g., `collection.read.schema.yaml`).

Typical structure for collections using schema inference:
```yaml
allOf:
  - $ref: flow://relaxed-write-schema
  - $ref: flow://inferred-schema
```

### Step 3: Add Default Values

Add `default` annotations at the top level of the readSchema, alongside `allOf`:

```yaml
allOf:
  - $ref: flow://relaxed-write-schema
  - $ref: flow://inferred-schema
properties:
  status:
    type: string
    default: "pending"
  count:
    type: integer
    default: 0
  is_active:
    type: boolean
    default: true
```

The `properties` block with defaults sits alongside the `allOf`, not inside it.

### Step 4: Publish Changes

```bash
flowctl catalog publish --source flow.yaml
```

## Common Patterns

### Default for Optional String Field

```yaml
allOf:
  - $ref: flow://relaxed-write-schema
  - $ref: flow://inferred-schema
properties:
  category:
    type:
      - string
      - "null"
    default: "uncategorized"
```

Note: Default only applies when field is missing. If value is explicitly `null`, it remains `null`.

### Nullable Primary Key with Default

The most common use case. SQL materializations reject key fields that are nullable unless they have a default. This often happens when the inferred schema doesn't mark the field as `required` (e.g., because it was absent in historical data).

```yaml
allOf:
  - $ref: flow://relaxed-write-schema
  - $ref: flow://inferred-schema
properties:
  year:
    default: 0
```

### Timestamp Default

```yaml
allOf:
  - $ref: flow://relaxed-write-schema
  - $ref: flow://inferred-schema
properties:
  created_at:
    type: string
    format: date-time
    default: "1970-01-01T00:00:00Z"
```

## Handling Explicit NULLs

`default` doesn't replace explicit `null` values, only missing fields. If you can't fix the source data, use a SQL derivation to replace nulls and materialize the derived collection instead:

```yaml
collections:
  tenant/derived/collection:
    schema: schema.yaml      # schema + key for the derived collection
    key: [/id]
    derive:
      using:
        sqlite: {}
      transforms:
        - name: replaceNulls
          source: tenant/source/collection
          shuffle: any
          lambda: SELECT COALESCE($status, 'unknown') AS status, $id AS id;
```

## Troubleshooting

### Default Not Applied

- Check if field is **missing** vs explicitly **null**
- Defaults only apply to missing fields
- Verify default is in **readSchema**, not just writeSchema
- Defaults are only used by materializations — ignored by captures and derivations

### Nullable PK Error in Materialization

```
cannot materialize collection 'X' with nullable key field 'Y' unless it has a default value annotation
```

- Add `default` annotation to the key field in readSchema (alongside `allOf`, not inside it)
- This can happen even if the field looks non-nullable in the current schema — the inferred schema may have seen it as nullable historically
- Alternatives: collection reset (`reset: true`) to re-infer from scratch, or add the field to `required` in the readSchema

### Schema Validation Errors After Adding Default

- Ensure default value matches the field's type
- For typed fields, default must be valid: `default: 0` for integer, not `default: "0"`

### Existing NULL Data Causing Issues

- Defaults don't transform existing null values
- Use a derivation to replace nulls (see "Handling Explicit NULLs" above)
- Or perform a Dataflow reset after fixing source data

## Related Skills

- `schema-field-selection` — Control which nullable fields materialize
- `schema-define-fields` — Define fields in the readSchema (pruned fields, type refinement, etc.). Note: that skill adds new field definitions as a third entry inside `allOf:`, while this skill places `properties:` as a peer of `allOf:`. Both are valid JSON Schema (sibling keywords are AND'd at the same level). The peer-`properties` pattern is convenient for annotation-only changes like `default`; the third-`allOf` pattern is preferred when introducing new field structure.

Versions

The database has no other skills named schema-null-defaults.

Database snapshot: 13.11.2026 · package version 3d45aacfed64 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 95csharp-nullable-types
    Use when C# nullable reference types and value types for null safety, nullable annotations, and patterns for handling null values.
  • 90absent-values
    Apply when a value can be empty or missing — an optional field, prop, column or procedure input, a `?:`, `undefined`, `null`, `.optional()`, `.nullable()`, `.default(...)`, `|| undefined`, `?? null`, or a filter dropping empties before a call. Esposter's one absent form per value.
  • 100fp-option-ref
    Quick reference for Option type. Use when user needs to handle nullable values, optional data, or wants to avoid null checks.
  • 100fp-option-ref
    Quick reference for Option type. Use when user needs to handle nullable values, optional data, or wants to avoid null checks.
  • 91csharp-nullable-types
    Use when C# nullable reference types, null safety patterns, and migration strategies. Use when ensuring null safety in C# code.
  • 100dart-null-safety
    Sound null safety in Dart — nullable vs non-nullable types, flow analysis and type promotion, why fields don't promote (and the private-final exception), the ! operator, late and late final, ??, ??=, ?., ?.., null-aware elements (?item in collections), required named parameters,…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.