Data foundation module creation

While Cortex Framework provides out-of-the-box data foundation modules for enterprise ERP systems such as SAP (cortex.sap), you can also create new custom data foundation modules within a custom namespace. This lets you define custom build behaviors and extend support to a new source system. These can include database management systems like PostgreSQL, MySQL, etc. that replicate their raw data into BigQuery.

This guide walks you through an end-to-end example of creating a new data foundation module for a Customer Service Ticketing System whose data (customers, tickets, ticketlogitem tables) has been replicated from a PostgreSQL database into a raw BigQuery dataset named ticketing_data_raw.

When creating a custom data foundation module, we recommend using a dedicated custom namespace to improve lifecycle management by separating extensions and customizations from Cortex Framework artifacts.

Example scenario overview

In this walkthrough, we will:

  1. Create a dedicated custom namespace ticketing to isolate the data foundation assets.
  2. Define a new data foundation module of path ticketing.ticketing.foundations.ticketing_system.
  3. Configure table settings (table_settings.default.yaml) for the customers, tickets, and ticketlogitem tables.
  4. Create column and field-level metadata annotations for each table.
  5. Register the raw data source (ticketing_data_raw), the target conformed dataset (data_foundation_ticketing), and the new foundation module in config/config.yaml.

Module folder and file structure

All physical files for your new data foundation module reside inside your custom namespace under src/data_modules/. The following table and directory tree outline where to place each file:

config/
└── config.yaml                                           # Global configuration & module registration
src/data_modules/ticketing/ticketing/foundations/ticketing_system/
├── manifest.yaml                                         # Declares module category, type, and builder
├── table_settings.default.yaml                           # Table materialization, bigQueryLabels, dataformTags, and layouts
├── builder.py                                            # (Optional) Custom Dataform generator class for this module
└── annotations/                                          # Field and column-level schema descriptions
    ├── customers.yaml
    ├── tickets.yaml
    └── ticketlogitem.yaml

Important: Before starting, ensure the source tables you plan to process exists in the raw layer dataset.

