Null, Empty, or Missing? Define Field States Before Data Crosses Systems

A missing field, an explicit null, an empty string, and a value such as unknown may look interchangeable in a spreadsheet. They are not interchangeable once data moves through an API, database, CRM, survey export, or reporting workflow.

When teams do not define these states, one system can interpret “no value” as “leave the old value alone,” while another interprets it as “clear the field.” A report may count an empty string as a real response. A sync may turn an unavailable date into a fake default. The result is not merely messy data. It is a workflow that cannot reliably explain what happened.

JSON Schema makes one part of this distinction explicit: null is a data type, and it is not equivalent to a property being absent. Its required keyword also governs whether a property must be present, not whether its value is non-null. See the JSON Schema guidance on null and required properties for the formal behavior. The broader implementation lesson is a DigitalWerks recommendation: define field states as part of the data contract before you map fields between systems.

Four states that need four decisions

There is no universal meaning for each state across every platform. The important decision is to make the meaning explicit for each field and each direction of travel.

Missing often means “do not change”

Consider a contact update that contains a name and email address but does not include a phone property. One receiver may interpret that as “the sender has no phone number.” Another may interpret it as “phone is outside the scope of this update,” so the existing phone number remains untouched.

Both behaviors can be reasonable. The failure comes when the integration assumes one and the destination implements the other.

For partial updates, document whether omitted properties are ignored, cleared, defaulted, or rejected. Then test that behavior against a record that already has a value. A test that starts with an empty destination record cannot reveal an accidental clear or an accidental preserve.

null can be an instruction

In many systems, explicit null communicates something stronger than absence: “this field is intentionally empty.” That can be useful when a user removes a phone number, a CRM owner is intentionally unassigned, or a source system has confirmed that a date is not available.

It can also be dangerous. A connector that translates every null into a blank string may lose the difference between “confirmed empty” and “not evaluated.” A destination that rejects nulls may cause the whole record to fail. A database update that treats null as a clear command may erase a value that the source never intended to touch.

For every nullable field, define the allowed states and the write behavior. A simple contract might say: “The property may be omitted for no change. The property may be null only to clear the value. A string must contain a normalized phone number.” That sentence is more valuable than a vague field label such as “phone, optional.”

Empty strings create false agreement

Empty strings are especially common at the boundary between forms, spreadsheets, CSV files, and APIs. A form export may represent an unanswered text question as "". A CRM may store the same concept as null. A reporting query may count both as present because the column is not technically null.

Do not normalize empty strings globally without understanding the field. For a free-text comment, an empty string may reasonably become null. For a status field, empty string should probably be rejected. For an address line, an empty second line is normal while an empty country may be a validation failure.

Normalization belongs in a field-specific mapping layer. Record the original value, the normalized value, the rule that was applied, and whether the transformation was accepted, corrected, or quarantined.

Placeholders are values, not absence

Values such as unknown, N/A, not applicable, or pending are often introduced to help people complete a spreadsheet or form. Once they enter a system, they become ordinary strings unless the schema gives them special meaning.

That creates familiar problems. A report groups “unknown” as though it were a customer segment. A search returns placeholder records. An email campaign treats not provided as a usable field. A downstream system rejects N/A because it expects an ISO date.

If a placeholder has business meaning, model it as an enum or separate status field. For example, use phone_value plus phone_status with states such as supplied, intentionally blank, not collected, and pending review. If the meaning does not deserve its own state, reject the placeholder at the boundary and route it for correction.

Define the contract at both ends

A reliable field contract answers more than “what type is this?” For each field, document:

• Whether the property is required on create, update, response, or export.
• Whether omission means no change, not collected, or an invalid request.
• Whether null is allowed and whether it clears the destination.
• Whether empty strings are accepted, normalized, or rejected.
• Which placeholder values are prohibited or mapped to a controlled enum.
• Which system owns the field and which systems may update it.
• How the value appears in logs, reports, exports, and retry payloads.

Keep the contract close to the code, schema, mapping table, or integration documentation that implements it. A field dictionary in a separate spreadsheet is useful for review, but it should not be the only place where the rule exists.

Test state transitions, not just valid records

Most integration tests prove that a complete record can travel from A to B. Data quality failures appear in transitions: a known phone is removed, a pending date becomes known, an optional field is omitted in a partial update, or a placeholder is replaced with a real value.

Build a matrix that starts with a destination record in each meaningful state. Then send each supported source state and verify the outcome. At minimum, test:

• Missing property against an existing destination value.
• Explicit null against an existing destination value.
• Empty string against a required and an optional text field.
• Prohibited placeholder against a normalizable field.
• Valid replacement after a previous null or placeholder.
• Retry of the same payload after a partial failure.

Check more than the HTTP response. Inspect the destination record, the stored raw payload, the transformed value, the error or quarantine path, and any report or notification built from the result. An accepted request can still produce the wrong state.

Make ambiguity visible to operators

When a record is rejected or transformed, the log should identify the field, the original state, the normalized state, the rule, the destination, and the stable record identifier. Avoid logging sensitive values when the field contains personal information. It is often enough to log the field name, a state label, and a redacted correlation ID.

Monitoring should separate expected empty states from unexpected data loss. A rise in “not collected” may be a campaign design issue. A rise in “invalid placeholder” may be a form or import problem. A rise in “property omitted” may indicate an API version change. Those are different operational conversations and should not be collapsed into one generic “sync error” count.

Use a state model before adding another mapping

When a field behaves inconsistently, adding another one-off conversion often hides the problem for one workflow and creates a new discrepancy elsewhere. Start by naming the states, assigning ownership, defining the write behavior, and testing the transitions. Then implement the mapping once at a controlled boundary.

DigitalWerks can review a data contract, form or import workflow, API mapping, and downstream reporting path together. The goal is to make “no value” an intentional, observable state instead of a guess that each system interprets differently.

For a related implementation example, see why CRM imports need a staging layer and why an accepted API request is not the same as a successful data sync.

Sources: JSON Schema null type; JSON Schema required properties.