In StarRocks 4.1, the VARIANT type is specifically designed for compatibility with Apache Iceberg v3 tables that use the Parquet Variant Encoding. If you are querying a Snowflake-managed Iceberg table and seeing a "pointer" or internal structure instead of the expected data, it is likely due to the following reasons: 1. Direct Selection vs. Extraction Functions In StarRocks, the

VARIANT

type is stored in a binary format. When you perform a simple

SELECT variant_col

, the output may show the internal binary representation or a structure. To view the data as JSON or extract specific values, you must use the provided VARIANT functions or casting: * View as JSON:

SELECT CAST(variant_col AS STRING) FROM table;

* Extract specific fields:

SELECT get_variant_string(variant_col, '$.field_name') FROM table;

* Query the root:

SELECT variant_query(variant_col, '$') FROM table;

* Check the type:

SELECT variant_typeof(variant_col) FROM table;

2. Shredding (Subcolumnarization) Compatibility This is the most likely cause of compatibility issues between Snowflake and StarRocks 4.1. * Snowflake Behavior: Snowflake's Iceberg implementation heavily utilizes shredding. This process extracts nested fields from a VARIANT into separate, typed Parquet columns (subcolumns) for better performance. * StarRocks 4.1 Limitation: Currently, StarRocks 4.1 supports only unshredded variant values. * Result: If Snowflake has shredded the data into subcolumns, StarRocks may only see the "residual" binary part of the VARIANT column. If all data was successfully shredded into subcolumns, the main VARIANT column's residual part might appear empty or contain only metadata pointers, which StarRocks' current engine cannot yet reconstruct into a full object. Recommended Troubleshooting Steps 1. Test Casting: Run

SELECT CAST(your_variant_column AS STRING) FROM your_table LIMIT 5;

. If this returns a valid JSON string, the data is being read correctly but simply needs explicit casting for display. 2. Verify the Table Version: Ensure the Iceberg table is using

format-version = '3'

. StarRocks 4.1's native VARIANT support is built on the Iceberg v3 specification. 3. Check Shredding in Snowflake: If possible, check if the Snowflake Iceberg table was written with shredding enabled. If the data is fully shredded, you may need to wait for a future StarRocks update that supports variant subcolumn reconstruction, or write the data from Snowflake in a way that preserves the unshredded binary (though Snowflake typically optimizes for its own shredded format). For more details on available functions, refer to the StarRocks VARIANT documentation. References • sql-reference/data-types/semi_structured/VARIANT.md • data_source/catalog/iceberg/procedures.md

@Rocky that does not seem to work. See below:

It looks like the details you intended to share didn't come through. Could you please provide the SQL command you ran, the error message you received, and any relevant table schemas? That will help me figure out what's going wrong.

@Rocky There is one row in the table and in snowflake it contains a value that looks like this

{
  "a": 1,
  "b": "two"
}

The hex value

0x6E756C6C

is the ASCII representation of the string

"null"

. This means StarRocks is successfully reading the column as a

VARIANT

type, but it is interpreting the content of that column as a JSON null literal. The most likely reason for this discrepancy (Snowflake seeing data while StarRocks sees

null

) is a compatibility issue with Variant Shredding. 1. The "Shredded" vs. "Unshredded" Problem StarRocks v4.1 introduced support for the new Apache Iceberg/Parquet Variant encoding. However, the current implementation has a key limitation: * StarRocks Requirement: Currently only supports unshredded variant values (where the entire JSON object is stored in a single binary "value" field). * Snowflake Behavior: Snowflake’s implementation of Iceberg v3 Variants often uses shredding (subcolumnarization). This process extracts JSON fields into separate, typed Parquet columns to improve performance. If Snowflake has "shredded" the data, the main "value" field (the residual) might only contain a

null

or a placeholder, while the actual data lives in hidden sub-columns that StarRocks 4.1 does not yet read. This results in StarRocks seeing a

null

variant. 2. Verify the Column Type To confirm how StarRocks is mapping this column, run:

sql
DESCRIBE ADMANAGER_FACT.MY_V3_ICEBERG_TABLE;

If the type is

VARIANT

, StarRocks is using the new 4.1 native logic. If the type is

VARCHAR

or

VARBINARY

, the issue is different. 3. Check your StarRocks Version Native

VARIANT

support and the

variant_query

function are only available in StarRocks 4.1 and later.

sql
SELECT version();

If you are on an older version (e.g., 3.x), the

VARIANT

type is not officially supported for Iceberg, and the behavior you are seeing might be the result of a fallback mapping (e.g., mapping a binary blob to a string). 4. Troubleshooting Steps * Try `variant_typeof`: Check what the root type is. If it returns

"Null"

, the reader is definitely not seeing the object structure.

sql
    SELECT variant_typeof(RECORD) FROM ADMANAGER_FACT.MY_V3_ICEBERG_TABLE LIMIT 1;

* Snowflake Compatibility: If possible, check if the Snowflake Iceberg table was created with shredding enabled. If the table can be written in an "unshredded" or "raw" format, StarRocks should be able to read it. * Workaround (External Tables): If the data is actually stored as a JSON string in Parquet (not the new binary Variant format), ensure the column is mapped as

VARCHAR

in StarRocks and use

parse_json()

or

get_json_string()

