topic Re: Temporary streaming tables (CDC) in Data Engineering

Temporary streaming tables (CDC)

mbaas — Wed, 24 Jul 2024 12:06:08 GMT

I am currently using the `apply_changes` feature. I saw for the regular decorator `dlt.table` you can create temporary tables. I do not see the option you could use this feature with `dlt.create_streaming_table(`, in the sql version it looks it is supported: CREATE OR REFRESH TEMPORARY STREAMING TABLE.

Is there any other way I could create a table without the use of `dlt.create_streaming_table`?

Re: Temporary streaming tables (CDC)

raphaelblg — Wed, 24 Jul 2024 21:54:02 GMT

Hi @mbaas, support for temporary tables by using the create_streaming_table() function is under development at the current moment.

Is there any other way I could create a table without the use of `dlt.create_streaming_table`?

Yes, through the CREATE STREAMING TABLE SQL statement on DLT or DBSQL. In the future this might also be possible through serverless notebooks or serverless job compute.

Re: Temporary streaming tables (CDC)

Icassatti — Thu, 25 Jul 2024 04:03:13 GMT

Read this articles:

Delta Live Tables Python language reference - Azure Databricks | Microsoft Learn

The APPLY CHANGES APIs: Simplify change data capture with Delta Live Tables - Azure Databricks | Microsoft Learn

Even you could define as temporary, it would be storage into catalog _Databricks_Internal, so forget temporary in this case.

You must have an append only data source before you apply changes

Re: Temporary streaming tables (CDC)

mbaas — Thu, 25 Jul 2024 13:13:46 GMT

Hi @raphaelblg, good news you guys are working on it. I am using python file deployed by databricks bundles. I think it is not possible to mix SQL with python in dlt pipelines right?

Re: Temporary streaming tables (CDC)

raphaelblg — Thu, 25 Jul 2024 14:51:40 GMT

@mbaas you're right, it's not possible to mix %sql with %python magic commands in DLT pipelines source code notebooks.

Adding to the @Icassatti explanation on DLT temporary tables:

"Create a table, but do not publish metadata for the table. The temporary keyword instructs Delta Live Tables to create a table that is available to the pipeline but should not be accessed outside the pipeline. To reduce processing time, a temporary table persists for the lifetime of the pipeline that creates it, and not just a single update". Source: Delta Live Tables Python language reference