Malloy: Hierarchical Data, Semantic Models, and the Future of Analytics

WEBVTT
NOTE
Transcription provided by Podhome.fm
Created: 12/08/2025 00:41:16
Duration: 3528.184
Channels: 1
1
00:00:11.280 --> 00:00:15.460
Hello, and welcome to the Data Engineering podcast, the show about modern data management.
2
00:00:16.155 --> 00:00:24.654
Data teams everywhere face the same problem. They're forcing ML models, streaming data, and real time processing through orchestration tools built for simple ETL.
3
00:00:25.275 --> 00:00:29.215
The result, inflexible infrastructure that can't adapt to different workloads.
4
00:00:30.070 --> 00:00:32.730
That's why Cash App and Cisco rely on Prefect.
5
00:00:33.350 --> 00:00:41.930
Cash App's fraud detection team got what they needed, flexible compute options, isolated environments for custom packages, and seamless data exchange between workflows.
6
00:00:42.625 --> 00:00:47.525
Each model runs on the right infrastructure, whether that's high memory machines or distributed compute.
7
00:00:48.305 --> 00:00:52.645
Orchestration is the foundation that determines whether your data team ships or struggles.
8
00:00:53.345 --> 00:01:00.510
ETL, ML model training, AI engineering, streaming, Prefect runs it all from ingestion to activation in one platform.
9
00:01:01.289 --> 00:01:04.830
WHOOP and 1Password also trust Prefect for their data operations.
10
00:01:05.369 --> 00:01:12.189
If these industry leaders use Prefect for critical workloads, see what it can do for you at dataengineeringpodcast.com/prefect.
11
00:01:13.085 --> 00:01:17.665
Composable data infrastructure is great until you spend all of your time gluing it back together.
12
00:01:18.445 --> 00:01:22.945
BRUIN is an open source framework driven from the command line that makes integration a breeze.
13
00:01:23.245 --> 00:01:27.090
Write Python and SQL to handle the business logic and let BRUIN handle the heavy
14
00:01:27.649 --> 00:01:31.509
lifting of data movement, lineage tracking, data quality monitoring, and governance enforcement.
15
00:01:32.450 --> 00:01:39.509
Bruin allows you to build end to end data workflows using AI, has connectors for hundreds of platforms, and helps data teams deliver faster.
16
00:01:40.115 --> 00:01:45.815
Teams that use Bruin need less engineering effort to process data and benefit from a fully integrated data platform.
17
00:01:46.435 --> 00:01:49.475
Go to dataengineeringpodcast.com/bruin
18
00:01:49.475 --> 00:01:55.575
today to get started. And for DBT Cloud customers, they'll give you a thousand dollar credit to migrate to Bruin Cloud.
19
00:01:57.850 --> 00:02:08.910
Your host is Tobias Macy, and today I'm interviewing Michael Toy about Malloy, a modern language for building composable and maintainable analytics and data models on relational engines. So, Michael, can you start by introducing yourself?
20
00:02:09.564 --> 00:02:11.424
Hi. I'm Michael Toy.
21
00:02:11.965 --> 00:02:13.825
I'm one of the creators of Malloy.
22
00:02:14.765 --> 00:02:17.344
And do you remember how you first got started working in data?
23
00:02:18.284 --> 00:02:23.584
Yes. The way I got started working in data is that a friend dragged me into his company, and
24
00:02:24.010 --> 00:02:26.750
the friend is Lloyd Tabb, who's the other cocreator,
25
00:02:27.290 --> 00:02:27.790
codiscoverer
26
00:02:28.490 --> 00:02:29.230
of Malloy.
27
00:02:29.530 --> 00:02:34.830
And he's someone who's been passionate about data his whole life. I'll probably be talking about Lloyd a lot, because
28
00:02:35.530 --> 00:02:50.325
his his vision of of what the data experience should be is is a huge influence in what Malloy is. You know, he started a company to build a data tool, and, he said, hey. You wanna come start with this company with me? I said, oh, that's stupid. Nobody pays money for tools. And then a year later,
29
00:02:51.020 --> 00:03:06.245
I didn't start the company with him. I but I joined him, and then that company became Looker, which was a big deal. I don't know. But we we did pretty good. And so I I I worked a lot on on on making Looker, and that was my first experience in, in in being seriously
30
00:03:06.705 --> 00:03:09.765
involved in in data as opposed to, like, you know, using data.
31
00:03:10.625 --> 00:03:23.629
And so digging now into Malloy, can you give a bit of an overview about what it is and some of the story behind how it got started? Well, sure. And and and it's it's kinda bound up in the history of, of Looker and and of what problems,
32
00:03:24.170 --> 00:03:26.750
exist in the world. So there's a there's a set of problems,
33
00:03:27.209 --> 00:03:33.305
in trying to understand what the right user experience is to create, are for people who are trying to do work based on data.
34
00:03:33.765 --> 00:03:51.819
And and Looker was a an attempt to address some of those things. And, the at the core of Looker, there's a little thing which we now call semantic modeling. I don't I don't know if what we called it before Looker. But now that Looker exists, everyone calls it semantic modeling. I don't wanna claim that we invented it, but we did it really well where, you could write the SQL
35
00:03:52.120 --> 00:04:07.975
for your model like you always did, but you had to write it in kind of a structured way, so that there was enough metadata about your computation so that you could mix and match and sort of compose queries based on what the user wanted to see, and then you could compose the SQL from there. And,
36
00:04:08.295 --> 00:04:58.240
Looker was based main selling point for Looker when we walked into people who'd never seen Looker before was, look, it's just SQL. Right? Everything that's getting written here is is either SQL that we're generating or that you wrote yourself and look at the queries we're writing and they're just SQL. And Looker was tremendously successful, but there were some places where SQL itself was kind of in the way, and it needed to not just be SQL because the the the data surface, of SQL doesn't really match great to the way that that people like to think about data. But, you know, we made a multibillion dollar company on a pretty good idea, and that was great. But as, Lloyd and I, stopped being so involved in the day to day operation of Looker and then Google bought Looker, we had some time to say, well, what should data look like? And and so Malloy was just you know, we had some time to to sort of take a fresh cut at thinking about how to interact with data and our frustrations with with Looker, resulted in Malloy. So
37
00:04:59.165 --> 00:05:03.425
And so in terms of the Malloy language and project,
38
00:05:03.965 --> 00:05:14.385
as you mentioned, SQL can be very verbose. It can be very obtuse to try and get to the thing that you're really trying to do, and it is also verging on the
39
00:05:14.819 --> 00:05:18.840
area of Pearl of being a write once, read never language if you're not careful.
40
00:05:19.460 --> 00:05:27.479
And so in terms of Malloy in particular, I'm curious what the core problem is that you're trying to solve and who you're trying to solve it for.
41
00:05:28.055 --> 00:05:43.290
Yeah. Well, this is where so I'm I'm gonna claim to be some things that I am not, but let's pretend that I'm standing in for all the people who did a thing. And when I say I, I really mean we, because I don't wanna be weird about it. And a lot of the stuff that I say I is me, and a lot of it is me and the people
42
00:05:48.170 --> 00:06:05.215
language, designing the language, implementing the language. And that's not again, that's not true. There's a bunch of other people who's who did a a ton of this work, but let's pretend it's just me just for the purposes of this conversation. And then purposes of this conversation. And then Lloyd is the person who is responsible for I have spent my entire life figuring out how to make great user experiences
43
00:06:05.515 --> 00:06:06.655
with data forever.
44
00:06:06.955 --> 00:06:54.800
And every place I go, this is the job that I'm solving, and I know more about this than anyone in the world. He he wouldn't say that, but I would say that because I've I've watched him work. And I wish Lloyd were here to say great things about me, because I'm sure that that would happen too because he also thinks highly of me. So when we come to this problem of what's the best way to think about data and can it be expressed as a language? I'm super passionate about all the ways that computer languages work. And so a lot of things you mentioned about SQL are true and and shouldn't be held against SQL because SQL is, I don't know, 45 years old. It's it's a quite old computer language, and we've learned a lot about computer languages since then. And if someone were designing SQL today, they they would write a a a different language that didn't have all those problems because we've learned a lot about software engineering. And we and so I am passionate about all those things, and and I've tried to fix absolutely all those problems in the design of Molloy.
45
00:06:55.415 --> 00:07:04.475
It that it's a it's a computer language which is, you know, composable so that you can build things out of smaller things and glue them together and and and predictable and readable
46
00:07:04.775 --> 00:07:20.030
by humans and, you know, a a a bunch of things that that modern language are that that SQL SQL isn't. But then the other impetus for Malloy is not even even really as a as a language. The, so there's a I'm gonna try and channel Lloyd here. So all interactions with data
47
00:07:20.410 --> 00:07:22.190
are, should be interactive
48
00:07:22.564 --> 00:08:26.175
and open ended. So that at one point, the traditional way that data worked is there's a big room with spinning disks in them, and then there's some people who write SQL to reduce that down to a smaller subset of data. And then there's some people who worked on that smaller subset of data who then hand that to decision a curated version of that to decision makers. And then all decision, it's are being made on three generations away from the original data. So I've got a spreadsheet that the c that only the CFO sees or and and that's the ground truth for the company, but only one person can have that because it's so highly curated by the time it gets to that person. But all data action should be iterative and open ended means that at any point, if I'm holding a piece of data, I need both the insight of this piece of data reveals and all of the context that went into its generation so that I can ask the next question. Right? And that anytime that I produce a thing that's a dead end, I've I've limited your ability to do the right thing with data. And so we didn't start to design a language. We started by saying, in a world where there's no SQL, there's but there is SQL because SQL tells us a lot about data that we shouldn't ignore. What are these sort of objects and actions of humans
49
00:08:26.475 --> 00:08:58.865
who are on a sort of iterative data journey? And and then once we sort of know what that is, then what's the natural language of of someone on that journey? And and if we if we describe the problem of SQL first of all, I'm gonna say a lot of bad things about SQL, but SQL is great. There's so much software engineering into making these SQL engines be able to do the things that they do. And Malloy generates SQL. And we think of SQL as the assembly language of data. Like, if you want to do things with data, the machine that you that it should be doing your data thing is SQL.
50
00:08:59.245 --> 00:09:22.505
We just shouldn't ever ask humans to write SQL. That's just an unfair thing in the same way that we don't ask humans to write assembly language. So there are, you know, if there are a million programmers in the world, there are 10 who write assembly language. We would love to live in a world where if there are a million people in the world doing data work, 10 of them have to write an SQL and everyone else says I have I have no time for that, and I really don't need to because I get my work done faster
51
00:09:23.125 --> 00:09:29.945
another way. So, anyway, what are the objects and and what's the natural language of someone on that journey? And I I I haven't I almost lost my thread. I was busy praising SQL.
52
00:09:30.630 --> 00:09:36.650
And I think that the good transition to the topic of the numerous
53
00:09:37.110 --> 00:09:52.785
projects that have tried, and some of them have partially succeeded. Many of them have faded into the mists of time as far as trying to be a layer above SQL and compile down to SQL or completely obviate SQL. And some of them are trying to be more
54
00:09:53.165 --> 00:10:08.140
pure to the original concepts of relational algebra. Some of them are just trying to be a way of being able to do some string smashing to generate some SQL. I'm gonna call out DBT in that regard. And then there are numerous various ones that are
55
00:10:08.920 --> 00:10:12.620
various points along that continuum. And I'm wondering if you can give your
56
00:10:12.945 --> 00:10:19.445
stance on how you think about Malloy in that overall ecosystem and some of the things that you've learned from your predecessors.
57
00:10:20.225 --> 00:10:43.450
Sure. So I I would put Looker in the string smashing to generate SQL universe. So we we've certainly been in that world for a while. And and, again, the reason that the the the history of Malloy is, we wanna have a user centric view of data interactions, and then we wanna build an ecosystem to support that. And, it turns out that the the language that you use to describe what's going on with data
58
00:10:43.935 --> 00:10:44.915
strongly influences,
59
00:10:45.295 --> 00:10:48.515
how you think about the the journey. And one of the reasons that,
60
00:10:48.975 --> 00:10:54.115
SQL is a problem is it forces everybody to think about data like SQL does. And SQL
61
00:10:54.575 --> 00:11:28.269
thinks about in a data in a way which is super useful, you know, like, to to generate efficient queries, but it doesn't necessarily think about data, the way that people do. And if you ever sit down to write I'm not I'm talking to the wrong people. Anyone who's done any querying in SQL just knows that, like, I have a simple question, and now I have to figure out how to write join statement, right, that doesn't blow up the database that answers my question, and and everything is inverted from the way that I like it to think about it. And so SQL is a problem there. And then SQL is also a problem because it's an it's an it's an ancient language. And so there's an opportunity to replace SQL,
62
00:11:28.810 --> 00:11:50.745
which we took, but in, replacing SQL isn't a goal. Right? Our goal is the all data interactions for all time should be done, through the Malloy view of of what data is, and, and and then executed by SQL engines that answer the question. Oh, and what makes Malloy stand out? Yeah. It's based on do what questions do we need to ask? What operations do we need to do in order to answer questions?
63
00:11:51.125 --> 00:12:15.495
Not what is the you know, what's relational algebra and and and how do how does data sit on disks and, you know, those aren't part of Malloy. You you can things that you do in Malloy result in those kinds of operations, but that's not your because those aren't your nouns and verbs. Your nouns and verbs are what's the information I have and what's the relationships between these different pieces of information and what do I wish I knew and keeps you focused sort of on the tasks that you you wanna like, any good high level language. Right?
64
00:12:16.035 --> 00:12:17.654
I I think it's also interesting
65
00:12:18.115 --> 00:12:25.779
as far as the overall history of SQL and the patterns around it that it's only in, I'll say, the past decade, maybe
66
00:12:26.240 --> 00:12:30.100
maybe even more recent than that, that we've really started to invest in
67
00:12:30.400 --> 00:12:43.855
SQL as a version controlled language rather than it being just a pile of SQL scripts that somebody has in a drive somewhere or stored in their DBMS or stored procedures. And I'm wondering how that change in the approach
68
00:12:44.475 --> 00:13:00.245
to SQL, particularly in the analytics space, has changed some of the ways that people think about what SQL or, I guess, the job of SQL and the ways that they're using it and, in particular, the complexity of the operations that they're trying to do versus just doing
69
00:13:00.785 --> 00:13:07.445
relying on maybe ETL scripts where most of that logic is in a programming language that happens before the data even lands in the database.
70
00:13:08.305 --> 00:13:41.250
Yeah. No. I think, again, this is a place where Looker was kinda forward looking. So, you're crafting some sort of, data experience. You have, you know, your users and your transactions and and then the, you know, your business things that that are happening. You're trying to build dashboards and insights based on that. And there's what that looked like yesterday, and then there's the better thing I'm trying to do today that's gonna be a new version of that. So Looker had a full IDE and with branches and commits and all those things and some way for you to roll stuff out, like real software engineering, not because we're brilliant. We're like, we know how this works in software and this data,
71
00:13:41.970 --> 00:14:27.845
interface that we're creating is just software. And we just want the things that we know to be good in software to also be good in data engineering. And so, this isn't to us, it's like we already decided that these curated data experiences are things which which change over time and are software and should be treated like software. And so it was like that what was a was a no brain. The more interesting question to me is there's still sort of two data universes that are not well integrated, and I I sometimes wonder why that is. One is all of the things that we would call data, you know, like columns of numbers and names and and time stamps and stuff. And then there's the other data, which is this curated set of, you know, data transformations. And they just sit in completely different places and have completely different rules for, like, how you access them and how you change them.
72
00:14:28.325 --> 00:14:37.865
And and if you say, well, we should put all of Malloy code in the database. It's like, well, that really breaks because databases are designed to have certain kinds of, you know, controls,
73
00:14:38.339 --> 00:14:47.000
over, like, who can do stuff and who cannot do stuff. And it gets really clumsy to try and, like, oh, I wanna add a new table. Oh, gosh. You need to file a form with somebody
74
00:14:47.380 --> 00:14:49.139
to to add a new table because
75
00:14:49.620 --> 00:15:15.070
I mean, not not because there's bureaucracy. There are reasons that there are controls like that on the data, and we think of data as a thing which has a certain kind of controls. I think a source code is things that have a different kind of controls. It continues to feel to me like if we just thought about this better that all data could sort of live in a beautiful, world where, you could get, version control on in branches on data like you do with source code. Right? And you could get sort of infinite shareability because everybody's looking at the same data,
76
00:15:15.770 --> 00:15:26.915
that that they could they could live together. But I don't know how that works. Yeah. It's definitely another whole problem that people are working on solving, just the idea of versioning data and the challenges of data gravity. And
77
00:15:27.295 --> 00:15:48.190
Well, and and and we know that it's it's it's it's a great thing because we in this other world of data, we do that all the time, and it's super useful. And if you think about it well, it it it's not that expensive. Right? But you just need to have a high enough level of of view of what it is to actually implement it. I think another interesting element of what you're doing with Malloy is
78
00:15:48.665 --> 00:15:49.805
the audience where
79
00:15:50.264 --> 00:16:26.615
SQL as a language was originally conceived of as something that was close enough to English that business users would be able to write it and execute it, which never truly came to fruition. There are pockets where there are people who are somewhat removed from the engineering department who do write SQL and are able to explore things and fulfill their needs, whereas Malloy is much more targeted at, a software engineering audience. It's treated as a programming language. It has elements of composability and reuse, which SQL never really incorporated. And I'm wondering how you think about the design of the language
80
00:16:27.235 --> 00:16:32.350
given the original formulation of what SQL was supposed to be versus where we are today?
81
00:16:33.050 --> 00:16:43.769
Of all the people in the Malloy project, I'm the perfect person to ask about this because I've thought about this more than anybody. So there was a a long there's been a long standing experiment in programming languages to make them like human speech, and,
82
00:16:44.605 --> 00:17:49.684
none of those have been long term successful. They all sort of like bubble up and then go away. And and I've I have my theory about that, which I will now stand on my soapbox and and and speak of, because a lot of this, goes into why Malloy is designed like it is. One of the most important activities in some sort of long lived maintainable piece of software is, I open it up and throw it on a page. And then with my eyeballs, I understand something about its structure. And so it's like I'm flying over a city and I can see that there's the football stadium and there's the high school at right, and there's the place where people live, and there's the thing that's obviously the industrial district. And I kinda I can kinda see at 10,000 feet where those pieces are. And then, you know, if I had an airplane that I could steer, I would then go land in the place where I needed to land and then walk around and do the things that I needed to do. And so when you're scanning a piece of code and it's a conversation, right, you have to read the whole conversation to know what the pieces are that you need to change. And so having some textual markers so that as your eyes scan across it, you know what parts you don't have to read is is super important. And a lot of that has gone into design of why Molloy,
83
00:17:50.304 --> 00:17:51.044
is designed,
84
00:17:51.345 --> 00:18:00.150
both top to bottom and left to right where it is so that you can get to the part that you need to care about and not the parts that you don't care about. And so I have tremendous respect for,
85
00:18:00.450 --> 00:18:00.850
let's let's
86
00:18:01.730 --> 00:18:07.910
I I I often use the word poetry for the poetry, of a programming language that, when you when you read a sentence,
87
00:18:08.210 --> 00:18:35.409
you you know, in your gut what it means, right? That the words make sense to you. And so like that, Pearl would be the worst language in the universe for this. There's no poetry in Pearl. I've written significant applications in Pearl. I don't wanna say bad things about Pearl, but there's, you know, been around a long time and did a lot of things. But it being poetic is not one of the, the things that's that's great about Pearl. Pearl is only poetic, after you're a Pearl expert. And so so there's a human there's a there's a human dimension that that individual sentences
88
00:18:35.710 --> 00:18:44.915
need to read and scan like, oh, I know I don't even know the language, but I kinda know what you're doing there that matches well to to SQL where you're saying, you know, sum of cost, right,
89
00:18:45.535 --> 00:18:46.035
from
90
00:18:46.335 --> 00:19:07.605
table. Right? Oh, I I know what's happening. And there's a that whole thing is a sentence which scans to human beings. So in the design of Malloy, we started with, we're just gonna sit inside of SQL. Like you could write a select statement and an whatever the Malloy version of a select statement is, which we originally called an explorer. And and you could there there there's a tool which turns the the Malloy into SQL,
91
00:19:07.985 --> 00:19:52.420
and it's just sort of like a a a macro. And sometimes I I regret walking away from there and that the the reasons that we did that are are interesting, but we eventually walked away from there. And and our our new position was most of our users are experts in SQL. And so we don't wanna be surprising to them in places where we don't have to be. But we do wanna be surprising to them in places where they actually need to think about data differently to get stuff done in Malloy. So we have a where statement and a having statement, and we use those words because that's what SQL users expect. Right? And, and, select and select. So we try to use SQL words and SQL gestures wherever we can, because not because we think that's the best way to talk about data, but because all the people who are really experts in data already know those things, and we wanna be useful to
92
00:19:54.340 --> 00:19:57.800
I think that the elements of the language are very
93
00:19:58.100 --> 00:20:00.200
important, and I think it's also interesting
94
00:20:00.660 --> 00:20:01.160
the
95
00:20:01.684 --> 00:20:25.580
ways that you are constrained in terms of the keywords and semantics of the language when you do have another target language that people are actively developing in. So I think the closest parallel that we have is probably things like TypeScript to JavaScript JavaScript or the various JavaScript transpiled languages that we've had over the past few years. And I'm wondering just how having that target representation
96
00:20:25.960 --> 00:20:28.780
as the intermediate state and the existing
97
00:20:29.160 --> 00:20:30.300
corpus of
98
00:20:30.865 --> 00:20:32.005
experience and
99
00:20:32.465 --> 00:20:33.445
reference material
100
00:20:33.904 --> 00:20:40.005
changes the ways that you think about what the language can and should do and how it can and should be structured?
101
00:20:40.625 --> 00:20:58.280
Okay. Well, there's there's a bunch of different questions. First first of all, I wanna say this about JavaScript. Like, I wish I could remember the name of this guy. So this is I think it's, like, a gentleman at Microsoft who did a really interesting video, and his video was, let's look at, like, a transition from c to c plus plus and the transition from JavaScript to TypeScript. Because one of those is,
102
00:20:59.235 --> 00:21:21.910
I mean, at this point, people who are switching from c are switching to Rust instead of c plus plus, like, c plus plus never really replaced c. And TypeScript replaced JavaScript, like, you turned around and suddenly everybody was programming in in TypeScript. And he was theorizing about why. Well, what's the difference between those two things? Because, maybe as language designers, we we could learn from that. And and he was trying to kill c plus plus.
103
00:21:22.290 --> 00:21:32.235
And so he wanted to learn that lesson so that he could, you know, kill it better. And and one of his theories was that the thing that TypeScript did really well is it sat in the tool chain.
104
00:21:32.695 --> 00:21:52.935
Right? Like, any place where where you needed JavaScript, TypeScript sat perfectly, so it was not hard to decide to use TypeScript at at any one time. And in fact, you could write a line of TypeScript and then a line of JavaScript and then a line of TypeScript if if you wanted to in one file and and it would work okay. So in in Malloy, the relationship with Malloy and SQL, because we just start generating SQL,
105
00:21:53.415 --> 00:22:19.710
you can, with some limitations, write some Malloy and some SQL and some Malloy and some SQL and sort of build a computation or set of computations that that use, mixed expressions. And you could choose the expression that works best for you. But the the initial reason that we did that is that initially, Malloy didn't do very much. Right? And so, yes, if you wanted to, you know, group by some things and and add up some numbers, Malloy would be great. But as soon as you wanna do something complicated like a window function or
106
00:22:20.075 --> 00:22:36.410
then you'd have to go to SQL. And we wanted to be able to show Malloy doing interesting things. And so we had a way to say, oh, and there's this little piece of SQL here in this corner. We don't yet implement that, but, you know, here's this competition, which is all in Malloy for this little piece of SQL. And and the number the reasons that you would escape to SQL,
107
00:22:37.030 --> 00:22:43.770
continue to exist, and we would like to make them all go away, but they still continue to exist. Okay. So that was on the relationship between
108
00:22:44.070 --> 00:22:46.630
one language and another. So we would love to be,
109
00:22:47.110 --> 00:22:48.490
as easy a decision,
110
00:22:49.205 --> 00:23:01.865
as like the Java and JavaScript. Those are really two different languages. They have the same name for reasons. And actually I was at Netscape when JavaScript was invented and so was Lloyd and we were both involved in that. And so I know a lot why JavaScript is called JavaScript.
111
00:23:02.405 --> 00:23:04.140
It was called Live Script at one point.
112
00:23:04.700 --> 00:23:14.460
We thought that Java was gonna take over the universe, and we thought that calling it JavaScript was was gonna be the the thing which made people take it seriously because it was just as powerful as Java.
113
00:23:14.940 --> 00:23:35.580
So but TypeScript and and and JavaScript is is a great example. We would love to be as as sort of fluid and fluent between those two, as possible, which is, again, another reason why we would like them to as much as possible not have cognitive dissonance between them, except where where it's necessary. Kinda felt like there was a third point. And so that was the other thing that I wanted to say is that, in SQL,
114
00:23:35.960 --> 00:23:41.899
the most important verb in SQL is the join. Right? That all the magic of computations
115
00:23:42.279 --> 00:23:50.395
come from being able to write creative joins. Right? And that the people who are really good at writing SQL can do things with joins that blow your mind.
116
00:23:50.775 --> 00:23:58.155
But so I've been working in data for over a decade now, so I'm not a complete newbie. But I cannot tell you the number of times,
117
00:23:58.455 --> 00:24:39.720
in the last year that I've had to sit down with an LLM and say, hey. Let's talk about join types again. You know, explain them to me again so that I can make sure that I'm thinking about this right as I do this thing because, really, I wanna do this kind of operation. But what do joins do, and and how is that useful to do the thing that I wanna do with data? They're just it's just not the way that humans think about data. It's the way that a mathematician thinks about data, which is an important thing because the fact that they are mathematical is super useful. Right? But the the pieces of joins that you need to use, you'd like to be able to express those in a way which is human understandable. And so that's a a place where where SQL where looking at it like SQL as opposed to looking at like Malloy,
118
00:24:40.260 --> 00:24:43.000
is a big difference and you need to do it differently,
119
00:24:43.545 --> 00:24:48.285
and expose different sometimes I call them nouns and verbs. Expose different nouns and verbs,
120
00:24:48.745 --> 00:24:55.320
in the universe of the objects that we're we're talking about than SQL does in order to make the language better. Yeah. I I said that.
121
00:24:58.440 --> 00:25:06.940
Are you tired of data migrations that drag on for months or even years? What if I told you there's a way to cut that timeline by up to a factor of six while guaranteeing accuracy?
122
00:25:07.845 --> 00:25:17.145
DataFold's migration agent is the only AI powered solution that doesn't just translate your code. It validates every single data point to ensure a perfect parity between your old and new systems.
123
00:25:17.845 --> 00:25:28.400
Whether you're moving from Oracle to Snowflake, migrating stored procedures to DBT, or handling complex multisystem migrations, they deliver production ready code with a guaranteed timeline and fixed price.
124
00:25:29.179 --> 00:25:34.480
Stop burning budget on endless consulting hours. Visit dataengineeringpodcast.com/datafold
125
00:25:34.620 --> 00:25:39.395
to book a demo and see how they turn months long migration nightmares into week long success stories.
126
00:25:41.935 --> 00:26:02.320
And then another aspect of building any sort of programming language is the ecosystem and the tooling around it and the capabilities of what it can integrate with. And I'm interested in how you're thinking about that as far as Malloy and the adoption of it and some of the, I guess, stumbling blocks or challenges
127
00:26:02.620 --> 00:26:04.745
of being a new language
128
00:26:05.125 --> 00:26:08.505
and, in particular, targeting a very prominent
129
00:26:08.965 --> 00:26:09.465
existing
130
00:26:09.845 --> 00:26:13.145
audience and existing set of workflows and tool chains?
131
00:26:14.325 --> 00:26:26.640
Yeah. That you're really good at what you do because these are great questions. So currently, Malloy is written entirely in TypeScript. If you have a TypeScript runtime, you you can compile Malloy to SQL.
132
00:26:27.100 --> 00:26:28.720
You can run queries,
133
00:26:29.495 --> 00:26:53.970
and you can get the data back from your queries and process them into charts and things. And that that's all sitting in one in one TypeScript runtime. And, there were a couple of reasons that we made that decision. One was Looker was entirely written in Ruby because it was the cool language thousand years ago when we started Looker. And so we understood a lot of them, and I have a deep love for Ruby. There's there's something really poetic about that language, which I will which I'll always cherish.
134
00:26:54.669 --> 00:26:55.970
But in terms of
135
00:26:56.270 --> 00:27:13.820
a foundation for an application that there we weren't entirely happy with all the things about Ruby. And so we on purpose picked TypeScript because it it felt to us like this was a language that was powerful enough that it wasn't gonna get in our way, but that the type checking in TypeScript and and the extension of type checking to actual interfaces,
136
00:27:14.360 --> 00:27:18.380
as opposed to just functions and, and, and variables was just a
137
00:27:18.840 --> 00:27:37.085
super powerful tool that was gonna let us do things. And we just felt like it looked like TypeScript runtimes were gonna be everywhere. And if we ran in TypeScript, we could run what all the places that Malloy needed to go, we would be able to get there with TypeScript. And and we just have gotten a tremendous benefits from from that one decision. Today,
138
00:27:37.465 --> 00:27:42.490
if I could have a wish, I don't know if I would have wished that we had done it all in Python,
139
00:27:42.870 --> 00:27:43.770
because today,
140
00:27:44.230 --> 00:28:24.335
a lot of the really important and interesting work that's happening in in database happens centered around Python. And if Malloy and you can, download a Python package and compile Malloy, but that Python package works by having a node runtime and talking to it and then getting the data out of the node runtime and putting in a Python thing, which I'm not I'm not the marketing guy from Malloy. That's just a bad way to do stuff. It it it's a nice demo of capabilities, but it doesn't it doesn't feel great to me as a software engineer for the thing that I would like to to to ship to people. But in order if I were in Python, all the things that we can do right now because we're in TypeScript, we would kind of lose because Wasm compiled Python works. Right?
141
00:28:24.635 --> 00:28:41.980
In the same way that using Malloy from Python works, it's it's not it's not pleasant. And so, you know, I know in an in an infinitely great world, we would have a two parallel implementations of Malloy. One was in pure Python and one was in pure TypeScript, and and there was some way to keep them in sync. I I don't know what the the right answer to that is.
142
00:28:42.360 --> 00:28:52.755
Although I do spend I have an LLM context. I have it where I just talk about this problem to the LLM, and I say, hey. What about truffle? Hey. You know? What about,
143
00:28:53.294 --> 00:29:11.360
if we did this like this, what would it do that? And and then in the end, you know, I say, yeah. But if we did that, this would be a problem. At the end, the LLM says, yeah. It looks like you have a big problem. I don't know if it's solvable, but I keep dreaming that there's some way that that it is solvable. There's only one piece of core Malloy, and it's sort of available everywhere to all people.
144
00:29:12.164 --> 00:29:19.144
Seems like the natural next step would be to just rewrite it all in Rust, and then you can compile to a WASM binary and link it to Python.
145
00:29:19.684 --> 00:29:25.225
That that was, one of the LLM's suggestions was was to rewrite everything in Rust.
146
00:29:25.799 --> 00:29:39.019
And and that's actually yeah. I I well, at at that time, I went, okay. I understand the size and scope of that project, and I can't afford to stop working on Malloy long enough to take the time to do that. But I hope that someday,
147
00:29:39.735 --> 00:29:55.035
somebody says it's time to do that. And and the day that somebody does that is the day that I know that Malloy has has has won. And so my goal is for that to be a real project. But you you went right to the thing that it took an LLM and I days to come to, so that was quite insightful.
148
00:29:57.140 --> 00:29:58.360
And the other aspect
149
00:29:59.140 --> 00:30:01.080
of language ecosystem and integration
150
00:30:01.540 --> 00:30:03.960
is the set of tool chains around it
151
00:30:04.420 --> 00:30:21.035
where being in TypeScript, obviously, there are a lot of potentials for integrating particularly in front end style applications. I know that you have the Versus code plug in, which I'm sure was a very natural extension given the language that you're working with. But I'm also interested in things such as how it's being integrated into
152
00:30:21.495 --> 00:30:33.510
orchestration tools and data work flows and whether this is largely being used in the context of interactive notebooks for people who are doing exploratory analysis and then generating data visualizations,
153
00:30:34.370 --> 00:30:36.549
or if it is a potential
154
00:30:37.355 --> 00:30:47.055
replacement or augmentation to something like a DBT for being able to be the actual transformation engine for a sequence of data workflows, etcetera.
155
00:30:47.755 --> 00:30:57.130
Yeah. Well, the marketing answer is all of those, but the the the real answer is, so the we have a Versus code plug in which works as a sort of an IDE to Malloy.
156
00:30:57.669 --> 00:31:01.929
And then we have a an extension to our extension which lets you open up notebooks.
157
00:31:02.230 --> 00:31:08.235
So I can have, you know, some text and and some Malloy query and some text in the Malloy query, and I can successively, you know,
158
00:31:08.875 --> 00:31:09.855
transform data,
159
00:31:10.715 --> 00:31:17.375
in in in the course of a notebook. And there have been experiments, and I don't I'm not up on all the of integrating with Python notebooks
160
00:31:17.755 --> 00:31:18.495
with Malloy.
161
00:31:18.875 --> 00:31:23.535
So if Malloy is the right way to to write a query, there's there's a way to to use Malloy
162
00:31:23.920 --> 00:31:34.180
in in a notebook like situation. And if it doesn't work in your notebook today, it wouldn't be that hard because the core run time is already browser resident. Right? So and that's and that's a goodness.
163
00:31:34.560 --> 00:31:46.955
For transformation pipelines, the the unit of, because transformation pipelines are operating on SQL, the one of the units of transformation is a table. Right? And Malloy works natively with tables
164
00:31:47.335 --> 00:31:53.450
because otherwise it wouldn't be interesting. And so you can write any stage of a transformation pipeline in Malloy.
165
00:31:53.990 --> 00:32:06.275
And for that job, if your transformation is at all complicated, it Malloy is a great language to write a stage of a transformation pipeline. But and and, there are lots of people who we have pull quotes from people who write SQL for a living in law. And
166
00:32:06.655 --> 00:32:16.915
a very common pull quote is if I could figure out how to never write SQL again and program in Malloy the rest of my life, I'd be the happiest guy ever. But the problem of saying that the table is the unit of transformation,
167
00:32:17.855 --> 00:32:21.040
is back to what I said at the beginning, that data experiences are interactive
168
00:32:21.420 --> 00:32:40.745
and ongoing. And so as soon as I make it a table, all of the context I use to create that table is gone. All I have is the end. And so if I then want to ask a different question of that table, which the context that produced the table could have answered, but the table can't answer. If all I have is the table, I can't answer the next question. And so in Michael's imaginary
169
00:32:41.125 --> 00:32:45.705
future world, transformation pipelines are centered around Malloy and not around SQL
170
00:32:46.005 --> 00:32:50.585
so that the context continues to to move down the chain. And so you could make decisions
171
00:32:50.890 --> 00:33:11.205
like, you know, what aggregation level do I wanna do different computations at? Because you you have access to all the computations that produce this answer, and all the connections, and you can make, efficient things about that. Today, because we do just read and write SQL, anytime you just wanna do a transformation one kind of SQL to another kind of SQL, we have a command line version of Malloy,
172
00:33:11.505 --> 00:33:21.390
and you could use it with DBT to do that. But the other thing that's happening with Malloy's and this is maybe a problem with our vision, is that it's not focused on a particular application,
173
00:33:21.850 --> 00:33:24.430
but on a a way of thinking about data.
174
00:33:24.890 --> 00:33:27.470
Are the places where MOLO exists,
175
00:33:28.810 --> 00:34:02.039
we've gone wide but not deep. So there's a thing called Malloy CLI, which lets you, you know, run Malloy queries, from a Unix command prompt. But it's not really well integrated with DBT. And today, somebody really should just say, you know, if you wanna do DBT pipelines with Malloy, here's the plug in and you're done. We didn't do that. We said, let's spend a a day and a half. Well, maybe four days. I don't know. Some small period of time making a CLI version of Malloy and stick it up on the repository and and move on. And there's a lot of that in in Malloy where we can sort of demo.
176
00:34:02.535 --> 00:34:15.060
Here's what it would look like if Malloy sat in your universe, but there is no universe where Malloy is really sitting, where it's fully polished. The the most polished experience for Malloy is is the Versus code extension because that's where we sit.
177
00:34:15.620 --> 00:34:20.360
I think another interesting aspect of that whole question is also
178
00:34:20.900 --> 00:34:30.545
where I think Malloy does have a lot of strengths is that question of the semantic layer where it has been a bolt on to things like DBT or a completely separate environment
179
00:34:30.845 --> 00:34:41.085
or a whole bunch of YAML with some SQL if you wanna have a metrics layer. And then another angle of the overall problem that DBT, for instance, is solving is the
180
00:34:41.724 --> 00:34:51.280
I'm hesitant to say lineage tracking because there are a lot of gaps in terms of dbt's out of the box capabilities, but that ability to understand the sequencing of
181
00:34:51.740 --> 00:34:53.840
transformations and managing their orchestration.
182
00:34:54.540 --> 00:35:02.725
And then to add another layer on this, the the recent acquisition of SDF project that added a lot of very
183
00:35:03.185 --> 00:35:06.165
enriched type details where you could, for instance,
184
00:35:06.545 --> 00:35:11.525
flag a column as having PII of a certain type and then have that propagate through that orchestration
185
00:35:11.825 --> 00:35:24.960
graph so that at the end result table, five steps removed from the source system, you still have information about whether that is PII so that you can apply appropriate controls or various other type constraints, etcetera. And I'm just wondering
186
00:35:25.260 --> 00:35:27.200
how you're thinking about the
187
00:35:27.585 --> 00:35:32.724
potential for Malloy to be able to address any or all of those capabilities.
188
00:35:34.065 --> 00:35:36.865
Yeah. Okay. So that's a really big thing. And so I'm gonna
189
00:35:37.505 --> 00:36:11.500
I I'm a guarantee you I'm gonna forget what I'm doing on the way to this one. One of the interesting things about Malloy is that we can attach random metadata to things that you query sort of at the let's call it the table level. Malloy, it's called a source and and then a column level as well. And so there is a way sort of attach arbitrary metadata that you can attach, business logic to sort of any part of a query, and it sort of carries along through the query. And one of the and this is just our experience in the universe of doing Lookers. Like, we kind of know what capabilities you're going to need to build these these data applications
190
00:36:11.960 --> 00:36:13.420
on. And so,
191
00:36:13.800 --> 00:36:16.380
we're we're we're creating these mechanisms,
192
00:36:16.840 --> 00:36:36.010
but there's no, thing on top of the mechanism. There's just a mechanism going on. Pretty sure this mechanism is gonna scale to to answer a whole a whole bunch of problems. I've already lost this thread. Let's go right back to the question. There are a number of layers that I threw at you. So the first one, I think, was the managing of sequencing of transformations
193
00:36:36.470 --> 00:36:45.450
so that being able to, for instance, use the source element in Malloy to understand that I have to wait until this previous stage has
194
00:36:45.765 --> 00:36:50.984
executed before I can actually have that source present to be able to execute the next stage. And then
195
00:36:51.285 --> 00:36:55.305
on top of that, being able to incorporate some of the rich type information
196
00:36:55.845 --> 00:36:56.825
and annotations
197
00:36:57.125 --> 00:36:58.744
of a particular column
198
00:36:59.045 --> 00:37:02.980
that doesn't exist within the data itself, so being able to attach
199
00:37:03.280 --> 00:37:19.704
richer metadata to be able to have that flow through the overall execution graph. Alright. And well, and the other thing was is about this this distinction now we have that we're trying to do where there's semantic modeling or or metrics definitions. So we're all sort of wrestling with the same problem, which which, again,
200
00:37:20.005 --> 00:37:23.704
I mentioned at the beginning is that data experiences are are are interactive
201
00:37:24.085 --> 00:39:01.210
and ongoing. And that the way that data is and the other interesting is that the is that the way that data is written into a database, right, is here are the pieces of data that I have. I need to put this on a disk right now because I have better things to do with my life. And I don't know what anyone's gonna wanna compute from this, but I know that this piece of data, like, you know, what time I did this and how many molecules that are in the air at that instant time needs to be written down and people will be able so there's the data in the day and then there's the information that we need, which is, you know, always some sort of computation and aggregation on those. And we would love to be able to think about data as the as the information that we care about, and not as the numbers that were used to produce it. And so this idea of a semantic model where I can think about data in the way that's meaningful to me as a, you know, my business or whatever my my activity is and not care that that's not the way it's stored. Right? I can pretend that it's stored like the the thing that I care about is a sort of a bolt on. It's like, oh, you have SQL and then you have this other thing. But in Malloy, that's that's integrated. And anytime that I think about, oh, you know what? I would really like to be able to think about as if this column existed, and it's actually a computation of these other things with this join, but let's just pretend it's a column. In Malloy, it as soon as you write that, it essentially is a column. It acts like a column. You don't know that it's not a column. Everyone in the universe can treat it treat it like a column. And so and the way that this interacts with the idea that data is iterative is that, in asking a question about this, like, I'd like to know this thing, and it's essentially a column. I have now made the data world richer because I answered that question. And now my answer to that question is part of the landscape that I'm standing on to answer the next question. And so it's not, you know, the table is over here and the answers are over here. It's as we ask questions,
202
00:39:01.830 --> 00:39:04.570
the table gets better. And so we have a slightly different,
203
00:39:04.870 --> 00:39:27.319
one of the things we said earlier early on in the dimension of Loy is that a semantic model without a semantic query language is not interesting, that the semantic model and the query language need to be integrated. And we don't really have all the insights about why we were saying that, but we were saying that a lot. And I think the thing that I'm saying now about data experiences being interactive and ongoing is is a better way to to encapsulate that. That felt like that's the answer I wanted to say.
204
00:39:28.180 --> 00:39:38.375
I think circling back around to the construct of Malloy as a programming language and a language ecosystem, the other question then becomes that of packaging,
205
00:39:38.675 --> 00:40:08.565
where what is the unit of exchange for being able to say, I have a defined set of queries that will give you a useful view on this set of common data where maybe somebody has an export of their HubSpot or their Salesforce data. Here's the set of views and queries that will give you useful insights about that out of the box that you can then build on top of. How do I now take that, package it up, post it somewhere that somebody else can just download it and start using it on their own work?
206
00:40:09.025 --> 00:40:15.825
Okay. That's a great question, and I and I'm the right person to ask. So we're trying not to invent the universe where we don't need to. And so,
207
00:40:16.305 --> 00:40:31.610
we have a an import statement where you can say, hey. There's another collection of Malloy, and there's these objects in there that are interesting to me. Please them import them into this thing. So I lost a fight inside of the Malloy team about the word model because model means
208
00:40:31.990 --> 00:40:32.490
anything.
209
00:40:32.925 --> 00:40:38.865
If you, like, if you could read all the code from whatever you're interacting with on your your thing all the way down to the,
210
00:40:39.245 --> 00:41:01.670
you know, to the bits, the meaning of model would change six times by the time you got down to the bits. So everyone uses the word model, and it means nothing. Right? And so, one of the things I said is can we please not use the word model anywhere in Malloy? Let everyone else use the word model in any way that they want. But for the thing that we though they were calling model internally now, let's use a different word. I I lost.
211
00:41:02.805 --> 00:41:04.025
So in Loy,
212
00:41:04.565 --> 00:41:05.625
a model is,
213
00:41:06.325 --> 00:41:07.204
represented by,
214
00:41:07.685 --> 00:41:23.450
one one file, say, and it's it's sort of like a JavaScript model in that it has objects that are defined and it can import objects from other models into its scope in order to do the modeling and computation that you have. And a model consists of basically, queries and sources,
215
00:41:24.710 --> 00:41:26.090
that that that answer
216
00:41:26.470 --> 00:41:27.690
a particular question.
217
00:41:28.135 --> 00:41:31.435
One of the interesting things about Malloy is is that there's this idea
218
00:41:31.735 --> 00:41:39.015
that, I could like, let's pretend I had this brilliant insight about how to do computations on, two way,
219
00:41:39.495 --> 00:42:13.800
two way markets with where buyers and sellers are selling to each other. And I just I had this brilliant idea about how to do that. You could define an abstract model that says, here's everything I need to know about two way markets. I just don't know what your users are and what the transaction is and what the value of the transactions. If you write those three things, right, then you can you can use my two way market things, and you can get graphs and stuff so that so that there's a there's a way in Malloy to sort of have sort of abstract business logic that's abstract from all business. Right? And you you can map it to that. And so there's this composition at the the the composition
220
00:42:14.660 --> 00:42:15.880
blocks are a
221
00:42:16.340 --> 00:42:29.724
a source, and a sort the simplest source is a is a table. It's a set of columns and a and a schema. And but, instantly in in Malloy, you you start saying, well, there's a a thing, which is if something is has no ship date
222
00:42:30.105 --> 00:43:04.165
and and and this and that, then there's a Boolean flag that I would like to imagine as a column because I'm gonna wanna filter on that Boolean flag. And so I'm gonna say, here's the Boolean expression for that flag, and that's now like a column in the database or, you know, here's the revenue computation or here's the value of this transaction, which is some secret thing. And so you extend sources and none of the this isn't a query. This is just saying, here's different computations that are interesting doing this or here's an interesting aggregation. And, also, the other thing that happens in a source is that in Malloy, the user view of data this is really important. I should have said this an hour ago. All data is hierarchical. Right? It's not stored in hierarchical form for for reasons.
223
00:43:04.545 --> 00:43:26.895
And for any particular kind of data, sometimes it's better to store it normalized, and sometimes it's better to store to store it, you know, as a graph. And one of the problems with SQL is that those two ways of storing data change how you can interact with the data. And in Malloy, that's all data is hierarchical. You treat it like it's hierarchy. There you have dotted paths to things. And whether it's a join or a repeated set of records, it does matter in Malloy.
224
00:43:27.215 --> 00:44:05.759
You the user view of the data is a hierarchy. And so in a source, one of the things happening in the source is that you're explaining what the graph is that's interesting. You know, I wanna join this in and that join is a source, and it might also have other joins as all that happens. So there's sources, which end up being the suit these sort of rich descriptions of of how you'd like to think about the data. And then there's queries, which are, like, given that source, here's some interesting computation or here's some dashboards and and things. And then the other interesting thing about Malloy is that a query is a source because it it the output of a query is just a table, and and that can be the the place for a new piece of modeling. And so there's the the circularity. So when you're doing a a model, I hate that word, in Malloy, you start with some sources and then you write some queries.
225
00:44:06.059 --> 00:44:13.359
And maybe you're pulling some of your sources or just tables or maybe their sources that have been described in other models. That answer the question? I think so.
226
00:44:14.140 --> 00:44:17.039
Yeah. And the other aspect of
227
00:44:17.575 --> 00:44:21.835
Malloy and particularly to your point of data being hierarchical
228
00:44:22.535 --> 00:44:26.155
and the restrictions of having these flat representations
229
00:44:27.015 --> 00:44:29.355
is that it has led to a proliferation
230
00:44:29.735 --> 00:44:30.395
of various
231
00:44:30.950 --> 00:44:40.410
opinions on how best to structure your data, particularly for analytical use cases where we have star schemas, snowflake schemas, data vault, anchor modeling, etcetera.
232
00:44:41.029 --> 00:44:41.529
And
233
00:44:41.829 --> 00:44:43.289
once you move to
234
00:44:43.985 --> 00:44:46.645
a workflow where you do have that hierarchical
235
00:44:46.945 --> 00:44:47.445
capability
236
00:44:47.745 --> 00:44:51.765
modeled into the language itself, how does that change the ways that teams
237
00:44:52.145 --> 00:44:53.445
think about the
238
00:44:53.745 --> 00:44:56.965
fundamental structure of their data at the storage layer?
239
00:44:57.970 --> 00:45:04.290
I'm really happy to say I'm the wrong person to answer that question. So I'm the I am so deep in, you know, how do I,
240
00:45:05.250 --> 00:45:06.870
parse this computer language,
241
00:45:07.410 --> 00:45:25.035
that we've invented into this other computer language that exists in the world? And and how do we make sure that the people who write this computer language, user facing things, have good user experiences, and how to make sure that the language, the code we generate is efficient and runs. I know everything about that. I I actually don't have a lot of conversations with people who who write Malloy.
242
00:45:25.400 --> 00:45:33.420
So I I I feel kind of lucky because I get to dodge the question, but I'm also, like, really honest. Like, I if I've answered that question, I'd just be making crap up.
243
00:45:33.880 --> 00:45:37.100
Fair enough. Well, from your experience of
244
00:45:37.400 --> 00:45:47.194
building this tool chain, working with the people who you have been exposed to who are actually building with it, what are some of the most interesting or innovative or unexpected ways that you've seen it used?
245
00:45:49.575 --> 00:45:53.030
There are more than I can count on two hands, but,
246
00:45:53.510 --> 00:45:55.290
not enough people to fill an interesting,
247
00:45:55.910 --> 00:46:05.930
concert hall. People in the world who use Malloy for daily work to to find answers to questions that that that they don't know the answer to. They know that it's hidden somewhere in the data.
248
00:46:06.305 --> 00:46:07.685
And I am continually
249
00:46:08.145 --> 00:46:27.119
thrilled to like, I am just thrilled every time there's another person that pops up, says, oh my gosh. I could never have answered this question. I'm Malloy. Thank you for existing. But the most surprising thing, which was completely accidental, it just happened to be the right place at the right time, is because Loy is designed for humans to understand. It turns out to be a really good target or LMS to generate.
250
00:46:27.500 --> 00:46:29.280
Like, I want to make a really good,
251
00:46:29.900 --> 00:47:15.645
query experience for users, and I want them to interact in a natural language. And then I wanna generate something which actually generates queries. There are a number of efforts in the universe of people saying, Malloy is the correct semantic layer for us because it's a both a semantic layer and a query language. So once something is described in Malloy, it can also be queried in Malloy, and it's working really well in a number of places, which was a surprise to me because I didn't know what an AI was at all when we started Malloy, however many years ago it was, even though that's sort of the time that AIs were starting to become smart and useful. So that's probably the the biggest surprise is how useful it is in the AI world because it was never, designed for that, and it's just an artifact of the fact that because it's designed to work like a human brain and LLMs are some kind of mapping of the human brain, that it's really useful that.
252
00:47:18.390 --> 00:47:20.410
You're a developer who wants to innovate.
253
00:47:20.710 --> 00:47:29.369
Instead, you're stuck fixing bottlenecks and fighting legacy code. MongoDB can help. It's a flexible, unified platform that's built for developers by developers.
254
00:47:30.204 --> 00:47:40.224
MongoDB is asset compliant, enterprise ready with the capabilities you need to ship AI apps fast. That's why so many of the Fortune 500 trust MongoDB with their most critical workloads.
255
00:47:40.845 --> 00:47:45.779
Ready to think outside rows and columns? Start building at mongodb.com/build
256
00:47:45.779 --> 00:47:46.279
today.
257
00:47:48.819 --> 00:47:49.799
And in your experience
258
00:47:50.099 --> 00:47:50.599
of
259
00:47:50.900 --> 00:47:53.960
working on this project, building this language, exploring
260
00:47:54.260 --> 00:47:58.875
this problem space, what are some of the most interesting or unexpected or challenging lessons that you've learned
261
00:47:59.355 --> 00:47:59.855
personally?
262
00:48:00.235 --> 00:48:08.655
Oh, my gosh. So I I have written about this. I'm just gonna say some things that I've said before. There's a great thing and a horrible thing about about designing things,
263
00:48:09.115 --> 00:48:23.390
for people to use. And the good day, the great day when you're making tools is when you see people getting stuff done with the tool that you made and see them feeling like, oh, wow. I have got you know, I'm more productive or I'm exactly as productive, but I'm happier.
264
00:48:23.930 --> 00:48:55.530
Right? It's like, oh, I feel like I just made the world better, and and and I love that. But the other problem is that people don't want to learn new things. Like, some people do. Some people like the best thing in the world is, you know, yesterday, I solved this problem in language a day, and today, I'm gonna solve it in language b. And if I could solve it in language c tomorrow, I'd I'd be happier, and and I'll be sad when there are no more ways to solve this problem. Right? But other people, especially, like, the people that I really care about, which is, like, I'm trying to get stuff done and someone's paying me, and I wanna do that well. And I actually
265
00:48:56.045 --> 00:49:01.745
honor and respect the person who's paying me by not wasting a lot of time learning how to do this in 32 different languages,
266
00:49:02.045 --> 00:49:13.190
but being a really expert at doing it with the tools that I have. And so when I ask that person to learn a new thing, their first question is, can you please not ask me to learn a new thing but also help me? And so trying to figure out
267
00:49:13.490 --> 00:49:22.265
when something is important enough to say, hey. Please learn a new thing right now. It's gonna make your life better. And when to say, I'm just gonna do it your way.
268
00:49:23.365 --> 00:49:24.165
So for in
269
00:49:24.645 --> 00:50:24.295
so there's lots of places where we've made that decision wrong or could theoretically made it wrong in terms of, like, we're gonna ask SQL people to say that thing that they always say one way and another way. Like oh, let's just as an example, let's just take the, case statement in SQL. There is not a case statement in Malloy. There's a thing called a pick statement, which I think if you search for Michael Toy in the universe, there's a I did a high tide boy presentation about Malloy, and I ended with waxing eloquent about how much I love the pick statement, because it's this beautiful thing that's kinda like a case statement where you get to, you know, say, when data looks like this, the the results should be like this. And I think it's just incredibly elegant and beautiful, and it's a disservice to all SQL people that I don't just have a case statement. Right? And it's because I'm so much in love with the PIC statement that I that it would feel like I would be desecrating my beautiful language to put the pick statement in put the case statement in. But, really, there should just be a case statement in Malloy. And so sometimes when I've made that decision, I feel like I've made the right decision. The way that we do null comparisons
270
00:50:24.675 --> 00:50:34.420
in Malloy, if you say x not equal null in SQL, x is null, the answer to that is null. And in Malloy, if you say x is not equal, no, the answer to that is false,
271
00:50:34.720 --> 00:50:42.025
right, if it's not null and true if it is null. And so we have a we have an opinion about Booleans, which is different than SQL, which mostly,
272
00:50:42.485 --> 00:50:48.265
people are appreciative of because they're having to work around the fact that SQL is has this weird thing with Booleans.
273
00:50:48.725 --> 00:51:24.730
And so I'm okay with the weird decision we made about Booleans, which is different than SQL because there's the way to get the SQL behavior for the three people in the universe who want Booleans to be a three state value. Right? And then everyone else who just wants Booleans to be Booleans can treat them like they're Booleans, and it works. But it's really hard to know, and and you often have to do it wrong, before you do it right. Like, there's there's just no way to know except to put it in front of users and go, oh, that was wrong. I was really passionate about that. I really for me, if or my programming language, it would always work that way. But this isn't my programming language. This is a programming language for people who are engaged in data, and I need to listen to them when they tell me I was wrong.
274
00:51:25.750 --> 00:51:28.410
And for people who are interested
275
00:51:28.790 --> 00:51:34.570
in not fighting with SQL all of the time, what are the cases where you would say that Malloy is the wrong choice?
276
00:51:35.385 --> 00:51:36.345
Yeah. Okay. So,
277
00:51:36.745 --> 00:51:40.445
if I had my marketing hat on, I'd say, oh, Malloy is always the right choice.
278
00:51:40.985 --> 00:51:42.605
But the reality is that right now,
279
00:51:42.985 --> 00:51:46.205
unless you are a full time data explorer
280
00:51:46.505 --> 00:52:01.484
or unless you're really excited about the possibility of law of Molloy and you want to help us refine the vision of what Molloy does and also help us to get there, it's the wrong choice. It's a super powerful language. It does a lot of things. It's wide but not deep, and it's full of possibility.
281
00:52:01.865 --> 00:52:02.345
But,
282
00:52:02.744 --> 00:52:37.965
again, if you're a full time data explorer and you have the ability to choose your own tools, then Malloy is a 100% the right choice. Right now, Lloyd is off, doing some consulting for another company, and they said, oh, you know, what are we doing? Help us understand what we do because he, you know, has started billion dollar companies. He's a very valuable thing. And the first thing he does is like, okay. Well, if I wanna answer any of those questions, first, I need to look at your data with Malloy. Right? And now I can start to answer real questions and produce a space where when you ask me the next question, I can answer that. So I hate that most of the things you would do with data, Malloy, is the wrong choice. But, the vision of Malloy is it would eventually be the right choice everywhere,
283
00:52:38.505 --> 00:52:47.250
except for the 10 people who are really good at writing optimized SQL and that you need those people somewhere. And, hopefully, two of those guys are working on the Malloy compiler.
284
00:52:48.670 --> 00:52:49.569
And so
285
00:52:50.190 --> 00:52:51.809
given all of the potential,
286
00:52:52.109 --> 00:52:54.210
given the fact that it is,
287
00:52:54.510 --> 00:53:03.145
in your words, not the absolute right answer for everyone all the time yet, What are some of the things you have planned for the near to medium term or any particular
288
00:53:03.525 --> 00:53:06.025
projects or problem areas that you're excited to explore?
289
00:53:06.725 --> 00:53:11.385
Well, right now, there's an an active discussion about, dimensional filtering.
290
00:53:12.050 --> 00:53:23.350
It's it turns out and this is something we learned at Looker, and we haven't really folded it into Malloy because we didn't really understand what we learned at Looker. When you're doing a query, one of the interesting things about a query
291
00:53:23.744 --> 00:53:37.349
is what dimensional filtering you know, what filtering are you doing to to to reduce the amount of data? But and it turns out that that filter itself is a really interesting piece of metadata because, for example, when I join one thing, there might be a
292
00:53:37.650 --> 00:53:49.830
a an entity in that join that wants that would like to have that same filter applied to it. And if I change how I'm looking at the data as a whole, I would like that join to to to have that same change. And that's just one example
293
00:53:50.184 --> 00:53:55.645
of of a place where this idea of having enough data about your sort of low detail data about your your filtering,
294
00:53:56.184 --> 00:54:16.135
to to have that sort of be part of the modeling of your data. So then in this case, what you're modeling is a user's glance into data and and what in what ways that should change, how how the data looks. And so we're really close, I think, having nouns and verbs, to use my language, for for dimensional filtering and then surfacing that in the language so that, again, as users
295
00:54:16.435 --> 00:54:29.155
look at data, then they are modeling, hey. This is an interesting way to look at data, and we should remember that and make that available to the next person who who comes by. So that's that's that's one thing. Aggregation is super important. We've been working with,
296
00:54:29.900 --> 00:54:41.740
big companies. We were sitting inside Google as a sort of research project for a while, and then we were sitting inside Meta as a research project for a while. And so we had a chance to sort of measure Malloy against some of the biggest datasets on the planet. And,
297
00:54:42.385 --> 00:54:48.085
the way that people deal with giant sets of data is that they make, you know, successive kinds of of aggregations.
298
00:54:48.625 --> 00:54:50.645
And there's an experiment in Malloy,
299
00:54:51.265 --> 00:54:57.605
called composite sources where if you have data that's aggregated at different levels, you can treat it as if it's not aggregated.
300
00:54:57.930 --> 00:55:29.365
And then based on the query, it'll select correct aggregation and run the query in the in the fastest way possible. And that experiment showed us what was brilliant and not brilliant about that idea. But because that's the way data sets, Malloy needs to be better at working in that universe, and and we have projects in mind for that. And then all the things that SQL does that you have to currently escape to SQL too that we don't handle very well. For example, like, databases have a, you know, a giant list of functions that you can call. And sometimes it's hard to call a function because Malloy needs to know the data types of everything.
301
00:55:29.710 --> 00:55:30.530
And so,
302
00:55:31.070 --> 00:55:40.130
sometimes it's a little ugly when you need to call a function that Moli doesn't want you to call. We would like to fix all those little places where people are escaping SQL to not escape to SQL.
303
00:55:40.590 --> 00:55:42.050
The way that we do window functions
304
00:55:42.435 --> 00:55:42.935
is
305
00:55:43.395 --> 00:55:47.975
a vision for how window functions should work, but it's not complete. And so some of the things you'd like to do,
306
00:55:48.275 --> 00:56:02.690
and the way that custom aggregation is really important. We don't really support custom migration. I know. Well, we have a list, and it's long. And and we've been in data for a long time, and we understand they're important. We we feel like it's sorted well. Like, we know what order we wanna do things in, but we're not,
307
00:56:03.710 --> 00:56:05.970
you know, it would we're never gonna be done.
308
00:56:07.070 --> 00:56:10.050
Well, for anybody who is interested in contributing,
309
00:56:10.590 --> 00:56:13.810
my understanding is that Malloy is an open source language. So
310
00:56:14.115 --> 00:56:14.615
absolutely,
311
00:56:15.635 --> 00:56:22.455
I I recommend helping to move this vision forward. I'm very excited for the tool chain that you're building. If there are any specific requests,
312
00:56:22.995 --> 00:56:29.540
feel free to throw them out there. Yeah. And I just wanna say the thing about open source is that we were open source, from day one.
313
00:56:29.860 --> 00:56:48.895
One of the things that's unfortunate about LookML is that it is a a secret. It's not a hard secret to clone. But, we feel like if if there's any kind of fear that this thing is proprietary, it would've just killed it. So even though it was developed inside Google, paid for Google, day one, we were open source because we just felt like that was the only way to go forward. If you're gonna ask people to trust something,
314
00:56:49.195 --> 00:56:53.355
there needs to be some reason for them to not fear. So it's been open source.
315
00:56:53.835 --> 00:56:55.375
And up until recently,
316
00:56:55.675 --> 00:56:56.655
most of the contributors
317
00:56:57.020 --> 00:57:00.480
to the Malloy effort have been people who are on the Malloy team.
318
00:57:00.859 --> 00:57:06.720
We're getting better at being an open source community, but we're still learning how to do that. And, with with all apologies,
319
00:57:07.180 --> 00:57:10.160
please come and and and hang out with us. We're on GitHub
320
00:57:10.875 --> 00:57:11.115
and,
321
00:57:11.835 --> 00:57:23.610
and and Slack, and we'll, we'll do our best to to be helpful. And and, actually, you can show up on Slack and say, hey. How do I write this query in Malloy, this whole language? I can't be bothered to read the documentation, and and we'll we'll help you there too.
322
00:57:24.570 --> 00:57:38.510
Are there any other aspects of the work that you're doing on Malloy or the overall vision for it or the ecosystem around it that we didn't discuss yet that you'd like to cover before we close out the show? No. My brain is a a dead sponge at this point. You suck me dry.
323
00:57:39.395 --> 00:57:39.895
Thanks.
324
00:57:40.675 --> 00:58:00.870
Alright. Well, thank you very much for taking the time today to join me. Thank you for all of the great work that you're putting into Malloy. I'm very excited to see the project continue to grow. I've been monitoring it for a long time now, so I appreciate all the time and energy that you and the rest of the team are putting into that, and I hope you enjoy the rest of your day. Oh, thanks for letting me, ramble. This was fun.
325
00:58:08.204 --> 00:58:11.265
Thank you for listening, and don't forget to check out our other shows.
326
00:58:11.565 --> 00:58:12.525
Podcast.net
327
00:58:12.525 --> 00:58:21.424
covers the Python language, its community, and the innovative ways it is being used, and the AI Engineering Podcast is your guide to the fast moving world of building AI systems.
328
00:58:22.230 --> 00:58:32.310
Visit the site to subscribe to the show, sign up for the mailing list, and read the show notes. And if you've learned something or tried out a project from the show, then tell us about it. Email hosts@dataengineeringpodcast.com
329
00:58:32.310 --> 00:58:38.494
with your story. Just to help other people find the show, please leave a review on Apple Podcasts and tell your friends and coworkers.