How does Assembled's Snowflake integration work?

Assembled can share your workforce management data into Snowflake so you can build your own reports and dashboards on top of it. This article covers what the Snowflake integration includes, what it doesn't cover, and how to correctly retrieve report data if you're pulling it through the Reports API.

How does Assembled data get into Snowflake?

Assembled offers a direct data share for eligible accounts: a set of secure views in your own Snowflake account that reflect your company's analytics data. The share includes three views:

• ASSEMBLED_METRIC_MAPPING: documents every metric available in the share, including a title and description, and, for metrics that are calculated from other metrics, the fields used in the calculation.
• ASSEMBLED_METRICS: values for metrics that are computed directly from your data.
• ASSEMBLED_METRICS_DERIVED: values for metrics that are calculated from other metrics, such as adherence_percentage.

Data in these views is broken down by hourly interval, agent, queue, channel, and metric.

You don't need the data share to get Assembled data into Snowflake. Many teams instead call the Assembled API from their own script or ETL tool and load the results into a Snowflake table they manage. This works for any account, but you're responsible for how you call the API and how you handle asynchronous report requests (see below).

What doesn't the Snowflake integration do?

The data share only includes the metrics listed in ASSEMBLED_METRIC_MAPPING. It doesn't include scheduled events, forecasts, or activities. If your use case depends on future-looking data like upcoming shifts or forecasts, use the Assembled API instead.

Snowflake also doesn't run report generation for you. Whether you're using the data share or calling the API directly, report data is still produced asynchronously on Assembled's side before it's available to query.

Why do report tables show a report ID instead of the metric values I expected?

This is expected behavior, not an error. The Reports API generates reports in two steps: you request a report, and Assembled builds it in the background. Until that report finishes, the only thing available is its ID, a placeholder for the finished result.

If your pipeline writes the response of the first request straight into a table, that table will show a report ID in the column where you expected a metric value. A second call, once the report is done, is what returns the real numbers.

How do I generate and retrieve report data through the Reports API?

Use these two calls together. The example below generates an adherence report, but the same pattern applies to every report type.

1. Select POST /v0/reports/adherence and include your time range and any filters, such as start_time, end_time, interval, and channel. This request returns a reportID right away. The report itself is still generating in the background.
2. Select GET /v0/reports/{reportID}, using the ID from step 1, to check on and retrieve the report. Poll this endpoint until the report shows as finished, then read the metric values from the response.

For an adherence report, the finished result includes metrics such as scheduled_seconds_total (how long each agent was scheduled), actual_seconds_worked (how long they actually worked), and the derived metric adherence_percentage. Each metric is returned for the full time range as well as for each sub-interval you requested.

Where can I learn more?

• Reports API reference
• Adherence report reference
• POST /v0/reports/:reportType reference
• GET /v0/reports/:reportID reference