Postgres views - Cedar.AI Documentation

Postgres in Data Depot gives you live, read-only SQL access to your carrier’s Cedar data. You pick a data source and its columns, Data Depot creates a Postgres view for your carrier, and you get one dedicated database login to query it from any PostgreSQL client, BI tool, or JDBC/ODBC connection.

Postgres access is a pull model: your tools query the view when they need data. If you want Cedar to push files to your storage instead, see CDC delivery.

Browse the catalog

Open Postgres in the sidebar.⁠‌‌‌‌‌‌‌‌‌‌⁠ The Sources page shows one card for each data source available to the selected carrier. Use Search sources to filter by name or description.

Postgres Sources page with source cards and the Source preview panel — The Sources page. The Source preview on the right follows the selected source.

Each card shows badges:

Carrier-scoped — the view contains only the selected carrier’s rows.
Global — reference data that is the same for every carrier.
Provisioned — you already have a view for this source.
Selected — the source shown in the Source preview panel.

Select a card to preview its first columns, then select Open details to set up access.

Available sources

Source	Scope	What it contains
Shipments	Carrier	Current shipment tracking records with the operational fields available in ARMS
Charges	Carrier	Revenue charge records with the fields in the ARMS Charges grid
Charge Audit Records	Carrier	Per-car revenue details at the charge audit-record level
Car Hire Monthly Detail	Carrier	Consolidated monthly payable and receivable car-hire detail records. North American carriers only
X12 Messages	Carrier	Inbound and outbound X12 message metadata for EDI monitoring and troubleshooting. North American carriers only
Waybills	Carrier	Waybill records covering the fields in the ARMS All Waybills grid
Equipment Inventory	Carrier	Current equipment inventory records covering the fields in the ARMS inventory grid
Equipment History	Carrier	Historical equipment events with core, waybill, car location, and location fields
Locations	Carrier	Your carrier’s location hierarchy and location template attributes, including deleted locations
STCC Hazmat Detail	Global	STCC commodity and hazardous-material reference data used by ARMS Commodity Lookup. North American carriers only

Sources marked “North American carriers only” are not listed for carriers in the EU region.

Set up access

Select Open details to open the source’s own page, headed with the source’s name, where you configure the view and its login. The Sources list on the left has every source, so you can switch between them without returning to the Sources page.

Choose columns

Under Choose columns, turn on the columns you want in the view. Select all and Clear all change every column at once, and the badge shows how many of the source’s columns are selected. Each row shows the column’s display name, its type, whether it can be empty, and a description. When the column’s name in the view is different from its display name, it appears underneath, for example waybillNumber under Waybill ID.

A column marked Links to another source, such as Links to Equipment Inventory, is what joins this view to that one. If you turn off a column that a link needs, Data Depot warns that Some links to other views are off. See Join sources together.If a source shows Column details aren’t published for this source yet, the view uses Cedar’s default columns.

Set an automatic expiry (optional)

Under Provision & database access, set Automatic expiry if the access should end on its own, for example for a short project or a contractor. Data Depot removes the view and its login within about five minutes after the time you choose. Leave it empty to keep access until you revoke it.

Provision the view

Select Provision view. When it finishes, the button reads View up to date and the step shows Completed, with the view’s name underneath.

Provision & database access panel with Configure view completed, Automatic expiry, View up to date, and the Create database credential button — A provisioned view, ready for its database login

Create the database login

Under Connection details, select Create database credential. Data Depot creates one login for this view and shows it once.

Save the credential

Copy the details before you leave the page. Each field has its own copy button. The Connection string has every setting, including the port, in one line that psql and most PostgreSQL drivers accept. The page hides the password in it, but its copy button copies the real password.

Credential ready panel with copy buttons for host, port, database, username, password, SSL mode, and view, and the connection string — The credential is shown once. Save it in your password manager or secret store.

The password is shown only once and is never returned again. If you lose it, use Reset credential to issue a new one.

Connect to the view

Use the Host, Port, Database, Username, and Password from the credential. SSL is required. Your login can read only its own view, which lives in the cedar_apps schema and is named like cedar_apps.data_depot_<source>_<carrier>_<id>.

psql
JDBC
Python

psql "host=<host> port=<port> dbname=<database> user=<username> sslmode=require"

psql asks for the password. The Connection string you copied works here too, but it includes the password, so it ends up in your shell history.

jdbc:postgresql://<host>:<port>/<database>?sslmode=require

Use the username and password from the credential in your tool’s connection settings.

import psycopg

with psycopg.connect(
    host="<host>",
    port=5432,  # use the port shown in your credential
    dbname="<database>",
    user="<username>",
    password="<password>",
    sslmode="require",
) as conn:
    rows = conn.execute("SELECT * FROM <view> LIMIT 10").fetchall()

