How to Store Query Results as Static Table or Snapshot in ksqlDB

ksqlDB does not support storing pull query results as a static table. However, there is a workaround.

You may think that the following statement would create the snapshot you want.

CREATE TABLE today_orders
AS SELECT * FROM orders;

The truth is that ksqlDB converts that pull query into a push query by adding the EMIT CHANGES modifier for you. The result is that a persistent query that updates the state from any new incoming order is created alongside the new table.

To keep a snapshot of today’s orders and prevent the table from getting new orders for tomorrow, you can delete that persistent query.

TERMINATE CTAS_today_orders;

To know the ID of the persistent query, run :

DESCRIBE today_orders EXTENDED;

and look for the query that writes in the table.