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