Self-Optimizing Analytics on Databricks: Table-Valued Functions and Semantic Layers

Summary

Musinsa, South Korea's largest fashion platform managing 16,000 tables, solved an analytics architecture trilemma — where every design choice forces trade-offs between productivity, latency, and cost — by shifting from a table-first to a logic-first paradigm using Table-Valued Functions (TVFs) on Databricks SQL.
Four standardized TVF patterns — snapshot, snapshots, period, and periods — create a composable semantic layer with dynamic lineage that maps the entire function graph, enabling reusable logic that survives across query contexts without organizational amnesia.
Demand-driven caching built on top of the TVF semantic layer resolves cost concerns automatically, and the declarative SQL substrate makes the system safe for AI agents, preventing non-deterministic modifications to the pipeline.

Self-Optimizing Analytics on Databricks: Table-Valued Functions and Semantic Layers

Musinsa, Korea's largest fashion platform managing 16,000 tables across decentralized domains, faced a trilemma where every architecture choice forced trade-offs between productivity, latency, and cost. The root cause was not technology but mindset: build a table before you analyze. This created organizational amnesia where results survived but logic disappeared.
Watch how Musinsa flipped the paradigm from tables to logic, building a TVF-based semantic layer on Databricks SQL that powers self-optimizing analytics. Discover how dynamic lineage maps the entire function graph, demand-driven caching solves the cost problem, and declarative SQL provides a safe substrate for AI agents. Learn the four TVF patterns (snapshot, snapshots, period, periods) that standardize time and enable composable, reusable logic. Includes live demos of caching and cohort analysis.
🤝

Chapters

FAQs

What is the analytics architecture trilemma Musinsa faced?

Musinsa identified a trilemma where every analytics architecture decision forces unavoidable trade-offs between three competing needs: productivity, latency, and cost. Traditional table-first approaches resolve one or two of these but consistently fail on the third, creating organizational tension that no team configuration can fully resolve without a fundamentally different architectural foundation.

What are Table-Valued Functions and how does Musinsa use them?

Table-Valued Functions are SQL functions that return a table as their output, allowing query logic to be encapsulated and reused like a function rather than materialized as a physical table. Musinsa built a semantic layer on Databricks SQL using four standardized TVF patterns — snapshot, snapshots, period, and periods — that standardize time handling and enable composable query logic that persists even as business definitions evolve.

How does Musinsa's demand-driven TVF caching work?

Musinsa's caching layer evaluates three questions before deciding whether to materialize a TVF result: how frequently the function is called, how expensive it is to compute, and how time-sensitive the result is. When the economics justify caching, the system automatically stores and reuses computed results, resolving the cost dimension of the trilemma without requiring manual engineering decisions for each query.

Why is Musinsa's semantic layer described as AI-safe?

Musinsa's CTO describes the semantic layer as AI-safe because the underlying TVF architecture is purely declarative SQL, meaning AI agents can advise on what to compute but cannot alter the pipeline logic or introduce non-deterministic modifications to how data is processed. This separation ensures that AI can accelerate analytics without compromising the correctness guarantees that mission-critical business data requires.

Full transcript

