Michele Esri — Spatial Coverage Intelligence

Flow How the data moves — sources to decision

1 · Sources

Your systems

CRM, site DB, work orders, crowdsource + Esri / Census / spatial files

CDICAI

▸

2 · Informatica IDMC

Ingest & govern

CDI moves data at rest (batch, CDC) · CAI calls live APIs · data quality · catalog and lineage

▸

3 · Bronze

Raw, as-is

Every source landed exactly as it arrived, tagged by origin

▸

4 · Silver

Governed + spatial

DQ rules cleanse & audit; BigQuery-GIS point-in-polygon join to venue & state

▸

5 · Gold

The decision

85th-percentile score per carrier · winning carrier · opportunity flag · CRM context

▸

6 · Serve

Consume anywhere

Esri query layers · Grafana BI · this app · PDF reports

Report Executive summary, how it works, and definitions

Executive summary

A telecom company sells connectivity to large venues: stadiums, arenas, campuses. Before it spends money it wants two answers for every venue: where can we win a sale today, and where should we build or fix coverage. This application answers both, on a map, from data it can trace back to the source.

How it works, in plain words

Picture three boxes. The first box (Bronze) is where every piece of information is dropped exactly as it arrived: map shapes from Esri, county lines from the US Census, thousands of phone-signal readings that people collected on their phones, the list of venues, the customer records, and the open repair tickets. Nothing is changed in this box, so we can always prove what came in and when.

The second box (Silver) is where the data is cleaned. Bad readings (no location, impossible signal values, unknown carrier) are removed, and every removal is written down with the reason. Each good reading is then placed inside the right venue, state and county using the map shapes. This is the step a specialist used to do by hand; here it is a rule that runs the same way every time.

The third box (Gold) holds the answer. For each venue and each carrier (Verizon, AT&T, T-Mobile) we compute one coverage score, decide who wins, and flag the opportunity: Greenfield (everyone is weak, so build here), Competitive (no clear winner, so sell hard), or Dominated (one carrier owns it). Next to the score sit the account owner, the sales stage, the monthly revenue and the open work orders, so the map answers the business question, not just the engineering one.

Why Informatica IDMC

The boxes are filled by Informatica Intelligent Data Management Cloud. It moves data in two ways: CDI (Cloud Data Integration) for data that sits still in databases, files and business applications, moved on a schedule or as it changes; and CAI (Cloud Application Integration) for data you must ask for right now through an API, such as the live Esri map service. The same platform keeps the catalog, the lineage and the quality rules, so every number on the map can be traced to its source and every removed row is explained.

Why BigQuery and Esri

The three boxes live in Google BigQuery, which can do the map mathematics (which point falls inside which shape) inside the warehouse at any scale, with no separate GIS server. Esri provides the authoritative shapes and basemaps, and reads the Gold table directly as a map layer, so the people who already work in ArcGIS keep their tools.

What you are looking at in this demo

Two sources are pulled live from the internet on every run: Esri state polygons and Census county polygons. One source streams continuously: National Weather Service watches and warnings from the Esri Living Atlas, polled every minute and landed in Bronze within seconds, then matched to venues on the Gold map. The full pipeline also runs on a nightly schedule and records every run. A spatial file is ingested from disk. The venue list, the crowdsourced readings, the CRM accounts and the work orders are realistic sample data that stand in for the client's private systems, using the same connector patterns a production deployment would use. The tab In this demo vs. not lists exactly which is which, so nothing here overstates what is real.

How it works, end to end

Sources → Informatica IDMC (CDI for data at rest, CAI for live APIs) → BigQuery Bronze (raw) → Silver (clean and spatial) → Gold (decision) → Esri map, Grafana, reports.

1. Sources

Seven kinds of input: the venue master (site database), crowdsourced coverage readings, CRM accounts (Dynamics 365), field work orders (ServiceNow), Esri state polygons (ArcGIS REST feature service), Census county polygons (TIGERweb ArcGIS REST) and a spatial file (GeoJSON converted from shapefile or KML).

