Repository: dbt-labs/dbt-core
Pull request: Support configurable delimiter for seed files, default to comma (#3990)
resolves #3990

### Original description
Added support to configure a custom delimiter for seed files, defaults to comma.

### Copilot description
This pull request introduces several changes related to seed files and configuration options. The most important changes include adding new test classes and methods to test different scenarios related to seed files with unique delimiters, adding a new configuration option for specifying a delimiter for seed files, and modifying various functions to support the new delimiter configuration option.

Summary of changes:

* <a href="diffhunk://#diff-69d52d1dd2e30581f2a9aa9f8f3fe63ae72240e44536afe6a259dd60bb69c350R168-R245">`tests/adapter/dbt/tests/adapter/simple_seed/test_seed.py`</a>: Added a new test class and several test methods to test seed files with unique delimiters. <a href="diffhunk://#diff-69d52d1dd2e30581f2a9aa9f8f3fe63ae72240e44536afe6a259dd60bb69c350R168-R245">[1]</a> <a href="diffhunk://#diff-69d52d1dd2e30581f2a9aa9f8f3fe63ae72240e44536afe6a259dd60bb69c350R21">[2]</a> <a href="diffhunk://#diff-69d52d1dd2e30581f2a9aa9f8f3fe63ae72240e44536afe6a259dd60bb69c350R33">[3]</a>
* <a href="diffhunk://#diff-007a86f768b23e1516e6d253d71656b96d46672fc7432dbc81d1d9445c98ca05R547">`core/dbt/contracts/graph/model_config.py`</a>: Added a new configuration option `delimiter` to the `SeedConfig` class.
* <a href="diffhunk://#diff-ab89b13f1cb33eb645957af7078ced47451063f725e2f27e9172aae7b9e494fbR58">`tests/functional/artifacts/expected_manifest.py`</a>: Added a new configuration option `delimiter` to a function in `expected_manifest.py`.
* <a href="diffhunk://#diff-a56008cbef9f36a5956db28a2328433eca1e3f910e68b6b1e4c51f6c413255ecR674">`tests/unit/test_contracts_graph_parsed.py`</a>: Added new configuration options `delimiter` to different functions. <a href="diffhunk://#diff-a56008cbef9f36a5956db28a2328433eca1e3f910e68b6b1e4c51f6c413255ecR674">[1]</a> <a href="diffhunk://#diff-a56008cbef9f36a5956db28a2328433eca1e3f910e68b6b1e4c51f6c413255ecR523">[2]</a> <a href="diffhunk://#diff-a56008cbef9f36a5956db28a2328433eca1e3f910e68b6b1e4c51f6c413255ecR615">[3]</a>

Other changes:

* <a href="diffhunk://#diff-ffdbb2f99c9725318181589796b3786fb0aec6fcc543c7ff71beb52dbba18451R37-R40">`tests/adapter/dbt/tests/adapter/simple_seed/fixtures.py`</a>: Added a new model to the list of fixtures.
* <a href="diffhunk://#diff-f1f6f54acf17e1434c92df4da7a7bc08178a8c76c57fab20dae79097827c5f50R1-R6">`.changes/unreleased/Features-20230714-202445.yaml`</a>: Added support for configuring a delimiter for a seed file.
* <a href="diffhunk://#diff-97295dee71db4906a1e5ad954690dfcb8561854777d0ef106038b1cce7b3e9e5R395">`tests/functional/list/test_list.py`</a>: Added a new configuration option to a function.
* <a href="diffhunk://#diff-d14b7d00e52da5da96bfb3bdef414e9289d923efe0a9cdadd716d792c7199535R868-R870">`core/dbt/context/providers.py`</a>: Added a new parameter to a function.
* <a href="diffhunk://#diff-2eee7e45ddbdac7ecdd8458a1c8c6b0d6d0c7dc6169e321eb2ab7a537f4171b1L138-R143">`core/dbt/clients/agate_helper.py`</a>: Added a new parameter to a function.

### Checklist

- [x] I have read [the contributing guide](https://github.com/dbt-labs/dbt-core/blob/main/CONTRIBUTING.md) and understand what's expected of me
- [x] I have signed the [CLA](https://docs.getdbt.com/docs/contributor-license-agreements)
- [x] I have run this code in development and it appears to resolve the stated issue
- [x] This PR includes tests, or tests are not required/relevant for this PR
- [x] I have [opened an issue to add/update docs](https://github.com/dbt-labs/docs.getdbt.com/issues/new/choose), or docs changes are not required/relevant for this PR -> https://github.com/dbt-labs/docs.getdbt.com/issues/3015
- [x] I have run `changie new` to [create a changelog entry](https://github.com/dbt-labs/dbt-core/blob/main/CONTRIBUTING.md#adding-a-changelog-entry)


Problem statement:
Introduce "delimiter" parameter for seeds? Allow non-csv file types
### Describe the feature
Use alternative delimiters for seeds besides "," (comma)

### Describe alternatives you've considered
Currently, the only option is to load a non-csv seed into database as a temp table and then reference that as a "source".

### Additional context
No, not database specific. 

### Who will this benefit?
Anyone who uses seeds.

Hints from issue discussion:
@sgoley When you say "non-csv file types," are you talking only about tabular data with non-comma delimiters (e.g. TSVs)? Or are you also thinking about other formats for structured data (JSON, XML, ...)?

If it's just a question of delimiters, could you provide a bit more background on the difficulty you're encountering? Is there a blocker to pre-processing your files, so as to switch from tab/semicolon/other delimiter to commas?

We've got another issue already open for newline-delimited JSON support: https://github.com/dbt-labs/dbt/issues/2365
Yes, in my case I am specifically talking about non-comma delimiters like TSVs, semi-colon delimited (common in some european countries given the numerical comma standard), or "|" ( 'VERTICAL LINE' U+007C ) delimited files (infrequent but used within US financial systems). 

I do completely understand that pre-processing is the only current workable solution, just opening the issue here since I searched and no truly similar issue has been raised or closed. 

After reading more into the [agate csv](https://agate.readthedocs.io/en/latest/api/csv.html) reader function, it looks like that particular api does not support a "sep" / "delimiter" parameter which is why I assume this was not supported natively?
@sgoley You're right, we just call agate's `from_csv` method for this:
https://github.com/dbt-labs/dbt/blob/f7680379fca80f653e8e7e2d45d7165a0fd864da/core/dbt/clients/agate_helper.py#L146

The good news: in that method, `agate` itself is happy to pass through any/all keyword arguments supported by python's built-in reader. It [accepts `**kwargs`](https://github.com/wireservice/agate/blob/9b12d4bcc75bf3788e0774e23188f4409c3e7519/agate/table/from_csv.py#L11) and [passes them into `csv.reader()`](https://github.com/wireservice/agate/blob/9b12d4bcc75bf3788e0774e23188f4409c3e7519/agate/table/from_csv.py#L79).

So I think this could be as simple as:
- adding a `delimiter` arg to `agate_helper.from_csv`
- adding a delimiter config to seeds (right around [here](https://github.com/dbt-labs/dbt/blob/f7680379fca80f653e8e7e2d45d7165a0fd864da/core/dbt/contracts/graph/model_config.py#L405-L410)), with default value either `,` or `None` (i.e. comma)
- adjusting the [`load_agate_table` context method](https://github.com/dbt-labs/dbt/blob/f7680379fca80f653e8e7e2d45d7165a0fd864da/core/dbt/context/providers.py#L763)

```python
        column_types = self.model.config.column_types
        delimiter = self.model.config.delimiter
        try:
            table = agate_helper.from_csv(path, text_columns=column_types, delimiter=delimiter)
```

So, I'm pretty close to tagging this as a `good first issue`. I have just a few more questions:
- What if you need custom quoting, escape characters, etc? Should we seek to add generalized support for _all_ `csv.reader()` kwargs as configs? Or do we think a configurable delimiter covers 90% of the bases?
- Would you expect to be able to define these seed files with other file extensions (e.g. `*.tsv`)? That change would need to happen in [a different part of the codebase](https://github.com/dbt-labs/dbt/blob/f7680379fca80f653e8e7e2d45d7165a0fd864da/core/dbt/parser/read_files.py#L144).
- Would we be better off saying "no" to all of the above, keep seed support _very_ simple (dbt isn't a data loader!), even at the cost of pre-processing?
Hello @jtcohen6 

Following this issue along with  JSON related (following #2365 )
As a  dbt community member, If I may answer your questions to @sgoley 

I understand the poor use cases outlined for dbt here https://docs.getdbt.com/docs/building-a-dbt-project/seeds - for valid/good use cases seeding small amounts of data as part of a build process (sampling ) - writing tests, documentation etc


What if you need custom quoting, escape characters, etc? Should we seek to add generalized support for all csv.reader() kwargs as configs? Or do we think a configurable delimiter covers 90% of the bases?

-- I believe the community would benefit from adding generalized support for csv - COMMA is a very common character and shows up everywhere. If the data is not escaped or quoted, the data may shift even for simple files. 
- support for different delimiters, text enclosure and escape character at a minimum


Would you expect to be able to define these seed files with other file extensions (e.g. *.tsv)? That change would need to happen in a different part of the codebase.

-- Another community benefit, sometimes we need to work with small datasets from different formats. I believe support for CSV (if it has the configurable delimiter with text enclosure and escape character; this covers any character delimited file *.txt,*.tsv, pipe delimited etc - `csv.reader()` kwargs should handle this beautifully), json , xml, parquet and ORC: these are becoming common. 

Would we be better off saying "no" to all of the above, keep seed support very simple (dbt isn't a data loader!), even at the cost of pre-processing? keeping it simple is great but this limits the entry point to dbt as only comma separated files. Going through 

---- As the community grows this is going to be a growing need. Now, dbt seed is still not ideal for loading data into a warehouse, as part of the build process for a data project, sampling (maybe find size limitations to avoid functionality abuse) needs to be recommended as part of best practice to keep the functionality light as is. Yes (dbt is not a a data loader), but can it be used to build an ELT pipeline based on small samples of different file formats? Mocking the (EL) process for common data formats, build pipeline based on a small amount of data. Let's not say "no", say yes 😄  - the community needs these functionalities. The preprocess of a file before entry to dbt is a little hectic and expensive for a modern day tool.

Thank you for considering this. Keep up the great work.

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.
Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest; add a comment to notify the maintainers.

Commit message: Update Features-20230317-144957.yaml

Patch to review:
diff --git a/.changes/unreleased/Features-20230317-144957.yaml b/.changes/unreleased/Features-20230317-144957.yaml
new file mode 100644
index 00000000000..f00c52795c9
--- /dev/null
+++ b/.changes/unreleased/Features-20230317-144957.yaml
@@ -0,0 +1,6 @@
+kind: Features
+body: Added support to configure a delimiter for a seed file, defaults to comma
+time: 2023-03-17T14:49:57.564210866+01:00
+custom:
+  Author: ramonvermeulen
+  Issue: "3990"
diff --git a/core/dbt/clients/agate_helper.py b/core/dbt/clients/agate_helper.py
index 1d69a2bd17f..e1a66d6375a 100644
--- a/core/dbt/clients/agate_helper.py
+++ b/core/dbt/clients/agate_helper.py
@@ -135,12 +135,12 @@ def as_matrix(table):
     return [r.values() for r in table.rows.values()]
 
 
-def from_csv(abspath, text_columns):
+def from_csv(abspath, text_columns, delimiter = ","):
     type_tester = build_type_tester(text_columns=text_columns)
     with open(abspath, encoding="utf-8") as fp:
         if fp.read(1) != BOM:
             fp.seek(0)
-        return agate.Table.from_csv(fp, column_types=type_tester)
+        return agate.Table.from_csv(fp, column_types=type_tester, delimiter=delimiter)
 
 
 class _NullMarker:
diff --git a/core/dbt/context/providers.py b/core/dbt/context/providers.py
index fec5111e36c..cc462e4280b 100644
--- a/core/dbt/context/providers.py
+++ b/core/dbt/context/providers.py
@@ -791,8 +791,9 @@ def load_agate_table(self) -> agate.Table:
         assert self.model.root_path
         path = os.path.join(self.model.root_path, self.model.original_file_path)
         column_types = self.model.config.column_types
+        delimiter = self.model.config.delimiter
         try:
-            table = agate_helper.from_csv(path, text_columns=column_types)
+            table = agate_helper.from_csv(path, text_columns=column_types, delimiter=delimiter)
         except ValueError as e:
             raise LoadAgateTableValueError(e, node=self.model)
         table.original_abspath = os.path.abspath(path)
diff --git a/core/dbt/contracts/graph/model_config.py b/core/dbt/contracts/graph/model_config.py
index 60cfb114ad0..3946107d9ef 100644
--- a/core/dbt/contracts/graph/model_config.py
+++ b/core/dbt/contracts/graph/model_config.py
@@ -435,6 +435,7 @@ class NodeConfig(NodeAndTestConfig):
         default_factory=dict,
         metadata=MergeBehavior.Update.meta(),
     )
+    delimiter: str = ","
     full_refresh: Optional[bool] = None
     # 'unique_key' doesn't use 'Optional' because typing.get_type_hints was
     # sometimes getting the Union order wrong, causing serialization failures.
diff --git a/test/unit/test_contracts_graph_compiled.py b/test/unit/test_contracts_graph_compiled.py
index 931dc9ddbaa..8fdbfeeb37f 100644
--- a/test/unit/test_contracts_graph_compiled.py
+++ b/test/unit/test_contracts_graph_compiled.py
@@ -133,6 +133,7 @@ def basic_uncompiled_dict():
         'tags': [],
         'config': {
             'column_types': {},
+            'delimiter': ',',
             'enabled': True,
             'materialized': 'view',
             'persist_docs': {},
@@ -182,6 +183,7 @@ def basic_compiled_dict():
         'tags': [],
         'config': {
             'column_types': {},
+            'delimiter': ',',
             'enabled': True,
             'materialized': 'view',
             'persist_docs': {},
diff --git a/test/unit/test_contracts_graph_parsed.py b/test/unit/test_contracts_graph_parsed.py
index 3ff0e2d75ee..5cff87ae675 100644
--- a/test/unit/test_contracts_graph_parsed.py
+++ b/test/unit/test_contracts_graph_parsed.py
@@ -65,6 +65,7 @@ def populated_node_config_object():
 def populated_node_config_dict():
     return {
         'column_types': {'a': 'text'},
+        'delimiter': ',',
         'enabled': True,
         'materialized': 'table',
         'persist_docs': {},
@@ -91,6 +92,7 @@ def test_config_populated(populated_node_config_object, populated_node_config_di
 def unrendered_node_config_dict():
     return {
         'column_types': {'a': 'text'},
+        'delimiter': ',',
         'materialized': 'table',
         'post_hook': 'insert into blah(a, b) select "1", 1',
     }
@@ -150,6 +152,7 @@ def base_parsed_model_dict():
         'tags': [],
         'config': {
             'column_types': {},
+            'delimiter': ',',
             'enabled': True,
             'materialized': 'view',
             'persist_docs': {},
@@ -251,6 +254,7 @@ def complex_parsed_model_dict():
         'meta': {},
         'config': {
             'column_types': {'a': 'text'},
+            'delimiter': ',',
             'enabled': True,
             'materialized': 'ephemeral',
             'persist_docs': {},
@@ -278,6 +282,7 @@ def complex_parsed_model_dict():
         'checksum': {'name': 'sha256', 'checksum': 'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855'},
         'unrendered_config': {
             'column_types': {'a': 'text'},
+            'delimiter': ',',
             'materialized': 'ephemeral',
             'post_hook': ['insert into blah(a, b) select "1", 1'],
         },
@@ -311,6 +316,7 @@ def complex_parsed_model_object():
         meta={},
         config=NodeConfig(
             column_types={'a': 'text'},
+            delimiter=',',
             materialized='ephemeral',
             post_hook=[Hook(sql='insert into blah(a, b) select "1", 1')],
         ),
@@ -318,15 +324,16 @@ def complex_parsed_model_object():
         checksum=FileHash.from_contents(''),
         unrendered_config={
             'column_types': {'a': 'text'},
+            'delimiter': ',',
             'materialized': 'ephemeral',
             'post_hook': ['insert into blah(a, b) select "1", 1'],
         },
     )
 
 
-{'enabled': True, 'tags': [], 'meta': {}, 'materialized': 'ephemeral', 'persist_docs': {}, 'quoting': {}, 'column_types': {'a': 'text'}, 'on_schema_change': 'ignore', 'grants': {}, 'packages': [], 'docs': {'show': True}, 'contract': False, 'post-hook': [{'sql': 'insert into blah(a, b) select "1", 1', 'transaction': True}], 'pre-hook': []}
+{'enabled': True, 'tags': [], 'meta': {}, 'materialized': 'ephemeral', 'persist_docs': {}, 'quoting': {}, 'column_types': {'a': 'text'}, 'delimiter': ',', 'on_schema_change': 'ignore', 'grants': {}, 'packages': [], 'docs': {'show': True}, 'contract': False, 'post-hook': [{'sql': 'insert into blah(a, b) select "1", 1', 'transaction': True}], 'pre-hook': []}
 
-{'column_types': {'a': 'text'}, 'enabled': True, 'materialized': 'ephemeral', 'persist_docs': {}, 'post-hook': [{'sql': 'insert into blah(a, b) select "1", 1', 'transaction': True}], 'pre-hook': [], 'quoting': {}, 'tags': [], 'on_schema_change': 'ignore', 'meta': {}, 'grants': {}, 'docs': {'show': True}, 'packages': []}
+{'column_types': {'a': 'text'}, 'delimiter': ',', 'enabled': True, 'materialized': 'ephemeral', 'persist_docs': {}, 'post-hook': [{'sql': 'insert into blah(a, b) select "1", 1', 'transaction': True}], 'pre-hook': [], 'quoting': {}, 'tags': [], 'on_schema_change': 'ignore', 'meta': {}, 'grants': {}, 'docs': {'show': True}, 'packages': []}
 
 def test_model_basic(basic_parsed_model_object, base_parsed_model_dict, minimal_parsed_model_dict):
     node = basic_parsed_model_object
@@ -452,6 +459,7 @@ def basic_parsed_seed_dict():
         'alias': 'foo',
         'config': {
             'column_types': {},
+            'delimiter': ',',
             'enabled': True,
             'materialized': 'seed',
             'persist_docs': {},
@@ -542,6 +550,7 @@ def complex_parsed_seed_dict():
         'alias': 'foo',
         'config': {
             'column_types': {},
+            'delimiter': ',',
             'enabled': True,
             'materialized': 'seed',
             'persist_docs': {'relation': True, 'columns': True},
@@ -803,6 +812,7 @@ def base_parsed_hook_dict():
         'tags': [],
         'config': {
             'column_types': {},
+            'delimiter': ',',
             'enabled': True,
             'materialized': 'view',
             'persist_docs': {},
@@ -882,6 +892,7 @@ def complex_parsed_hook_dict():
         'meta': {},
         'config': {
             'column_types': {'a': 'text'},
+            'delimiter': ',',
             'enabled': True,
             'materialized': 'table',
             'persist_docs': {},
@@ -910,6 +921,7 @@ def complex_parsed_hook_dict():
         'checksum': {'name': 'sha256', 'checksum': 'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855'},
         'unrendered_config': {
             'column_types': {'a': 'text'},
+            'delimiter': ',',
             'materialized': 'table',
         },
         'config_call_dict': {},
@@ -941,6 +953,7 @@ def complex_parsed_hook_object():
         meta={},
         config=NodeConfig(
             column_types={'a': 'text'},
+            delimiter=',',
             materialized='table',
             post_hook=[]
         ),
@@ -949,6 +962,7 @@ def complex_parsed_hook_object():
         checksum=FileHash.from_contents(''),
         unrendered_config={
             'column_types': {'a': 'text'},
+            'delimiter': ',',
             'materialized': 'table',
         },
     )
@@ -1226,6 +1240,7 @@ def test_invalid_severity(complex_parsed_schema_test_dict):
 def basic_timestamp_snapshot_config_dict():
     return {
         'column_types': {},
+        'delimiter': ',',
         'enabled': True,
         'materialized': 'snapshot',
         'persist_docs': {},
@@ -1262,6 +1277,7 @@ def basic_timestamp_snapshot_config_object():
 def complex_timestamp_snapshot_config_dict():
     return {
         'column_types': {'a': 'text'},
+        'delimiter': ',',
         'enabled': True,
         'materialized': 'snapshot',
         'persist_docs': {},
@@ -1288,6 +1304,7 @@ def complex_timestamp_snapshot_config_dict():
 def complex_timestamp_snapshot_config_object():
     cfg = SnapshotConfig(
         column_types={'a': 'text'},
+        delimiter=',',
         materialized='snapshot',
         post_hook=[Hook(sql='insert into blah(a, b) select "1", 1')],
         strategy='timestamp',
@@ -1324,6 +1341,7 @@ def test_invalid_missing_updated_at(basic_timestamp_snapshot_config_dict):
 def basic_check_snapshot_config_dict():
     return {
         'column_types': {},
+        'delimiter': ',',
         'enabled': True,
         'materialized': 'snapshot',
         'persist_docs': {},
@@ -1360,6 +1378,7 @@ def basic_check_snapshot_config_object():
 def complex_set_snapshot_config_dict():
     return {
         'column_types': {'a': 'text'},
+        'delimiter': ',',
         'enabled': True,
         'materialized': 'snapshot',
         'persist_docs': {},
@@ -1386,6 +1405,7 @@ def complex_set_snapshot_config_dict():
 def complex_set_snapshot_config_object():
     cfg = SnapshotConfig(
         column_types={'a': 'text'},
+        delimiter=',',
         materialized='snapshot',
         post_hook=[Hook(sql='insert into blah(a, b) select "1", 1')],
         strategy='check',
@@ -1472,6 +1492,7 @@ def basic_timestamp_snapshot_dict():
         'tags': [],
         'config': {
             'column_types': {},
+            'delimiter': ',',
             'enabled': True,
             'materialized': 'snapshot',
             'persist_docs': {},
@@ -1614,6 +1635,7 @@ def basic_check_snapshot_dict():
         'tags': [],
         'config': {
             'column_types': {},
+            'delimiter': ',',
             'enabled': True,
             'materialized': 'snapshot',
             'persist_docs': {},


Code review comments:
diff_hunk: @@ -435,6 +435,7 @@ class NodeConfig(NodeAndTestConfig):
         default_factory=dict,
         metadata=MergeBehavior.Update.meta(),
     )
+    delimiter: str = ","
path: core/dbt/contracts/graph/model_config.py
text: @user1:
This should go in [`SeedConfig`](https://github.com/dbt-labs/dbt-core/blob/main/core/dbt/contracts/graph/model_config.py#L500), now defined separately, rather than `NodeConfig`