SQL and Snowflake Consumers

Connection properties#

Taking Microsoft SQL as representative:

The five queries#

This is what distinguishes a SQL consumer from the others: you control the SQL it writes. Each consumer exposes several statements, one per kind of content:

You only need the ones matching what you are publishing. A consumer feeding a warehouse with numbers needs the Value Query and probably nothing else.

Infohub can generate a starting set of these statements for you, which saves writing them from scratch and gives you the correct shape and parameter names.

This one habit prevents the most common SQL consumer problem. It also gives you a chance to review what the statement actually does before it starts running unattended against a production warehouse.

Design the destination for restatement#

Infohub's values can change after the fact. A backfill, a corrected calculation, or a late-arriving source value causes the consumer to send that period again.

The Value Query must therefore be idempotent. Sending the same period twice must update, not duplicate.

Approaches, in order of preference:

A merge or upsert. SQL Server's MERGE, PostgreSQL's INSERT ... ON CONFLICT DO UPDATE, MySQL's INSERT ... ON DUPLICATE KEY UPDATE. The destination table needs a unique key over the measure identity and the period.

Delete then insert for the period being written. Simple and correct, more expensive.

Insert with versioning, keeping every version with a load timestamp. Appropriate where the destination is an audit store and needs the history of restatements, and where storage is not a concern.

What does not work is a plain insert into a table with no unique constraint. It appears to work perfectly, and accumulates duplicates that nobody notices until someone sums a column and gets double.

Measure identity#

The destination needs to know which measure each value belongs to. Consumers let you choose what to send — a measure name, a measure ID, a time scheme name, or a combination.

Measure ID is stable across renames, meaningless to a human reading the warehouse, and not portable between Infohub instances.

Measure name is readable and breaks when someone renames a measure — which is otherwise a safe operation in Infohub.

For a warehouse that people will query directly, names are usually worth the renaming risk, and the mitigation is a naming convention plus a rule that renames are coordinated with the destination's owner.

Whatever you choose, write it down. "Is the key the name or the ID?" is the question nobody can answer a year later, and by then a rename has created what looks like a new series alongside the old one.

Snowflake#

Snowflake behaves like the other SQL consumers with two commercial differences worth planning around.

Compute costs while it runs. A warehouse wakes to service a statement and costs while awake. Many small writes can keep one awake continuously. Prefer fewer, larger sends, and discuss the pattern with whoever owns the Snowflake account before deploying.

Loading patterns differ from transactional databases. Snowflake is optimised for bulk loads rather than row-by-row writes. A consumer sending frequent small batches is working against the grain. Where volume is significant, staging into files that Snowflake loads is more idiomatic — that is a conversation with the destination's team rather than an Infohub setting.

Integrated security#

Leaving username and password blank uses Windows integrated security, which authenticates as the Flow Bootstrap service account once deployed — not as you.

The service account needs write access to the destination, unlike a data source where read is enough. Ask for exactly the rights the statements need: insert and update on the target tables, and nothing more. A consumer account with broad rights on a corporate warehouse is a reasonable thing for a database administrator to object to.

Volume and scheduling#

Do the arithmetic before deploying. Measures × periods × send frequency gives you rows per day, and it is frequently larger than people expect — a thousand measures at hourly intervals is 24,000 rows a day, and a backfill of a year is millions.

Where that matters:

• Keep Maximum Concurrent Connections modest, particularly against a shared warehouse.
• Set a realistic Timeout so a slow statement fails rather than holding a connection.
• Anticipate backfills. If you are about to correct a year of history, tell the destination's owner.

Diagnosing#

See also#

• What a Data Consumer Is
• Consumer Reference
• Multi-Site Architecture
• SQL Databases