The Future of SQL Development — the essence of the dbt F✦SION engine and the world beyond

We introduce the vision of 'the future of SQL development' realized by the dbt F✦SION engine. We will provide an easy-to-understand explanation of SQL development challenges, the possibilities of static analysis and local execution with F✦SION, and the future of SQL portability, covering technical approaches and prospects.

Transcript

1. Copyright stable, inc. All rights reserved.
 The Future of SQL Development — the essence of the dbt F✦SION engine and the world beyond 2025.06.17 dbt Global Circuit Series: Tokyo dbt Meetup#14 1 stable Inc. Data Engineer Yuki Misumi
2. Copyright stable, inc. All rights reserved.
 Today’s theme
 2
 ⚠ For convenience, “dbt F✦SION engine” is abbreviated as “F✦SION.” We’ll explore the future of SQL development powered by the dbt F✦SION engine.
 - Challenges of SQL/dbt development
 - dbt F✦SION engine
 - The approach of F✦SION
 - The future of F✦SION
 - What F✦SION has given us

3. Copyright stable, inc. All rights reserved.
 About Me 3 Yuki Misumi (@myshmeh) Data Engineer, stable Inc. - Background - Privacy governance → Software engineer → M.S. in Computer Science → Data engineer - Joined stable Inc. in January 2025 - Areas of Interest - Computer systems in general - Weekends - Enjoying Monster Hunter World :)
4. Copyright stable, inc. All rights reserved.
 1. Introduction 2. Challenges of SQL/dbt development 3. dbt F✦SION engine 4. The approach of F✦SION 5. The future of F✦SION 6. What F✦SION has given us 4
5. Copyright stable, inc. All rights reserved.
 Challenges of SQL/dbt development Broadly speaking, they fall into two categories: 5 it costs
 it’s slow
 Every step requires the DWH The dev experience is not quite there…
6. Copyright stable, inc. All rights reserved.
 6 Sound familiar? (Part 1/2) dbt run --empty just to lint your SQL
7. Copyright stable, inc. All rights reserved.
 Sound familiar? (Part 1/2) Would this run? 7
8. Copyright stable, inc. All rights reserved.
 Sound familiar? (Part 1/2) Would this run? 8 Oops – we just standardized on British English… 😇
 -- this does not exist

9. Copyright stable, inc. All rights reserved.
 Sound familiar? (Part 1/2) dbt run --empty → burns 1 minute of Snowflake credits 9 -- this does not exist
 Oops – we just standardized on British English… 😇

10. Copyright stable, inc. All rights reserved.
 Sound familiar? (Part 1/2) dbt run --empty → burns 1 minute of Snowflake credits 10 Slow feedback &
 The credits add up💰
 Oops – we just standardized on British English… 😇

11. Copyright stable, inc. All rights reserved.
 Sound familiar? (Part 1/2) dbt run --empty → burns 1 minute of Snowflake credits 11 Can’t we lint locally just like in Python?
 Oops – we just standardized on British English… 😇

12. Copyright stable, inc. All rights reserved.
 12 Sound familiar? (Part 2/2) Painful debugging
13. Copyright stable, inc. All rights reserved.
 Sound familiar? (Part 2/2) When debugging, copy-paste code from the target directory, tweak it by hand, and run it manually... 13 1) copy-paste
14. Copyright stable, inc. All rights reserved.
 Sound familiar? (Part 2/2) When debugging, copy-paste code from the target directory, tweak it by hand, and run it manually... 14 2) modify the FROM clause 1) copy-paste
15. Copyright stable, inc. All rights reserved.
 Sound familiar? (Part 2/2) When debugging, copy-paste code from the target directory, tweak it by hand, and run it manually... 15 2) modify the FROM clause 1) copy-paste ah.. such a pain

16. Copyright stable, inc. All rights reserved.
 Sound familiar? (Part 2/2) When debugging, copy-paste code from the target directory, tweak it by hand, and run it manually... 16 Can’t we just peek at the CTE output quickly? 🐢
 2) modify the FROM clause 1) copy-paste
