Delta live table not refreshing - window function

ibrar_aslam — Fri, 05 Jul 2024 14:29:15 GMT

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 """ )

Re: Delta live table not refreshing - window function

RishabhTiwari07 — Thu, 18 Jul 2024 15:11:37 GMT

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

topic Re: Delta live table not refreshing - window function in Data Engineering

Delta live table not refreshing - window function

Re: Delta live table not refreshing - window function