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)
   

   View Slide

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/
   

   View Slide

3. ຊ೔͓࿩͢Δ͜ͱ
   • Stailerͷ঺հ
   
   
   • Stailerʹ͓͚ΔσʔλϞσϦϯάͷ՝୊
   
   
   • Data Vaultʹ͍ͭͯ
   
   
   • dbtͱBigQueryΛ༻͍ͨData Vaultʹ͍ͭͯ
   

   View Slide

4. Stailer
   খചࣄۀऀͷσδλϧԽΛ࣮ݱ͢Δ͢΂ͯͷγεςϜΛϓϥοτϑΥʔ
   Ϝͱͯ͠ఏڙ
   

   ͓٬༷޲͚ খചࣄۀऀ޲͚ ഑ૹۀऀ޲͚
   

   View Slide

5. Our Issues
   খചࣄۀऀຖʹҟͳΔෳ਺ͷγεςϜͷσʔλΛ࿈ܞ͢ΔͨΊʹσʔλ
   ιʔεͷଟ༷ੑ͕ߴ͍
   ঎඼৘ใ ൢଅ ഑ૹ৘ใ ࡏݿ৘ใ
   ձһ৘ใ
   4UBJMFS%BUB-BLF
   

   View Slide

6. Our Issues
   খചࣄۀऀຖʹҟͳΔෳ਺ͷγεςϜͷσʔλΛ࿈ܞ͢ΔͨΊʹσʔλ
   ιʔεͷଟ༷ੑ͕ߴ͍
   ঎඼৘ใ ঎඼৘ใ
   4UBJMFS%BUB-BLF
   খചࣄۀऀA খചࣄۀऀB
   ҟͳΔϑΥʔϚοτ
   

   View Slide

7. Data Vault
   • σʔλ΢ΣΞϋ΢εʹ͓͚ΔσʔλϞσϦϯάख๏ͷҰͭͰ2000೥
   ʹDaniel (Dan) LinstedtʹΑͬͯఏҊ͞Εͨ
   
   
   • 2014೥ʹఏҊऀͷϒϩάͰData Vault 2.0͕঺հ͞Εͨ
   

   (ຊൃදͰ͸Data Vault 2.0ʹج͍ͮͯઆ໌͠·͢)
   

   View Slide

8. Business Objects
   Ӧۀ΍෼ੳऀ౳ͷϏδωεϢʔβʔ͕ར༻͢ΔΦϒδΣΫτΛϢχʔΫ
   ʹಛఆͰ͖ΔϏδωεΩʔΛઃܭ͢Δඞཁ͕͋Δ
   0CKFDU #VTJOFTT,FZT
   6TFS VTFS*%PS&NBJM
   1SPEVDU ੡଄ݩ ੡඼൪߸
   4IPQ ళฮ໊PSاۀ໊ ళฮ໊
   Ex)
   

   View Slide

9. Data Vaultʹ͓͍ͯγεςϜ͕ੜ੒͢ΔओͳΧϥϜ
   System Fields
   'JFMET $PMVNOOBNF %FTDSJQUJPO
   )BTILFZ \PCKFDU^@IBTILFZ
   %8)Ͱར༻͢ΔΩʔ
   ϏδωεΩʔ͔ΒϋογϡΛ༻͍ͯܭࢉ͢Δ
   -PBE%BUF5JNF4UBNQ MPBE@EUT
   %8)͕ॳΊͯϏδωεΦϒδΣΫτΛ
   ֬ೝͨ͠೔࣌
   3FDPSE4PVSDF SFDPSE@TPVSDF ֨ೲ͞Εͨσʔλͷσʔλιʔε໊
   

   View Slide

10. Example: e-Commerce
    )VC6TFS
    )VC4IPQ )VC1SPEVDU
    -JOL0SEFS 4BU0SEFS
    4BU6TFS
    4BU1SPEVDU
    )VC
    -JOL
    4BUFMMJUF
    

    View Slide

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
    

    View Slide

12. ෳ਺ͷϏδωεΦϒδΣΫτͷؔ܎Λอ࣋͢Δςʔϒϧ
    -JOL0SEFS
    VTFS@IBTILFZ
    QSPEVDU@IBTILFZ
    TIPQ@LFZ
    MPBE@EUT
    SFDPSE@TPVSDF
    Link
    

    View Slide

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
    

    View Slide

