Enterprise Data Vault 2.0 Modeling

Objective:

• understand the architecture and components of a metadata-driven automation framework.

Topics:

• data vault 2.0 implementation lifecycle

• manual versus automated data vault development

• metadata-driven architecture and design principles

• automation framework components

• source, landing, bronze, raw vault and business vault layers

• SQL and python responsibilities

• framework configuration and execution flow.

Practical Excercises:

• design an end-to-end automation architecture and define its components.

Deliverable:

• automation architecture diagram.

OBJECTIVE:

• design metadata structures that drive automated data vault development.

TOPICS:

• metadata repository architecture

• source system and source object metadata

• file format and stage metadata

• table and column metadata

• hub, link and satellite configuration

• business key and hashdiff metadata

• load configuration and execution status

• metadata naming standards and relationships

PRACTICAL EXCERCISES:

• create metadata tables such as:

• GTE_METADATA_SOURCE_SYSTEM

• GTE_METADATA_FILE_FORMAT

• GTE_METADATA_STAGE_LOAD

• metadata tables for columns, hubs, links and satellites.

DELIVERABLE:

• metadata repository with sample configurations.

OBJECTIVE:

• extract database metadata and populate the automation repository.

TOPICS:

• database catalog and information schema

• extracting tables, columns and data types

• primary key and foreign key metadata

• metadata validation and synchronization

• source-to-target metadata mapping

• dynamic SQL generation from metadata

• metadata-driven configuration management

PRACTICAL EXCERCISES:

• develop SQL scripts to extract source table and column metadata and populate the repository.

DELIVERABLE:

• automated metadata extraction scripts.

OBJECTIVE:

• develop a reusable python framework for executing metadata-driven automation.

TOPICS:

• python project structure and modular design

• snowflake python connector

• connection management and environment variables

• reading metadata from snowflake

• executing SQL dynamically

• parameterized scripts and reusable functions

• logging, configuration and exception handling

• master execution program.

PRACTICAL EXCERCISES:

• develop python modules for connecting to snowflake, retrieving metadata and executing SQL scripts.

DELIVERABLE:

• reusable python execution framework.

OBJECTIVE:

• automate source file ingestion and landing table creation.

TOPICS:

• snowflake internal and external stages

• automated file format creation

• CSV, JSON and Parquet file handling

• stage creation using metadata

• file discovery and validation

• LIST, PUT and COPY INTO

• file patterns and incremental file processing

• landing table generation and load auditing.

PRACTICAL EXCERCISES:

• develop python and SQL scripts to create stages, file formats and landing tables, then load source files.

DELIVERABLE:

• metadata-driven file ingestion framework.

OBJECTIVE:

• automate bronze table creation and ingestion using source metadata.

TOPICS:

• bronze layer architecture and design | source-to-bronze mapping

• automated table DDL generation

• dynamic column and data type mapping

• audit and technical columns

• source system identifiers

• file name, load timestamp and record tracking

• bronze layer validation and reconciliation.

PRACTICAL EXCERCISES:

• build a python generator that reads metadata and creates bronze tables and corresponding load scripts.

DELIVERABLE:

• reusable bronze table generator.

OBJECTIVE:

• generate hub tables and loading logic automatically from metadata.

TOPICS:

• hub metadata requirements | business key identification and configuration

• hash key generation

• hub DDL generation

• record source and load timestamp

• dynamic SQL for hub loading

• duplicate detection and insert-only loading

• multi-source hub integration.

PRACTICAL EXCERCISES:

• build a metadata-driven hub generator and load customer and account hubs.

DELIVERABLE:

• hub DDL and load automation modules.

OBJECTIVE:

• automate link table creation and loading using relationship metadata.

TOPICS:

• link metadata design

• hub relationship configuration

• link Hash Key generation

• foreign key resolution

• standard and dependent-child Links

• dynamic link DDL generation

• link loading and duplicate prevention

• handling missing Hub records.

PRACTICAL EXCERCISES:

• develop a link generator for customer-account and account-transaction relationships.

DELIVERABLE:

• automated link generation and loading scripts.

