Database views as sources

A view is often where the business logic lives: the joins, the filters, the monthly roll-up. Elglide syncs a view like a table. It lands as a Snowflake table, starts on Full, and moves to Incremental once a key is found in the rows you’ve loaded. Before every incremental run, that key is checked again against the view, so a view that changes shape drops back to Full instead of merging rows on a key that no longer holds.

Picking views

Views are picked the same way as tables. On the pipeline screen the source’s objects are grouped as Tables and Views; tick the views you want and save. Views are listed for SQL Server, PostgreSQL, MySQL / MariaDB and Oracle sources. PostgreSQL materialized views appear in the Views group too.

How a view syncs

• Full — the starting point for every view except a SQL Server indexed view (below). A view can’t declare a primary key, so there is nothing to match rows on yet.
• Incremental · RowHash — once the view has a key. Each run still reads the whole view, but compares a hash of every row with the last sync and writes only the rows that changed.
• No CDC, Change Tracking, binary log or LogMiner. Those read the change record a database keeps for a table. A view stores no rows of its own, so it has no change record to read. A change to any table under the view shows up on the next RowHash run, because RowHash compares what the view returns.

See Sync modes for the full ladder.

Finding a key: Check for a Key

Open the task, then Settings → Sync Mode & Optimization. Check for a Key is available once a Full sync has loaded rows; before that the panel says so. The check looks at the rows already loaded in Snowflake, not at the source, so it adds no load on your database.

• A single column is tried first, then two columns together, then three.
• A column with any NULL is never part of a key. JSON, binary and true/false columns are skipped, and so are audit columns such as CREATED_AT or UPDATED_BY.
• Columns that look like identifiers are tried first (names ending in ID, KEY, CODE, NO, NUM), then dates and timestamps, then other whole numbers and text. Decimal amounts come last, because a revenue figure that happens to be unique today won’t be tomorrow.
• That order is what finds the key of a summary view. A monthly scorecard has one row per customer per month, so its key is CUSTOMERID + ORDERMONTH, even though the month has only a few dozen distinct values.
• Every candidate is checked exactly against all the loaded rows before it’s shown. On a very large table a sample narrows the candidates first; the one that is shown has still been checked against every row.

When a key is found the panel reads Ready for Incremental. Rows are matched on followed by the column names, with a note that the source doesn’t declare a key, so it was found in the loaded rows, and that the pipeline moves back to Full if duplicates turn up later. Promote to Incremental checks the key against the Snowflake table once more before saving it; if it’s no longer unique, nothing changes and you’re asked to check again.

The key re-check before every incremental run

A key found in loaded rows is evidence, not a rule the database enforces. So before each incremental run of a view, Elglide asks the source whether the key still holds, with one query on the view written for your database. The check fails when:

• two or more rows share the same key,
• a key column holds a NULL, or
• a key column is no longer in the view.

It doesn’t matter what caused it: new data, or an edit to the view. Typical edits that break a key:

• a new join that returns more than one row per key (one customer, several contacts),
• an INNER JOIN changed to LEFT JOIN, which can put NULLs in key columns that come from the joined table,
• a changed WHERE clause that lets duplicates through,
• a key column dropped from the SELECT.

When the check fails:

• this run loads Full, so no rows are merged on a broken key;
• the view goes back to Full and the key is cleared;
• the run log shows a warning starting [PreCheck] Merge key … rejected that says why (for example, how many distinct values across how many rows);
• a Sync method downgraded notification is raised. It’s off by default; turn it on under Account → Notifications.

Once the view is fixed, run Check for a Key again after the next Full sync.

Every key on a view is re-checked this way. On a table, only a key Elglide found is re-checked; a real primary key or unique index is enforced by the database and is trusted.

Indexed and materialized views

A SQL Server indexed view already has a key: its unique clustered index. Elglide reads it when you save the pipeline, so the view skips the Full-then-Check for a Key steps and starts on Incremental · RowHash, matched on the index’s columns. The first run still reads the whole view, to record what it holds. Every key column has to be NOT NULL in the view; if one can be NULL, the view syncs like any other view. Like every key on a view, it is re-checked before each incremental run.

PostgreSQL materialized views are listed with views. Elglide reads what the last REFRESH MATERIALIZED VIEW left in them and never refreshes them itself, so schedule the refresh before the sync. They start on Full and get their key from Check for a Key, which is re-checked before every incremental run. A unique index on the materialized view doesn’t change this: PostgreSQL never marks a materialized view’s columns NOT NULL, so the index can’t rule out NULL keys.

When the view’s definition changes

A view that gains, loses or retypes a column is handled exactly like a table: the task’s schema evolution settings decide whether the Snowflake table follows. See Schema evolution. If the edit also breaks the key, the re-check above catches it before the next incremental run.

Audits

Audit rules work on a view as on a table: the COUNT(*) rule every pipeline starts with, the built-in aggregates (SUM, COUNT(DISTINCT), MIN / MAX and the rest), and custom SQL rules, which can query the view directly. See Data quality audits.

Permissions

The source login needs SELECT on the view. Whether it also needs rights on the tables underneath depends on the database:

• SQL Server — when the view and its tables are in the same database with the same owner, SELECT on the view is enough (ownership chaining). A view that reads another database also needs the login to have access there, since cross-database ownership chaining is off by default. A view that reads through a linked server runs the remote part under the linked server’s login mapping, so that mapping needs read access on the remote side.
• PostgreSQL, MySQL / MariaDB, Oracle — views run with their owner’s rights by default, so SELECT on the view is enough. A PostgreSQL view created with security_invoker, or a MySQL view with SQL SECURITY INVOKER, also needs SELECT on the tables it reads.

Cautions

• Keep the key columns in the view. Removing one sends the view back to Full on the next run.
• RowHash still reads the whole view. It saves writes to Snowflake, not reads on the source, and the key re-check is one more query on the view. An expensive view costs the same on the source every run, so schedule it accordingly.