17. Copyright stable, inc. All rights reserved.
 Challenges of SQL/dbt development The list goes on and on! 17 - Linting needs the DWH - Function-call validation needs the DWH - Column existence checks need the DWH - Running tests needs the DWH - … - Network latency for every single query - Peek at CTE data requires manually writing the query - Investigating a column’s origin requires manually reading the upstream model files - … it costs
 it’s slow

18. Copyright stable, inc. All rights reserved.
 18 Why can’t we do X?
19. Copyright stable, inc. All rights reserved.
 Why can’t we do X? dbt lacks a built-in SQL execution engine! That means, if dbt had one, we’d be able to lint and run SQL locally. 19 unit test!
 failed...

20. Copyright stable, inc. All rights reserved.
 20 dbt F✦SION engine tackles this challenge directly.
21. Copyright stable, inc. All rights reserved.
 1. Introduction 2. Challenges of SQL/dbt development 3. dbt F✦SION engine 4. The approach of F✦SION 5. The future of F✦SION 6. What F✦SION has given us 21
22. Copyright stable, inc. All rights reserved.
 dbt Fusion engine understands SQL natively and “will sit at the heart of the next generation of analytics engineering workflows” What is dbt F✦SION engine? 22 *1 https://docs.getdbt.com/blog/dbt-fusion-engine *1
 - Released May 28 (Public Beta) - Currently supports: Snowflake, Databricks - License: - mostly open-source (ELv2、 Apache) - some are closed e.g. State aware orchaestration, LSP server
23. Copyright stable, inc. All rights reserved.
 23 Cool cool. So.. what can it do?
24. Copyright stable, inc. All rights reserved.
 24 Current dbt F✦SION engine features 1/3 SQL validation
25. Copyright stable, inc. All rights reserved.
 Current dbt F✦SION engine features 1/3 SQL validation works now on VSCode! Bye bye to wasting money on dbt run --empty💰 25 No DWH needed to validate function call! No DWH needed for syntax check!
26. Copyright stable, inc. All rights reserved.
 Current dbt F✦SION engine features 1/3 SQL validation works now on VSCode! Bye bye to wasting money on dbt run --empty💰 26 Even catches invalid columns! -- this does not exist

27. Copyright stable, inc. All rights reserved.
 Current dbt F✦SION engine features 1/3 SQL validation works now on VSCode! Bye bye to wasting money on dbt run --empty💰 27 Even catches invalid columns!
28. Copyright stable, inc. All rights reserved.
 28 Current dbt F✦SION engine features 2/3 Preview CTE
29. Copyright stable, inc. All rights reserved.
 Current dbt F✦SION engine features 2/3 “Preview CTE” lets you quickly inspect CTE data! 🐢 29 1
 2

30. Copyright stable, inc. All rights reserved.
 30 Current dbt F✦SION engine features 3/3 Column level lineage
31. Copyright stable, inc. All rights reserved.
 Current dbt F✦SION engine features 3/3 Column-level lineage shows how each column was derived at a glance!🐢 31
32. Copyright stable, inc. All rights reserved.
 dbt F✦SION engine as a solution Most of the challenges are already resolved! 32 it costs
 it’s slow
 - ✅ Linting needs the DWH - ✅ Function-call validation needs the DWH - ✅ Column existence checks need the DWH - (later) Running tests needs the DWH - … - ✅ Network latency for every single query - ✅ Peek at CTE data requires manually writing the query - ✅ Investigating a column’s origin requires manually reading the upstream model files - …
33. Copyright stable, inc. All rights reserved.
 F✦SION roadmap Increases adapter support and dbt-core feature coverage toward GA. Possible F✦SION support on dbt Projects in Snowflake. 33 *1 https://docs.getdbt.com/blog/dbt-fusion-engine-path-to-ga *2 https://youtu.be/w7C7OkmYPFs *1
 *2

34. Copyright stable, inc. All rights reserved.
 34 Sounds great — but
35. Copyright stable, inc. All rights reserved.
 35 Why couldn’t dbt-core do all these? What makes F✦SION different?