2. Ingest with IDMC

Databases, files and business applications are read by CDI: on a schedule (batch) or continuously as rows change (CDC). Live map services are called by CAI through a Service Connector, which is how any REST API is reached when no native connector exists. Every load writes one line to the ingest ledger: source, connector, path, mode, row count and time.

3. Bronze: raw, as it arrived

One table per source, rows untouched, tagged with the source system. This is the audit anchor: if a number is questioned, Bronze shows what came in.

4. Silver: governed and spatial

Data quality rules run in order and each rule records how many rows it removed and why (silver.dq_audit). Carrier names are normalised. Then BigQuery GIS performs the spatial joins: a reading is assigned to a venue if it falls within the venue radius (ST_DWITHIN), and stamped with the state and county whose polygon contains it (ST_CONTAINS).

5. Gold: the decision

For each venue and carrier the 85th-percentile of the quality score is computed (a robust "how good is it usually" number that ignores a few lucky readings). Carriers are ranked, the winner is marked, and the opportunity flag is derived from the spread between carriers. CRM and work-order context is joined on the venue.

6. Serve

The Gold table is read by this map, by Esri as a BigQuery query layer, by Grafana dashboards, and by the two generated reports (Where to Win, Where to Build).

Open the architecture diagram →

Why these sources

Each source answers one business question. Remove any of them and the map loses a dimension of the decision.

Source	Business question it answers	Without it
Crowdsourced coverage readings	What do customers actually experience at this venue, per carrier?	No score; the whole analysis is opinion.
Venue master (site database)	Where exactly are the venues we care about, and what type are they?	Readings cannot be assigned to anything.
Esri state polygons (ArcGIS REST)	Which state is each reading in, using the authoritative boundary?	Regional roll-ups and Esri layers disagree with the warehouse.
Census county polygons (TIGERweb)	Which county, for permits, franchise areas and public funding programmes?	Build decisions cannot be tied to the jurisdiction that approves them.
Spatial file (GeoJSON from shapefile / KML)	Where are our own assets, such as fibre routes and points of interest?	Build cost cannot be estimated from distance to existing plant.
CRM accounts (Dynamics 365)	Do we already sell here, at what stage, and for how much per month?	Sales cannot prioritise; a strong-coverage venue may already be a customer.
Field work orders (ServiceNow)	Are crews already working at this site?	A build recommendation may duplicate work already under way.

Technology

Component	What it does here	Why it was chosen
Informatica IDMC, CDI	Moves data at rest: databases, files, business applications; batch and change-data-capture.	One governed tool for hundreds of connectors; the client already standardises on it.
Informatica IDMC, CAI	Calls live REST APIs (Esri, Census) through Service Connectors; orchestrates runs with parameters.	Real-time and API sources without custom code.
IDMC data quality, catalog, lineage	Quality rules with audit; catalog of every table; lineage from map to source.	Trust: every number is explainable.
Google BigQuery	Holds Bronze, Silver, Gold; runs the spatial joins with BigQuery GIS.	Serverless scale; spatial functions in SQL; Esri reads it directly.
Esri ArcGIS	Authoritative polygons via REST feature services; basemaps; query layers over Gold.	The client's GIS standard; no re-platforming of the GIS team.
US Census TIGERweb	County polygons via ArcGIS REST.	Public, authoritative, free.
This application	Serving API over BigQuery plus the map and report views.	A thin, replaceable presentation layer; the value is in the warehouse.
Grafana	Operational dashboards over the same Gold table.	Already in the estate.

In this demo vs. not

Three honest categories. LIVE is pulled from the real service on every run. SAMPLE is realistic sample data standing in for a private system, using the connector pattern a production deployment would use. ARCHITECTURE is supported by the platform but not built in this demo.

Live in this demo

