Write Once, Deploy Anywhere (Without Losing Semantics) — Multi-Dialect DDL Generation

Metadata-First DDL Generation.

Write Once, Deploy Anywhere (Without Losing Semantics) — Multi-Dialect DDL Generation

Metadata-First DDL Generation.

Most data platforms are not single-dialect anymore.

In modern environments, you often see:

Snowflake for analytics
Databricks for processing
BigQuery for BI
DuckDB for local experimentation
PostgreSQL for operational workloads

And sometimes all of them in the same organization.
The question becomes:

How do you keep structural consistency across platforms without rewriting everything?

MDDE approaches this through metadata-first DDL generation.

The Problem With Platform-Specific SQL

Consider a simple table:

CREATE TABLE customer (  
    customer_id VARCHAR(50) NOT NULL,  
    customer_name VARCHAR(255),  
    PRIMARY KEY (customer_id)  
);

That works in ANSI SQL.

Now deploy it to:

Snowflake
Databricks
BigQuery
DuckDB

You quickly encounter:

Quoting differences
Type differences (VARCHAR vs STRING vs TEXT)
Constraint support variations
Comment syntax differences
Schema naming patterns
Storage clauses

Teams usually solve this by:

Maintaining separate DDL scripts
Introducing conditional templating
Accepting slight divergence
Or standardizing on the lowest common denominator

None of these scale well.

The Metadata-First Alternative

In MDDE, DDL is not the source of truth. Metadata is.

The logical layer defines:

entity:  
  name: customer  
  attributes:  
    - name: customer_id  
      data_type: VARCHAR(50)  
      mandatory: true  
      is_primary_key: true  
    - name: customer_name  
      data_type: VARCHAR(255)

From this, MDDE generates dialect-aware SQL.

ANSI Baseline

CREATE TABLE customer (  
    customer_id VARCHAR(50) NOT NULL,  
    customer_name VARCHAR(255),  
    PRIMARY KEY (customer_id)  
);

Snowflake Output

CREATE TABLE "ANALYTICS"."CUSTOMER" (  
    "CUSTOMER_ID" VARCHAR(50) NOT NULL,  
    "CUSTOMER_NAME" VARCHAR(255),  
    PRIMARY KEY ("CUSTOMER_ID")  
)  
COMMENT = 'Customer master data';

Databricks Output

CREATE TABLE analytics.customer (  
    customer_id STRING NOT NULL,  
    customer_name STRING  
)  
USING DELTA  
COMMENT 'Customer master data';

DuckDB Output

CREATE TABLE customer (  
    customer_id VARCHAR(50) NOT NULL,  
    customer_name VARCHAR(255),  
    PRIMARY KEY (customer_id)  
);

Same metadata.
Different dialects.
No duplication of structural definition.

Type Mapping Strategy

One of the hardest problems is type mapping.

Example:

VARCHAR(50) → STRING (Databricks)
DECIMAL(18,2) → NUMBER(18,2) (Snowflake)
BOOLEAN → BOOL (some engines)

MDDE uses:

Logical type definitions
Dialect mapping tables
Configurable overrides

This allows:

Enterprise-wide type standards
Platform-specific adjustments
Centralized evolution of mappings

When a new dialect is added, you don’t rewrite models.
You extend mappings.

Dependency Ordering

DDL generation also handles:

Relationship dependencies
Foreign key ordering
Schema grouping

Entities are created in dependency order.
No manual reordering required.

Why This Matters in Migration

In migration programs, platform shifts are common:

SQL Server → Snowflake
On-prem → Databricks
Snowflake → BigQuery
Legacy DW → Modern Lakehouse

Without metadata centralization, migration becomes:

Manual rewriting
Risk-prone diffing
Inconsistent constraints
Partial enforcement

With metadata-first DDL:

Logical structure remains stable
Dialect differences are mechanical
Structural consistency is preserved
Testing focuses on semantics, not syntax

Beyond Tables

DDL generation also includes:

Views
Materialized views
Index definitions (where supported)
Constraints
Comments
Documentation annotations

All derived from metadata.
Not duplicated in scripts.

Platform Portability as Architectural Discipline

Portability is not about running the same SQL everywhere.

It’s about:

Preserving structural intent
Abstracting syntax differences
Enforcing consistent modeling standards

Metadata becomes: The portability layer.

Practical Example

Generate Snowflake DDL:

python -m mdde.scripts.export_model mdl_sales \  
    --output workspace/my_model \  
    --ddl \  
    --dialect snowflake

Switch dialect:

python -m mdde.scripts.export_model mdl_sales \  
    --output workspace/my_model \  
    --ddl \  
    --dialect databricks

No model rewrite.
No duplicated definitions.

Why This Is Not Just a Code Generator

Code generators usually:

Operate from templates
Lack deep lineage context
Ignore governance rules

MDDE generates DDL from:

Structured logical entities
Explicit relationships
Stereotype classifications
Quality metadata
Compliance constraints

DDL is a projection of architectural structure.
Not a handcrafted script.

VSCode Workflow

Inside VSCode:

Modify metadata
Run export
Review generated DDL
Commit changes

Platform shifts become diffs.
Not rewrites.

Closing

Data platforms evolve.

Cloud vendors change.
Strategies shift.
Costs fluctuate.

If your architecture depends on dialect-specific SQL, portability becomes fragile.

If your architecture depends on metadata, dialects become implementation details.

Next in the Series

In the next article:

Data Quality as Metadata — Checks, Profiles, and Contracts

We’ll explore:

Generated duplicate checks
Null validations
Referential integrity enforcement
Profiling SQL generation
Data contract export

Structure deployed.
Next: structure validated.

🔧 Technical Deep Dive — Type Mapping Resolution

Inspect dialect resolution:

SELECT  
    logical_type,  
    snowflake_type,  
    databricks_type,  
    duckdb_type  
FROM metadata.type_mapping;

DDL generation engine:

resolved_type = type_mapping.resolve(  
    logical_type="decimal(18,2)",  
    dialect="snowflake"  
)

Output:

NUMBER(18,2)

Dialect switching is mechanical.
Structure remains stable.