Writing to an External DB
When working with the C3 Agentic AI Platform, you can connect to external databases using the CData JDBC driver available on the platform. Create, Read, Update, Delete (CRUD) operations are supported on external databases. However, you should exercise caution regarding write operations to virtualized tables. Here are key recommendations based on best practices:
Avoid Writing to Virtualized Tables – It is inadvisable to perform write operations directly on virtualized tables, as this can lead to complications and unpredictable behavior in data management.
Implement Primary Keys – Always define a primary key (PK) on tables in external databases that you are writing to. A primary key is essential for merging or updating records correctly. Without a PK, you may encounter problems such as incomplete actions or failed writes.
Create a set of tests – You should define a set of test queries based on the expected write and update patterns of the application. This ensures that application behaves as expected when in use.
Implement primary keys in external databases
Using primary keys (PK) is crucial when writing to external databases. Here are several reasons to implement primary keys:
Data Integrity – Primary keys uniquely identify each record in a database table, preventing duplicate entries and ensuring data integrity, which is fundamental for maintaining accurate and reliable datasets.
Concurrency Control – In environments with multiple processes updating data simultaneously, a primary key helps manage concurrency effectively. It enables the system to distinguish between different records, which ensures updates are applied correctly.
Predictable Behavior – Enforce primary keys to ensure predictable outcomes in data operations. Without a primary key, users may encounter unpredictable results, especially when multiple updates occur concurrently.
Best Practices – Adhering to database design best practices, including the use of primary keys, contributes to better data management and a more robust application architecture.
Alternative data ingestion methods
Additionally, consider alternative data ingestion methods, such as file-based data integration, which allows for the use of file storage integrations to facilitate data loading into external databases. This method can enhance flexibility in data handling and streamline the integration process. File-based data integration can involve creating storage integrations from blob storage to external databases, enabling systems (like Snowflake or BigQuery) to look for files at specified locations for ingestion.
After defining a file-based integration, you can create an Export job to export Type data to file.
These methods provide users with different options to optimize their data workflows while ensuring that primary keys are implemented for efficient and reliable data management.