Warehouse-native Bulk Model Management

This article helps you:

efficiently manage multiple models in batch

Bulk Model Management helps you manage your warehouse native models. Instead of managing one model at a time, you can create or edit multiple models with one configuration file.

Approach

This section provides detail about the approach of using Bulk Model Management.

Why YAML

A configuration file is a YAML-based file that enables you to define your models. YAML is a human-friendly format that's easy to read and edit in a standard text editor or IDE.

Supported operations

Warehouse-native Amplitude supports both create and update operations with the config file. If the model name exists, Amplitude updates the existing model based on that name. If the name doesn't exist, Amplitude creates a new model with that name.

A model's name value is a unique identifier. Make sure model names in your configuration file are correct to ensure updates apply to the right model

What happens to models that aren't in the config file?

Amplitude ignores and doesn't alter models that aren't in the config file.

Supported model types

Warehouse-native Amplitude Bulk Model Management supports these model types:

Events
User properties (current)
User properties (historical)
Group properties (current)
Group properties (historical)
Event properties

Create the configuration file

To get started:

Download an example configuration file from Amplitude. On the Data > Sources tab of your warehouse-native project, click Update/Create via YAML. Click Download YAML File to generate a YAML file with all your project's models.
Follow the specification below to create a new config file.

Apply the changes

Upload your configuration file to apply changes to your models. All changes commit to the database in one transaction, and roll back automatically if the process encounters an error.

Config specification

For information about creating a configuration file, see the following sections.

Field descriptions

The following specification outlines the structure and purpose of the fields in the data modeling configuration file.

DataModel object

Field	Type	Description
`apiVersion`	string	Defines the API version for the data modeling configuration.
`metadata`	object	Contains metadata used primarily to identify the target models.
`spec`	object	Specifies the desired behavior and structure of the model.

Metadata object

Field	Type	Description
`org`	string	A unique identifier for the organization.
`app`	string	A unique identifier for the app.
`integration`	string	A unique identifier for the data's integration type. Currently only support `snowflake`

Spec object

Field	Type	Description
`models`	array	A list of data models included in this specification.

Model object

Field	Type	Description
`name`	string	The name of the model which is used as the unique identifier.
`selection`	string	Specifies the selection type: either `sql` or `table`.
`type`	string	The type of model could be `event`, `current_user_properties`, `historical_user_properties`, `current_group_properties`, `historical_group_properties`, `event_properties`
`tableConfig`	object	Configuration settings for table-based models.
`sqlConfig`	object	Configuration settings for SQL-based models.

TableConfig object

Field	Type	Description
`database`	string	The name of the database being used.
`schema`	string	The name of the schema within the database.
`table`	string	The name of the table within the schema.
`isMultiEventTable`	bool	Indicates if the table contains multiple event types (default is `false`). Only `false` is supported currently.
`multiEventConfig`	object	Configuration details for multi-event tables (currently not supported).
`requiredFields`	object	Required fields necessary for defining the model based on its type.
`additionalFields`	array	Definitions and mappings for additional fields/columns in the table.

SQLConfig object

Field	Type	Description
`query`	string	The SQL query used to define the model.
`isMultiEventTable`	bool	Reserved, Only `false` is supported currently. Indicates if the result is from a table with multiple event types (default is `false`).
`multiEventConfig`	object	Configuration details for multi-event tables.
`requiredFields`	object	Fields required to define the model for a specific type.
`additionalFields`	array	An array of mappings for additional fields.

RequiredFields object (Event-Specific)

Field	Type	Description
`eventTime`	object (field mapping)	Field mapping configuration for event time, with the default label "Event Time".
`userId`	object (field mapping)	Field mapping configuration for the user ID, with the default label "User ID".

RequiredFields object (User Properties-Specific)

Field	Type	Description
`userId`	object (field mapping)	Field mapping configuration for the user ID, with the default label "User ID".

RequiredFields object (Group Properties-Specific)

Field	Type	Description
`groupId`	object (field mapping)	Field mapping configuration for the group ID, with the default label "Group ID".

FieldMapping object

