dbtとBigQueryで始めるData Vault入門

dbt Tokyo Meeup #3の発表内容です
発表のアーカイブはこちらから

https://youtu.be/SYsiRFR2LGw

#dbt_tokyo

Transcript

1. dbtͱBigQueryͰ࢝ΊΔ Data Vaultೖ໳ dbt Tokyo Meetup #3 Kazuki Taniguchi (@Kazk1018)
2. 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/
3. ຊ೔͓࿩͢Δ͜ͱ • Stailerͷ঺հ • Stailerʹ͓͚ΔσʔλϞσϦϯάͷ՝୊ • Data Vaultʹ͍ͭͯ • dbtͱBigQueryΛ༻͍ͨData Vaultʹ͍ͭͯ
4. Stailer খചࣄۀऀͷσδλϧԽΛ࣮ݱ͢Δ͢΂ͯͷγεςϜΛϓϥοτϑΥʔ Ϝͱͯ͠ఏڙ 
 ͓٬༷޲͚ খചࣄۀऀ޲͚ ഑ૹۀऀ޲͚
5. Our Issues খചࣄۀऀຖʹҟͳΔෳ਺ͷγεςϜͷσʔλΛ࿈ܞ͢ΔͨΊʹσʔλ ιʔεͷଟ༷ੑ͕ߴ͍ ঎඼৘ใ ൢଅ ഑ૹ৘ใ ࡏݿ৘ใ ձһ৘ใ 4UBJMFS
   
   %BUB
   -BLF
6. Our Issues খചࣄۀऀຖʹҟͳΔෳ਺ͷγεςϜͷσʔλΛ࿈ܞ͢ΔͨΊʹσʔλ ιʔεͷଟ༷ੑ͕ߴ͍ ঎඼৘ใ ঎඼৘ใ 4UBJMFS
   
   %BUB
   -BLF খചࣄۀऀA খചࣄۀऀB ҟͳΔϑΥʔϚοτ
7. Data Vault • σʔλ΢ΣΞϋ΢εʹ͓͚ΔσʔλϞσϦϯάख๏ͷҰͭͰ2000೥ ʹDaniel (Dan) LinstedtʹΑͬͯఏҊ͞Εͨ • 2014೥ʹఏҊऀͷϒϩάͰData Vault 2.0͕঺հ͞Εͨ 
 (ຊൃදͰ͸Data Vault 2.0ʹج͍ͮͯઆ໌͠·͢)
8. Business Objects Ӧۀ΍෼ੳऀ౳ͷϏδωεϢʔβʔ͕ར༻͢ΔΦϒδΣΫτΛϢχʔΫ ʹಛఆͰ͖ΔϏδωεΩʔΛઃܭ͢Δඞཁ͕͋Δ 0CKFDU #VTJOFTT
   ,FZT 6TFS VTFS
   *%
   PS
   &NBJM 1SPEVDU ੡଄ݩ
   ੡඼൪߸ 4IPQ ళฮ໊
   PS
   اۀ໊
   ళฮ໊ Ex)
9. Data Vaultʹ͓͍ͯγεςϜ͕ੜ੒͢ΔओͳΧϥϜ System Fields 'JFMET $PMVNO
   OBNF %FTDSJQUJPO )BTILFZ \PCKFDU^@IBTILFZ %8)Ͱར༻͢ΔΩʔ
   ϏδωεΩʔ͔ΒϋογϡΛ༻͍ͯܭࢉ͢Δ -PBE
   %BUF5JNF
   4UBNQ MPBE@EUT %8)͕ॳΊͯϏδωεΦϒδΣΫτΛ
   ֬ೝͨ͠೔࣌ 3FDPSE
   4PVSDF SFDPSE@TPVSDF ֨ೲ͞Εͨσʔλͷσʔλιʔε໊