[00:09] Hi everyone. It's wonderful to be here. I'm Jin Young from Musinsa's data platform team. Before we get into main acts, let me have three quick things. First, a greeting from our CTO who can't make it in person. Second, 30 seconds intro about myself. Third, my company Musinsa.
[00:27] And then straight into the main story. Oh, and one thing, has a quick heads-up. We brought some Musinsa standard gift kit. So, anyone who asks a question walks away with one at the end. So, let's get started.
[00:52] So, a word from our CTO, Andrew. I'm going to play a video.
[01:11] Hi everyone. I'm Andrew Jun, CTO of team Musinsa, the number one fashion e-commerce platform in Korea. Today, let's talk about the foundation underneath what you are about to see. Here's the tension every team in this room is facing right now. AI is brilliant, but it's probabilistic.
[01:27] Sometimes it's confidently wrong. And the data a business actually runs on settlement, inventory, and transactions can't be wrong, not even once. Most teams try to close that gap by making the AI more careful, but we went the
[01:43] other way. We asked a different question. What do we build underneath the AI so that it's safe to let it in? That's exactly what Jin Young is about to show you, a semantic layer where AI works with a world of intent and figures out what you want to
[02:01] know, while the system quietly takes care of how it's computed and it keeps it consistent. AI doesn't alter the pipeline, but it but it advises. The foundation layer keeps it true. That's what we mean when we say Musinsa is going AI native, not
[02:19] AI placed on top of everything, but a foundation built for AI. So, we can move faster for our customers and brand partners without ever losing the ground beneath us. That foundation That foundation is today's story. Now, I'll
[02:36] hand it over to Juyoung. Thank you.
[02:56] So, thank you, Andrew. A quick background of myself, 7 years building data platform from zero at enterprise scale at startup speed across APAC companies. The job is almost the same every time. Make data fast enough to keep up the business. Three patterns kept resurfacing and they
[03:12] became the foundation of today's talk. Let me name them up front. First, semantic layer. Second, dynamic lineage. Third, cached TVF, table valued function. Separately, each is useful. Together,
[03:28] they resolve the tension data engineer have lived with for many years. That's my claim for next 30 minutes. Let's start with the scale of the problem. Musinsa is South Korea's largest fashion ecosystem, 3.4 billion dollar in GMV,
[03:44] 11,000 partner brands, 10 million active users across 13 markets. This is not a just shopping mall. It's multi-vertical giants. Seven verticals from offline mega stores all the way to the manufacturing national teams Olympic uniforms.
[04:01] These are beautiful numbers, but here's the catch. Every single one of them multiplies our table. Business complexity dictates data complexity. As of 2026, we manage over 16,000 table with roughly 6,500 tables in core.
[04:18] And asking a finance team to carry that volume of traditional architecture, that's a tall order. So, let me introduce our company. So, how do we get here? Musinsa captured the fashion market online and off online and offline across
[04:36] many platforms. Musinsa 29CM Sold out and more. Every acquisition, every new service scaled incredibly fast. And eve- every single one of them multiply our data beneath it.
[04:52] The byproduct of the growth was a sure a sheer proliferation of table. First day on my job, a question every analyst knows, where's the single source of truth for orders? Search for order in the catalog.
[05:08] I hit 140 tables. And all of them look like order. 16,000 table, but only a fraction of that in actual meaning. The gap is the reason I'm standing here today.
[05:24] Let me give you a very quick map of today's journey. Think of it as a six-chapter story. One, we name the enemy, the trilemma. And two, the paradigm shift. And three, we standardize time. We make the layer navigable with lineage.
[05:41] And we cache it into a substrate where AI can safely stand. That's the chapter. And by the way, it's not just a slight tour. We prepared four different live demos along the way.
[05:59] Let me walk you through the first trilemma. Every data team architecture lives under three different constraints: cost, latency, and productivity. They are constantly at odd. When it's faster, it costs more. When it's cheaper, it runs slower. So, try to
[06:17] win both. The system gets so complex that people running it burn out. So, pick two. That's the trilemma we defined. Not now classically, this was just cost versus latency. Productivity got ignored because pipelines were the private domain of
[06:33] specialist engineers. Today, we put all three all three of them on the table. If that sounds a bit obscure, let me break it down. Three attempts, three dead ends. First table.
[06:49] Materialize everything. It's cheap until one late upstream jobs drags the whole tree behind hours hours behind. Latency cascades. Second, view always fresh until every read runs again and nested view rescan
[07:05] the same data. Compute cost shoots up. Third, lambda. Split batch and speed for fresh and cheap, but now your logic lives twice and every change cost double. The three corners, the trilemma, we needed to break the triangle.
[07:25] The turning point in our thinking was the platform itself like Databricks. Well, old platforms store the data, but modern platform stack logic. On the old ones, there were no logical asset. So, you join dimensions, facts at query time. It was strictly specialist
[07:40] work. On the other side, the modern platform flips that. Logic becomes a first-class asset, living, parameter-driven, stacked in layer, and kept close to the business.
[07:57] Why function? Let's look at the scoreboard. Table, great for volume, but they require a pipeline per grain. Latents cascade down downstream, and on-call burdens growth with every
[08:13] Materialized view, less overhead, but they struggle with a nested structure. Not great for building a layer. Views, the simplest option until a filter misses the partition key. Then then they lose the data skipping and recompute everything from scratch.
[08:29] The TVF is the only option left. Business option in declarative SQL, a force time parameter that guarantees pruning, composable, nestable, and but lazy by nature. It's the only one that scales as a layer.
[08:44] It has two gaps, though. First, it stores nothing, so compute costs linger. Second, it's invisible to catalog, the lineage, making it hard to trace. So, a cached TVF fixes the first.
[09:00] Dynamic lineage fixes the second. That's the semantic layer I'm going to introduce. The semantic layer, right on top of your data marts, where data access becomes consistent, spoken in the language of declarative SQL. The bar is high. It has
[09:17] to satisfy three different audience at once. The uh for analyst, short queries and a stable rule set. For analytics engineer, double uh zero double zero duplication, excuse me, write once, reuse everywhere, update in
[09:33] one place. For platform engineers, lean compute, staying well within the engine's limit. One layer, every team wins. And it scales in two directions. Up by adding layer and out by adding domains.
[09:49] Because we aren't compounding tables here, we're compounding logics. And it gets more valuable over time. Here's the payoff. On one side, self-service. Anyone can get the data they need just
[10:05] by passing parameters. On the other side, centralization. Engineers own the protocol underneath. Usually these tools are incompatible. But the semantic layer I'm going to present is ambidextrous, which means the paradigm shift that I'm going to introduce. Next slide.
[10:22] The paradigm shift. So, technology can scan hundreds of terabytes in second right now. So, why we do why do we struggle still? Because data mart keep the what but lose the why. The raw number survives in marts.
[10:39] For example, here. What does survive? VIP revenue at 5.2 million, retention at 34%, but the reasoning behind is gone. I call it organizational amnesia.
[10:55] It's like a postman who memorized every address and shortcut. The day he quits, the knowledge disappear with him. That is the fatal flaw of the data mart. Instead of store the logic as a TVF, by doing so, the logic the why lives inside the function itself. It remains
[11:12] readable, declarative, and permanent. The logic stay intact. Ultimately, logic codification is data mart's sharpest weapon. So, we flipped the question. The old question was, which table do I query?
[11:29] The new question, which logic do I compose? What logic do I compose? It sounds minor, but it changed the entire landscape. Before, one pipeline per mart, one schema change meant redesign the whole thing. And if an analyst or engineer
[11:45] left, the logic was gone for good. But after you manage a single center function, you just change parameter, the logic lives in code owned by the domain, just like infrastructure as code, IAC. It's just test within 30 minutes. You
[12:02] stop stacking stable, and you start treating data as a product. So, here's the shape that I'm going to introduce. The tool is TVF, and its heart is encapsulation. At at a core lives the logic. Metric
[12:17] definition, grain preservation, business rule, and naming conventions. Expert manage it inside, which means the data engineer govern the whole system by designing the model model protocol. That is centralization. On the other side on
[12:32] the outside is the interface. Simple, time-based parameter. An analyst or product manager, anyone passes those three parameters and gets a complex analysis back. That is self-service. As I said before, centralization and
[12:50] self-service are incompatible, but encapsulation allows them to coexist coexist, excuse me. Look, locked on the inside and open to open to everyone on the outside.
[13:08] Here's a high-level of view of the system on one page. Motions are semantic layer on Databricks. Let's look at the just the spine of it here. Oh. Where where the medallion architecture usually sit, bronze, silver, and gold
[13:25] layer, we put our own three different layers, layer zero, layer one, and layer two. The cash TVF goes exactly where where it pays off. I'm going to explain later. Nowhere else. And every everything else is built to support those. Think of this
[13:42] as a map and I will zoom in piss by piss. Standardizing time, let's start from the foundation. Why time first? To stop one question from getting three different answers. Ask three analysts
[13:58] for January's data, you get different columns, different formats, different boundaries. Three people, three answers. That is a total fiasco. Our standard simplifies time into two concepts using TVF. First, a state.
[14:14] Um state, a point of point in time, such as what was it at the exact moment? A brand profile as of January 1st, the dimension data. Second, events, a time range, what happened during this period? January's
[14:31] orders, that's the facts. One question, one answer, that's why time is the very first thing we standardize. So, standard breaks down into four distinctive patterns based on just two
[14:47] questions. One, is it state or is it event? That's one question. And is it one or many? That's the second question. Crossing them, you get a simple two by two matrix, which is snapshot
[15:04] and snapshots. So, snapshot, singular, one state at a moment, like a photo, and snapshots, plural, a state over time, a time lapse. And period here, and periods, the plural. So, period is a
[15:20] single aggregate of events over a period and periods plural the aggregate slice into a series like a report. So photo time lapse summary and report these four mental images make up the entire vocabulary. You can pick any
[15:37] granularity you like from 1 minute to a full year. The naming convention so here like this 1 minute to full year. And the TVF name is going to be like this which is the naming convention itself is very self-explanatory
[15:55] structured by layer entity and temporal type. This is an example here layer zero motion size an MSS and order and periods. So this is nice example. So you read it you immediately know it's what
[16:11] it is. So no documentation required and it relies on four words that analysts already use every day. Let's make it more concrete. So dimensions are things with a state so they take the snapshot or snapshots
[16:27] pattern for a singular point in time. What did this brand look like on January 10th? And I'm going to use that layer zero brand snapshot and input the one single parameter here. You will get the state
[16:44] as of and for multiple points in time how did it update day by day throughout January? That's layer zero brand snapshots and I put the daily interval in here in case and that's like a time lapse you we're
[17:00] going to get as a data a a table format of data here. So this is nice because instead of looping a query for each thing each day it anchors the state to the right partition. It and then reads it once and one
[17:17] optimized scan that we're going to provide. Now, moving to facts, the transaction that occur is the same pattern as before, just with range instead of points. For a single range, aggregate all of January like that.
[17:34] And that's the layer zero MSS order period and two parameters because it's a one aggregation. And the other multiple ranges show this as a daily series. I'm going to use layer zero MSS order periods with the time interval, which is
[17:51] day in here. So, this is live demo I'm going to show show you. So, we've got the building blocks, now the payoff.
[18:13] So, step one, a simple composition. I'm going to use layer zero user attribute snapshot for dimensional data. Gives me the state of each user on January 1st. And layer zero user order periods gives me what they bought month by month. And I join them using user number.
[18:38] So, there it is. Every user cross growth with their monthly orders. This used to be an entire marked projects, hunting down the raw resources, writing down window functions, and joining to the transaction data. But, we did it just using the TVF. And step two, let's turn
[18:54] that into a cohort retention analysis. Take everyone whose first purchase was in February, like here. And uh track how many come backs month by
[19:10] month. And February is 100% right here, and then you see the curve goes like this. And you can drop this straight into your BI report with time granularity passed simply as a filter. That's how we use
[19:26] it, TVF. Going back to the next slide. Chap uh the lineage.
[19:44] Now, here's a fair question. Why build your my own lineage? You need a Unity Catalog already does lineage, and yes, it's genuinely good. But, it's table native. So, we need something function native. A TVF doesn't just sit there as a static object. When it runs, it expands inline and
[20:00] disappear. So, that was a problem. Now, we face a new challenge. The system forgets the function as soon as it runs. How do you govern logic of these then that leaves no trace? So, we stopped asking the catalog. We
[20:16] asked execution logs what actually what actually ran. And we built our own map from two sources. Source one, with the function definition themselves. We pull each function output schema with a simple trick. Select from it with a false
[20:33] predicate where one equals zero. No rows, no execution, just the shape. And source two, the query history. The parse it with SQL glot to to recover the real function to function edges. And to avoid re-parsing
[20:48] the same query a thousand time, we fingerprint and group them. Cutting their parsing the workload by one to two uh two one to two orders of magnitude. Merge the two, and you get a fully recovered function
[21:04] graph. Let me show you why that matters. I'll take our core L0 user daily table, one
[21:20] of the foundation of everything we just built, and I will ask a single question. If I modify this table, what breaks? And using our lineage function with as in down and putting the table name here,
[21:37] you get the result right here. So, and there's a blast radius right here. Everything downstream lights up like this. And everything every TVF stacked on top, every
[21:52] dashboard, every job, every analyst query that actually ran, and every and even AI consumer. Notice what we captured here, not just function, but real consumers. So, bypassing the semantic layers allowed in
[22:07] the in our enterprise, but also it is not invisible. So, this is some kind of enforcement in practice in our company.
[22:23] And now we can navigate the entire layer. So, we established two pillar, the TVF and the lineage map. With those in place, it is time for the final piece, which is the cached TVF. Let's dive into the architecture. Here's a quick simple setup. There are
[22:39] two concepts to understand before I show you this live. First, through CDC, we capture the change comment in the production database and exact moment it happens, and using the DMS to save the parquet log. Second, single serving layer
[22:56] here, this one. Um single serving layer reads the slow accurate batch table and unions it with a fresh change log on top, deduplicates to keep the only most recent comment, and returns the absolute current truth.
[23:12] Now, watch the conflict happens. The batch table here refreshed an hour ago, so it is stale data. It says the user is active. But, our function reads the batch plus the live change log.
[23:28] It says deactivated here. Same question, same instant, two completely different answers. Remember our very first primers? I told you the lambda usually meaning maintaining two separate code bases, batch and speed.
[23:44] They drift apart, essentially burning down the productivity corner of the tree lemma, but this TVF lambda is the answer. Um let me show you the live code for this how this this is
[24:00] working. So, here we are in the real workspace with live data. The user is active right now. I'm going to close this dear dear account live in front of you, this one.
[24:17] So, this is like a my manual comment for change status of the user. So, let's assume that we committed this change that from CDC. And then, I'm going to run this one.
[24:33] Um So, let's say that comment, which is operational events just written, and then I'm going to check this table, which is layer zero user, which is not a function, and it says active here.
[24:50] But, if I run the TVF with the parameter current timestamp, Which is user snapshot and the parameter is current time stamp. It says deleted in here.
[25:07] So for truth the exact second. See the layer column in here? I put it in purpose like layer is speed. So it's seamless. Our function quietly did the four thing, reach the batch pull
[25:23] reach the batch and pull the fast change log, deduplicate the newest right and return the answer. And here's the twist.
[25:39] So I I asked the same function about May 31st and it reads the batch and active, which means that history is not touched. So this single function uh is both a live feed as well as the time machine.
[26:02] Okay. And just one left. Cost. A pure TVF has a uh cost problem. Every query rescan the source just like the view. The cached TVF fixed this with demand-driven caching.
[26:18] When you call periods, the system splits the request into time blocks. Cached block return instantly. Only the missing blocks trigger a live scan. To show you this clearly, I deployed a data bricks app with a better UI like
[26:36] this. Watch. So I'm going to run a first call from range 2000 2026 June 8th 2026 June 10th
[26:51] and granularity as of day and play.
[27:09] So, first call all miss. It calculates and feels the cache as it go. See, compute and feel catalog here and it's done right now. And I'm we're going to run a
[27:25] And we're going to run the same thing. The June 8th to June 10th. See, since we already compute and feel that the data is all hit and the results gets faster, which is caching.
[27:41] And we're going to extend the date of June 11th.
[27:58] So, this is the elegant part. I update the time range forward as of June 11th. And the overlap comes from the cache, which is hit in here. And the new gap is calculated fresh, which is a miss here.
[28:15] Which is a partial hit. And this works exactly the same as in SQL editor as well. So, I'm going to switch back to the SQL editor and we're going to do the same thing with the TVF in SQL editor.
[28:36] So, let me truncate the cache data ready first. And we're going to retry the TVF. Uh it takes a little bit of time. When I did this, it was really fast. I mean,
[28:52] sorry about that. So, we're going to truncate the date. We already cashed. To make sure um
[29:29] So, since we deleted the data, we ran layer zero order period cache again. And the time range here with the same granularity as the day.
[30:01] So, here So, see? It's all missed because we truncated the data and re-backfilled in here. So, everyone can use a TVF in SQL editor as well. And this is the point where the pic two things is broken because we had the TVF
[30:17] and caching. So, it's not uh it's not compute and fill every single time like view, even if we're using the TVF.
[30:38] Okay. But, here's hard truth. The cached TVF works so well and we got really greedy. We try to cache everything in sight and it backfired really badly. So, we learned hard way. We learned to ask three question. Number one, compute
[30:55] cost. Does caching actually make the result smaller and faster? And number two, storage cost. Do people really re-query that historical range or just only the recent data? Number three,
[31:10] coverage. Is it the logic even in deterministic class that we can safely cache? Caching everything is wasteful, not prudent. The goal is to cache wisely only when the economic says yes.
[31:27] Two factors dictate what is worth caching, coverage and access pattern. Let's start with the coverage. Think of four nested circle here. At the center, the materialized view is tiny but rigid and rock solid. Views are
[31:42] bigger. Semantic layer is also bigger. The raw TVF is the largest circle. It can express anything but is least predictable. More power, less stability is always a trade trade-off. So, where
[31:58] do you cache? Not the TVF because it's too unstable. Not the materialized view, it's too narrow. You can cache the largest region that is still deterministic which is a semantic layer. Next, access pattern. Some consumers
[32:14] only read the recent data like this. For them, caching old history is just paying rent. So, a plain TVF is better. So, others reread the same history constantly and that is exactly where
[32:29] cached TVF pays off. Now, I'm going to make a bold claim based on Machine Learning Project because everything we have just built so far leads to one ultimate conclusion. The TVF becomes the substrate for all AI
[32:45] and data and AI. The problem is this one sentence. AI is non-deterministic, pipelines must be deterministic. Ask AI the same question twice, you get the two different answer. But in a pipeline, yesterday number and
[33:00] today's number have to match theoretically. So, which layer is the right place for AI? If you make AI orchestrate the process such as the physical DAG, it has to pick every joint, every partition, every dependency. The answer space grows exponentially and
[33:17] errors are silent. It's terrible place for AI. But if you only give AI your pure intent through declarative SQL, it only has to pick the result. The engine handles the rest. The answer space is narrow, the code is pure intent, and it can be verified at a
[33:33] glance. The This declarative AI layer is a substrate for AI can play safely. Now, you might have you might have an objection. If we have two queries batch and real time, the AI still has to pick
[33:50] one, which one to use. That is fair point, but that's a fork where AI can make a mistake. So, we erase the fork completely in here, unified TVF. We unified it into a single TVF. Batch
[34:06] and real time are now the exact same function. Only the parameter differ. There's no decision left for the AI to get wrong. The AI is safe here not because it's clever, but because the ground beneath it cannot move.
[34:26] Let me wrap up with the final chapter. Let's return to the beginning, the challenge. I type order into catalog, I got 140 table over 16,000 table in total. No single source of truth. To manage this problem, we had to resolve the trilemma. Productivity, latency, and cost.
[34:43] And our newest data consumer, a a non-deterministic AI that queries a row table and answers differently every time. That was challenge and the solution step stacking logic uh step stacking table, sorry, and stack logic.
[34:59] The TVF draws a clean line between the logical world and physical world. And exactly one line lambda collapses into a single function. Our customer Our custom lineage maps the entire layer and a cache TVF turns caching into a observed economic
[35:15] decision. And ultimately, because the logical side is strictly declarative and deterministic, AI becomes a safe, trusted advisor. So, this is a this this has been our journey. From manual pipeline to logic
[35:32] standardized as TVF to a to a layer made navigable by lineage and to demand-driven TVF caching. And ultimately, toward a system that safely optimizes itself with AI.
[35:50] If you forget everything else, remember remember just three things. One, trilemma is a mindset that we can fix. Two, standardize logic as function, TVF, and cache with discipline. Three, add lineage and AI and a semantic layer from something you maintain.
[36:08] Uh into something that you made something that maintain itself. I didn't do this alone. I built with Seho, who led this architecture, Jin Hwan and fellow engineer Cheol, Haesong, and Seung Yong.
[36:24] And this isn't the end. We intend to open source this facade. The Trino belongs to all of us. And one quick note, you'll get a survey for each session you attend. You can submit it in Data Bricks events under my survey. If you can spare a moment, I'd
[36:39] be really great grateful. And that's everything. And thank you very much.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.