Business Requirement to Enterprise Data Architecture

• 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.

OBJECTIVE:

• Understand the role of dimensional modeling in modern analytical data platforms

TOPICS:

• oltp vs. olap systems

• operational databases vs. analytical databases

• enterprise data warehouse

• data mart and lakehouse

• Introduction to the Kimball methodology

• Dimensional modeling vs. normalized modeling

• Star schema vs. snowflake schema

• Analytical workloads and reporting requirements

• Overview of modern data warehouse architecture

PRACTICAL ACTIVITY:

• Examine an operational order-processing database and identify the information required for analytical reporting

ASSESSMENT:

• Identify the differences between an operational model and an analytical model.

OBJECTIVE:

• convert business requirements into a dimensional modeling design.

TOPICS:

• business process identification

• business requirements and analytical questions

• identifying business events and measures

• identifying dimensions and descriptive attributes

• business process matrix

• dimensional bus architecture

• identifying shared dimensions

• translating business requirements into facts and dimensions

PRACTICAL ACTIVITY:

• analyze a retail business case and prepare a business process matrix covering sales, returns, inventory and payments.

ASSESSMENT:

• create a preliminary dimensional design from a business requirements document.

OBJECTIVE:

• design fact tables with the appropriate grain

• measures and dimensional relationships.

TOPICS:

• fact table fundamentals

• defining and documenting fact table grain

• transaction fact tables

• periodic snapshot fact tables

• accumulating snapshot fact tables

• factless fact tables

• additive, semi-additive and non-additive measures

• foreign keys and dimensional relationships

• multiple facts and different grains

• common fact table design mistakes

PRACTICAL ACTIVITY:

• Design sales transaction, monthly inventory snapshot and order fulfillment fact tables.

ASSESSMENT:

• Document the grain and measures for three different business processes.

OBJECTIVE:

• design dimensions that provide consistent and meaningful analytical context.

TOPICS:

• dimension table fundamentals

• surrogate keys vs. natural keys

• dimension attributes and hierarchies

• conformed dimensions

• role-playing dimensions

• degenerate dimensions

• junk dimensions

• mini-dimensions

• date and time dimensions

• handling unknown and not-applicable dimension members.

PRACTICAL ACTIVITY:

• design customer, product, date, location and sales-channel dimensions.

OBJECTIVE:

• Build complete dimensional schemas and evaluate alternative designs.

TOPICS:

• star schema architecture

• snowflake schema architecture

• fact-to-dimension relationships

• one-to-many dimensional relationships

• dimension normalization and denormalization

• shared dimensions across business processes

• multiple fact tables

• multi-star and constellation schemas

• schema design trade-offs

• designing schemas for BI consumption.

PRACTICAL ACTIVITY:

• Convert a business requirements model into a star schema and compare it with a snowflake alternative.

ASSESSMENT:

• Produce a logical star schema diagram and justify its design decisions.

OBJECTIVE:

• Implement historical tracking of changes in dimensional attributes.

TOPICS:

• why dimensional attributes change

• SCD type 0: Retain original value

• SCD type 1: overwrite

• SCD type 2: historical versioning

• SCD type 3: previous value

• effective dates and expiration dates

• current record indicators

• surrogate key management

• late-arriving dimension records

• historical fact association

• SQL implementation of SCD Type 1 and Type 2

PRACTICAL ACTIVITY:

• implement customer and product dimension changes using SQL.

ASSESSMENT:

• design an SCD Type 2 dimension and write SQL to process new and changed records.

OBJECTIVE:

• address complex business requirements using advanced dimensional modeling patterns.

TOPICS:

• multiple fact tables with shared dimensions

• header and line-level transaction facts

• bridge tables for many-to-many relationships

• multivalued dimensions

• allocating measures across bridge relationships

• hierarchical dimensions

• ragged and variable-depth hierarchies

• parent-child relationships

• snapshot facts with changing dimensional relationships

• handling multiple currencies and units of measure

PRACTICAL ACTIVITY:

• design a banking or investment model with customers, accounts, transactions and joint account holders.

ASSESSMENT:

• solve a many-to-many dimensional modeling scenario using a bridge table.

OBJECTIVE:

• Model time-dependent business processes and complex measures.

TOPICS:

• date dimension design

• multiple date roles in a fact table

• business calendars and fiscal calendars

• event-based and periodic measurements

• accumulating snapshots and milestone dates

• inventory and account balance snapshots

• semi-additive balances

• fact table partitioning considerations

• late-arriving facts and dimensions

• handling corrections and restatements

PRACTICAL ACTIVITY:

• model an order lifecycle with order, shipment, delivery and payment milestones.

ASSESSMENT:

• design an accumulating snapshot fact table and demonstrate its analytical use.

OBJECTIVE:

• Translate logical dimensional designs into implementable database structures.

TOPICS:

• logical vs. physical dimensional models

• naming standards and data types

• primary keys and foreign keys

• creating fact and dimension tables using SQL

• surrogate key generation

• loading dimensions and facts

• referential integrity and data quality

• incremental loading concepts

• SQL joins between fact and dimension tables

• query patterns for analytical reporting.

PRACTICAL ACTIVITY:

• Implement a sales star schema using SQL and populate sample data.

ASSESSMENT:

• Create the physical schema and execute analytical queries against it.

OBJECTIVE:

• Understand how dimensional models are implemented in modern cloud data platforms.

TOPICS:

• dimensional modeling in Snowflake

• dimensional modeling in Azure and Microsoft Fabric

• dimensional modeling in Databricks and Delta Lake

• data warehouse vs. lakehouse implementation

• data quality and reconciliation

• performance considerations for analytical workloads

• partitioning, clustering and indexing concepts

• BI semantic models and dimensional relationships.

PRACTICAL ACTIVITY:

• review the implementation of a star schema in a cloud data warehouse and write analytical SQL queries.

ASSESSMENT:

• identify implementation considerations for deploying a dimensional model on a selected platform.

OBJECTIVE:

• Validate dimensional models and improve their suitability for reporting and analytics.

TOPICS:

• reviewing the business process matrix

• grain consistency and dimensional conformance

• fact and dimension validation

• referential integrity checks

• handling duplicate and missing dimension members

• aggregation correctness

• query performance analysis

• avoiding double counting

• model usability for BI developers

• common dimensional modeling anti-patterns.

PRACTICAL ACTIVITY:

• Review a deliberately flawed dimensional model, identify design issues and correct them.

ASSESSMENT:

• Complete a dimensional model review checklist and demonstrate the corrected design.

OBJECTIVE:

• Apply the complete dimensional modeling lifecycle to a realistic business scenario.

TOPICS:

• business requirements analysis

• business process identification

• fact grain definition

• dimension and measure identification

• star schema design

• SCD strategy selection

• physical model creation

• analytical SQL and reporting validation

• model documentation and presentation

PRACTICAL ACTIVITY:

• design a dimensional data warehouse for a retail, banking or logistics business case.

FINAL ASSESSMENT:

• submit and present a complete dimensional model, including business process matrix, schema diagram, table specifications and sample analytical queries.