OBJECTIVE:

• automate satellite DDL and loading logic for historical data management.

TOPICS:

• satellite metadata and attribute configuration

• hub, link and satellite generation

• hashdiff generation

• satellite DDL generation

• change detection and incremental loading

• insert-only historization

• handling nulls and changed attributes

• multi-source and source-aligned satellites.

PRACTICAL EXCERCISES:

• build a satellite generator and automate customer profile and account status loading.

DELIVERABLE:

• reusable satellite generator and load framework.

OBJECTIVE:

• integrate hub, link and satellite loading into a configurable raw vault pipeline.

TOPICS:

• raw vault load sequence

• metadata-driven dependency management

• hub, link and satellite execution order

• incremental loading and change detection

• late-arriving records

• transaction handling and restartability

• load reconciliation and record counts

• load status and audit management.

PRACTICAL EXCERCISES:

• build an automated Raw Vault loader that executes the generated scripts in the correct order.

DELIVERABLE:

• end-to-end raw vault loading engine.

OBJECTIVE:

• extend the automation framework to support derived structures and business rules.

TOPICS:

• business vault metadata requirements

• derived satellite generation

• business rule configuration

• calculated attributes and transformations

• effectivity processing

• business key resolution

• automated business vault DDL generation

• dependency management for derived objects.

PRACTICAL EXCERCISES:

• Configure and generate a derived Satellite for customer classification using business rules.

DELIVERABLE:

• Business Vault automation design and sample scripts.

OBJECTIVE:

• automate the generation and maintenance of PIT and bridge structures.

TOPICS:

• PIT and bridge metadata

• PIT table DDL generation

• satellite dependency configuration

• snapshot and incremental PIT loading

• bridge relationship metadata

• bridge table generation and refresh

• historical point-in-time retrieval

• performance and maintenance considerations.

PRACTICAL EXCERCISES:

• Generate a customer PIT and a customer-account Bridge from metadata.

DELIVERABLE:

• PIT and bridge automation modules.

OBJECTIVE:

• build a centralized execution engine to control the complete automation pipeline.

TOPICS:

• master execution framework

• configuration-driven processing

• pipeline dependency management

• sequential and parallel execution

• task status and execution logs

• retry and restart mechanisms

• failure handling and notifications

• integration with scheduling tools.

PRACTICAL EXCERCISES:

• develop a master python program that executes ingestion, bronze generation, raw vault loading

• downstream processing based on metadata.

DELIVERABLE:

• centralized orchestration framework.

OBJECTIVE:

• implement quality controls and operational capabilities for the automation framework.

TOPICS:

• metadata validation

• generated SQL validation

• data quality and reconciliation

• duplicate and missing record checks

• exception and error logging

• audit tables and execution history

• performance optimization

• secure configuration and credentials

• unit and integration testing.

PRACTICAL EXCERCISES:

• introduce error handling, audit logging and validation into the existing automation framework.

DELIVERABLE:

• tested automation components and operational monitoring design.

OBJECTIVE:

• Integrate course modules into a working metadata-driven data vault automation solution

BUSINESS SCENARIO:

• a financial organization receives customer, account, order, trade and portfolio data from multiple source systems.

• objective is to automate ingestion, bronze creation and data vault loading through a centralized metadata.

PROJECT ACTIVITIES:

1. configure file metadata

2. extract table and column metadata

3. generate stages, file formats and landing tables

4. generate bronze tables and ingestion scripts

5. generate hub, link and satellite DDL

6. automate hash key and hashdiff generation

7. execute raw vault loading

8. implement audit logging and error handling

9. configure master execution program

10. run the complete pipeline and validate the results.

FINAL DELIVERABLES:

• metadata repository

• python automation framework

• automated DDL generators

• hub, link and satellite loaders

• master execution program

• audit and validation scripts

• Introduction to enterprise data architecture

• Business requirements versus functional and non-functional requirements

• Role of the Business Analyst, Data Architect, Data Modeler and Data Engineer

• Relationship between business architecture, application architecture and data architecture

• Challenges in translating business language into technical solutions

