Frequently Asked Questions (FAQ)

Find answers to the most common questions about the PDQ data platform.

Platform Architecture and Core Concepts

Q: What is the PDQ platform, and how do the core modules fit together?

A: PDQ by Simplitics is a unified data management platform that combines automation with end-to-end active metadata management. Seven modules cover the flow from source to delivery:

• AME (Active Metadata Engine): The central repository holding all system logic, historical operations and planned schedules. Every other module communicates through the AME APIs.
• INGEST (Data Importer): A containerised agent that extracts data from SQL databases, REST APIs and file systems on a schedule, writing raw output to the Landing zone.
• DLS (Data Lake Service): An event-driven worker that detects new files in Landing, archives them in Raw Archive, standardises them into Trusted and publishes them for SQL access.
• DWA (Data Warehouse Automation): A batch- and event-triggered engine that automates data modelling, SQL code generation and ELT push-down loading in the target database.
• QPI (Quality Performance Indicator): The data quality framework that runs SQL-based controls and detects anomalies.
• DataOps Console: The operator-facing interface for monitoring daily execution, slicing runtime statistics and resolving failures.
• DevUI / Configuration UI: The developer interface, backed by Git integration, for managing metadata definitions and CI/CD deployment.

See Architecture for the full picture and the Platform guide for a layer-by-layer deep dive.

Q: What does "Design as Documentation" mean, and how does it prevent code drift?

A: Design as Documentation is the founding principle that your design is the documentation — and that documentation is what generates the executable code. Because tables, transformation rules and loading scripts are generated from active metadata, the documentation cannot fall out of sync with what actually runs. There is no separate artefact to maintain, and therefore no code drift over time.

Q: What does PDQ actually create in my target database, besides tables and rows?

A: The generation run emits the governance along with the structure:

• Column and table comments, inside the CREATE TABLE on Snowflake and Databricks, taken from the descriptions in the data contract and the model.
• Primary keys on publish tables, unique constraints from business keys on core tables, and foreign keys from relationship metadata. All three default to on.
• Category tags from fieldDomain and sensitivity tags from the sensitive flag, using each platform's own tagging facility — SET TAGS and SET MASK on Databricks.
• Query tags on every statement, so each query in the target's history names the job, step and run that issued it.

Nothing on that list is a separate step you run afterwards. See What lands in the target environment.

Q: Do I have to classify sensitive columns again in the target?

A: No. Classification is declared once on the sourcefile field — sensitive and fieldDomain — and the generator applies it to the target columns as part of building the tables. On hierarchical sources, the domain tags of business keys inherited from ancestor levels are also applied to the normalised child tables that carry those keys, so a column classified once stays classified everywhere the platform copies it.

Two caveats worth knowing: Databricks emits no enforced UNIQUE, PK or FK, so uniqueness there has to be a QPI quality check; and the tag names come from TAG_NAME, SENSITIVE_TAG_NAME and SENSITIVE_TAG_VALUE, which are worth setting to your own scheme before the first generation run.

Q: Can I get PDQ's metadata and lineage into my own catalogue?

A: Yes, by two routes.

• The REST API. The AME v3 API is the same interface the console and the generators read — configuration, data contracts, schedules, logs, audit records and lineage. /api/v3.2 covers object-level generation, and the SQL Generator API returns the generated DDL itself, so definitions can be diffed in CI before deployment.
• OpenLineage. Every execution emits start and end events; QPI quality checks emit parent-job events; attribute-level lineage is available as a detailed option. Lineage therefore lands in whatever catalogue or observability platform you already run, without a separate ingestion pipeline of its own.

Q: How does PDQ avoid vendor lock-in across clouds and databases?

A: All modules are packaged as standalone Docker containers (Python 3.12 on Debian 12), so the platform runs on Azure, AWS, GCP or on-premises without change.

Data can also be added, modified or discontinued at any architectural layer — Landing, Raw Archive, Trusted, Published, Integrated or Business. That loose coupling means you can bypass a component or substitute a preferred storage or database technology without breaking the rest of the ecosystem.

Installation and Configuration

Q: What are the minimum requirements to run the PDQ platform?

A: The platform runs as Docker containers on a Linux host — Ubuntu 22.04 is the reference platform — with network access to the source and target systems.

	Minimum	Recommended
Docker Engine	20.10+	24.0+
RAM	8 GB	16 GB
CPU	4 cores	8 cores
Storage	100 GB	500 GB
Database	Shared	Dedicated instance (SQL Server, PostgreSQL or another supported engine)

The sizing table is maintained in Installing the components — treat that page as the source of truth.

