Dan Sotolongo

@julianhyde Thanks! We're very excited about the use cases it can enable, and to share what we've learned through the process.

@sortalongo By the way, congratulations on getting this work into Snowflake. It only sounds complicated until you use it (just like relational DB!). Therefore getting it into customers hands is crucial.

In DB theory, a constraint determines, statically, whether a tuple, relation or database value is valid. A "transition constraint" would determine whether a change to a database is valid (e.g. modifies an even number of rows). Is this a new concept? (Don't say triggers.)

@julianhyde I think there's certainly a strong analogy, if not a direct correspondence between the two. Dependent types let you apply logic in the types, so they can certainly be used to enforce constraints. How mutation and transactions fit into it is more complicated...

@sortalongo When we spoke about Morel a couple of years ago, you brought up dependent types. I am *still* trying to decide constraints in DB theory are the same as dependent types in PL theory. And I don’t want to adopt anything that will mess with my beloved Hindley-Milner type inference.

@julianhyde sign me up! I have thoughts :)

Am I the only one thinking it’s time to explore the connections between DB and PL? Maybe a coffee group.

Incremental processing is the foundation of efficient stream processing across the entire latency spectrum, yet SQL lacks a native mechanism for querying changes to a table over time. Today we present our #SIGMOD paper describing Snowflake Change Queries dl.acm.org/doi/10.1145/35…

@embano1 @SnowflakeDB Haha perhaps not the best choice of name… it was intended as a deep talk for people familiar with the 101 article 😅

Question for Data Twitter. What are your most important SQL Window Function use cases? Rank? Sequence numbers? Rolling Averages? Row Totals? Cumulative Sums? What else?

@christzolov @fhueske @takidau That conveyor belt animation at the end is 👌

@sortalongo @fhueske @takidau Thanks Dan! Nice catch! I fixed few things and streamlined second part a bit. Still challenging to follow but there is a paper link for a rescue ;)

Visualisation for the "Watermarks in Stream Processing Systems" paper example. E.g. streaming analytics pipeline for an online team game, aggregating hourly, per-team scores. Skipped the pub/sub source. @sortalongo, @fhueske, @takidau any obvious flaws?

Galactic atmospheres influence how galaxies live and die, but how do they work? Join us Friday, December 2 @ 7PM PT for a public astronomy lecture on galactic atmospheres. It will be hosted in-person at Caltech and streamed on Youtube Live. See you there! youtu.be/xmxW7z7IjfY

New recording (no registration required!) at confluent.io/events/current…

Slides at s.apache.org/streaming-101-…

The #current2022 talk I gave with @takidau is online! It’s called “Streaming 101 Revisited: A Fresh Hot Take”. We talk about what’s missing in existing models and propose new ideas to make declarative stream processing easier and more powerful than ever.

@emaxerrno @ccomeau79 @takidau .@ccomeau79 Here’s how I’d put it: we start with normal SQL (just like Mz), then add in multi-temporality, changelogs, and better watermarks. Like @emaxerrno said: lower latencies. That puts pressure on declarative models to keep up, so we need new concepts.

@ccomeau79 @takidau @sortalongo Tyler covers materialize there. I think is more conceptual for those of us building streaming systems. Effectively a different approach from timely (mz). The importance is the trend towards lower latency stateful stream proc.

agree w/ @takidau and @sortalongo on this direction for streaming sql. lots to digest on this prezo. real low latency opens up new opportunities to build things we haven't yet imagined. punctuating the stream/(insert-only-table) with snapshots is a good idea. \

@mim_djo @jaceksan @SteveMcCanne iceberg.apache.org/spec/#:~:text=…

@sortalongo @jaceksan @SteveMcCanne how ?

TIL how delta lake table support delete rows, find the parquet file with those rows, create a new parquet file without those rows and tag the previous parquet as obsolete !!! that seems very efficient 🤡

@jaceksan @mim_djo @SteveMcCanne Or Iceberg row level delete files

@mim_djo @SteveMcCanne Register delete vectors, like e.g. in Vertica

@bothra90 They seem to be a “used by few, needed by many” kind of thing. Some who use them swear by them, but not enough DBs support them for them to be mainstream. Arguably, anyone building SCD2 tables would benefit from them.

@sortalongo I realized that what I was asking is being addressed by updating the value of valid_to at the time of deletion from infinity to the "current time". I presume it's not common for people to use bi-temporal tables though. What do you see in your experience?

@bothra90 E.g. if you have a row with valid_to=null, and the table is final where valid_to < 2022-10-19T00:00, that means the row cannot be deleted until after the 18th. So any query that needs to join with this row before that time can do so, but joins later may have to wait for progress.

@sortalongo Would the NULL case cover the case where the value might change at an unknown time in the future? For example, the city of a user can change but we don’t know if/when that’s going to happen. Also, how can the system make progress in this case?

@bothra90 Yes, of course! This schema is intended to be a [bitemporal table](en.wikipedia.org/wiki/Temporal_…). That’s supported by some databases natively, but one can implement it manually in others. Typically, valid_to is set to 9999-12-31 or NULL until the row is deleted.

@sortalongo @sortalongo - thx for sharing the slides! Can you clarify what it means for the inventory table to have the schema (id, count, valid_from, valid_to, last_updated)? Who's responsible for providing the last 3 columns? What if the value of "valid_to" cannot be determined?