Articles in this section

See more

Smart Ingest Data Models Overview

When you set up a sync with Smart Ingest, you need to create a data model. A data model defines the data that’s being imported from your connected source. Generally, it is a data table composed of columns and rows, but it might also be a CSV or JSON file, or a SQL query.

Smart Ingest only reads data from the columns you select during the sync setup. It never imports data from columns you don’t select, and never makes changes to your source data.

Continue reading to learn more about data models and how to create them.

In this article

Data model field requirements

When preparing a data model for Smart Ingest, you need to ensure that it meets contains the fields required for the sync you are setting up.

Every data model needs to contain a unique primary key that identifies each record. This is a requirement so that Smart Ingest can keep track of records during the change data capture process and provide incremental syncs. The primary key doesn’t have to be the same as the unique identifier in Iterable.

Additional requirements depend on the sync type you are setting up. For example, a sync that updates user profiles requires a user ID or email address, while a catalog sync requires a unique identifier for each catalog item.

To learn more, read the Smart Ingest sync guide for the sync type you're setting up.

NOTE

Duplicate records in source data are skipped. To learn more, read How are duplicate records handled during a sync?.

Creating a data model for a database connection

Smart Ingest provides two different ways to create a data model when connecting to a database: with a table selector, or with a SQL query.

The modeling method you choose determines the interface that you use to define the data you want to import to Iterable.

Here is what Smart Ingest does with the data contained in your data model:

  • All rows are included in the sync to Iterable. Each row can represent a user, an event, or a catalog item. This varies by sync type.

  • All columns are available to sync as fields. Fields aren’t sent to Iterable until you use field mapping to add them to the sync.

You can change a sync’s modeling method while you are in the creation flow, but the modeling method can’t be changed after it’s been saved. If you need to change an existing sync from table selector to SQL query, for example, you must create a new sync.

Table selector

An easy-to-use interface. Use the table selector to retrieve the data tables or views from your data warehouse and select the one you wish to import.

You can select which columns to import later during the field mapping step. All you need to know is the name of the data table or view and the field name for its primary key for this step.

Table selector

SQL query

Write a SQL query selecting your data, like:

    SELECT email, first_name, last_name, user_id
    FROM customers
    WHERE email IS NOT NULL;

Using the SQL editor to create a data model can provide a highly customized data import, allowing you to do things like filtering out rows with records that don’t belong in Iterable (like inactive users), joining data tables from your data warehouse to expand the data fields you can import, building JSON object arrays, and more.

If your query doesn’t return data, you won’t be able to proceed with setup.

WARNING

SQL is an advanced modeling method that warrants some caution. When creating SQL queries, it’s possible to write computationally expensive and inefficient queries that result in processing delays each time the sync runs.

To avoid excessive slowness or other performance concerns, ask your data warehouse administrator to prepare a performant SQL query.

SQL query

Creating a data model from a file import (CSV or JSON)

Some Smart Ingest sources allow you to upload a CSV or JSON file as a data model. Sources that support this currently include:

For these sources, you begin creating a sync by providing the path to the data file during sync setup.

The data file needs to contain the same required fields as with a database connection:

  • A primary key to identify unique rows.
  • The same required fields as needed for a given sync type.

CSV requirements

To import data to Smart Ingest, CSV files must meet the following requirements:

  • Header row: The values in the header row are automatically available as column names when you set up a sync.
  • Delimiter: Smart Ingest only accepts comma-separated values. Tabs and other delimiters aren't supported.
  • Quoted values: CSVs must use double quotes (") for quoted values.

JSON requirements

To import data to Smart Ingest, JSON files must meet the following requirements:

  • The input file must contain an array at the top level.
  • Each element of the array must be an object with the same keys.
  • The keys of the array elements are automatically available as column names when you set up a sync.

Related articles