MongoDB “Field Names Are Case Insensitive” Option in Dremio – Dremio Support

Summary/Reported Issue

Dremio provides a “Field names are case insensitive” option in MongoDB source configuration to allow querying fields regardless of case differences (for example, field, Field, FIELD). When enabled, Dremio treats all known case variations of a field as a single logical column.

This article explains how Dremio learns field name variations, how metadata refresh and sample size behave, and how Dremio resolves conflicts when multiple case variants exist, both at the top level and within nested structures.

Relevant Versions

All Dremio Active Versions

Key Behaviors and Considerations

Schema Learning and Metadata Discovery

When Field names are case insensitive is enabled:

Dremio scans the entire MongoDB collection to identify all field name variations.
There is no dedicated MongoDB API used to fetch field names; Dremio reads all documents.
For large collections, this full scan can impact performance.

Dremio does not automatically learn new field spellings introduced after initial metadata discovery. A manual metadata refresh is required to detect newly ingested case variations.

Sample Size Parameter Behavior

When case insensitivity is enabled:

The sample size parameter is completely ignored.
Dremio always scans the full dataset, regardless of:
- New spelling variations of existing fields
- Newly added columns
Any user-configured sample size value is overridden.

ALTER TABLE and Metadata Refresh Behavior

The following behavior is expected:

Running ALTER TABLE ADD COLUMN on a MongoDB source does not trigger a full rescan.
Newly added columns may appear with NULL values until metadata is refreshed.
An explicit ALTER TABLE REFRESH METADATA is required for Dremio to:
- Learn new field spellings
- Populate existing data correctly

Case Sensitivity Rules (Top-Level Fields)

When case insensitivity is disabled:

Dremio references field names exactly as defined in the schema.
Using ALTER TABLE explicitly defines the field case and overrides automatic schema learning.
If the schema is inferred implicitly, the chosen case depends on which document is sampled first.

As a result:

A top-level attribute can only be read using the case registered in metadata.
Conflicts such as a.b vs A.b may lead to inaccessible data unless metadata is adjusted.

Nested / Struct Field Behavior

For nested (struct) fields:

Dremio applies a fallback case-insensitive lookup if an exact match is not found.
This behavior applies even when the table is configured as case-sensitive.
Nested fields are therefore more tolerant of case conflicts than top-level fields.

Handling Multiple Case Variations in a Single Document

If a MongoDB document contains multiple case variations of the same field, for example:

{
 "Ab": 42,
 "aB": 43
}

Dremio behavior:

During metadata discovery, all variations are registered as the same logical field.
When querying, Dremio pushes down an $or condition covering all known variations.
MongoDB returns all matching values to Dremio.
Dremio uses the first field encountered in document order to populate the result.

This selection is not deterministic from the user’s perspective and depends on field ordering in the MongoDB document.

SELECT * vs Explicit Column Selection

Dremio uses different resolution paths depending on query type:

SELECT a or SELECT A
- Uses explicit column resolution
- Returns the first matching field found
SELECT *
- Uses a wildcard resolution path
- May return the last matching field
Queries such as:
```
SELECT *, a AS x
```
may include both values, each resolved using a different strategy
This behavior is expected and results from separate internal projection mechanisms.

Best Practices

Avoid storing multiple case variants of the same field within the same MongoDB document.
Run metadata refresh after introducing new fields or new case spellings.
Be cautious when enabling case insensitivity on large collections due to full scan overhead.
Prefer explicit schema definitions (ALTER TABLE) to control field case resolution.
Avoid relying on SELECT * when deterministic field selection is required.

Additional Resources

https://docs.dremio.com/dremio-cloud/bring-data/connect/databases/mongodb/#advanced-options

https://docs.dremio.com/dremio-cloud/sql/commands/alter-table/