Dataflow Gen2 Mashup Error: Loading Data into Lakehouse

Thanks for your reply, I already tried to overriede the Date-Type manually in the Dataflow but it didn't change anything...

Thanks for your reply! 
Here a screenshot of the column and data types mappings for the table 

Remark: The columns in my source originally have spaces. In the Data Destination settings Fabric automatically suggest me to fix this, since they are not allowed in destination. Thats reason for the information on the top 

If the source query is staged, try disable staging as I suggested in my comment of 03-21-2024.

Of course type DateTime is not the same as type "date".  You need to explicitly convert type "datetime" to type "date", which will drop the time component of type "datetime".  

FYI type "time" does not exist in lakehouses.  But you can still store time information by using the lakehouse type "timestamp", which will store date and time info. In DFgen2 the equivalent type is called "datetime".

So if your lakehouse column is of type Date, but also needs to hold time information, then you need to change the column type to "timestamp".  

Here is just an example on what I am doing with type mapping when using a Copy data activity in a pipeline.  This shows you the destination type of a lakehouse that I use as a sink.  As you can see, the sink, or lakehouse destination type is set to "timestamp" to store an incoming SQL Server data of type "datetime2".

But if a pipeline is not an option, you will have to drop down to a PySpark Notebook and run some code to effect a type change on your lakehouse column, like so:

from delta.tables import DeltaTable
from pyspark.sql.functions import col
from pyspark.sql.types import TimestampType

# 1. Specify your table
catalog_table = "your_catalog.your_schema.your_table"  # e.g. "LH_name.raw_events"

# 2. Load the Delta table as a DataFrame
df = spark.table(catalog_table)

# 3. Cast the column from Date to Timestamp
df_converted = df.withColumn(
    "your_date_column",
    col("your_date_column").cast(TimestampType())
)

# 4. Overwrite the existing table with the new schema
df_converted.write.format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .saveAsTable(catalog_table)

Best practices

• Backup / dev test: Try this first on a development copy of your table to validate downstream queries.

• Partitions: If your table is partitioned, include those same partition columns in your rewrite to avoid full-table shuffles. For example:

df_converted.write \
    .format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .partitionBy("year", "month") \
    .saveAsTable(catalog_table)

• Zero-downtime pattern: If you need to avoid interrupting readers, write to a temporary table, then swap names:

  1. Write to your_table_tmp

  2. spark.sql("DROP TABLE your_table")

  3. spark.sql("ALTER TABLE your_table_tmp RENAME TO your_table")

That’s all you need to recast a date column to a full timestamp/datetime in Fabric’s Delta Lakehouse via PySpark.

Hope this helps.