Data Vault Automation at
de Bijenkorf
PRESENTED BY
ROB WINTERS
ANDREI SCORUS
Presentation agenda
◦ Project objec*ves ◦ Architectural overview ◦ The data warehouse data model ◦ Automa*on in the data warehouse ◦ Successes and failures ◦ Conclusions About the presenters
 Rob Winters Andrei Scorus  Head of Data Technology, the Bijenkorf  BI Consultant, Incentro  Project role:  Project role: ◦ Project Lead ◦ Main ETL Developer ◦  Systems architect and administrator ◦  ETL Developer ◦  Data modeler ◦  Modeling support ◦  Source system expert ◦  Developer (ETL, predic;ve models, reports) ◦  Stakeholder manager ◦ Joined project September 2014 ◦ Joined project November 2014 Project objectives
Technical Requirements ◦  Informa*on requirements ◦  Have one place as the source for all reports ◦  Security and privacy ◦  Informa*on management ◦  Integrate with produc*on •  One environment to quickly generate customer insights •  Then feed those insights back to produc;on ◦ Non-‐func*onal requirements ◦  System quality ◦  Extensibility ◦  Scalability ◦  Maintainability ◦  Security ◦  Flexibility ◦  Low Cost •  Then measure the impact of those changes in near real ;m
Source system landscape
Source Type Number of Sources Examples Load Frequency Data Structure 2x/hour Par;al 3NF Oracle DB 2 Virgo ERP MySQL 3 Product DB, Web 10x/hour Orders, DWH 3NF (Web Orders), Improperly normalized Event bus 1 Web/email events 1x/minute Tab delimited with JSON ﬁelds Webhook 1 Transac;onal Emails 1x/minute JSON REST APIs 5+ GA, DotMailer 1x/hour-‐1x/day JSON SOAP APIs 5+ AdWords, Pricing 1x/day XML Architectural overview
 Tools   AWS ◦  S3 ◦  Kinesis ◦  Elas;cache ◦  Elas;c Beanstalk ◦  EC2 ◦  DynamoDB   Open Source ◦  Snowplow Event Tracker ◦  Rundeck Scheduler ◦  Jenkins Con;nuous Integra;on ◦  Pentaho PDI   Other ◦ 
◦ 
◦ 
◦ 
HP Ver;ca Tableau Github RStudio Server DWH internal architecture
•  Tradi;onal three ;er DWH •  ODS generated automa;cally from staging •  Ops mart reﬂects data in original source form •  Helps oﬄoad queries from source systems •  Business marts materialized exclusively from vault Bijenkorf Data Vault overview
  Aligned to Data Vault 2.0   Model contains • Hash keys • Sales transac;ons • Hashes used for CDC • Customer and corporate loca;ons • Parallel loading • Maximum u;liza;on of available resources • Data unchanged in to the vault • Customers   Excluded from the vault ◦  Event streams ◦  Server logs ◦  Unstructured data • Products   Some sta*s*cs • Payment methods   18 hubs •  34 loading scripts • E-‐mail • Phone • Product grouping • Campaigns • deBijenkorf card • Social media   27 links •  43 loading scripts   39 satellites •  43 loading scripts   13 reference tables •  1 script per table   Data volumes • ~1 TB base volume • 10-‐12 GB daily • ~250 source tables Deep dive: Transactions in DV
