The Hidden Complexity of PointClickCare Data Relay 

What to expect before you build products, analytics, or integrations on top of PCC RRDB. 

PointClickCare Data Relay can look deceptively straightforward. PCC publishes a full backup of a reporting database, followed by hourly incremental log files delivered to a secure SFTP folder. Restore those files into SQL Server, and you have a local, read-only copy of your EHR data, ready to be queried, combined with other systems, used for analytics, connected to AI tools, or exposed to your own applications. 

At least, that is how the process looks on paper. In production, a considerable gap exists between having access to PCC Data Relay files and having reliable, trustworthy data an application can depend on. 

The challenge is not simply restoring a database. A working implementation has to account for access and credentials, backup sequencing, delayed or incomplete data, PCC’s internal ETL processes, a large and sometimes difficult-to-interpret data model, schema changes, database migrations, performance limitations, and eventual scaling. 

This complexity is significant enough that vendors such as CareDataLake have built products around managing the RRDB feed, describing it as a process that requires SQL Server expertise and ongoing infrastructure management. A missed restore can leave data stale, while a schema change can disrupt an otherwise stable integration. 

After working hands-on with PCC RRDBs, including environments supporting multiple databases, we have encountered many of these problems ourselves. Here is what teams should understand before building products, analytics, or integrations on top of PointClickCare Data Relay. 

1. Getting Access to PCC Data Relay Is Already Part of the Project 

Before writing extraction logic or designing an application, you must solve a more basic dependency: getting the right access. 

The facility – not the technology vendor – must request Data Relay from PointClickCare. PCC may charge a separate fee for the service, and third-party vendors report that PCC typically issues credentials within two to five business days after the request. 

If a vendor will host the database on behalf of the provider, PCC will generally also expect a Letter of Authorization identifying the authorized party. These may sound like administrative details, but getting one of them wrong can stop the project before the technical work even starts. 

For example, PCC organizations can have multiple FTP users. Having SFTP credentials doesn’t necessarily mean having the right credentials: only the Data Relay user provides access to the database backups. 

The backups themselves introduce another dependency. PCC delivers encrypted Redgate SQL Backup (.sqb) files. Restoring them requires both the appropriate decryption password and a licensed copy of Redgate SQL Backup. 

You also need to establish SQL Server compatibility early. The Data Relay version should match the SQL Server environment where the RRDB will be restored. For example, if you plan to run SQL Server 2022, request the corresponding Data Relay version. You cannot restore a backup created for a newer SQL Server to an older version. 

Credentials should not be treated as permanent configuration either. PCC introduced stronger decryption passwords in January 2025, illustrating why automated pipelines need a secure way to accommodate credential rotation rather than relying on hard-coded values. 

In other words, access planning belongs in the architecture discussion, not in the final setup checklist. 

2. An RRDB Is Not a Live Copy of PointClickCare 

One of the most important things to understand about Data Relay is what the RRDB actually represents. It is not a real-time replica of the transactional database behind the PointClickCare application. 

Instead, PCC creates the reporting database through internal ETL processes running on a schedule, typically hourly. Based on our observations, several ETL processes may be involved, responsible for different parts of the dataset and potentially operating on different schedules. 

Only after the RRDB updates does another process generate incremental transaction log backups and deliver them to SFTP. PCC documentation notes that hourly logs typically appear in SFTP approximately 20–30 minutes after the hour. 

That architecture creates an unavoidable chain between an action in the PCC interface and the moment that information becomes available in your own database: 

PCC application – internal ETL processes – RRDB – log backup generation – SFTP delivery – download – restore – your application 

Every stage adds latency and every stage can fail. As a result, a change a user makes in PointClickCare can take well over an hour to appear in your local copy. 

More importantly, “the database restored successfully” does not necessarily mean “the data is current.” 

3. A Healthy Database Can Still Contain Stale Data 

Some of the hardest PCC Data Relay issues are partial failures. An internal PCC ETL process may stop updating a particular group of tables while the rest of the RRDB continues to refresh normally. From an infrastructure perspective, everything can appear healthy: files arrive, restores succeed, SQL Server is online, and most queries return fresh records. 

Yet one part of the dataset may quietly lag by hours or days. That is why monitoring only whether the latest backup restored successfully is insufficient. 

