[NEW] Database Replicators
Table Replicators copy tables from your own database into your Daasity warehouse on a recurring schedule. You set them up yourself: choose the table, pick your columns, filter the rows, decide how the data loads, and test the query before it runs. No developer and no support ticket required.
Supported sources and destinations
Azure
Snowflake
BigQuery
Redshift
SQL Server
BigQuery
MongoDB
MySQL
NetSuite
PostgreSQL
Redshift
Salesforce Service Cloud (SOQL)
Snowflake
How Table Replicators are organized
Two things work together:
A Data Source is the parent. It stores your database credentials and connection details: host, port, username, password, and so on.
A Table Replicator is a child of a Data Source. Each one replicates a single table from that database into your warehouse.
One Data Source can have as many Table Replicators as you need.
The Data Connections page is being retired. Manage your Data Sources from the Integrations page instead.
The Data Source page
Open a Data Source to manage everything about it. You'll find its connection details, a Test Connection button, and a Table Replicators section listing everything running on it.
Name
The Table Replicator's name
Destination Table
The schema and table in your warehouse
Status
Whether it's active
Action
How the data loads
Last Refreshed
When it last ran
Next Refresh
When it runs next, or Disabled if no workflow is scheduled
Select any row to open that Table Replicator.
Test Connection
Test Connection confirms Daasity can still reach your database with the saved credentials. It checks the connection only, not any individual query.
If it fails you'll see the actual error, such as an unreachable host or rejected credentials, rather than a generic message.
Run Test Connection first whenever several Table Replicators on the same Data Source fail at once. That pattern almost always points at the connection rather than the queries.
Creating a Table Replicator
From the Data Source page, choose Create Table Replicator. Work through the sections in order.
Step 1: Name it
Give it a name that identifies what it's extracting. This is a display label only and you can change it later.
Step 2: Select your source
Under Connection Information, confirm the Datasource. If you started from a Data Source page it's already filled in.
Under Source Table Information, choose your Source Schema and Source Table from the dropdowns. Both are read live from your database, so you never type a schema or table name by hand.
NetSuite works differently. There's no schema step. Daasity retrieves the tables your NetSuite role has permission to see, so the list reflects that role's access. If a table you expect is missing, check the role's permissions in NetSuite.
Loading tables and fetching columns on NetSuite can take several minutes depending on your account size. A counter shows elapsed time while it connects. Leave the screen open until it finishes.
Step 3: Choose your columns
Select Fetch Columns. Daasity reads the table's structure and lists every column it found.
Include
Tick to replicate this column
Source Column Name
The column name in your database
Destination Column Name
What it will be called in your warehouse. Pre-filled, and editable
Data Type
How the value is stored in your warehouse. Pre-filled from the source, and changeable
Tick the columns you want, then rename any destination columns you'd like to change. If two destination names end up identical, Daasity flags the duplicate so you can rename one before continuing.
Data Type lets you land a column as something other than its source type. The options are Boolean, Currency, Date, Decimal/Float, Integer, Percentage, String/Text, and Timestamp. Most of the time the pre-filled value is right. Change it when a source column is stored loosely, for example a price held as text that you want as Currency, or a rate held as a decimal that you want as Percentage.
Selecting Fetch Columns again resets your custom destination names back to the defaults. Do your final fetch first, then edit names.
Because you select columns explicitly, the query Daasity runs names each column instead of using SELECT *. Adding a new column to your source table no longer breaks the extraction.
Step 4: Filter your data
Open Data Filtering and choose how to limit the rows you pull. Four tabs cover the common cases.
Filter by Date. Filter the result set on a date column you choose. The lookback window is configured separately.
Filter by Last Date. Pull only rows newer than what you already have. Choose a date field in your source table and the matching field in your destination table. Daasity finds the maximum value already loaded and makes it available as {{max_date}} in your query. Useful for tables where you only want the most recent records on each run.
Filter by Last Index. The same idea using a number instead of a date. Best for tables with an auto-incrementing ID, typically a primary key. Daasity pulls records with an ID greater than the highest one already in your destination table.
Advanced. Write the complete SQL statement for the extraction yourself.
Need something the tabs don't cover? Additional custom filtering lets you add your own logic to the WHERE clause without writing the whole query.
The Generated SQL Query panel shows exactly what Daasity will run, updating as you make changes. Check it before moving on.
Step 5: Choose how the data loads
Open Data Loading and pick one of three actions.
Full Data Replacement
Deletes all existing records in the destination table, then loads the data fresh.
The source table is small, you don't need historical data, or you can't define a primary key on the source.
Update/Insert
Keeps existing records, adds new ones, and updates records that already exist.
You're loading incrementally and need to capture changes to records already loaded. Requires a sync key.
Append
Keeps existing records and adds new ones only. Records already loaded are never updated.
You're loading incrementally and don't need to capture changes to existing records.
Setting the sync key
A sync key tells Daasity which rows are the same row across runs. It's required for Update/Insert and optional for the other two actions.
Under Select your replication key type, choose either:
Individual key - primary key, then pick the single column that uniquely identifies a row, or
Compound key - multiple columns, then pick the columns that are unique in combination.
Current sync key shows what's in effect. It reads (no columns selected) until you choose, then shows your columns in brackets, for example [categoryid]:[description]:[id].
Choosing the wrong columns here is the most common cause of duplicate or missing rows later. Sync keys covers how to pick them.
Sources with no unique key
Some tables have no reliable way to tell one row from another. A NetSuite source without a primary key is the common example.
For these, tick Do not remove duplicates under Advanced. Daasity will load every row as it arrives instead of trying to match rows against a key that can't identify them.
Use this only when you've confirmed there's no usable key. Turning it on when a valid key exists will produce duplicate rows.
Step 6: Set the destination
Destination Schema
The schema in your warehouse that will contain the table
Destination Tablename
The table name to load the data into
Both are required. Daasity handles the destination primary key and sync key for you, and shows them on the Table Replicator's detail page once it's created.
Select Create to finish.
The Table Replicator detail page
After you create a Table Replicator you land on its detail page, and this is where you return to check on it. Open one by selecting its name from the Data Source page.
A new Table Replicator shows No data has been loaded yet. Please check back tomorrow. The first load runs on the next scheduled refresh. After that, the banner tells you how long ago it last refreshed.
The page shows:
Details
Refreshed On, Datasource, source schema and table, destination schema and table, destination primary key and sync key
SQL Query
The exact query Daasity runs, with every column named
Data Load Action
Which action is in effect and what it does
Fields used to generate the Sync Key(s)
The columns making up the sync key
Sync Key Generation
How those columns become a key
Test Query
Run the query on demand
Field Mappings
Every column being replicated, its destination name, its format, and whether it's part of the sync key
Configured Workflows
Which workflows run this Table Replicator
Test Query
You can run a Table Replicator's query against your database at any time, before you save it or long after it's running. Use it to confirm a new one works, and to diagnose one that stopped returning what you expect.
Open the Table Replicator's detail page.
Find the Test Query card.
Select Run Test Query.
Enter any parameters the query uses: start date, end date, max ID, max date.
Submit. Daasity connects to your source database and runs the query, limited to 5 rows.
Results show the row count, execution time, and column names.
Select Download Results (CSV) to keep a copy.
If the query fails you'll see the actual error your database returned, not a generic failure. When the error points at the connection rather than the query, run Test Connection on the Data Source.
Test Query is not available on MongoDB or Salesforce Service Cloud.
Sync keys
A sync key is how Daasity tells whether an incoming row is new or an update to a row it already has. You set it when you create a Table Replicator, and you can change it later.
Update/Insert
Required. At least one column, or the Table Replicator won't save.
Append
Optional. Every row is inserted regardless.
Full Data Replacement
Optional. The table is emptied and reloaded on each run.
How a sync key works
Daasity takes the columns you choose, combines them, and generates an MD5 hash. That hash becomes the row's identity. Two rows with the same values in those columns produce the same hash, so Daasity treats them as the same row.
Columns are always combined in alphabetical order, so the order you pick them in doesn't matter and the result is identical on every run.
What makes a good sync key
Pick columns whose values never change. If a value in your sync key changes on the source row, Daasity generates a different hash and treats it as a brand new row. You get a duplicate instead of an update. IDs and creation timestamps are safe. Status fields, quantities, and last-modified dates are not.
Pick the narrowest combination that's actually unique. Too few columns and separate rows collapse into one, overwriting each other. Too many and rows that should match stop matching.
Remember these are destination column names. If you renamed a column while mapping, the sync key uses your new name. Check the key after any renaming.
Checking the key on an existing Table Replicator
Open the detail page. Fields used to generate the Sync Key(s) lists the columns, and the Field Mappings table marks each one Yes under Used in Key?. On a wide table that's the fastest way to see what's in the key.
Changing a sync key
Choose Edit. The Sync Key section lists every destination column with a checkbox, its name, and its type. Tick the columns you want and Current sync key updates to match.
A different set of key columns produces a different hash for every row. The rows already in your destination table were written with the old key, so they can no longer be matched against incoming rows. Left alone they would sit there permanently while every incoming row inserted alongside them as a duplicate.
Daasity won't let that happen. When you save a changed sync key you'll get a Sync Key Changed, Action Required dialog asking what to do with the existing rows:
Copy and truncate
Your existing data is copied to a table named with a _backup_ suffix and timestamp, then the original is emptied. This is the default.
Truncate the destination table
The table is emptied. The existing data is not kept.
Type the confirmation text, then choose Confirm & Save.
Take the copy option unless you're certain you don't need the old rows. The backup table stays in your warehouse, so you can compare against it after the next run and drop it once you're satisfied.
The table refills on the next scheduled run, so expect a gap between saving the change and having complete data again. On a large table, plan the change for a time when that gap is acceptable.
Editing a Table Replicator
Open a Table Replicator and choose Edit. Three things can change:
Name
Action, the data load action
Sync Key
Everything else is fixed when you create it: the source table, the columns, the filtering, and the destination. To change any of those, create a new Table Replicator through the guided flow and delete the old one. It takes a couple of minutes and validates as you go.
Changing the sync key changes every row's identity, so the rows already in your destination table can no longer be matched. Daasity will ask you what to do with them before saving. See Changing a sync key.
Deactivating a Table Replicator
Deactivating pauses a Table Replicator without deleting it. Its scheduled workflows stop running it, and your destination table and all the data already loaded stay exactly as they are. The configuration is kept, so you can pick it back up later.
Open a Table Replicator, then choose Deactivate from the menu next to Edit. To start it again, choose Activate. It resumes on its next scheduled run.
Deactivate when you want to stop a table from refreshing for a while, for example while you work on the source table or while you investigate a data issue. To remove it entirely, delete it instead.
If Daasity support deactivated a Table Replicator for you, you won't be able to reactivate it yourself. You'll see a message saying it was deactivated by support. Email support@daasity.com to have it turned back on.
Deleting a Table Replicator
Open a Table Replicator, then choose Delete from the menu next to Edit.
Deleting removes the Table Replicator. What happens to the data it already loaded is up to you.
Keep the data in the destination table
The Table Replicator is removed. Your table and its data stay in the warehouse, no longer updated.
Drop the destination table
The Table Replicator is removed and the table is permanently deleted from your warehouse.
Rename the destination table
The Table Replicator is removed and the table is renamed with a _deleted_ suffix and timestamp, so the data stays recoverable.
To confirm, type the destination table name exactly as the dialog shows it, including the schema. For example netsuite_odbc_prod.accounts, not accounts.
Check what reads from the destination table before dropping it. Dashboards, dbt models, and downstream queries pointing at a dropped table will break.
Troubleshooting and recommendations
Performance
Extraction time depends on the size of the source table and the complexity of the query.
Avoid unscoped Full Data Replacement or Update/Insert operations on large tables. Filter them.
Errors
connection reset by peer: increase the timeout settings on your source database.killed: contact support@daasity.com.Update/Insert requires at least one sync key column: choose a replication key type and select your columns.Several Table Replicators on the same Data Source failing together: run Test Connection on the Data Source before anything else.
Data
Fewer rows in your destination than your source: different rows are producing the same sync key and overwriting each other. Add columns until the combination is unique.
Rows that should update are inserting as new instead: a sync key column's value changes between runs. Rebuild the key from columns that don't change.
Every row has an identical sync key: no columns are selected, so every row hashes the same empty value. Set a key with at least one column.
BigQuery Star Tables are not directly supported.
JSON fields are not supported.
Aliases work for limited transformations as long as the alias name matches an existing column.
Setup
Failed to fetch columns: Daasity couldn't read the table's structure. Run Test Connection on the Data Source, then confirm the database user has permission to read that table.Custom destination column names reverted to defaults: selecting Fetch Columns again resets them. Re-apply after your final fetch.
A table you expect is missing from the dropdown: refresh the dropdown. On NetSuite, check the role's table permissions.
Schema, table, and column lists load in the background, so you'll see Connecting to database, Fetching column metadata, or Processing table list while Daasity works. Large tables and NetSuite accounts take longest. Leave the screen open.
Next Refresh shows Disabled: no workflow is scheduled for that Table Replicator. Check Configured Workflows on its detail page.
Still stuck? Email support@daasity.com with the Table Replicator name and, if you have one, the CSV from Test Query.
Last updated
Was this helpful?