Then query the view by its full name:

SELECT "waybillNumber", "waybillDate", "waybillStatus"
FROM cedar_apps.data_depot_waybills_demo_3f9a1c2b7d4e
ORDER BY "waybillDate" DESC
LIMIT 100;

The view always reflects Cedar’s current data. There is nothing to refresh.

If your network limits outbound connections, allow the host and port shown in your credential. To use the view from Databricks, see Databricks.

Column names in SQL

Choose columns shows each column’s display name, such as Waybill ID or Track ID. In the view, the column has its own name, such as waybillNumber or trackId, which Choose columns shows underneath the display name. Use that name in SQL.

Put a name in double quotes when it has a capital letter or a dot, for example "waybillNumber" or "station.region". Without quotes, Postgres reads waybillNumber as waybillnumber and can’t find the column.
Lowercase names with underscores, such as row_key or charge_id, work with or without quotes.

Equipment Inventory, Equipment History, Waybills, Charges, and Shipments use names like waybillNumber. Locations, Charge Audit Records, Car Hire Monthly Detail, X12 Messages, and STCC Hazmat Detail use names like charge_id. To list the column names in your view, run:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'cedar_apps'
  AND table_name = 'data_depot_waybills_demo_3f9a1c2b7d4e'
ORDER BY ordinal_position;

Private network connectivity

By default, your tools connect to the view over the internet, encrypted with SSL. If your security policy requires BI traffic to stay on a private network, Cedar can set up a private connection to your Postgres views. This is a custom setup arranged per customer: contact your Cedar account team, and we’ll work out the details with your network team.

Your BI tool runs in	How the private connection works
AWS	An AWS Site-to-Site VPN connection between your AWS network and Cedar’s
Azure	A private link between your Azure network and Cedar’s AWS network. This is usually a site-to-site VPN from an Azure VPN Gateway. For higher, more predictable throughput, use Azure ExpressRoute paired with AWS Direct Connect. AWS describes these patterns in Designing private network connectivity between AWS and Microsoft Azure

Postgres views or CDC delivery?

A Postgres view is live: every query your BI tool runs is answered by Cedar’s database. That makes views the quickest way to get current data, but it also means your dashboards depend on Cedar’s Postgres service, including its availability, performance, and maintenance windows. If you want full control of your data, with your own storage, your own retention, and your own SLA, use CDC delivery instead. Cedar pushes the data to your Azure or Amazon S3 storage, and your BI tools read from your own copy, so they keep working even while Cedar’s database is unavailable.

	Postgres views	CDC delivery
Where your BI tools read from	Cedar’s Postgres database	Your own storage and warehouse
Freshness	Live	Every 5, 10, or 15 minutes
Service level	Cedar’s Postgres SLA	Your own, for everything after the files land
Network	Internet with SSL, or a private connection by arrangement	Cedar writes to your storage; your tools never connect to Cedar
Availability	Every carrier	EU pilot. US on request through Cedar sales

Change the columns later

Return to the source, change the selected columns (or the automatic expiry), and select Update view with N columns.⁠‌‌‌‌‌‌‌‌‌‌⁠ The view is updated in place, and the login and password don’t change, so your connected tools keep working. Queries that name a column you removed will start to fail, so update them at the same time.

Manage existing access

Select History at the bottom of Provision & database access to open it. It lists every view created for that source on this carrier, including revoked ones, with who created it and when. The number next to History counts them.

History section showing an active and a revoked view for Waybills, each with its view name, username, and columns — History lists current and past views for a source

Reset the password

Select Reset credential, then Reset password to confirm. The current password stops working immediately, and the new password is shown once. Update every tool that uses the login.

Revoke access

Select Revoke access, then Revoke access again in the dialog to confirm. Data Depot removes the view and its login. You can provision the source again later. Revoking can take a moment; the status shows Revoking until it finishes.

Statuses

Status	Meaning
Active	The view and login exist and can be used
Revoking	Removal is in progress
Revoked	The view and login were removed
Expired	The automatic expiry time passed
Action required	Cleanup didn’t finish. Select Retry revoke to try again
Failed	Provisioning didn’t complete. Try again, or contact Cedar if it keeps failing

Limits

One active view per source per carrier. To change it, update its columns rather than creating a second view.
One login per view.
While an earlier view for the source is still being revoked, or its cleanup needs attention, you can’t provision the source again. Data Depot opens History and shows a notice. If removing the earlier view and login didn’t finish, select Retry revoke in the notice. If the removal is still running, wait for it to finish.
Once a view’s automatic expiry passes, the button reads Provision expired until Data Depot finishes removing the view. After that, you can provision the source again.
Views are read-only. You can’t write to Cedar through them.