Source	Path	What the app does with it	Status
Esri ArcGIS REST feature service (state polygons)	CAI	Service Connector pulls US state polygons as GeoJSON into Bronze; used for the spatial join and the state stamp.	LIVE
US Census TIGERweb ArcGIS REST (county polygons)	CAI	Service Connector pulls county polygons into Bronze; county stamped in Silver.	LIVE
NWS watches and warnings, Esri Living Atlas feature service	CAI	Polled every 60 seconds for events updated since the last poll; new events streamed into Bronze nws_live_events; matched to venues within 40 km on the Gold map.	STREAM
Spatial file (GeoJSON, converted from shapefile / KML)	CDI	Flat-file ingest into Bronze spatial_files; used for distance to existing plant.	LIVE (file)
Esri basemaps (streets, imagery, topographic)	Map	Visual context on the Gold map. Not ingested.	LIVE (map)

Simulated in this demo (sample data, real connector pattern)

Source	Path	What the app does with it	Status
Crowdsourced coverage readings	CAI / CDI	Carrier signal-quality readings into Bronze; the data-quality story (bad rows removed and audited) runs on this feed.	SAMPLE
Venue master, SQL Server / Azure SQL	CDI + CDC	Sixteen venues with coordinates into Bronze; production would use change-data-capture.	SAMPLE
Microsoft Dynamics 365 CRM	CDI	Account, opportunity stage and monthly revenue per venue into Gold.	SAMPLE
ServiceNow field work orders	CAI / CDI	Open work orders per venue into Gold and the Where to Build report.	SAMPLE

Architecture only (supported, not built here)

Source	Path	How it would be done	Status
Esri geodatabase (ArcSDE on Oracle or SQL Server)	CDI	Read natively at the database layer; the reference estate in the next section does this.	ARCHITECTURE
Large national datasets (buildings, rivers, fibre, ~500 GB)	CDI	Selective ingest and warehouse pushdown rather than full copies.	ARCHITECTURE
CSV with geocoding	CDI	Flat file plus IDMC geocoding to latitude and longitude.	ARCHITECTURE
Excel	CDI	Spreadsheet ingest.	ARCHITECTURE
Salesforce	CDI / CAI	Accounts, opportunities and cases into the CRM context.	ARCHITECTURE
Points and areas of interest as run parameters	CAI	Orchestration parameters to scale from one venue to many.	ARCHITECTURE
Boomi (existing in-house integration)	n/a	Overlaps IDMC; a migration topic for the account team.	ARCHITECTURE

Ingestion modes and IDMC status

Mode	Meaning	Status in this demo
Live API pull	Called from the real service each run.	LIVE Esri and Census.
Real-time stream	New events land in Bronze within seconds of being published.	PENDING being added.
Scheduled batch	The full pipeline runs on a schedule and records each run.	PENDING being added.
IDMC-executed jobs	Loads run by Informatica IDMC itself, with IDMC job identifiers in the ledger.	PENDING IDMC organisation access is being restored.

Glossary

Venue

A stadium, arena or campus where connectivity is sold. The unit of every decision here.

Carrier

A mobile network operator: Verizon, AT&T or T-Mobile.

Coverage reading

One measurement of signal at one place and time, taken by a phone. Thousands make up the picture at a venue.

Signal (dBm)

Signal strength in decibel-milliwatts. Closer to zero is stronger; -70 is good, -110 is poor.

Quality score

A 0 to 100 number derived from the reading, so different measurements can be compared.

85th percentile

The value that 85% of readings are at or below. Used so a few unusually good readings cannot mask a poor typical experience.

Winning carrier

The carrier with the highest 85th-percentile score at a venue.

Opportunity flag

Greenfield: all carriers weak, build here. Competitive: no clear winner, sell hard. Dominated: one carrier clearly leads.

Bronze / Silver / Gold (medallion)

Three layers of the warehouse: raw as received, cleaned and enriched, ready for decisions.

Data quality (DQ) rule

A check that removes or flags rows that fail a condition, such as a missing location.

