Upgrade to Pro — share decks privately, control downloads, hide ads and more …

dbtとBigQueryで始めるData Vault入門

dbtとBigQueryで始めるData Vault入門

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

https://youtu.be/SYsiRFR2LGw

#dbt_tokyo

Kazuki Taniguchi

May 10, 2022
Tweet

More Decks by Kazuki Taniguchi

Other Decks in Programming

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 [email protected]
    %8)͕ॳΊͯϏδωεΦϒδΣΫτΛ
    ֬ೝͨ͠೔࣌
    3FDPSE4PVSDF [email protected] ֨ೲ͞Εͨσʔλͷσʔλιʔε໊

    View Slide

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

    View Slide

  11. View Slide

  12. ෳ਺ͷϏδωεΦϒδΣΫτͷؔ܎Λอ࣋͢Δςʔϒϧ
    -JOL0SEFS
    [email protected]
    [email protected]
    [email protected]
    [email protected]
    [email protected]
    Link

    View Slide

  13. View Slide

  14. Satellite
    ஋ͷཤྺΛอ࣋͢Δ(SCD type2)໾ׂ͕͋ΔͷͰඞཁʹԠͯ࣍͡ͷ
    System FieldsΛར༻͢Δ
    'JFMET $PMVNOOBNF %FTDSJQUJPO
    )BTI%J
    f
    )BTIEJ
    f
    มߋ͞Ε͔ͨͲ͏͔Λൺֱ͢ΔͨΊͷϋογϡ஋
    -PBE&OE%BUF5JNF4UBNQ [email protected]@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