Field	Type	Description
`column`	string	The name of the column in the table or SQL result set.
`label`	string	The label displayed in Amplitude, defaulting to a title-cased version of the `column` name.

Example

  1apiVersion: warehouse/models/v1
  2metadata:
  3  org: 12345
  4  app: 12345
  5  integration: snowflake
  6spec:
  7  models:
  8    - name: Play Song
  9      selection: "table"
 10      type: "event"
 11      tableConfig:
 12        database: "DB_1234"
 13        schema: "SCHEMA_3456"
 14        table: "all_events"
 15        isMultiEventTable: true
 16        multiEventConfig:
 17          eventType:
 18            column: "Event_Type"
 19            label: "play"
 20          subEvents:
 21            - play_song
 22            - purchase_song
 23          excludedEvents:
 24            - cancel_subscription
 25        requiredFields:
 26          eventTime:
 27            column: "EVENT_TIME"
 28            label: "Event Time Label"
 29          userId:
 30            column: "USER_ID"
 31            label: "User ID Label"
 32        additionalFields:
 33          - column: "prop_1"
 34          - column: "prop_2"
 35            label: "Prop 2"
 36    - name: Add Song ...
 37      selection: "table"
 38      type: "event"
 39      tableConfig:
 40        database: "DB_1234"
 41        schema: "SCHEMA_3456"
 42        table: "add_songs"
 43        isMultiEventsTable: false
 44        requiredFields:
 45          eventTime:
 46            column: "EVENT_TIME"
 47            label: "Event Time Label"
 48          userId:
 49            column: "USER_ID"
 50            label: "User ID Label"
 51        additionalFields:
 52          - column: "prop_1"
 53          - column: "prop_2"
 54            label: "Prop 2"
 55    - name: Pause Song
 56      selection: "sql"
 57      type: event
 58      sqlConfig:
 59        query: "SELECT * FROM schema_main.all_events"
 60        isMultiEventsTable: true
 61        multiEventConfig:
 62          eventType:
 63            column: "Event_Type"
 64            label: "play"
 65          subEvents:
 66            - play_song
 67            - purchase_song
 68          excludedEvents:
 69            - cancel_subscription
 70        requiredFields:
 71          eventTime:
 72            column: "EVENT_TIME"
 73            label: "Event Time Label"
 74          userId:
 75            column: "USER_ID"
 76            label: "User ID Label"
 77        additionalFields:
 78          - column: "prop_1"
 79          - column: "prop_2"
 80            label: "Prop 2"
 81    - name: Delete Song
 82      selection: "sql"
 83      type: "event"
 84      sqlConfig:
 85        query: "SELECT * FROM schema_main.delete_songs"
 86        isMultiEventsTable: false
 87        requiredFields:
 88          eventTime:
 89            column: "EVENT_TIME"
 90            label: "Event Time Label"
 91          userId:
 92            column: "USER_ID"
 93            label: "User ID Label"
 94        additionalFields:
 95          - column: "prop_1"
 96          - column: "prop_2"
 97            label: "Prop 2"
 98    - name: Current User Property Table-based
 99      selection: "table"
