Data Warehouse Connector

The connector keeps a copy of your Routy tracking data in a database you control. You give Routy a PostgreSQL host and credentials, Routy creates the tables and then writes each click, page view and conversion into them as they are recorded. From there your analysts query the data with whatever your warehouse already uses, and join it to customer, finance or product tables that Routy will never hold.

It's a one-way mirror out of Routy. Nothing is read back from your database into Routy, and nothing in Routy depends on your copy being healthy, so the worst case of a broken connector is a stale copy and an issue raised in your account.

• Ten tables in a schema you nominate: affiliate_clicks, page_views, conversions, conversion_audits, conversion_types, affiliate_accounts, brands, brand_links, traffic_sources and commission_plans. The event rows carry Routy's own ids, so they join to the dimension tables the way they do inside Routy.
• Clicks and page views written in batches of up to 100 events, or every two seconds when the batch hasn't filled, on a queue dedicated to your account. A conversion is written as it changes rather than on a batch timer.
• Writes that can be repeated safely. Clicks and page views insert on a conflict key of affiliate plus event id and a duplicate is skipped, so a retry after a network blip doesn't double a row.
• A history of conversion revisions. When a conversion changes, the previous version is written to conversion_audits before the live row is updated, so you can see what a restatement replaced instead of only the figure that replaced it.
• Device, browser and operating system already resolved on the click row. No user-agent parsing your side.
• Dimension tables on their own 30-minute cycle, so a brand rename or a new link reaches your copy within half an hour while the events that reference it keep arriving in seconds.
• A health check on your database every five minutes. If it can't be reached, Routy raises a critical issue against the connector in your account, and the issue resolves itself once your database answers again.
• A connection test you can run before saving a connector and again on an existing one, which is the first thing to use after your team changes a password or a firewall rule.

Setting one up

You need a reachable PostgreSQL host and port, a database name, a username and password, and optionally a schema name, which defaults to public. SSL is a toggle and it's off unless you turn it on. Run the connection test first, then save the connector with Enabled set, and the first tables appear shortly after.

Routy owns the tables it writes to and runs its own schema migrations against your database on a 30-minute cycle, so the credentials you supply need to create and alter tables in that schema, not just insert into them. A read-only user won't work. Point the connector at a schema of its own and the rest of your database is untouched by it.

Passwords are encrypted before they're stored and are never shown again, so a rotation means supplying the new password rather than looking up the old one.

Keeping it working

Your database has to be reachable from Routy's outbound IP addresses over the public internet, which your team allows through the firewall once. A database that only answers inside a VPN can't be used; the usual way around that is a publicly reachable read replica or a tunnel your side.

If a connector stops working, it's almost always one of four things: a changed password, a moved host, a firewall rule that no longer lists Routy's IPs, or the database being down for maintenance. The connection test names which. Routy's own check runs every five minutes and raises the issue without waiting for you to notice.

There is no manual re-sync button. A gap gets filled by a reload for a date range and one data type at a time, clicks, conversions or page views, which your account manager runs for you.

What isn't in the mirror

The figures Routy pulls from your affiliate programs are not written to your database. The account-integration event tables, the daily, monthly, tracker, dynamic, brand and customer-level rows behind your program reporting, stay in Routy. A later crawl revises and retracts those rows, and mirroring that correctly needs machinery the connector doesn't have yet.

Clicks, page views and conversions are your own first-party events. Those are what the mirror holds.

PostgreSQL is the only destination that works today. SQL Server appears in the engine list, but the code path behind it isn't active, so pick Postgres. Object storage, S3 and the like, is a design on paper and nothing more.

The connector is for the point where somebody other than you needs the data. An analyst joining clicks to signup records in your own product database, a finance team reconciling conversions against what was invoiced, a model that scores traffic quality off raw click rows. None of that works through a report screen, and all of it works in SQL against tables that sit next to the data they need to join.

The alternative most teams start with is a scheduled CSV export into the warehouse. That works until it doesn't: the export is a day stale, somebody renames a column, the job fails on a Friday and nothing says so until Monday. A push mirror fails differently and more visibly. Routy writes each event as it happens and tells you within five minutes when your database stopped accepting them.

It's the wrong feature if nobody on your side writes SQL. The report builder answers most reporting questions without a database to run, and a one-off export covers the rest. Reach for the connector when the data has to live somewhere you control, either because other systems need to join to it or because your own governance rules say so.