Other operational problems include log files arriving out of sequence, missing logs, or a broken log chain. Because transaction logs have to be restored in sequence, a gap can prevent subsequent logs from being applied. In some situations, recovery requires requesting a new full backup from PCC and re-seeding the database. 

There can also be periods when no log files arrive because of failures in PCC’s internal processes. Even when a file does appear on SFTP, it should not automatically be treated as ready. Accessing a log while its transfer is still incomplete can lead to failed or corrupted restores. 

Another practical constraint is that the RRDB cannot be queried while it is restoring. Every hourly restore therefore creates a window in which applications attempting to query that database can fail. 

A production-grade PCC Data Relay pipeline consequently needs considerably more than a scheduled SQL restore task. At minimum, teams should think about log sequence validation, retry logic, missing-file detection, stale-table monitoring, alerting, and a documented re-seed process. 

The objective is not merely to automate restoration. It is to know when the data can, and cannot, be trusted. 

4. Hundreds of Tables Do Not Automatically Mean Usable Data 

Once the database is running reliably, another challenge appears: understanding it. A freshly restored RRDB can contain hundreds of tables and views. One healthcare analytics company reported 475 tables and 409 views, accompanied by a data dictionary of more than 300 pages. 

That is a lot of documentation, but documentation alone does not tell a development team how to calculate the metric, populate the application screen, or reconstruct the workflow it actually needs. 

PCC’s own best practices recommend using the view_ods_* reporting views rather than querying underlying structures directly. They also advise against selecting every column and recommend testing integrations after every Data Relay release because PCC regularly adds new columns and views. But the larger challenge is semantic rather than technical. 

The field visible in the PointClickCare UI does not always map neatly to an obvious field in the RRDB. For relatively simple entities such as facilities or residents, discovering the relationship may be manageable. Once the project reaches custom assessments, orders, care plans, billing workflows, and similar processes, database analysis starts to resemble reverse engineering. 

A common approach is surprisingly hands-on: enter controlled test data in the PCC interface, wait for it to propagate through Data Relay, locate where it appears in the RRDB, and then determine how to interpret the relevant codes, flags, keys, and relationships. 

That mapping work is part of the integration. 

5. PCC Has Data Conventions You Need to Learn 

Even after locating the correct tables or views, queries need to account for conventions that are easy to miss. 

Soft deletes are one example. Records may be flagged as deleted or struck out instead of being physically removed from the database. Queries that don’t apply the right filters can therefore return records users would consider inactive or deleted. 

Custom forms introduce their own challenge. User Defined Assessments (UDAs) are not stored in dedicated tables. Instead, they use the same generic assessment structures as MDS. Identifying a specific UDA and mapping its individual questions therefore requires careful analysis of those shared structures. 

Finally, views evolve. PCC periodically introduces replacements, including _v2 versions, and retires older views. Taken individually, none of these conventions is impossible to handle. Together, however, they explain why a generic “connect to the database and start querying” approach quickly becomes unreliable. 

6. Recommended PCC Views Are Not Always the Best Performing Views 

The view_ods_* reporting views are the recommended starting point for Data Relay consumers, but that does not mean every one of them will perform efficiently for every workload. 

Some views rely on numerous joins and layers of nested logic. On larger databases, they can become expensive, particularly when an application queries them repeatedly or uses them for incremental extraction. 

In these situations, optimization may require going beyond the default view. We have encountered cases where it made sense to reproduce the required logic using an optimized query or to build materialized tables tailored to a specific use case. You can then validate the output against the original PCC view to ensure the optimized implementation produces the same result. 

The principle is simple: use PCC’s reporting layer to understand the intended data model, but don’t assume every standard view is automatically suitable for a high-volume production workload. 

7. PCC Migrations Can Break Assumptions That Previously Worked 

Another mistake is treating an RRDB as a static environment. Healthcare organizations change. Facilities can move between PCC organizations. Data can be migrated from one RRDB to another. Organizational structures evolve. 

And those changes can expose assumptions buried inside an integration. We have seen a migration where records moved from one RRDB referenced codes that did not exist in the destination RRDB. The migrated data was technically present, but some of its reference data was not, resulting in orphaned references and broken joins. 

