Importing Initial Metadata

When getting started with Agile Data Engine, you will need to bring in initial metadata about the entities you plan to manage. In this context, metadata refers to entities in your source systems, such as tables, their attributes, data types, and descriptions.

The approach for acquiring this metadata depends on your organization’s development practices and the available data sources. This guide outlines the common scenarios to help you import entity metadata into ADE efficiently.

Recommended metadata format

The entity import feature in Agile Data Engine allows you to bring in metadata for any type of object, including data warehouse and dimensional model entities. However, as a starting point, it is recommended to import source entities only.

Recommended values for initial metadata imports:

Field	Value	Description
`entity_physical_type`	`METADATA_ONLY`	Source metadata should be imported as METADATA_ONLY type entities. This approach makes it easy to map source attributes first into staging tables, and then into further downstream entities.
`entity_type`	`SOURCE`	Identifies the entity as a source system object.
`entity_dv_source`	`source_system_name`	Define the source system name in CONFIG_SYSTEMS and set it here for the SOURCE entity.
`entity_schema`	`src`	Use e.g. `src` or the actual schema name from the source system.

See Import entities for the entity import CSV format.

If needed, you can extend or adjust attribute data type mappings through the CONFIG_DATATYPES configuration package, including defining importable types for different DBMSs and default precision, scale, and length values.

Options for importing metadata

The following diagram illustrates the common pattern for loading data from source systems into a data warehouse:

Depending on the use case and your organization's practices, metadata for Agile Data Engine entities can be retrieved from several sources:

From source system, if the source is a relational database:
1. Metadata queries can be executed manually against the database.
2. Alternatively, metadata extraction queries can be built into your data integration tool.
From source files stored in cloud storage, where file structures and schemas define the metadata.
From the target database (data warehouse), if data ingestion is handled outside of ADE and the existing data structures serve as the source of truth.

Whenever possible, use the actual schema from the source system as the basis for metadata in Agile Data Engine. This ensures accuracy in table structures, data types and naming, reducing the risk of mismatches.

Detecting metadata from files (e.g., CSV or Parquet) is a secondary option. File-based sources may lack strict typing or contain incomplete information, making them less reliable than direct source system schemas.

1. From source system

In this approach, the source is a relational database from which data is extracted to cloud storage using an integration tool. To ensure accurate schema representation in the target database, the recommended method is to directly query the information schema of the source system.

To import metadata from a source database:

Run an SQL query against the source system’s information schema to extract table and column metadata.
Export the query results as a CSV file, following the entity import CSV format.
Import the CSV into ADE using the Import Entities feature.

Since Agile Data Engine is not directly connected to source systems, metadata must be imported using CSV files.

Optionally, you could implement metadata extraction into your data integration tool to further automate the process.

Examples

Example metadata queries for various source DBMSs are available in GitHub.

See example video about the process:

https://youtu.be/lcgqXflBcQ8

2. From source files

In this scenario, there is no direct access to source systems, and metadata must be derived from files stored in cloud storage or downloaded locally.

To extract metadata from source files:

Use the Import from data source feature:
Navigate to the package where you want the entity to be imported into and select Package Entities → Import from data source.
Choose a file, confirm the file type and set a number of rows to sample from the file.
Preview the import entity and attribute details. Make changes to entity details, attributes and data types as needed.
Confirm the import.

Examples

If you need to customize the source file profiling process, see these example scripts in GitHub.

See example video about the process:

https://www.youtube.com/watch?v=s06L-o0NWks

3. From the target database

In this scenario, data has already been ingested into the data warehouse, and Agile Data Engine is used for downstream data modeling and transformation. For example, you might be using an external tool to extract data from source systems that also automatically manages the creation and evolution of staging tables in the data warehouse.

To populate metadata definitions in ADE based on existing schemas, you can query the information schema of the target database.

To import metadata from the target database:

Run an SQL query against the target database to retrieve table and column metadata.
Export the query results as a CSV file, following the entity import CSV format.
Import the CSV into ADE using the Import Entities feature.

Examples

Example metadata queries for various target DBMSs are available in GitHub.