Ingest Instance¶

The Ingest instance configures data sources and transfers data into Ingest storage.

In this module: what the Ingest instance is · Metadata Manager · Query Tool · Tasks in Ingest instances · Incremental load

What is the Ingest instance?¶

The Ingest instance is the first point of contact for incoming data. It copies raw data from sources into a designated storage area, so you keep control over your data, automate schema changes, and support incremental loading — only new or changed records are ingested.

1 · Data source configuration. Opening the Ingest instance in Timextender Desktop lets you configure your data sources — selecting tables and fields for ingestion, identifying primary keys, configuring incremental load, and scheduling recurring transfers.

2 · Data transfer. The Ingest Server service is controlled remotely from the desktop client to move data from the source into Ingest storage, using one of many ADO.NET providers or Azure Data Factory. Incremental load ensures only new and updated rows are loaded.

3 · Raw data storage. You can configure either an Azure Data Lake or a SQL database for the initial storage of raw source data. The Ingest instance automatically handles changes in the source schema by creating a new version of the target table to match, keeping the old version by default.

Metadata Manager¶

To transfer data from a source into Ingest storage, you first establish which tables and columns exist in the source and which of them you want. That process is called synchronizing metadata, and it has three steps:

Synchronizing metadata and selecting tables¶

1. Right-click the data source in TDI and click Metadata Manager.
2. Click Synchronize Metadata.
3. Review the synchronization results.
4. Select the check box for every table you want to ingest. All of a table's columns are selected by default; unselect them as needed.
5. When you are ready to commit, click OK.
6. In the Review your selections window, clear the checkbox for any change you do not want to commit, then click Commit. This updates the working copy of the metadata.

Handling schema drift¶

The Metadata Manager lets you handle schema drift without breaking table and field mappings to the Prepare instance. When a synchronization detects metadata changes, you do the remapping in the tool.

After synchronization, review the Status column:

For each table needing review, select it and click Edit. In the window that opens you can:

• Click the Data Source Table list and choose a table, to map the table you are editing to a different table in the data source. Useful when names changed in a way the application could not fix automatically.
• Under Fields, click the Data Source Table list and choose a field name, to map that field to a different field on the source table.
• Review the detected changes in the box next to the Fields list.

See also Metadata Manager in the reference documentation.

Query Tool¶

The Query Tool lets you query the data source directly, before transferring any data — very useful during initial data discovery.

1. Right-click the data source and choose Query Tool.
2. Find your table and drag it into the query window.
3. Click Execute.
4. Review the results.

Validate a primary key¶

This query counts the rows in the table and compares that with the number of distinct primary key values — if they differ, the column is not unique.

SELECT
    COUNT(*) AS [RowCount],
    COUNT(DISTINCT [BusinessEntityID]) AS [DistinctBizEntityID]
FROM [HumanResources].[vEmployee]

Select primary keys¶

A primary key is a field, or set of fields, that uniquely identifies each record in a table. In Timextender a primary key must be identified on any table where you want incremental load. Timextender identifies primary keys automatically during Synchronize task execution on supported data sources.

1. Right-click a data source and choose Set Up Primary Keys.
2. Select a table without a primary key.
3. Click Override.
4. Identify and select the primary key.
5. Click OK, and repeat as necessary.

What is incremental load?¶

Incremental load saves significant time and resources by loading only new and changed source records. It requires a reliable primary key and an incremental value column — a field on the source table, often a date, indicating when the row was created or changed.

1 · Initial full load¶

The first execution after incremental load is configured loads every available row in the data source.

2 · Identify max incremental value¶

Timextender finds the largest incremental column value in the source and stores it for the next step.

3 · Incremental load¶

Subsequent loads transfer only the rows where the incremental value column is greater than the previously stored maximum.

Configure incremental load¶

1. Right-click the data source and choose Set Up Incremental Load.
2. Click Add.
3. Set Column > Equals (ignore case).
4. Enter ModifiedDate.
5. Check Handle Primary Key Updates. This ensures records matching a previously loaded primary key are updated in the data warehouse instance.
6. Click OK.
7. Click Refresh, then OK.

"Subtract from value" incremental load¶

Subtract from value subtracts from ruled fields. Offset rules help with date and time fields: incremental loads then update by created date if the row was altered within the interval.

• Amount decreases a value.
• Time subtracts from the last DateTime, in seconds.

1. Right-click the data source and choose Set Up Incremental Load.
2. Click Add.
3. Set Column > Equals (ignore case).
4. In Subtract from value, enter the value to decrement on each incremental load.
5. Complete the setup and click OK.

For more detail, see Incremental Load in an Ingest Instance.

Next in the course¶

Install & Configure · Portal · Prepare Instance

The exercise and quiz for this module are on the training site.