How Step 7 Designed and Built a Production Encompass Data Connect Integration for Over 100,000 Real Estate Agents
A top-10 real estate organization and its mortgage subsidiary needed reliable, near real-time access to loan-level mortgage data to power agent workflows, recruiting, analytics, and CRM. Step 7 architected and built the Encompass Data Connect (EDC) integration that became the operational data backbone of the platform.
Outcome: A purpose-designed relational database — over 100 tables and 200+ supporting stored procedures, views, and functions — ingesting hundreds of inserts and updates every hour on a 10-minute cadence, serving as the single authoritative data layer for the portal, recruiting, Salesforce CRM, and analytics.
Related Case Study: This case study focuses on the backend EDC integration and operational data layer of the platform. For a broader view of the platform, including the agent portal, recruiting application, admin tools, and Salesforce integration that this data layer powered, see the following case study — Enterprise Portal to Unify Real Estate Agents, Mortgage Officers, & Leadership.
Relational Database Design Is a Core Step 7 Capability
Step 7 designs and builds production relational database systems in Microsoft SQL Server, MySQL, and PostgreSQL as part of our Platforms & Development practice. This includes architecture, schemas, data warehouses, ETL pipelines, indexing and query optimization, and the stored procedure, view, and function layers. The Encompass Data Connect engagement described below is a representative example of that practice — a large production database powering an enterprise platform used by over 100,000 real estate agents.
Client Background
The client is a top-10 real estate organization with an independent mortgage subsidiary supporting over 100,000 real estate agents and the loan officers who serve them nationwide. Mortgage data lived inside Encompass and was inaccessible to agents, recruiters, and downstream CRM. Step 7 was engaged as the engineering and delivery partner to change that.
Encompass Data Connect from ICE Mortgage Technology (formerly Ellie Mae) is the database data delivery product for Encompass — a cloud-based digital loan origination system (LOS) that streamlines the entire mortgage process from application to closing.
Designing the Operational Database
Step 7 designed a destination SQL Server database in the client’s AWS environment that became the authoritative data layer for the entire platform.
Source data. The operational database was populated via a SQL Server Integration Services ETL pipeline that ingested data from Encompass Data Connect to the client’s AWS instance.
Destination schema. The destination mirrored the Encompass schema but only for the objects the platform actually needed — over 100 tables across the borrower, loan, contact, closing, commitment, and related objects. This provided consistency between source and destination objects to ensure data mapped cleanly.
Operational and staging separation. Each ingestion target was paired with a staging table, supporting safe re-runs and partial-failure recovery — essential at a 10-minute production cadence.
Stored procedures, views, and functions as the access layer. Application code never queried tables directly. Over 200 stored procedures, views, and functions enforced consistency, encapsulated business logic, and gave the team a single place to evolve schema changes without breaking consumers.
Scale. Over 1 million records at steady state across the full schema, with the largest tables in the hundreds of thousands.
Dual-Pipeline ETL Design
Two parallel SSIS pipelines ran every 10 minutes:
- An insert pipeline captured records newly added to the source.
- An update pipeline captured records modified in the source.
Separating inserts from updates kept each pipeline simple, fast, and independently monitorable. Both were idempotent — failed runs could be safely re-run without producing duplicates or inconsistent state. Steady-state throughput was hundreds of new and modified records per hour.
Scheduling and Operations
SQL Server Agent on AWS EC2 orchestrated the ETL workload. The 10-minute pipelines ran alongside a related job that refreshed the Salesforce-facing subset of the data warehouse, keeping CRM users on the same data as the portal. A daily job refreshed the full data warehouse for leaderboards, recruiting metrics, and executive reporting. Multiple EC2 instances isolated the SSIS, operational database, and warehouse environments and allowed each to scale independently.
Push Notifications
The 10-minute cadence enabled milestone-driven agent notifications. A queue watched for status changes and milestone transitions during ETL runs and produced email and SMS notifications routed to the relevant agent. The queue flushed hourly, with email through SendGrid and SMS through Twilio. Because notification logic ran against the operational database rather than Encompass, the notification system never touched the system of record.
What the Integration Enabled
- Near real-time deal status visibility for agents in the portal
- Recruiting workflows surfacing low-engagement agents based on current activity
- Performance leaderboards and analytics at agent, team, regional, and national levels
- Salesforce CRM consumption of normalized current mortgage data without integrating to Encompass directly
- Executive reporting on a single authoritative analytical layer
Results
- A 100+ table operational relational database with 200+ supporting stored procedures, views, and functions
- Production EDC integration on a 10-minute cadence supporting over 100,000 real estate agents
- Over 1 million mortgage records ingested and maintained in the operational data layer
- Near real-time mortgage visibility delivered without impact to the Encompass system of record
- Idempotent dual-pipeline ETL with reliable recovery from operational interruptions
- Single authoritative data layer eliminating duplicate ingestion logic across portal, recruiting, Salesforce, and analytics
- Milestone-driven email and SMS notifications delivered to agents within minutes of underlying mortgage events
Why This Matters
Encompass Data Connect is the right product for scaled, reliable access to Encompass data — but using it well is a relational database engineering exercise. Schema design, indexing, access layer architecture, ingestion cadence, idempotency, and the operational/analytical boundary all determine whether the integration supports real workflows or just basic reporting.
Step 7 took accountability for all of it as the engineering and delivery partner — database design, ETL, scheduling, monitoring, and downstream enablement — and delivered a production data layer that has continued to scale with the platform.
Note: At the time of this engagement, EDC was delivered through a deployment model called Replication. ICE has since succeeded that with Direct Write, which delivers the same SQL Server-based data model into customer-operated infrastructure. The schema design, ETL architecture, and access layer patterns described here apply directly to current EDC Direct Write deployments.
Project Artifacts
ETL Pipeline Architecture and Downstream Support