DQ audit / lineage

The record of what each rule removed and why, and the trail from every output back to its source rows.

Ingest ledger

One line per load: source, connector, path, mode, rows, time. The proof of what arrived.

Polygon

A closed shape on a map, such as a state or county boundary.

Spatial join / point-in-polygon

Matching a point (a reading) to the shape that contains it or the venue it is near.

GeoJSON

A text format for map shapes and points. The common currency between Esri services, files and BigQuery.

Shapefile / KML

Older map-file formats from Esri and Google Earth. Converted to GeoJSON before ingest.

ArcGIS REST feature service

An Esri web service that returns map features on request. How live polygons are pulled.

Basemap

The background map (streets, imagery, topographic). Visual context, not data.

Geodatabase (ArcSDE)

Esri's spatial data stored inside a relational database such as Oracle or SQL Server.

Census TIGERweb

The US Census Bureau's ArcGIS REST service for boundaries such as counties.

FIPS code

The standard numeric identifier for a state or county.

IDMC

Informatica Intelligent Data Management Cloud: the integration, quality and governance platform.

CDI

Cloud Data Integration: IDMC's service for moving data at rest, in batch or by change-data-capture.

CAI

Cloud Application Integration: IDMC's service for calling APIs and orchestrating processes in real time.

CDQ / CDGC

IDMC's data quality and data governance and catalog services.

Connector

A pre-built IDMC adapter for a specific system, such as SQL Server, Dynamics 365 or ServiceNow.

Service Connector

IDMC's generic REST adapter, used when no native connector exists (Esri, Census).

Secure Agent

IDMC's runtime installed inside the customer's network to reach private systems. A hosted agent is the Informatica-run alternative for cloud sources.

Batch

A scheduled load of a whole dataset or of everything changed since the last run.

CDC (change data capture)

Reading a database's change log so only changed rows move, continuously.

Real-time / streaming

Events arriving one by one within seconds of happening. Here: NWS watches and warnings, polled every minute.

NWS watches and warnings

National Weather Service alerts (tornado, flood, wind, heat and so on) published as an Esri Living Atlas feature service with polygons.

Living Atlas

Esri's curated collection of ready-to-use, continuously updated map services.

Pushdown / ELT

Running the transformation inside the warehouse rather than moving data out to transform it.

BigQuery / BigQuery GIS

Google's serverless warehouse and its built-in spatial SQL functions.

Query layer

An Esri map layer that reads directly from a database table, here the Gold table.

CRM / Dynamics 365

The customer relationship system holding accounts, opportunities and revenue.

MRR

Monthly recurring revenue for an account.

Work order (ServiceNow)

A ticket for field work at a site.

Idempotent pipeline

Running it twice gives the same result as running it once; safe to re-run.

Live Pipeline results — Bronze → Silver → Gold

Every source lands in Bronze exactly as it arrived: nothing is cleaned, and each row is tagged by origin. The ledger below lists one row per source with its connector, its integration path (CDI moves data at rest, CAI calls a live API) and how it was ingested. LIVE means the data was pulled from the real service on this run; SAMPLE means realistic sample data standing in for a private enterprise system. Click any source to inspect its raw rows.

Raw rows

Sources Source catalog: what is live, what is sample, what is architecture

Source catalog the sources discussed, sorted by what this demo actually does with them

Rule of thumb: CDI moves data at rest (databases, warehouses, files, object stores, bulk SaaS; batch and CDC). CAI calls and orchestrates in real time (REST APIs such as Esri and Census, application actions, events). LIVE pulled from the real service each run SAMPLE realistic sample data, real connector pattern ARCHITECTURE supported, not built here. The full sorted list with explanations is in the Report tab In this demo vs. not.

A · Sources discussed, sorted