36. Copyright stable, inc. All rights reserved.
 1. Introduction 2. Challenges of SQL/dbt development 3. dbt F✦SION engine 4. The approach of F✦SION 5. The future of F✦SION 6. What F✦SION has given us 36
37. Copyright stable, inc. All rights reserved.
 Why can’t we do X? dbt lacks a built-in SQL execution engine! That means, if dbt had one, we’d be able to lint and run SQL locally. 37 unit test!
 failed...
 The root problem in dbt-core was lacking a built-in SQL execution engine.
38. Copyright stable, inc. All rights reserved.
 Why can’t we do X? dbt lacks a built-in SQL execution engine! That means, if dbt had one, we’d be able to lint and run SQL locally. 38 unit test!
 failed...
 How exactly does F✦SION tackle this?
39. Copyright stable, inc. All rights reserved.
 The approach of F✦SION F✦SION contains a SQL execution engine i.e. it brings the compilation and execution capabilities dbt-core relied on to itself! 39 ⚠ For illustrative purpose, only the relevant parts of the compilation process have been extracted and simplified. ？
 ？
 F✦SION
 dbt-core
 input
 execution
 compilation
 ？
 ？
 data
 SQL
 STEP
 ARTIFACT

40. Copyright stable, inc. All rights reserved.
 What does compilation do? A general idea would be generating an executable structure from SQL, but what exactly is happening under the hood? 40 ⚠ For illustrative purpose, only the relevant parts of the compilation process have been extracted and simplified. ？
 ？
 F✦SION
 dbt-core
 input
 execution
 compilation
 ？
 ？
 data
 SQL
 STEP
 ARTIFACT
 What’s happening?

41. Copyright stable, inc. All rights reserved.
 41 In a (very) nutshell:
42. Copyright stable, inc. All rights reserved.
 In a (very) nutshell: Given a SQL-like statement, F✦SION performs a series of checks to determine the validity. They are the basis of many F✦SION features. 42 F✦SION
 uses
 creates
 ⚠ For illustrative purpose, only the relevant parts of the compilation process have been extracted and simplified. check 2
 check 1
 input
 execution
 compilation
 data 
 structure 1
 data
 structure 2
 data
 SQL
 ARTIFACT
 STEP

43. Copyright stable, inc. All rights reserved.
 Overview of SQL Compilation Using a simple Snowflake SQL example as an example, let’s dive into each compilation step. 43 F✦SION
 select dateadd(‘day’, 1, ‘2025-01-01’) as new_day ⚠ For illustrative purpose, only the relevant parts of the compilation process have been extracted and simplified. semantic
 check
 syntax
 check
 input
 execution
 compilation
 STEP
 data 
 structure 1
 data
 structure 2
 data
 SQL
 ARTIFACT

44. Copyright stable, inc. All rights reserved.
 Overview of SQL Compilation In syntax check, the order of keywords is validated. As a deliverable, a tree structure is created so that SQL elements become searchable. 44 F✦SION
 select dateadd(‘day’, 1, ‘2025-01-01’) as new_day select as dateadd new_day ‘day’ 1 ‘2025-01-01’ e.g. the traversal select → as → new_day gives the “dateadd” alias
 ⚠ For illustrative purpose, only the relevant parts of the compilation process have been extracted and simplified. input
 execution
 compilation
 STEP
 abstract
 syntax tree
 logical
 plan
 physical plan
 data
 SQL
 ARTIFACT
 semantic
 check
 syntax
 check

45. Copyright stable, inc. All rights reserved.
 Overview of SQL Compilation In semantic check, things like column and function usage are validated. As a deliverable, a logical plan describing "what to do" is created. 45 F✦SION
 select dateadd(‘day’, 1, ‘2025-01-01’) as new_day Projection { expr = dateadd( ‘day’::date part, 1::number, ‘2025-01-01’::date ) schema = new_day::date } type info
 function definition
 ⚠ For illustrative purpose, only the relevant parts of the compilation process have been extracted and simplified. input
 execution
 compilation
 STEP
 semantic
 check
 syntax
 check
 abstract
 syntax tree
 logical
 plan
 physical plan
 data
 SQL
 ARTIFACT