• Requirement gathering techniques

• Business requirement documents (BRDs)

• Functional and non-functional requirements

• Business process identification

• Business scenarios, use cases and user stories

• Requirement classification and prioritization

• Requirement ambiguity, duplication, inconsistency and gaps

• Business events and their lifecycle

• Business rules and decision points

• Operational versus analytical requirements

• Business domains and subdomains

• Data domain ownership

• Master, reference, transactional and event data

• Cross-domain data dependencies

• Translating business activities into data requirements

• Business objects versus data entities

• Identifying data capture, update, retrieval and reporting requirements

• Data attributes and business definitions

• Data ownership and authoritative sources

• Data retention and historical requirements

• Operational, analytical and regulatory data needs

• Source system identification

• Source-to-business data mapping

• Structured, semi-structured and unstructured data

• Data profiling and source data assessment

• Identifying missing, duplicate and conflicting data

• Source system assessment and gap analysis

• What is enterprise data architecture?

• Data architecture principles and standards

• Centralized, decentralized and federated architectures

• Enterprise data architecture building blocks

• Data architecture patterns

• Data architecture artifacts and documentation

• Landing and staging layers

• Bronze, Silver and Gold architecture

• Enterprise Data Warehouse architecture

• Operational Data Store (ODS)

• Data Lake and Lakehouse

• Data marts and semantic layers

• Batch versus streaming architecture

• Data integration patterns

• ETL versus ELT

• API-based and event-driven integration

• Data replication and CDC

• Data transformation and enrichment

• Error handling and recovery

• Data latency and integration SLAs

• Purpose of conceptual data models

• Identifying business entities

• Business entity relationships

• Relationship types and cardinality

• Enterprise conceptual data modeling

• Logical data modeling methodology

• Entities, attributes and relationships

• Primary keys, alternate keys and candidate keys

• Foreign keys and referential integrity

• Identifying, non-identifying and recursive relationships

• Associative entities and many-to-many relationships

• Normalization: 1NF, 2NF and 3NF

• Enterprise-wide entity reuse

• Common business entities and shared data structures

• Master data and reference data modeling

• Transaction, event and status modeling

• Effective dating and historical attributes

• Address, contact and party modeling

• Hierarchies and recursive relationships

• Modeling complex business relationships

• Model validation and quality checks

• Logical model review and stakeholder approval

• Introduction to dimensional modeling and Kimball methodology

• Star schema and snowflake schema

• Fact tables and dimension tables

• Identifying business processes and fact table grain

• Transaction, periodic snapshot and accumulating snapshot facts

• Conformed dimensions

• Role-playing, junk and degenerate dimensions

• Slowly Changing Dimensions (SCD Types 0, 1, 2 and 3)

• Introduction to Data Vault 2.0 architecture

• Business Vault versus Raw Vault

• Hub, Link and Satellite structures

• Business keys and hash keys

• Satellite historization and load tracking

• Effectivity and record tracking satellites

• Multi-active satellites

• Point-in-Time (PIT) and Bridge tables

• Enterprise data warehouse integration patterns

• Data Vault to dimensional data mart transformation

• Hybrid enterprise data architecture

• Logical versus physical data models

• Mapping logical entities to physical tables

• Indexing and partitioning strategies

• Denormalization and performance considerations

• Database-specific implementation considerations

• Forward and reverse engineering using ER/Studio

• Data warehouse, data lake and Lakehouse architecture

• Cloud data platform design considerations

• Cloud storage and compute separation

• Data ingestion and transformation services

• Data platform scalability and elasticity

• Platform selection based on business and technical requirements

• Batch and streaming processing architecture

• Data storage design and lifecycle

• Data partitioning and clustering

• CDC and historical data management

• Data transformation architecture

• Query performance and workload optimization

• Data retention and archival

• Technical architecture decisions and trade-offs

• Data governance framework

• Data ownership and stewardship

• Data governance operating model

• Business glossary and data dictionary

• Technical and business metadata

• Master data management (MDM)

• Reference data management

• Data classification and sensitive data identification

• Data quality rules and validation

• Data profiling and reconciliation