Source	Path	What the app does with it	Status
Esri ArcGIS REST feature service (state polygons)	CAI	Service Connector pulls US state polygons into Bronze; spatial join and state stamp.	LIVE
US Census TIGERweb (county polygons)	CAI	County polygons into Bronze; county stamped in Silver.	LIVE
NWS watches and warnings (Esri Living Atlas)	CAI	Real-time stream: polled every minute, new events land in Bronze within seconds, matched to venues.	STREAM
Spatial file: GeoJSON (converted shapefile / KML)	CDI	Flat-file ingest into Bronze `spatial_files`.	LIVE (file)
Esri basemaps (streets, imagery, topographic)	Map	Visual context only; not ingested.	LIVE (map)
Crowdsourced coverage readings	CAI / CDI	Signal-quality readings into Bronze; the data-quality rules run on this feed.	SAMPLE
Venue master, SQL Server / Azure SQL	CDI + CDC	Sixteen venues with coordinates into Bronze.	SAMPLE
Microsoft Dynamics 365 CRM	CDI	Account, opportunity stage, monthly revenue into Gold.	SAMPLE
ServiceNow field work orders	CAI / CDI	Open work orders per venue into Gold and the Where to Build report.	SAMPLE
Esri geodatabase (ArcSDE)	CDI	Read natively at the database layer (Oracle SDE or SQL Server).	ARCHITECTURE
Large national datasets (buildings, rivers, fibre, ~500 GB)	CDI	Selective ingest and warehouse pushdown.	ARCHITECTURE
CSV with geocoding	CDI	Flat file plus IDMC geocoding to coordinates.	ARCHITECTURE
Excel	CDI	Spreadsheet ingest.	ARCHITECTURE
Salesforce	CDI / CAI	Accounts, opportunities, cases into the CRM context.	ARCHITECTURE
Points and areas of interest as run parameters	CAI	Orchestration parameters to scale from one venue to many.	ARCHITECTURE
Boomi (existing in-house integration)	n/a	Overlaps IDMC; a migration topic for the account team.	ARCHITECTURE

B · Reference: connectors in a comparable production IDMC estate

Taken from an export of a production Informatica IDMC organisation at a fibre and utility enterprise: about 230 CDI endpoints and 120 CAI connections across four Secure Agents. It shows what "all sources, one governed pipeline" looks like at scale, including the Esri geodatabase read natively at its Oracle SDE layer.

Connector in the real estate	Path	What it is / typical use
Oracle Database + Oracle Database Ingestion (CDC)	CDI	Core operational DBs (billing/CCB, ERP, Maximo) — bulk + change-data-capture
Oracle GIS — Esri SDE geodatabase (schema SDE_*)	CDI	Esri spatial data read natively at its Oracle SDE layer — the "backing geodatabase" path
Microsoft SQL Server (2012 / 2016 / 2019, Azure SQL)	CDI	Warehouses, app DBs, SCADA / Ignition historians
Snowflake Cloud Data Warehouse V2	CDI	Cloud EDW — raw / curated / marts (the Zayo CDW)
Microsoft Dynamics 365 · Salesforce	CDI & CAI	CRM accounts, opportunities, cases
Amazon S3 · FileIO · Flat / CSV files	CDI	Object storage, extracts, reference sets, geocode inputs
Advanced SFTP V2 · SharePoint Online	CDI	Partner file exchange; documents & survey exports
Web Services (SOAP / WSConsumer) · REST V2	CDI	App / service calls inside mappings & taskflows
Service Connector (generic REST)	CAI	Real-time API orchestration — how Esri & Census are wired here (112 in the reference org)
Maximo · Oracle Field Service · ServiceNow · WorkDay	CAI	App-integration Service Connectors — dispatch, work orders, HR
Email · File (process) events	CAI	Notifications, event-driven file handling, middleware

Frequency and orchestration in production: operational systems load on a scheduled batch cadence, with CDC where the source supports it; Esri and Census polygons are pulled on demand through CAI Service Connectors; one run can cover one venue or many. The pipeline is idempotent, and the Secure Agent runs inside the customer network, which is where catalog, lineage and access control are enforced.