dbt Tokyo Meeup #3の発表内容です 発表のアーカイブはこちらから
https://youtu.be/SYsiRFR2LGw
#dbt_tokyo
dbtͱBigQueryͰ࢝ΊΔData Vaultೖdbt Tokyo Meetup #3Kazuki Taniguchi (@Kazk1018)
View Slide
Introduction• Kazuki Taniguchi (@Kazk1018)• SWE(Data) @ 10X, Inc• Careers• Data Scientist @ CyberAgent, Inc• Co-founder @ the Babels, Inc• CEO @ ExpData, LLChttps:/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,FZT6TFS VTFS*%PS&NBJM1SPEVDU ݩ ൪߸4IPQ ళฮ໊PSاۀ໊ ళฮ໊Ex)
Data Vaultʹ͓͍ͯγεςϜ͕ੜ͢ΔओͳΧϥϜSystem Fields'JFMET $PMVNOOBNF %FTDSJQUJPO)BTILFZ \PCKFDU^@IBTILFZ%8)Ͱར༻͢ΔΩʔϏδωεΩʔ͔ΒϋογϡΛ༻͍ͯܭࢉ͢Δ-PBE%BUF5JNF4UBNQ [email protected]%8)͕ॳΊͯϏδωεΦϒδΣΫτΛ֬ೝͨ࣌͠3FDPSE4PVSDF [email protected] ֨ೲ͞Εͨσʔλͷσʔλιʔε໊
Example: e-Commerce)VC6TFS)VC4IPQ )VC1SPEVDU-JOL0SEFS 4BU0SEFS4BU6TFS4BU1SPEVDU)VC-JOL4BUFMMJUF
Hub֤ϏδωεΦϒδΣΫτͷϏδωεΩʔΛอ࣋͢Δςʔϒϧ)VC6TFS[email protected][email protected][email protected][email protected])VC4IPQ[email protected]OBNF[email protected][email protected])VC1SPEVDU[email protected][email protected][email protected][email protected]
ෳͷϏδωεΦϒδΣΫτͷؔΛอ࣋͢Δςʔϒϧ-JOL0SEFS[email protected][email protected][email protected][email protected][email protected]Link
SatelliteHubLinkΛઆ໌͢ΔͨΊͷɺ͓ΑͼͦͷཤྺΛอ࣋͢Δςʔϒϧ4BU6TFS[email protected]fi[email protected][email protected][email protected][email protected]4BU1SPEVDU[email protected]OBNFQSJDF[email protected][email protected]4BU0SEFS[email protected]BNPVOU[email protected][email protected][email protected][email protected]
SatelliteͷཤྺΛอ࣋͢Δ(SCD type2)ׂ͕͋ΔͷͰඞཁʹԠͯ࣍͡ͷSystem FieldsΛར༻͢Δ'JFMET $PMVNOOBNF %FTDSJQUJPO)BTI%Jf)BTIEJfมߋ͞Ε͔ͨͲ͏͔Λൺֱ͢ΔͨΊͷϋογϡ-PBE&OE%BUF5JNF4UBNQ [email protected]@EUT1,ຖʹ৽͍͕͠ೖ͖ͬͯͨͱ͖ͷ࣌ಉ͡1,Ͱ࠷৽ͷߦʹ/6--͕ೖ͍ͬͯΔ
Data Vault Pros• ༷ʑͳσʔλιʔε͕૿͍͑ͯ͘߹Ͱ࠷খݶͷมߋͰ࣮͢Δ͜ͱ͕ՄೳͰ͋Δ• σʔλؒͷ͕ؔมߋ͞Εͯ༰қʹมߋ͕ՄೳͰ͋Δ• DWHʹ͓͍ͯσʔλιʔεͷ͕ՄೳͰ͋Δ
Data Vault Pros)VC6TFS 4BU6TFS)VC-JOL4BUFMMJUF4BU$3.ҟͳΔσʔλιʔεΛՃ͢Δ߹SatelliteΛՃ͢Δ͚ͩͰྑ͍
Data Vault Cons• ຊޠͷใ͕গͳ͍ͷͰӳޠΛಡΊΔඞཁ͕͋Δ• ଞͷσʔλϞσϦϯάʹൺͯൣғ͕͍͜ͱ͋Δ͕ɺߏஙͷͨΊʹඞཁͱ͢Δ͕ࣝଟ͍ (ຊൃදͰհͰ͖͍ͯΔ༰جຊతͳ෦͚ͩͰ͢)
Data Vault @ 10Xdbtͱ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