dbtͱBigQueryͰ࢝ΊΔ Data Vaultೖ໳ dbt Tokyo Meetup #3 Kazuki Taniguchi (@Kazk1018)

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/

ຊ೔͓࿩͢Δ͜ͱ • 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,FZT 6TFS VTFS*%PS&NBJM 1SPEVDU ੡଄ݩ ੡඼൪߸ 4IPQ ళฮ໊PSاۀ໊ ళฮ໊ Ex)

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

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

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

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

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

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

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

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

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

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

Data Vault @ 10X dbtͱ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Ͱ΋ϑϧεΫϥονͰ΋جຊ໰୊ͳ࣮͘૷Ͱ͖Δ • ARRAY΍STRUCTͷѻ͍ʹ஫ҙ͢Δ • Data VaultͰଟ༻͞ΕΔhash΍distinct͕௚઀ѻ͑ͳ͍ • (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