Delta live table not refreshing - window function
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-05-2024 06:56 AM - edited 07-05-2024 07:29 AM
We have a list of streaming tables populated by Autoloader from files on S3, which serve as sources for our live tables. After the Autoloader Delta pipeline completes, we trigger a second Delta Live Tables (DLT) pipeline to perform a deduplication operation.
Our current deduplication process computes the rank of the latest record and filters based on this rank. However, we encounter an issue where we need to recreate the schema every time to update the live table, and even a full refresh doesn't resolve the problem.
Could anyone suggest best practices or optimizations for the deduplication process within Delta Live Tables? Additionally, how can we address the need to recreate the schema every time to ensure our live tables are correctly updated?
def generate_live_table(live_table_name: str, table_name: str, id_coll: str, spark: SparkSession) -> None:
"""Create or refresh Delta live table
Args:
live_table_name: live DLT name
full_table_name: base table name
full_table_schema: table schema
id_coll: object ID column to partition by
spark: spark session
"""
.table(name=live_table_name, table_properties={"delta.columnMapping.mode": "name", "delta.minReaderVersion": "2", "delta.minWriterVersion": "5"})
def create_or_refresh_live_table():
return spark.sql(
f"""
SELECT *
FROM (Select *, rank() OVER (PARTITION BY `{id_coll}` ORDER BY installment_date DESC) as row_rank FROM {table_name})
WHERE row_rank == 1
"""
)
- Labels:
-
Workflows
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-18-2024 08:11 AM
Hi @ibrar_aslam ,
Thank you for reaching out to our community! We're here to help you.
To ensure we provide you with the best support, could you please take a moment to review the response and choose the one that best answers your question? Your feedback not only helps us assist you better but also benefits other community members who may have similar questions in the future.
If you found the answer helpful, consider giving it a kudo. If the response fully addresses your question, please mark it as the accepted solution. This will help us close the thread and ensure your question is resolved.
We appreciate your participation and are here to assist you further if you need it!
Thanks,
Rishabh