Q: How do I configure FQDN for Caddyserver?

A: For Caddyserver configuration:

1. Ensure your server has a Fully Qualified Domain Name (FQDN)
2. Open ports 80 and 443 in firewall (internal only, not public)
3. Create a new Docker network (e.g., xx_caddynet)
4. Remove all mapped ports from ame-compose.yml for security
5. Configure Azure AD App Registration for authentication

Q: Why can't I access the developer interface?

A: Check the following:

1. Docker containers running: docker ps -a - all should be "Up"
2. Network configuration: Verify ports are correctly mapped
3. Caddyserver status: If used, check Caddy configuration
4. Firewall settings: Ensure correct ports are open
5. DNS resolution: Verify FQDN works correctly

Data Ingestion (INGEST)

Q: How do I add a new data source?

A: An ingestion flow can be configured through the DevUI or programmatically through the API.

Via the DevUI:

1. Open the Ingest page and click Add new system (a unique, single-word system name plus a description)
2. Select the connection type — DB or API
3. For DB: provide database name, alias, server address, port, user and password
4. For API: provide the API URL, auth method (OAuth 2.0, TOKEN or NONE), token URL and any headers or connection properties
5. Configure the Source Export: the fromClause (DB) or apiPath (API), output format (JSON or CSV), output file name, suffix and schedule cycle
6. Test the connection and verify the first delivery

Via code:

1. Connection — post a connection JSON to /api/v2/connection/for/:sourceSystem. Database passwords must first be encrypted through the AME encryption API.
2. Export definition — post an export instruction JSON containing fromClause/apiPath, alias, suffix, inclColumns/exclColumns, whereClause, scheduleCycle and EnableCDC to /api/v2/connection/for/:sourceSystem/:alias
3. Agent — start an INGEST container with the target -n :sourceSystem

Full parameter reference: Ingest.

Q: How does Change Data Capture (CDC) work in INGEST?

A: With EnableCDC = 1, INGEST compares each exported row against the previously exported state and writes only new, changed or deleted records.

CDC is an in-memory process. Restarting the INGEST container clears that row memory, so the next execution performs a full comparison. Combining EnableCDC = 1 with dynamic filtering in the whereClause should be done with care — rows filtered out by the query look like deletes.

Q: Which dynamic timestamp placeholders can I use?

A: Placeholders can be used in the filename suffix and in query filters:

Placeholder	Meaning
[SCHEDULEDTIME]	The planned schedule timestamp. A late-running job still extracts the window it was meant to, not the window it happened to run in.
[EXECUTIONTIME]	The actual timestamp when the export started.
[LASTEXECUTION]	The timestamp of the previous export — the basis for incremental queries such as WHERE ModifyDate > '[LASTEXECUTION]'.

Relative offsets such as [SCHEDULEDTIME_MINUS_7_DAYS] are supported for rolling-window extraction. In the output filename suffix the same values are written as [__SCHEDULEDTIME__], [__EXECUTIONTIME__] and [__LASTEXECUTIONTIME__].

Q: What does "Missing Expected Files (Red Banner)" mean?

A: A red banner indicates:

• Expected file types haven't been delivered according to schedule
• INGEST agents have failed or haven't run
• Source systems may be unavailable or have changed structure
• Check INGEST Tasks view for specific error messages

Q: How do I configure INGEST for Microsoft Teams and SharePoint?

A: Files stored in a Teams channel live in the underlying SharePoint document library, so both are ingested through the Microsoft Graph API:

1. Azure AD setup — register an application in Azure AD/Entra, note the Application (client) ID and Directory (tenant) ID, and generate a client secret
2. API permissions — grant the Microsoft Graph application permissions (Sites.Read.Selected or Sites.Read.All, plus Files.Read.All) and have an Azure AD administrator grant admin consent
3. Connection — post an API connection to /api/v2/connection/for/<SOURCESYSTEMNAME> with APIUrl: "https://graph.microsoft.com/v1.0", APIAuthMethod: "OAuth 2.0", APITokenUrl: "https://login.microsoft.com/<TENANT_ID>/oauth2/v2.0/token" and the client credentials in APIConnectionProperties
4. Export definition — point at the Graph item path, for example /sites/{site-id}/drives/{drive-id}/items/{item-id}/children, with a filter such as lastModifiedDateTime=[SCHEDULEDTIME_MINUS_3_DAYS]
5. Container — create a teams.yaml or sharepoint.yaml setting sourcetype to teams or sharepoint, and run the simplitics1/simpleingestion container

Store client secrets encrypted through the AME encryption API — never in plain text in a configuration file.

