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

F2bd408c57c20505bd4202c9e28ecd59?s=128

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)

  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 $PMVNOOBNF %FTDSJQUJPO )BTILFZ \PCKFDU^@IBTILFZ %8)Ͱར༻͢ΔΩʔ

    ϏδωεΩʔ͔ΒϋογϡΛ༻͍ͯܭࢉ͢Δ -PBE%BUF5JNF4UBNQ MPBE@EUT %8)͕ॳΊͯϏδωεΦϒδΣΫτΛ ֬ೝͨ͠೔࣌ 3FDPSE4PVSDF 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 $PMVNOOBNF %FTDSJQUJPO )BTI%J f

    )BTIEJ f มߋ͞Ε͔ͨͲ͏͔Λൺֱ͢ΔͨΊͷϋογϡ஋ -PBE&OE%BUF5JNF4UBNQ 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