Join sources together

Some columns in one view point at rows in another view. For example, "equipmentId" in Equipment History points at a car in Equipment Inventory. Use these links to relate the views you’ve provisioned for the same carrier. Each login can read only its own view, so a single query on Cedar’s database can’t read two views. Connect each view with its own login, then join them in your BI tool, as a relationship between two tables, or in your warehouse after you load them. Use the column names from the view, in double quotes where needed. See Column names in SQL. Each source’s page shows its links under Links to other views, as a Diagram or a List, and marks each link column in Choose columns with a Links to badge.

Every row has a row_key

Every view has a row_key column that identifies its rows. It’s always in the view, even though Choose columns doesn’t list it. Every link points at the row_key of the other view.

Source	`row_key` holds the
Shipments	Shipment ID
Charges	Charge ID
Charge Audit Records	Audit record ID
Car Hire Monthly Detail	Detail row ID
X12 Messages	Message ID
Waybills	Waybill record ID, not the waybill number
Equipment Inventory	Equipment ID
Equipment History	History event ID
Locations	Location ID, the same value as `grouping_id`
STCC Hazmat Detail	Commodity code

Every carrier-scoped view also has a carrier_id column. Each view holds one carrier’s rows, so you don’t need it to join two views for the same carrier. Use it to tell carriers apart when you combine views from more than one carrier.

Links between views

Arrows point from the view that holds the link to the view it points at. The tables below list every link. Keep selected is the column, as Choose columns names it, that must stay selected in the “from” view for the link to be there. Shipments, Car Hire Monthly Detail, and STCC Hazmat Detail don’t link to other views.

Cars, history, and locations

From	Link column	Points at the `row_key` of	Keep selected	Good to know
Equipment History	`"equipmentId"`	Equipment Inventory	Nothing. It’s always in the view	The car the event belongs to. Choose columns doesn’t list it
Equipment Inventory	`"trackId"`	Locations	Track ID	The track the car is on now. It matches only when that track is in your Locations view
Equipment Inventory	`("track_secondary"->>'groupingId')::bigint`	Locations	Track	The same track as `"trackId"`
Equipment Inventory	`("station_secondary"->>'groupingId')::bigint`	Locations	Station	The station above the car’s current track. It matches only when stations are in your Locations view
Equipment Inventory	`("yard_secondary"->>'groupingId')::bigint`	Locations	Yard	The yard above the car’s current track. It matches only when yards are in your Locations view
Equipment Inventory	`("zone_secondary"->>'groupingId')::bigint`	Locations	Zone	The zone above the car’s current track. It matches only when zones are in your Locations view
Locations	`parent_grouping_id`	Locations	Parent Grouping ID	The parent location in your main hierarchy. Parents that aren’t a track or a hierarchy location type, such as roads, aren’t in the view, so they don’t match

Charges and waybills

From	Link column	Points at the `row_key` of	Keep selected	Good to know
Charge Audit Records	`charge_id`	Charges	Charge ID	The charge the record belongs to. Charges with a Deleted billing status aren’t in the Charges view, so their records don’t match
Charge Audit Records	`equipment_id`	Equipment Inventory	Equipment ID	The car the record covers. Cars removed from your inventory don’t match
Charge Audit Records	`waybill_id`	Waybills	Waybill ID	The waybill in effect at the charge’s start event. The Waybills view holds only pending and active waybills, so archived waybills don’t match
Charge Audit Records	`start_eqh_id` and `end_eqh_id`	Equipment History	Start EQH ID and End EQH ID	The events that start and end the charge. They’re empty for storage charges, and deleted, muted, and rejected events aren’t in Equipment History, so they don’t match
Equipment History	`"chargeId"`	Charges	Charge ID	The charges the event started or ended, as a comma-separated list of charge IDs. Split it into one ID per row before you join. Charges with a Deleted billing status aren’t in the Charges view, so they don’t match
Charges	`("equipment_secondary"->>'equipmentId')::bigint`	Equipment Inventory	Equipment	The charge’s car, from its first audit record, or the received car on transload charges. It’s empty for excess storage and freight charges, so use Charge Audit Records for those cars
Waybills	`("initialAndNumber_secondary"->>'equipmentResourceId')::bigint`	Equipment Inventory	Associated Inventory	One car on the waybill, not every car billed on it. When several cars are billed and attached it’s the lead billed car (empty when that car isn’t in your inventory); otherwise it’s the attached car with the lowest ID