File or directory path Purpose and description
config/config.yaml Registers the ticketing namespace, the PostgreSQL raw data source, the target BigQuery dataset, and the foundation module instance.
src/data_modules/ticketing/ticketing/foundations/ticketing_system/manifest.yaml Declares the module metadata, display name, category (e.g., foundation), module type (e.g., generic), and the generator builder class used during compilation.
src/data_modules/ticketing/ticketing/foundations/ticketing_system/table_settings.default.yaml Configures which source tables from ticketing_data_raw should be conformed, along with BigQuery optimization dataformTags, bigQueryLabels, partition details, and cluster details.
src/data_modules/ticketing/ticketing/foundations/ticketing_system/annotations/*.yaml Contains rich YAML metadata describing table and field definitions. These are automatically merged into the compiled Dataform definitions so descriptions persist in BigQuery table metadata.
src/data_modules/ticketing/ticketing/foundations/ticketing_system/builder.py Optional. If your source database requires custom data cleaning or dialect-specific SQL transformations during compilation, you can define a module-level builder class here.

Step 1: Register the namespace, data source, and target in config.yaml

Before creating the physical files, open your deployment configuration file (config/config.yaml) and declare the custom namespace, the raw PostgreSQL source dataset, and the destination dataset where conformed tables will be created:

data:
  namespaces:
    - name: cortex
      path: ../src/data_modules/cortex
    - name: ticketing                              # <-- Name of custom namespace
      path: ../src/data_modules/ticketing          # <-- Points to subdirectory under 'src/data_modules/'

  datasets:
    - id: ticketing_data_raw                       # <-- Unique source ID
      projectId: "source_project_id"
      datasetId: ticketing_data_raw                # <-- Raw dataset containing PostgreSQL replication tables
    - id: data_foundation_ticketing                # <-- Unique target ID
      projectId: "target_project_id"
      datasetId: data_foundation_ticketing         # <-- Target dataset for conformed foundation tables

Step 2: Register the data foundation module in config.yaml

Under the data.modules.foundations section of config/config.yaml, register the new data foundation module instance, linking the data source (ticketing_data_raw) to the data target (data_foundation_ticketing):

data:
  modules:
    foundations:
      - moduleId: ticketing_foundation
        modulePath: ticketing.ticketing.foundations.ticketing_system   # Format: {namespace}.{systemtype}.{module_type:foundations}.{subsystemtype}
        dataSourceId: ticketing_data_raw
        dataTargetId: data_foundation_ticketing
        # Custom table settings file relative to 'config/' directory
        # Recommended path: '{namespace_dir}/{system_type}/foundations/{system_sub_type}/table_settings.yaml'
        # If omitted, defaults to "../src/data_modules/ticketing/ticketing/foundations/ticketing_system/table_settings.default.yaml"
        tableSettings: "ticketing/ticketing/foundations/ticketing_system/table_settings.yaml"

Step 3: Create the module manifest file

Create the manifest file declaring your module metadata: src/data_modules/ticketing/ticketing/foundations/ticketing_system/manifest.yaml.

displayName: Ticketing System Data Foundation
description: Conformed foundation tables for PostgreSQL raw ticketing database.
category: foundation
type: generic
builder: ticketing_foundation

Step 4: Create the table settings file (table_settings.default.yaml)

Create the default table configuration file src/data_modules/ticketing/ticketing/foundations/ticketing_system/table_settings.default.yaml. This file defines how the replicated PostgreSQL tables (customers, tickets, ticketlogitem) are materialized, partitioned, and clustered within BigQuery:

common:
  - source:
      tableName: customers
    target:
      bigQueryLabels:
        - key: data_class
          value: master
      dataformTags: [ticketing, foundation, masterdata]
      clusterDetails:
        columns: [customer_id]

  - source:
      tableName: tickets
    target:
      bigQueryLabels:
        - key: data_class
          value: transactional
      dataformTags: [ticketing, foundation, transactional]
      partitionDetails:
        column: created_at
        partitionType: time
        timeGrain: day
      clusterDetails:
        columns: [ticket_id, customer_id]

  - source:
      tableName: ticketlogitem
    target:
      bigQueryLabels:
        - key: data_class
          value: transactional
      dataformTags: [ticketing, foundation, transactional]
      partitionDetails:
        column: log_timestamp
        partitionType: time
        timeGrain: day
      clusterDetails:
        columns: [ticket_id, log_id]

Step 5: Create field-level metadata annotations

To ensure your conformed tables contain clear documentation in BigQuery, create an annotation YAML file for each table inside src/data_modules/ticketing/ticketing/foundations/ticketing_system/annotations/. The filename must exactly match the source table name.

annotations/customers.yaml

description: "Customer master data conformed from PostgreSQL raw ticketing database."
fields:
  - name: "customer_id"
    description: "Unique customer identifier, PK"
  - name: "email"
    description: "Primary email address associated with the customer"
  - name: "full_name"
    description: "Customer full name or account contact name"
  - name: "created_at"
    description: "Timestamp when the customer record was originally created in PostgreSQL"

annotations/tickets.yaml

description: "Customer service tickets conformed from PostgreSQL raw ticketing database."
fields:
  - name: "ticket_id"
    description: "Unique ticket identifier, PK"
  - name: "customer_id"
    description: "Foreign key referencing customers.customer_id"
  - name: "subject"
    description: "Summary or subject line of the customer inquiry"
  - name: "status"
    description: "Current ticket lifecycle status (e.g., OPEN, IN_PROGRESS, RESOLVED, CLOSED)"
  - name: "priority"
    description: "Priority severity level (e.g., LOW, MEDIUM, HIGH, URGENT)"
  - name: "created_at"
    description: "Timestamp when the ticket was created"
  - name: "updated_at"
    description: "Timestamp when the ticket was last modified"

annotations/ticketlogitem.yaml

description: "Audit log history and activity events for customer service tickets."
fields:
  - name: "log_id"
    description: "Unique log event identifier, PK"
  - name: "ticket_id"
    description: "Foreign key referencing tickets.ticket_id"
  - name: "action"
    description: "Action or event performed on the ticket"
  - name: "description"
    description: "Notes and description on performed events on the ticket"
  - name: "performed_by"
    description: "User, agent, or automated system that performed the action"
  - name: "log_timestamp"
    description: "Exact timestamp when the activity log event occurred"

Step 6: (Optional) Define a custom foundation builder

If your PostgreSQL data foundation requires logic during compilation (such as automatic data type casting, timestamp conversion, or data cleaning rules across all tables), you can define a custom builder scoped to this module.

Create src/data_modules/ticketing/ticketing/foundations/ticketing_system/builder.py:

import logging
import pathlib
import yaml
from common.builders.base import FoundationBuilder, Source
from common.registry import builder_registry
from common.schemas import config_schema, manifest_schema

logger = logging.getLogger(__name__)

@builder_registry.register("ticketing_foundation")
class TicketingFoundationBuilder(FoundationBuilder[config_schema.BaseModuleConfig]):
    """Custom Dataform generator for PostgreSQL ticketing data foundation."""

    def build(
        self,
        *,
        module_id: str,
        module_config: config_schema.BaseModuleConfig,
        global_config: config_schema.GlobalConfig,
        manifest: manifest_schema.ManifestConfig,
        base_dir: pathlib.Path,
        annotations_dir: pathlib.Path,
        output_dir: pathlib.Path,
        module_dir_name: str,
        sources_registry: set[Source],
        table_settings_file: pathlib.Path | None = None,
        required_tables: set[str] | None = None,
    ) -> None:
        logger.info("Building ticketing data foundation for module: %s", module_id)
        
        # 1. Load table settings
        if not table_settings_file or not table_settings_file.exists():
            logger.warning("No valid table settings found for %s