I’m using the QuickSight connection to Databricks, with queries ingested into SPICE.
Queries that work perfectly in Databricks started breaking completely when I ran them using the new dataset experience.
With the old dataset experience, the data comes through correctly.
With the new one, the schema gets corrupted:
NULL values are ignored
Values from other fields are pushed into the wrong columns
This happens even when I explicitly CAST every field to a fixed type in the SQL query.
I’ll add the query and screenshots below (with no sensitive data) to demonstrate the issue.
the quary:
SELECT
CAST(Id AS STRING) AS Id,
CAST(Name AS STRING) AS Name,
CAST(Website AS STRING) AS Website,
CAST(Industry__c AS STRING) AS Industry__c,
CAST(BillingCountry AS STRING) AS BillingCountry,
CAST(AnnualRevenue AS DOUBLE) AS AnnualRevenue,
CAST(NumberOfEmployees AS INT) AS NumberOfEmployees,
CAST(Phone AS STRING) AS Phone,
CAST(Description AS STRING) AS Description,
CAST(Type AS STRING) AS Type,
CAST(BillingCity AS STRING) AS BillingCity,
CAST(BillingState AS STRING) AS BillingState,
CAST(AccountSource AS STRING) AS AccountSource,
CAST(Account_Status__c AS STRING) AS Account_Status__c,
CAST(Region__c AS STRING) AS Region__c,
CAST(Vertical_A__c AS STRING) AS Vertical_A__c,
CAST(Sub_Vertical__c AS STRING) AS Sub_Vertical__c,
CAST(CreatedDate AS TIMESTAMP) AS CreatedDate
FROM xxxxx
WHERE CreatedDate > NOW() - INTERVAL 30 DAYS
I don’t want to paste account data here, but all the NULL values disappeared and values from incorrect columns were inserted in their place. All the NULLs were pushed into the last columns of each row, regardless of the original data.
Check SPICE errors, access the dataset’s Refresh tab to view the ingestion error summary, which details skipped rows, error codes (like SQL_SCHEMA_MISMATCH_ERROR), and affected columns. Download the error rows file for specifics on your query fields, such as mismatched CAST types (e.g., timestamps or doubles causing overflows). Fix by editing the dataset to adjust types, filter invalid rows, or validate data before SPICE import.
Please refer to the following Quick documentation and community post this might be helpful for you.
I think this is not an issue related to combining datasets from the old vs the new experience.
It seems like the backend just shifts the data incorrectly to the left side if the dataset contains null values and the last columns then end up having null values.
Seems like a bug to me, I’ve just posted a question here also
Hope everything is well! As your case does resemble behavior from Kirovsk’s post that I replied to, I would definitely create an AWS Support ticket, as they may be able to better address the issue you are encountering.
Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.