10. Example: e-Commerce )VC6TFS )VC4IPQ )VC1SPEVDU -JOL0SEFS 4BU0SEFS 4BU6TFS 4BU1SPEVDU )VC -JOL 4BUFMMJUF
11. 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
12. ෳ਺ͷϏδωεΦϒδΣΫτͷؔ܎Λอ࣋͢Δςʔϒϧ -JOL0SEFS VTFS@IBTILFZ QSPEVDU@IBTILFZ TIPQ@LFZ MPBE@EUT SFDPSE@TPVSDF Link
13. Satellite Hub΍LinkΛઆ໌͢ΔͨΊͷ஋ɺ͓ΑͼͦͷཤྺΛอ࣋͢Δςʔϒϧ 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
14. Satellite ஋ͷཤྺΛอ࣋͢Δ(SCD type2)໾ׂ͕͋ΔͷͰඞཁʹԠͯ࣍͡ͷ System FieldsΛར༻͢Δ 'JFMET $PMVNO
    OBNF %FTDSJQUJPO )BTI
    %J f )BTIEJ f มߋ͞Ε͔ͨͲ͏͔Λൺֱ͢ΔͨΊͷϋογϡ஋ -PBE
    &OE
    %BUF5JNF
    4UBNQ MPBE@FOE@EUT 1,ຖʹ৽͍͠஋͕ೖ͖ͬͯͨͱ͖ͷ೔࣌
    ಉ͡1,Ͱ࠷৽ͷߦʹ͸/6--͕ೖ͍ͬͯΔ
15. Example: e-Commerce )VC6TFS )VC4IPQ )VC1SPEVDU -JOL0SEFS 4BU0SEFS 4BU6TFS 4BU1SPEVDU )VC -JOL 4BUFMMJUF
16. Data Vault Pros • ༷ʑͳσʔλιʔε͕૿͍͑ͯ͘৔߹Ͱ΋࠷খݶͷมߋͰ࣮૷͢Δ͜ ͱ͕ՄೳͰ͋Δ • σʔλؒͷؔ܎͕มߋ͞Εͯ΋༰қʹมߋ͕ՄೳͰ͋Δ • DWH಺ʹ͓͍ͯσʔλιʔεͷ௥੻͕ՄೳͰ͋Δ
17. Data Vault Pros )VC6TFS 4BU6TFS )VC -JOL 4BUFMMJUF 4BU$3. ҟͳΔσʔλιʔεΛ௥Ճ͢Δ৔߹͸SatelliteΛ௥Ճ͢Δ͚ͩͰྑ͍
18. Data Vault Cons • ೔ຊޠͷ৘ใ͕গͳ͍ͷͰӳޠΛಡΊΔඞཁ͕͋Δ • ଞͷσʔλϞσϦϯάʹൺ΂ͯൣғ͕޿͍͜ͱ΋͋Δ͕ɺߏஙͷͨΊ ʹඞཁͱ͢Δ஌͕ࣝଟ͍ (ຊൃදͰ঺հͰ͖͍ͯΔ಺༰͸جຊతͳ෦ ෼͚ͩͰ͢)
19. Data Vault @ 10X dbtͱBigQueryΛ༻͍ͯData VaultΛݕূ͍ͯ͠Δ BigQuery dbt BigQuery
20. • dbtvault • (ৄࡉ͸ޙͷൃදΛ͝ௌߨ͍ͩ͘͞) • ࠓճͷݕূͰ͸ௐࠪ·ͰͰ࣮ࡍʹ͸ར༻͍ͯ͠·ͤΜ Data Vault using dbt with BigQuery
21. Data Vault using dbt with BigQuery • dbtͰͷϑϧεΫϥον • dbtvault͕͋ΔΑ͏ʹςϯϓϨʔτͰSQLΛੜ੒Ͱ͖Δdbt͸૬ੑ ͕ඇৗʹྑ͍ • MaterializationͷIncrementalΛ༻͍࣮ͯ૷͢Δ͜ͱ͕Ͱ͖Δ • (Incrementalʹ͍ͭͯ΋ޙͷൃදΛ͝ௌߨ͍ͩ͘͞)
22. Data Vault using dbt with BigQuery • dbtvaultͰ΋ϑϧεΫϥονͰ΋جຊ໰୊ͳ࣮͘૷Ͱ͖Δ • ARRAY΍STRUCTͷѻ͍ʹ஫ҙ͢Δ • Data VaultͰଟ༻͞ΕΔhash΍distinct͕௚઀ѻ͑ͳ͍ • (dbt snapshotͱ࢓༷ͷ໰୊ͱͯ͠͸ಉ͡)
23. (ߋʹৄ͍͠ઃܭ΍ৄࡉʹ͍ͭͯ͸ԼهͷຊΛࢀߟʹ͍ͯͩ͘͠͞) More Information about Data Vault
24. Summary • 10Xʹ͓͚ΔDWHߏஙͷ՝୊ • Data Vaultͷجຊతͳ֓೦ • dbtͱBigQueryΛ༻͍ͨData Vault
25. References • Books • Building a Scalable Data Warehouse with Data Vault 2.0 • Articles • A short intro to #datavault 2.0