Who is this article for?

Users who need to combine datasets for analysis.

Data feed subscription is required.

Ideagen Audit Analytics data feeds are delivered as structured text files via SFTP, with most databases including a main feed file plus supporting files containing related detail such as people names, taxonomy categories, subsidiary information, or financial data.

This article explains how to join data feeds and link datasets within.

Understanding the feed structure

Each Ideagen Audit Analytics database is delivered as a set of files: one main feed and one or more supporting tables. The main feed contains one row per record (for example, one row per audit opinion or one row per restatement event). Supporting tables contain related detail that doesn't fit in a single row, such as multiple fiscal year periods for a restatement or multiple parties named in an AAER enforcement action.

Every feed comes with two reference documents:

• Entity Relationship Diagram (ERD) – a visual map showing the main feed at the centre and each supporting table branching off, with the key fields that connect them
• Data Dictionary – field-level definitions for the main feed and all supporting tables, including data types, allowable values, and which fields are primary keys, foreign keys, and business keys

Both are available from your Account Manager or support.

Key field naming conventions

Ideagen Audit Analytics feeds follow consistent naming patterns:

Key fields (for joining)

• Fields ending in _KEY are primary keys (for example, AUDIT_OPINION_KEY, AAER_EVENT_KEY). These uniquely identify each row in a feed and are auto-increment numeric identifiers assigned by Ideagen Audit Analytics
• Fields ending in _FKEY are foreign keys (for example, AUDITOR_FKEY, COMPANY_FKEY, AUDIT_OPINION_FKEY). These reference a primary key in another table and are how you join tables together
• Business keys are fields that uniquely describe a row in human-readable terms, such as a combination of company identifier and date. The data dictionary identifies these separately from primary keys

Other common naming patterns

• Date fields: _DATE for a specific date, _YEAR for a fiscal or reporting year, _QTR for quarter, _FY for fiscal year
• Flag fields: IS_* and HAS_* for binary indicators, typically using 0 = No, 1 = Yes, 2 = Did not disclose
• Financial fields: LATEST* prefixes indicate the most recent value (for example, LATESTQU_MARKET_CAP for most recent quarterly market cap, LATESTFY_MARKET_CAP for most recent fiscal year market cap)

Joining a main feed to its supporting tables

This is the most common join – enriching a main feed with detail from its supporting files. The pattern is straightforward: match the foreign key in the supporting table to the primary key in the main feed.

For example, Feed 34 (Revised Audit Opinions) has supporting tables for fund associations, going concern issues, and financial data. The supporting table audit_opinion_to_going_concern_issues.txt contains an AUDIT_OPINION_FKEY field that matches to AUDIT_OPINION_KEY in the main feed.

Most supporting table joins follow this one-step pattern: foreign key in the supporting table → primary key in the main feed.

Multi-level supporting tables

Some databases have multi-level supporting tables, where a supporting table has its own child tables. For example, in the AAER database (Feed 91), the main feed connects to feed_support91_all_related_release via AAER_EVENT_KEY, and then that table connects to further detail tables (related parties, release issues, respondent orders) via AAER_RELEASE_FKEY. The ERD makes these chains easy to follow – look for tables that branch off other supporting tables rather than connecting directly to the main feed.

Another common example is Feed 74 (AQRM), which uses a similar chain: the main feed is keyed on COMPANY_FKEY and FLAG_YEAR, and supporting tables like feed_support74_aaer_respondent link through AAER_RELEASE_TO_PARTY_FKEY to reach further detail such as respondent publish dates. AQRM is a multi-category risk flag tool, so its supporting tables connect to the underlying data behind each flag.

Tip

Always start with the ERD for the feed you're working with. It shows every table, every key relationship, and whether the join is one-to-one or one-to-many. This tells you upfront whether your result set will expand (one main record → multiple supporting rows) or stay flat.

Linking to company demographics

Most analysis requires company attributes (industry, location, market cap, revenue) that aren't stored in every feed. These come from Company Block feeds, which are shared reference tables containing company demographics and financial data.

Ideagen Audit Analytics maintains separate Company Block feeds for each geographic market, each with its own primary identifier.

To link a main feed to company demographics, match the COMPANY_FKEY (or equivalent) in your feed to the primary identifier in the relevant Company Block.

For SEC databases, COMPANY_FKEY is the CIK, used by the SEC and other datasets. In Canada, ISSUERNO comes from SEDAR. For Europe and Funds, identifiers (ENTITY_MAP_KEY and MFH_FUND_KEY) are internal Ideagen Audit Analytics keys, so linking to external data requires mapping via other fields.

COMPANY_KEY/CIK is also the main field for linking Ideagen Audit Analytics data to external sources like Compustat or CRSP for SEC-registered entities.

Linking across databases

Sometimes your research question spans two different Ideagen Audit Analytics databases – for example, you want to know whether companies with internal control weaknesses also had financial restatements. Linking across databases requires understanding what the two feeds have in common.

Databases share a common key

Many Ideagen Audit Analytics databases use company-level identifiers (COMPANY_FKEY/CIK for SEC data), allowing you to link databases by company. However, matching specific records across databases requires the correct key or field combination.

Some databases have direct key links. For example, Critical Audit Matters (CAMs) connect to Audit Opinions via a foreign key, as CAMs are included in the audit opinion filing. These relationships are detailed in each feed's data dictionary and ERD.

Databases don't share a common key

Some cross-database links need a date-range join instead of a direct key match, especially when databases track overlapping events without a common record.

For instance, linking Internal Controls (SOX 404) to Financial Restatements involves matching the fiscal year end (FISCAL_YEAR_ENDED_IC_OPINION) with the restatement period (RES_BEGIN_DATE to RES_END_DATE) alongside the company ID. This identifies which restatements coincide with specific internal control opinions.

Date-range joins require careful attention to the meaning of each date field (e.g., fiscal year end vs filing date). Consult the data dictionary to ensure accurate matching.

Combining approaches

In practice, a research project often involves multiple types of joins.

A typical workflow might look like:

1. Start with a main feed (for example, Restatements).
2. Join to its supporting tables for detail you need (for example, restatement categories, affected fiscal year periods).
3. Link to the Company Block for demographics (industry, market cap, location).
4. Optionally link to another database (for example, Auditor Changes) using a company identifier and date logic.

Each step builds on the previous one. The ERDs and data dictionaries for each feed involved will tell you exactly which fields to use at each step.

Working with data feeds

Below are some best practices to get you started:

• Start with the ERD. Before writing any join logic, look at the ERD for the feed you're working with. It gives you the complete picture of tables and relationships in one view
• Check the data dictionary for field definitions. Field names are descriptive, but don't assume – FISCAL_YEAR_OF_OPINION and FISCAL_YEAR_ENDED_OF_OPINION are different fields with different meanings. The data dictionary explains exactly what each field contains
• Watch for one-to-many joins. Joining a main feed to a supporting table often expands your row count. If your result set is larger than expected, check whether the supporting table has multiple rows per main record – this is by design, not an error
• Use the right identifier for the market. CIK works for SEC data, ISSUERNO for Canada, and Ideagen Audit Analytics-assigned keys for Europe and Funds. Mixing identifiers across markets will produce empty joins
• Be cautious with date-range joins. When linking databases that don't share a key, verify you're matching on the right date fields and understand whether you need an exact match, an overlap, or a "falls within" condition