Automating Data Warehouse Delivery
Executive Summary
What if the dimensional model you designed was also the specification that generated your load logic? Not a document that describes what the warehouse should look like, but an active asset that the platform reads to produce the SCD handling, the surrogate key lookups, the staging layers, and the load sequences automatically.
That is how Astera Centerprise works. Users define their dimensional model (entity types, key types, SCD configurations, row identifiers, fact role columns) and the platform reads those definitions to generate the load logic and execution sequences. The model is not a planning artifact. It is the input to the automation engine.
Throughout this process, Centerprise's agentic AI works alongside the user as a collaborative partner. Built on an Agentic Data Stack with MCP-based tools, the AI has access to the same operations as the visual interface. It can perform structural changes to the model – creating, modifying, or deleting entities and attributes, merging entities, and redefining keys – as well as configure dimensional behavior, suggest mappings, build transformation expressions, and assist with structural design decisions. Users work with the AI the way they would work with an expert colleague: through conversation, with full ability to switch to the GUI at any point.
This paper explains how the platform automates warehouse delivery, from model design through dimension and fact loading to scheduled incremental refresh.
Discovery and Mapping for Warehouse Sources
Before pipelines can be generated, the platform needs to understand the source data and align it with the target warehouse model.
Source Discovery
Centerprise supports source discovery through a combination of reverse engineering, metadata analysis, and data profiling. Reverse engineering from an existing database automatically identifies the metadata, including the primary keys, foreign keys, and constraints that are explicitly defined within source databases.
For raw or lightly governed sources where constraints may be missing or incomplete, profiling analyzes the actual data to identify candidate primary keys and foreign keys. Centerprise includes built-in key profilers that evaluate uniqueness, nullability, and relationship patterns, and metadata can also be used to supplement and validate these findings.
The quality of the source model directly affects the quality of generated pipelines: missing or incorrect relationships result in missing joins and lookups downstream. Users can review the findings, confirm or adjust detected keys and relationships, and ask the AI chat questions about the source structures. All discovery methods can be guided and controlled through natural language via the AI agent. The finalized discovery output feeds directly into the source model used for mapping.
Mapping Normalized Sources to Warehouse Targets
Warehouse mapping is not a simple field-to-field exercise. The source is typically normalized (third normal form or similar), while the target is a denormalized dimensional model. A single warehouse dimension may draw attributes from multiple source tables, and a fact table's measures and keys may originate from several different transactional entities. The mapping has to account for these structural differences, not just column-level alignment.
Centerprise's mapping engine handles this by working at the model level rather than the table level. It takes the source model and the target warehouse model and aligns entities and attributes across both, using two layers: structural matching (column names, data types, positional metadata) for exact matches, and AI-driven semantic matching for cases where naming conventions differ between systems.
The AI also suggests transformation expressions based on the nature of the columns. A target column called "TotalAmount" might be recognized as a calculated field. A field called "FullAddress" might be flagged as a concatenation of address components. Users can accept, modify, or refine these suggestions through conversation with the AI or through the mapping UI.
For warehouse projects with many source tables feeding into a dimensional model, the mapping engine processes all entity pairs in a single pass, producing a complete mapping proposal that users review in a unified view rather than configuring each table individually.
The Data Model as the Warehouse Specification
Centerprise's approach is to encode all the information the load process needs into the data model itself. Instead of writing load logic that implements SCD handling, surrogate key lookups, and load ordering, users configure the model and the platform generates the implementation. Every model property has a direct effect on the behavior of the generated pipelines, which means changes to the model automatically propagate to the load logic.
Building the Dimensional Model
Users can create a warehouse model from scratch using the visual model builder, or reverse-engineer an existing database and convert it into a dimensional model. The modeling interface supports Kimball-style star schemas.
Users designate each table as a fact, dimension, or reference table using simple point-and-click selections. This is not just a label. It tells the platform what kind of load logic to generate for that table. Based on these selections, surrogate keys, row identifiers, and standard dimensional behaviors are added automatically, allowing users to build a complete dimensional model quickly without scripting.
Common warehouse constructs such as numeric fact extraction, date and time relationships, and child fact handling can be applied through straightforward options in the modeling interface.
The agentic AI participates directly in model creation as an accelerator. A user can describe the structure they want ("create a customer dimension with name, address, and segment attributes, tracked as SCD Type 2 with effective and expiration dates"), and the AI builds the entity, sets the key types, configures the row identifiers, and assigns the SCD types. If a user is working in the visual designer and wants to make a change, they can describe it in conversation ("change the segment attribute to SCD Type 1"), and the AI applies it. Every operation the AI performs is the same operation available through the GUI.
What the Model Carries
A dimensional model in Centerprise includes the following properties, each of which the load engine reads at execution time:
Entity type: fact, dimension, or reference. Determines which loading strategy applies.
Business key: the natural identifier from the source system. Used for matching incoming records to existing dimension rows.
Surrogate key: the warehouse-generated identifier. Assigned during dimension loads and referenced by fact table foreign keys.
Active row identifier: the strategy for determining which dimension row is current. Options are effective and expiration dates, version numbers, or a binary current-record designator. This controls the lookup logic for both dimension loads and fact loads.
SCD type per attribute: Type 1 (overwrite), Type 2 (version with history), Type 3 (store previous value), or Type 6 (hybrid). Each attribute can have a different type within the same dimension.
Fact role columns: columns in the fact table layout that identify the transaction date key and other fields the load process needs for historical surrogate key resolution.
Referential integrity constraints: foreign key relationships between facts and dimensions (and between parent and child tables) that determine load order.
These are not metadata annotations. They are the inputs that the load engine consumes. When you configure a dimension with SCD Type 2 and effective/expiration date tracking, you are defining the behavior of the load process for that dimension. Changes to any of these properties regenerate the corresponding load logic, so the model remains the single point of control.
Generating Warehouse Pipelines from the Model
Once the model is defined and mappings are confirmed, Centerprise generates the warehouse load pipelines automatically. This is where the model-driven approach pays off most directly.
Bulk Pipeline Generation
For each mapped source-to-target entity pair, the platform produces a complete, executable pipeline. These pipelines include the transformations specified in the mapping (filters, expressions, lookups) and are ready to run without further configuration. Users can review and modify any generated pipeline through the visual designer or by working with the AI, adding additional business logic where needed. The structural work is handled by the generation step; the user retains full control over the final pipeline.
Source-Side Joins from the Model
A common challenge in warehouse loading is that the target is a denormalized version of the source. A single dimension or fact table in the warehouse may draw data from multiple normalized source tables, which means the load pipeline needs joins on the source side to assemble the right data before it reaches the target.
Because the source model contains the relationships between tables, Centerprise can build these joins automatically at pipeline generation time. This happens behind the scenes when pipelines are auto-generated, so users do not need to manually write or configure the source-side join logic.
Load Ordering
In a warehouse, load order is critical. Dimension tables must be loaded before the fact tables that reference them. Parent tables must be loaded before child tables. Centerprise determines this order automatically by reading the referential integrity constraints from the model. The dependency graph is resolved without user intervention.
ELT Execution and Staging
Warehouse pipelines in Centerprise are executed in ELT mode. Instead of extracting data, transforming it in an intermediary engine, and then loading it, the platform pushes the transformation logic down to the database as set-based operations. This is particularly important for warehouse workloads where the data volumes are large and the transformation logic (SCD handling, lookups, key resolution) benefits from running where the data already lives.
The platform supports ELT pushdown to major warehouse and database platforms, including SQL Server, PostgreSQL, Oracle, and Snowflake. When the source and target reside on the same server, full pushdown is used. When they are on different servers, partial pushdown distributes the processing between source and target to minimize data movement.
The platform automatically generates staging layers behind the scenes to facilitate this process. Source data is landed in staging tables, and the warehouse load operations run against those staging tables to populate the final dimension and fact tables. Users do not need to design or manage these staging layers. They are created and cleaned up as part of the automated execution.
How Dimension Loading Works
When a dimension pipeline executes, the engine reads the SCD configuration defined in the model and generates the corresponding operations inside the warehouse. The behavior is attribute-driven, and all logic runs as set-based ELT operations.
Change Detection
Incoming records are compared against the currently active dimension rows using the business key and the configured active-row identification strategy. Change detection is scoped only to attributes that require historical tracking. If no relevant change is detected, the row is left untouched.
Versioned Updates and Inserts
When a change is detected, the generated logic performs coordinated updates and inserts based on the attribute-level SCD configuration.
For versioned dimensions:
The current active row is identified.
That row is expired according to the model definition.
A new row is inserted with a new surrogate key and updated values.
Active-row indicators are initialized per configuration.
When multiple SCD types exist within the same dimension, the engine evaluates each attribute independently but applies all resulting operations in a single coordinated load step. Only one row remains active per business key, and partial transitions cannot occur.
Execution Model
All comparisons and updates execute as set-based operations against staging tables. No procedural scripting or row-by-row logic is introduced. Changes to attribute SCD types or row-identification strategy in the model regenerate the load logic automatically.
How Fact Table Loading Works
Fact loading centers on surrogate key resolution. For each dimension relationship defined in the model, the engine generates lookup logic that resolves source business keys to the correct dimension surrogate keys at load time.
Surrogate Key Resolution
Lookups execute as set-based joins inside the warehouse. The join predicate is derived from the dimension's active-row identification strategy:
Flag-based: filter on active flag.
Date-range: constrain to effective-to-expiration window.
Version-based: select current version.
All foreign keys for a fact table are resolved within a coordinated load operation.
Historical Resolution
If a fact table defines a transaction date key, that date replaces the system date in lookup predicates. This ensures that surrogate resolution reflects the dimension state at the time of the transaction.
The platform enforces at design time that any fact using a transaction date key references dimensions with date-based row identification. This prevents configurations that would produce incorrect lookups at runtime.
Late-Arriving Dimensions
If a fact references a dimension member that does not yet exist, a placeholder row is inserted with a generated surrogate key. The fact row references that surrogate immediately. When the full dimension record arrives later, the placeholder is updated or versioned according to SCD rules, and the surrogate key remains unchanged.
The entire dimension and fact loading process (SCD handling across all types, surrogate key assignment, lookup generation, transaction date resolution, placeholder management, staging layer creation) is derived from the data model. The model is the specification. The load behavior is the output.
Incremental Loads and Sustained Automation
A warehouse is not a one-time build. It needs to stay current. Centerprise supports this through Change Data Capture (CDC) and the built-in job scheduler.
Change Data Capture
CDC in Centerprise tracks audit fields on source records to identify which rows have been inserted or updated since the last load. On each execution cycle, only the changed records are processed. This avoids the cost and time of reloading entire tables and is essential for keeping warehouse refreshes fast as data volumes grow.
For dimension loads, CDC means only new or changed dimension records go through the SCD logic. For fact loads, only new transactions are picked up and routed through the surrogate key lookups. The load logic generated from the model handles both scenarios correctly regardless of whether the load is a full initial load or an incremental refresh.
Scheduling and Workflow Orchestration
The workflows built on top of the warehouse pipelines can be scheduled for recurring execution. Centerprise's scheduler supports both time-based and event-based triggers, and workflows can execute multiple pipelines in series or parallel, run SQL scripts, send email notifications, and handle conditional branching.
For a warehouse, a typical scheduled workflow loads dimensions first (respecting load order from the model), then loads fact tables, and then runs any post-load processes. Because load order is derived from the model's referential integrity constraints, the workflow's execution sequence is automatically correct.
The combination of model-driven load generation, CDC for incremental processing, and scheduled workflows is what makes the warehouse self-sustaining. After the initial setup (model design, mapping review, pipeline generation), the ongoing refresh cycle runs without manual intervention.
What This Means in Practice
Once the warehouse is in production, the data model remains the single point of control. When a source schema changes, the source model is updated (through reverse engineering or manually), mappings are reviewed, and the affected pipelines are regenerated. The load logic for every impacted dimension and fact table updates automatically because it is derived from the model, not maintained independently.
When business requirements change (a new SCD strategy on an attribute, an additional dimension, a restructured fact table), the change is made in the model and the downstream load behavior follows. There is no need to locate and edit individual pipelines or rewrite stored procedures. The model-driven approach centralizes maintenance in one place.
The agentic AI continues to be useful during ongoing operations, not just initial setup. Users can ask the AI to modify the model, adjust SCD configurations, add new entities, or investigate pipeline behavior through conversation. The same collaborative workflow that accelerates initial warehouse delivery also accelerates change management over the life of the warehouse.
For teams building and maintaining enterprise warehouses against SQL Server, PostgreSQL, Oracle, or Snowflake, the practical outcome is a faster path from requirements to production, a lower maintenance burden as schemas evolve, and a clear separation between what the warehouse should do (defined in the model) and how it does it (generated by the platform).