Waybill ID means different things in two views. In Charge Audit Records, Waybill ID (waybill_id) is the waybill’s record ID, which matches the Waybills row_key. In Waybills, the column shown as Waybill ID is "waybillNumber", the number on the waybill. Waybill numbers repeat across dates, so join on row_key, not on the number.

X12 Messages

From	Link column	Points at the `row_key` of	Keep selected	Good to know
X12 Messages	`ack_message_id`	X12 Messages	997 Ack Message ID	The 997 functional acknowledgment for this message, once one has been sent or received
X12 Messages	`duplicated_message_id`	X12 Messages	Duplicated Message ID	The earlier message this one duplicates. It’s set only when the message was flagged as a duplicate
X12 Messages	`forwarded_message_id`	X12 Messages	Forwarded Message ID	The original message this outbound message was forwarded from

Links inside JSON columns

Some links are inside a JSON column whose name ends in _secondary, such as station_secondary. It holds extra values for the column it’s named after, such as the location ID behind a station name. The JSON column is in the view only while that column is selected. Read the value with ->>, then convert it with ::bigint before you compare it to a row_key.

Use a LEFT JOIN so rows without a match stay in your results. Some link columns are empty, and some point at rows the other view leaves out, as Good to know describes.

Join examples

These examples use PostgreSQL. Replace <equipment_view> and the other placeholders with the tables you loaded the views into. If your warehouse uses another SQL dialect, keep the same columns and change how the JSON value is read. Each history event with its car. Keep Timestamp and Type selected in Equipment History, and Equipment in Equipment Inventory.

SELECT h.row_key AS history_event_id, h."timestamp", h."type", e."equipmentName"
FROM <equipment_history_view> AS h
LEFT JOIN <equipment_view> AS e
  ON e.row_key = h."equipmentId";

Each charge audit record with its waybill. Keep Waybill ID and Charge Audit Amount selected in Charge Audit Records, and Waybill ID and Waybill Date in Waybills.

SELECT a.row_key AS audit_record_id, a.charge_audit_amount, w."waybillNumber", w."waybillDate"
FROM <charge_audit_records_view> AS a
LEFT JOIN <waybills_view> AS w
  ON w.row_key = a.waybill_id;

Each car’s station, read from a JSON column. Keep Station selected in Equipment Inventory. This query reads only the Equipment Inventory view, so you can run it on Cedar’s database:

SELECT row_key AS equipment_id,
       "station",
       ("station_secondary"->>'groupingId')::bigint AS station_location_id
FROM <equipment_view>;

In your BI tool, relate station_location_id to the row_key of your Locations view. In a warehouse that holds both views, join with ON l.row_key = (e."station_secondary"->>'groupingId')::bigint. A few things to know about Locations:

parent_grouping_id walks your location hierarchy. Road rows aren’t in the view, so a top-level location’s parent may not match a row.
Location template attributes become columns named <location type>.<attribute>, for example station.region, so attributes with the same name on different location types stay separate. Because of the dot, put these names in double quotes in SQL: "station.region".
The view includes deleted locations as well as current ones.

Troubleshooting

Password authentication failed

Check that you copied the password exactly.⁠‌‌‌‌‌‌‌‌‌‌⁠ If it was lost or someone reset it, select Reset credential and use the new password. A reset immediately invalidates the old one.

Permission denied for the view

Your login can read only the view it was created for. Check that you’re querying the full view name shown in the credential, including the cedar_apps schema.

A column disappeared

Someone updated the view’s columns. Open the source in Data Depot to see the current selection and add the column back if you need it.

I can browse sources but can't provision

You have read access but not the permission to create views. Data Depot shows a View only bar that names the role to ask for, and Choose columns becomes Columns, which shows the columns in the current view. See Access and permissions.

A source I expected isn't listed

Some sources are available only to North American carriers. Check the available sources table.

​Browse the catalog

​Available sources

​Set up access

​Connect to the view

​Column names in SQL

​Private network connectivity

​Postgres views or CDC delivery?

​Change the columns later

​Manage existing access

​Reset the password

​Revoke access

​Statuses

​Limits

​Join sources together

​Every row has a row_key

​Links between views

​Cars, history, and locations

​Charges and waybills

​X12 Messages

​Links inside JSON columns

​Join examples

​Troubleshooting

​Related pages

Browse the catalog

Available sources

Set up access

Connect to the view

Column names in SQL

Private network connectivity

Postgres views or CDC delivery?

Change the columns later

Manage existing access

Reset the password

Revoke access

Statuses

Limits

Join sources together

Every row has a row_key

Links between views

Cars, history, and locations

Charges and waybills

X12 Messages

Links inside JSON columns

Join examples

Troubleshooting

Related pages