Data Lake Service (DLS)

Q: What do the different zones in DLS Trace mean?

A: DLS zones represent:

• Landing: First landing for raw data from INGEST
• Raw Archive: Permanent archival for audit and replay
• Trusted: Standardized format with validated structure
• Published: Table format optimized for SQL access
• Profiled: Extended metadata and data profiling statistics

Q: Why are counts different between zones?

A: Small differences are normal:

• Landing to Raw Archive: deduplication handling may reduce the count
• Raw Archive to Trusted: validation may filter out invalid records
• Trusted to Published: aggregation or filtering may change the count

Landing, Raw Archive, Trusted and Profile should match. The Stage count may sit slightly lower; a difference of up to 10–20 is normal and not by itself a fault. Anything beyond that is worth investigating.

Q: How do I enable streaming for large XML/JSON files?

A: A large or deeply nested XML or JSON file that is loaded into memory in one piece can exhaust the server's RAM and crash the processing worker. Streaming solves this:

1. Identify the hierarchical level at which records should be split — typically the repeating element, such as <company> or <order>
2. Set "useForSplittingRecords": 1 on that level in the fileStructure metadata
3. Use the API — this setting is not available in the DevUI and must be updated through the backend, for example /api/v3.1/sourcefiles/:sourceFilename

DLS then streams the file chunk by chunk from that level downwards, omitting the parent elements above the split point and emitting multiple flatter records. Memory consumption drops sharply and throughput increases. The DWA SQL generator detects the flag automatically and simplifies the downstream staging queries accordingly.

Q: What are Hierarchical IDs (_HID) and granular checksums?

A: Both are generated by the Data Modifier for every element in a nested document, and both exist to make nested data traceable:

• _HID is a dot-delimited path such as 1.2.4.1 that records an element's exact position in the source document. It lets you trace any database row back to where it came from, and it acts as a stable internal join key when nested arrays are split into separate tables — so joins do not depend on business keys being present.
• Granular checksums are calculated at every object level rather than only at the document root. This makes change detection precise: an edit to one child record does not mark the whole parent file as changed.

Data Warehouse Automation (DWA)

Q: What is the difference between the Ensemble model and the CORE model?

A: DWA uses a two-layered modelling architecture inside the Integrated layer:

Model	Purpose	Shape
Ensemble / Intermediate	Agile, highly normalised integration based on Anchor Modeling or typed Data Vault patterns	Every attribute and relationship in its own physical table, fully historised, with direct traceability to __fileKey
CORE / Enterprise	A physical, consolidated copy of the ensemble views representing the integrated enterprise model	One row per business key — the latest version — for fast BI and downstream reporting

The Ensemble model answers "what did we know, and when?". The CORE model answers "what is true now?" quickly. See Data Warehouse Automation (DWA).

Q: What CORE loading patterns are available?

A: Five patterns control how the CORE/Enterprise model is populated:

Pattern	Behaviour
None	Leaves the data as a consolidated view without materialising a physical table (default)
Full	Truncates and reloads all data on every run
Dayspan	Rolling window — captures changes from a configurable timeframe, for example the last 7 days
Incremental	Captures only the latest changes, based on the execution schedule interval
Transaction	Appends new records only, without performing updates

Q: What's required for DWA to generate SQL?

A: Every mapping group — the unit of work — must contain at least one business key mapping and one attribute mapping. If the key mapping is missing, DWA generates no SQL and no data loads, and neither the UI nor the API will warn you. Per load type:

• Object load: complete key mapping
• Attribute load: key mapping + attribute mapping
• Relationship load: two complete keys from the same source
• All mappings must sit within the same mapping group, which also shares source file, filter conditions and sort orders

Q: How are relationships created?

A: Relationships are defined in the target model, never in the individual mapping program. When one source file populates the complete business keys of two related model objects within the same mapping group, DWA generates and runs the relationship load by itself.

The condition is that the complete business key is populated for both objects. If it is not, the load still creates a relationship — but against a false business key, which means no related data.

Q: How do I restart failed DWA tasks?

A: To restart tasks:

1. Identify failed tasks (red in list/Gantt chart)
2. Mark relevant tasks for specific schedule and source file
3. Use "Restart Tasks" from DataOps console
4. Monitor progress to ensure successful restart

Q: Why are unnecessary DWA schedules created?

A: This can happen when:

• File-triggered tasks are created without CORE model dependencies
• Solution: Stop schedule via API: POST /api/v2/master/schedule/{sourcefile}
• Set ValidTo date to deactivate the schedule

DataOps and Monitoring

Q: What are the daily monitoring routines?