Resident identifiers can also change during migration. This matters enormously if an external application stores PCC resident IDs and treats them as permanent identifiers. After migration, the resident may still exist, and their historical data may still be present, but the ID your application stored may no longer point to them. At that point, the system needs a way to re-match residents using other attributes. 

The architectural lesson is important: PCC internal identifiers should not be treated as permanent external keys. Applications built on Data Relay should maintain their own stable identifiers and include reconciliation strategies for migrations and other structural changes on the PCC side. 

8. Supporting One RRDB and Supporting 50 RRDBs Are Different Engineering Problems 

A solution that works perfectly for one facility can become inadequate when a platform starts serving 20 or 50 RRDBs. Each database operates independently. Logs may arrive at different times. Restore schedules differ. One database may require a full backup or re-seed while the others continue normally. 

Because an RRDB cannot be read while it is restoring, a multi-tenant platform can reach a state where some databases are effectively unavailable at almost any moment. 

If an application, analytics platform, or centralized data warehouse needs consistent access across all tenants, the infrastructure must be designed for availability. 

One approach is to introduce a central control database that knows the state of every RRDB: which files have been downloaded, which have been restored, when the latest successful update occurred, and what happened historically. 

Restore operations can then be staggered, with limits on parallel restores to keep server load predictable. For workloads that require continuous read access, two or more restore copies of an RRDB can be maintained and restored in turns. While one copy enters the restoring state, another remains available to applications. 

This effectively creates a custom “always-on” pattern for Data Relay. Standard SQL Server availability groups do not solve the problem directly because standby databases cannot simply participate in them in the way this workflow requires. 

At greater scale, several landing servers may be needed for capacity and high availability, together with per-tenant freshness monitoring. The architecture must also maintain proper tenant isolation, secure credential storage, and audit logging. 

At this point, Data Relay is no longer a database-restoration script. It is an operational data platform. 

9. How We Approach a PointClickCare Data Relay Project 

The most successful implementations we have seen have one thing in common: teams treat PCC Data Relay as a data engineering product rather than a one-time integration task. 

That changes the questions asked at the beginning of the project. Before implementation, confirm how Data Relay will be requested, who at the facility owns that process, which facilities must be included, which SQL Server version will be used, and what authorization the hosting vendor requires. 

Then design ingestion as an automated and monitored pipeline rather than a scheduled restore job. That includes sequence checks, retries, stale-table detection, missing-file alerts, and a defined recovery and re-seeding procedure. 

The data model should also be analyzed for the use case before finalizing extraction logic. UI-to-database mapping for an assessment workflow may look completely different from the work required for billing, residents, orders, or analytics. 

Test performance with realistic data volumes. Heavy PCC views may need optimization or reproduction for application-specific workloads. 

And change should be considered part of the normal lifecycle: schema updates, deprecated views, credential rotation, organizational changes, and database migrations should not be treated as exceptional events. 

If the product is expected to expand beyond one RRDB, design for that scale before database number 20 exposes assumptions that worked only for database number one. 

From Data Relay to Reliable Healthcare Data Infrastructure 

PointClickCare Data Relay provides something extremely valuable: access to a local copy of EHR data that organizations can use beyond the PCC application. 

That opens the door to custom healthcare applications, analytics platforms, AI-powered workflows, integrations, reporting, and data products. But the existence of the feed should not be confused with a production-ready data layer. 

Between the PCC interface and the application consuming the data sits a chain of ETL processes, backups, file transfers, restores, database structures, semantic mappings, infrastructure decisions, and operational controls. The engineering challenge is making that chain predictable. 

At Langate, we have built and operated PointClickCare Data Relay pipelines for both individual facilities and multi-tenant platforms. Our work has included automated RRDB restores, monitoring and recovery processes, high-availability approaches for restored databases, and APIs built on top of PCC data. 

Along the way, we have encountered the same issues described throughout this article, from broken log chains and stale tables to difficult data mappings, migrations, changing identifiers, and multi-RRDB availability. 

That experience has shaped one principle we apply to PCC Data Relay projects: getting the data is only the beginning. The real work is making sure the data remains available, current, understandable, and trustworthy once a product starts depending on it. 

Planning a new PointClickCare integration or dealing with reliability, performance, or scaling problems in an existing Data Relay implementation? Talk to Langate about your PCC project.