carlineng.github.io/index.html at master · carlineng/carlineng.github.io · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!-- https://github.com/cadars/john-doe/ -->
<!DOCTYPE html>
<html lang="en">

<head>
  <!-- Google tag (gtag.js) -->
  <script async src="https://www.googletagmanager.com/gtag/js?id=G-TYMKFWKMQE"></script>
  <script>
    window.dataLayer = window.dataLayer || [];
    function gtag() { dataLayer.push(arguments); }
    gtag('js', new Date());

    gtag('config', 'G-TYMKFWKMQE');
  </script>
  <meta charset="utf-8" />
  <meta name="viewport" content="width=device-width,initial-scale=1" />
  <title>Carlin Eng</title>
  <link rel="shortcut icon" type="image/png" href="./img/favicon.png">
  <meta name="Carlin Eng" content="Homepage of Carlin Eng" />
  <!-- Recommended minimum -->
  <meta property="og:title" content="Carlin Eng" />
  <meta property="og:description" content="Homepage of Carlin Eng" />
  <meta property="og:image" content="img/site-image.webp" />
  <meta name="twitter:card" content="summary_large_image" />
  <link rel="stylesheet" href="style.css" />
  <link rel="stylesheet" href="font-awesome-4.7.0/css/font-awesome.min.css" />
  <link rel="preload" href="./img/site-image.webp" as="image" type="img/jpg" />
</head>

<body>
  <header>
    <h1>
      <a href="#home">Carlin Eng</a>
    </h1>
    <nav>
      <a href="#blog">Blog</a>
      <!-- <a href="#pbp">PBP 2019 🚴‍♂️</a> -->
      <a href="#about">About</a>
    </nav>
  </header>
  <main>
    <section id="home">
      <!-- HOME -->
      <figure>
        <a href="#img-home">
          <img alt="" src="./img/site-image.webp" width="480" height="360" />
        </a>
      </figure>
      <p> I have worn many hats in my career, including Data Engineer, Engineering Manager, Enterprise Sales, Software
        Engineer, and Product Manager; but always in the domain of data and analytics. Previously worked at <a
          href='https://strava.com/'>Strava</a>, <a href='https://www.snowflake.com/'>Snowflake</a>, <a
          href='https://www.geteppo.com'>Eppo</a>, and <a href='https://google.com'>Google</a>. Currently working on the
        <a href='https://malloydata.github.io/documentation/index.html'>Malloy</a> query language at <a
          href='http://meta.com'>Meta</a>. Avid cyclist, and proud member of the Dolphin Club in San Francisco,
        California. I use this space to post my <a href='https://carlineng.com/#blog'>musings on the topic of data and
          analytics.</a></p>
      <div style="position: absolute; bottom: 5%; font-size: .8em; color: #949494"> Made with <span
          id="emoji-carousel">🚴‍♂️</span> by Carlin Eng <br />
        <div id="social" style="width: fit-content; padding: 5px">
          <a href="https://www.facebook.com/carlin.eng/" class="fa fa-facebook"></a>
          <a href="https://twitter.com/carlineng" class="fa fa-twitter"></a>
          <a href="https://www.linkedin.com/in/carlineng/" class="fa fa-linkedin"></a>
        </div>
      </div>
    </section>
    <section id="about">
      <h1>About</h1>
      <p> This page is based off of the <a href='https://github.com/cadars/john-doe/'>John Doe</a> template, a
        single-file website written only in HTML and CSS, to which I've added small Javascript snippets for toy
        functionality. Source code for this website can be found at <a
          href='https://github.com/carlineng/carlineng.github.io'>Github</a>. <a href="#home">↩️</a>
      </p>
    </section>
    <section id="blog">
      <!-- BLOG -->
      <article>
        <label for="platform-shifts-malloy" class="post-title">Escaping SQL’s Legacy: Malloy and the era of AI-powered
          Analytics</label>
        <a href="?postid=platform-shifts-malloy#blog" class="selflink">[link]</a>
        <time datetime="2025-04-27">04.27.2025</time>
        <input type="checkbox" id="platform-shifts-malloy" />
        <div class="post-body">
          <p>In the 1980s, a fundamental debate emerged in computer architecture between two competing approaches: CISC
            (Complex Instruction Set Computer) and RISC (Reduced Instruction Set Computer). CISC architectures, backed
            by Intel with leaders such as Pat Gelsinger pushing x86 forward - emphasized powerful, multi-function
            instructions to optimize programmer effort. Meanwhile, RISC architectures, developed by researchers like
            John Hennessy at Stanford, prioritized modularity and speed with simpler instructions. RISC was universally
            considered a technically superior approach, but not strong enough to overcome the immense weight of legacy
            software investments that favored CISC. Enormous amounts of critical software had already been written for
            CISC architectures, and migrating it to RISC would have required costly, large-scale reimplementation - a
            tradeoff few businesses could justify. It was only with the rise of mobile computing in the 2000s - a new
            platform with different constraints - that RISC architectures like ARM finally displaced incumbents,
            following the classic pattern of disruptive innovation. With GenAl, I believe that a similar shift is
            underway in analytics. </p>
          <p> Today, SQL holds a position in the world of analytics very similar to CISC's position in computing decades
            ago: deeply entrenched, widely understood, and supported by a massive ecosystem of tools, systems, and
            talent. This weight of legacy is what has made previous efforts to replace or significantly evolve SQL so
            difficult, no matter how promising the alternative. Tens of millions of people have been trained to write
            and understand SQL, and an enormous volume of business-critical systems rely on it. Replacing SQL not only
            requires migrating software and rewriting queries - it also demands retraining entire workforces, redefining
            best practices, and rebuilding a huge stack of interconnected tools and integrations. Even if a new language
            like Malloy offers clear technical advantages, the switching cost is staggering, and the risks high. </p>
          <p> This is the trap of legacy: the best technology doesn't always win. Sometimes the most compatible and
            entrenched one does. However, platform shifts create rare windows of opportunity - moments when the weight
            of legacy temporarily weakens, and new technologies have a chance to break through and redefine the future.
          </p>
          <h3>Why SQL is not fit for GenAI-Powered Analytics</h3>
          <p> GenAl has the potential to become a primary interface for interacting with data, but it requires
            foundational technologies that are modular, composable, and semantically rich. SQL was never designed for
            this. SQL is a low-level language: every complex analysis must be constructed manually, with little inherent
            support for abstraction or reuse. Metrics are often derived from scratch, based on formulas and business
            rules that live not in the database itself, but in the minds of human experts. These recipes are rarely
            codified in ways that LLMs can easily discover or reuse — they are domain-specific, highly contextual, and
            sparsely represented in the training data available to large models. As a result, the risk of hallucination
            and semantic error when generating SQL is extremely high. </p>
          <p> The problem is structural, not stylistic. Writing semantically correct SQL is not simply a matter of
            following grammar rules; it requires correctly reassembling entire analytical concepts from first
            principles, every time. To borrow an analogy from software engineering, SQL is like Assembly language:
            precise, powerful, but fundamentally low-level. An LLM can write syntactically correct Assembly, but it
            becomes vastly more capable when operating in higher-level environments like Python or TypeScript, where
            complex abstractions like networking, storage, and Ul components are already built. Without those
            higher-level building blocks, even trivial applications would require enormous, error-prone efforts. The
            same is true in analytics. Asking LLMs to generate full analyses in SQL - without composable, reusable
            models for key metrics - is akin to asking them to vibe-code an entire web application from raw Assembly.
            It's technically possible, but the room for error grows exponentially with complexity. </p>
          <h3>Malloy was Built for this Moment</h3>
          <p> <a href="https://docs.malloydata.dev/documentation/index.html">Malloy</a>, a new semantic modeling and
            query language, was designed with the realities of the GenAl era in mind. It elevates the level of
            abstraction by treating analytic concepts - measures, dimensions, relationships, and queries - as
            first-class, modular building blocks. Instead of forcing an LLM (or a human) to re-derive complex metrics
            and joins from scratch, Malloy allows these analytical patterns to be defined once, in clear, reusable
            models. This composability dramatically reduces the cognitive burden on either man or machine, and minimizes
            the risk of errors and hallucinations when generating analysis. Just as high-level programming languages
            unlocked new capabilities for software developers, Malloy unlocks a higher-level interface for analytics,
            where GenAl systems can build on solid foundations instead of reinventing them every time. In a world where
            machines are becoming active participants in data exploration and decision-making, a modular, semantic, and
            composable query language isn't just helpful — it's essential. </p>
          <p> Even in a future where GenAl tools generate much of the initial analysis, human judgment remains
            essential. Analysts, decision-makers, and domain experts will still need to review, modify, and iterate on
            the outputs. However, SQL makes this human-in-the-loop process difficult and error-prone. SQL queries,
            whether written by a human or an LLM, are very often extremely complex and brittle, and challenging to read,
            debug, or safely modify. It's rarely obvious which parts of a long SQL statement can be edited without
            accidentally breaking joins, filters, or business logic. Small changes can have cascading, unintended
            effects. </p>
          <p> Malloy changes this dynamic fundamentally. Because Malloy introduces higher-level abstractions - modular
            models, defined relationships, reusable measures — it provides clear, semantically meaningful structures
            that are easier for humans to understand and edit. Instead of reverse-engineering the intent behind hundreds
            of lines of generated SQL, an analyst can quickly see which models, fields, and metrics are being used, and
            can make targeted, safe adjustments. The iteration loop between human and machine becomes faster, more
            reliable, and far less risky. </p>
          <p>In a GenAl-driven world, success won't just be measured by how well machines generate queries — it will be
            measured by how well humans and machines can collaborate. Malloy provides the foundation for that
            collaboration to be safe, scalable, and productive. </p>
          <h3>The Opportunity Ahead</h3>
          <p> When RISC architectures like ARM powered the rise of smartphones, it didn't just create better computers -
            it created a revolution in accessibility. Computing, once confined to those who could afford a desktop
            machine, became truly global. Billions of people could suddenly access the internet, applications, and
            information from the palms of their hands. The impact wasn't just technical; it was economic, cultural, and
            societal. </p>
          <p> Today, a similar opportunity exists in analytics. SQL has historically served as the gatekeeper to
            sophisticated data work — an essential but specialized skillset, accessible only to those with the time,
            training, and expertise to master it. This bottleneck has limited who can directly interrogate data and
            extract insights. The combination of GenAl and Malloy holds the potential to break that barrier. By making
            analytic logic modular, composable, and semantically rich, Malloy enables LLMs to build, extend, and adapt
            sophisticated analyses - and enables humans without deep SQL expertise to participate meaningfully in the
            process. </p>
          <p> This shift could massively expand the number of people who can engage with data at a sophisticated level.
            Analysts, product managers, operations teams, and even executives could move from passive consumers of
            dashboards to active participants in data-driven problem solving. Just as the smartphone era democratized
            access to computing, the GenAl era - powered by Malloy - has the potential to democratize access to deep
            analytics. </p>
          <hr class="solid">
        </div>
      </article>
      <article>
        <label for="holy-grail-data-engineering" class="post-title">Reusability in Data Engineering: Holy Grail or
          Fool's Errand?</label>
        <a href="?postid=holy-grail-data-engineering#blog" class="selflink">[link]</a>
        <time datetime="2024-11-01">11.03.2024</time>
        <input type="checkbox" id="holy-grail-data-engineering" />
        <div class="post-body">
          <p>It’s not a controversial statement to say that Data Engineering as a discipline does not have much in the
            way of reusability. Maxime Beauchemin explored this idea in his <a
              href="https://preset.io/blog/why-data-teams-keep-reinventing-the-wheel/">recent blog post</a>, and
            attributed the lack of reusability to subtle differences between organizations:</p>
          <blockquote>
            <p> “As you dig deeper, you realize that no two companies calculate their metrics in exactly the same way.
              Whether it’s the way they define engagement or measure churn, every business has its own quirks. These
              small but significant differences prevent the kind of standardization and reuse that should be possible in
              software engineering.” </p>
          </blockquote>
          <p> But in the same way that no two companies are alike, the same can be said of software products. Every
            software product is unique, and yet software engineers across the infinite variety of products can share and
            reuse code libraries for foundational tasks like database management, user authentication and cryptography.
            This reuse allows engineers to focus on building product-specific features rather than reinventing basic
            components. So why doesn’t data engineering have similar shared core libraries? I believe the problem is not
            the subtle differences between how companies define metrics, but something much more crude and overt: <b>the
              primary language of data engineering (SQL) simply does not allow for it!</b></p>
          <p> First, let’s define data engineering. It’s a term that can mean many things, but in this case, I’m going
            to look at a very SQL-centric view. Let’s assume data arrives in the warehouse via some load process –
            either CDC from a transactional DB, or from a vendor like Fivetran. In this scenario, I define data
            engineering as the process of turning these raw tables (which are often shaped in ways that are more
            suitable for an application to work with) into new tables that are easier for human consumers to use via
            dashboarding tools or hand-written SQL queries. Some might call this “analytics engineering”. </p>
          <p> One of the most popular tools for this job is <a href="https://www.getdbt.com/">dbt</a>. In dbt, users
            write SQL queries against tables in their data warehouse. These queries form a directed acyclic graph,
            sometimes called a DAG or a pipeline, where each node in the graph is a single query, and the queries are
            all executed in the appropriate order. In these pipelines, each query can be thought of as a function that
            takes one or more tables as input, and produces a single table as output. </p>
          <p>
            <code>
              𝑓(𝑡₁, 𝑡₂, …, 𝑡ₙ) ⇒ 𝑡ₒ
            </code>
          </p>
          <p>Conceptually, this is no different from how functions in standard programming languages work: take some
            input parameters, and produce some output. When writing a function in a language like TypeScript, a software
            engineer might begin by defining an interface for the function’s input – a blueprint that defines which
            parameters are allowed, and their input types. This allows the function to execute on any input data that
            conforms to the required shape. But now consider this scenario in the context of data engineering. In the
            example I described, each of the parameters, 𝑡₁ through 𝑡ₙ, is a table in the data warehouse, and the
            function, 𝑓, is a SQL query. In the first case (software engineer writing TypeScript), the function is
            defined over an abstract interface. Any piece of data that conforms to the interface can be used as input to
            the function. In the latter case (data engineer writing SQL), the function (a SQL query) must be written
            against specific tables that exist in the warehouse, and can only be executed on those tables. There is no
            way in SQL itself to define queries on an abstract interface.</p>
          <p> Let’s look at one of the simplest possible examples: deduplicating log events. A data warehouse might have
            several tables that contain logged events. Log event tables often contain duplicates due to issues that
            arise in the source systems that emit the events, and data engineers need to deduplicate rows from these
            tables in the warehouse. Usually this looks like a query that performs a <code>row_number</code> window
            function partitioned over the event’s primary key, and grabs the first row from each window. </p>
          <p>
            <code>
              <pre>
