Boolean and Enum Encoding Across Warehouse Targets

A boolean at the source is true or false. An enum at the source is a fixed, named list of values, nothing more exotic than a status field cycling through a handful of states. PostgreSQL stores an enum internally as a four-byte integer, mapped to its textual label through the system catalog pg_enum, and a downstream reader never sees that integer at all, only the label it resolves to. That gap between what's stored and what's shown is where the trouble starts.

The moment that data has to leave PostgreSQL and land somewhere else, whoever builds the pipeline has to decide what actually crosses the wire: the label, the underlying ordinal, or some third representation invented at the boundary. Snowflake, BigQuery, and Redshift each answer that question differently, and none of them answer it the way PostgreSQL would. The failures that follow rarely announce themselves. A boolean column stored as the integer 1 in one target and as the string "true" in another will not throw an error, and an enum ordinal that quietly stands in for its label will pass every schema check while corrupting whatever logic depends on reading it correctly. Nothing crashes. Reports just start being wrong in ways nobody notices until someone traces a bad number back through three systems and finds a type coercion nobody documented.

PostgreSQL's enum encoding as a migration starting point

PostgreSQL's enum type gives engineers two guarantees that most schema designers take for granted and few warehouse migrations preserve: a strict, declared order, and a closed set of allowed values enforced at the type level. Every migration off PostgreSQL has to reckon with the fact that no major cloud warehouse replicates either guarantee natively, so the translation is lossy by default unless someone builds the missing enforcement back in.

The mechanics matter because the rest of this piece measures every warehouse against them. PostgreSQL 18 documentation specifies that each enum value takes up four bytes on disk, with the mapping from that internal value to its human-readable label held in the system catalog pg_enum. The documentation even notes that querying the catalog directly can be useful, an acknowledgment that the internal representation is something engineers sometimes need to inspect directly rather than trust blindly. Ordering is fixed by declaration, not inferred or alphabetical. It's fixed by declaration order: a type created with CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy') makes the comparison 'ok' > 'sad' valid, and standard comparison operators and aggregate functions operate across enum values exactly as they would across integers.

Type safety runs just as strict. Type safety is strict: two enum types with overlapping labels cannot be compared without an explicit cast to text, comparing mood = happiness raises an error even if both contain the label 'happy'. That isolation extends to schema evolution. Once a value exists in a PostgreSQL enum, it can't be removed, and the sort order can't be changed without dropping and recreating the type entirely, a constraint the source schema enforces with no equivalent enforcement waiting at any warehouse target. Enum labels are case-sensitive and whitespace-significant in PostgreSQL, and neither property is guaranteed to survive ingestion into a warehouse that normalizes strings.

None of this is exotic engineering. It's ordinary relational database design, done carefully. Carefulness doesn't travel downstream.

What Parquet does to booleans and enums in transit

Data rarely moves from PostgreSQL straight into a warehouse's native storage. Parquet is not a neutral pass-through. It makes its own encoding decisions for both booleans and enum-like strings, and those decisions shape what the warehouse eventually sees, before any warehouse-specific logic even runs.

For booleans, Parquet's encoding documentation specifies two options: RLE, Run Length Encoding combined with bit-packing, or PLAIN, which packs values bit by bit with the least significant bit first. Neither option corresponds to a Parquet physical type called BOOLEAN that maps cleanly onto a SQL BOOLEAN column without a reader performing an interpretation step of its own. The bit gets stored efficiently. Turning that bit back into a value a SQL engine recognizes as true or false depends entirely on the reader implementing that step correctly.

Enum-like values face a sharper transformation. The natural way to store a column of repeated string values in Parquet is dictionary encoding: RLE_DICTIONARY builds a dictionary scoped to each column chunk and stores the actual values as integer indices into that dictionary. A reader that doesn't reconstitute the dictionary back into labels receives nothing but integers. The older PLAIN_DICTIONARY encoding is deprecated in favor of this scheme, with RLE_DICTIONARY used in data pages and PLAIN reserved for the dictionary page itself, a distinction that older reader implementations may not handle correctly.

