Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
dbtとBigQueryで始めるData Vault入門
Search
Kazuki Taniguchi
May 10, 2022
Programming
0
2.9k
dbtとBigQueryで始めるData Vault入門
dbt Tokyo Meeup #3の発表内容です
発表のアーカイブはこちらから
https://youtu.be/SYsiRFR2LGw
#dbt_tokyo
Kazuki Taniguchi
May 10, 2022
Tweet
Share
More Decks by Kazuki Taniguchi
See All by Kazuki Taniguchi
経済学者に知ってほしい機械学習 ~反事実モデルによる予測~ / JEA2020 tutorial CFML
kazk1018
3
2.2k
CFML関連のライブラリの紹介 / cfml #3 libraries
kazk1018
1
280
CFMLの概要と研究動向 / cfml #1 introduction
kazk1018
5
1k
Unsupervised Domain Adaptation by Backpropagation
kazk1018
1
430
Counterfactual Machine Learning 入門 / Introduction to Counterfactual ML
kazk1018
5
2.2k
【devsumi2017】人工知能の研究開発チームが プロダクト・組織をどのように変えたのか
kazk1018
8
3.6k
Other Decks in Programming
See All in Programming
After go func(): Goroutines Through a Beginner’s Eye
97vaibhav
0
230
CSC305 Lecture 03
javiergs
PRO
0
230
Pull-Requestの内容を1クリックで動作確認可能にするワークフロー
natmark
2
450
開発者への寄付をアプリ内課金として実装する時の気の使いどころ
ski
0
350
CSC305 Lecture 02
javiergs
PRO
1
260
どの様にAIエージェントと 協業すべきだったのか?
takefumiyoshii
2
600
詳しくない分野でのVibe Codingで困ったことと学び/vibe-coding-in-unfamiliar-area
shibayu36
3
4.4k
エンジニアとして高みを目指す、 利益を生み出す設計の考え方 / design-for-profit
minodriven
23
12k
LLMとPlaywright/reg-suitを活用した jQueryリファクタリングの実際
kinocoboy2
4
670
iOS 17で追加されたSubscriptionStoreView を利用して5分でサブスク実装チャレンジ
natmark
0
590
iOSアプリの信頼性を向上させる取り組み/ios-app-improve-reliability
shino8rayu9
0
150
iOSエンジニア向けの英語学習アプリを作る!
yukawashouhei
0
180
Featured
See All Featured
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
45
2.5k
We Have a Design System, Now What?
morganepeng
53
7.8k
Java REST API Framework Comparison - PWX 2021
mraible
33
8.8k
The Art of Delivering Value - GDevCon NA Keynote
reverentgeek
15
1.7k
How To Stay Up To Date on Web Technology
chriscoyier
791
250k
Raft: Consensus for Rubyists
vanstee
139
7.1k
A Modern Web Designer's Workflow
chriscoyier
697
190k
Embracing the Ebb and Flow
colly
88
4.8k
Measuring & Analyzing Core Web Vitals
bluesmoon
9
610
Making the Leap to Tech Lead
cromwellryan
135
9.5k
Build your cross-platform service in a week with App Engine
jlugia
232
18k
How STYLIGHT went responsive
nonsquared
100
5.8k
Transcript
dbtͱBigQueryͰ࢝ΊΔ Data Vaultೖ dbt Tokyo Meetup #3 Kazuki Taniguchi (@Kazk1018)
Introduction • Kazuki Taniguchi (@Kazk1018) • SWE(Data) @ 10X, Inc
• Careers • Data Scientist @ CyberAgent, Inc • Co-founder @ the Babels, Inc • CEO @ ExpData, LLC https:/kazk1018.github.io/
ຊ͓͢Δ͜ͱ • Stailerͷհ • Stailerʹ͓͚ΔσʔλϞσϦϯάͷ՝ • Data Vaultʹ͍ͭͯ • dbtͱBigQueryΛ༻͍ͨData
Vaultʹ͍ͭͯ
Stailer খചࣄۀऀͷσδλϧԽΛ࣮ݱ͢Δͯ͢ͷγεςϜΛϓϥοτϑΥʔ Ϝͱͯ͠ఏڙ ͓٬༷͚ খചࣄۀऀ͚ ૹۀऀ͚
Our Issues খചࣄۀऀຖʹҟͳΔෳͷγεςϜͷσʔλΛ࿈ܞ͢ΔͨΊʹσʔλ ιʔεͷଟ༷ੑ͕ߴ͍ ใ ൢଅ ૹใ ࡏݿใ ձһใ 4UBJMFS%BUB-BLF
Our Issues খചࣄۀऀຖʹҟͳΔෳͷγεςϜͷσʔλΛ࿈ܞ͢ΔͨΊʹσʔλ ιʔεͷଟ༷ੑ͕ߴ͍ ใ ใ 4UBJMFS%BUB-BLF খചࣄۀऀA খചࣄۀऀB ҟͳΔϑΥʔϚοτ
Data Vault • σʔλΣΞϋεʹ͓͚ΔσʔλϞσϦϯάख๏ͷҰͭͰ2000 ʹDaniel (Dan) LinstedtʹΑͬͯఏҊ͞Εͨ • 2014ʹఏҊऀͷϒϩάͰData Vault
2.0͕հ͞Εͨ (ຊൃදͰData Vault 2.0ʹج͍ͮͯઆ໌͠·͢)
Business Objects ӦۀੳऀͷϏδωεϢʔβʔ͕ར༻͢ΔΦϒδΣΫτΛϢχʔΫ ʹಛఆͰ͖ΔϏδωεΩʔΛઃܭ͢Δඞཁ͕͋Δ 0CKFDU #VTJOFTT,FZT 6TFS VTFS*%PS&NBJM 1SPEVDU ݩ
൪߸ 4IPQ ళฮ໊PSاۀ໊ ళฮ໊ Ex)
Data Vaultʹ͓͍ͯγεςϜ͕ੜ͢ΔओͳΧϥϜ System Fields 'JFMET $PMVNOOBNF %FTDSJQUJPO )BTILFZ \PCKFDU^@IBTILFZ %8)Ͱར༻͢ΔΩʔ
ϏδωεΩʔ͔ΒϋογϡΛ༻͍ͯܭࢉ͢Δ -PBE%BUF5JNF4UBNQ MPBE@EUT %8)͕ॳΊͯϏδωεΦϒδΣΫτΛ ֬ೝͨ࣌͠ 3FDPSE4PVSDF SFDPSE@TPVSDF ֨ೲ͞Εͨσʔλͷσʔλιʔε໊
Example: e-Commerce )VC6TFS )VC4IPQ )VC1SPEVDU -JOL0SEFS 4BU0SEFS 4BU6TFS 4BU1SPEVDU )VC
-JOL 4BUFMMJUF
Hub ֤ϏδωεΦϒδΣΫτͷϏδωεΩʔΛอ࣋͢Δςʔϒϧ )VC6TFS VTFS@IBTILFZ VTFS@JE MPBE@EUT SFDPSE@TPVSDF )VC4IPQ TIPQ@IBTILFZ OBNF
MPBE@EUT SFDPSE@TPVSDF )VC1SPEVDU QSPEVDU@IBTILFZ QSPEVDU@OVNCFS MPBE@EUT SFDPSE@TPVSDF
ෳͷϏδωεΦϒδΣΫτͷؔΛอ࣋͢Δςʔϒϧ -JOL0SEFS VTFS@IBTILFZ QSPEVDU@IBTILFZ TIPQ@LFZ MPBE@EUT SFDPSE@TPVSDF Link
Satellite HubLinkΛઆ໌͢ΔͨΊͷɺ͓ΑͼͦͷཤྺΛอ࣋͢Δςʔϒϧ 4BU6TFS VTFS@IBTILFZ fi STU@OBNF MBTU@OBNF MPBE@EUT SFDPSE@TPVSDF 4BU1SPEVDU
QSPEVDU@IBTILFZ OBNF QSJDF MPBE@EUT SFDPSE@TPVSDF 4BU0SEFS PSEFS@IBTILFZ BNPVOU TIJQQJOH@EBUF PSEFS@EBUF MPBE@EUT SFDPSE@TPVSDF
Satellite ͷཤྺΛอ࣋͢Δ(SCD type2)ׂ͕͋ΔͷͰඞཁʹԠͯ࣍͡ͷ System FieldsΛར༻͢Δ 'JFMET $PMVNOOBNF %FTDSJQUJPO )BTI%J f
)BTIEJ f มߋ͞Ε͔ͨͲ͏͔Λൺֱ͢ΔͨΊͷϋογϡ -PBE&OE%BUF5JNF4UBNQ MPBE@FOE@EUT 1,ຖʹ৽͍͕͠ೖ͖ͬͯͨͱ͖ͷ࣌ ಉ͡1,Ͱ࠷৽ͷߦʹ/6--͕ೖ͍ͬͯΔ
Example: e-Commerce )VC6TFS )VC4IPQ )VC1SPEVDU -JOL0SEFS 4BU0SEFS 4BU6TFS 4BU1SPEVDU )VC
-JOL 4BUFMMJUF
Data Vault Pros • ༷ʑͳσʔλιʔε͕૿͍͑ͯ͘߹Ͱ࠷খݶͷมߋͰ࣮͢Δ͜ ͱ͕ՄೳͰ͋Δ • σʔλؒͷ͕ؔมߋ͞Εͯ༰қʹมߋ͕ՄೳͰ͋Δ • DWHʹ͓͍ͯσʔλιʔεͷ͕ՄೳͰ͋Δ
Data Vault Pros )VC6TFS 4BU6TFS )VC -JOL 4BUFMMJUF 4BU$3. ҟͳΔσʔλιʔεΛՃ͢Δ߹SatelliteΛՃ͢Δ͚ͩͰྑ͍
Data Vault Cons • ຊޠͷใ͕গͳ͍ͷͰӳޠΛಡΊΔඞཁ͕͋Δ • ଞͷσʔλϞσϦϯάʹൺͯൣғ͕͍͜ͱ͋Δ͕ɺߏஙͷͨΊ ʹඞཁͱ͢Δ͕ࣝଟ͍ (ຊൃදͰհͰ͖͍ͯΔ༰جຊతͳ෦ ͚ͩͰ͢)
Data Vault @ 10X dbtͱBigQueryΛ༻͍ͯData VaultΛݕূ͍ͯ͠Δ BigQuery dbt BigQuery
• dbtvault • (ৄࡉޙͷൃදΛ͝ௌߨ͍ͩ͘͞) • ࠓճͷݕূͰௐࠪ·ͰͰ࣮ࡍʹར༻͍ͯ͠·ͤΜ Data Vault using dbt
with BigQuery
Data Vault using dbt with BigQuery • dbtͰͷϑϧεΫϥον • dbtvault͕͋ΔΑ͏ʹςϯϓϨʔτͰSQLΛੜͰ͖Δdbt૬ੑ
͕ඇৗʹྑ͍ • MaterializationͷIncrementalΛ༻͍࣮ͯ͢Δ͜ͱ͕Ͱ͖Δ • (Incrementalʹ͍ͭͯޙͷൃදΛ͝ௌߨ͍ͩ͘͞)
Data Vault using dbt with BigQuery • dbtvaultͰϑϧεΫϥονͰجຊͳ࣮͘Ͱ͖Δ • ARRAYSTRUCTͷѻ͍ʹҙ͢Δ
• Data VaultͰଟ༻͞ΕΔhashdistinct͕ѻ͑ͳ͍ • (dbt snapshotͱ༷ͷͱͯ͠ಉ͡)
(ߋʹৄ͍͠ઃܭৄࡉʹ͍ͭͯԼهͷຊΛࢀߟʹ͍ͯͩ͘͠͞) More Information about Data Vault
Summary • 10Xʹ͓͚ΔDWHߏஙͷ՝ • Data Vaultͷجຊతͳ֓೦ • dbtͱBigQueryΛ༻͍ͨData Vault
References • Books • Building a Scalable Data Warehouse with
Data Vault 2.0 • Articles • A short intro to #datavault 2.0