46. Copyright stable, inc. All rights reserved.
 Overview of SQL Compilation During execution, F✦SION plans the optimal "how" based on the logical plan and retrieves the data. It can execute SQL just like a DWH does! 46 F✦SION
 select dateadd(‘day’, 1, ‘2025-01-01’) as new_day 2025-01-02T00:00:00 ⚠ For illustrative purpose, only the relevant parts of the compilation process have been extracted and simplified. input
 execution
 compilation
 STEP
 semantic
 check
 syntax
 check
 abstract
 syntax tree
 logical
 plan
 physical plan
 data
 SQL
 ARTIFACT

47. Copyright stable, inc. All rights reserved.
 47 So, the answer to “What does compilation do?” is..
48. Copyright stable, inc. All rights reserved.
 The answer to “What does compilation do?” Compilation checks the syntactic and semantic correctness and produces an execution plan. This process is called static analysis. 48 F✦SION
 static analysis
 ⊂
 ⚠ For illustrative purpose, only the relevant parts of the compilation process have been extracted and simplified. input
 execution
 compilation
 STEP
 semantic
 check
 syntax
 check
 abstract
 syntax tree
 logical
 plan
 physical plan
 data
 SQL
 ARTIFACT

49. Copyright stable, inc. All rights reserved.
 49 Given the power of static analysis, how can we understand the current features of F✦SION?
50. Copyright stable, inc. All rights reserved.
 50 Static Analysis Example 1/2 Column Reference Check
51. Copyright stable, inc. All rights reserved.
 b Static Analysis Example 1/2 To verify that a column exists, we can search for the column name in the AST of the source model. 51 ⚠ This is a conceptual example; the actual implementation may (quite) differ. select id organisation from …
 …
 with as select …
 organization b . source_b The column organization is not found in the select subtree.
 Do all the columns exist?
 TODO：エラー説明入りの 画像に差し替え 
 -- this does not exist
 this model
 source_b

52. Copyright stable, inc. All rights reserved.
 52 Static Analysis Example 2/2 Preview CTE
53. Copyright stable, inc. All rights reserved.
 Static Analysis Example 2/2 Preview CTE feature is achieved by replacing part of the AST to execute only the desired CTE. ⚠ This is a conceptual example; the actual implementation may (quite) differ. 53 with as source …
 as modify_1 …
 as modify_2 final …
 as …
 …
 with as source …
 as modify_1 …
 select * from modify_1 substitute a subtree after modify_1 with the select statement
 this model
 Preview CTE query

54. Copyright stable, inc. All rights reserved.
 54 Hope this helped connect the implementation details with the features!
55. Copyright stable, inc. All rights reserved.
 Re: The approach of F✦SION F✦SION contains the compilation and execution capabilities dbt-core relied on a DWH for, enabling static analysis and the Public Beta features. 55 F✦SION
 dbt-core
 ⚠ For illustrative purpose, only the relevant parts of the compilation process have been extracted and simplified. input
 execution
 compilation
 STEP
 semantic
 check
 syntax
 check
 static analysis
 abstract
 syntax tree
 logical
 plan
 physical plan
 data
 SQL
 ARTIFACT

56. Copyright stable, inc. All rights reserved.
 1. Introduction 2. Challenges of SQL/dbt development 3. dbt F✦SION engine 4. The approach of F✦SION 5. The future of F✦SION 6. What F✦SION has given us 56
57. Copyright stable, inc. All rights reserved.
 The future of F✦SION By applying the F✦SION’s execution step, what can we expect? 57 F✦SION
 Let’s speculate based on publicly available information ⚠ For illustrative purpose, only the relevant parts of the compilation process have been extracted and simplified. input
 execution
 compilation
 STEP
 semantic
 check
 syntax
 check
 abstract
 syntax tree
 logical
 plan
 physical plan
 data
 SQL
 ARTIFACT

58. Copyright stable, inc. All rights reserved.
 58 Future F✦SION’s possibilities 1/3 Local test execution
