Skip to main content

Send data from a data warehouse

Instead of pushing data through the DataHub API or uploading CSV files, you can have DoiT pull metrics from a view in your Snowflake data warehouse. DoiT queries the view on a recurring schedule and ingests the rows as DataHub events, so your business metrics stay up to date without manual uploads or custom API integrations.

Note

How often DoiT queries the view, and how far back each run reads, are set by your DoiT account team and may change.

Before you begin​

  • You must have an existing Snowflake connection with DoiT. This integration reuses the DoiT user and DoiT role created when you connect your Snowflake account.

  • We configure this integration on your behalf. Contact your DoiT account team to set it up for your Snowflake account.

Create the view in Snowflake​

Create a view in your Snowflake account that DoiT can query. By default, DoiT reads from DOIT_DATAHUB.PUBLIC.DATAHUB_METRICS, but you can place the view in any database, schema, and view name as long as you inform your DoiT account team of the location.

The view must contain the following columns:

ColumnTypeRequiredDescription
EVENT_TIMETIMESTAMP_NTZYesThe UTC timestamp of the event.
METRIC_TYPEVARCHARYesThe type of the metric. cost and usage map to the basic metrics in Cloud Analytics reports. Other values are also accepted. This is the same metric type used in the DataHub API and CSV uploads.
METRIC_VALUEFLOATYesThe numeric value of the metric. Rows with NaN or infinite values are skipped.
LABELSOBJECTNoCustom key-value dimensions. Each key becomes a DataHub label. See Dimension columns.
PROJECT_LABELSOBJECTNoProject-level labels. See Dimension columns.
SYSTEM_LABELSOBJECTNoSystem labels. See Dimension columns.
FIXED_DIMENSIONSOBJECTNoFixed dimensions that join business metrics with cloud billing data. Only DataHub's known fixed keys are accepted. See Dimension columns.
EVENT_IDVARCHARNoA stable identifier for the row, up to 256 characters. Longer values are skipped. See Event identity.

Additional columns beyond the ones listed above are ignored. If a required column is missing, the ingestion run fails. Rows with a null EVENT_TIME, METRIC_TYPE, or METRIC_VALUE are skipped.

Dimension columns​

Dimension columns are optional OBJECT columns that attach metadata to each event. Each column corresponds to a different DataHub dimension type:

  • LABELS: Custom key-value pairs that appear as labels in Cloud Analytics reports. Keys are case-sensitive. Use this column for any custom dimensions, such as team names, environments, or feature flags.

  • PROJECT_LABELS: Project labels. Keys are case-sensitive.

  • SYSTEM_LABELS: System labels. Keys are case-sensitive.

  • FIXED_DIMENSIONS: Fixed dimensions that join business metrics with cloud billing data for unit economics. Only these keys are accepted, and they are lowercased automatically: billing_account_id, cost_type, country, is_marketplace, operation, pricing_unit, project_id, project_name, project_number, region, resource_global_id, resource_id, service_description, service_id, sku_description, sku_id, and zone. An unknown key causes the ingestion run to fail. The is_marketplace key accepts only true or false. See Allowed keys for fixed dimensions.

The same key can appear in different dimension columns without conflict. For example, region can be set as both a label and a fixed dimension, and each is stored separately in DataHub. Within a single column, each key appears once. In all columns, string values are stored unchanged, numbers are stored as their exact text representation, and null values are dropped.

Example view​

CREATE OR REPLACE VIEW DOIT_DATAHUB.PUBLIC.DATAHUB_METRICS AS
SELECT
    usage_date::TIMESTAMP_NTZ         AS EVENT_TIME,
    'usage'                           AS METRIC_TYPE,
    call_count::FLOAT                 AS METRIC_VALUE,
    OBJECT_CONSTRUCT(
        'team', team_name,
        'environment', env
    )                                 AS LABELS
FROM my_database.my_schema.api_usage;

To include fixed dimensions that join with cloud billing data, add a FIXED_DIMENSIONS column:

CREATE OR REPLACE VIEW DOIT_DATAHUB.PUBLIC.DATAHUB_METRICS AS
SELECT
    usage_date::TIMESTAMP_NTZ         AS EVENT_TIME,
    'usage'                           AS METRIC_TYPE,
    call_count::FLOAT                 AS METRIC_VALUE,
    OBJECT_CONSTRUCT(
        'team', team_name,
        'environment', env
    )                                 AS LABELS,
    OBJECT_CONSTRUCT(
        'project_id', gcp_project,
        'region', cloud_region
    )                                 AS FIXED_DIMENSIONS
FROM my_database.my_schema.api_usage;

Grant access to DoiT​

Grant doit_role the permissions it needs to read the view and run queries:

GRANT USAGE ON DATABASE DOIT_DATAHUB TO ROLE doit_role;
GRANT USAGE ON SCHEMA DOIT_DATAHUB.PUBLIC TO ROLE doit_role;
GRANT SELECT ON VIEW DOIT_DATAHUB.PUBLIC.DATAHUB_METRICS TO ROLE doit_role;

If your configuration uses a warehouse other than DOIT_WAREHOUSE, also grant usage on that warehouse:

GRANT USAGE ON WAREHOUSE <your_warehouse> TO ROLE doit_role;
Warning

Make sure to sanitize your data, for example, mask personally identifiable information (PII), before exposing it through the view.

Data ingestion​

After your account is configured, DoiT queries your view on a recurring schedule and sends the rows to DataHub as events.

  • Schedule: Your DoiT account team sets how often DoiT queries the view.

  • Lookback window: Each run reads rows starting from the UTC day before the last successful run, within the lookback period set for your account. The first run reads the full lookback window.

  • Data availability: After a successful ingestion run, the data is available in the DoiT console within 15 minutes.

Event identity​

DataHub uses the event ID to identify and deduplicate events. Each row from your view maps to one DataHub event, and the event ID determines whether a row creates a new event or updates an existing one.

  • With EVENT_ID: When your view provides an EVENT_ID column, that value is used as the DataHub event ID. This gives you control over upsert behavior. Re-sending a row with the same EVENT_ID and a new METRIC_VALUE replaces the previous event. You can later delete those individual events with the DataHub delete API, using the same EVENT_ID values. See Corrections and deletions.

  • Without EVENT_ID: When the column is absent, DoiT generates a stable ID from the combination of your customer ID, dataset, warehouse source, event time, metric type, and dimensions (including each dimension's type, so the same key under different dimension types produces distinct identities). The generated ID does not include the metric value, so correcting a value for the same time, metric type, and dimensions replaces the original event.

Note

EVENT_ID must be unique across all your DataHub data, not only within this view or dataset. An EVENT_ID that matches an event ID in another dataset, CSV upload, or API call replaces that event in reports.

Duplicate rows​

Rows within the same ingestion run that produce the same event ID, either from repeated EVENT_ID values or identical generated identities, are deduplicated. The first occurrence is kept and subsequent duplicates are dropped.

Corrections and deletions​

Ingestion is upsert-only. To correct data, update the row in your view and the next ingestion run replaces the event (as long as the event identity stays the same).

Rows removed from the view are not automatically deleted from DataHub. To delete events, use the DataHub delete API (POST /datahub/v1/events/delete), specifying either the event IDs or a time range. Only events older than 90 minutes can be deleted.

Limitations​

  • Only Snowflake is supported as a data warehouse source.

  • You cannot configure this integration in the DoiT console.

  • The FIXED_DIMENSIONS column accepts only DataHub's known fixed dimension keys. An unknown key causes the ingestion run to fail. See Dimension columns for the full list of accepted keys.

  • An outage longer than the lookback period can leave a gap in ingested data. Contact your DoiT account team if you need the missed dates re-ingested.

  • A view that returns more than 1,000,000 rows in one run causes the run to fail.

Last updated on