instead. Summary: You are likely hitting the "shredded variant" limitation in StarRocks 4.1. If the table is using the newest Iceberg v3 Variant spec with subcolumnarization, StarRocks cannot yet reassemble those shredded pieces into the full object. References • sql-reference/sql-functions/variant-functions/get_variant.md • sql-reference/data-types/semi_structured/VARIANT.md • sql-reference/sql-functions/variant-functions/variant_typeof.md

@Rocky yes, StarRocks 4.1.1 sees the data as VARIANT but Snowflake is doing Iceberg Variant Shredding. Where can I look to see where StarRocks development is on this issue?

You are correct that StarRocks 4.1.1 supports the

VARIANT

type for Iceberg but is currently limited to unshredded data. When Snowflake or other engines write with "shredding" enabled, they split the JSON object into separate columnar sub-fields in Parquet. StarRocks 4.1 can only read the "binary fallback" column; if that fallback is empty/null (which is common in fully shredded files), you see the

0x6E756C6C

or

NULL

values you observed. Where to track development You can track the progress of Iceberg Variant support and its optimizations (like shredding/reassembly) through these primary channels: 1. Main Feature Issue (GitHub): #55949 - Implement Support for Iceberg Variant Format This is the parent issue for the Iceberg Variant feature. Most sub-tasks and PRs related to variant encoding and shredding reassembly will be linked here. 2. StarRocks 2026 Roadmap: #67632 - StarRocks Roadmap 2026 Under the "Data Lakehouse" section, the community tracks the evolution of Iceberg V3 features. Support for the full Iceberg V3 spec (including advanced shredding reassembly) is listed as a major priority for the upcoming year. 3. Release Notes: Watch the StarRocks Releases page. Updates to the

VARIANT

type (such as "Support for reassembling shredded variant columns") will be highlighted in the "New Features" or "Improvements" sections of version 4.2 or 4.1.x patch releases. Current Status The initial 4.1 release focused on the core distributed analytical execution of the Variant type (treating it as a first-class citizen in the engine). Supporting the reassembly of shredded sub-columns from external engines is a planned next step to ensure full interoperability with highly optimized Snowflake/Databricks writes. Recommendation: If you have control over the writer, the immediate workaround is to disable variant shredding in your Snowflake Iceberg table properties (set

write.parquet.shred-variants = false

) so that the data is stored in the unshredded binary format that StarRocks 4.1 can currently read. References • sql-reference/data-types/semi_structured/VARIANT.md • sql-reference/sql-functions/variant-functions/variant_query.md • data_source/catalog/iceberg/procedures.md

@Rocky If I create an Iceberg VIEW that flattens the shredded variant would StarRocks 4.1.1 be able to read those values?

While creating an Iceberg View in Snowflake might seem like a logical bridge, it likely will not solve the problem in StarRocks 4.1.1 due to how Iceberg views and shredding work: 1. The Dialect Problem Iceberg views are essentially stored SQL queries. When Snowflake creates an Iceberg view, it stores the SQL in the Snowflake dialect. * StarRocks can only query Iceberg views that have a StarRocks or Trino dialect definition. * You would need to use

ALTER VIEW ... ADD DIALECT

in StarRocks to provide a StarRocks-compatible SQL string for that view. 2. The Scanner Problem (The "Wall") Even if you provide a compatible dialect, an Iceberg View is just a logical layer. When you query the view in StarRocks: 1. StarRocks parses the view's SQL. 2. It identifies the underlying Iceberg table. 3. The StarRocks Parquet Scanner then tries to read the

VARIANT

column from that table. Because the underlying physical data is still "shredded" into optimized sub-columns that the StarRocks 4.1.1 scanner cannot yet "reassemble," the scanner will still return

null

for the

VARIANT

column. A view cannot "un-shred" data that the scanner doesn't know how to read from the Parquet file. What WILL Work: A "Flattened" Iceberg Table Instead of a View, you can create a Flattened Iceberg Table in Snowflake. If you use a

CREATE TABLE ... AS SELECT

(CTAS) in Snowflake to pull the values out of the variant and into standard columns (e.g.,

STRING

,

INT

,

DECIMAL

), Snowflake will write a new Iceberg table where those values are stored as regular, non-shredded types. Example (Snowflake side):

sql
CREATE TABLE ICEBERG_FLATTENED_FACT 
  EXTERNAL_VOLUME = '...' 
  CATALOG = '...' 
  AS 
SELECT 
  RECORD:a::INT as col_a, 
  RECORD:b::STRING as col_b 
FROM ADMANAGER_FACT.MY_V3_ICEBERG_TABLE;

StarRocks side: StarRocks 4.1.1 will be able to read

ICEBERG_FLATTENED_FACT

perfectly because

col_a

and

col_b

are standard Parquet columns that require no special "Variant" logic or reassembly. Summary * Iceberg View: Does not work because the underlying scanner still hits the shredded variant limitation. * Flattened Iceberg Table: Works because it converts the complex variant into simple types that StarRocks handles natively. * Snowflake Config: You can also try setting

write.parquet.shred-variants = false

on the original table in Snowflake and re-writing the data, which forces Snowflake to use the "unshredded" format StarRocks can read. References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/iceberg/DDL.md • sql-reference/data-types/semi_structured/VARIANT.md References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/iceberg_meta_table.md • data_source/catalog/iceberg/DDL.md