14. Satellite
    ஋ͷཤྺΛอ࣋͢Δ(SCD type2)໾ׂ͕͋ΔͷͰඞཁʹԠͯ࣍͡ͷ
    System FieldsΛར༻͢Δ
    'JFMET $PMVNOOBNF %FTDSJQUJPO
    )BTI%J
    f
    )BTIEJ
    f
    มߋ͞Ε͔ͨͲ͏͔Λൺֱ͢ΔͨΊͷϋογϡ஋
    -PBE&OE%BUF5JNF4UBNQ MPBE@FOE@EUT
    1,ຖʹ৽͍͠஋͕ೖ͖ͬͯͨͱ͖ͷ೔࣌
    ಉ͡1,Ͱ࠷৽ͷߦʹ͸/6--͕ೖ͍ͬͯΔ
    

    View Slide

15. Example: e-Commerce
    )VC6TFS
    )VC4IPQ )VC1SPEVDU
    -JOL0SEFS 4BU0SEFS
    4BU6TFS
    4BU1SPEVDU
    )VC
    -JOL
    4BUFMMJUF
    

    View Slide

16. Data Vault Pros
    • ༷ʑͳσʔλιʔε͕૿͍͑ͯ͘৔߹Ͱ΋࠷খݶͷมߋͰ࣮૷͢Δ͜
    ͱ͕ՄೳͰ͋Δ
    
    
    • σʔλؒͷؔ܎͕มߋ͞Εͯ΋༰қʹมߋ͕ՄೳͰ͋Δ
    
    
    • DWH಺ʹ͓͍ͯσʔλιʔεͷ௥੻͕ՄೳͰ͋Δ
    

    View Slide

17. Data Vault Pros
    )VC6TFS 4BU6TFS
    )VC
    -JOL
    4BUFMMJUF
    4BU$3.
    ҟͳΔσʔλιʔεΛ௥Ճ͢Δ৔߹͸SatelliteΛ௥Ճ͢Δ͚ͩͰྑ͍
    

    View Slide

18. Data Vault Cons
    • ೔ຊޠͷ৘ใ͕গͳ͍ͷͰӳޠΛಡΊΔඞཁ͕͋Δ
    
    
    • ଞͷσʔλϞσϦϯάʹൺ΂ͯൣғ͕޿͍͜ͱ΋͋Δ͕ɺߏஙͷͨΊ
    ʹඞཁͱ͢Δ஌͕ࣝଟ͍ (ຊൃදͰ঺հͰ͖͍ͯΔ಺༰͸جຊతͳ෦
    ෼͚ͩͰ͢)
    

    View Slide

19. Data Vault @ 10X
    dbtͱBigQueryΛ༻͍ͯData VaultΛݕূ͍ͯ͠Δ
    BigQuery dbt BigQuery
    

    View Slide

20. • dbtvault
    
    
    • (ৄࡉ͸ޙͷൃදΛ͝ௌߨ͍ͩ͘͞)
    
    
    • ࠓճͷݕূͰ͸ௐࠪ·ͰͰ࣮ࡍʹ͸ར༻͍ͯ͠·ͤΜ
    Data Vault using dbt with BigQuery
    

    View Slide

21. Data Vault using dbt with BigQuery
    • dbtͰͷϑϧεΫϥον
    
    
    • dbtvault͕͋ΔΑ͏ʹςϯϓϨʔτͰSQLΛੜ੒Ͱ͖Δdbt͸૬ੑ
    ͕ඇৗʹྑ͍
    
    
    • MaterializationͷIncrementalΛ༻͍࣮ͯ૷͢Δ͜ͱ͕Ͱ͖Δ
    
    
    • (Incrementalʹ͍ͭͯ΋ޙͷൃදΛ͝ௌߨ͍ͩ͘͞)
    

    View Slide

22. Data Vault using dbt with BigQuery
    • dbtvaultͰ΋ϑϧεΫϥονͰ΋جຊ໰୊ͳ࣮͘૷Ͱ͖Δ
    
    
    • ARRAY΍STRUCTͷѻ͍ʹ஫ҙ͢Δ
    
    
    • Data VaultͰଟ༻͞ΕΔhash΍distinct͕௚઀ѻ͑ͳ͍
    
    
    • (dbt snapshotͱ࢓༷ͷ໰୊ͱͯ͠͸ಉ͡)
    

    View Slide

23. (ߋʹৄ͍͠ઃܭ΍ৄࡉʹ͍ͭͯ͸ԼهͷຊΛࢀߟʹ͍ͯͩ͘͠͞)
    More Information about Data Vault
    

    View Slide

24. Summary
    • 10Xʹ͓͚ΔDWHߏஙͷ՝୊
    
    
    • Data Vaultͷجຊతͳ֓೦
    
    
    • dbtͱBigQueryΛ༻͍ͨData Vault
    

    View Slide

25. References
    • Books
    
    
    • Building a Scalable Data Warehouse with Data Vault 2.0
    
    
    • Articles
    
    
    • A short intro to #datavault 2.0
    

    View Slide