• Transac;ons Deep dive: Customers in DV
• Same as link on customer Challenges encountered during data
modeling
Challenge Issue Details Resolu*on Source issues •  Source systems and original data •  Business keys rebuilt in staging unavailable for most informa;on prior to vault loading •  Data ohen transformed 2-‐4 ;mes before access was available •  Business keys (ex. SKU) typically replaced with sequences Modeling returns •  Retail returns can appear in ERP •  Original model showed sale in 1-‐3 ways across mul;ple tables state on line item satellite with inconsistent keys •  Revised model recorded •  Online returns appear as a state “nega;ve sale” transac;ons and change on original transac;on used a new link to connect to and may/may not appear in ERP original sale when possible Fragmented knowledge •  Informa;on about the systems was being held by mul;ple people •  Documenta;on was out-‐of-‐date •  Talking to as many people as possible and tes;ng hypotheses on the data Targeted benefits of DWH automation
Objec*ve Achievements Speed of development •  Integra;on of new sources or data from exis;ng sources takes 1-‐2 steps •  Adding a new vault dependency takes one step Simplicity •  Five jobs handle all ETL processes across DWH Traceability •  Every record/source ﬁle is traced in the database and every row automa;cally iden;ﬁed by source ﬁle in ODS Code simpliﬁca*on •  Replaced most common key deﬁni;ons with dynamic variable replacement File management •  Every source ﬁle automa;cally archived to Amazon S3 in appropriate loca;ons sorted by source, table, and date •  En;re source systems, periods, etc can be replayed in minutes Source loading automation
o  Design of loader focused on process abstrac;on, traceability, and minimiza;on of “moving parts” o  Final process consisted of two base jobs working in tandem: one for genera;ng incremental extracts from source systems, one for loading ﬂat ﬁles from all sources to staging tables o  Replica;on was desired but rejected due to limited access to source systems Workﬂow of source integra*on Source tables duplicated in staging with addi;on of loadTs and sourceFile columns Metadata for source ﬁle added Loader automa;cally generates ODS, begins tracking source ﬁles for duplica;on and data quality Example: Add addi*onal table from exis*ng source
Query generator automa;cally executes full duplica;on on ﬁrst execu;on and incrementals aherward CREATE TABLE stg_oms.customer ( customerId int , customerName varchar(500) , customerAddress varchar(5000) , loadTs ;mestamp NOT NULL , sourceFile varchar(255) NOT NULL ) ORDER BY customerId PARTITION BY date(loadTs) ; INSERT INTO meta.source_to_stg_mapping (targetSchema, targetTable, sourceSystem, ﬁleNamePapern, delimiter, nullField) VALUES ('stg_oms','customer','OMS','OMS_CUSTOMER','TAB','NULL') ; Vault loading automation
o  Loader is fully metadata driven with focus on horizontal scalability and management simplicity o  To support speed of development and performance, variable-‐driven SQL templates used throughout All Staging Tables Checked for Changes •  New sources automa;ca
lly added •  Last change epoch based on load stamps, advanced each ;me all dependenc
ies execute successfull
y •  Dependenc
ies declared at List of ;me of job Depende
crea;on nt Vault •  Load Loads priori;za;
Iden;ﬁed on possible but not u;lized Loads Planned in Hub, Link, Sat Order •  Jobs parallelized across tables but serialized per job •  Dynamic job queueing ensures appropriat
e execu;on order Loads Executed •  Variables automa;ca
lly iden;ﬁed and replaced •  Each load records performan
ce sta;s;cs and error messages Design goals for mart loading automation
Requirement Solu;on Beneﬁt Simple, standardized models Metadata-‐driven Pentaho PDI Easy development using parameters and variables Easily Extensible Plugin framework Rapid integra;on of new func;onality Rapid new job development Recycle standardized jobs and transforma;ons Limited moving parts, easy modiﬁca;on Low administra;on overhead Leverage built in logging and tracking Easily integrated mart loading repor;ng with other ETL reports Data Information mart automation flow
Retrieve commands Get dependencies • Each dimension and fact is processed independently • Based on deﬁned transforma;on, get all related vault tables: links, satellites or hubs • From the related tables, build a list of unique keys that have changed since the last update of the fact or dimension Retrieve changed • Store the data in the database un;l further processing data Execute transforma*ons Maintentance • Mul;ple Pentaho transforma;ons can be processed per command using the data captured in previous steps • Logging happens throughout the whole process • Cleanup aher all commands have been processed •  DV drives recommenda;on engine and customer recommenda;ons (updated nightly) •  Data pipeline supports near real ;me upda;ng of customer recommenda;ons based on web ac;vity Business Intelligence •  Provided ﬁrst uniﬁed data model of customer ac;vity •  80% reduc;on in unique customer keys •  Allowed for segmenta;on of customers based on combina;on of in-‐store and online ac;vity Personaliza;on Customer Analysis Primary uses of Bijenkorf DWH
•  DV-‐based marts replace joining dozens of tables across mul;ple sources with single facts/ dimensions •  IT-‐driven repor;ng being replaced with self-‐service BI Biggest drivers of success
AWS Infrastructure Automa;on PDI framework Cost: En;re infrastructure for less than one server in the data center Speed: Enormous ;me savings aher ini;al investment Ease of use: Adding new commands takes at most 45 minutes Toolset: Most services available oﬀ the shelf, minimizing administra;on Simplicity: Able to run and monitor 40k+ queries per day with minimal eﬀort Agile: Building the framework took 1 day Freedom: No dependency on IT for development support Auditability: Enforced tracking and archiving without developer involvement Low proﬁle: Average memory usage of 250MB Scalability: Systems automa;cally scaled to match DWH demands Biggest mistakes along the way
Reliance on documenta;on and requirements over expert users •  Ini;al integra;on design was based on provided documenta;on/models which was rarely accurate •  Current users of sources should have been engaged earlier to explain undocumented caveats Late u;liza;on of templates and variables •  Variables were u;lized late in development, slowing progress signiﬁcantly and crea;ng consistency issues •  Good ini;al design of templates will signiﬁcantly reduce development ;me in mid/long run Aggressive overextension of resources •  We apempted to design and populate the en;re data vault prior to focusing on customer deliverables like reports (in addi;on to other projects) •  We have shihed focus to con;nuous release of new informa;on rather than wai;ng for completeness Primary takeaways
◦ Sources are like cars: the older they are, the more idiosyncrasies. Be cau;ous with design automa;on! ◦ Automa;on can enormously simplify/accelerate data warehousing. Don’t be afraid to roll your own ◦ Balance stateful versus stateless and monolithic versus fragmented architecture design ◦ Cloud based architecture based on column store DBs is extremely scalable, cheap, and highly performant ◦ A successful vault can create a new problem: gewng IT to think about business processes rather than system keys! Rob Winters Andrei Scorus [email protected] [email protected]