Differences Between "TEMPORARY STREAMING TABLE" and "TEMPORARY STREAMING LIVE VIEW" in DLT

[Kai]

Hello Databricks community,

I'm seeking clarification on the distinctions between the following two syntaxes:

1. CREATE OR REFRESH TEMPORARY STREAMING TABLE
2. CREATE TEMPORARY STREAMING LIVE VIEW

As of my understanding, both of these methods do not store data as a table in the catalog. Could someone provide insights into the practical differences between these two approaches when used? Thank you!

[gabsylvain · Accepted solution]

Hi @Kai,

The two syntaxes you're asking about, CREATE OR REFRESH TEMPORARY STREAMING TABLE and CREATE TEMPORARY STREAMING LIVE VIEW, are used in Delta Live Tables and have distinct purposes.

1. CREATE OR REFRESH TEMPORARY STREAMING TABLE: This syntax is used to declare a streaming table in Delta Live Tables. The TEMPORARY clause instructs Delta Live Tables to create a table that is available to the pipeline but should not be accessed outside the pipeline. This table persists for the lifetime of the pipeline that creates it, and not just a single update. The CREATE OR REFRESH part of the syntax allows you to either create a new streaming table or refresh an existing one. This is particularly useful when you want to process the latest data available in the sources provided in the query.

2. CREATE TEMPORARY STREAMING LIVE VIEW: This syntax is used to declare a view in Delta Live Tables. Similar to the temporary streaming table, the TEMPORARY clause here also instructs Delta Live Tables to create a view that is available to the pipeline but should not be accessed outside the pipeline. The key difference is that a view is a virtual table based on the result-set of anSQL statement. A view contains rows and columns, just like a real table, and it is used to simplify complex queries, hide the complexity of data, or protect data by limiting the data that can be accessed by the user.

The practical differences between CREATE OR REFRESH TEMPORARY STREAMING TABLE and CREATE TEMPORARY STREAMING LIVE VIEW lie in their use cases and how they handle data.

1. CREATE OR REFRESH TEMPORARY STREAMING TABLE:  The practical use of this syntax is when you want to process the latest data available in the sources provided in the query. This is particularly useful in scenarios where your data is continuously growing and you want to keep your table up-to-date with the latest data. For example, if you're ingesting data from a streaming source like Kafka or a cloud storage, you can use this syntax to create a table that processes the data as it arrives. This syntax also allows you to incrementally calculate results as new data arrives, keeping results up-to-date without needing to fully recompute all source data with each update.

2. CREATE TEMPORARY STREAMING LIVE VIEW:  The practical use of this syntax is when you want to simplify complex queries, hide the complexity of data, or protect data by limiting the data that can be accessed by the user. A view is a virtual table based on the result-set of an SQL statement and contains rows and columns, just like a real table. However, unlike a table, a view does not physically store the data but provides a way to view and query the data as if it were a standalone table. This is particularly useful in scenarios where you want to provide a simplified interface to your data or encapsulate the logic of your data, thereby hiding the complexity of your data operations.

In summary, while both syntaxes are used to create temporary objects in Delta Live Tables, the CREATE OR REFRESH TEMPORARY STREAMING TABLE is used when you want to process the latest data available in your sources and keep your table up-to-date with the latest data, and the CREATE TEMPORARY STREAMING LIVE VIEW is used when you want to simplify your data operations and provide a simplified interface to your data.

Hope that helps clarify things a little bit.

Thanks,

Gab