59. Copyright stable, inc. All rights reserved.
 Future F✦SION’s possibilities 1/3 — local test execution With a F✦SION-contained database, unit tests can run locally — no more DWH costs. No more credits, quality first development is here! 59 F✦SION
 Test execution is relied on DWH. The more tests run, the more credits cost Unit test can be run locally. dbt-core
 F✦SION

60. Copyright stable, inc. All rights reserved.
 Future F✦SION’s possibilities 1/3 — local test execution With a F✦SION-contained database, unit tests can run locally — no more DWH costs. No more credits, quality first development is here! 60 F✦SION
 Unit test can be run locally. Data tests can leverage Sample Mode to reduce cost after a single data pull. dbt-core
 F✦SION
 Test execution is relied on DWH. The more tests run, the more credits cost
61. Copyright stable, inc. All rights reserved.
 - ✅ Linting needs the DWH - ✅ Function-call validation needs the DWH - ✅ Column existence checks need the DWH - ✅ Running tests needs the DWH - … - ✅ Network latency for every single query - ✅ Peek at CTE data requires manually writing the query - ✅ Investigating a column’s origin requires manually reading the upstream model files - … Future F✦SION’s possibilities 1/3 — local test execution Now all the challenges are addressed! 61 it costs
 it’s slow

62. Copyright stable, inc. All rights reserved.
 62 Future F✦SION’s possibilities 2/3 SQL portability
63. Copyright stable, inc. All rights reserved.
 Future F✦SION’s possibilities 2/3 — SQL portability Free from dialect lock-in and choose the best execution engine per task. Write Once, Run Anywhere 🚀 63 F✦SION
 dbt-core
 F✦SION

64. Copyright stable, inc. All rights reserved.
 Future F✦SION’s possibilities 2/3 — SQL portability A naive syntax rewrite can’t preserve semantics 64 select * from person order by age asc select * from person order by age asc Yay same syntax!
 20 32 null 32 null 20 ≠ ???

65. Copyright stable, inc. All rights reserved.
 20 32 null Future F✦SION’s possibilities 2/3 — SQL portability SQL transpilation at execution plan level captures correct semantics! 65 *1 https://docs.rs/datafusion/latest/datafusion/logical_expr/struct.SortExpr.html select * from person order by age asc select * from person order by age asc nulls first 20 32 null = true true age F✦SION
 *1

66. Copyright stable, inc. All rights reserved.
 Future F✦SION’s possibilities 3/3 Truly free from vendor-lockin by Iceberg x F✦SION 66
67. Copyright stable, inc. All rights reserved.
 Future F✦SION’s possibilities 3/3 — Iceberg x F✦SION Without Iceberg, queries and data were tightly coupled in a single DWH — data migration was required to switch platforms. 67 w/o Iceberg tight
 query execution
 data

68. Copyright stable, inc. All rights reserved.
 Future F✦SION’s possibilities 3/3 — Iceberg x F✦SION Iceberg standardizes table formats, decoupling data from query execution. The data layer is free from vendor lock-in! 68 query execution
 data
 w/ Iceberg loose

69. Copyright stable, inc. All rights reserved.
 69 decouple query execution and data layer == free from vendor-lockin?
70. Copyright stable, inc. All rights reserved.
 Future F✦SION’s possibilities 3/3 — Iceberg x F✦SION Data layer is abstracted by Iceberg, but query ‘layer’ is not. So manual SQL rewrite is still necessary... 70 w/ Iceberg query
 tight
 loose
 query execution
 data

71. Copyright stable, inc. All rights reserved.
 Future F✦SION’s possibilities 3/3 — Iceberg x F✦SION F✦SION’s precise SQL transformations abstract both input and output, enabling true vendor-lock-in freedom! 🚀 71 w/ F✦SION F✦SION
 loose
 loose
 query
 query execution
 data

72. Copyright stable, inc. All rights reserved.
 Summary — the future of F✦SION Execution step has an exciting future! 72 - Local test execution
 
 - SQL portability — Write Once, Run Anywhere
 
 - Iceberg x F✦SION — truly free from vendor-lockin
 *Since the focus in this year would be the GA, these features (if realised) may arrive a bit later.

