Setting up and maintaining data pipelines is hard. With the rising adoption of SaaS applications, the ability for a data pipeline to adapt to changing source schemas is increasingly important.
With the new SQL Server replication feature, mismatched schemas between the data source and SQL Server will be automatically reconciled. For you, it means greater convenience and less worry because your data pipelines will be automatically and efficiently maintained. Even as the schema changes in the source system, you can rest assured knowing that data will not be lost.
Watch this video to see the SQL Server replication feature in action:
Below are some examples of automations that companies are implementing with the SQL Server connector.
Faster initial setup and load
Setting up a new data pipeline to SQL Server usually requires an infrastructure manager or someone in IT to set up the object definition for each object. This can take weeks of work, especially if you have to set up hundreds of objects, and mistakes can be made if done manually.
The SQL Server replication feature enables you to set up the object definitions automatically in SQL Server based on the schema in the source application. For apps with a ton of objects, like NetSuite, this feature can a ton of effort and headache by automating the table creation for each object as well as performing the initial data load at the same time.
Set it and forget it
Once a data pipeline is set up, maintaining the pipeline is an ongoing challenge. If source systems are fairly static, the workload is manageable. With the rise of SaaS applications, however, source schemas are in constant flux. Admins for apps like Salesforce are constantly adding or changing fields, which can break an integration or cause data to be missed until the pipeline is updated.
Now you never have to worry about lost data again. As new fields are added in Salesforce, the replicate rows action automatically detects schema changes and updates the corresponding SQL Server tables. It also deduplicates rows and ensures the two systems remain in sync. Also, to safeguard sensitive data, you can choose which fields you want to exclude from the data load, ensuring that the data of the company will not be accidentally divulged.
How it works:
1) Initial setup for the data pipeline
Initially set up the data pipeline with the “Replicate rows” action in the SQL Server connector in the Action step. Note that the “Replicate Rows” action processes data in batches.
When you run the recipe job for the first time, Workato automatically reads the object definition (e.g. contact object in Salesforce in the example above). It then creates a table in SQL Server based on the following information you provide in the Setup step below:
- Table Name: A unique table name that will be the destination for the replicated data. If there is no existing table, Workato automatically creates the table with the right field types.
- Unique Key: The unique key in the table that will be used to determine new/updated data for UPSERT operations.
- Flatten Columns: When replicating an object that may have a hierarchical data structure (e.g. address columns for an account object), you have the option to replicate the data as is or flatten the structure to create a separate column for each element.
- For example, when you flatten an Address object: Address Line 1, Address Line 2, City, State, Zip, and Country will be created as separate columns.
2) Capturing changed data and schema
- When changed data is detected in the data source, Workato first inspects the new object definition against the existing SQL Server table schema.
- When mismatches in source schema object and the table definition in SQL Server are detected, the recipe job will alter the table definition to match that of the source object.
- Next, the recipe job will sync the data for the updated object definitions to ensure no data or schema changes are lost.
Learn more about Workato’s Replicate action here.