Cardinality changes the picture further. If a column's dictionary grows past a practical size and holds too many distinct values, Parquet abandons dictionary encoding and falls back to PLAIN, storing each value as a length-prefixed raw byte array, a four-byte little-endian length followed by the bytes themselves. So a high-cardinality enum column can arrive at the warehouse encoded completely differently than a low-cardinality one from the same source, depending on how many distinct labels it happened to contain when it was written. The practical consequence is straightforward and easy to miss: an enum column originating in a transactional database can arrive at a Parquet-backed object store as nothing but a dictionary index integer, and unless some transformation layer explicitly reconstitutes that index back into its label, the warehouse loads an integer column where a string column was expected.

Snowflake's boolean representation and missing native enum type

Snowflake gives engineers a native BOOLEAN type, which handles the simpler half of the problem cleanly enough. Enums get no equivalent. Snowflake has no ENUM type at all, so any enum arriving from a PostgreSQL or MySQL source has to be mapped explicitly during ingestion, or it lands as a plain VARCHAR with no enforced value set whatsoever.

That gap sits inside a broader dialect mismatch. Snowflake's SQL dialect differs from Redshift's PostgreSQL-based syntax, requiring explicit data type mapping, particularly for VARCHAR limits and semi-structured types, during any migration from a PostgreSQL source. Without a native enum, a pipeline that does not explicitly map the source enum to a CHECK-constrained VARCHAR or a lookup table produces a column that accepts any string, silently discarding the source's value-set guarantee.

The deployment layer adds one more wrinkle. Snowflake runs across AWS, Azure, and GCP, and a single pipeline can land data across Snowflake accounts configured differently by region, which only raises the stakes for documenting type mappings explicitly rather than assuming they'll behave consistently everywhere. The pattern that holds up in practice: load the enum as VARCHAR, then enforce its actual value set downstream, through a dbt model or a view built around a CASE expression, with the mapping written down somewhere a person can read it. Whatever invisible normalization happens automatically at the connector layer removes exactly that inspectability, leaving engineers unable to audit or correct a decision they never got to see made.

BigQuery's boolean coercion and repeated string encoding

BigQuery's serverless architecture produces its own coercion behavior, distinct from Snowflake's, and the differences matter most for boolean values arriving as strings or integers rather than as native booleans.

Like Snowflake, BigQuery has no native enum type. Enum columns land as STRING, the rough equivalent of Snowflake's VARCHAR workaround, and that substitution costs the source type's ordering guarantee and its closed value set in one move. A STRING column will hold any text at all, sorted however STRING sorts, with nothing resembling the declared order PostgreSQL enforced at the source.

BigQuery STRING columns are case-sensitive, which at least preserves the exact casing of a PostgreSQL enum label rather than flattening it. That preservation doesn't extend to enforcement, though: without a CHECK constraint or any equivalent value-set enforcement, a brand-new label added at the source loads into BigQuery without complaint, and schema drift that should have been visible becomes invisible instead. BigQuery's REPEATED field mode adds a further trap for anyone reaching for it as an enum substitute. REPEATED can represent genuinely multi-valued attributes well, but it accepts any string in any position, with no relationship whatsoever to the closed set the source type defined, so using it to approximate an enum buys the appearance of structure without any of the actual constraint.

Redshift's PostgreSQL heritage in boolean and enum handling

Redshift descends directly from PostgreSQL, and that lineage is most visible in its BOOLEAN type, which accepts a genuinely wide range of input representations without complaint. Redshift's PostgreSQL heritage means TRUE, FALSE, 1, 0, 't', 'f', 'yes', and 'no' all get accepted as valid boolean input without raising an error.

That permissiveness reads as a convenience right up until it becomes a trap. A pipeline sending 'yes'/'no' strings to Redshift will succeed silently, while the identical pipeline sending the same strings to BigQuery may fail outright or coerce them into INT64 instead of a boolean. The warehouses are not interchangeable even when the source encoding is character-for-character the same. An engineer who tests a pipeline against Redshift first and assumes the behavior generalizes is building on an assumption that doesn't hold.

