Home› Getting Started› Quick Start Guide› Synchronize (SQL Generation)

Synchronize (SQL Generation)

After the model has been refined, the next step is to synchronize the data warehouse. Synchronization converts the metadata stored in the repository into physical SQL objects in the target database.

At this stage, AnalyticsCreator materializes the designed warehouse structure. Tables, views, and generated procedures become visible in the SQL Server database. This is the point where the model moves from design-time metadata to deployable database objects.

Purpose

Generate and materialize the physical database structure from the metadata model.

Design Principle

Synchronization is the controlled transition from metadata definition to SQL implementation.

  • The repository remains the source of truth
  • The target database is regenerated or updated from metadata

Developers do not manually create warehouse objects in the target database. Instead, AnalyticsCreator generates them consistently from the repository definitions.

Inputs / Outputs

Inputs

  • Refined metadata model in the repository
  • Target database configuration
  • Naming conventions and generation settings

Outputs

  • Generated SQL Server database objects, including:
    • STG tables
    • Persistent staging and historization tables
    • CORE views and tables
    • DM views and tables
    • Stored procedures for loading, historization, and persisting

Internal Mechanics

1. Metadata evaluation

AnalyticsCreator reads the current model definition from the repository and determines which SQL objects must be created or updated.

2. Object generation

Based on the metadata, the system generates SQL artifacts such as:

  • Physical tables for staging and persistent layers
  • Views for generated transformations
  • Stored procedures for historization and persisting

3. Schema materialization

The generated structure is applied to the target SQL Server database. After synchronization, the database contains the warehouse objects defined in the model.

4. Dependency-aware generation

Objects are generated in the required order so that dependent objects can reference upstream objects correctly.

5. Re-synchronization behavior

If the model changes, synchronization updates the target structure accordingly. This keeps the generated SQL database aligned with the repository metadata.

Types / Variants

Typical synchronized object types

  • Import tables
  • Historization tables
  • Transformation views
  • Persisted transformation tables
  • Stored procedures

Generation patterns

  • View-based transformations
  • Table-based persisted layers
  • Procedure-driven loading and historization

Example

A refined model contains:

  • One source import table
  • One historized customer table
  • One fact transformation
  • One customer dimension

After synchronization, the target SQL Server database contains generated objects such as:

stg.Customer_Import
pst.Customer_History
core.vw_FactSales
dm.vw_DimCustomer
sp_Load_Customer_Import
sp_Historize_Customer

The model now exists as physical SQL objects, but data is not yet loaded unless execution is triggered separately.

When to Use / When NOT to Use

Use when

  • The model has been refined and validated
  • You want to materialize the current warehouse structure
  • You need to inspect or test generated SQL objects

Do NOT treat synchronization as execution

  • Synchronization creates structure, not loaded business data
  • ETL or pipeline execution happens later

Performance & Design Considerations

  • Synchronization affects schema, not data volume
  • Frequent changes to metadata can cause repeated structural updates
  • Manual database changes outside AnalyticsCreator can be overwritten

Design trade-off:

  • Consistent generated structure vs manual database customization

Integration with other AnalyticsCreator features

  • Repository: remains the source for all generated objects
  • Refinement: defines what is materialized
  • Persisting: adds generated persisted tables and procedures
  • Deployment: packages the synchronized structures for release

Common Pitfalls

  • Assuming synchronization loads data
  • Editing generated database objects manually
  • Synchronizing before validating keys and joins
  • Forgetting that metadata, not the target database, is authoritative

Key Takeaway

Synchronization materializes the metadata model as physical SQL objects in the target database, but it does not execute data loading by itself.