Connect a Database and Control Writes

What you will do

Connect a relational database, bring a table into the Data Hub, and decide how strictly pipelines may write back to it. The steps are the same for MySQL, SQL Server and Redshift apart from the connection fields.

Before you start

• Signed in to DLWAY
• A database you can reach and a user for it. A read-only user is best for browsing.
• If the database is on a private network, the DLWAY server must be allowed to reach it; a hosted Studio cannot reach localhost on your machine.

Step 1: Open the Connections screen

In the sidebar open the Data Hub, then Database connections. You will see the connector catalogue.

Step 2: Create a connection

Choose PostgreSQL. Fill in a name, host, port (5432 by default), database, schema (public by default), user and password. Switch SSL on if your server requires it. Press Test. The credentials are encrypted on the server and never shown again.

Step 3: Browse

Save the connection. The tables, views and collections are discovered and listed. Select a table to preview its first rows. Use Re-discover tables if the schema changes.

Step 4: Import it two ways

1. Import the table as Live. The dataset is bound to the connection; its preview is limited and marked as such.
2. Import the same table as a Snapshot. A complete copy is stored as a dataset version. Snapshots read in batches and stop at 25,000 rows; a truncated copy says so.

Open the Transform window on each. The live one is refused, because transforms need a full copy; the snapshot works.

Step 5: Set a write policy

Reading is always allowed; writing is off until you turn it on. Open the connection's Writes settings and choose a mode:

• Dry run first is a good default. A pipeline's write is tried in a transaction and rolled back, and you see exactly what it would add, change and remove.
• Ask each time pauses the run for your approval.
• Type to confirm makes you type the table name.
• Auto writes without asking, only to the tables you list and only up to a row limit. Use it for scheduled runs nobody watches.

Set the mode and, for Auto, the allowed table list and maximum rows. Save.

Step 6: Use it in a pipeline

In Pipelines, add Refresh from Database to read only changed rows, then Upsert Rows, and finish with Write to Database. On the first run with Dry run first, review the preview before applying it. After the run, the connection's write log lists the table, the kind of write, whether it was a dry run, how many rows and who approved it.

Safety checklist

• Use a database account that can only touch the tables the pipeline writes.
• Prefer Dry run first or Ask each time until a pipeline has behaved correctly many times.
• Treat Auto as a scheduled-job setting, not an everyday one.

Related

Database connections, Pipeline step reference.