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.

Note: If you only want to process additional standard or custom tables, you do not need to create a custom module. Instead, you can configure standard module to run multiple instances with its own table settings.

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

Note: The builder: parameter instructs Cortex Framework which compiler class to use when generating Dataform .sqlx or .js definitions. If you register a custom builder (see Step 6), specify its key here.

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", module_id)
            return

        with open(table_settings_file, encoding="utf-8") as f:
            settings = yaml.safe_load(f) or {}

        tables = settings.get("common", [])
        source_config = global_config.get_data_source(module_config.data_source_id)
        target_dataset = global_config.get_data_target(module_config.data_target_id)

        # 2. Generate Dataform .sqlx files for each table
        for table_item in tables:
            source_table = table_item["source"]["tableName"]
            if required_tables and source_table not in required_tables and not table_item.get("deployAlways"):
                continue

            # Register source table for centralized source generation
            sources_registry.add(Source(source_config.project_id, source_config.dataset_id, source_table))

            # Retrieve labels if configured
            bigquery_config = {}
            if "bigQueryLabels" in table_item["target"]:
                labels_dict = {label["key"]: label["value"] for label in table_item["target"]["bigQueryLabels"]}
                bigquery_config["labels"] = labels_dict

            dataform_tags = table_item["target"].get("dataformTags", ["ticketing", "foundation"])

            sqlx_content = f"""config {{
  type: "table",
  schema: "{target_dataset.dataset_id}",
  name: "{source_table}",
  tags: {dataform_tags}"""
            
            if bigquery_config:
                sqlx_content += f",\n  bigquery: {bigquery_config}"
                
            sqlx_content += f"""
}}

SELECT *
FROM `${{source_config.project_id}}.${{source_config.dataset_id}}.{source_table}`
"""
            out_file = output_dir / f"{source_table}.sqlx"
            out_file.write_text(sqlx_content, encoding="utf-8")
            logger.info("Generated %s", out_file)

Note: When builder.py is placed directly inside the module directory, Cortex Framework dynamically discovers and loads it without requiring global python package registration.

Verification of new foundation module

To verify and deploy your newly created data foundation module:

1. Execute the Cortex Framework build and deploy script: bash uv run cortex-build-and-deploy --config "config/config.yaml"
2. Check that the Dataform compilation succeeded without errors and that .sqlx scripts were generated for customers, tickets, and ticketlogitem.
3. Follow the Post deployment steps to run your Dataform pipeline actions and verify the conformed records inside your data_foundation_ticketing dataset in BigQuery.

To verify that the custom data foundation module compiles and deploys successfully, refer to the Verification section in the data product extensibility page.

---

• Previous step: Setting up a custom namespace
• Next step: Data product module creation
• Back to Overview