A: Daily checklist:

1. ☑ System Health: Are the services up, and did anything fail or warn overnight?
2. ☑ INGEST Task Verification: Confirm completed runs, and look for runs that returned zero rows
3. ☑ DLS Trace Check: With the date filter on Last 24 Hours, verify that the counts for Landing, Raw Archive, Trusted and Profile match — Stage may sit slightly lower, and a difference of up to 10–20 is normal. Confirm the green banner on the DLS Task Checklist ("All configured files have at least one delivery").
4. ☑ DWA Loading Confirmation: Expected and Completed should match, and Failed should be 0. If Failed > 0, filter the task table, select the failed tasks and click Restart Tasks.
5. ☑ QPI Monitor: Did any quality check report a failing result?

Each console page carries its own checklist comparing configured against executed — the fastest way to spot work that never started at all, which a "no failures" metric will never reveal. See The daily pass.

Q: How do I use the DataOps Console effectively?

A: DataOps Console offers:

• Dashboard View: High-level status across all components
• Runtime Statistics: Detailed performance and trends
• Task Management: Restart of failed processes
• Alert Management: Centralized notification handling

A page reporting "no data for the selected time range" is usually answering correctly for a window that is too narrow — widen the global date range before assuming a failure.

Troubleshooting

Q: What do I do if the entire system is down?

A: First steps:

1. Stop and start VM from cloud portal (most common solution)
2. If problem persists: SSH to server
3. Check disk: df -h — a full /datadrive blocks everything
4. Check Docker: docker ps -a (all should be "Up")
5. Manual restart: from /datadrive/configs, run docker compose down followed by docker compose up -d for each component in strict order: UI → AME → DLS → DWA → INGEST

The order matters: each service depends on the one before it, and AME must be up before DLS, DWA and INGEST can fetch their instructions.

Q: How do I handle "System Health Status FAIL"?

A: When System Health shows FAIL:

1. Identify specific failed tasks/files
2. Restart the failed components
3. Check logs for root cause
4. Document incident for future reference

Q: How do I recover interrupted pipelines after a crash?

A: Work forward through the layers, restarting only the step that did not complete:

1. Find what never finished — open the Job Log in System Health and filter on status = INIT to find processes that started but never reported completion. Check the target tables using the __fileKey to see whether data was partially loaded.
2. Inspect DLS Trace — filter on lastSeenOn:
   • Stuck in raw: select the rows, open the admin console and click Load Trusted
   • Stuck in landing: check the file size first. A very large file that caused an out-of-memory crash needs to be split into smaller subsets before you re-run Load Raw.
   • Missing Trusted/Bronze load (trustedNumberOfFiles = 0): filter on that count or on the specific fileKey and click Load Trusted
3. Restart the containers if they are unresponsive — see the startup order above.

Q: How do I resolve a stage load failure caused by JSON line length in Azure Synapse?

A: Symptom: the stage load fails with an error referencing a 500,000-character limit in the Synapse OPENROWSET function.

Cause: large nested JSON extractions accumulate _hid and _checksum attributes across deep hierarchies, which can push a single JSON line past Synapse's hard line-length limit.

Resolution:

1. Open Azure Storage Explorer and navigate to trusted/temporary_files/<System>/<SourceFile>
2. Download the .json.gz files created since the last successful load
3. Run the local cleanup script to split the over-length JSON lines
4. Upload the cleaned files back to the storage root folder, overwriting the original filenames
5. In the DataOps Console, click Restart on the failed stage job

Q: What does "Metadata Case Mismatch" mean?

A: Target database engines — Snowflake, Databricks, SQL Server, Azure Synapse, Fabric and PostgreSQL — evaluate fieldKey against the JSON/XML document path using SQL JSON operators that require an exact case match. If fieldKey or path differs from the source by a single character, the operator finds nothing and the column returns NULL.

• Keep fieldKey and path matched to the source exactly — they are case-sensitive
• To apply a different naming convention in the target tables (uppercase, f_ prefixes and so on), use fieldAlias and levelAlias together with the global settings columnPrefix and tableNameCasing

Performance and Scaling

Q: How do I scale the PDQ platform for larger data volumes?

A: For scaling:

• DLS: Minimum 6 containers, scale workers based on capacity
• DWA: Minimum 4 containers, place near database
• INGEST: Distribute agents close to data sources
• Consider separate regions for different components

Q: What are performance best practices?

A: Performance recommendations:

• Place AME and DLS in same region as repository database
• Use streaming for large XML/JSON files
• Optimize network latency between components
• Monitor resource usage regularly via DataOps Console