73. Copyright stable, inc. All rights reserved.
 1. Introduction 2. Challenges of SQL/dbt development 3. dbt F✦SION engine 4. The approach of F✦SION 5. The future of F✦SION 6. What F✦SION has given us 73
74. Copyright stable, inc. All rights reserved.
 Re: The approach of F✦SION F✦SION contains the compilation and execution capabilities dbt-core relied on a DWH for, enabling static analysis and the Public Beta features. 74 F✦SION
 dbt-core
 ⚠ For illustrative purpose, only the relevant parts of the compilation process have been extracted and simplified. input
 execution
 compilation
 STEP
 semantic
 check
 syntax
 check
 static analysis
 abstract
 syntax tree
 logical
 plan
 physical plan
 data
 SQL
 ARTIFACT
 Today’s presentation covered F✦SION’s technical approach, its current capabilities, and what lies ahead.
75. Copyright stable, inc. All rights reserved.
 75 In a nutshell, what has F✦SION given to SQL developers?
76. Copyright stable, inc. All rights reserved.
 76 That is ...
77. Copyright stable, inc. All rights reserved.
 What F✦SION has given us The fundamentals of modern software development! software development dbt-core dbt F✦SION engine Local code execution ❌ ✅ Local SQL execution IDE intellisense ❌ ✅ Intellisense Unit tests ⚠ Executes at DWH ✅ Free local test execution Write Once, Run Anywhere ❌ ✅ SQL portability 77
78. Copyright stable, inc. All rights reserved.
 Final Summary F✦SION is amazing! 78 - dbt-core depended on a DWH for SQL understanding
 
 - dbt F✦SION engine internalizes compile/execute and uses static analysis for dev support
 
 - Local testing and true vendor-lock-in freedom can be expected
 
 - The fundamentals of modern software development have finally come to SQL development!

79. Copyright stable, inc. All rights reserved.
 References 1. Jeremy Cohen and Joel Labes, Path to GA: How the dbt Fusion engine rolls out from beta to production, https://docs.getdbt.com/blog/dbt-fusion-engine-path-to-ga 2. Jason Ganz and Joel Labes, The Components of the dbt Fusion engine and how they fit together, https://docs.getdbt.com/blog/dbt-fusion-engine-components 3. Tristan Handy, Where we're headed with the dbt Fusion engine, https://www.getdbt.com/blog/where-we-re-headed-with-the-dbt-fusion-engine 4. Jason Ganz, Meet the dbt Fusion Engine: the new Rust-based, industrial-grade engine for dbt, https://docs.getdbt.com/blog/dbt-fusion-engine 5. Elias DeFaria and Azzam Aijazi, The future is now: Get to know the new dbt Fusion engine and VS Code Extension, https://www.getdbt.com/blog/get-to-know-the-new-dbt-fusion-engine-and-vs-code-extension 6. Joel Labes, The Three Levels of SQL Comprehension: What they are and why you need to know about them, https://docs.getdbt.com/blog/the-levels-of-sql-comprehension 7. Dave Connors, The key technologies behind SQL Comprehension, https://docs.getdbt.com/blog/sql-comprehension-technologies 8. dbt Labs, Accelerate data workflows with the dbt Fusion engine, https://www.getdbt.com/product/fusion 9. dbt Labs, About the dbt VS Code extension, https://docs.getdbt.com/docs/about-dbt-extension 10. dbt Labs, Quickstart for the dbt Fusion engine, https://docs.getdbt.com/guides/fusion 11. dbt Labs, About the dbt Fusion engine, https://docs.getdbt.com/docs/fusion/about-fusion 12. dbt Labs, dbt-fusion, https://github.com/dbt-labs/dbt-fusion 13. t-hiroto, 【Snowflake Summit 2025】Platform Keynoteまとめ速報, https://zenn.dev/finatext/articles/db766782b40e2c 14. Docs.rs, Apache DataFusion, https://docs.rs/datafusion/latest/datafusion/index.html 79