The sharpest divergence, though, sits with enums, and it's the one most likely to produce a false sense of safety. Redshift has no native enum type, so the standard workaround, borrowed straight from PostgreSQL instinct, is a VARCHAR column paired with a CHECK constraint. Redshift doesn't support CHECK constraints at table creation time at all. An engineer migrating from PostgreSQL who reaches for the exact pattern that worked at the source finds that the one mechanism available to approximate enum safety simply doesn't exist on the target. VARCHAR limits and behavior differ between Redshift and PostgreSQL in ways that require explicit mapping during migration: a PostgreSQL enum label that fits within PostgreSQL's NAMEDATALEN limit may still hit Redshift VARCHAR column width issues if the migration does not set widths explicitly.

Adding a new enum value at the source breaks downstream logic silently

Static representation is only half the problem. Schemas change, and adding a single new value to a source enum after data has already loaded into a warehouse won't raise an error at any of the three targets covered here, yet it can quietly corrupt boolean-flag logic and any ordinal-based comparison a downstream consumer relies on.

MySQL's ENUM type illustrates the mechanism cleanly, since it's built for storage efficiency, converting each label into an integer using just one or two bytes on disk. Adding a new member to that enum requires a schema migration, and that migration propagates inconsistently across any warehouse that already materialized the original integer codes before the change happened. A downstream table that stored status_code = 2 to mean "active" can end up matching a completely different status once the source enum gets a new member inserted ahead of it in declaration order, with no error, no warning, and no obvious symptom beyond a report that's suddenly wrong.

PostgreSQL's own documentation is explicit that existing enum values can't be removed and sort order can't change without dropping and recreating the type. None of that protection travels downstream with the data. The warehouse just sees the new label as another string, or another integer depending on how it got encoded, with nothing flagging that the value set underneath it just changed. Absent an explicit schema migration and a mapping update on the destination side, that new value gets rejected outright, stored as NULL, or stored as an unmapped string that whatever business logic exists downstream simply ignores.

The stakes rise further for any AI agent querying warehouse data directly. An agent reasoning across boolean access-control flags or enum status fields will return inconsistent answers if the ordinal mapping shifted after the initial load, because the query surfaces the new value while the business logic it's supposed to satisfy was written against the old set entirely. The agent read correctly but is reasoning against a value set that no longer matches what the source actually means.

Connector-layer type normalization often makes the problem worse

The standard argument for handling all of this inside the connector or ETL layer, invisibly, is a reasonable one on its face: engineers shouldn't have to memorize the boolean coercion quirks of three different warehouses just to move a status field, and a connector that handles the translation automatically frees them to focus on the actual data model instead of warehouse trivia. Engineers who lean on that normalization are accepting the platform's implicit promise that the translation will be handled correctly, so they don't have to verify it themselves.

The trouble is that the normalization, done invisibly, strips out the one thing that would let anyone catch it when it's wrong: the ability to inspect the mapping at all. When a connector silently converts an enum into a plain string or a boolean into a bare integer, nothing downstream, not the BI tool, not an AI agent, not an audit system, has any record that a transformation happened, let alone a way to confirm it was done correctly.

A closed bug filed against the WSO2 product integrator shows the failure pattern concretely. When a function carried an optional boolean or enum value, selecting a valid item from the dropdown and saving caused the function to save without those parameters at all, a serialization-side bug whose visible effect was the silent loss of enum and boolean values right at the integration boundary.

For boolean fields tied to access control, the stakes stop being abstract. An is_active boolean coerced into the integer 0, that a downstream system then fails to evaluate correctly as false, can leave a deprovisioned account looking active in an audit log, a SOC 2 control failure that traces back to a type mapping nobody documented in the first place. The mapping wasn't wrong because someone was careless. It was wrong because nobody could see it well enough to check.

That's the real cost of invisibility here. A connector that surfaces its type decisions, even imperfectly, gives an engineer something to audit against the source schema. A connector that hides them gives a warehouse full of columns that look correct, resolve cleanly in every query, and quietly diverge from what the source system actually meant.