• Data privacy and security principles

• Role-based and attribute-based access control

• Data masking and encryption

• Personally identifiable information (PII) classification

• Data retention and deletion requirements

• Regulatory and audit requirements

• Source-to-target mapping (STTM)

• Transformation rules and derivation logic

• Data lineage: source to report

• Data reconciliation and auditability

• ETL/ELT specifications

• Documentation and traceability

• Introduction to AI-assisted data architecture

• AI-assisted BRD analysis and requirement extraction

• Prompt engineering for business requirement analysis

• Identifying entities, attributes and relationships using AI

• Generating business rule catalogues

• AI-assisted conceptual and logical model generation

• AI-assisted source-to-target mapping

• Analyze the business requirements and processes.

• Identify business domains, entities and business rules.

• Design the enterprise data architecture.

• Develop a Data Vault and dimensional model for selected processes.

• Prepare data flows, STTM and lineage.

• Prepare the implementation blueprint.

• Enterprise architecture walkthrough

• Conceptual and logical model review

• Data integration and modeling strategy review

• Architecture decision documentation

• Capstone project presentation

• Final assessment and course completion

• Introduction to Generative AI and LLMs

• Traditional AI versus Generative AI

• LLM architecture at a conceptual level

• Tokens, tokenization and context windows

• Training data, inference and next-token prediction

• Model parameters and temperature

• System prompts, user prompts and conversation context

• Text generation and structured output

• Strengths and limitations of LLMs

• Hallucinations, outdated knowledge and ambiguous answers

• Deterministic versus probabilistic outputs

• Common use cases in data engineering, modeling and analytics

• Overview of hosted and open-source LLMs

• Responsible use of AI in enterprise environments

• Ask an LLM to explain a complex SQL query.

• Compare responses to the same prompt using different instructions.

• Test how ambiguous business requirements affect generated answers.

• Identify incorrect SQL and fabricated database objects in AI output.

• Prompt engineering fundamentals

• Anatomy of a well-structured prompt

• Role, task, context, constraints and output format

• Zero-shot and few-shot prompting

• Prompt templates and reusable prompt libraries

• Structured outputs using JSON and tables

• Prompting for SQL generation and explanation

• Prompting for query debugging and optimization

• Generating data dictionaries and technical documentation

• Generating source-to-target mapping (STTM) drafts

• Business requirement analysis and business rule extraction

• AI-assisted entity and attribute identification

• Iterative prompt refinement

• Common prompt failure patterns

• Generate SQL from a business question.

• Generate table and column descriptions from database metadata.

• Convert a business requirement into candidate entities and relationships.

• Generate data quality rules from sample data.

• Limitations of LLMs when answering enterprise-specific questions

• Introduction to Retrieval-Augmented Generation

• RAG architecture and components

• Document ingestion and preprocessing

• Text extraction and cleaning

• Chunking strategies and chunk size

• Vector databases and vector indexes

• Top-K retrieval

• Metadata filtering

• Context construction and prompt augmentation

• RAG response generation

• Basic RAG evaluation

• Retrieval failures and incomplete context

• Split documents into chunks.

• Generate embeddings using an available embedding model.

• Store and retrieve embeddings using a vector store.

• Build a simple question-answering pipeline over metadata.

• Compare ordinary LLM answers with RAG-grounded answers.

• Responsible AI in data teams

• Data privacy and confidentiality

• Personally identifiable information (PII)

• Sensitive data and enterprise data policies

• Risks of sharing proprietary metadata

• Safe use of public versus enterprise AI tools

• Access control and data minimization

• Prompt injection and untrusted retrieved content

• Reviewing AI-generated SQL

• Reviewing AI-generated data models and documentation

• SQL execution, testing and reconciliation

• Hallucination detection and factual verification

• AI governance and auditability

• Limitations of automated evaluation

• Capstone project presentation and trainer review

• Review an AI-generated SQL query and identify errors.

• Identify sensitive information in sample prompts.

• Evaluate RAG answers for relevance, correctness and source support.

• Design a human approval workflow for AI-generated data artifacts.