WITH ranked AS (
  SELECT
    *
    , ROW_NUMBER() OVER (
      PARTITION BY event_uuid ORDER BY unixtime ASC
    ) as row_rank
  FROM log_table
)
SELECT *, except(row_rank) FROM ranked
WHERE row_rank = 1
              </pre>
            </code>
          </p>
          <p> I should be able to specify this query by calling a simple “deduplicate” function, and providing it with
            three parameters: tablename, partition columns, and ordering. Instead, for every table, I need to rewrite
            the full query, despite the fact that the majority of the logic is not unique. </p>
          <p> Many software and data engineers have noticed this problem and end up solving this problem by writing code
            that writes SQL. Often this looks like writing a Python function that wraps a SQL string template and
            produces the desired query based on the function’s input parameters. Unfortunately, this pushes us into a
            new realm of complexity. The SQL now lives as a string in a Python program which is painful to write and
            maintain, since typical IDE functionality like type-checking and autocomplete won’t work inside the string.
            Additionally, syntax errors aren’t caught until runtime, making debugging more difficult. </p>
          <p> From the query runner’s perspective, the logic is now split between two different languages. Some of the
            business logic lives in SQL, and some lives in Python. Directly executing the query is no longer an option –
            first it must be run through a compilation process, and in the event of problems, there are now two separate
            systems that must be debugged: the query generation logic, and the business logic of the query itself. This
            added complexity is significant, and in practice, means that writing these kinds of abstracted data
            pipelines is not accessible to the typical data engineer. </p>
          <p> Taking a step back, this is rather absurd. Imagine if this same idea were true of software engineering. It
            would be as if in order to write reusable Javascript, an engineer would need to wrap it inside of a string
            template in a Python program. It would also mean that only the most talented software engineers could
            actually write reusable components. I hope that one day, data engineers will have a way to write “natively”
            reusable code, i.e., plain queries that can be run on abstract interfaces, without requiring the use of
            SQL-generating templates. This could happen if SQL adopts a way to run abstract queries (e.g., <a
              href="https://duckdb.org/2024/03/01/sql-gymnastics.html">DuckDB’s Macros</a>), or some other language or
            framework offers this capability and becomes mainstream. But I firmly believe that until this happens, the
            discipline of Data Engineering will not meaningfully advance. </p>
          <hr class="solid">
        </div>
      </article>
      <article>
        <label for="data-modeling-divide" class="post-title">The Data Modeling Divide</label>
        <a href="?postid=data-modeling-divide#blog" class="selflink">[link]</a>
        <time datetime="2023-06-03">06.03.2023</time>
        <input type="checkbox" id="data-modeling-divide" />
        <div class="post-body">
          <p> There is no shortage of opinions on the Internet about how best to organize data: <a
              href="https://en.wikipedia.org/wiki/Star_schema">star schemas</a>, <a
              href="https://www.fivetran.com/blog/star-schema-vs-obt">OBTs</a> (“One Big Table”), <a
              href="https://en.wikipedia.org/wiki/Slowly_changing_dimension">slowly changing dimensions</a>, and <a
              href="https://www.activityschema.com/">Activity Schema</a>, just to name a few. However, despite the
            existence of all these “best practices”, most data practitioners I know still describe their data warehouse
            as a complete mess. The most innocent question of “why are these two numbers different?” can send an analyst
            or data engineer down a deep rabbit hole for hours or days. With so much discourse about data modeling, why
            are we still in such a fragile situation? In this post, I argue that the root cause is the unnatural
            division of “data modeling” into two separate workflows: transformation and semantic modeling. </p>
          <p> It's worth taking a second to discuss what data modeling actually is. Here’s my best attempt at a
            definition: <em>“The process of turning raw data into useful business insights.”</em> While this definition
            is broad and perhaps a bit too generalized, I’ve noticed that much of the online discourse seems to focus on
            specific tools or techniques. Some people talk about data modeling in terms of the artifacts it produces,
            for example, the aforementioned star schemas and OBTs. Others talk about data modeling as a way to map
            already-existing database objects to business metrics – what tables and columns feed into the revenue
            calculation? What segments can we cut revenue by? These two viewpoints correspond to two related, but mostly
            separate camps: data transformation and semantic modeling. Data transformation uses tools like <a
              href="https://www.getdbt.com/">dbt</a> to define pipelines of SQL queries. These queries take raw data
            from business systems as input, and produce “clean” tables and views for downstream consumers to use. In the
            other camp, semantic modeling uses tools like <a
              href="https://cloud.google.com/looker/docs/what-is-lookml">LookML</a> to specify joins between tables and
            define how measures are calculated from those tables. </p>
          <p> A very common workflow is to use dbt to create pipelines that produce clean output tables, then use those
            clean output tables to generate metrics for reporting. These metrics can be defined either in a semantic
            modeling layer like LookML or directly in a visualization tool like Tableau. In either case, it’s rare for
            the transformation layer to own 100% of the metric logic. There are usually too many dimensional cuts and
            edge cases to enumerate explicitly in the pipeline. </p>
          <p> At first glance this might seem like a reasonable approach, but let’s look at what happens when the
            inevitable question arises: “why is Number X different from Number Y?” Numbers X and Y probably come from
            two different dashboards, and if you’re lucky enough to use a tool like Looker, you can easily inspect the
            semantic layer to find the measure definitions that generated them. This is great, until you hit a wall:
            measures X and Y are built on tables that are “clean output tables” from a dbt job. Looker has no idea where
            those tables came from or how they were calculated; it just sees the materialized tables in the database. To
            understand the logic behind the columns where measures X and Y come from, you need to jump into dbt and
            trace the pipeline that generated their input tables. There is no longer a single place to look up how a
            metric is defined – the business logic for numbers X and Y is split across two tools that use different
            languages (SQL and LookML), and might not even be owned by the same team (BI team owns the BI tool, data
            engineering team owns the transformation tool). If this is the case, good luck in your quest. Crossing team
            boundaries can mean adding hours or even days to a task. </p>
          <p> Transformation and semantic modeling are not fundamentally different tasks – they are two parts of the
            same process: turning raw data into business insight. And yet, our ecosystem forces them to happen in
            separate places, oftentimes with tools operated by separate users. </p>
          <p> This problem has been recognized by many in the industry, but existing solutions all fall short. Looker’s
            Persistent Derived Table functionality allows users to construct data pipelines to materialize their models,
            but practically speaking, the resulting tables are really only usable by Looker, and are difficult to query
            directly from the data warehouse. dbtLabs’ acquisition of Transform is a sign that they have an intuitive
            understanding of the issue, but I’m not confident that their current direction will improve the situation.
            While full details of the integration haven’t been published, early indications are that it will look very
            similar to the workflow described above with all its flaws: SQL+dbt for transformations, and a YAML-based
            configuration language for semantic modeling. Still two separate languages and two separate toolsets for
            what is essentially the same problem. </p>
          <p> Is there any solution to all of this? I can’t say for sure, but in my dream world, transformations don’t
            create tables or views, they create Models. Models are simply tables with additional metadata attached:
            joins, dimensions, measures, and more. Even further, every intermediate node in a pipeline DAG should also
            be a Model with the same types of metadata attached. Every Model should be queryable by BI tools,
            operational applications, or human analysts using a query language that understands the Model metadata. In
            such a world, the artificial divide between transformation and semantic modeling no longer exists, two tools
            get integrated into one, and the question of “why are these numbers different” becomes a lot easier to
            answer. </p>
          <br />
          <p><em>Discuss this post on <a href="https://news.ycombinator.com/item?id=36177068">Hacker News</a></em></p>
          <hr class="solid">
        </div>
      </article>
      <article>
        <label for="semantic-layer" class="post-title">What Happened to the Semantic Layer?</label>
        <a href="?postid=semantic-layer#blog" class="selflink">[link]</a>
        <time datetime="2023-04-26">04.26.2023</time>
        <input type="checkbox" id="semantic-layer" />
        <div class="post-body">
          <p>
            <i>Disclaimer: this blog post relates directly to my work as a Product Manager at Google; however, all
              opinions expressed here are my own and do not reflect those of Google. I also can't comment on any future
              Google product plans.</i>
          </p>
          <p> Semantic layers, also known as metrics layers or “headless BI”, have become a popular topic in recent
            years. The idea of a semantic layer is straightforward but powerful – put simply, it’s a system that takes
            tables in a SQL database and provides business context around them. A semantic layer can help answer
            surprisingly complex questions like “what is the exact definition of revenue?” Such an innocent question
            might require a lot of background knowledge to answer: what tables contribute to revenue, and how are they
            joined together? Which columns are the right ones to use, and which rows must be filtered out? It’s the job
            of a semantic layer to store all of this knowledge, so anyone can ask the question “what is revenue”, and
            not need to know all the arcane knowledge about the underlying dataset in order to answer it. </p>
          <p> This is an old idea, present in many enterprise ecosystems (e.g., Microsoft, SAP), but in modern cloud
            data warehousing environments, semantic layers are always tightly coupled to a visualization layer. The BI
            tool <a href="https://www.looker.com/">Looker</a>, with its LookML configuration language is a prime
            example. Much of the recent buzz around this topic relates to decoupling the semantic layer from the
            visualization layer, which would allow many other types of applications to leverage centrally defined
            metrics. Example use cases include customer segmentation for marketing campaigns and lead-scoring in a CRM.
            Benn Stancil called the semantic layer <a href="https://benn.substack.com/p/metrics-layer"> “the missing
              piece of the modern data stack”</a>. In a post titled <a
              href="https://basecase.vc/blog/headless-bi">Headless Business Intelligence</a>, Ankur Goyal and Alana
            Anderson predicted that it would be the foundation of “the next generation of software companies“. Airbnb
            made a big splash with a blog post describing its internal metrics layer, <a
              href="https://medium.com/airbnb-engineering/how-airbnb-achieved-metric-consistency-at-scale-f23cc53dea70">Minerva</a>.
            All these articles generated a fair amount of excitement in the online data community, but despite all that,
            we have yet to see this supposedly transformative idea change our profession. Several startups gave it a
            shot (<a href="https://transform.co/">Transform</a>, <a
              href="https://www.businesswire.com/news/home/20211109005713/en/Supergrain-Raises-6.8M-from-Benchmark-to-Build-Headless-Business-Intelligence-Platform">Supergrain</a>,
            <a href="https://cube.dev/">Cube</a>, and more), but haven’t seen much traction amongst analysts. Transform
            was recently acquired by dbt Labs for an undisclosed amount, Supergrain has pivoted, and Cube explicitly
            targets developers rather than analysts. So why has a concept that seemed to resonate so deeply with so many
            in the data community failed to take off? </p>
          <h3>Querying a Semantic Layer</h3>
          <p> To begin, I’ll defer again to Benn Stancil, co-founder and CTO of Mode Analytics, when he asks <a
              href="https://benn.substack.com/p/minerva-metrics-layer">“Is Minerva the answer?”</a> </p>
          <blockquote>
            <p>My biggest gripe with Minerva is how you “query” it. As best I can tell, metrics are extracted from
              Minerva via API; [...] it looks like the API lets people request metrics over date ranges, with filters,
              and grouped by different dimensions. </p>
            <p> [...] API-based solutions don’t work for analysts working directly in SQL. Though I don’t know how
              analysts work at Airbnb, in the broader market, most analysts’ work starts in SQL; for many, it also ends
              there. Unfortunately, Minerva appears to be inaccessible to these workflows. Analysts, when inevitably
              asked to explain why some KPI is yo-yoing across a Minerva-backed dashboard, will eventually have to
              recreate that metric, in SQL, on raw data in their warehouse. </p>
          </blockquote>
          <p> Benn hits it right on the nose. Querying these models takes an analyst out of their most familiar tool
            (SQL) and forces them to use an API, shunting them into tools like Python or R to carry their analysis
            further. If they hit a snag (e.g., numbers don't match, or things look wonky), they're forced back into the
            world of raw SQL without any of the benefits of the metrics layer, and stuck with the unpleasant task of
            resolving differences between hand-written and machine-generated SQL. But even assuming the happy path, once
            in Python-land the analyst is hamstrung in the following ways:
          <ul>
            <li> Analytical freedom is severely limited. Since data from the metrics API is already aggregated, there’s
              no way to drill into specific records, or create on-the-fly dimensions and measures to slice the data in a
              way that might reveal new insight. You can request other dimensions from the metrics layer, but what if
              the dimension you’re interested in doesn’t exist yet? </li>
            <li> The analyst is now constrained to whatever environment is hosting their Python runtime, whether that’s
              a local laptop or a cloud-hosted notebook. Operations that require more memory or compute aren’t possible.
            </li>
          </ul> Clients of these metrics layers are isolated from the data warehouse, and only able to communicate with
          the warehouse via a very restrictive API. This might be sufficient for a subset of an analyst’s work such as
          building executive reports and dashboards, but it falls flat when the analyst needs to do critical work like
          debugging misbehaving metrics, or more creative data work such as exploratory deep-dives. </p>
          <h3>Model Development</h3>
          <p> We’ve looked at the metrics layer from the perspective of an analyst, but what about a data modeler?
            Suppose you want to define a new metric or dimension… what does the workflow look like? I imagine something
            like the following:
          <ol>
            <li>Write ad-hoc SQL queries against the data warehouse to develop the metric</li>
            <li>Once the SQL logic has been validated, edit a YAML configuration file to encode this logic in the
              semantic layer </li>
            <li> Push the new configuration to source control, potentially get it code reviewed </li>
            <li> Redeploy the semantic layer service, or trigger data pipelines to calculate the new metric </li>
            <li> Get a coffee </li>
            <li> Fire an API call to the semantic layer to get your metric, and confirm that it’s actually what you want
            </li>
          </ol> Steps 2-5 have the <a
            href="https://medium.com/airbnb-engineering/airbnb-metric-computation-with-minerva-part-2-9afe6695b486#:~:text=Staging%20environment%20where%2C-,within%20a%20few%20hours%2C,-the%20entire%20history">potential
            to take hours</a>, and step 6 takes the analyst away from their most familiar language. If there’s a bug in
          the metric logic, or something else in the process goes wrong, iterating is painfully slow and requires
          context switching between SQL, YAML, and API. In summary, in order to solve the problem of consistent business
          logic, semantic layers like Minerva take an analyst’s primary tool (SQL) and break it out into three parts
          that don’t play well together: SQL for exploration, a modeling language for definitions (typically YAML), and
          an API to interact with the data model. </p>
          <h3>Enter Malloy</h3>
          <p> I recently joined a team at Google trying to solve these issues. We’re building a new open-source language
            that offers a better way, and it’s called <a href="https://malloydata.github.io/documentation/">Malloy</a>.
            Malloy unifies the semantic layer and the query language. Instead of writing exploratory SQL, developing
            YAML config, then making API calls, you simply write and execute Malloy. Malloy compiles to SQL, which runs
            directly on the data warehouse. <b> This gives you the superpowers of a semantic layer, without sacrificing
              the freedom to explore, manipulate, and drill into the full unaggregated dataset. </b> </p>
          <p> To see an example that illustrates the point, check out this <a
              href="https://github.dev/malloydata/quickstart/blob/5c6c1ad02c5e799cf17c40f04f3bc5156782ecc7/is_early.malloynb">interactive
              Malloy notebook</a>. It runs directly in your browser in Github's browser-based VSCode environment,
            github.dev. Once your VSCode environment has loaded in your browser, install the Malloy extension, and
            navigate back to the <a
              href="https://github.dev/malloydata/quickstart/blob/5c6c1ad02c5e799cf17c40f04f3bc5156782ecc7/is_early.malloynb">notebook
              file</a>. The notebook demonstrates an example of building up a data model, using that model to explore
            and drill down into the raw data, and using insights from that exploration to iterate further on the data
            model. This sort of workflow is only possible when the semantic layer and the query language are the same,
            unified experience.</p>
          <p> At first glance, it may seem like an impossible task to convince people to learn a new query language.
            After all, SQL has been around for over 40 years, and it’s “good enough”, right? Wrong. The excitement
            around metric layers struck a nerve because SQL is clearly NOT good enough. However, most iterations of
            metrics layers up until this point have provided a solution with too grave a cost. They completely fragment
            the analyst workflow and ask users to learn not one, but TWO additional languages: the metric configuration
            language and the metric API (an API is not so different from a small language, after all). With all that in
            mind, it’s not surprising that the metrics layer hasn’t taken off. </p>
          <p> This is why I’m so excited about Malloy. It fulfills the promise of the metrics layer at a fraction of the
            cost. Plus, if we’re going to ask users to learn a new language, we can seize the opportunity to <a
              href="https://medium.com/@michaeltoy/designing-malloy-0-introduction-88b8809d75d0">design it
              thoughtfully</a>, incorporating everything we’ve learned from the past 40 years of working with data.
            After all, language is a tool for thought, and improving the syntax of a language has deep implications for
            what we can imagine and what we can express. </p>
          <p> If you’d like to learn more about Malloy, check out the <a
              href="https://github.com/malloydata/quickstart">Quickstart repository</a> and our <a
              href="https://malloydata.github.io/documentation/index.html">documentation</a>. We also have a <a
              href="https://join.slack.com/t/malloy-community/shared_invite/zt-1t32mufpy-THwP1o1ADJVkd3o2L2zaZw">community
              Slack channel</a>, where you are welcome to post any questions or feedback! </p>
          <hr class="solid">
        </div>
      </article>
      <article>
        <label for="malloy-tpcds" class="post-title">Exploring the TPC-DS Benchmark Queries with Malloy</label>
        <a href="?postid=malloy-tpcds#blog" class="selflink">[link]</a>
        <time datetime="2023-02-24">02.24.2023</time>
        <input type="checkbox" id="malloy-tpcds" />
        <div class="post-body">
          <p> I’ve been writing a lot recently about <a href="https://github.com/malloydata/malloy">Malloy</a>, an
            experimental analytical query language built by members of Looker’s founding team. Upon reading the overview
            materials and documentation, it sounded like exactly what I was hoping for, but in order to develop a more
            informed opinion, I needed to get some experience with actually writing the language. Hence, I decided to
            embark on a project to translate each of the 99 TPC-DS benchmark SQL queries to Malloy. This post will give
            an overview of the TPC-DS dataset, the queries, and the opinions I've formed about Malloy along the way. For
            a more introductory overview of Malloy, check out my <a href="?postid=malloy-intro#blog">prior blog
              post</a>, as well as the <a href="https://malloydata.github.io/documentation/">Malloy documentation</a>.
          </p>
          <p> I posted the translations of all 99 queries to <a
              href="https://github.dev/carlineng/malloy-tpcds/tree/remote_files">this Github repository</a>. If you'd
            like to try any of these out for yourself, it's trivial from within the github.dev web editor. Simply go to
            "Extensions" in the lefthand sidebar of the editor and install the Malloy extension. The Malloy queries will
            then be runnable directly in your browser by clicking on the "Run" button above any query. Give it a shot
            with <a
              href="https://github.dev/carlineng/malloy-tpcds/blob/82650a9e105e95b58a14aee1dcfabce3912d2858/malloy_queries/01.malloy#L24">this
              query!</a></p>
          <h2>A Brief Overview of the TPC-DS Benchmark</h2>
          <p> TPC-DS is a well-known standard for benchmarking the performance of OLAP database systems. From a paper
            entitled <a href="https://www.tpc.org/tpcds/presentations/the_making_of_tpcds.pdf">“The Making of
              TPC-DS”</a>:
          <blockquote> “TPC-DS models the decision support functions of a retail product supplier. The supporting schema
            contains vital business information such as customer, order, and product data. The imaginary retail company
            sells goods through the three distribution channels, store, catalog and Internet (web).” </blockquote> The
          benchmark contains 99 SQL queries of varying complexity against a star-schema data model containing 7 fact
          tables and 17 dimension tables. This sample schema diagram describing a portion of the data model has been
          taken from the paper above: </p>
          <a href="#img-tpcds-schema"><img loading="lazy" alt="" id="tpcds-schema" src="./img/tpcds_schema.png"
              style="max-width: 50%" /></a>
          <p> The queries test a wide range of OLAP query functionality as specified by the SQL99 standard, including
            common table expressions, ranking window functions, and joins of all types. Because of this, it is
            considered by many to be a “completeness” test for a query engine’s capabilities. For example, as of
            February 2023, <a
              href="https://github.com/Altinity/tpc-ds/tree/bc9725416f96bccc43ad377462768343fe0b3703">Altinity’s
              benchmark repository</a> reports that Clickhouse currently only passes 75% of the queries. Based on this
            reputation, I thought the query suite would make a great case study in testing the capabilities of Malloy,
            as well as giving me the opportunity to develop a firsthand perspective on the language. </p>
          <h2>Getting Acquainted with the TPC-DS Queries</h2>
          <p> Though I had previously read a lot of TPC-DS benchmark reports and looked superficially at some of the
            queries, I didn’t actually know the queries or the schema all that well. It didn’t take long for me to
            realize that the benchmark is far from a picture-perfect dataset with cleanly written SQL. Some of the
            queries contain bugs, the data model is not always applied consistently, and the SQL style employed by the
            authors is somewhat unconventional. </p>
          <h3>Bugs in Queries</h3>
          <p> Several of the queries contain exploding joins due to poorly specified join conditions between tables. In
            some queries, like <a
              href="https://github.com/duckdb/duckdb/blob/cdd23d5e03b2a82b7e611c40a543303722071465/extension/tpcds/dsdgen/queries/02.sql">Query
              2</a>, the results contain duplicate rows that provide no meaningful information. In another example, <a
              href="https://github.com/duckdb/duckdb/blob/cdd23d5e03b2a82b7e611c40a543303722071465/extension/tpcds/dsdgen/queries/54.sql#L26-L33">Query
              54</a>, one of the joins (<code>store</code> and <code>customer_address</code>) causes a fan-out followed
            by a sum on duplicate rows, ultimately generating an incorrect result. In <a
              href="https://github.com/duckdb/duckdb/blob/cdd23d5e03b2a82b7e611c40a543303722071465/extension/tpcds/dsdgen/queries/77.sql#L76-L82">Query
              77</a>, one of the joins is simply missing any join conditions at all, which results in an aggregation on
            top of a cartesian join, again producing incorrect results.</p>
          <h3>Inconsistencies in Data Model Between Queries</h3>
          <p>In <a
              href="https://github.com/duckdb/duckdb/blob/cdd23d5e03b2a82b7e611c40a543303722071465/extension/tpcds/dsdgen/queries/49.sql#L26-L27">Query
              49</a>, the join condition between the <code>store_sales</code> and <code>store_returns</code> tables uses
            two columns, <code>item_sk</code> and <code>ticket_number</code>. In <a
              href="https://github.com/duckdb/duckdb/blob/cdd23d5e03b2a82b7e611c40a543303722071465/extension/tpcds/dsdgen/queries/17.sql#L28-L30">Query
              17</a>, the join between the tables includes a third column, <code>customer_sk</code>. It’s possible that
            there’s a legitimate reason why one query should use a different set of join keys than another, but this is
            exactly the sort of arcane knowledge that is likely to cause bugs when working in a complex data warehouse.
            When encountering these tables for the first time, how does an analyst know which join keys are appropriate
            for which situation?</p>
          <h3>Other Miscellaneous Issues</h3>
          <p><b>Join Specification</b>: The majority of joins are specified in the <code>WHERE</code> clause instead of
            using the <code>JOIN</code> keyword. I dislike this syntax because I think it makes queries harder to
            interpret. I prefer to structure my SQL queries such that each part of the query is associated with a
            particular “action”, e.g., joining, grouping, aggregating, or filtering. Using the <code>WHERE</code> clause
            to specify joins means that filtering and joining operations can be interleaved in the query text, and that
            the join condition is oftentimes far away from the names of the joining tables (see <a
              href="https://github.com/duckdb/duckdb/blob/cdd23d5e03b2a82b7e611c40a543303722071465/extension/tpcds/dsdgen/queries/25.sql#L9-L25">Query
              25</a>). This makes understanding the query just a bit more difficult since filters and joins can be hard
            to distinguish from each other. Understanding how the join occurs now requires jumping back and forth
            between different parts of the query. </p>
          <p><b>Correlated Subqueries</b>: A large number of the queries use <a
              href="https://en.wikipedia.org/wiki/Correlated_subquery">correlated subqueries</a> (e.g., <a
              href="https://github.com/duckdb/duckdb/blob/cdd23d5e03b2a82b7e611c40a543303722071465/extension/tpcds/dsdgen/queries/01.sql#L15-L18">Query
              1</a> and <a
              href="https://github.com/duckdb/duckdb/blob/cdd23d5e03b2a82b7e611c40a543303722071465/extension/tpcds/dsdgen/queries/41.sql#L5-L7">Query
              41</a>). I’ve always found correlated subqueries hard to understand, since the join syntax requires
            joining a column in the subquery to an “outside” column, which oftentimes lacks immediate context to
            indicate where it comes from. This forces the reader to backtrack (oftentimes quite far) to earlier in the
            query to figure out what’s going on. </p>
          <p><b>GROUP BY ROLLUP</b>: A lot of the queries use the <code>GROUP BY ROLLUP</code> construct (e.g., <a
              href="https://github.com/duckdb/duckdb/blob/cdd23d5e03b2a82b7e611c40a543303722071465/extension/tpcds/dsdgen/queries/18.sql#L41-L44">Query
              18</a>). While most modern data warehouses support this functionality, I’ve very rarely seen it used in
            practice because the output is so clumsy and difficult to work with. For more on this topic, check out my <a
              href="https://carlineng.com/?postid=sql-bad-syntax#blog">prior blog post</a>. </p>
          <p>To be fair, most of the above issues are quite likely to appear in a real-world data warehouse. It’s not
            unreasonable for a benchmark suite to contain problems like these, but I doubt they were intentional. I
            couldn’t find any mention of intentionally including mistakes when I browsed through the TPC-DS reference
            materials. </p>
          <h2>Translating to Malloy</h2>
          <p> The Malloy language currently supports querying Postgres, DuckDB, and BigQuery. I opted to use DuckDB
            because of the ease of setup — it’s an in-process database that doesn’t require running a separate server or
            a connection to the Internet, and it operates directly on local files.</p>
          <p> Once I had the database up and running, writing the actual queries was pretty straightforward. The
            examples in the Malloy documentation provide a great template for getting started. I created a <a
              href="https://github.dev/carlineng/malloy-tpcds/blob/82650a9e105e95b58a14aee1dcfabce3912d2858/malloy_queries/tpcds.malloy">“model
              file”</a> to develop the data model with predefined joins, commonly used aggregations, and other reusable
            constructs. Each individual query file imports this model file and uses one of the Sources defined there as
            a starting point for querying. Of the 99 queries, I was able to write logical translations of all but one:
            <a
              href="https://github.dev/carlineng/malloy-tpcds/blob/82650a9e105e95b58a14aee1dcfabce3912d2858/malloy_queries/51.malloy">Query
              51</a>, which requires cumulative window functions. Of the 98 translated queries, 96 returned successfully
            with correct answers. One query failed due to a bug where the Malloy-to-SQL compilation process generated
            invalid SQL (<a
              href="https://github.dev/carlineng/malloy-tpcds/blob/82650a9e105e95b58a14aee1dcfabce3912d2858/malloy_queries/14.malloy">Query
              14</a>), and the other was due to DuckDB crashing on the compiled SQL (<a
              href="https://github.dev/carlineng/malloy-tpcds/blob/82650a9e105e95b58a14aee1dcfabce3912d2858/malloy_queries/64.malloy">Query
              64</a>). Given that the TPC-DS queries are considered a “completeness” benchmark for logical capabilities,
            I was rather impressed with Malloy’s coverage. 97% (96 out of 99) is solid, especially for a project so
            young. </p>
          <p> In total, the Malloy queries consisted of 4,727 lines, 13,788 words, 129,880 characters. The SQL queries
            from the DuckDB repository are 5,524 lines, 14,961 words, and 184,424 characters. In lines of code, the
            Malloy is about 15% more compact, and over 30% more compact when looking at character count. In addition to
            being more concise, I’ve found the Malloy queries to be quite a bit more readable. <a
              href="https://github.com/duckdb/duckdb/blob/cdd23d5e03b2a82b7e611c40a543303722071465/extension/tpcds/dsdgen/queries/04.sql">Query
              4</a> is an extreme example -- the SQL query comes in at a whopping 119 lines of code. Parsing a single
            119 line SQL query is a task that requires a good deal of mental focus. The Malloy equivalent is only 37
            lines:</p>
          <div id="tpcds4" class="side-by-side">
            <div class="sbs-child">
              <script src="./gists/tpcds4sql.js"></script>
            </div>
            <div class="sbs-child">
              <script src="./gists/tpcds4malloy.js"></script>
            </div>
          </div>
          <p>It’s not a completely fair comparison, since the Malloy query leverages pre-defined logic imported from the
            model file, but even with that in mind, I find the difference between the two implementations quite
            informative. </p>
          <p> Overall, writing Malloy queries is a lovely experience. Coming from SQL, the syntactic structure of the
            language feels familiar, but much more refined — as if the clunky, jagged edges of the language have been
            reshaped and sanded down, leaving something with a much smaller footprint, and a much more pleasing
            ergonomics. Malloy’s syntax seems smaller and has a more explicit structure. Unlike SQL, it doesn’t make the
            hopeless attempt to mimic human language, and seems instead optimized for someone trying to manipulate data
            using a well-understood set of actions — grouping, joining, aggregating, and filtering. </p>
          <p>Interestingly, the explicit structure imposed by the language meant that I settled in very quickly on
            conventions for formatting my Malloy queries. When writing a query, there are far fewer formatting decision
            points than in the equivalent SQL query. While this may seem trivial at first, it’s a subject that has
            spawned a <a href="https://github.com/un-ts/prettier/tree/master/packages/sql">number</a> of <a
              href="https://sqlfum.pt/">linting</a> <a href="https://github.com/sqlfluff/sqlfluff">packages</a>, and
            more than a few <a href="https://benn.substack.com/p/the-case-against-sql-formatting">essays by analytics
              influencers</a>. Reducing the time spent picking apart query formatting means more mental energy can be
            spent on the logic itself. This is a much improved experience for writing queries, and even better for
            reading them. There are countless examples of this in the TPC-DS queries, but <a
              href="?postid=malloy-tpcds&scrollto=tpcds4#blog">Query 4</a> above is a prime example. The SQL query
            contains filters with nested case statements, <code>UNION ALL</code> statements, and common table
            expressions. With each of these elements, formatting is applied inconsistently, and it took me a good deal
            of time to piece together the logic. The <a href="?postid=malloy-tpcds&scrollto=tpcds4#blog">Malloy
              version</a> is clearly much more concise and far less chaotic.</p>
          <p> Drilling into a few more details, here are some additional points where I found Malloy to be a clear
            improvement over SQL:</p>
          <p><b>Joins:</b> the ability to leverage a pre-defined data model and not have to remember and write out join
            keys for every single join was really great. </p>
          <p><b>Nested Aggregations:</b> Nested aggregations take the place of <code>GROUP BY ROLLUP</code>, and do a
            far better job (see <a
              href="https://github.dev/carlineng/malloy-tpcds/blob/82650a9e105e95b58a14aee1dcfabce3912d2858/malloy_queries/22.malloy">Query
              22</a>). As mentioned previously, see my <a href="?postid=sql-bad-syntax#blog">previous blog post</a> for
            a deeper dive on this topic.</p>
          <p><b>Filtered Aggregations:</b> Filtered aggregates are so much nicer to use and interpret than using CASE
            statements inside aggregate functions (see <a
              href="https://github.dev/carlineng/malloy-tpcds/blob/82650a9e105e95b58a14aee1dcfabce3912d2858/malloy_queries/21.malloy">Query
              21</a>). Some SQL databases have implemented this functionality, and I think it’s a no-brainer to make it
            a standard part of the language. </p>
          <div id="tpcds21" class="side-by-side">
            <div class="sbs-child">
              <script src="./gists/tpcds21sql.js"></script>
            </div>
            <div class="sbs-child">
              <script src="./gists/tpcds21malloy.js"></script>
            </div>
          </div>
          <h2>Potential Areas for Improvement</h2>
          <p> While the experience was overwhelmingly positive, there were still a few spots where I fell into traps, or
            where the language didn’t feel quite optimized for the task. </p>
          <p><b>LEFT JOIN vs INNER JOIN:</b> All joins in Malloy are <code>LEFT JOIN</code>s by default. To implement
            <code>INNER JOIN</code> semantics, a query needs to specify a filter on
            <code>right_table.column = null</code>, which I would often forget to do. It’s trivial to add these filters,
            but in SQL queries, the type of join is a critical piece of information that is oftentimes the first thing
            an analyst will look at to determine the nature of a query. Having the <code>INNER JOIN</code> indicator
            placed in a <code>`where`</code> clause might not be obvious to someone quickly glancing at a query, since
            <code>`where`</code> clauses are typically reserved for filtering down datasets based on properties. From a
            UX perspective, since joins in Malloy are usually defined in a Source and not necessarily the query itself,
            it’s not clear to me how to make this more obvious, and it might just be one of those things that takes some
            getting used to with a new tool. </p>
          <p>
            <b>SEMI-JOIN:</b> The semi-join operation in SQL takes the form of
            <code>WHERE customer_id IN (SELECT … FROM …)</code>, as seen in <a
              href="https://github.com/duckdb/duckdb/blob/cdd23d5e03b2a82b7e611c40a543303722071465/extension/tpcds/dsdgen/queries/33.sql#L8-L11">Query
              33</a>. The Malloy implementation of this construct requires an extension to a Source with an ad-hoc join
            (lines 2-7 in the query below), and an additional <code>NULL</code> filter in the <code>where</code> block
            (line 16):
          </p>
          <script src="./gists/tpcds33malloy.js"></script>
          <p>Logically, this produces the correct output, but I don’t love the syntax since it combines two operations
            that could each have separate semantic meanings, and which are far apart in the query. This makes it harder
            to interpret exactly what the query is doing. By contrast, the SQL syntax of
            <code>column IN (sub-select)</code> is neatly contained in a single location, and unambiguous in its intent.
          </p>
          <h2>Conclusion</h2>
          <p>Throughout this entire process, I learned a lot about both Malloy and the TPC-DS benchmark. Initially,
            writing 99 queries in a new query language felt like a huge task, but it wasn’t long before I hit my stride.
            The Malloy language made the task easy, and dare I say, fun. Getting up close with the benchmark queries was
            hugely educational as well. The dataset and queries are impressive in many aspects, but ultimately, I don’t
            think they’re very representative of actual workloads. Even though it has earned a reputation as a coverage
            test for a query engine’s logical completeness, there are still many real-world query patterns and scenarios
            that are not represented, such as <a href="https://mode.com/blog/finding-user-sessions-sql/">session
              analysis</a>, or the <a
              href="https://discourse.getdbt.com/t/finding-active-days-for-a-subscription-user-account-date-spining/265">construction
              of “date-spined” reporting tables</a>. As a result, I’ve come away from the exercise more excited about
            Malloy than ever, but acutely aware that there is still much to be tested. Will Malloy ever truly replace
            SQL as the lingua franca of the analytics world? If it does, it will take a long time to get there, but it’s
            a future I’d like to live in. </p>
          <hr class="solid">
        </div>
      </article>
      <article>
        <label for="sql-renaissance" class="post-title">SQL, Malloy, and the Art of the Renaissance</label>
        <a href="?postid=sql-renaissance#blog" class="selflink">[link]</a>
        <time datetime="2023-02-05">02.05.2023</time>
        <input type="checkbox" id="sql-renaissance" />
        <div class="post-body">
          <p> This post is the third in a series comparing SQL with a promising new query language called <a
              href="https://www.malloydata.dev/">Malloy</a>. I think Malloy represents a leap forward in how we work
            with data, and in the following paragraphs, I'll attempt to draw a connection to another time in history of
            rapid technological advancement: the Renaissance in Western Europe.</p>
          <p>Lloyd Tabb, former Looker CTO and creator of Malloy, recently wrote a post entitled <a
              href="https://lloydtabb.substack.com/p/data-is-rectangular-and-other-limiting">Data is Rectangular and
              Other Limiting Misconceptions</a>. If you haven’t read it yet, go check it out now — it very neatly lays
            out a fundamental flaw in SQL’s perspective of data, and describes how the Malloy language does better. In
            particular, SQL resultsets can only naturally represent data in two dimensions: rows and columns. However,
            data in the real world is NOT two-dimensional. This mismatch results in awkwardness in both writing SQL
            queries and interpreting SQL results. As I was reading Lloyd's post, it sparked a memory of a recent trip I
            took to the Uffizi art gallery in Florence, Italy. </p>
          <p> The Uffizi is the former administrative office of the powerful Medici family that ruled Florence during
            the 15th century. It currently houses one of the world’s largest collections of Gothic and Renaissance art,
            spanning from the 12th to the 16th century in Western Europe. I personally have minimal knowledge of art and
            art history, but the tour we took highlighted some fascinating aspects of technological advancement during
            this time. </p>
          <h3>Gothic Art</h3>
          <p> Gothic paintings such as this <a
              href="https://www.uffizi.it/en/artworks/virgin-and-child-enthroned-and-prophets-santa-trinita-maesta">
              depiction of the of the Virgin Mary</a>, attributed to Florentine painter Cimabue circa 1290-1300, were
            flat scenes with close-up figures in the foreground. The notion of perspective had not yet been developed,
            and rather than attempt to create images with depth, many paintings filled the background with gold leaf,
            which served as a representation of heaven.</p>
          <a href="#img-cimabue"><img loading="lazy" alt="" id="renaissance-cimabue" src="./img/cimabue-mary.jpeg"
              style="max-width: 100%; height: 400px" /></a>
          <h3>Transitioning to the Renaissance</h3>
          <p>The transition from the Gothic to the Renaissance period was marked, among other things, by a growing
            understanding of perspective: the ability to accurately represent three-dimensional scenes on a flat canvas.
            <a href="https://www.uffizi.it/en/artworks/battle-of-san-romano">The Battle of San Romano</a> by Paolo
            Uccello circa 1435-1440 is a great example from this transition period. A fierce battle rages in the
            foreground, while scenes from life in the Italian countryside play out in the background. Uccello clearly
            understood that far away objects in the background must be smaller than those in the foreground, but just
            how much smaller hadn’t been fully worked out yet. Look at the hunting scene in the background — the hunters
            and hares would be massively tall if brought to the foreground. </p>
          <a href="#img-uccello"><img loading="lazy" alt="" id="renaissance-uccello"
              src="./img/uccello-battle-of-san-romano.jpeg" style="max-width: 100%" /></a>
          <h3>The Renaissance</h3>
          <p>Finally we arrive at <a href="https://www.uffizi.it/en/artworks/annunciation">The Annunciation by Leonardo
              da Vinci</a>, painted around 1472-1476. Here, the lines of the building behind Mary clearly converge into
            a well-defined <a href="https://en.wikipedia.org/wiki/Vanishing_point">vanishing point</a> in the
            background. The mountains, ships, and trees behind the kneeling archangel Gabriel appear realistically
            distant. This was as much a technological advancement as an artistic one. Da Vinci applied his knowledge of
            mathematics to accurately represent the three-dimensional world on a two-dimensional canvas. </p>
          <a href="#img-davinci"><img loading="lazy" alt="" id="renaissance-davinci"
              src="./img/davinci-annunciation.jpeg" style="max-width: 100%" /></a>
          <h3>SQL: The Gothic Art of Query Languages</h3>
          <p> A SQL resultset is a two-dimensional canvas with rows on one axis and columns on the other. The typical
            star-schema data model contains hundreds of dimensions which can be used to slice, dice, and aggregate data.
            Because a SQL resultset can only naturally represent two dimensions, any single projection of the
            star-schema model can only represent the data in two dimensions. A query that aggregates along a single
            level of granularity is like the Gothic painting above -- a flat scene representing two dimensions, without
            any notion of depth. Queries that require aggregation at different levels of granularity (like the
            hierarchical subtotals of my <a href="index.html?postid=sql-bad-syntax#blog">last post</a>) are possible,
            but fit awkwardly into the two-dimensional output of the query. Looking at the results of a <code>GROUP BY
            ROLLUP</code> query is akin to looking at the Battle of San Romano. The point comes across, but the image
            is contorted and unnatural. </p>
          <h3>Malloy: A Query Language Renaissance?</h3>
          <p> Malloy’s approach breaks free of the two-dimensional constraints of rows and columns by allowing columns
            to take a “table” type. This means sub-tables can be nested within a single column, and hierarchies can be
            preserved. By allowing sub-tables within resultsets, Malloy results are able to faithfully represent the
            true dimensionality of the underlying data. The idea of nested tables is not a new one. Database researchers
            have discussed this idea of "relation-valued attributes" <a
              href="https://www.sciencedirect.com/science/article/abs/pii/0306437986900037">since at least 1986</a>, but
            no relational database on the market supports them natively. </p>
          <p>I described the following example in depth in my <a href="index.html?postid=sql-bad-syntax#blog">previous
              post</a>, but to quickly go over it again, the following SQL query generates a result aggregated at both
            the yearly and monthly level.</p>
          <script src="./gists/art__group_by_rollup_sql.js"></script>
          <script src="./gists/art__raw_results_with_duplicates.js"></script>
          <p>Note that data aggregated at the yearly level is duplicated in every row. This column is now "unsafe" for
            use by downstream queries. Summing the <code>yearly_sales</code> column will result in values that are
            wildly incorrect.</p>
          <p>Compare this to the equivalent Malloy query:</p>
          <script src="./gists/art__malloy_rollup.js"></script>
          <p>The <code>by_month</code> column contains a nested table, with its own rows and columns, and we no longer
            have duplicated data for yearly sales.</p>
          <a href="#img-malloy-nest-result-2"><img loading="lazy" alt="" id="malloy-nest-result-2"
              src="./img/malloy_nest_result.png" style="max-width: 100%" /></a>
          <p>This is more than just an aesthetic improvement. From a practical standpoint, any Malloy result can be
            plugged into downstream computations without worrying about the necessity of arbitrary filters or falling
            into the <a href="https://stackoverflow.com/questions/14328319/fan-trap-and-chasm-trap-database">fan/chasm
              traps</a>. The importance of this cannot be overstated! Writing a query the "obvious" way in SQL will
            oftentimes result in something that is syntactically valid, but semantically meaningless. I believe this is
            a major deficiency of the tools we use. The "obvious" way should be the correct way, and anything less is a
            serious design flaw.</p>
          <hr class="solid">
        </div>
      </article>
      <article>
        <label for="sql-bad-syntax" class="post-title">Why SQL syntax sucks, and why it matters</label>
        <a href="?postid=sql-bad-syntax#blog" class="selflink">[link]</a>
        <time datetime="2022-12-07">12.07.2022</time>
        <input type="checkbox" id="sql-bad-syntax" />
        <div class="post-body">
          <p>In a <a href="https://carlineng.com/?postid=malloy-intro#blog">previous blog post</a>, I wrote about
            Malloy, a new language for querying analytical databases. My main argument was that the killer feature of
            Malloy is its integration of a semantic layer inside an interactive query language. The <a
              href="https://news.ycombinator.com/item?id=32738874">discussion on Hacker News</a> was, as expected, quite
            lively, and provided great food for thought. This post is a response to some of those comments, an
            exploration of some of the deficiencies of SQL's syntax, and a demonstration of how Malloy gets it right.
          </p>
          <p>A lot of those comments were along the lines of: </p>
          <blockquote>
            <p><a href="https://news.ycombinator.com/item?id=32738874#32744949"> SQL + recursive queries is Turing
                complete. Thus every arrangement and selection of data is reachable by a query. Therefore there is
                nothing Malloy can do that SQL can't (I'm absolutely sure of it).</a></p>
          </blockquote> and: <blockquote>
            <p><a href="https://news.ycombinator.com/item?id=32738874#32744378">I don't see much value in this. This is
                not aesthetically better than SQL. It's also not semantically better. This is just a different syntax
                that would parse to the same AST.</a></p>
          </blockquote>
          <p> Both of these comments dismiss Malloy, pointing out that one can write semantically equivalent queries in
            SQL. While they are technically correct that Malloy queries all have semantically equivalent SQL queries,
            this misses a critical point: <b>the syntax of a language has a profound impact on what users of that
              language choose to express.</b> As the saying goes, “first we shape our tools, then our tools shape us”.
          </p>
          <p>After over a decade of using SQL nearly every day for data analysis, I’ve developed a strong conviction
            that SQL’s syntax is not just awkward and annoying, but actively harmful. By making certain types of queries
            difficult to write and interpret, it trains users to avoid asking those questions. In <a
              href="https://news.ycombinator.com/item?id=32738874#32746468">this thread</a>, some commenters take on the
            challenge of implementing a SQL equivalent to a Malloy query. However, I think the relevant question is not
            “can you write a SQL query that answers question X?”, but rather “would you have thought to ask question X
            in the first place?” </p>
          <p> This speaks more broadly to a common <a
              href="https://ryxcommar.com/2022/11/27/goodbye-data-science/">dissatisfaction I see amongst analysts and
              data scientists</a>. Many analytics jobs are essentially writing SQL queries to answer questions from
            higher-ups. Data lies at the bottom of a mystical lake, and only the analyst knows how to mutter the right
            SQL incantations to retrieve it. However, when the time comes for the analyst to ask their own questions,
            the first ideas that will likely come to mind are ideas that are easy to express, and not necessarily those
            that are most impactful or creative. I suspect this is especially true in cases where the analyst does not
            have deep domain expertise, and operates in a centralized “ticket-taking” support model. The syntax of SQL
            subtly guides a user’s mindset towards answering particular types of questions, and avoid answering others.
          </p>
          <h3>A Simple Example: Nested Analysis</h3>
          <p>A concrete example of something difficult in SQL is any kind of nested analysis, such as computing
            subtotals and percent of total in a hierarchical dimension. For example, suppose we have a table of daily
            sales, and we want to roll up total sales simultaneously at the monthly and yearly level: </p>
          <script src="./gists/sql_sucks__sales_by_date.js"></script>
          <p> This is typically done in SQL with one of three approaches: (1) computing multiple subqueries and joining
            the results together, (2) using a window function to aggregate at different levels, or (3) using a GROUP BY
            ROLLUP clause, if supported by the database. The queries for each of these approaches is below: </p>
          <script src="./gists/sql_sucks__multiple_grain_queries.js"></script>
          <p> Each of these three options produces a correct answer, but presented in a seriously flawed way. A query
            like this requires the data to be aggregated along multiple grains (year and month, in this case); however,
            a typical SQL query is only capable of naturally representing data in two dimensions (rows and columns),
            which corresponds to one level of granularity. To represent more than one level of granularity, a query must
            resort to unnatural representations that are difficult to interpret and use. This is tough to explain in
            words, but hopefully the following examples are more clear. In approaches (1) and (2) above, the SQL results
            contain duplicate data for the coarser grain (yearly sales):</p>
          <script src="./gists/sql_sucks__results_with_duples.js"></script>
          <p> The <code>yearly_sales</code> column above contains duplicate data for each month's row. Doing a naive
            <code>SUM(yearly_sales)</code> aggregation on this table is a dangerous operation that will lead to
            incorrect results without the appropriate filters.
          <p>In approach (3), yearly aggregates are represented as rows where the finer-grained column (month) contains
            <code>NULL</code> values: </p>
          <script src="./gists/sql_sucks__results_with_nulls.js"></script>
          <p>Values aggregated at different grains are all contained in the <code>total_sales</code> column. Whether
            it's a yearly aggregate or a monthly aggregate depends on the values of other columns. Here again, running
            <code>SUM(total_sales)</code> is potentially a very dangerous operation. </p>
          <p>In all of these cases, the resulting “sales” columns can no longer be used as inputs to aggregate functions
            or other downstream transformations, since the rows contain duplicated information. If this table is
            consumed by downstream queries, those queries must take extreme care to apply the appropriate filters or
            transformations to prevent duplicates from entering the calculations.</p>
          <h3>The Malloy Way</h3>
          <p>The <a href="https://malloydata.github.io/malloy/documentation/language/nesting.html">nesting
              functionality</a> in Malloy allows you to write a subquery within an aggregation. This will produce a
            sub-table for each row in the GROUP BY clause. It feels much more natural to write the query this way, since
            the query is now written in a way that matches the hierarchical logic that’s being performed:</p>
          <script src="./gists/sql_sucks__malloy_rollup.js"></script>
          <p>The output is presented in a way that respects the hierarchical relationship of the data. Every aggregate
            column contains data at a single level of granularity, and we no longer see duplicate values for data at the
            coarser grain.</p>
          <a href="#img-malloy-nest-result"><img loading="lazy" alt="" id="malloy-nest-result"
              src="./img/malloy_nest_result.png" style="max-width: 100%" /></a>
          <p>This is possible because the <code>by_month</code> column in the result set of this query is of type <a
              href="https://duckdb.org/docs/sql/data_types/struct.html"><code>struct</code></a>, which effectively
            contains a sub-table. The Malloy VSCode extension renders this <code>struct</code> data as if it were just
            another table. This is easier to write, easier to interpret, and avoids all the pitfalls of the equivalent
            SQL implementation. The output of this query can be used naturally by any downstream queries or analyses
            without having to worry about hidden traps.</p>
          <p>It’s a pretty simple example, but I think it illustrates the point nicely. In my career as a SQL monkey,
            I’ve <b>*been asked*</b> to do analysis like this many times in the past, and its always been possible. But
            when looking at a dataset on my own, its rare that these are the first questions that <b>*I will ask on my
              own.*</b> I’m convinced that these syntactic improvements offer more than just enhanced productivity for
            the user, but enhanced creativity as well. To see what’s possible with more sophisticated examples, I highly
            recommend checking out some of the <a
              href="https://malloydata.github.io/malloy/documentation/examples/ecommerce.html">examples on the Malloy
              documentation site</a>, or playing with the <a href="http://malloy.lloydtabb.com/"> Composer application
            </a>, a web app built on top of Malloy. </p>
          <hr class="solid">
        </div>
      </article>
      <article>
        <label for="malloy-intro" class="post-title">A Sequel to SQL? An introduction to Malloy</label>
        <a href="?postid=malloy-intro#blog" class="selflink">[link]</a>
        <time datetime="2022-09-05">09.05.2022</time>
        <input type="checkbox" id="malloy-intro" />
        <div class="post-body">
          <p><i>Follow along with the discussion at <a href="https://news.ycombinator.com/item?id=32738874">Hacker
                News</a>.</i></p>
          <p>The foundations of SQL were laid at the dawn of the relational database. Back then, there was no such thing
            as a data warehouse, no such thing as a BI tool, and certainly no such thing as an <a
              href="https://www.getdbt.com/what-is-analytics-engineering/">Analytics Engineer</a>. And yet, SQL is still
            the primary user interface by which most data professionals interact with their raw materials. The
            underlying technologies have improved immeasurably, but aside from a handful of updates to the ANSI
            standard, the core of the language remains untouched. It’s practically a miracle that after 40+ years of use
            by countless data professionals, our interface to data is effectively the same. </p>
          <p>Since its inception, many computer scientists and database researchers have expressed their disdain for
            SQL, and their critiques are usually well-founded; however, every serious attempt to replace it as the
            de-facto standard has failed. Most attempts to replace SQL primarily address the language’s awkward syntax;
            for example putting the <code>FROM</code> clause first or removing the need for <code>HAVING</code> and
            <code>QUALIFY</code> clauses. Unfortunately, the reality is that SQL is “good enough” for most use cases,
            and according to <a href="https://youtu.be/IVlMB9akD1A?t=420">Aiken’s Law</a>, programmer training is the
            dominant cost for a programming language. It seems that syntactic sugar is simply not enough to overcome
            SQL’s entrenchment. </p>
          <p>This brings me to <a href="https://github.com/looker-open-source/malloy">Malloy</a>, a new query language
            for data analysis currently being developed by <a href="https://twitter.com/lloydtabb">Lloyd Tabb</a>,
            founder and former CTO of <a href="https://www.looker.com/">Looker</a>. Malloy addresses many of the
            aesthetic concerns that plague SQL, but far more interesting in my opinion is its integration of a query
            language and a semantic layer into a single language. But what is a semantic layer, and why is it important?
          </p>
          <h3>Semantic Layers</h3>
          <p>A semantic layer’s purpose is to codify domain-specific logic on top of database tables, and to prevent
            users from issuing queries that are syntactically valid, but semantically meaningless. For example, what
            happens if you write a join between two tables with incorrect keys? In most relational databases, primary
            and foreign keys are just strings or numbers. Some databases may enforce referential integrity, but most
            will not complain if you attempt to join, say, <code>CUSTOMER_ID</code> with <code>ORDER_ID</code>. As
            another example, it’s often the case that primary key columns are integers, and databases will happily let
            you use them as inputs to any aggregate function that takes a number input, like <code>SUM</code> or
            <code>AVG</code>, even though the result is nonsense. Lastly, every organization has special rules that must
            be applied to their datasets in order to correctly calculate key metrics. For example, what inputs and
            adjustments go into calculating the revenue that Investor Relations reports out to Wall Street? Someone with
            access only to the raw data, but not the requisite domain-specific knowledge will not be able to accurately
            reproduce these metrics. The semantic layer provides a place to set these rules and require queries to abide
            by them; e.g., which joins are valid, which columns can be grouped on, or what inputs go into a particular
            aggregate function. </p>
          <p>A semantic layer usually takes the form of an application that sits on top of the database, along with
            configuration files that define the rules described above. Examples of products on the market today include
            SAP’s <a
              href="https://help.sap.com/docs/SAP_BUSINESSOBJECTS_BUSINESS_INTELLIGENCE_PLATFORM/3d4f417fd0764f909c0ef7931e19fe1a/e4af1c39d2b94ca5bc8a991b4ff26f5f.html?locale=en-US">Business
              Objects Universe</a>, Looker’s <a href="https://cloud.google.com/looker/docs/what-is-lookml">LookML</a>
            and <a href="https://cube.dev/">Cube</a>. I was an early user of Looker, and that experience left a strong
            impression. LookML allowed me to define the logic necessary to package all of our data sources together as a
            cohesive single source of truth. Our data warehouse turned from a tangled mess of tables that only a skilled
            data scientist could operate, into a trusted repository that the typical PM or business partner could pull
            insights from. </p>
          <p>Despite the huge value we got out of Looker and LookML, the analytics and data science group never loved it
            as much as I did. In comparison with <a href="https://www.tableau.com/">Tableau</a>, Looker’s interactive
            data exploration and analysis capabilities are relatively limited, and most data scientists saw exploration
            and analysis as their primary activity. They viewed writing LookML configuration as a chore, and the user
            experience did not help. To add a new dimension or measure, a data scientist would have to edit a YAML
            configuration file, check their changes into source control, and reload their configuration before they
            could view any results. A simple workflow that could take 2 seconds in Tableau might take over a minute in
            Looker. When trying to explore a dataset, the ability to iterate “at the speed of thought” is critical, and
            that extra latency was a source of frustration for many. As a result, data modeling and data exploration
            were viewed as two entirely separate disciplines. Data scientists greatly preferred tools that aided the
            latter, much to the detriment of everyone who was NOT a data scientist. </p>
          <h3>Malloy</h3>
          <p>So finally we return to Malloy. As I mentioned previously, Malloy is a query language that compiles to SQL,
            and looks very familiar to anyone who has used SQL. The team has also built a VSCode extension that allows
            users to connect to a database and start writing queries, currently with support for BigQuery, DuckDB and
            Postgres. The semantic layer within Malloy is accessed via writing Sources. From the <a
              href="https://looker-open-source.github.io/malloy/documentation/language/source.html#sources">docs</a>:
          </p>
          <blockquote>
            <p>A source can be thought of as a table and a collection of computations and relationships which are
              relevant to that table. These computations can consist of measures (aggregate functions), dimensions
              (scalar calculations) and query definitions; joins are relationships between sources.</p>
          </blockquote>
          <p> Let’s take a look at an example Source from the <a
              href="https://github.com/looker-open-source/malloy/blob/2135a51c46f3076f9d28c2136f9d8ca4cf9a101b/samples/duckdb/faa/2_flights.malloy#L8-L32">Malloy
              Github repo</a>, looking at a database of flights.: </p>
          <p><code>
<pre>source: flights is table('duckdb:data/flights.parquet') + {
  primary_key: id2

  // rename some fields as from their physical names
  rename: origin_code is origin
  rename: destination_code is destination

  // join all the data sources
  join_one: carriers with carrier
  join_one: origin is airports with origin_code
  join_one: destination is airports with destination_code
  join_one: aircraft with tail_num

  // declare some resusable aggregate calculations
  measure:
    count is count()
    total_distance is sum(distance)
  }
}</pre></code>
          </p>
          <p>This Source has a few key components: the name of a table in the database, the primary key of the table,