100      type: "current_user_properties"
101      tableConfig:
102        database: "DB_1234"
103        schema: "SCHEMA_3456"
104        table: "user_properties"
105        requiredFields:
106          userId:
107            column: "USER_ID"
108            label: "User ID Label"
109        additionalFields:
110          - column: "prop_1"
111          - column: "prop_2"
112            label: "Prop 2"
113    - name: Current User Property sql-based
114      selection: "sql"
115      type: "current_user_properties"
116      sqlConfig:
117        query: "SELECT * FROM schema_main.current_user_properties"
118        requiredFields:
119          userId:
120            column: "USER_ID"
121            label: "User ID Label"
122        additionalFields:
123          - column: "prop_1"
124          - column: "prop_2"
125            label: "Prop 2"
126    - name: Historical User Property Table-based
127      selection: "table"
128      type: "current_user_properties"
129      tableConfig:
130        database: "DB_1234"
131        schema: "SCHEMA_3456"
132        table: "user_properties"
133        requiredFields:
134          userId:
135            column: "USER_ID"
136            label: "User ID Label"
137          startTime:
138            column: "START_TIME"
139            label: "START TIME Label"
140          endTime:
141            column: "END_TIME"
142            label: "END TIME Label"
143        additionalFields:
144          - column: "prop_1"
145          - column: "prop_2"
146            label: "Prop 2"
147    - name: Historical User Property sql-based
148      selection: "sql"
149      type: "historical_user_properties"
150      sqlConfig:
151        query: "SELECT * FROM schema_main.current_user_properties"
152        requiredFields:
153          userId:
154            column: "USER_ID"
155            label: "User ID Label"
156          startTime:
157            column: "START_TIME"
158            label: "START TIME Label"
159          endTime:
160            column: "END_TIME"
161            label: "END TIME Label"
162        additionalFields:
163          - column: "prop_1"
164          - column: "prop_2"
165            label: "Prop 2"
166    - name: Current Group Properties Table-based
167      selection: "table"
168      type: "current_group_properties"
169      eventJoinField: "GROUP_ID"
170      tableConfig:
171        database: "DB_1234"
172        schema: "SCHEMA_3456"
173        table: "add_songs"
174        requiredFields:
175          groupId:
176            column: "GROUP_ID"
177            label: "GROUP ID Label"
178        additionalFields:
179          - column: "prop_1"
180          - column: "prop_2"
181            label: "Prop 2"
182    - name: Current Group Properties Sql-based
183      selection: "sql"
184      type: "current_group_properties"
185      eventJoinField: "GROUP_ID"
186      sqlConfig:
187        query: "SELECT * FROM schema_main.all_events"
188        requiredFields:
189          groupId:
190            column: "GROUP_ID"
191            label: "GROUP ID Label"
192        additionalFields:
193          - column: "prop_1"
194          - column: "prop_2"
195            label: "Prop 2"
196    - name: Historical Group Properties Table-based
197      selection: "table"
198      type: "historical_group_properties"
199      eventJoinField: "GROUP_ID"
200      tableConfig:
201        database: "DB_1234"
202        schema: "SCHEMA_3456"
203        table: "add_songs"
204        requiredFields:
205          groupId:
206            column: "GROUP_ID"
207            label: "GROUP ID Label"
208          startTime:
209            column: "START_TIME"
210            label: "START TIME Label"
211          endTime:
212            column: "END_TIME"
213            label: "END TIME Label"
214        additionalFields:
215          - column: "prop_1"
216          - column: "prop_2"
217            label: "Prop 2"
218    - name: Historical Group Properties Sql-based
219      selection: "sql"
220      type: "historical_group_properties"
221      eventJoinField: "GROUP_ID"
222      sqlConfig:
223        query: "SELECT * FROM schema_main.all_events"
224        requiredFields:
225          groupId:
226            column: "GROUP_ID"
227            label: "GROUP ID Label"
228          startTime:
229            column: "START_TIME"
230            label: "START TIME Label"
231          endTime:
232            column: "END_TIME"
233            label: "END TIME Label"
234        additionalFields:
235          - column: "prop_1"
236          - column: "prop_2"
237            label: "Prop 2"
238    - name: Event Properties Table-based
239      selection: "table"
240      type: "event_properties"
241      eventJoinField: "EVENT_ID"
242      tableConfig:
243        database: "DB_1234"
244        schema: "SCHEMA_3456"
245        table: "add_songs"
246        requiredFields:
247          eventId:
248            column: "EVENT_ID"
249            label: "EVENT ID Label"
250        additionalFields:
251          - column: "prop_1"
252          - column: "prop_2"
253            label: "Prop 2"  
254    - name: Event Properties Sql-based
255      selection: "sql"
256      type: "event_properties"
257      eventJoinField: "EVENT_ID"
258      sqlConfig:
259        query: "SELECT * FROM schema_main.all_events"
260        requiredFields:
261          eventId:
262            column: "EVENT_ID"
263            label: "EVENT ID Label"
264        additionalFields:
265          - column: "prop_1"
266          - column: "prop_2"
267            label: "Prop 2"